SQl Sub Queries

DISPLAY ALL THE EMPLOYEES WHOSE DEPARTMENT NAMES ENDING 'S'

SELECT ENAME
FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO
                FROM DEPT   
                WHERE DNAME LIKE ‘%S’);

WRITE A QUERY TO DISPLAY THE EMPLOYEE NAMES WHO IS HAVING MAXIMUM SALARY IN DEPT NAME "ACCOUNTING"

SELECT ENAME
FROM EMP
WHERE SAL IN(SELECT MAX(SAL)
             FROM EMP 
             WHERE DEPTNO IN(SELECT DEPTNO
                             FROM DEPT
                             WHERE DNAME=’ACCOUNTING’);

WRITE A QUERY TO DISPLAY THE DEPT NAME WHO IS HAVING HIGHEST COMMISSION

SELECT DNAME
FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO       
                FROM EMP             
                WHERE COMM =(SELECT MAX(COMM)         
                             FROM EMP);

WRITE A QUERY TO DISPLAY THE EMPLOYEE NAMES WHOSE NAME AS 2ND CHARACTER SHOULD BE 'O'

SELECT ENAME
FROM EMP 
WHERE DEPTNO IN(SELECT DEPTNO             
                FROM DEPT                 
               WHERE DNAME LIKE ‘_O%’);

WRITE A . QUERY TO DISPLAY ALL THE EMPLOYEES WHO’S DEPT NUMBER IS SAME AS SCOTT.

SELECT ENAME
FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO               
                FROM EMP               
                WHERE ENAME=’SCOTT’);

WRITE A QUERY TO DISPLAY ALL THE EMPLOYEES IN 'OPERATIONS AND ACCOUNTING' DEPT.

SELECT ENAME
FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO           
                FROM DEPT                   
         v   WHERE DNAME IN(‘OPERATIONS’,’ACCOUNTING’));

LIST THE EMPLOYEES WHO HAS SALARY GREATER THAN MILLER

SELECT ENAME
FROM EMP
WHERE SAL>(SELECT SAL             
          FROM EMP             
          WHERE ENAME=’MILLER’);

LIST DEPARTMENT NAME HAVING ATLEAST 3 SALESMAN

SELECT DNAME 
FROM DEPT 
WHERE DEPTNO IN(SELECT DEPTNO                 
               FROM EMP                 
               WHERE JOB=’SALESMAN’               
               GROUP BY DEPTNO                 
               HAVING COUNT(*)>=3);

DISPLAY ALL EMPLOYESS WHO DO NOT HAVE ANY REPORTER

SELECT ENAME
FROM EMP
WHERE EMPNO NOT IN(SELECT MGR                     
                   FROM EMP                 
                   WHERE MGR IS NOT NULL);

LIST ALL THE EMPLOYEES WHO ARE REPORTING TO JONES MANAGER

SELECT ENAME 
FROM EMP
WHERE MGR=(SELECT MGR           
           FROM EMP             
           WHERE ENAME=’JONES’);

LIST EMPLOYEES FROM RESEARCH&ACCOUNTING HAVING AT LEAST 2 REPORTING.

SELECT ENAME
FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO                   
                FROM DEPT             
                WHERE DNAME IN(‘RESEARCH’,’ACCOUNTING’) AND EMPNO IN(SELECT MGR
                                                                     FROM EMP
                                                                     GROUP BY MGR
                                                                     HAVING COUNT(*)>=2));

WRITE A QUERY TO DISPLAY THE DEPARTMENT NAME OF THE EMPLOYEE WHOSE NAME DOES NOT STARTS WITH S AND SALARY BETWEEN 1500 TO 3000.

SELECT DNAME 
FROM DEPT 
WHERE DEPTNO IN(SELECT DEPTNO           
                FROM EMP                   
                WHERE ENAME NOT LIKE’S%’ AND SAL BETWEEN 1500 AND 3000);

DISPLAY LOCATION OF EMPLOYEE WHOSE SALARY IS MINIMUM SALARY BUT SALARY IS GREATER THAN 2000

SELECT LOC 
FROM DEPT 
WHERE DEPTNO IN(SELECT DEPTNO                   
                FROM EMP               
                WHERE SAL IN(SELECT MIN(SAL)             
                             FROM EMP             
                             WHERE SAL>2000));

DISPLAY THE LOCATION OF AN EMPLOYEE IN ACCOUNTING DEPARTMENT

SELECT LOC
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO               
                 FROM EMP                 
                 WHERE DEPTNO IN (SELECT DEPTNO               
                                  FROM DEPT         
                                  WHERE DNAME=’ACCOUNTING’);

DISPLAY THE DEPARTMENT LOCATION THAT IS HAVING GREATER THAN FOUR EMPLOYEES IN IT

SELECT LOC 
FROM DEPT 
WHERE DEPTNO IN(SELECT DEPTNO                 
                FROM EMP                 
               GROUP BY DEPTNO                     
           HAVING COUNT(*)>4);

