Nested Sub-Queries

A query with in a Sub-Query is nothing but Nested Sub Query.

Syntax:-

SQL>Select ,  … from  where  (select  from where  (select  from where ));  
 
Example:-
Display the Manager of ‘TURNER’ and display the ‘MANAGER’ of ‘TURNER’ manager.
 
SQL> select *from EMP where empno in (select mgr from EMP where empno in (select mgr from EMP where ename ='TURNER'));
 

Query for display the total no.of employees and year wise total no.of employees joined in to company

select count(*),count(decode(to_char(hiredate,'yyyy'),'1981',ename))"1981"
                ,count(decode(to_char(hiredate,'yyyy'),'1982',empno)) "1982"
                ,count(decode(to_char(hiredate,'yyyy'),'1983',empno)) "1983" from jagan;

Query for display the first two highest salaries in dept wise

select  deptno,sal,ename from emp e
where 2>(select count(d.sal) from emp d where e.deptno=d.deptno and e.sal<d.sal) order by 1,3 desc