1 subquery: is a select statement embedded in other sql statement.
.the subquery execute (inner query) once before the main query
.the result of the subquery is used by the main query.
2 pairwise comarison subquery
select * from employee
where (manager_id,department_id) in
(select manager_id,department_id
from employees
where employee_id in (178,174))
and employee_id not in (178,174);
nonpairwise comparison subquery
select employee_id,manager_id,department_id
from employee
where manager_id in
(select manager_id
from employees
where employee id in (174,141))
and department_id in
(select department_id
from employees
where employee_id in (174,141))
and employ_id not in (174,141);
)
3 using a subquery in the from clause
select a.last_name,a,salary,b.slaavg
from employees a ,(select department_id,
avg(salary) salavg
from employees
group by department_id) b
where a.department_id=b.department_id
and a.salary>b.salavg;
4 scalar subquery expressions
. a scalar subquery expression is a subquery that return exactly on column value from one row
. in oracle8i scalar subqueries can be used in condition and expression part and all clause.
1) sclaar subqueries in casse expression
select employee_id ,last_name,
(case
when department_id=
(select department_id from departments
where location_id=1800)
then 'canada'
else 'usa'
end) location
from employees
2)scalar subqueries in order by clasue
select employee_id,last_name
from employees e
order by (select department_name
from departments d
where e.department_id=d.department);
4 correlated subqueries
the wubquery references a column form a table in the parment query
select column1,solumn2....
from table1 outer
where column1 operator
(select column1,column2
from table2
where expr1=out.expr2);
e.g 1
select last_name,salary,department_id
from employees outer
where salary>
(select avg(salary)
from employees
where department_id=
outer.department_id);
)
e.g 2
display details of those employees who have switched jobs at lease twice
select e.employee_id,last_name,e.job_id
from employees e
where 2<=(select count(*)
from job_history
where employee_id=e.employee_id);
6 using the exists operator
. the exists operator tests for existencee of rows in the results set of the subquery
. if a subquery row value id found:
the search does not continue in the inner query
the condition is flagged true
.if a subquery row value is not fount
the condition is flagged fasle
the search continues in the inner query
e.g
find employees who have at least one person reporting to them
select employee_id,last_name,job_id,department_id
from employees outer
where exists (select count(*)
from employees
where manager_id=outer.employee_id);
not exist.
7 corelated update
use a correlated subquery to update rows in on table based on rows from another table
e.g
--denormalize the employees table by adding a column to store the department name
alter table employees
add(department_name varchar2(14));
--populate the table by using a correlated update
update employees e
set department_name=
(select department_name
from departments d
where e.departmentid=d.department);
8 correlated delete
delete test1 t1
where ster_id in(select ster_id form sales t2 where t.ster_id=t2.ster_id);
9 using the with clause ,you can use the same query block in a a select statement when it cocurs more than once within a complex query
the with clause retrieves the results of a query block and stores it in the user's the user's templary tablespace
the with clause improves performance.
e.g
with
dept_costs as(
select d.department_name,sum(e.salary) as dept_total
from employee e,departments d
where e,department_id=d.department_id
group by d.department_name),
avg_cost as(
select sum(dept_total)/count(*) as dept_avg
from dept_cost)
select *
from dept_costs
where dept_total>(select dept_avg
from afb_cost)
order by department_name;