Subqueries in Oracle

------------- Multi Row Subqueries ---------------

SELECT Ename, Sal, Deptno

FROM Emp

WHERE Sal IN (Select MIN(Sal)

              FROM Emp

              GROUP BY Deptno);

SELECT Ename, Sal, Deptno

FROM Emp

WHERE Sal IN(SELECT MAX(Sal)

             FROM Emp

             GROUP BY Deptno);

             

SELECT Ename, Sal , Deptno , Job

From Emp

Where Sal IN(SELECT MAX(Sal)

            FROM Emp

            GROUP BY Job);

SELECT Empno, Ename, Job, Sal, Deptno

FROM Emp

WHERE Sal IN(Select Sal

                FROM Emp

                WHERE HireDate IN(SELECT HireDate

                                    FROM Emp

                                    WHERE Deptno = 20)

            );

           

SELECT Empno, Ename, Job

FROM Emp

WHERE Sal < ANY(SELECT Sal

                FROM Emp

                WHERE Job = 'CLERK');  

               

SELECT Empno, Ename, Job

FROM Emp

WHERE Sal < ANY(SELECT Sal

                FROM Emp

                WHERE Deptno = 20);

SELECT Empno, Ename, Job, Sal

FROM Emp

WHERE Sal < ANY(Select Sal

                FROM Emp

                WHERE Deptno = 20)

AND Job <>  'CLERK';

SELECT Empno, Ename, Job, Sal

FROM Emp

WHERE Sal < ANY(Select Sal

                FROM Emp

                WHERE MGR IN(SELECT DISTINCT MGR

                                FROM Emp)

                );

               

SELECT Empno, Ename, Job, Sal, Deptno

FROM Emp

WHERE Sal <ANY (Select Sal

                   FROM Emp

                   WHERE HireDate IN(SELECT HireDate

                                           FROM Emp

                                   WHERE Deptno = 20);     

SELECT Empno, Ename, Job

FROM Emp

WHERE Sal <ANY(SELECT Sal

                FROM Emp

                WHERE HireDate IN (SELECT HireDate

                                    FROM Emp

                                    WHERE HireDate LIKE '%-DEC-%'));

SELECT Empno, Ename, Job

FROM Emp

WHERE Sal >ANY(SELECT Sal

                FROM Emp

                WHERE Job = 'CLERK');

SELECT Empno, Ename, Job

FROM Emp

WHERE Sal >ANY(SELECT Sal

                FROM Emp

                WHERE Deptno = 10);

SELECT Empno, Ename, Job

FROM Emp

WHERE Sal >ANY(SELECT Sal

                FROM Emp

                WHERE Deptno = (SELECT Deptno

                                FROM Dept

                                WHERE Dname = 'ACCOUNTING'));

SELECT Empno, Ename, Job

FROM Emp

WHERE Sal >ANY(SELECT Sal

                FROM Emp

                WHERE Deptno IN (SELECT Deptno

                                    FROM Dept

                                    WHERE LOC IN('NEWYORK', 'DALLAS')));

SELECT Empno, Ename, Job

FROM Emp

WHERE  Sal = ANY(SELECT Sal

                FROM Emp

                WHERE Job = 'CLERK');

SELECT Empno, Ename, Job, Sal

FROM Emp

WHERE Sal >ALL(SELECT AVG(Sal)

                FROM Emp

                GROUP BY Deptno);

SELECT Empno, Ename, Job, Sal

FROM Emp

WHERE Sal >(SELECT MAX(AVG(Sal))

            FROM Emp

            GROUP BY Deptno);

SELECT Empno, Ename Job, Sal

FROM Emp

WHERE Sal <ALL(SELECT  AVG(Sal)

                FROM Emp

                GROUP BY Deptno);

SELECT Empno, Ename Job, Sal

FROM Emp

WHERE Sal <(SELECT  MIN(AVG(Sal))

            FROM Emp

            GROUP BY Deptno);