SQL QUERIES

GROUP BY AND HAVING

1. WRITE A QUERY TO DISPLAY TOTAL SALARY NEEDED TO PAY

EACH JOB IN EMPLOYEE TABLE.

ANS:

SELECT SUM(SAL)

FROM EMP

GROUP BY JOB;

OUTPUT:

SUM(SAL)

----------

4150

5600

5000

8275

6000

2. WRITE A QUERY TO DISPLAY THE HIRE DATE ON WHICH AT

LEAST 3 EMPLOYEES WHERE HIRED.

ANS:

SELECT HIREDATE

FROM EMP

GROUP BY HIREDATE

HAVING COUNT (*)>=3;

OUTPUT:

no rows selected

3. WRITE A QUERY TO DISPLAY THE DEPARTMENT NUMBER WHICH

HAS MORE THAN 2 EMPLOYEES AND THE TOTAL AMOUNT

REQUIRED TO PAY THE MONTHLY SALARIES OF ALL THE

EMPLOYEES IN THAT DEPARTMENT SHOULD BE MORE THAN 9000.

ANS:
SELECT DEPTNO, SUM(SAL)

FROM EMP

GROUP BY DEPTNO

HAVING COUNT (*) >2 AND SUM(SAL)>9000;

OUTPUT:

DEPTNO SUM(SAL)

------------- ----------

30 9400

20 10875

4. WRITE A QUERY TO DISPLAY NUMBER OF EMPLOYEES WORKING

IN EACH DEPARTMENT AND ITS’ AVERAGE SALARY BY EXCLUDING

ALL THE EMPLOYEES WHOSE SALARY IS LESS THAN THEIR

COMMISSION.

ANS:

SELECT COUNT (*), AVG(SAL)

FROM EMP

WHERE SAL>COMM

GROUP BY DEPTNO;

OUTPUT:

COUNT (*) AVG(SAL)

------------- ----------

3 1450

5. WRITE A QUERY TO DISPLAY THE SALARIES WHICH HAS

REPETITIONS IN THE SAL COLUMN OF EMPLOYEE TABLE.

ANS:

SELECT SAL

FROM EMP

GROUP BY SAL

HAVING COUNT(SAL)>1;

OR
 SELECT SAL

FROM EMP

GROUP BY SAL

HAVING COUNT(*)>1;

OUTPUT:

SAL

------

1250

3000

6. WRITE A QUERY TO DISPLAY THE EMPLOYEE NAME ONLY IF

MORE THAN ONE PERSON IN THE EMPLOYEES OF THE COMPANY

HAS SAME NAME.

ANS:

SELECT ENAME

FROM EMP

GROUP BY ENAME

HAVING COUNT (*)>1;

OUTPUT:

no rows selected

7. WRITE A QUERY TO DISPLAY THE DEPARTMENT NUMBER

WHOSE AVERAGE SALARY IS BETWEEN 2500 AND 3000.

ANS:

SELECT DEPTNO

FROM EMP

GROUP BY DEPTNO

HAVING AVG(SAL) BETWEEN 2500 AND 3000;

OUTPUT:

DEPTNO

-----------

10

8. WRITE A QUERY TO DISPLAY THE NUMBER OF EMPLOYEES ONLY

IF THEY ARE WORKING AS MANAGER OR ANALYST AND THEIR

ANNUAL SAL SHOULD END WITH A ZERO, IN EACH DEPARTMENT.

ANS:

SELECT COUNT (*)

FROM EMP

WHERE JOB IN ('MANAGER','ANALYST') AND SAL*12 LIKE '%0'

GROUP BY DEPTNO;

OUTPUT:

COUNT (*)

------------------

9. WRITE A QUERY TO DISPLAY NO OF CLERKS WORKING IN EACH

DEPARTMENT.

ANS:

SELECT COUNT (*)

FROM EMP

WHERE JOB='CLERK'

GROUP BY DEPTNO;

OUTPUT:

COUNT (*)

---------

10. WRITE A QUERY TO DISPLAY HIGHEST SALARY GIVEN TO A

MANAGER IN EACH DEPARTMENT.

ANS:

SELECT MAX(SAL)
FROM EMP

WHERE JOB='MANAGER'

GROUP BY DEPTNO;

OUTPUT:

MAX(SAL)

---------

2850

2975

2450

11. WRITE A QUERY TO DISPLAY NO OF TIMES THE SALARIES HAVE

REPEATED IN THE EMP TABLE.

ANS:

SELECT COUNT (*)

FROM EMP

GROUP BY SAL;

OUTPUT:

COUNT (*)

------------

1
12.WRITE A QUERY TO DISPLAY DEPTNO AND MUNBER OF

EMPLOYEES WHORKING IN EACH DEPARTMENT EXCEPT FOR

THOSE WORKING IN DEPT 10

SELECT DEPTNO, COUNT (*)

FROM EMP

WHERE DEPTNO! =10

GROUP BY DEPTNO;

DEPTNO COUNT (*)

------------ ----------

30 6

20 5

13.WAQTD NUMBER OF EMPLOYEES GETTING COMISSION IN EACH

DEPARTMENT

SELECT COUNT(*)

FROM EMP

WHERE COMM IS NOT NULL

GROUP BY DEPTNO;

COUNT (*)

---------

14.WAQTD NUMBER OF EMPLOYEES GETTING SALARY MORE

THAN 1600 EXCLUDING ALL THE MANAGERS IN EACH

DEPARTEMNT

ANS:

SELECT COUNT(*)

FROM EMP

WHERE SAL>1600 AND JOB != 'MANAGER'

GROUP BY DEPTNO;

OUTPUT:
COUNT(*)

---------

15.WAQTD AVERAGE SALARY NEEDED TO PAY ALL THE

EMPLOYEES WHO ARE HAVING A REPORTING MANAGER IN EACH

JOB .

ANS:

SELECT AVG(SAL)

FROM EMP

WHERE MGR IS NOT NULL

GROUP BY JOB;

OUTPUT:

AVG(SAL)

----------

1037.5

1400

2758.33333

3000

16.WAQTD NUMBER OF EMPLOYEES HIRED INTO THE SAME

DEPARTMENT ON THE SAME DAY

ANS:

SELECT COUNT(*)

FROM EMP

GROUP BY HIREDATE,DEPTNO

HAVING COUNT(*)>1;

no rows selected

17.WAQTD NUMBER OF EMPLOYEES GETTING THE SAME SALARY ,

WORKING IN THE SAME DEPARTMENT

SQL> SELECT COUNT(*)FROM EMP

2 GROUP BY DEPTNO,SAL

3 HAVING COUNT(*)>1;

COUNT(*)

----------

18.WAQTD MAXIMUM SALARY GIVEN IN EACH DESIGNATION

EXCLUDING THOSE WHOS NAME STARTS WITH ‘K’

ANS:

SQL> SELECT MAX(SAL)

2 FROM EMP

3 WHERE ENAME NOT LIKE 'K%'

4 GROUP BY JOB;

MAX(SAL)

----------

1300

1600

2975

3000

19.WAQTD NUMBER OF EMPLOYEES REPORTING TO 7839 IN EACH

DEPT

ANS:

SQL> SELECT COUNT(*)

2 FROM EMP

3 WHERE MGR=7839

4 GROUP BY DEPTNO;
 COUNT(*)

----------

20.WAQTD NUMBER OF EMPLOYEE NAMES STARTING WITH AN

VOWEL IN EACH DEPARTMENT

ANS:

SQL> SELECT COUNT(*)

2 FROM EMP

3 WHERE ENAME LIKE 'A%' OR ENAME LIKE 'E%' OR ENAME LIKE 'I%' OR ENAME LIKE 'O%' OR
ENAME LIKE 'U%';

COUNT(*)

----------

subquery
1.DISPLAY ALL THE EMPLOYEES WHOSE DEPARTMET NAMES

ENDING 'S'

ANS:

SQL> SELECT ENAME

2 FROM EMP,DEPT

3 WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME LIKE '%S';

ENAME

----------

ALLEN

WARD

MARTIN
BLAKE

TURNER

JAMES

2.QUERY TO DISPLAY THE EMPLOYEE NAMES WHO IS HAVING

MAXIMUM SALARY IN DEPT NAME "ACCOUNTING"

ANS:

SQL> SELECT ENAME

2 FROM EMP

3 ,DEPT

4 WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME='ACCOUNTING' AND SAL IN (SELECT

MAX(SAL)

5 FROM EMP);

ENAME

----------

KING

3.QUERY TO DISPLAY THE DEPT NAME WHO IS HAVING HIGHEST

COMMISSION

ANS:

SQL> SELECT DNAME

2 FROM EMP,DEPT

3 WHERE EMP.DEPTNO=DEPT.DEPTNO AND COMM IN (SELECT MAX(COMM)

4 FROM EMP);

DNAME

--------------

SALES

4. QUERY TO DISPLAY THE EMPLOYEE NAMES WHOSE

DEPARTMENT NAME HAS 2ND CHARACTER AS 'O'.

