DAY 11

Thursday, July 30, 2020 9:40 AM

SUB-QUERY
" A QUERY WRITTEN INSIDE ANOTHER QUERY IS KNOWN
AS SUB QUERY "

Working Principle :

Let us consider two queries Outer Query and Inner Query .

 Inner Query executes first and produces an Output .
 The Output of Inner Query is given / fed as an Input to Outer Query .
 The Outer Query generates the Result.
 Therefore we can state that 'the Outer Query is dependent on
Inner Query' and this is the Execution Principle of Sub Query
.

Why / When Do we use SUB QUERY :

Case 1 : Whenever we have Unknowns present in the Question

We use sub query to find the Unknown .

Example :

EMP
EID ENAME SAL DEPTNO
1 ALLEN 1000 20
2 BLAKE 2000 10
3 CLARK 3000 30
4 MILLER 1500 10
5 SMITH 2500 10

1. WAQTD names of the employees earning more than 2500 .

SELECT ENAME
FROM EMP
New Section 1 Page 1
 WHERE SAL > 2500 ;

2. WAQTD names of the employees earning less than MILLER .

SELECT ENAME
FROM EMP
WHERE SAL < ( SELECT SAL
FROM EMP
WHERE ENAME = 'MILLER' );

3. WAQTD name and deptno of the employees working in the

same Dept as SMITH .

SELECT ENAME , DEPTNO

FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME ='SMITH' ) ;

4. WAQTD name and hiredate of the employees if the

employee Was hired after JONES .

SELECT ENAME , HIREDATE

FROM EMP
WHERE HIREDATE > ( SELECT HIREDATE
FROM EMP
WHERE ENAME ='JONES' ) ;

5. WAQTD all the details of the employee working in the

same Designation as KING .

SELECT *
FROM
EMP
WHERE JOB = ( SELECT JOB
FROM EMP
WHERE ENAME ='KING' );

6. WAQTD name , sal , deptno of the employees if the employees

Earn more than 2000 and work in the same dept as JAMES .

SELECT ENAME , SAL , DEPTNO

FROM EMP
WHERE SAL > 2000 AND DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME ='JAMES' ) ;

7. WAQTD all the details of the employees working in the

Same designation as MILLER and earning more than
1500.

SELECT *
FROM
EMP
WHERE SAL > 1500 AND JOB = ( SELECT JOB
FROM EMP
New Section 1 Page 2
WHERE ENAME ='MILLER' ) ;

New Section 1 Page 3

 SELECT *
FROM EMP
WHERE JOB = ( SELECT JOB
FROM EMP
WHERE ENAME ='MILLER' ) AND SAL > 1500 ;

8. WAQTD details of the employees earning more than

SMITH But less than KING .

SELECT *
FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='SMITH' ) AND SAL < ( SELECT SAL
FROM EMP
WHERE ENAME ='KING' ) ;

9. WAQTD name , sal and deptno of the employees if the employee

Is earning commission in dept 20 and earning salary more than
Scott .

SELECT ENAME , SAL , DEPTNO

FROM EMP
WHERE COMM IS NOT NULL AND DEPTNO = 20 AND
SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='SCOTT' ) ;

10. WAQTD name and hiredate of the employees who's name ends
with 'S' and hired after James .

SELECT ENAME , HIREDATE

FROM EMP
WHERE ENAME LIKE '%S' AND
HIREDATE > ( SELECT HIREDATE
FROM EMP
WHERE ENAME ='JAMES' ) ;

11. WAQTD names of the employees working in the same dept as

JAMES and earning salary more than ADAMS and working in
the same job role as MILLER and hired after MARTIN .

SELECT ENAME
FROM EMP
WHERE DEPTNO=(SELECT DEPTNO
FROM EMP
WHERE ENAME='JAMES') AND
SAL>(SELECT SAL
FROM EMP
WHERE ENAME='ADAMS') AND
JOB=(SELECT JOB
FROM EMP
WHERE ENAME='MILLER') AND

New Section 1 Page 4

 HIREDATE>(SELECT HIREDATE
FROM EMP
WHERE ENAME='MARTIN');

12. WAQTD all the details of the employees working as salesman in

the dept 20 and earning commission more than Smith and hired
after KING .

