Sub Queries

-------- Simple Sub Queries ----------------

SELECT Ename, Sal, Job

FROM Emp

WHERE Sal > (SELECT Sal

            FROM Emp

            WHERE Empno = 7566);

           

SELECT Empno, Ename, Hiredate, Sal

FROM Emp

WHERE Hiredate > (SELECT Hiredate

                  FROM Emp

                  WHERE Ename = 'TURNER')

ORDER BY Sal;

SELECT Ename, Sal, Job

FROM Emp

WHERE Job = (SELECT Job

             FROM Emp

             WHERE Ename = UPPER('smith'))

ORDER BY Sal;          

SELECT Empno, Ename, Sal, Job

FROM Emp

WHERE Deptno = (SELECT Deptno

                FROM Dept

                WHERE Dname = 'SALES');

               

SELECT Empno, Ename, Sal, Comm, Sal + NVL( Comm, 0 )

FROM Emp

WHERE Deptno = (SELECT Deptno

                FROM Dept

                WHERE Loc = 'DALLAS'); 

       

------------- SubQueries With Group Functions --------------

SELECT Ename, Job , Sal

FROM Emp

WHERE Sal = (SELECT MAX(Sal)

            FROM Emp);

           

SELECT Ename, Job, Sal

FROM Emp

WHERE Sal = (SELECT MIN(Sal)

            FROM Emp);

SELECT Ename, Job, Sal

FROM Emp

WHERE Sal > (SELECT AVG(Sal)

            FROM Emp);

SELECT Ename, Job, Sal

FROM Emp

WHERE Sal < (SELECT STDDEV(Sal)

            FROM Emp);