SELECT ENAME FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT
WHERE DNAME LIKE '_O%');
OUTPUT:
NO ROWS SELECTED

5. 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');
OUTPUT:
ENAME
-------
FORD
ADAMS
SCOTT
JONES
SMITH

6.QUERY TO DISPLAY ALL THE EMPLOYEES IN 'OPERATIONS AND

ACCOUNTING' DEPT.
SELECT ENAME FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT
WHERE DNAME IN 'OPERATIONS' AND DNAME IN 'ACCOUNTING');
OUTPUT:
NO ROWS SELECTED

7.LIST THE EMPLOYEES WHO HAS SALARY GREATER THAN

MILLER
SELECT ENAME FROM EMP
WHERE SAL>(SELECT SAL FROM EMP
WHERE ENAME='MILLER');
OUTPUT:
ENAME
-------
ALLEN
JONES
BLAKE
CLARK
SCOTT
KING
TURNER
FORD

8. LIST DEPARTMENT NAME HAVING ATLEAST 3 SALESMAN

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

OUTPUT:
DNAME
------------
SALES

9. DISPLAY THE DNAME OF AN EMPLOYEES WHO HAS NO

REPORTING MANAGER.
SELECT DNAME FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO FROM EMP
WHERE MGR IS NULL);
OUTPUT:
DNAME
-------------
ACCOUNTING

10. LIST ALL THE EMPLOYEES WHO ARE REPORTING TO JONES

MANAGER
SELECT ENAME FROM EMP
WHERE MGR=(SELECT EMPNO FROM EMP
WHERE EMPNO=(SELECT MGR FROM EMP
WHERE ENAME='JONES'));
OUTPUT:
ENAME
-------
JONES
BLAKE
CLARK

11. LIST EMPLOYEES FROM RESEARCH&ACCOUNTING HAVING

ATLEAST 2 REPORTING.

12. DISPLAY THE DEPARTNAME 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 DNAME NOT LIKE 'S%'
AND SAL IN(SELECT SAL FROM EMP
GROUP BY SAL
HAVING SAL BETWEEN 1500 AND 3000));
OUTPUT:
DNAME
-----------
ACCOUNTING
RESEARCH
13.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));
OUTPUT:
LOC
-------------
NEW YORK

14. DISPLAY THE LOCATION OF AN EMPLOYEE IN ACCOUNTING

DEPARTMENT.
SELECT LOC FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT
WHERE DNAME='ACCOUNTING');
OUTPUT:
LOC
-------------
NEW YORK

15. DISPLAY THE DEPARTMENT ‘S LOCATION THAT IS HAVING

GREATER THAN FOUR EMPLOYEES IN IT.

SQL> SELECT DNAME,LOC

2 FROM DEPT

3 WHERE DEPTNO IN (SELECT DEPTNO

4 FROM EMP

5 GROUP BY DEPTNO

6 HAVING COUNT(*)>4);

DNAME LOC
-------------- -------------

SALES CHICAGO

RESEARCH DALLAS

16. WRITE A QUERY TO DISPLAY ALL THE EMPLOYEE WHOSE JOB

NOT SAME AS ALLEN AND SALARY IS GREATER THAN MARTIN.

SQL> SELECT ENAME

2 FROM EMP

3 WHERE JOB NOT IN (SELECT JOB FROM EMP

4 WHERE ENAME='ALLEN')AND SAL>(SELECT SAL

5 FROM EMP

6 WHERE ENAME='MARTIN');

ENAME

----------

JONES

BLAKE

CLARK

SCOTT

KING

FORD

MILLER

7 rows selected.

17. 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 EMPNO=(SELECT MGR FROM EMP
WHERE ENAME='ADAMS')));
OUTPUT:
SCOTT

18. DISPLAY THE JOB, MANAGER NUMBER OF EMPLOYEES WHO IS

WORKING FOR JONES?

SELECT ENAME,JOB,MGR FROM EMP
WHERE MGR=(SELECT EMPNO FROM EMP
WHERE ENAME='JONES');
OUTPUT:
ENAME JOB MGR
---------- --------- ----------
SCOTT ANALYST 7566
FORD ANALYST 7566

19. 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');
OUTPUT:
ENAME HIREDATE COMM
---------- --------- ----------
JONES 02-APR-81
20. DISPLAY THE NUMBER OF EMPLOYEES WHO ARE GETTING

SALARY LESS THAN THE BLAKE'S MANAGER

SELECT ENAME FROM EMP
WHERE SAL<(SELECT SAL FROM EMP
WHERE EMPNO=(SELECT MGR FROM EMP
WHERE ENAME='BLAKE'));
OUTPUT:
ENAME
---------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
TURNER
ADAMS
JAMES
FORD
MILLER

21. LIST EMPLOYEES WHO LOCATED IN CHICAGO AND THEIR

COMMISSION IS ZERO.
SELECT LOC FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO FROM EMP
WHERE LOC='CHICAGO' AND COMM=0);
OUTPUT:
LOC
-------------
CHICAGO

22. LIST EMPLOYEES WHO WORK FOR SALES DEPARTMENT AND

THEIR SALARY GREATER THAN AVERAGE SALARY OF THEIR

DEPARTMENT.
SELECT ENAME FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT
WHERE DNAME='SALES' AND
SAL>(SELECT AVG(SAL) FROM EMP));
OUTPUT:
BLAKE

23. LIST EMPLOYEES WHO ARE WORKING IN RESEARCH

DEPARTMENT AND THEY ARE MANAGER.

SELECT ENAME FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT
WHERE DNAME IN(SELECT DNAME FROM EMP
WHERE DNAME IN 'RESEARCH' AND
JOB='MANAGER'));
OUTPUT:
ENAME
--------
FORD
ADAMS
SCOTT
JONES
SMITH

24. DISPLAY DEPARTMENT NAME OF THE EMPLOYEES WHO EARN

COMMISSION.
SELECT DNAME FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO FROM EMP
WHERE COMM IS NOT NULL);
OUTPUT:
SALES

25. DISPLAY DEPARTMENT NAME OF THE EMPLOYEE WHO EARN

MAXIMUM SALARY AND HAVE NO REPORTING MANAGER.

SQL> SELECT *
2 FROM EMP
 3 WHERE MGR IN (SELECT EMPNO
4 FROM EMP
5 WHERE ENAME='BLAKE' AND COMM IN (SELECT COMM
6 FROM EMP
7 GROUP BY COMM
8 HAVING COUNT(*)>0));

no rows selected
26. DISPLAY EMPLOYEE DETAILS WHO ARE REPORTING TO BLAKE

AND HAVE COMMISSION WITHOUT USING NULL OR NOT NULL

SQL> SELECT EMP.*

2 FROM EMP

3 WHERE MGR IN (SELECT EMPNO FROM EMP

4 WHERE ENAME='BLAKE') AND COMM !=0;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

27. LIST ALL THE DEPTNAME 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 IN 'SALESMAN')));
OUTPUT:
DNAME LOC
-------------- -----------
ACCOUNTING NEW YORK

28. 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 IN 'CLERK' AND MGR IN(SELECT EMPNO FROM EMP
WHERE ENAME IN 'BLAKE' AND SAL<(SELECT SAL FROM EMP WHERE
ENAME IN 'MARTIN')));
OUTPUT:
NO ROWS SELECTED

29. 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

SQL> SELECT *

2 FROM EMP

3 WHERE MGR IN(SELECT EMPNO FROM EMP

4 WHERE MGR IN(SELECT EMPNO FROM EMP

5 WHERE JOB='PRESIDENT')) AND COMM!=0 AND SAL >ALL(SELECT SAL

6 FROM EMP

7 WHERE JOB='CLERK');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

30. LIST THE EMPLOYEES WHO JOINED AFTER 2 YEARS OF FIRST

EMPLOYEE OF THE COMPANY AND MORE THAN BLAKE SALARY

SQL> SELECT ENAME,HIREDATE,SAL

 2 FROM EMP

3 WHERE HIREDATE >( SELECT MIN(HIREDATE) + INTERVAL '2' YEAR AS DATE_AFTER_2_YEARS

4 FROM EMP) AND SAL> (SELECT SAL

5 FROM EMP

6 WHERE ENAME='BLAKE');

ENAME HIREDATE SAL

---------- --------- ----------

SCOTT 19-APR-87 3000

31. DISPLAY LOCATION OF ALL THE EMPLOYEES WHO

REPORTING TO BLAKE

SELECT LOC FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO FROM EMP
WHERE MGR=(SELECT EMPNO FROM EMP
WHERE ENAME='BLAKE'));
LOC
-----------
CHICAGO

32. LIST ALL THE EMPLOYEES WHOSE JOB IS SAME AS JONES AND

THEIR SALARY LESSER THAN SCOTT

SQL> SELECT *

2 FROM EMP

3 WHERE JOB IN(SELECT JOB

4 FROM EMP

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

6 FROM EMP

7 WHERE ENAME='SCOTT');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

33.DISPLAY ALL THE EMPLOYEES OF DEPARTMENT 30, 20 WITH

THERE ANUAL SALARY AND HAVING ATLEAST 3 EMPLOYEES