WRITE A QUERY TO DISPLAY ALL THE EMPLOYEE WHOSE JOB NOT SAME AS ALLEN AND SALARY IS GREATER THAN MARTIN.

SELECT ENAME
FROM EMP
WHERE JOB NOT IN(SELECT JOB                 
                FROM EMP                   
                WHERE ENAME =’ALLEN’) AND SAL>(SELECT SAL      
                                               FROM EMP           
                                               WHERE  ENAME=’MARTIN’));

. DISPLAY ALL THE EMPLOYEES WHO IS HAVING LOCATION IS SAME AS ADAM'S MANAGER?

SELECT ENAME
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO             
                 FROM DEPT             
                 WHERE LOC IN (SELECT LOC                   
                               FROM DEPT                 
                               WHERE DEPTNO IN (SELECT DEPTNO       
                                                FROM EMP           
                                                WHERE EMPNO IN (SELECT MGR                                         
                                                                FROM EMP                                     
                                                                WHERE ENAME='ADAMS'))));

DISPLAY THE JOB, MANAGER NUMBER OF EMPLOYEES WHO IS WORKING FOR JONES?

SELECT JOB 
FROM EMP 
WHERE MGR=(SELECT EMPNO             
           FROM EMP             
           WHERE ENAME=’JONES’);

DISPLAY THE EMPLOYEE NAMES, HIGHER DATE, COMMISSION OF FORD'S MANAGER?

SELECT ENAME,HIREDATE,COMM 
FROM EMP WHERE EMPNO=(SELECT MGR               
                 FROM EMP                     
            WHERE ENAME=’FORD’);

DISPLAY THE NUMBER OF EMPLOYEES WHO ARE GETTING SALARY LESS THAN THE BLAKE'S MANAGER

SELECT COUNT(*)
FROM EMP
WHERE SAL<(SELECT SAL         
          FROM EMP         
          WHERE EMPNO=(SELECT MGR                 
                       FROM EMP             
                       WHERE ENAME=’BLAKE’));

LIST EMPLOYEES WHO LOCATED IN CHICAGO AND THEIR COMMISSION IS ZERO.

SELECT ENAME

FROM EMP

WHERE DEPTNO=(SELECT DEPTNO

              FROM DEPT

              WHERE LOC=’CHICAGO’) AND COMM =0;

LIST EMPLOYEES WHO WORK FOR SALES DEPARTMENT AND THEIR SALARY GREATER THAN AVERAGE SALARY OF THEIR DEPARTMENT

SELECT ENAME

FROM EMP

WHERE DEPTNO=(SELECT DEPTNO

              FROM DEPT

              WHERE DNAME=’SALES’) AND SAL>(SELECT AVG(SAL)

                                FROM EMP

WHERE DEPTNO IN (SELECT DEPTNO

                                FROM DEPT

                                WHERE DNAME=’SALES’))

LIST EMPLOYEES WHO ARE WORKING IN RESEARCH DEPARTMENT AND THEY ARE MANAGER

SELECT ENAME

FROM EMP

WHERE DEPTNO=(SELECT DEPTNO

              FROM DEPT

              WHERE DNAME='RESEARCH') AND JOB=’MANAGER’;

DISPLAY DEPARTMENT NAME OF THE EMPLOYEES WHO EARN COMMISSION.

SELECT DNAME

FROM DEPT

WHERE DEPTNO=(SELECT DEPTNO

              FROM EMP

              WHERE COMM IS NOT NULL);

DISPLAY DEPARTMENT NAME OF THE EMPLOYEE WHO EARN MAXIMUM SALARY AND HAVE NO REPORTING MANAGER

SELECT DNAME

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE SAL IN (SELECT MAX(SAL)

                              FROM EMP) AND MGR IS NULL);

DISPLAY EMPLOYEE DETAILS WHO ARE REPORTING TO BLAKE AND HAVE COMMISSION WITHOUT USING NULL OR NOT NULL

SELECT *

 FROM EMP

 WHERE MGR=(SELECT EMPNO

            FROM EMP

            WHERE ENAME='BLAKE') AND COMM >=0;

LIST ALL THE DEPT NAME AND LOC OF ALL THE SALESMAN MANAGER-MANAGER'S

SELECT DNAME,LOC

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE EMPNO IN(SELECT MGR

                               FROM EMP

                               WHERE EMPNO IN(SELECT MGR

                                              FROM EMP

                                              WHERE JOB='SALESMAN')));

LIST THE EMPLOYEE DEPT NAME AND LOC OF ALL THE EMPLOYEES WHO ARE CLERK ,REPORTING TO BLAKE AND SALARY IS LESSER THAN MARTIN SALARY