SELECT *
FROM
EMP
WHERE JOB ='SALESMAN' AND
DEPTNO = 20 AND
COMM > ( SELECT COMM
FROM EMP
WHERE ENAME ='SMITH' ) AND
HIREDATE > ( SELECT HIREDATE
FROM EMP
WHERE ENAME ='KING' ) ;

13. WAQTD number of employees earning more than SMITH and

less than MARTIN .

SELECT COUNT(*)
FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='SMITH') AND
SAL < ( SELECT SAL
FROM EMP
WHERE ENAME ='MARTIN' )

14. WAQTD Ename and SAL for all the employees earning more
than JONES .

SELECT ENAME , SAL

FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP
WHERE ENAME =JONES' ) ;

15. WAQTD all the details of the employees working as a manager .

SELECT *
FROM
EMP
WHERE JOB ='MANAGER' ;

NOTE :
 In the Inner Query / Sub Query we cannot select more than
One column .
 The corresponding columns need not be same , but the
datatypes of those has to be same .

ASSIGNMENT ON CASE 1
New Section 1 Page 5
1. WAQTD NAME OF THE EMPLOYEES EARNING MORE
THAN ADAMS
2. WAQTD NAME AND SALARY OF THE EMPLOYEES
EARNING LESS THAN KING
3. WAQTD NAME AND DEPTNO OF THE EMPLOYEES IF THEY
ARE WORKING IN THE SAME DEPT AS JONES
4. WAQTD NAME AND JOB OF ALL THE EMPLOYEES
WORKING IN THE SAME DESIGNATION AS JAMES
5. WAQTD EMPNO AND ENAME ALONG WITH ANNUAL
SALARY OF ALL THEEMPLOYEES IF THEIR ANNUAL SALARY
IS GREATER THAN WARDS ANNUAL SALARY.
6. WAQTD NAME AND HIREDATE OF THE EMPLOYEES IF
THEY ARE HIRED BEFORE SCOTT
7. WAQTD NAME AND HIREDATE OF THE EMPLOYEES IF
THEY ARE HIRED AFTER THE PRESIDENT
8. WAQTD NAME AND SAL OF THE EMPLOYEE IF THEY ARE
EARNING SAL LESS THAN THE EMPLOYEE WHOS EMPNO IS
7839 9.WAQTD ALL THE DETAILS OF THE EMPLOYEES IF THE
EMPLOYEES ARE HIRED BEFORE MILLER
10. WAQTD ENAME AND EMPNO OF THE EMPLOYEES
IF EMPLOYEES ARE EARNING MORE THAN ALLEN
11. WAQTD ENAME AND SALARY OF ALL THE EMPLOYEES
WHO ARE EARNING MORE THAN MILLER BUT LESS THAN
ALLEN .
12. WAQTD ALL THE DETAILS OF THE EMPLOYEES WORKING IN
DEPT 20 AND WORKING IN THE SAME DESIGNATION AS SMITH
13.WAQTD ALL THE DETAILS OF THE EMPLOYEES WORKING
AS MANAGER IN THE SAME DEPT AS TURNER
14. WAQTD NAME AND HIREDATE OF THE EMPLOYEES
HIRED AFTER 1980 AND BEFORE KING
15. WAQTD NAME AND SAL ALONG WITH ANNUAL SAL FOR
ALL EMPLOYEES WHOS SAL IS LESS THAN BLAKE AND MORE
THAN 3500
16. WAQTD ALL THE DETAILS OF EMPLOYEES WHO EARN
MORE THAN SCOTT BUT LESS THAN KING
17. WAQTD NAME OF THE EMPLOYEES WHOS NAME
STARTS WITH 'A' AND WORKS IN THE SAME DEPT AS
BLAKE
18. WAQTD NAME AND COMM IF EMPLOYEES EARN
COMISSION AND WORK IN THE SAME DESIGNATION AS SMITH
19. WAQTD DETAILS OF ALL THE EMPLOYEES WORKING
AS CLERK IN THE SAME DEPT AS TURNER .
20. WAQTD ENAME, SAL AND DESIGNATION OF THE
EMPLOYEES WHOS ANNUAL SALARY IS MORE THAN SMITH
AND LESS THAN KING.

1. WAQTD NAME OF THE EMPLOYEES EARNING MORE