SQL> SELECT EMP.*,SAL*12 ANNUAL_SAL

2 FROM EMP

3 WHERE DEPTNO IN (SELECT DEPTNO

4 FROM EMP

5 WHERE DEPTNO IN (30,20)

6 GROUP BY DEPTNO

7 HAVING COUNT(*)>=3);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ANNUAL_SAL

---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------

7900 JAMES CLERK 7698 03-DEC-81 950 30 11400

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 18000

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 34200

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 15000

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 15000

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 19200

7902 FORD ANALYST 7566 03-DEC-81 3000 20 36000

7876 ADAMS CLERK 7788 23-MAY-87 1100 20 13200

7788 SCOTT ANALYST 7566 19-APR-87 3000 20 36000

7566 JONES MANAGER 7839 02-APR-81 2975 20 35700

7369 SMITH CLERK 7902 17-DEC-80 800 20 9600

11 rows selected.

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

SQL> SELECT SAL,ENAME

2 FROM EMP

3 WHERE SAL<ANY(SELECT SAL

4 FROM EMP

5 WHERE JOB='SALESMAN');

SAL ENAME

---------- ----------

800 SMITH

950 JAMES

1100 ADAMS

1250 WARD

1250 MARTIN

1300 MILLER

1500 TURNER

7 rows selected.

35.DISPLAY ALL THE EMPLOYEES WHO ARE

JOINED BEFORE THE LAST PERSON?

SQL> SELECT * FROM EMP

2 WHERE HIREDATE < (SELECT MAX(HIREDATE)

3 FROM EMP);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

 7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

13 rows selected.

35.FIND 3RD MINIMUM SALARY IN THE EMPLOYEE TABLE.

SQL> SELECT MIN(SAL) FROM EMP

2 WHERE SAL>(SELECT MIN(SAL) FROM EMP

3 WHERE SAL>(SELECT MIN(SAL) FROM EMP));

MIN(SAL)

----------

1100

36.DISPLAY ALL THE EMPLOYEES WHO ARE EARNING MORE

THAN ANY OF THE MANAGER.

SQL> SELECT * FROM EMP

2 WHERE SAL>ANY(SELECT SAL

3 FROM EMP

4 WHERE JOB='MANAGER');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

 7839 KING PRESIDENT 17-NOV-81 5000 10

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7566 JONES MANAGER 7839 02-APR-81 2975 20

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

37.LIST EMPLOYEES WHO JOINED AFTER 4 YEARS OF 1ST

EMPLOYEE OF THE COMPANY AND LESS THAN BLAKE SALARY.

SQL> SELECT *

2 FROM EMP

3 WHERE HIREDATE >(SELECT MIN(HIREDATE)+INTERVAL'4' YEAR AS AFTER_4_YEARS

4 FROM EMP) AND SAL <(SELECT SAL

5 FROM EMP

6 WHERE ENAME='BLAKE');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

38. DISPLAY THE DEPARTMENT INFORMATION OF EMPLOYEE

WHO IS WORKING FOR NEW YORK LOCATION

SQL> SELECT *

2 FROM DEPT

3 WHERE LOC='NEW YORK';

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

39. DISPLAY LOCATION OF EMPLOYEES, WHOSE NAME DOESN'T

START WITH A AND SALARY BETWEEN 1000 AND 3000.

SQL> SELECT LOC

2 FROM EMP JOIN DEPT

 3 ON DEPT.DEPTNO=EMP.DEPTNO

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

LOC

-------------

CHICAGO

DALLAS

40. DISPLAY DEPARTMENT NAME OF ALL THE EMPLOYEES WHO

ARE REPORTING TO BLAKE.

SQL> SELECT LOC

2 FROM EMP JOIN DEPT

3 ON DEPT.DEPTNO=EMP.DEPTNO

4 WHERE MGR IN (SELECT EMPNO FROM EMP

5 WHERE ENAME='BLAKE');

LOC

-------------

CHICAGO

CHICAGO

CHICAGO

CHICAGO

CHICAGO

41. DISPLAY MARTIN'S MANAGER'S MANAGER'S DEPARTMENT

NAME AND LOCATION.

SQL> SELECT LOC,DNAME

2 FROM EMP JOIN DEPT

3 ON DEPT.DEPTNO=EMP.DEPTNO

4 WHERE EMPNO IN(SELECT MGR

5 FROM EMP

6 WHERE EMPNO IN (SELECT MGR

 7 FROM EMP

8 WHERE ENAME='MARTIN'));

LOC DNAME

------------- --------------

NEW YORK ACCOUNTING

42.DISPLAY THE MANAGER NUMBER,JOB AND DEPARTMENT

NUMBER FOR THOSE WHO DON'T HAVE COMMISSION IN THE

LOCATION CHICAGO OR DALLAS

SQL> SELECT MGR,JOB,EMP.DEPTNO

2 FROM EMP JOIN DEPT

3 ON DEPT.DEPTNO=EMP.DEPTNO

4 WHERE COMM IS NULL AND LOC IN ('CHICAGO','DALLAS');

MGR JOB DEPTNO

---------- --------- ----------

7902 CLERK 20

7839 MANAGER 20

7839 MANAGER 30

7566 ANALYST 20

7788 CLERK 20

7698 CLERK 30

7566 ANALYST 20

7 rows selected.

43.DISPLAY THE EMPLOYEE DETAILS WITH THEIR ANNUAL

SALARY WHO EARN MAXIMUM COMMISSION

SQL> SELECT EMP.*,SAL*12 ANNUAL_SAL

2 FROM EMP

3 WHERE COMM IN (SELECT MAX(COMM) FROM EMP);

 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ANNUAL_SAL

---------- ---------- --------- --------- --------- ---------- ---------- ---------- ----------

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 15000

44.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.

SQL> SELECT DNAME,COMM,HIREDATE

2 FROM DEPT JOIN EMP

3 ON DEPT.DEPTNO=EMP.DEPTNO

4 WHERE DNAME='SALES' AND COMM IS NOT NULL AND HIREDATE<(SELECT MAX(HIREDATE)

5 FROM EMP);

DNAME COMM HIREDATE

-------------- ---------- ---------

SALES 300 20-FEB-81

SALES 500 22-FEB-81

SALES 1400 28-SEP-81

SALES 0 08-SEP-81

45.DISPLAY ALL THE DEPARTMENT NAMES FOR WARD'S

MANAGER'S MANAGER

SQL> SELECT DNAME

2 FROM DEPT

3 WHERE DEPTNO IN (SELECT DEPTNO

4 FROM EMP

5 WHERE EMPNO IN (SELECT MGR

6 FROM EMP WHERE EMPNO IN (SELECT MGR

7 FROM EMP

8 WHERE ENAME='WARD')));
DNAME

--------------

ACCOUNTING

46:DISPLAY DEPARTMENT NAMES OF EMPLOYEE'S WHOSE

SALARY IS GREATER THAN AVERAGE SALARY OF ALL THE

CLERK'S

SQL> SELECT DNAME

2 FROM DEPT

3 WHERE DEPTNO IN (SELECT DEPTNO

4 FROM EMP

5 WHERE SAL> (SELECT AVG(SAL)

6 FROM EMP

7 WHERE JOB='CLERK'));

DNAME

--------------

SALES

RESEARCH

ACCOUNTING

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

SALARY.

SQL> SELECT EMP.*,SAL+(0.25*SAL)

2 FROM EMP

3 WHERE EMPNO IN(SELECT MAX(EMPNO) FROM EMP);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SAL+(0.25*SAL)

---------- ---------- --------- ---------- --------- ---------- ---------- ---------- --------------

7934 MILLER CLERK 7782 23-JAN-82 1300 10 1625

48.DISPLAY THE DEPARTMENT NUMBER WHO WORKING IN SALES

DEPARTMENT AND THEY ARE MANAGER.

SQL> SELECT DNAME

2 FROM DEPT

3 WHERE DEPTNO IN(SELECT DEPTNO

4 FROM EMP

5 WHERE SAL IN (SELECT MIN(SAL) FROM EMP

6 ) AND MGR IS NOT NULL);

DNAME

--------------

RESEARCH

49.DISPLAY DEPARTMENT NAME OF THE EMPLOYEE WHO EARN

MINMUM SALARY AND HAVE REPORTING MANAGER.

SQL> SELECT DNAME

2 FROM DEPT

3 WHERE DEPTNO IN(SELECT DEPTNO

4 FROM EMP

5 WHERE SAL IN (SELECT MIN(SAL) FROM EMP

6 ) AND MGR IS NOT NULL);

DNAME

--------------

RESEARCH

50.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');
OUTPUT:
HIREDATE JOB
--------- --------
20-FEB-81 SALESMAN
22-FEB-81 SALESMAN
28-SEP-81 SALESMAN
01-MAY-81 MANAGER
08-SEP-81 SALESMAN
03-DEC-81 CLERK

51.DISPLAY LOCATION AND DNAME OF EMPLOYEE WHO

WORKING AS PRESIDENT