SELECT DNAME,LOC

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE JOB =’CLERK’) AND MGR IN(SELECT EMPNO

                                              FROM EMP

                                               WHERE ENAME =’BLAKE’) AND SAL<(SELECT SAL

                                                                              FROM EMP

                                                                              WHERE ENAME =’MARTIN’);

LIST THE EMPLOYEES WHO DOES NOT DIRECTLY REPORT TO PRESIDENT,HAVE COMMISSION AND SALARY MORE THAN MAX SALARY OF ALL THE CLERK WITHOUT USING NULL OR NOT NULL

SELECT ENAME

FROM EMP

WHERE MGR IN(SELECT EMPNO

             FROM EMP

             WHERE MGR IN(SELECT EMPNO

                          FROM EMP

                          WHERE JOB='PRESIDENT')) AND COMM >=0 AND SAL>(SELECT MAX(SAL)

                                                                        FROM EMP

                                                                        WHERE JOB='CLERK');

LIST THE EMPLOYEES WHO JOINED AFTER 2 YEARS OF FIRST EMPLOYEE OF THE COMPANY AND MORE THAN BLAKE SALARY

SELECT ENAME

FROM EMP

WHERE HIREDATE>(SELECT MIN(HIREDATE)+365*2

                FROM EMP) AND SAL>(SELECT SAL

                                   FROM EMP

                                   WHERE ENAME='BLAKE');

DISPLAY LOCATION OF ALL THE EMPLOYEES WHO REPORTING TO BLAKE

SELECT LOC

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE MGR IN(SELECT EMPNO

                             FROM EMP

                             WHERE ENAME='BLAKE'));

LIST ALL THE EMPLOYEES WHOSE JOB IS SAME AS JONES AND THEIR SALARY LESSER THAN SCOTT

SELECT ENAME

FROM EMP

WHERE JOB=(SELECT JOB

           FROM EMP

           WHERE ENAME='JONES') AND SAL<(SELECT SAL

                                         FROM EMP

                                         WHERE ENAME='SCOTT');

.DISPLAY ALL THE EMPLOYEES OF DEPARTMENT 30, 20 WITH THERE ANUAL SALARY AND HAVING AT LEAST 3 EMPLOYEES

SELECT ENAME,SAL*12 ANN_SAL

FROM EMP

WHERE DEPTNO IN (SELECT DEPTNO

                 FROM EMP

                 WHERE DEPTNO IN(30,20)

                 GROUP BY DEPTNO

                 HAVING COUNT(*)>=3);

DISPLAY ALL THE EMPLOYEES WHO ARE EARN LESS THAN ANY OF THE SALESMAN?

SELECT ENAME

FROM EMP

WHERE SAL < ANY (SELECT SAL

                 FROM EMP

                 WHERE JOB ='SALESMAN');

DISPLAY ALL THE EMPLOYEES WHO ARE JOINED BEFORE THE LAST PERSON?

SELECT ENAME

FROM EMP

WHERE HIREDATE<(SELECT MAX(HIREDATE)

                FROM EMP);

DISPLAY ALL THE EMPLOYEES WHO ARE EARNING MORE THAN ANY OF THE MANAGER

SELECT ENAME

FROM EMP

WHERE SAL > ANY(SELECT SAL

                FROM EMP

                WHERE JOB='MANAGER');

LIST EMPLOYEES WHO JOINED AFTER 4 YEARS OF 1ST EMPLOYEE OF THE COMPANY AND LESS THAN BLAKE SALARY.

SELECT ENAME

FROM EMP

WHERE HIREDATE>(SELECT MIN(HIREDATE)+365*4

                FROM EMP) AND SAL<(SELECT SAL

                                  FROM EMP

                                   WHERE ENAME='BLAKE');

DISPLAY THE DEPARTMENT INFORMATION OF EMPLOYEE WHO IS WORKING FOR NEW YORK LOCATION

SELECT *

FROM DEPT

WHERE DEPTNO IN (SELECT DEPTNO

                 FROM EMP

                 WHERE DEPTNO IN (SELECT DEPTNO

                                  FROM DEPT

                                  WHERE LOC IN ’NEW YORK’))

.DISPLAY LOCATION OF EMPLOYEES, WHOSE NAME DOESN'T START WITH A AND SALARY BETWEEN 1000 AND 3000.

SELECT LOC

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE ENAME NOT LIKE 'A%' AND SAL BETWEEN 1000 AND 3000);

.DISPLAY LOCATION OF EMPLOYEES, WHOSE NAME DOESN'T START WITH A AND SALARY BETWEEN 1000 AND 3000.

SELECT DNAME

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE MGR IN(SELECT EMPNO

                             FROM EMP

                             WHERE ENAME='BLAKE'));

DISPLAY MARTIN'S MANAGER'S MANAGER'S DEPARTMENT NAME AND LOCATION

SELECT DNAME,LOC

FROM DEPT