THAN ADAMS
SELECT ENAME
FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='ADAMS' );

New Section 1 Page 6

2. WAQTD NAME AND SALARY OF THE EMPLOYEES
EARNING LESS
THAN KING
SELECT ENAME , SAL
FROM EMP
WHERE SAL < ( SELECT SAL
FROM EMP
WHERE ENAME ='KING' );

3. WAQTD NAME AND DEPTNO OF THE EMPLOYEES IF THEY

ARE WORKING
IN THE SAME DEPT AS JONES
SELECT ENAME , DEPTNO
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME =JONES');

4. WAQTD NAME AND JOB OF ALL THE EMPLOYEES

WORKING IN THE SAME
DESIGNATION AS JAMES
SELECT ENAME , JOB
FROM EMP
WHERE JOB = ( SELECT JOB
FROM EMP
WHERE ENAME ='JAMES' );

5. WAQTD EMPNO AND ENAME ALONG WITH ANNUAL

SALARY OF ALL THE
EMPLOYEES IF THEIR ANNUAL SALARY IS GREATER THAN
WARDS
ANNUAL SALARY.
SELECT EMPNO , ENAME . SAL*12
FROM EMP
WHERE SAL * 12 > ( SELECT SAL*12
FROM EMP
WHERE ENAME = 'WARD' );

6. WAQTD NAME AND HIREDATE OF THE EMPLOYEES IF

THEY ARE HIRED
BEFORE SCOTT
SELECT ENAME , HIREDATE
FROM EMP
WHERE HIREDATE < ( SELECT HIREDATE
FROM EMP
WHERE ENAME ='SCOTT' );

7. WAQTD NAME AND HIREDATE OF THE EMPLOYEES IF

THEY ARE HIRED
AFTER THE PRESIDENT
SELECT ENAME , HIREDATE
FROM EMP
WHERE HIREDATE > ( SELECT HIREDATE
FROM EMP

New Section 1 Page 7

WHERE JOB = 'PRESIDENT' );

8. WAQTD NAME AND SAL OF THE EMPLOYEE IF THEY

ARE EARNING SAL
LESS THAN THE EMPLOYEE WHOS EMPNO IS 7839
SELECT ENAME , SAL
FROM EMP
WHERE SAL < ( SELECT SAL
FROM EMP
WHERE EMPNO = 7839 );

9. WAQTD ALL THE DETAILS OF THE EMPLOYEES IF

THE EMPLOYEES ARE
HIRED BEFORE MILLER
SELECT *
FROM EMP
WHERE HIREDATE < ( SELECT HIREDATE
FROM EMP
WHERE ENAME ='MILLER' );

10. WAQTD ENAME AND EMPNO OF THE EMPLOYEES

IF EMPLOYEES ARE
EARNING MORE THAN ALLEN
SELECT ENAME , EMPNO
FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='ALLEN' );

11. WAQTD ENAME AND SALARY OF ALL THE EMPLOYEES

WHO ARE EARNING
MORE THAN MILLER BUT LESS THAN ALLEN
SELECT ENAME , SAL
FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='MILLER ) AND SAL < ( SELECT SAL
FROM EMP
WHERE ENAME ='ALLEN' );

12. WAQTD ALL THE DETAILS OF THE EMPLOYEES WORKING

IN DEPT 20
AND WORKING IN THE SAME DESIGNATION AS SMITH
SELECT *
FROM EMP
WHERE DEPTNO = 20 AND JOB = ( SELECT JOB
FROM EMP
WHERE ENAME ='SMITH');

13. WAQTD ALL THE DETAILS OF THE EMPLOYEES WORKING

AS MANAGER
IN THE SAME DEPT AS TURNER
SELECT *
FROM EMP

New Section 1 Page 8

WHERE JOB ='MANAGER' AND DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME ='TURNER' ) ;

14. WAQTD NAME AND HIREDATE OF THE EMPLOYEES

HIRED AFTER 1980
AND BEFORE KING
SELECT ENAME , HIREDATE
FROM EMP
WHERE HIREDATE > '31-DEC-1980 ' AND HIREDATE < ( SELECT
HIREDATE
FROM EMP
WHERE ENAME ='KING' );