SELECT DNAME,LOC FROM DEPT

WHERE DEPTNO IN(SELECT DEPTNO FROM EMP
WHERE JOB='PRESIDENT');
OUTPUT:
DNAME LOC
-------------- -----------
ACCOUNTING NEW YORK

52.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));
OUTPUT:
DNAME
------------
ACCOUNTING

53.DISPLAY THE DEPARTMENT NAME WHO ARE REPORTING TO

ADAMS.
SELECT DNAME FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO FROM EMP
WHERE MGR=(SELECT EMPNO FROM EMP
WHERE ENAME='ADAMS'));
OUTPUT:
NO ROW SELECTED

54.DISPLAY LAST EMPLOYEE RECORD ACCORDING TO EMPNO.

SQL> SELECT EMP.*

2 FROM EMP

3 WHERE EMPNO IN(SELECT MAX(EMPNO)

4 FROM EMP);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7934 MILLER CLERK 7782 23-JAN-82 1300 10

55.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);
OUTPUT:
ENAME
--------
SMITH
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER
56.DISPLAY THE NUMBER OF EMPLOYEES WHO WORK FOR

RESEARCH DEPT AND THEIR SALARY IS LESSER THAN ONE OF

THE SALARY IN DEPARTMENT IN 10.

SQL> SELECT COUNT(*)

2 FROM EMP

3 WHERE DEPTNO IN(SELECT DEPTNO

4 FROM DEPT

5 WHERE DNAME='RESEARCH') AND SAL<ANY(SELECT SAL FROM EMP

6 WHERE DEPTNO=10);

COUNT(*)

----------

57.DISPLAY THE DNAME THAT ARE HAVING CLERK IN IT.

SELECT DNAME FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO FROM EMP
WHERE JOB='CLERK');
OUTPUT:
DNAME
-------------
ACCOUNTING
RESEARCH
SALES

58.DISPLAY THE DEPARTMENT NAMES THAT ARE HAVING

ATLEAST ONE L IN IT.

SELECT DNAME FROM DEPT
WHERE DNAME IN(SELECT DNAME FROM EMP
WHERE DNAME='%L%');
OUTPUT:
no rows selected
59. DISPLAY ALL THE EMPLOYEES WHO ARE JOINED AFTER

BLAKE.
SELECT ENAME FROM EMP
WHERE HIREDATE>(SELECT HIREDATE FROM EMP
WHERE ENAME='BLAKE');
OUTPUT:
ENAME
-------
MARTIN
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

60. LIST THE DEPT NAME THAT ARE HAVING AT LEAST 3

EMPLOYEES BUT NOT MORE THAN 5 EMPLOYEES IN IT.

SQL> SELECT DNAME

2 FROM DEPT

3 WHERE DEPTNO IN (SELECT DEPTNO

4 FROM EMP

5 GROUP BY DEPTNO

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

DNAME

--------------

RESEARCH
ACCOUNTING

61. 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 EMPNO IN(SELECT MGR FROM EMP
WHERE SAL>2000));
OUTPUT:
LOC
----------
DALLAS
NEW YORK
62.SELECT THE EMPLOYEES WHOSE DNAME IS HAVING AT LEAST

TWO 'E' IN IT.

SELECT DNAME FROM DEPT
WHERE DNAME IN(SELECT DNAME FROM DEPT
WHERE DNAME LIKE '%E%E%');
OUTPUT:
DNAME
---------
RESEARCH

63.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');
OUTPUT:
ENAME SAL
---------- ----------
KING 5000
64.SELECT ALL THE EMPLOYEES WHO ARE WORKING FOR

CHICAGO
SELECT DNAME FROM DEPT
WHERE LOC=ALL(SELECT LOC FROM DEPT
WHERE LOC='CHICAGO');
DNAME
------
SALES

65. QUERY TO DISPLAY EMPLOYEE NAMES WHO IS HAVING

MINIMUM SALARY IN DEPARTMENT RESEARCH.

SELECT ENAME FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT
WHERE SAL IN(SELECT MIN(SAL) FROM EMP
WHERE DNAME='RESEARCH'));
OUTPUT:
ENAME
-------
SMITH

66.LIST THE DEPARTMENT NAMES THAT ARE HAVING SALESMAN.

SELECT DNAME FROM DEPT
WHERE DEPTNO IN(SELECT DEPTNO FROM EMP
WHERE JOB='SALESMAN');
OUTPUT:
DNAME
-------
SALES

67. LIST THE DEPARTMENT NAMES THAT ARE HAVING AT LEAST 3

EMPLOYES IN IT.
SQL> SELECT DNAME

2 FROM DEPT

3 WHERE DEPTNO IN (SELECT DEPTNO FROM EMP

4 GROUP BY DEPTNO

5 HAVING COUNT(*)>=3);

DNAME

--------------

SALES

RESEARCH

ACCOUNTING

68.LIST EMPLOYEES FROM RESEARCH AND ACCOUNTING

DEPARTMENT HAVING ATLEAST TWO REPORTING

SQL> SELECT E1.ENAME,COUNT(E2.EMPNO)

2 FROM EMP E1,EMP E2,DEPT D

3 WHERE E1.EMPNO =E2.MGR AND E1.DEPTNO=D.DEPTNO AND DNAME IN

('RESEARCH','ACCOUNTING')

4 GROUP BY E1.ENAME

5 HAVING COUNT(E2.EMPNO)>=2;

ENAME COUNT(E2.EMPNO)

---------- ---------------

JONES 2

KING 3

69.WRITE A QUERY TO DISPLAY EMPLOYEE NAME,

JOB,LOCATION OF ALL EMPLOYEES WHO ARE WORKING AS

MANAGER AND WORKS AT CHICAGO.

SQL> SELECT ENAME,JOB,LOC

2 FROM EMP,DEPT

3 WHERE DEPT.DEPTNO=EMP.DEPTNO AND JOB='MANAGER' AND LOC='CHICAGO';

ENAME JOB LOC

---------- --------- -------------

BLAKE MANAGER CHICAGO

70. SELECT ENAME OF EMPLOYEE WHO EARNS 2ND MAX SALARY

AND WORKS FOR LOCATION DALLAS.

SQL> SELECT ENAME

2 FROM EMP

3 WHERE DEPTNO IN (SELECT DEPTNO

4 FROM DEPT

5 WHERE LOC='DALLAS') AND SAL IN (SELECT MAX(SAL) FROM EMP

6 WHERE SAL<(SELECT MAX(SAL) FROM EMP));

ENAME

----------

SCOTT

FORD

71. WRITE A QUERY TO DISPLAY THE EMPLOYEE INFORMATION

WHO IS NOT TAKING COMMISSION AND JOINED COMPANY AFTER

JULY 83.

SQL> SELECT *

2 FROM EMP

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

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

72.LIST EMPLOYEES FROM SALES AND RESEARCH DEPARTMENT

HAVING ATLEAST 2 REPORTING EMPLOYEES .

SQL> SELECT E1.ENAME,COUNT(E2.MGR) AS REPORTING

2 FROM EMP E1,DEPT D,EMP E2

3 WHERE E1.EMPNO=E2.MGR AND E1.DEPTNO=D.DEPTNO AND DNAME IN ('SALES','RESEARCH')

4 GROUP BY E1.ENAME

5 HAVING COUNT(E2.MGR)>=2;

ENAME REPORTING

---------- ----------

JONES 2

BLAKE 5

73.LIST EMPLOYEES WHO HAVE COMMISSION GREATER THAN

MAXIMUM SALARY OF ALL THE SALESMAN AND WHO DO NOT

REPORT TO KING DIRECTLY .

SQL> SELECT ENAME

2 FROM EMP

3 WHERE COMM>ALL(SELECT SAL

4 FROM EMP

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

6 FROM EMP

7 WHERE MGR IN(SELECT EMPNO

8 FROM EMP

9 WHERE ENAME='KING'));

no rows selected;

74.DISPLAY THE LOCATION OF ALL THE DEAPRTMENTS WHICH

HAVE EMPLOYEES JOINED IN THE YEAR 81

SQL> SELECT LOC

2 FROM DEPT
 3 WHERE DEPTNO IN (SELECT DEPTNO

4 FROM EMP

5 WHERE HIREDATE>'31-DEC-81');

LOC

-------------

NEW YORK

DALLAS

75.DISPLAY DEPARTMENT WISE MINIMUM SALARY WHICH IS LESS

THAN AVERAGE SALARY OF EMPLOYEES.

SQL> SELECT MIN(SAL),AVG(SAL)

2 FROM EMP

3 GROUP BY(DEPTNO)

4 HAVING MIN(SAL)<AVG(SAL);

MIN(SAL) AVG(SAL)

---------- ----------

950 1566.66667

800 2175

1300 2916.66667

76.DISPLAY ALL THE EMPLOYEES WHO ARE REPORTING TO

'JONES'.

SQL> SELECT ENAME

2 FROM EMP

3 WHERE MGR IN (SELECT EMPNO

4 FROM EMP

5 WHERE ENAME='JONES');

ENAME
----------

SCOTT

FORD

