Apr 6, 2013

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);





OraApps Search

Custom Search

Search This Blog