15. WAQTD NAME AND SAL ALONG WITH ANNUAL SAL FOR
ALL EMPLOYEES
WHOS SAL IS LESS THAN BLAKE AND MORE THAN 3500
SELECT ENAME , SAL , SAL*12
FROM EMP
WHERE SAL > 3500 AND SAL < ( SELECT SAL
FROM EMP
WHERE ENAME ='BLAKE );

16. WAQTD ALL THE DETAILS OF EMPLOYEES WHO EARN

MORE THAN SCOTT
BUT LESS THAN KING
SELECT *
FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='SCOTT') AND SAL < ( SELECT SAL
FROM EMP
WHERE ENAME ='KING' );

17. WAQTD NAME OF THE EMPLOYEES WHOS NAME

STARTS WITH ‘A’ AND
WORKS IN THE SAME DEPT AS BLAKE
SELECT ENAME
FROM EMP
WHERE ENAME LIKE 'A%' AND DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME ='BLAKE' ) ;

18. WAQTD NAME AND COMM IF EMPLOYEES EARN

COMISSION AND WORK IN
THE SAME DESIGNATION AS SMITH
SELECT ENAME , COMM
FROM EMP
WHERE COMM IS NOT NULL AND JOB = ( SELECT JOB
FROM EMP
WHERE ENAME ='SMITH');

19. WAQTD DETAILS OF ALL THE EMPLOYEES WORKING AS

New Section 1 Page 9

CLERK IN THE
SAME DEPT AS TURNER
SELECT *
FROM EMP
WHERE JOB ='CLERK' AND DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME ='TURNER' ) ;

20. WAQTD ENAME, SAL AND DESIGNATION OF THE

EMPLOYEES WHOS
ANNUAL SALARY IS MORE THAN SMITH AND LESS THAN KING
SELECT ENAME , SAL , JOB
FROM EMP
WHERE SAL*12 > ( SELECT SAL *12
FROM EMP
WHERE ENAME ='SMITH') AND SAL < ( SELECT SAL *12
FROM EMP
WHERE ENAME ='KING' );

New Section 1 Page

 DAY 12
Saturday, August 1, 2020 9:38 AM

CASE-2 : Whenever the data to be selected and the condition to be

executed are present in different tables we use Sub Query .

Example :

Emp
EID ENAME SAL DEPTNO
1 ALLEN 1000 20 DEPT
2 BLAKE 2000 10 DEPTNO DNAME LOC
3 CLARK 3000 30 10 D1 L1
4 MILLER 1500 10 20 D2 L2
5 ADAMS 2500 20 30 D3 L3

1. WAQTD deptno of the employee whose name is Miller .

SELECT DEPTNO
FROM EMP
WHERE ENAME ='MILLER' ;

2. WAQTD dname of the employee whose name is Miller .

SELECT DNAM
FROM
DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME ='MILLER' ) ;

3. WAQTD Location of ADAMS

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

4. WAQTD names of the employees working in Location L2.

SELECT ENAME
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE LOC ='L2' ) ;

5. WAQTD number of employees working in dept D3 .

SELECT COUNT(*)
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO

New Section 1 Page 1

 FROM DEPT
WHERE DNAME ='D3' ) ;

6. WAQTD ename , sal of all the employee earning more

than Scott and working in dept 20 .

SELECT ENAME , SAL

FROM EMP
WHERE DEPTNO = 20 AND SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='SCOTT' ) ;

7. WAQTD all the details of the employee working as a

Manager In the dept Accounting .

SELECT *
FROM EMP
WHERE JOB ='MANAGER' AND
DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE DNAME ='ACCOUNTING' ) ;

8. WAQTD all the details of the employee working in the

same designation as Miller and works in location New York
.

SELECT *
FROM EMP
WHERE JOB = ( SELECT JOB
FROM EMP
WHERE ENAME ='MILLER' ) AND DEPTNO = (
SELECT DEPTNO FROM DEPT WHERE LOC ='NEW
YORK' ) ;

9. WAQTD number of employees working as a clerk in the

same deptno as SMITH and earning more than KING hired
after MARTIN in the location BOSTON .

SELECT COUNT(*)
FROM EMP
WHERE JOB ='CLERK' AND
DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME ='SMITH') AND
SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='KING' ) AND
HIREDATE > ( SELECT HIREDATE
FROM EMP
WHERE ENAME ='MARTIN' ) AND
DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE LOC ='BOSTON' ) ;

