add

About Me

My photo
Oracle Apps - Techno Functional consultant

Wednesday, October 23

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 DISTINCTMGR
                                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
            GROUPBY Deptno);  

No comments: