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;