WHERE DEPTNO IN (SELECT DEPTNO

                 FROM EMP

                 WHERE EMPNO IN (SELECT MGR

                                 FROM EMP

                                 WHERE EMPNO IN(SELECT MGR

                                                FROM EMP

                                                WHERE ENAME='MARTIN')));

DISPLAY THE MANAGER NO,JOB AND DEPARTMENT NUMBER FOR THOSE WHO DON'T HAVE COMMISSION

SELECT MGR,JOB,DEPTNO

FROM EMP

WHERE COMM IS NULL;

DISPLAY THE EMPLOYEE DETAILS WITH THEIR ANNUAL SALARY WHO EARN MAXIMUM COMMISSION

SELECT EMP.*,SAL*12 ANN_SALS

FROM EMP

WHERE COMM IN (SELECT MAX(COMM)

               FROM EMP);

DISPLAY ALL THE EMPLOYEE WHOSE DEPARTMENT IS SALES AND WHO IS EARNING SOME COMMISSION (I.E COMMISSION IS NOT NULL OR ZERO)AND WHO IS HIRED BEFORE THE LAST PERSON HIRED

SELECT ENAME

FROM EMP

WHERE DEPTNO IN (SELECT DEPTNO

                 FROM DEPT

                 WHERE DNAME='SALES') AND COMM >=0 AND HIREDATE<(SELECT  MAX(HIREDATE)

                                                                 FROM EMP);

DISPLAY ALL THE DEPARTMENT NAMES FOR WARD'S MANAGER'S MANAGER

SELECT DNAME

FROM DEPT

WHERE DEPTNO IN (SELECT DEPTNO

                FROM EMP

                 WHERE EMPNO IN(SELECT MGR

                                FROM EMP

                                WHERE EMPNO IN(SELECT MGR

                                               FROM EMP

                                               WHERE ENAME='WARD')));

DISPLAY DEPARTMENT NAMES OF EMPLOYEE'S WHOSE SALARY IS GREATER THAN AVERAGE SALARY OF ALL THE CLERK'S

SELECT DNAME

FROM DEPT

WHERE DEPTNO IN (SELECT DEPTNO

                 FROM EMP

                 WHERE SAL>(SELECT AVG(SAL)

                            FROM EMP

                            WHERE JOB='CLERK'));

. DISPLAY THE LAST EMPLOYEE RECORD WITH 25% HIKE IN SALARY

SELECT EMP.*,SAL+SAL*25/100 HIKE

FROM EMP

WHERE HIREDATE IN (SELECT MAX(HIREDATE)

                   FROM EMP);

DISPLAY THE DEPARTMENT NUMBER WHO WORKING IN SALES DEPARTMENT AND THEY ARE MANAGER.

SELECT DEPTNO

FROM EMP

WHERE DEPTNO IN(SELECT DEPTNO

                FROM DEPT

                WHERE DNAME='SALES') AND JOB='MANAGER';

DISPLAY DEPARTMENT NAME OF THE EMPLOYEE WHO EARN MINMUM SALARY AND HAVE REPORTING MANAGER.

SELECT DNAME

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE SAL IN(SELECT MIN(SAL)

                             FROM EMP where MGR IS NOT NULL));

DISPLAY HIREDATE AND JOB OF ALL THE EMPLOYEES WORKING FOR SALES

SELECT HIREDATE,JOB

FROM EMP

WHERE DEPTNO IN(SELECT DEPTNO

                FROM DEPT

                WHERE DNAME='SALES');

DISPLAY LOCATION AND DNAME OF EMPLOYEE WHO WORKING AS PRESIDENT

SELECT LOC,DNAME

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE JOB='PRESIDENT');

DISPLAY THE DNAME OF EMPLOYEES WHOES SALARY IS MAXIMUM SALARY BUT LESSER THAN 3000

SELECT DNAME

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE SAL IN(SELECT MAX(SAL)

                             FROM EMP

                             WHERE SAL<3000));

DISPLAY THE DEPARTMENT NAME WHO ARE REPORTING TO ADAMS

SELECT DNAME

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE MGR IN(SELECT EMPNO

                             FROM EMP

                             WHERE ENAME='ADAMS'));

DISPLAY LAST EMPLOYEE RECORD ACCORDING TO EMPNO.

SELECT *

FROM EMP

WHERE EMPNO IN(SELECT MAX(EMPNO)

               FROM EMP);

DISPLAY ALL THE EMPLOYEE WHOSE SALARY IS GREATER THAN AVERAGE SALARY OF DEPARTMENT 30

SELECT ENAME

FROM EMP

WHERE SAL>(SELECT AVG(SAL)

           FROM EMP

           WHERE DEPTNO=30);

DISPLAY ALL THE EMPLOYEE WHOSE SALARY IS GREATER THAN AVERAGE SALARY OF DEPARTMENT 30

SELECT COUNT(*)

FROM EMP