77.DISPLAY ALL THE EMPLOYEE INFORMATION WHO ARE LIVING

IN A LOCATION WHICH IS HAVING AT LEAST 2 'O' IN IT.

SQL> SELECT EMP.*

2 FROM EMP

3 WHERE DEPTNO IN (SELECT DEPTNO

4 FROM DEPT

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

no rows selected

78.DISPLAY THE NAMES OF EMPLOYEE FROM DEPARTMENT

NUMBER 10 WITH SALARY GREATER THAN THAT OF ALL

EMPLOYEE WORKING IN OTHER DEPARTMENTS.

SQL> SELECT ENAME

2 FROM EMP

3 WHERE DEPTNO=10 AND SAL>ALL(SELECT SAL

4 FROM EMP

5 WHERE DEPTNO!=10);

ENAME

----------

KING

79.DISPLAY THE NAMES OF EMPLOYEES WHO EARN HIGHEST

SALARY IN THEIR RESPECTIVE JOBS.

SQL> SELECT ENAME

2 FROM EMP

3 WHERE SAL IN (SELECT MAX(SAL)

 4 FROM EMP

5 GROUP BY JOB);

ENAME

----------

MILLER

ALLEN

KING

JONES

FORD

SCOTT

6 rows selected.

80. DISPLAY THE EMPLOYEE NUMBER AND NAME OF EMPLOYEE

WORKING AS CLERK AND EARNING HIGHEST SALARY AMONG

CLERKS.

SQL> SELECT EMPNO,ENAME

2 FROM EMP

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

4 FROM EMP

5 WHERE JOB='CLERK'

6 GROUP BY JOB);

EMPNO ENAME

---------- ----------

7934 MILLER

81. WRITE A QUERY TO FIND SMITH'S MANAGER'S MANAGER

HIREDATE.

SQL> SELECT ENAME,HIREDATE

2 FROM EMP
 3 WHERE EMPNO IN(SELECT MGR

4 FROM EMP

5 WHERE EMPNO IN (SELECT MGR

6 FROM EMP

7 WHERE ENAME='SMITH'));

ENAME HIREDATE

---------- ---------

JONES 02-APR-81

82.LIST THE NUMBER OF EMPLOYEES WHOSE JOB IS SALESMAN

WORKING FOR NEWYORK AND CHICAGO

SELECT EMPNO

FROM EMP

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

FROM DEPT

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

EMPNO

------

7499

7521

7654

7844

83. LIST THE DEPARTMENT NAMES IN WHICH THE EMPLOYEES

ARE HIRED BETWEEN 1ST OF JAN 1981 AND 31ST DEC 1982 WITH

SALARY MORE THAN 1800.

SQL>

SQL> SELECT DNAME

2 FROM DEPT

3 WHERE DEPTNO IN (SELECT DEPTNO

4 FROM EMP
 5 WHERE HIREDATE BETWEEN '01-JAN-81' AND '31-DEC-82' AND SAL>1800);

DNAME

--------------

ACCOUNTING

RESEARCH

SALES

84.DISPLAY LOCATION OF THE EMPLOYEE WHO EARN MAXIMUM

SALARY AND HAVE NO REPORTING MANAGER

SQL> SELECT LOC

2 FROM DEPT

3 WHERE DEPTNO IN (SELECT DEPTNO

4 FROM EMP

5 WHERE SAL IN(SELECT MAX(SAL) FROM EMP) AND MGR IS NULL);

LOC

-------------

NEW YORK

85. LIST EMPLOYEES WHO WORKS FOR ACCOUNTING

DEPARTMENT AND THEIR SALARY GREATER THAN AVERAGE

SALARY OF THEIR DEPARTMENT

SQL> SELECT ENAME

2 FROM EMP

3 WHERE DEPTNO IN(SELECT DEPTNO

4 FROM DEPT

5 WHERE DNAME='ACCOUNTING') AND SAL>(SELECT AVG(SAL)

6 FROM EMP

7 WHERE DEPTNO IN (SELECT DEPTNO

8 FROM DEPT
 9 WHERE DNAME='ACCOUNTING'));

ENAME

----------

KING

86. DISPLAY LOCATION OF THE EMPLOYEE WHO EARN

COMMISSION

SQL> SELECT LOC

2 FROM DEPT

3 WHERE DEPTNO IN(SELECT DEPTNO

4 FROM EMP

5 WHERE COMM IN (SELECT MAX(COMM)

6 FROM EMP));

LOC

-------------

CHICAGO

87.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

SQL> SELECT ENAME

2 FROM EMP

3 WHERE MGR IN (SELECT EMPNO

4 FROM EMP

5 WHERE MGR IN (SELECT EMPNO

6 FROM EMP

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

8 FROM EMP

9 WHERE JOB='CLERK');
ENAME

----------

TURNER

ALLEN

88.DISPLAY ALL THE EMPLOYEES WHOSE SALARY IS GREATER

THAN AVG SAL OF DEPARTMENT 20

SQL> SELECT ENAME

2 FROM EMP

3 WHERE SAL>(SELECT AVG(SAL)

4 FROM EMP WHERE DEPTNO=20);

ENAME

----------

JONES

BLAKE

CLARK

SCOTT

KING

FORD

6 rows selected.

89. LIST THE EMPLOYEE DEPTNAME AND LOC OF ALL THE

EMPLOYEES WHO ARE CLERK ,REPORTING TO BLAKE AND

SALARY IS LESSER THAN MARTIN SALARY

SQL> SELECT DNAME,LOC

2 FROM DEPT

3 WHERE DEPTNO IN (SELECT DEPTNO

4 FROM EMP

5 WHERE JOB='CLERK' AND MGR IN (SELECT EMPNO FROM EMP

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

7 FROM EMP
 8 WHERE ENAME='MARTIN'));

DNAME LOC

-------------- -------------

SALES CHICAGO

90.DISPLAY LOC AND DNAME WHOSE JOB IS MANAGER AND HAS

SAL LESS THAN CLERK.

SQL> SELECT LOC ,DNAME

2 FROM DEPT

3 WHERE DEPTNO IN (SELECT DEPTNO

4 FROM EMP

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

6 FROM EMP

7 WHERE ENAME='CLARK'));

no rows selected

91.DISPLAY EMPLOYEES LOCATION WHO HAS SOME

COMMISSION.

SQL> SELECT LOC

2 FROM DEPT

3 WHERE DEPTNO IN (SELECT DEPTNO

4 FROM EMP

5 WHERE COMM IS NOT NULL);

LOC

-------------

CHICAGO

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

DISPLAY EMPNO IN DESCENDING ORDER.

SQL> SELECT EMPNO,ENAME,JOB

2 FROM EMP

3 WHERE DEPTNO IN

4 (SELECT DEPTNO

5 FROM EMP

6 WHERE JOB LIKE '%E%')

7 ORDER BY EMPNO DESC;

EMPNO ENAME JOB

---------- ---------- ---------

7934 MILLER CLERK

7902 FORD ANALYST

7900 JAMES CLERK

7876 ADAMS CLERK

7844 TURNER SALESMAN

7839 KING PRESIDENT

7788 SCOTT ANALYST

7782 CLARK MANAGER

7698 BLAKE MANAGER

7654 MARTIN SALESMAN

7566 JONES MANAGER

7521 WARD SALESMAN

7499 ALLEN SALESMAN

7369 SMITH CLERK

93. DISPLAY DNAME, LOC,DEPTNO OF EMPLOYEES WHO HAS SAME

REPORTING MANAGER???

SQL> SELECT DNAME,LOC,DEPTNO

2 FROM DEPT

3 WHERE DEPTNO IN (SELECT DEPTNO

 4 FROM EMP

5 WHERE MGR IN (SELECT MGR

6 FROM EMP

7 GROUP BY MGR

8 HAVING COUNT(*)>1));

DNAME LOC DEPTNO

-------------- ------------- ----------

SALES CHICAGO 30

RESEARCH DALLAS 20

ACCOUNTING NEW YORK 10

94. DISPLAY AVG SALARY OF ALL EMPLOYEES WHOSE DEPT

NAME IS ACCOUNTING???

SQL> SELECT AVG(SAL)

2 FROM EMP

3 WHERE DEPTNO IN (SELECT DEPTNO

4 FROM DEPT

5 WHERE DNAME='ACCOUNTING');

AVG(SAL)

----------

2916.66667

95. DISPLAY ALL EMPLOYEES DETAILS

WHOSE HIREDATE IS IN YEAR 81???

SQL> SELECT *

2 FROM EMP

3 WHERE HIREDATE BETWEEN '01-JAN-81' AND '31-DEC-81' ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

10 rows selected.

96. DISPAY DETAILS OF SMITH AND EMPLOYEES WORKING AS

PRESIDENT ALONG WITH HIKE OF 35% IN SALARY.

SQL> SELECT EMP.*,SAL+(SAL*0.35)

2 FROM EMP

3 WHERE ENAME='SMITH' AND JOB ='PRESIDENT';

no rows selected

97. DISPLAY NUMBER OF EMPLOYEES WHOSE COMMISSION IS

