QUERIES ON SUB QUERY

SL_NO CASE 1

1. QUERY TO DISPLAY THE EMPLOYEE NAMES WHOSE NAME AS 2ND

CHARACTER SHOULD BE 'O'.
2. QUERY TO DISPLAY ALL THE EMPLOYEES WHO’S DEPT NUMBER IS
SAME AS SCOTT.
3. LIST THE EMPLOYEES WHO HAS SALARY GREATER THAN MILLER

4. DISPLAY THE LOCATION OF AN EMPLOYEE IN ACCOUNTING

DEPARTMENT.
5. WRITE A QUERY TO DISPLAY ALL THE EMPLOYEE WHOSE JOB NOT
SAME AS ALLEN AND SALARY IS GREATER THAN MARTIN
6. DISPLAY ALL THE EMPLOYEES WHO IS HAVING LOCATION IS SAME AS
ADAM'S MANAGER
7. LIST THE EMPLOYEES WHO JOINED AFTER 2 YEARS OF FIRST
EMPLOYEE OF THE COMPANY AND MORE THAN BLAKE SALARY
8. LIST ALL THE EMPLOYEES WHOSE JOB IS SAME AS JONES AND THEIR
SALARY LESSER THAN SCOTT
9. DISPLAY ALL THE EMPLOYEES OF DEPARTMENT 30, 20 WITH THERE
ANUAL SALARY AND HAVING ATLEAST 3 EMPLOYEES
10. DISPLAY ALL THE EMPLOYEES WHO ARE EARN LESS THAN ANY OF
THE SALESMAN
11. DISPLAY ALL THE EMPLOYEES WHO ARE JOINED BEFORE THE LAST
PERSON?
12. DISPLAY ALL THE EMPLOYEES WHO ARE EARNING MORE THAN ANY
OF THE MANAGER.
13. LIST EMPLOYEES WHO JOINED AFTER 4 YEARS OF 1ST EMPLOYEE OF
THE COMPANY AND LESS THAN BLAKE SALARY.
14. DISPLAY THE DEPARTMENT INFORMATION OF EMPLOYEE WHO IS
WORKING FOR NEW YORK LOCATION
15. DISPLAY THE EMPLOYEE DETAILS WITH THEIR ANNUAL SALARY
WHO EARN MAXIMUM COMMISSION
16. DISPLAY THE LAST EMPLOYEE RECORD WITH 25% HIKE IN SALARY

17. DISPLAY LAST EMPLOYEE RECORD ACCORDING TO EMPNO.

18. DISPLAY ALL THE EMPLOYEE WHOSE SALARY IS GREATER THAN

AVERAGE SALARY OF DEPARTMENT 30.
19. DISPLAY THE DEPARTMENT NAMES THAT ARE HAVING ATLEAST ONE
L IN IT.
20.
DISPLAY ALL THE EMPLOYEES WHO ARE JOINED AFTER BLAKE.
21. WRITE A QUERY TO DISPLAY THE EMPLOYEE INFORMATION WHO IS
NOT TAKING COMMISSION AND JOINED COMPANY AFTER JULY 83.
22. DISPLAY DEPARTMENT WISE MINIMUM SALARY WHICH IS LESS THAN
AVERAGE SALARY OF EMPLOYEES.

Page 1
QUERIES ON SUB QUERY

23. DISPLAY THE NAMES OF EMPLOYEE FROM DEPARTMENT NUMBER 10