WHERE DEPTNO IN(SELECT DEPTNO

                FROM DEPT

                WHERE DNAME='RESEARCH') AND SAL<(SELECT MAX(SAL)

                                                 FROM EMP

                                                 WHERE DEPTNO=10);

DISPLAY THE DNAME THAT ARE HAVING CLERK IN IT

SELECT DNAME

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE JOB='CLERK');

DISPLAY THE DEPARTMENT NAMES THAT ARE HAVING ATLEAST ONE L IN IT.

SELECT DNAME

FROM DEPT

WHERE DNAME LIKE '%L%';

DISPLAY ALL THE EMPLOYEES WHO ARE JOINED AFTER BLAKE

SELECT ENAME

FROM EMP

WHERE HIREDATE>(SELECT HIREDATE

                FROM EMP

                WHERE ENAME='BLAKE');

LIST THE DEPT NAME THAT ARE HAVING AT LEAST 3 EMPLOYEES BUT NOT MORE THAN 5 EMPLOYEES IN IT.

SELECT DNAME

FROM DEPT

WHERE DEPTNO IN (SELECT DEPTNO

                 FROM EMP

                 GROUP BY DEPTNO

                 HAVING COUNT(*)>=3 AND COUNT (*)>5);

DISPLAY THE LOCATION OF ALL EMPLOYEES WHOSE REPORTING MANAGER SALARY IS GREATER THAN 2000

SELECT LOC

FROM DEPT

WHERE DEPTNO IN (SELECT DEPTNO

                 FROM EMP

                 WHERE SAL >200 AND EMPNO IN (SELECT MGR

                                             FROM EMP

                                         WHERE MGR IS NOT NULL));

SELECT THE EMPLOYEES WHOSE DNAME IS HAVING AT LEAST TWO 'E' IN IT

SELECT ENAME

FROM EMP

WHERE DEPTNO IN(SELECT DEPTNO

                FROM DEPT

                WHERE DNAME LIKE '%E%E%');

.DISPLAY ENAME,SAL OF EMPLOYEES WHO ARE EARNING MORE THAN ANY OF THE ANALYST

SELECT ENAME,SAL

FROM EMP

WHERE SAL > ANY (SELECT SAL

                 FROM EMP

                 WHERE JOB='ANALYST');

SELECT ALL THE EMPLOYEES WHO ARE WORKING FOR CHICAGO

SELECT ENAME

FROM EMP

WHERE DEPTNO IN(SELECT DEPTNO

                FROM DEPT

                WHERE LOC='CHICAGO');

QUERY TO DISPLAY EMPLOYEE NAMES WHO IS HAVING MINIMUM SALARY IN DEPARTMENT RESEARCH.

SELECT ENAME

FROM EMP

WHERE SAL IN(SELECT MIN(SAL)

             FROM EMP

             WHERE DEPTNO IN(SELECT DEPTNO

                             FROM DEPT

                             WHERE DNAME='RESEARCH'));

LIST THE DEPARTMENT NAMES THAT ARE HAVING SALESMAN

SELECT DNAME

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE JOB='SALESMAN');

LIST THE DEPARTMENT NAMES THAT ARE HAVING AT LEAST 3 EMPLOYES IN IT

SELECT DNAME

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                GROUP BY DEPTNO

                HAVING COUNT(*)>=3);

LIST THE DEPARTMENT NAMES THAT ARE HAVING AT LEAST 3 EMPLOYES IN IT

SELECT ENAME

FROM EMP

WHERE DEPTNO IN (SELECT DEPTNO

                 FROM DEPT

                 WHERE DNAME='RESEARCH' AND DNAME='ACCOUNTING') AND EMPNO

                 IN (SELECT MGR

                 FROM EMP

                 GROUP BY MGR

                 HAVING COUNT(*)>=2);

WRITE A QUERY TO DISPLAY EMPLOYEE NAME, JOB,LOCATION OF ALL EMPLOYEES WHO ARE WORKING AS MANAGER AND WORKS AT CHICAGO

SELECT ENAME,JOB,LOC

FROM EMP

WHERE JOB IN ‘MANAGER’

AND DEPTNO IN (SELECT DEPTNO

               FROM DEPT

               WHERE LOC IN ‘CHICAGO’);

SELECT ENAME OF EMPLOYEE WHO EARNS 2ND MAX SALARY AND WORKS FOR LOCATION DALLAS

SELECT ENAME

FROM EMP

WHERE SAL<(SELECT MAX(SAL)

           FROM EMP

           WHERE SAL<(SELECT MAX(SAL)

                      FROM EMP

                      WHERE DEPTNO IN(SELECT DEPTNO

                                      FROM DEPT

                                      WHERE LOC='DALLAS')));

WRITE A QUERY TO DISPLAY THE EMPLOYEE INFORMATION WHO IS NOT TAKING COMMISSION AND JOINED COMPANY AFTER JULY 83

SELECT *

FROM EMP