MORE THAN SALARY.

SQL> SELECT COUNT(*)

2 FROM EMP

3 WHERE COMM>SAL;

COUNT(*)

----------

1
98. LIST THE EMPLOYEES WHOSE DAILY SALARY IS GREATER

THAN 1500 AND WHO ARE JOINED BEFORE 1982 ONLY.

SQL> SELECT *

2 FROM EMP

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

no rows selected

99. DISPLAY NUMBER OF EMPLOYEES WHOSE COMMISSION IS

MORE THAN SALARY.

SQL> SELECT COUNT(*)

2 FROM EMP

3 WHERE COMM>SAL;

COUNT(*)

----------

100. LIST THE EMPLOYEES WHOSE DAILY SALARY IS GREATER

THAN 1500 AND WHO ARE JOINED BEFORE 1982 ONLY.

SQL> SELECT *

2 FROM EMP

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

no rows selected

101. DISPLAY ALL THE EMPLOYEES WHOSE JOB SAME AS 'SMITH'

AND DEPARTMENT SAME AS 'JONES' AND SALARY MORE THAN

'TURNER'

SQL> SELECT *
 2 FROM EMP

3 WHERE JOB IN(SELECT JOB

4 FROM EMP

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

6 FROM EMP

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

8 FROM EMP

9 WHERE ENAME='TURNER');

no rows selected

102. DISPLAY ALL THE EMPLOYEES WHOSE NAME START WITH 'S'

AND HAVING SALARY MORE THAN 'ALLEN' AND LESS THAN FORD

SQL> SELECT *

2 FROM EMP

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

4 FROM EMP

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

6 FROM EMP

7 WHERE ENAME='FORD');

no rows selected

103. DISPLAY ALL THE CLERKS AND ANALYST WHO ARE NOT

WORKING FOR 'DALLAS'

SQL> SELECT *

2 FROM EMP

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

4 FROM DEPT

5 WHERE LOC='DALLAS');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

 7900 JAMES CLERK 7698 03-DEC-81 950 30

7934 MILLER CLERK 7782 23-JAN-82 1300 10

104. DISPLAY DEPARTMENT NAME WHICH IS HAVING AT LEAST

ONE 'MANAGER'

SQL> SELECT DNAME

2 FROM DEPT

3 WHERE DEPTNO IN (SELECT DEPTNO

4 FROM EMP

5 WHERE JOB='MANAGER')

6 GROUP BY DNAME;

DNAME

--------------

ACCOUNTING

RESEARCH

SALES

105. DISPLAY MAXIMUM SALARY OF 'SALES' DEPARTMENT

SQL> SELECT MAX(SAL)

2 FROM EMP

3 WHERE DEPTNO IN (SELECT DEPTNO

4 FROM DEPT

5 WHERE DNAME='SALES');

MAX(SAL)

----------

2850

106. DISPLAY THE 2ND MAXIMUM SALARY

SQL> SELECT MAX(SAL)

 2 FROM EMP

3 WHERE SAL<(SELECT MAX(SAL)

4 FROM EMP);

MAX(SAL)

----------

3000

107.DISPLAY THE DEPT NAME OF THE EMP WHO GETS 3RD

MAXIMUM SALARY

SQL> SELECT DNAME

2 FROM DEPT

3 WHERE DEPTNO IN (SELECT DEPTNO

4 FROM EMP

5 WHERE SAL IN (SELECT MAX(SAL)

6 FROM EMP

7 WHERE SAL<(SELECT MAX(SAL)

8 FROM EMP

9 WHERE SAL<(SELECT MAX(SAL)

10 FROM EMP))));

DNAME

--------------

RESEARCH

108. DISPLAY ALL THE EMPLOYEES WHO ARE EARNING MORE

THAN ALL THE MANAGERS(JOB).

SQL> SELECT *

2 FROM EMP

3 WHERE SAL>ALL(SELECT SAL

4 FROM EMP
 5 WHERE JOB='MANAGER');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7902 FORD ANALYST 7566 03-DEC-81 3000 20

109. DISPLAY ALL THE EMPLOYEES WHO ARE EARNING MORE

THAN ANY OF THE MANAGER(JOB)

8* FORD ANALYST 7566 03-DEC-81 3000 20

SQL> SELECT *

2 FROM EMP

3 WHERE SAL>ANY(SELECT SAL

4 FROM EMP

5 WHERE JOB='MANAGER');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7839 KING PRESIDENT 17-NOV-81 5000 10

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7566 JONES MANAGER 7839 02-APR-81 2975 20

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

110. 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');
EMPNO JOB SAL

------ --------- ----------

7788 ANALYST 3000

7902 ANALYST 3000

111. SELECT THE DEPARTMENT NAME AND LOCATION OF ALL

THE EMPLOYEES WORKING FOR CLARK.

SQL> SELECT DNAME,LOC

2 FROM DEPT

3 WHERE DEPTNO IN (SELECT DEPTNO

4 FROM EMP

5 WHERE MGR IN(SELECT EMPNO

6 FROM EMP WHERE ENAME='CLARK'));

DNAME LOC

-------------- -------------

ACCOUNTING NEW YORK

112. SELECT ALL THE EMPLOYEES WORKING FOR DALLAS

SQL> SELECT *

2 FROM EMP

3 WHERE DEPTNO IN (SELECT DEPTNO

4 FROM DEPT

5 WHERE LOC='DALLAS');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7566 JONES MANAGER 7839 02-APR-81 2975 20

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

 7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7902 FORD ANALYST 7566 03-DEC-81 3000 20

113. DISPLAY ALL THE EMPLOYEES WHOSE SALARY IS GREATER

THAN AVG SAL OF DEPARTMENT 20

SQL> SELECT *

2 FROM EMP

3 WHERE SAL > (SELECT AVG(SAL)

4 FROM EMP

5 WHERE DEPTNO=20);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7566 JONES MANAGER 7839 02-APR-81 2975 20

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7902 FORD ANALYST 7566 03-DEC-81 3000 20

6 rows selected.

114.DISPLAY ALL THE EMPLOYEES WHO GETS MAXIMUM SALARY.

SQL> SELECT *

2 FROM EMP

3 WHERE SAL =(SELECT MAX(SAL)

4 FROM EMP);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7839 KING PRESIDENT 17-NOV-81 5000 10

115.DISPLAY FIRST EMPLOYEE RECORD BASED ON HIREDATE.

SQL> SELECT *

2 FROM EMP

3 WHERE HIREDATE IN (SELECT MIN(HIREDATE)

4 FROM EMP);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

INNER JOIN
1.NAME OF THE EMPLOYEE AND HIS LOCATION OF ALL THE

EMPLOYEES.

ANS:

SQL> SELECT ENAME,LOC

2 FROM DEPT,EMP

3 WHERE EMP.DEPTNO=DEPT.DEPTNO;

ENAME LOC

---------- -------------

SMITH DALLAS

ALLEN CHICAGO

WARD CHICAGO

JONES DALLAS

MARTIN CHICAGO

BLAKE CHICAGO

CLARK NEW YORK

SCOTT DALLAS

KING NEW YORK

TURNER CHICAGO

ADAMS DALLAS

JAMES CHICAGO

FORD DALLAS

MILLER NEW YORK

14 rows selected.

2.WAQTD DNAME AND SALARY FOR ALL THE EMPLOYEE

WORKING IN ACCOUNTING.

ANS:

SQL> SELECT DNAME,SAL

2 FROM EMP,DEPT

3 WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME='ACCOUNTING';

DNAME SAL

-------------- ----------

ACCOUNTING 2450

ACCOUNTING 5000

ACCOUNTING 1300

3.WAQTD DNAME AND ANNUAL SALARY FOR ALL EMPLOYEES

WHOS SALARY IS MORE THAN 2340

ANS:

SQL> SELECT DNAME,SAL*12

2 FROM EMP,DEPT

3 WHERE EMP.DEPTNO=DEPT.DEPTNO AND SAL>2340;

DNAME SAL*12

-------------- ----------

RESEARCH 35700
SALES 34200

ACCOUNTING 29400

RESEARCH 36000

ACCOUNTING 60000

RESEARCH 36000

6 rows selected.

4.WAQTD ENAME AND DNAME FOR EMPLOYEES HAVING

CHARACTER 'A' IN THEIR DNAME

SQL> SELECT ENAME,DNAME

2 FROM EMP,DEPT

3 WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME LIKE '%A%';

ENAME DNAME

---------- --------------

SMITH RESEARCH

ALLEN SALES

WARD SALES

JONES RESEARCH

MARTIN SALES

BLAKE SALES

CLARK ACCOUNTING

SCOTT RESEARCH

KING ACCOUNTING

TURNER SALES

ADAMS RESEARCH

JAMES SALES

FORD RESEARCH

MILLER ACCOUNTING

5.WAQTD ENAME AND DNAME FOR ALL THE EMPLOYEES

WORKING AS SALESMAN

ANS:

SQL> SELECT ENAME,DNAME

2 FROM EMP,DEPT