WITH SALARY GREATER THAN THAT OF ALL EMPLOYEE WORKING IN
OTHER DEPARTMENTS
24. DISPLAY THE NAMES OF EMPLOYEES WHO EARN HIGHEST SALARY IN
THEIR RESPECTIVE JOBS.
25. DISPLAY THE EMPLOYEE NUMBER AND NAME OF EMPLOYEE
WORKING AS CLERK AND EARNING HIGHEST SALARY AMONG
CLERKS.
26. DISPLAY ALL THE EMPLOYEES WHOSE SALARY IS GREATER THAN
AVG SAL OF DEPARTMENT 20
27. DISPLAY EMPNO, ENAME, JOB, WHOSE JOB HAS 'E' IN IT AND DISPLAY
EMPNO IN DESCENDING ORDER.
28. DISPLAY DETAILS OF SMITH AND EMPLOYEES WORKING AS
PRESIDENT ALONG WITH HIKE OF 35% IN SALARY.
29. DISPLAY NUMBER OF EMPLOYEES WHOSE COMMISSION IS MORE
THAN SALARY.
30. LIST THE EMPLOYEES WHOSE DAILY SALARY IS GREATER THAN 1500
AND WHO ARE JOINED BEFORE 1982 ONLY.
31. DISPLAY NUMBER OF EMPLOYEES WHOSE COMMISSION IS MORE
THAN SALARY.
32. DISPLAY ALL THE EMPLOYEES WHOSE NAME START WITH 'S' AND
HAVING SALARY MORE THAN 'ALLEN' AND LESS THAN FORD.
33. DISPLAY ALL THE CLERKS AND ANALYST WHO ARE NOT WORKING
FOR 'DALLAS'
34. DISPLAY MAXIMUM SALARY OF 'SALES' DEPARTMENT

35. DISPLAY ALL THE EMPLOYEES WHO ARE EARNING MORE THAN ANY
OF THE MANAGER(JOB)
36. SELECT EMPNO, JOB AND SALARY OF ALL THE ANALYST WHO ARE
EARNING MORE THAN ANY OF THE MANAGER(JOB)
37. DISPLAY ALL THE EMPLOYEES WHOSE SALARY IS GREATER THAN
AVG SAL OF DEPARTMENT 20
38.
DISPLAY ALL THE EMPLOYEES WHO GETS MAXIMUM SALARY.
39. DISPLAY FIRST EMPLOYEE RECORD BASED ON HIREDATE

SL_NO CASE 2

1. DISPLAY ALL THE EMPLOYEES WHOSE DEPARTMET NAMES ENDING

'S'
2. QUERY TO DISPLAY THE EMPLOYEE NAMES WHO IS HAVING
MAXIMUM SALARY IN DEPT NAME "ACCOUNTING"
3. QUERY TO DISPLAY THE DEPT NAME WHO IS HAVING HIGHEST
COMMISSION
4. QUERY TO DISPLAY ALL THE EMPLOYEES IN 'OPERATIONS AND
ACCOUNTING' DEPT.
5. LIST DEPARTMENT NAME HAVING ATLEAST 3 SALESMAN

Page 2
QUERIES ON SUB QUERY

6.
DISPLAY ALL EMPLOYESS WHO DO NOT HAVE ANY REPORTER.
7. DISPLAY THE DEPARTNAME OF THE EMPLOYEE WHOSE NAME DOES
NOT STARTS WITH S AND SALARY BETWEEN 1500 TO 3000.
8. DISPLAY LOCATION OF EMPLOYEE WHOSE SALARY IS MINIMUM
SALARY BUT SALARY IS GREATER THAN 2000
9. DISPLAY THE DEPARTMENT LOCATION THAT IS HAVING GREATER
THAN FOUR EMPLOYEES IN IT.
10. LIST EMPLOYEES WHO LOCATED IN CHICAGO AND THEIR
COMMISSION IS ZERO.
11. LIST EMPLOYEES WHO WORK FOR SALES DEPARTMENT AND THEIR
SALARY GREATER THAN AVERAGE SALARY OF THEIR DEPARTMENT.
12. LIST EMPLOYEES WHO ARE WORKING IN RESEARCH DEPARTMENT
AND THEY ARE MANAGER.
13. DISPLAY DEPARTMENT NAME OF THE EMPLOYEES WHO EARN
COMMISSION
14. DISPLAY LOCATION OF EMPLOYEES, WHOSE NAME DOESN'T START
WITH A AND SALARY BETWEEN 1000 AND 3000.
15. 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
16. DISPLAY DEPARTMENT NAMES OF EMPLOYEE'S WHOSE SALARY IS
GREATER THAN AVERAGE SALARY OF ALL THE CLERK'S
17. DISPLAY THE DEPARTMENT NUMBER WHO WORKING IN SALES
DEPARTMENT AND THEY ARE MANAGER
18. DISPLAY HIREDATE AND JOB OF ALL THE EMPLOYEES WORKING FOR
SALES
19. DISPLAY LOCATION AND DNAME OF EMPLOYEE WHO WORKING AS
PRESIDENT
20. DISPLAY THE DNAME OF EMPLOYEES WHOES SALARY IS MAXIMUM
SALARY BUT LESSER THAN 3000
21. DISPLAY THE NUMBER OF EMPLOYEES WHO WORK FOR RESEARCH
DEPT AND THEIR SALARY IS LESSER THAN ONE OF THE SALARY IN
DEPARTMENT IN 10.
22. DISPLAY THE DNAME THAT ARE HAVING CLERK IN IT