WHERE COMM IS NULL AND HIREDATE>'31-JUL-83';

LIST EMPLOYEES FROM SALES AND RESEARCH DEPARTMENT HAVING ATLEAST 2 REPORTING EMPLOYEES

SELECT ENAME

FROM EMP

WHERE DEPTNO IN(SELECT DEPTNO

                FROM DEPT

                WHERE DNAME='SALES' AND DNAME='RESEARCH') AND EMPNO

                IN(SELECT MGR

                   FROM EMP

                   GROUP BY MGR

                   HAVING COUNT(*)>=2);

LIST EMPLOYEES WHO HAVE COMMISSION GREATER THAN MAXIMUM SALARY OF ALL THE SALESMAN AND WHO DO NOT REPORT TO KING DIRECTLY .

SELECT ENAME

FROM EMP

WHERE COMM>(SELECT MAX(SAL)

            FROM EMP

            WHERE JOB='SALESMAN') AND MGR IN(SELECT EMPNO

                                             FROM EMP

                                             WHERE MGR NOT IN (SELECT EMPNO

                                                              FROM EMP

                                                               WHERE ENAME='KING'))

DISPLAY THE LOCATION OF ALL THE DEAPRTMENTS WHICH HAVE EMPLOYEES JOINED IN THE YEAR 81

SELECT LOC

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE HIREDATE LIKE '%81');

DISPLAY THE LOCATION OF ALL THE DEAPRTMENTS WHICH HAVE EMPLOYEES JOINED IN THE YEAR 81

SELECT MIN(SAL),DEPTNO

FROM EMP

WHERE SAL<(SELECT AVG(SAL)

           FROM EMP)

           GROUP BY DEPTNO;

DISPLAY ALL THE EMPLOYEES WHO ARE REPORTING TO 'JONES'

SELECT ENAME

FROM EMP

WHERE MGR IN(SELECT EMPNO

             FROM EMP

             WHERE ENAME='JONES');

DISPLAY ALL THE EMPLOYEE INFORMATION WHO ARE LIVING IN A LOCATION WHICH IS HAVING AT LEAST 2 'O' IN IT

SELECT *

FROM EMP

WHERE DEPTNO IN(SELECT DEPTNO

                FROM DEPT

                WHERE LOC LIKE '%O%O%');

DISPLAY THE NAMES OF EMPLOYEE FROM DEPARTMENT NUMBER 10 WITH SALARY GREATER THAN THAT OF ALL EMPLOYEE WORKING IN OTHER DEPARTMENTS

SELECT ENAME

FROM EMP

WHERE DEPTNO=10 AND SAL> ALL (SELECT SAL

                              FROM EMP

                              WHERE DEPTNO<>10);

DISPLAY THE NAMES OF EMPLOYEES WHO EARN HIGHEST SALARY IN THEIR RESPECTIVE JOBS.

SELECT ENAME

FROM EMP

WHERE SAL IN (SELECT MAX(SAL)

              FROM EMP

              GROUP BY JOB );

DISPLAY THE EMPLOYEE NUMBER AND NAME OF EMPLOYEE WORKING AS CLERK AND EARNING HIGHEST SALARY AMONG CLERKS

SELECT ENAME,EMPNO

FROM EMP

WHERE JOB='CLERK' AND SAL IN(SELECT MAX(SAL)

                             FROM EMP

                             WHERE JOB='CLERK');

WRITE A QUERY TO FIND SMITH'S MANAGER'S MANAGER HIREDATE

SELECT HIREDATE

FROM EMP

WHERE EMPNO IN(SELECT MGR

               FROM EMP

               WHERE EMPNO IN(SELECT MGR

                              FROM EMP

                              WHERE ENAME='SMITH'));

LIST THE NUMBER OF EMPLOYEES WHOSE JOB IS SALESMAN WORKING FOR NEWYORK AND CHICAGO

SELECT COUNT(*)

FROM EMP

WHERE JOB='SALESMAN' AND DEPTNO IN(SELECT DEPTNO

                                   FROM DEPT

                                   WHERE LOC IN ('NEW YORK' ,'CHICAGO'));

LIST THE DEPARTMENT NAMES IN WHICH THE EMPLOYEES ARE HIRED BETWEEN 1ST OF JAN 1981 AND 31ST DEC 1982 WITH SALARY MORE THAN 1800

SELECT DNAME

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE HIREDATE BETWEEN '01-JAN-81' AND '31-DEC-82' AND

                SAL>1800);

DISPLAY LOCATION OF THE EMPLOYEE WHO EARN MAXIMUM SALARY AND HAVE NO REPORTING MANAGER

SELECT LOC

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE SAL IN(SELECT MAX(SAL)

                             FROM EMP

                             WHERE MGR IS NULL));

. LIST EMPLOYEES WHO WORKS FOR ACCOUNTING DEPARTMENT AND THEIR SALARY GREATER THAN AVERAGE SALARY OF THEIR DEPARTMENT

