Sql Sub-Query Process

Pairwise Comparison Subquery

/* Formatted on 7/22/2014 11:34:41 AM (QP5 v5.115.810.9015) */
SELECT *
FROM emp
WHERE (job, deptno) = (SELECT job, deptno
                       FROM emp
                       WHERE ename = 'SMITH');


/* Formatted on 7/22/2014 11:34:46 AM (QP5 v5.115.810.9015) */
SELECT *
FROM emp
WHERE (job, mgr) IN (SELECT job, mgr
                     FROM emp
                     WHERE ename = 'ALLEN')


Non-Pairwise Comparison Subquery

/* Formatted on 7/22/2014 11:34:55 AM (QP5 v5.115.810.9015) */
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.

/* Formatted on 7/22/2014 11:35:08 AM (QP5 v5.115.810.9015) */
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

/* Formatted on 7/22/2014 11:35:25 AM (QP5 v5.115.810.9015) */
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

/* Formatted on 7/22/2014 11:35:32 AM (QP5 v5.115.810.9015) */
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

/* Formatted on 7/22/2014 11:35:36 AM (QP5 v5.115.810.9015) */
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

/* Formatted on 7/22/2014 11:35:41 AM (QP5 v5.115.810.9015) */
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;

/* Formatted on 7/22/2014 11:35:47 AM (QP5 v5.115.810.9015) */
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.

/* Formatted on 7/22/2014 11:35:58 AM (QP5 v5.115.810.9015) */
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)

/* Formatted on 7/22/2014 11:36:20 AM (QP5 v5.115.810.9015) */
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.


/* Formatted on 7/22/2014 11:36:31 AM (QP5 v5.115.810.9015) */
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.

/* Formatted on 7/22/2014 11:37:06 AM (QP5 v5.115.810.9015) */
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 –

/* Formatted on 7/22/2014 11:37:17 AM (QP5 v5.115.810.9015) */
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 –

/* Formatted on 7/22/2014 11:37:21 AM (QP5 v5.115.810.9015) */
SELECT *
FROM s
WHERE NOT EXISTS (SELECT *
                  FROM r
                  WHERE s.roll = r.roll);