23. LIST THE DEPT NAME THAT ARE HAVING AT LEAST 3 EMPLOYEES BUT
NOT MORE THAN 5 EMPLOYEES IN IT
24. SELECT THE EMPLOYEES WHOSE DNAME IS HAVING AT LEAST TWO
'E' IN IT.
25. DISPLAY ENAME,SAL OF EMPLOYEES WHO ARE EARNING MORE THAN
ANY OF THE ANALYST.
26.
SELECT ALL THE EMPLOYEES WHO ARE WORKING FOR CHICAGO
27. QUERY TO DISPLAY EMPLOYEE NAMES WHO IS HAVING MINIMUM
SALARY IN DEPARTMENT RESEARCH.
28.
LIST THE DEPARTMENT NAMES THAT ARE HAVING SALESMAN.

Page 3
QUERIES ON SUB QUERY

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

EMPLOYES IN IT
30. DISPLAY THE LOCATION OF ALL THE DEAPRTMENTS WHICH HAVE
EMPLOYEES JOINED IN THE YEAR 81
31. DISPLAY ALL THE EMPLOYEE INFORMATION WHO ARE LIVING IN A
LOCATION WHICH IS HAVING AT LEAST 2 'O' IN IT.
32. LIST THE NUMBER OF EMPLOYEES WHOSE JOB IS SALESMAN
WORKING FOR NEWYORK AND CHICAGO
33. LIST THE DEPARTMENT NAMES IN WHICH THE EMPLOYEES ARE HIRED
BETWEEN 1ST OF JAN 1981 AND 31ST DEC 1982 WITH SALARY MORE
THAN 1800.
34. LIST EMPLOYEES WHO WORKS FOR ACCOUNTING DEPARTMENT AND
THEIR SALARY GREATER THAN AVERAGE SALARY OF THEIR
DEPARTMENT
35. DISPLAY LOCATION OF THE EMPLOYEE WHO EARN COMMISSION

36. DISPLAY LOC AND DNAME WHOSE JOB IS MANAGER AND HAS SAL
LESS THAN CLERK.
37. DISPLAY EMPLOYEES LOCATION WHO HAS SOME COMMISSION

38. DISPLAY AVG SALARY OF ALL EMPLOYEES WHOSE DEPT NAME IS

ACCOUNTING???
39. DISPLAY ALL THE EMPLOYEES WHOSE JOB SAME AS 'SMITH' AND
DEPARTMENT SAME AS 'JONES' AND SALARY MORE THAN 'TURNER'
40. SELECT ENAME OF EMPLOYEE WHO EARNS 2ND MAX SALARY AND
WORKS FOR LOCATION DALLAS.
41. SELECT THE DEPARTMENT NAME AND LOCATION OF ALL THE
EMPLOYEES WORKING FOR CLARK.
42.
SELECT ALL THE EMPLOYEES WORKING FOR DALLAS

Page 4