SELECT ENAME

FROM EMP

WHERE DEPTNO IN(SELECT DEPTNO

                FROM DEPT

                WHERE DNAME='ACCOUNTING') AND DEPTNO IN(SELECT DEPTNO

                                                        FROM EMP

                                                       WHERE SAL>(SELECT AVG(SAL)

                                                                   FROM EMP));

DISPLAY LOCATION OF THE EMPLOYEE WHO EARN COMMISSION

SELECT LOC

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE COMM IS NOT NULL);

LIST THE EMPLOYEES WHO DOES NOT DIRECTLY REPORT TO PRESIDENT,HAVE COMMISSION AND SALARY MORE THAN MAX SALARY OF ALL THE CLERK WITHOUT USING NULL OR NOT NULL

SELECT ENAME

FROM EMP

WHERE MGR IN(SELECT EMPNO

             FROM EMP

             WHERE MGR IN(SELECT EMPNO

                          FROM EMP

                          WHERE JOB='PRESIDENT')) AND COMM >=0 AND SAL>(SELECT MAX(SAL)

                                                                        FROM EMP

                                                                        WHERE JOB='CLERK');

.DISPLAY ALL THE EMPLOYEES WHOSE SALARY IS GREATER THAN AVG SAL OF DEPARTMENT 20

SELECT ENAME

FROM EMP

WHERE SAL>(SELECT AVG(SAL)

           FROM EMP

          WHERE DEPTNO=20);

LIST THE EMPLOYEE DEPTNAME AND LOC OF ALL THE EMPLOYEES WHO ARE CLERK ,REPORTING TO BLAKE AND SALARY IS LESSER THAN MARTIN SALARY

SELECT DNAME,LOC

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE JOB='CLERK' AND MGR IN(SELECT EMPNO

                                             FROM EMP

                                            WHERE ENAME='BLAKE' )AND SAL<(SELECT SAL

                                                                           FROM EMP

                                                                           WHERE ENAME='MARTIN'));

DISPLAY LOC AND DNAME WHOSE JOB IS MANAGER AND HAS SAL LESS THAN CLERK.

SELECT LOC,DNAME

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE JOB='MANAGER' AND SAL < (SELECT SAL

                                               FROM EMP

                                              WHERE JOB='CLERK'));

DISPLAY EMPLOYEES LOCATION WHO HAS SOME COMMISSION.

SELECT LOC

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE COMM IS NOT NULL);

DISPLAY EMPNO, ENAME, JOB, WHOSE JOB HAS 'E' IN IT AND DISPLAY EMPNO IN DESCENDING ORDER.

SELECT EMPNO,ENAME,JOB

FROM EMP

WHERE JOB LIKE '%E%'

ORDER BY EMPNO DESC;

DISPLAY DNAME, LOC,DEPTNO OF EMPLOYEES WHO HAS SAME REPORTING MANAGER???

SELECT DNAME,LOC,DEPTNO

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE EMPNO IN(SELECT MGR

                              FROM EMP

                               GROUP BY MGR

                              HAVING COUNT(*)>1));

DISPLAY AVG SALARY OF ALL EMPLOYEES WHOSE DEPT NAME IS ACCOUNTING???

SELECT AVG(SAL)

FROM EMP

WHERE DEPTNO IN(SELECT DEPTNO

                FROM DEPT

                WHERE DNAME='ACCOUNTING');

DISPLAY ALL EMPLOYEES DETAILS WHOSE HIREDATE IS IN YEAR 81???

SELECT *

FROM EMP

WHERE HIREDATE LIKE '%81';

DISPAY DETAILS OF SMITH AND EMPLOYEES WORKING AS PRESIDENT ALONG WITH HIKE OF 35% IN SALARY

SELECT EMP.*,SAL+SAL*35/100 HIKE

FROM EMP

WHERE ENAME='SMITH' OR JOB='PRESIDENT';

DISPLAY NUMBER OF EMPLOYEES WHOSE COMMISSION IS MORE THAN SALARY.

SELECT COUNT(*)

FROM EMP

WHERE COMM > ( SELECT SAL

FROM EMP);

LIST THE EMPLOYEES WHOSE DAILY SALARY IS GREATER THAN 1500 AND WHO ARE JOINED BEFORE 1982 ONLY

SELECT ENAME

FROM EMP

WHERE SAL/30 >1500 AND HIREDATE <'01-JAN-82';

DISPLAY NUMBER OF EMPLOYEES WHOSE COMMISSION IS MORE THAN SALARY

SELECT COUNT(*)

FROM EMP

WHERE COMM >(SELECT SAL

FROM EMP);

LIST THE EMPLOYEES WHOSE DAILY SALARY IS GREATER THAN 1500 AND WHO ARE JOINED BEFORE 1982 ONLY

SELECT ENAME

FROM EMP