10. WAQTD maximum salary given to a person working in

New Section 1 Page 2
 DALLAS .

SELECT MAX( SAL )

FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE LOC ='DALLAS' ) ;

ASSIGNMENT ON CASE 2 :

21. WAQTD DNAME OF THE EMPLOYEES WHOS NAME

IS SMITH
22. WAQTD DNAME AND LOC OF THE EMPLOYEE
WHOS ENAME IS KING
23. WAQTD LOC OF THE EMP WHOS EMPLOYEE NUMBER IS
7902
24. WAQTD DNAME AND LOC ALONG WITH DEPTNO OF
THE EMPLOYEE WHOS NAME ENDS WITH 'R' .
25. WAQTD DNAME OF THE EMPLOYEE WHOS
DESIGNATION IS PRESIDENT
26. WAQTD NAMES OF THE EMPLOYEES WORKING
IN ACCOUNTING DEPARTMENT
27. WAQTD ENAME AND SALARIES OF THE EMPLOYEES
WHO ARE WORKING IN THE LOCATION CHICAGO
28. WAQTD DETAILS OF THE EMPLOYEES WORKING IN SALES
29. WAQTD DETAILS OF THE EMP ALONG WITH
ANNUAL SALARY IF EMPLOYEES ARE WORKING IN
NEW YORK 30.WAQTD NAMES OF EMPLOYEES
WORKING IN OPERATIONS DEPARTMENT

ASSIGNMENT ON CASE 1 & 2

31. WAQTD NAMES OF THE EMPLOYEES EARNING

MORE THAN SCOTT IN ACCOUNTING DEPT
32. WAQTD DETAILS OF THE EMPLOYEES WORKING
AS MANAGER IN THE LOCATION CHICAGO
33. WAQTD NAME AND SAL OF THE EMPLOYEES
EARNING MORE THAN KING IN THE DEPT ACCOUNTING
34. WAQTD DETAILS OF THE EMPLOYEES WORKING
AS SALESMAN IN THE DEPARTEMENT SALES
35. WAQTD NAME , SAL , JOB , HIREDATE OF THE
EMPLOYEES WORKING IN OPERATIONS DEPARTMENT AND
HIRED BEFORE KING
36. DISPLAY ALL THE EMPLOYEES WHOSE
DEPARTMET NAMES ENDING 'S'.
37. WAQTD DNAME OF THE EMPLOYEES WHOS NAMES
HAS CHARACTER 'A' IN IT .
38. WAQTD DNAME AND LOC OF THE EMPLOYEES
WHOS SALARY IS RUPEES 800 .
39. WAQTD DNAME OF THE EMPLOYEES WHO
EARN COMISSION
40. WAQTD LOC OF THE EMPLOYEES IF THEY
EARN COMISSION IN DEPT 40

New Section 1 Page 3

MAX & MIN :
EID ENAME SAL DEPTNO
1 ALLEN 1000 20
2 BLAKE 2000 10
3 CLARK 3000 30
4 MILLER 1500 10
5 ADAMS 2500 20

1. WAQTD maximum salary of an employee .

SELECT MAX( SAL )

FROM EMP ;

2. WAQTD name of the employee getting maximum salary .

SELECT ENAME , MAX( SAL )

FROM EMP ;

SELECT ENAME
FROM EMP
WHERE SAL = MAX( SAL ) ;

SELECT ENAME
FROM EMP
WHERE SAL = ( SELECT MAX( SAL )
FROM EMP ) ;

3. WAQTD name and salary earned by the employee

getting Minimum salary .

SELECT ENAME , SAL

FROM EMP
WHERE SAL = ( SELECT MIN( SAL )
FROM EMP ) ;

ASSIGNMENT ON MAX & MIN :

41. WAQTD NAME OF THE EMPLOYEE EARNING

MAXIMUM SALARY
42. WAQTD NAME OF THE EMPLOYEE EARNING
MINIMUM SALARY
43. WAQTD NAME AND HIREDATE OF THE EMPLOYEE
HIRED BEFORE
ALL THE EMPLOYEES (FIRST EMP)
44. WAQTD NAME AND HIREDATE OF THE EMPLOYEES
HIRED AT THE LAST
45. WAQTD NAME, COMM OF THE EMPLOYEE WHO
EARNS MIN COMISSION
46. WAQTD NAME, SAL AND COMM OF THE
EMPLOYEE EARNING MAXIMUM
COMISSION

