Oracle SQL Subquery

Pairwise Comparison Subquery

select * from emp

where  (job, deptno)  =  (select job, deptno

                                          from emp

                                          where ename = 'SMITH');

select * from emp

where (job,mgr) in (select job, mgr

                                from emp

                                 where ename =  'ALLEN')

Non-Pairwise Comparison Subquery

select * from emp

where job = (select job

                       from emp

                       where ename =  'ALLEN')

and mgr = (select mgr

                    from emp

                     where ename =  'ALLEN')

 

 

 

 

 

 

 

 

 

 

Sub-Query in From Clause

To see names, salaries,deptno and average sal of those employees who earn more than the average salary in their departments.

SELECT a.ename, a.sal, a.deptno, b.salavg

FROM emp a, (SELECT deptno,

                           AVG(sal) salavg

                           FROM emp

                           GROUP BY deptno) b

WHERE a.deptno = b.deptno

AND a.sal > b.salavg;

Scalar Sub Queries

Sub Query in Case Statement

select empno,ename,sal,job,

(case when deptno = (select deptno from dept

                                     where DNAME = 'ACCOUNTING')

     then 'Acc-Dept'

     else 'Non Acc'

 end)  Dept_Details

from emp

Correlated Sub Query

To see employees who earn a salary less than the average salary of their own job

Parent query takes each row and submits it to child query.

Child query gets executed for each row

select ename,job,sal

 from emp E

 where sal < (Select avg(sal)

                        from emp

                         where job = E.job);

To see the first two salary values

select distinct sal

from emp E

where 2 >= (select count(*)

                      from emp

                       where  sal > E.sal)

order by sal desc

To see the fourth highest salary

select distinct sal

from emp E

where 4 =  (select count(distinct sal)

                      from emp

                       where  sal > E.sal)

Co related sub query involving two tables—

Drop table LFC;

create table LFC

(empno number,

 No_Of_Leaves number,

 from_date date,

 till_date date);

Insert into LFC

values(7369, 10, ‘8-aug-03’, ‘18-aug-04’);

Insert into LFC

values(7839, 20, ‘11-dec-04’, ‘31-Dec-04’);

Insert into LFC

values(7369, 7, ‘21-apr-05’, ‘26-apr-05’);

Insert into LFC

values(7902, 8, ‘5-may-04’, ‘10-may-04’);

Select * from LFC;

To see the employee names that have taken LFC benefit at least twice.

select e.empno, e.ename, e.job

from emp e

where 2 <= (select count(*)

                      from LFC

                       where empno = e.empno)

 

 

 

 

 

 

 

 

 

 

Correlated Update

Denormalize the emp table by adding dname column.

Then populate the dname column as per the dept table values for matching deptno.

Alter table emp add deptno varchar2(14)

Update emp e

Set deptno = (select dname

                       From dept d

                       Where e.deptno = d.deptno)

DRAWBACKS OF CORRELATED SUB QUEIRES

--To list the Sum of Salaries for departments comprising more than

--1/3 of the firm's annual salary.

select dname,sum(sal) as DEP_TOTAL

from emp,dept

where emp.deptno = dept.deptno

group by dname

having sum(sal)  >

                                (select sum(sal) * 1/3

                                  from emp,dept

                                  where emp.deptno  = dept.deptno);

-- Drawbacks of this mechanism are as follows --

-- 1) Each record of group from parent query is taken and compared with sub --query.

--2) In the sub query again the entire calculation is done.

--3)Due to this the performance overheads are increased.

ORACLE 9I  has a new concept of with clause in subqueries to solve this problem

--To list the Sum of Salaries for departments comprising more than

--1/3 of the firms's annual salary.

with summary as

(select dname,sum(sal) as  DTOTAL

from emp,dept

where emp.deptno = dept.deptno

 group by dname)

select dname, DTOTAL

from summary

where DTOTAL   >

                                    (select sum(DTOTAL) * 1/3

                                       from summary);

 -- Advantages of using with clause and  subquery                                

--1) From the parent query the summarized and grouped data is taken into

-- an temporary table called summary.

--2) Now the data from the summary table is compared with the sub query .

-- In the sub query also the same data from summary table is taken for --calculations.

--  So the child query gets executed only once and then it references to the parent query.

   -- Explanation pertaining to current example

-- The sum(sal) for each deptno is taken in the summary table

--Then sub query gets executed only once to see what is the value one third of the sum of all the

-- total salaries

-- So for the each record from summary table the sub query was executed only once!!!

-- It means that there are less performance overheads
 

Exists and Not Exists

Drop  table s;

Create table s(roll number, name varchar2(40));

Insert into s values(1,'A');

Insert into s values(2,'B');

Insert into s values(3,'C');

Insert into s values(4,'D');

Drop table r;

create table r(roll number,marks number);

 Insert into r values(2,90);

 Insert into r values(3,98);

To see records from s table for the students who have given exam –

select * from s

where  Exists

                       (select *

                        from r

                        where s.roll = r.roll);

To see records from s table for the students who not  have given exam –

select * from s

where Not Exists

                              (select *

                                from r

                                where s.roll = r.roll);