sub query

• let us consider two query’s out query and inner query
• inner query will execute first and generates and out put
• out put of a inner query is taken as a input to the outer query
• with the help of this input outer query will executes and generates the final output or a result table
• therefore we can say outer query is completely dependent on inner query

why we need sub subquery’s

case one

• when ever un known condition present in condition we go for sub query’s

direct condition

---- write a code name and salary who are earning salary is 3000
 select ename, sal
 from emp
 where sal = 3000;
/*
ENAME             SAL
---------- ----------
SCOTT            3000
FORD             3000*/

indirect condition / unknown condition

---write a code to disply name, sal of employe who are earning salary greater than adams

S   SELECT ENAME, SAL
    FROM EMP
    WHERE SAL > (SELECT SAL
    FROM EMP 
    WHERE ENAME = 'ADAMS')
    ;

ENAME             SAL
---------- ----------
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
FORD             3000
MILLER           1300

Code for display details of the employs who are heired before james

-- SELECT *
 FROM EMP
 WHERE HIREDATE > (SELECT HIREDATE
 FROM EMP
 WHERE ENAME = 'JAMES')
 ;
 

Code for display who are working as manager and salary more than smit

Code to display details of employ who are sal is greater tha smith and less than king

code to display details of employs who are working as manager or sales man in the same department adams and earning salary greater than allam