New Section 1 Page 4

47. WAQTD DETAILS OF THE EMPLOYEE WHO HAS
GREATEST EMPNO
48. WAQTD DETAILS OF THE EMPLOYEES HAVING THE
LEAST HIREDATE
49. WAQTD DETAILS OF THE EMPLOYEES EARNING
LEAST ANNUAL SALARY
50. WAQTD NAME , ANNUAL SALARY OF THE EMPLOYEES IF
THEIR ANNUAL SALARY IS MORE THAN ALL THE
SALESMAN

ASSIGNMENT ANSWERS ON CASE 2 :

21. WAQTD DNAME OF THE EMPLOYEES WHOS NAME

IS SMITH
SELECT DNAME
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME ='SMITH' ) ;

22. WAQTD DNAME AND LOC OF THE EMPLOYEE

WHOS ENAME IS KING
SELECT DNAME ,LOC
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME =''KING' ) ;

23. WAQTD LOC OF THE EMP WHOS EMPLOYEE NUMBER IS

7902
SELECT LOC
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE EMPNO=7902 ) ;

24. WAQTD DNAME AND LOC ALONG WITH DEPTNO OF

THE EMPLOYEE WHO’S NAME ENDS WITH ‘R’.
SELECT DNAME , LOC
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME LIKE '%R' ) ;

25. WAQTD DNAME OF THE EMPLOYEE WHOS

DESIGNATION IS PRESIDENT
SELECT DNAME
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE JOB = 'PRESIDENT' ) ;

26. WAQTD NAMES OF THE EMPLOYEES WORKING IN

New Section 1 Page 5

ACCOUNTING DEPARTMENT
SELECT ENAME
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE DNAME = 'ACCOUNTING' );

27. WAQTD ENAME AND SALARIES OF THE EMPLOYEES

WHO ARE WORKING IN
THE LOCATION ‘CHICAGO’
SELECT ENAME ,SAL
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE LOC = 'CHICAGO' );

28. WAQTD DETAILS OF THE EMPLOYEES WORKING IN SALES

SELECT *
FROM
EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE DNAME ='SALES' );

29. WAQTD DETAILS OF THE EMP ALONG WITH

ANNUAL SALARY IF EMPLOYEES
ARE WORKING IN NEW YORK
SELECT EMP.* , SAL*12
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE LOC ='NEW YORK' );

30. WAQTD NAMES OF EMPLOYEES WORKING

IN OPERATIONS DEPARTMENT
SELECT ENAME
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE DNAME ='OPERATIONS' );

ANSWERS ON CASE 1 & 2 :

31. WAQTD NAMES OF THE EMPLOYEES EARNING

MORE THAN SCOTT IN
ACCOUNTING DEPT
SELECT ENAME
FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='SCOTT' ) AND DEPTNO = ( SELECT DEPTNO
FROM DEPT

New Section 1 Page 6

 WHERE DNAME =
'ACCOUNTING');

32. WAQTD DETAILS OF THE EMPLOYEES WORKING

AS MANAGER IN THE
LOCATION CHICAGO
SELECT *
FROM
EMP
WHERE JOB ='MANAGER' AND DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE LOC
='CHICAGO');

33. WAQTD NAME AND SAL OF THE EMPLOYEES

EARNING MORE THAN KING
IN THE DEPT ACCOUNTING
SELECT ENAME ,SAL
FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='KING' ) AND DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE DNAME =
'ACCOUNTING');

34. WAQTD DETAILS OF THE EMPLOYEES WORKING

AS SALESMAN IN THE
DEPARTEMENT SALES
SELECT *
FROM
EMP
WHERE JOB ='SALESMAN' AND DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE DNAME
='SALES');

35. WAQTD NAME , SAL , JOB , HIREDATE OF THE