3 WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB='SALESMAN';

ENAME DNAME

---------- --------------

ALLEN SALES

WARD SALES

MARTIN SALES

TURNER SALES

6.WADTD DNAME AND JOB FOR ALL THE EMPLOYEES WHOS JOB

AND DNAME STARTS WITH CHARACTER 'S'

ANS:

SQL> SELECT DNAME ,JOB

2 FROM EMP,DEPT

3 WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB LIKE 'S%' AND DNAME LIKE 'S%';

DNAME JOB

-------------- ---------

SALES SALESMAN

SALES SALESMAN

SALES SALESMAN

SALES SALESMAN

7.WAQTD DNAME AND MGR NO FOR EMPLOYEES REPORTING TO

7839

ANS:
SQL> SELECT DNAME,MGR

2 FROM EMP,DEPT

3 WHERE EMP.DEPTNO=DEPT.DEPTNO AND MGR=7839 ;

DNAME MGR

-------------- ----------

RESEARCH 7839

SALES 7839

ACCOUNTING 7839

8.WAQTD DNAME AND HIREDATE FOR EMPLOYEES HIRED AFTER

83 INTO ACCOUNTING OR RESEARCH DEPT

ANS:

SQL> SELECT DNAME, HIREDATE

2 FROM EMP,DEPT

3 WHERE EMP.DEPTNO=DEPT.DEPTNO AND HIREDATE>'01-JAN-83' AND DNAME IN

('ACCOUNTING','RESEARCH');

DNAME HIREDATE

-------------- ---------

RESEARCH 19-APR-87

RESEARCH 23-MAY-87

9.WAQTD ENAME AND DNAME OF THE EMPLOYEES WHO ARE

GETTING COMM IN DEPT 10 OR 30

ANS:

SQL> SELECT ENAME,DNAME

2 FROM EMP,DEPT

3 WHERE EMP.DEPTNO=DEPT.DEPTNO AND COMM IS NOT NULL AND EMP.DEPTNO IN (10,30);

ENAME DNAME

---------- --------------

ALLEN SALES

WARD SALES
MARTIN SALES

TURNER SALES

10.WAQTD DNAME AND EMPNO FOR ALL THE EMPLOYEES WHO'S

EMPNO ARE (7839,7902) AND ARE WORKING IN LOC NEW YORK.

ANS:

SQL> SELECT DNAME,EMPNO

2 FROM EMP,DEPT

3 WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMPNO IN(7839,7902) AND LOC='NEW YORK';

DNAME EMPNO

-------------- ----------

ACCOUNTING 7839

11.WAQTD LOC AND AVERAGE SALARY GIVEN FOR EACH

LOCATION BY EXCLUDING ALL THE EMPLOYEES WHOS SECOND

CHAR IS A IN THEIR NAME .

ANS:

SQL> SELECT LOC,AVG(SAL)

2 FROM EMP,DEPT

3 WHERE EMP.DEPTNO=DEPT.DEPTNO AND ENAME NOT LIKE '_A%'

4 GROUP BY LOC;

LOC AVG(SAL)

------------- ----------

NEW YORK 2916.66667

CHICAGO 1983.33333

DALLAS 2175

12.WAQTD NAME OF THE EMP AND HIS LOC IF EMPLOYEE IS

WORKING AS MANAGER AND WORKING UNDER THE EMPLOYEE

WHOS EMPNO IS 7839

ANS:
SQL> SELECT ENAME,LOC

2 FROM EMP,DEPT

3 WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB='MANAGER' AND MGR=7839;

ENAME LOC

---------- -------------

JONES DALLAS

BLAKE CHICAGO

CLARK NEW YORK

13.WAQTD DNAME AND EMPLOYEE ID'S OF ALL THE EMPLOYEES

WHO ARE CLERKS AND HAVING REPORTING MANAGERS.

ANS:

SQL> SELECT DNAME,EMPNO

2 FROM EMP,DEPT

3 WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB='CLERK' AND MGR IS NOT NULL;

DNAME EMPNO

-------------- ----------

RESEARCH 7369

RESEARCH 7876

SALES 7900

ACCOUNTING 7934

14.WAQTD DNAME AND TOTAL SALARY GIVEN TO THAT DEPT IF

THERE ARE ATLEAST 4 EMPLOYEES WORKING FOR EACH DEPT.

SQL> SELECT DNAME,SUM(SAL)

2 FROM EMP JOIN DEPT

3 ON EMP.DEPTNO =DEPT.DEPTNO

4 GROUP BY DNAME

5 HAVING COUNT(*)>=4;
DNAME SUM(SAL)

-------------- ----------

RESEARCH 10875

SALES 9400

15.WAQTD DNAME AND NUMBER OF EMPLOYEES WORKING IN

EACH DEPT ONLY IF THERE ARE MANAGER OR CLERKS

SQL> SELECT DNAME,COUNT(*)

2 FROM DEPT,EMP

3 WHERE EMP.DEPTNO =DEPT.DEPTNO AND JOB IN ('MANAGER','CLERK')

4 GROUP BY DNAME;

DNAME COUNT(*)

-------------- ----------

ACCOUNTING 2

RESEARCH 3

SALES 2

ON ALL JOINS
1. LIST ENAME, JOB, ANNUAL SAL, DEPTNO, DNAME WHO EARN

30000 PER YEAR AND WHO ARE NOT CLERKS

SELECT ENAME,JOB,SAL*12,EMP.DEPTNO,DNAME

FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND SAL*12=30000 AND JOB!='CLERK';

OUTPUT:

no rows selected

2. LIST OUT THE ALL EMPLOYEES BY NAME AND EMPLOYEE

NUMBER ALONG WITH THEIR MANAGER'S NAME AND EMPLOYEE

NUMBER

ANS:

SELECT E1. ENAME, E1. EMPNO, E2. ENAME, E2. EMPNO

FROM EMP E1,EMP E2

WHERE E1.MGR=E2.EMPNO;

OUTPUT:

ENAME EMPNO ENAME EMPNO

---------- ---------- ---------- ----------

SMITH 7369 FORD 7902

ALLEN 7499 BLAKE 7698

WARD 7521 BLAKE 7698

JONES 7566 KING 7839

MARTIN 7654 BLAKE 7698

BLAKE 7698 KING 7839

CLARK 7782 KING 7839

SCOTT 7788 JONES 7566

TURNER 7844 BLAKE 7698

ADAMS 7876 SCOTT 7788

JAMES 7900 BLAKE 7698

FORD 7902 JONES 7566

MILLER 7934 CLARK 7782

13 rows selected.

3. DISPLAY ENAME,DNAME EVEN IF THERE NO EMPLOYEES

WORKING IN A PARTICULAR DEPARTMENT.

SELECT ENAME,DNAME

FROM EMP RIGHT OUTER JOIN DEPT

ON EMP.DEPTNO=DEPT.DEPTNO;

OUTPUT:

ENAME DNAME

---------- --------------

CLARK ACCOUNTING

KING ACCOUNTING
MILLER ACCOUNTING

JONES RESEARCH

FORD RESEARCH

ADAMS RESEARCH

SMITH RESEARCH

SCOTT RESEARCH

WARD SALES

TURNER SALES

ALLEN SALES

JAMES SALES

BLAKE SALES

MARTIN SALES

OPERATIONS

4. DISPLAY THE DEPARTMENT NAME ALONG WITH TOTAL

SALARY IN EACH DEPARTMENT

ANS:

SELECT DNAME, SUM(SAL)

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT

GROUP BY DEPT.DNAME;

DNAME SUM(SAL)

-------------- ----------

ACCOUNTING 8750

RESEARCH 10875

SALES 9400

5. DISPLAY EMPLOYEE NAME AND DEPARTMENT NAME FOR EACH

EMPLOYEE

ANS:
SELECT ENAME, DNAME

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO;

ENAME DNAME

---------- --------------

SMITH RESEARCH

ALLEN SALES

WARD SALES

JONES RESEARCH

MARTIN SALES

BLAKE SALES

CLARK ACCOUNTING

SCOTT RESEARCH

KING ACCOUNTING

TURNER SALES

ADAMS RESEARCH

JAMES SALES

FORD RESEARCH

MILLER ACCOUNTING

14 rows selected.

6. DISPLAY LOCATION NAME OF THE EMPLOYEE WHO EARN

COMMISSION.

ANS:

SELECT LOC

FROM EMP, DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND COMM IS NOT NULL;

LOC
-------------

CHICAGO

CHICAGO

CHICAGO

CHICAGO

7. DISPLAY DEPT NAME OF THE EMPLOYEE WHO EARN MIN

SALARY AND HAVE NO REPORTING MANAGER.

ANS:

SELECT DNAME

FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND SAL IN(SELECT MIN(SAL)

FROM EMP

WHERE MGR IS NULL);

OUTPUT:

DNAME

--------------

ACCOUNTING

8. DISPLAY DEPT NAME,LOC OF ALL THE EMPLOYEES WHO ARE

REPORTING TO SMITH.

SQL> SELECT DNAME,LOC

2 FROM EMP,DEPT