WHERE SAL/30 >1500 AND HIREDATE <'01-JAN-82';

DISPLAY ALL THE EMPLOYEES WHOSE JOB SAME AS 'SMITH' AND DEPARTMENT SAME AS 'JONES' AND SALARY MORE THAN 'TURNER

SELECT ENAME

FROM EMP

WHERE JOB IN(SELECT JOB

             FROM EMP

            WHERE ENAME='SMITH') AND DEPTNO IN(SELECT DEPTNO

                                                FROM DEPT

                                                WHERE DEPTNO IN(SELECT DEPTNO

                                                                FROM EMP

                                                                WHERE ENAME='JONES')) AND SAL>(SELECT SAL

                                                                                               FROM EMP

                                                                                               WHERE ENAME='TURNER');

DISPLAY ALL THE EMPLOYEES WHOSE NAME START WITH 'S' AND HAVING SALARY MORE THAN 'ALLEN' AND LESS THAN FORD

SELECT ENAME

FROM EMP

WHERE ENAME LIKE 'S%' AND SAL>(SELECT SAL

                               FROM EMP

                              WHERE ENAME='ALLEN') AND SAL<(SELECT SAL

                                                             FROM EMP

                                                             WHERE ENAME='FORD');

DISPLAY ALL THE CLERKS AND ANALYST WHO ARE NOT WORKING FOR 'DALLAS

SELECT JOB

FROM EMP

WHERE JOB IN ('CLERK','ANALYST') AND DEPTNO NOT IN(SELECT DEPTNO

                                                   FROM DEPT

                                                   WHERE LOC='DALLAS');

DISPLAY ALL THE CLERKS AND ANALYST WHO ARE NOT WORKING FOR 'DALLAS

SELECT DNAME

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE JOB='MANAGER'

                GROUP BY JOB

                HAVING COUNT(*)>=1);

DISPLAY MAXIMUM SALARY OF 'SALES' DEPARTMENT

SELECT MAX(SAL)

FROM EMP

WHERE DEPTNO IN(SELECT DEPTNO

                FROM DEPT

                WHERE DNAME='SALES');

DISPLAY THE 2ND MAXIMUM SALARY

SELECT MAX(SAL)

FROM EMP

WHERE SAL<(SELECT MAX(SAL)

           FROM EMP);

DISPLAY THE DEPT NAME OF THE EMP WHO GETS 3RD MAXIMUM SALARY

SELECT DNAME

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE SAL=(SELECT MAX(SAL)

                           FROM EMP

                          WHERE SAL<(SELECT MAX(SAL)

                                      FROM EMP

                                      WHERE SAL<(SELECT MAX(SAL)

                                                 FROM EMP))));

DISPLAY ALL THE EMPLOYEES WHO ARE EARNING MORE THAN ALL THE MANAGERS(JOB)

SELECT ENAME

FROM EMP

WHERE SAL> ALL (SELECT SAL

                FROM EMP

                WHERE JOB='MANAGER');

DISPLAY ALL THE EMPLOYEES WHO ARE EARNING MORE THAN ANY OF THE MANAGER(JOB)

SELECT ENAME

FROM EMP

WHERE SAL>ANY(SELECT SAL

              FROM EMP

              WHERE JOB='MANAGER');

SELECT EMPNO, JOB AND SALARY OF ALL THE ANALYST WHO ARE EARNING MORE THAN ANY OF THE MANAGER(JOB)

SELECT EMPNO,JOB,SAL

FROM EMP

WHERE JOB='ANALYST' AND SAL>ANY (SELECT SAL

                                 FROM EMP

                                 WHERE JOB='MANAGER');

SELECT THE DEPARTMENT NAME AND LOCATION OF ALL THE EMPLOYEES WORKING FOR CLARK

SELECT DNAME,LOC

FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO

                FROM EMP

                WHERE MGR IN(SELECT EMPNO

                             FROM EMP

                            WHERE ENAME='CLARK');

SELECT ALL THE EMPLOYEES WORKING FOR DALLAS

SELECT ENAME

FROM EMP

WHERE DEPTNO IN(SELECT DEPTNO

                FROM DEPT

                WHERE LOC='DALLAS');

DISPLAY ALL THE EMPLOYEES WHOSE SALARY IS GREATER THAN AVG SAL OF DEPARTMENT 20

SELECT ENAME

FROM EMP

WHERE SAL>(SELECT AVG(SAL)

           FROM EMP

          WHERE DEPTNO=20);

DISPLAY ALL THE EMPLOYEES WHO GETS MAXIMUM SALARY

SELECT ENAME

FROM EMP

WHERE SAL IN(SELECT MAX(SAL)

             FROM EMP);

DISPLAY FIRST EMPLOYEE RECORD BASED ON HIREDATE

SELECT *

FROM EMP

WHERE HIREDATE IN(SELECT MIN(HIREDATE)

                  FROM EMP);