EMPLOYEES WORKING IN OPERATIONS DEPARTMENT AND
HIRED BEFORE KING
SELECT ENAME ,SAL , JOB , HIREDATE
FROM EMP
WHERE HIREDATE < ( SELECT HIREDATE
FROM EMP
WHERE ENAME ='KING') AND DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE DNAME
='OPERATIONS');
36. DISPLAY ALL THE EMPLOYEES WHOSE
DEPARTMET NAMES ENDING 'S'.
SELECT ENAME
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE DNAME LIKE '%S' );

New Section 1 Page 7

37. WAQTD DNAME OF THE EMPLOYEES WHOS NAMES
HAS CHARACTER ‘A’ IN IT .
SELECT DNAME
FROM DEPT
WHERE DEPTNO IN( SELECT DEPTNO
FROM EMP
WHERE ENAME LIKE '%A%' ) ;

38. WAQTD DNAME AND LOC OF THE EMPLOYEES

WHOS SALARY IS RUPEES 800 .
SELECT DNAME , LOC
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE SAL = 800) ;

39. WAQTD DNAME OF THE EMPLOYEES WHO

EARN COMISSION
SELECT DNAME
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE COMM IS NOT NULL );

40. WAQTD LOC OF THE EMPLOYEES IF THEY

EARN COMISSION IN DEPT 40
SELECT LOC
FROM DEPT
WHERE DEPTNO = 40 AND DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE COMM IS NOT NULL ) ;

SELECT LOC
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE COMM IS NOT NULL AND DEPTNO = 40 ) ;

ANSWERS ON MAX & MIN :

41. WAQTD NAME OF THE EMPLOYEE EARNING

MAXIMUM SALARY
SELECT ENAME
FROM EMP
WHERE SAL = ( SELECT MAX(SAL)
FROM EMP );

42. WAQTD NAME OF THE EMPLOYEE EARNING

MINIMUM SALARY
SELECT ENAME
FROM EMP
WHERE SAL = ( SELECT MIN(SAL)
FROM EMP );

New Section 1 Page 8

43. WAQTD NAME AND HIREDATE OF THE EMPLOYEE
HIRED BEFORE
ALL THE EMPLOYEES (FIRST EMP)
SELECT ENAME , HIREDATE
FROM EMP
WHERE HIREDATE = ( SELECT
MIN(HIREDATE) FROM EMP );

44. WAQTD NAME AND HIREDATE OF THE EMPLOYEES

HIRED AT THE LAST
SELECT ENAME , HIREDATE
FROM EMP
WHERE HIREDATE = ( SELECT
MAX(HIREDATE) FROM EMP );

45. WAQTD NAME, COMM OF THE EMPLOYEE WHO

EARNS MIN COMISSION
SELECT ENAME , COMM
FROM EMP
WHERE COMM= ( SELECT
MIN(COMM) FROM EMP );

46. WAQTD NAME, SAL AND COMM OF THE

EMPLOYEE EARNING MAXIMUM
COMISSION
SELECT ENAME ,SAL, COMM
FROM EMP
WHERE COMM= ( SELECT
MAX(COMM) FROM EMP );

47. WAQTD DETAILS OF THE EMPLOYEE WHO HAS

GREATEST EMPNO
SELECT *
FROM
EMP
WHERE EMPNO= ( SELECT MAX(EMPNO)
FROM EMP );

48. WAQTD DETAILS OF THE EMPLOYEES HAVING THE

LEAST HIREDATE
SELECT *
FROM EMP
WHERE EMPNO= ( SELECT MIN(EMPNO)
FROM EMP );

49. WAQTD DETAILS OF THE EMPLOYEES EARNING

LEAST ANNUAL SALARY
SELECT ENAME
FROM EMP
WHERE SAL*12= ( SELECT MIN(SAL*12)
FROM EMP );

50. WAQTD NAME , ANNUAL SALARY OF THE EMPLOYEES IF

New Section 1 Page 9

THEIR ANNUAL
SALARY IS MORE THAN ALL THE SALESMAN
SELECT ENAME , SAL*12
FROM EMP
WHERE SAL*12 > ( SELECT MAX(SAL*12)
FROM EMP
WHERE JOB ='SALESMAN' );
OR
SELECT ENAME , SAL*12
FROM EMP
WHERE SAL*12 > ALL ( SELECT SAL*12
FROM EMP
WHERE JOB ='SALESMAN' );

New Section 1 Page 10

New Section 1 Page 11