3 WHERE EMP.DEPTNO=DEPT.DEPTNO AND MGR=(SELECT EMPNO

4 FROM EMP

5 WHERE ENAME='SMITH');

no rows selected

9. LIST ALL THE DEPT NAME AND LOCATION OF ALL THE

SALESMAN MANAGER'S MANAGER.

SQL> SELECT DNAME, LOC

FROM EMP, DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMPNO IN (SELECT MGR

FROM EMP

WHERE EMPNO IN (SELECT MGR

FROM EMP

WHERE JOB='SALESMAN'));

DNAME LOC

-------------- ------------

ACCOUNTING NEW YORK

10. LIST EMPLOYEES WHO ARE WORKING IN RESEARCH DEPT

AND THEY ARE MANAGER.

ANS:

SQL> SELECT ENAME

FROM EMP, DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME='RESEARCH' AND JOB='MANAGER';

ENAME

----------

JONES

11. DISPLAY THE NUMBER OF EMPLOYEES WHO ARE GETTING

SALARY LESS THAN THE BLAKE'S MANAGER

ANS:

SQL>

SELECT EMPNO

FROM EMP

WHERE SAL>(SELECT SAL

FROM EMP

WHERE EMPNO IN (SELECT MGR

 FROM EMP

WHERE ENAME='BLAKE'));

no rows selected

12. LIST THE EMPLOYEE DEPTNAME AND LOCATION OF ALL THE

EMPLOYEES WHO ARE ANALYST,REPORTING TO BLAKE.

ANS:

SELECT DNAME,LOC

FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB='ANALYST' AND MGR IN( SELECT EMPNO

FROM EMP

WHERE ENAME='BLAKE');

no rows selected

13. DISPLAY THE EMPLOYEE NAMES,HIREDATE,COMM OF FORD'S

MANAGER

ANS:

SQL> SELECT ENAME,HIREDATE,COMM

2 FROM EMP

3 WHERE EMPNO IN(SELECT MGR

4 FROM EMP

5 WHERE ENAME='FORD');

ENAME HIREDATE COMM

---------- --------- ----------

JONES 02-APR-81

14. DISPLAY ENAME, DNAME OF ALL THE EMPLOYEES WHOSE

SALARY LESS THAN AVG SAL OF DEPT 30

ANS:

SQL> SELECT ENAME,DNAME

2 FROM EMP,DEPT
 3 WHERE EMP.DEPTNO=DEPT.DEPTNO AND SAL<(SELECT AVG(SAL)

4 FROM EMP

5 WHERE DEPTNO=30);

ENAME DNAME

---------- --------------

SMITH RESEARCH

WARD SALES

MARTIN SALES

TURNER SALES

ADAMS RESEARCH

JAMES SALES

MILLER ACCOUNTING

7 rows selected.

15. DISPLAY ENAME DNAME AND LOC OF ALL THE EMPLOYEES

WHO ARE WORKING FOR JONES

SQL> SELECT ENAME,DNAME,LOC

2 FROM EMP,DEPT

3 WHERE EMP.DEPTNO=DEPT.DEPTNO AND MGR IN (SELECT EMPNO

4 FROM EMP

5 WHERE ENAME='JONES');

ENAME DNAME LOC

---------- -------------- -------------

SCOTT RESEARCH DALLAS

FORD RESEARCH DALLAS

16. DISPLAY ENAME DNAME OF ALL THE EMPLOYEES WHOSE

NAME STARTS WITH ‘S’

ANS:
SQL> SELECT ENAME, DNAME

2 FROM EMP, DEPT

3 WHERE EMP.DEPTNO=DEPT.DEPTNO AND ENAME LIKE 'S%';

ENAME DNAME

---------- --------------

SMITH RESEARCH

SCOTT RESEARCH

17. LIST THE DNAME WHO ARE NOT HAVING ANY EMPLOYEE IN IT

ANS:

SQL> SELECT DNAME

2 FROM DEPT

3 WHERE NOT EXISTS (SELECT DEPTNO

4 FROM EMP

5 WHERE EMP.DEPTNO=DEPT.DEPTNO);

DNAME

--------------

OPERATIONS

18. DISPLAY EMPLOYEE WHO ARE GETTING SAME COMMISSION

ANS:

SQL> SELECT ENAME

2 FROM EMP

3 WHERE EXISTS (SELECT COMM

4 FROM EMP

5 GROUP BY COMM

6 HAVING COUNT(*)=2);

no rows selected

19. DISPLAY ALL THE DEPT NAMES IRRESPECTIVE OF ANY

EMPLOYEE WORKING IN IT OR NOT. IF AN EMPLOYEE IS

WORKING DISPLAY HIS NAME.

SQL> SELECT DEPT.DNAME, EMP.ENAME

FROM DEPT LEFT JOIN EMP

ON DEPT.DEPTNO = EMP.DEPTNO;

DNAME ENAME

-------------- ----------

ACCOUNTING CLARK

ACCOUNTING KING

ACCOUNTING MILLER

RESEARCH JONES

RESEARCH FORD

RESEARCH ADAMS

RESEARCH SMITH

RESEARCH SCOTT

SALES WARD

SALES TURNER

SALES ALLEN

SALES JAMES

SALES BLAKE

SALES MARTIN

OPERATIONS

15 rows selected.

20. WRITE A QUERY TO DISPLAY EMPLOYEE NAME, JOB, DNAME,

LOCATION OF ALL EMPLOYEES WHO ARE WORKING AS ACTUAL

MANAGERS AND WORKS AT CHICAGO.

ANS:
SQL> SELECT ENAME, JOB, DNAME,LOC

FROM EMP, DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB='MANAGER' AND LOC='CHICAGO';

ENAME JOB DNAME LOC

---------- --------- -------------- -------------

BLAKE MANAGER SALES CHICAGO

21. LIST THE DEPARTMENT NAMES IN WHICH THE EMPLOYEES

ARE HIRED BETWEEN 1ST OF JAN 1981 AND 31ST DEC 1982 WITH

SALARY MORE THAN 1800.

ANS:

SQL> select dname

FROM EMP, DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND HIREDATE BETWEEN '01-JAN-1981' AND '31-DEC-1982'

AND SAL>1800;

DNAME

--------------

RESEARCH

SALES

ACCOUNTING

ACCOUNTING

RESEARCH

22. DISPLAY 2ND LEAST SALARY FROM EMPLOYEE TABLE.

SELECT MIN(SAL)

FROM EMP

WHERE SAL>(SELECT MIN(SAL)

FROM EMP);
 MIN(SAL)

----------

950

23. LIST THE EMPLOYEES WHOSE ANNUAL SALARY IS GREATER

THAN 1500 AND WHO ARE JOINED BEFORE 1982 ONLY.

SQL> SELECT ENAME

2 FROM EMP

3 WHERE SAL*12>1500 AND HIREDATE<'01-JAN-82';

ENAME

----------

SMITH

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

KING

TURNER

JAMES

FORD

11 rows selected.

24. DISPLAY DNAME, LOC, DEPTNO OF EMPLOYEES WHO HAS

SAME REPORTING MANAGER

ANS:

SQL> SELECT DNAME, LOC, EMP.DEPTNO

 FROM EMP, DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND MGR IN (SELECT MGR

FROM EMP

GROUP BY MGR

HAVING COUNT (*)>1);

DNAME LOC DEPTNO

-------------- ------------- ----------

ACCOUNTING NEW YORK 10

SALES CHICAGO 30

RESEARCH DALLAS 20

SALES CHICAGO 30

SALES CHICAGO 30

SALES CHICAGO 30

SALES CHICAGO 30

SALES CHICAGO 30

RESEARCH DALLAS 20

RESEARCH DALLAS 20

10 rows selected.

25. DISPLAY EMPLOYEE NAME ALONG WITH THEIR MANAGER

NAME.

ANS:

SQL> SELECT E1. ENAME, E2. ENAME

FROM EMP E1, EMP E2

WHERE E1.MGR=E2.EMPNO;

ENAME ENAME

---------- ----------
SMITH FORD

ALLEN BLAKE

WARD BLAKE

JONES KING

MARTIN BLAKE

BLAKE KING

CLARK KING

SCOTT JONES

TURNER BLAKE

ADAMS SCOTT

JAMES BLAKE

FORD JONES

MILLER CLARK

13 rows selected.

26. DISPLAY EMPLOYEE NAME AND HIS DEPT NAME FOR THE

EMPLOYEES WHOSE NAME STARTS WITH ‘S’.

ANS:

SQL> SELECT ENAME, DNAME

FROM EMP, DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND ENAME LIKE '%S';

ENAME DNAME

---------- --------------

JONES RESEARCH

ADAMS RESEARCH

JAMES SALES

27. DISPLAY EMPLOYEES WHO ARE GETTING SAME SALARY

SQL> SELECT ENAME

FROM EMP

WHERE SAL IN (SELECT SAL

FROM EMP

GROUP BY SAL

HAVING COUNT(*)>1);

ENAME

----------

MARTIN

WARD

FORD

SCOTT