[go: up one dir, main page]
Scribd
Upload
46 views13 pages

Question On Sub Query

The document contains a series of SQL queries aimed at retrieving specific employee and department information from a database. It includes queries to display employees based on department names, maximum salaries, commission levels, and various conditions related to employee attributes. Additionally, it showcases some errors encountered during query execution and the corresponding corrections made.

Uploaded by

Venkatesh Kanna
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
46 views13 pages

Question On Sub Query

The document contains a series of SQL queries aimed at retrieving specific employee and department information from a database. It includes queries to display employees based on department names, maximum salaries, commission levels, and various conditions related to employee attributes. Additionally, it showcases some errors encountered during query execution and the corresponding corrections made.

Uploaded by

Venkatesh Kanna
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 13

QUESTIONS on SUBQUERY

1.DISPLAY ALL THE EMPLOYEES WHOSE DEPARTMET NAMES ENDING'S

SQL> SELECT * FROM EMP


2 WHERE DEPTNO=ANY(SELECT DEPTNO FROM DEPT
3 WHERE DNAME LIKE '%S');

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

SQL> SELECT ENAME FROM EMP


2 WHERE SAL > =(SELECT MAX(SAL) FROM EMP
3 WHERE DEPTNO=(SELECT DEPTNO FROM DEPT
4 WHERE DNAME='ACCOUNTING'));

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

SQL> SELECT DNAME FROM DEPT


2 WHERE DEPTNO=(SELECT DEPTNO FROM EMP
3 WHERE COMM =(SELECT MAX(COMM) FROM EMP
4 WHERE COMM IS NOT NULL));

4. QUERY TO DISPLAY THE EMPLOYEE NAMES WHOSE DEPARTMENT NAME HAS 2ND
CHARACTER AS'O'.

SQL> SELECT ENAME


FROM EMP
WHERE DEPTNO IN (
SELECT DEPTNO
FROM DEPT
WHERE DNAME = '_o%'
);

5. QUERY TO DISPLAY ALL THE EMPLOYEES WHO'S DEPT NUMBER IS SAME AS SCOTT.

SQL> SELECT ENAME


2 FROM EMP
3 WHERE DEPTNO = (
4 SELECT DEPTNO
5 FROM EMP
6 WHERE ENAME = 'SCOTT'
7 );

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

SQL> SELECT ENAME


2 FROM EMP
3 WHERE DEPTNO =(SELECT DEPTNO FROM EMP
4 WHERE DNAME IN ('OPERATION','ACCOUNTING'));

7.LIST THE EMPLOYEES WHO HAS SALARY GREATER THAN MILLER

SQL> SELECT ENAME


2 FROM EMP
3 WHERE SAL > (
4 SELECT SAL
5 FROM EMP
6 WHERE ENAME = 'MILLER'
7 );

8. LIST DEPARTMENT NAME HAVING ATLEAST 3 SALESMAN

SQL> SELECT DNAME FROM DEPT


2 WHERE DEPTNO= (SELECT DEPTNO FROM EMP
3 WHERE JOB='SALESMAN'
4 GROUP BY DEPTNO
5 HAVING COUNT(*)>=3);

9. DISPLAY THE DNAME OF AN EMPLOYEES WHO HAS NO REPORTING MANAGER

SQL> SELECT DNAME


2 FROM DEPT
3 WHERE DEPTNO = (
4 SELECT DEPTNO
5 FROM EMP
6 WHERE MGR IS NULL);

10. LIST ALL THE EMPLOYEES WHO ARE REPORTING TO JONES MANAGER

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.
13.DISPLAY LOCATION OF EMPLOYEE WHOSE SALARY IS MINIMUM SALARY BUT SALARY
IS GREATER THAN 2000
14. DISPLAY THE LOCATION OF AN EMPLOY
DEPARTMENT
15. DISPLAY THE DEPARTMENT S LOCATION
THA"
GREATER THAN FOUR
EMPLOYEES IN IT.
16. WRITE A QUERY TO DISPLAY ALL THE EMPLOYEE WHOSE JOB NOT SAME AS ALLEN
AND SALARY IS GREATER THAN MARTIN.

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jun 15 16:48:44 2025

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Enter user-name: SCOTT


Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> spool "C:\Users\dell\Downloads\SQL\New folder\48 question.txt"


SQL> /* 1.DISPLAY ALL THE EMPLOYEES WHOSE DEPARTMET NAMES ENDING 'S' */
SQL> SET PAGES 100 LINES 100
SQL> SELECT *FROM EMPWHERE DEPTNO IN ( SELECT DEPTNO FROM DEPT WHERE DNAME LIKE
'%S');
SELECT *FROM EMPWHERE DEPTNO IN ( SELECT DEPTNO FROM DEPT WHERE DNAME LIKE '%S')
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL> SELECT * FROM EMP


2 WHERE DEPTNO IN (SELECT DEPTNO IN DEPT
3
SQL>
SQL> SELECT * FROM EMP
2 WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT
3 WHERE DNAME LIKE '%S');

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7900 JAMES CLERK 7698 03-DEC-81 950
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

6 rows selected.

SQL> /* 2.QUERY TO DISPLAY THE EMPLOYEE NAMES WHO IS HAVING MAXIMUM SALARY IN
DEPTNAME "ACCOUNTING" */
SQL> SELECT ENAME, MAX(SAL) FROM EMP
2 WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT
3 WHERE DNAME='ACCOUNTING');
SELECT ENAME, MAX(SAL) FROM EMP
*
ERROR at line 1:
ORA-00937: not a single-group group function

SQL> SELECT ENAME, SAL FROM EMP


2 WHERE SAL=(SELECT MAX(SAL) FROM EMP
3 WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT
4 WHERE DNAME='ACCOUNTING');
WHERE SAL=(SELECT MAX(SAL) FROM EMP
*
ERROR at line 2:
ORA-00921: unexpected end of SQL command

SQL> SELECT ENAME


2 FROM EMP
3 WHERE SAL = (
4 SELECT MAX(SAL)
5 FROM EMP
6 WHERE DEPTNO = (
7 SELECT DEPTNO
8 FROM DEPT
9 WHERE DNAME = 'ACCOUNTING'
10 );
SELECT MAX(SAL)
*
ERROR at line 4:
ORA-00921: unexpected end of SQL command

SQL> SELECT ENAMEFROM EMPWHERE SAL = ( SELECT MAX(SAL) FROM EMP WHERE
DEPTNO = ( SELECT DEPTNO FROM DEPT WHERE DNAME = 'ACCOUNTING' ));
SELECT ENAMEFROM EMPWHERE SAL = ( SELECT MAX(SAL) FROM EMP WHERE DEPTNO =
( SELECT DEPTNO FROM DEPT WHERE DNAME = 'ACCOUNTING' ))
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> SELECT ENAME FROM EMP


2 2 WHERE SAL > =(SELECT MAX(SAL) FROM EMP
3 3 WHERE DEPTNO=(SELECT DEPTNO FROM DEPT
4 4 WHERE DNAME='ACCOUNTING'));
2 WHERE SAL > =(SELECT MAX(SAL) FROM EMP
*
ERROR at line 2:
ORA-00933: SQL command not properly ended

SQL> SELECT ENAME FROM EMP


2 WHERE SAL > =(SELECT MAX(SAL) FROM EMP
3 WHERE DEPTNO=(SELECT DEPTNO FROM DEPT
4 WHERE DNAME='ACCOUNTING'));

ENAME
----------
KING

SQL> SELECT ENAME


2 FROM EMP
3 WHERE SAL = (
4 SELECT MAX(SAL)
5 FROM EMP
6 WHERE DEPTNO = (
7 SELECT DEPTNO
8 FROM DEPT
9 WHERE DNAME = 'ACCOUNTING'
10 )
11 )
12 AND DEPTNO = (
13 SELECT DEPTNO
14 FROM DEPT
15 WHERE DNAME = 'ACCOUNTING'
16 );

ENAME
----------
KING

SQL> /* QUERY TO DISPLAY THE DEPT NAME WHO IS HAVING HIGHEST COMMISSION
SQL>
SQL> /* QUERY TO DISPLAY THE DEPT NAME WHO IS HAVING HIGHEST COMMISSION
SQL> */
SQL> SELECT DNAME FROM DEPT
2 WHERE DEPTNO IN (SELECT DEPTNO FROM EMP
3 WHERE COMM=(SELECT MAX(COMM) FROM EMP) AND COMM IS NOT NULL;
WHERE COMM=(SELECT MAX(COMM) FROM EMP) AND COMM IS NOT NULL
*
ERROR at line 3:
ORA-00907: missing right parenthesis

SQL> SELECT DNAME FROM DEPT


2 WHERE DEPTNO IN (SELECT DEPTNO FROM EMP
3 WHERE COMM=(SELECT MAX(COMM) FROM EMP) AND COMM IS NOT NULL);

DNAME
--------------
SALES

SQL> /* QUERY TO DISPLAY THE EMPLOYEE NAMES WHOSE DEPARTMENT NAME HAS 2NDCHARACTER
AS 'O'. */
SQL> SELECT ENAMEFROM EMPWHERE DEPTNO IN ( SELECT DEPTNO FROM DEPT WHERE
SUBSTR(DNAME, 2, 1) = 'O');
SELECT ENAMEFROM EMPWHERE DEPTNO IN ( SELECT DEPTNO FROM DEPT WHERE
SUBSTR(DNAME, 2, 1) = 'O')
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> SELECT ENAMEFROM EMPWHERE DEPTNO IN ( SELECT DEPTNO FROM DEPT WHERE
SUBSTR(DNAME, 2, 1) = 'O');
SELECT ENAMEFROM EMPWHERE DEPTNO IN ( SELECT DEPTNO FROM DEPT WHERE
SUBSTR(DNAME, 2, 1) = 'O')
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> SELECT ENAME FROM EMP WHERE DEPTNO IN ( SELECT DEPTNO FROM DEPT WHERE
SUBSTR(DNAME, 2, 1) = 'O');

no rows selected

SQL> /* QUERY TO DISPLAY ALL THE EMPLOYEES WHO'S DEPT NUMBER IS SAME AS SCOTT. */
SQL> SELECT * FROM EMP
2 WHERE DEPTNO IN(SELECT DEPTNO FROM EMP
3 WHERE ENAME ='SCOTT');

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7566 JONES MANAGER 7839 02-APR-81 2975
20
7369 SMITH CLERK 7902 17-DEC-80 800
20

SQL> /* 6.QUERY TO DISPLAY ALL THE EMPLOYEES IN 'OPERATIONS AND ACCOUNTING' DEPT.
*/
SQL> SELECT * FROM EMP
2 WHERE DEPTNO IN (SELECT DEPTNO IN DEPT
3 WHERE DNAME
4 IN ('OPERATIONS','ACCOUNTING'));
WHERE DEPTNO IN (SELECT DEPTNO IN DEPT
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected

SQL> SELECT * FROM EMP


2 WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT
3 WHERE DNAME
4 IN ('OPERATIONS','ACCOUNTING'));

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7839 KING PRESIDENT 17-NOV-81 5000
10
7934 MILLER CLERK 7782 23-JAN-82 1300
10

SQL> /* 7.LIST THE EMPLOYEES WHO HAS SALARY GREATER THAN MILLER */
SQL> SELECT * FROM EMP
2 WHERE SAL>(SELECT SAL FROM EMP
3 WHERE ENAME='MILLER');

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
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
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20

8 rows selected.

SQL> SELECT SAL FROM EMP


2 WHERE ENAME='MILLER';

SAL
----------
1300

SQL> /* LIST DEPARTMENT NAME HAVING ATLEAST 3 SALESMAN */


SQL> SELECT DNAME FROM DEPT
2 WHERE DEPTNO IN (SELECT DEPTNO FROM EMP
3 WHERE COUNT(*)>=3 AND JOB='SALESMAN');
WHERE COUNT(*)>=3 AND JOB='SALESMAN')
*
ERROR at line 3:
ORA-00934: group function is not allowed here

SQL> SELECT DNAME FROM DEPT


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

DNAME
--------------
SALES

SQL>
SQL> /* DISPLAY THE DNAME OF AN EMPLOYEES WHO HAS NO REPORTING MANAGER. */
SQL> SELECT DNAME FROM DEPT
2 WHERE DEPTNO IN (SELECT DEPTNO FROM EMP
3 WHERE MGR IS NULL);

DNAME
--------------
ACCOUNTING

SQL> /* 10. LIST ALL THE EMPLOYEES WHO ARE REPORTING TO JONES MANAGER */
SQL> SELECT * FROM EMP
2 WHERE MGR IN(SELECT MGR FROM EMP
3 WHERE ENAME='JONES');

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
SQL> /*
SQL> 11. LIST EMPLOYEES FROM RESEARCH&ACCOUNTING HAVING ATLEAST 2 REPORTING. */
SQL> SELECT * FROM EMP
2 WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT
3 WHERE DNAME IN ('ACCOUNTING','RESEARCH'));

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7839 KING PRESIDENT 17-NOV-81 5000
10
7934 MILLER CLERK 7782 23-JAN-82 1300
10
7566 JONES MANAGER 7839 02-APR-81 2975
20
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7369 SMITH CLERK 7902 17-DEC-80 800
20
7788 SCOTT ANALYST 7566 19-APR-87 3000
20

8 rows selected.

SQL> SELECT * FROM EMP


2 WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT
3 WHERE DNAME IN ('ACCOUNTING','RESEARCH')
4 GROUP BY DEPTNO
5 HAVING COUNT(*)>=3);

no rows selected

SQL> SELECT *
2 FROM EMP
3 WHERE EMPNO IN (
4 SELECT MGR
5 FROM EMP
6 WHERE MGR IS NOT NULL
7 GROUP BY MGR
8 HAVING COUNT(*) >= 2
9 )
10 AND DEPTNO IN (
11 SELECT DEPTNO
12 FROM DEPT
13 WHERE DNAME IN ('ACCOUNTING', 'RESEARCH')
14 );

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7839 KING PRESIDENT 17-NOV-81 5000
10
7566 JONES MANAGER 7839 02-APR-81 2975
20

SQL> /* 12. DISPLAY THE DEPARTNAME OF THE EMPLOYEE WHOSE NAME DOES NOT STARTS WITH
SQL> S AND SALARY BETWEEN 1500 TO 3000. */
SQL> SELECT ENAME FROM EMP
2 WHERE
3
SQL> SELECT DNAME FROM DEPT WHERE
2 DEPTNO IN (SELECT DEPTNO FROM EMP
3 WHERE ENAME LIKE NOT 'S%' AND SAL BETWEEN 1500 AND 3000);
WHERE ENAME LIKE NOT 'S%' AND SAL BETWEEN 1500 AND 3000)
*
ERROR at line 3:
ORA-00936: missing expression

SQL> SELECT DNAME FROM DEPT WHERE


2 DEPTNO IN (SELECT DEPTNO FROM EMP
3 WHERE ENAME NOT LIKE 'S%' AND SAL BETWEEN 1500 AND 3000);

DNAME
--------------
ACCOUNTING
RESEARCH
SALES

SQL> /* 14. DISPLAY THE LOCATION OF AN EMPLOY


SQL> DEPARTMENT */
SQL> SELECT LOC FROM DEPT
2
SQL> /* 13.DISPLAY LOCATION OF EMPLOYEE WHOSE SALARY IS MINIMUM SALARY BUT SALARYIS
GREATER THAN 2000 */
SQL> SELECT LOC FROM DEPT
2 WHERE DEPTNO IN (SELECT DEPTNO FROM EMP
3 WHERE SAL < (SELECT SAL FROM EMP
4 WHERE SAL>2000));
WHERE SAL < (SELECT SAL FROM EMP
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row

SQL> SELECT LOC FROM DEPT


2 WHERE DEPTNO IN (SELECT DEPTNO FROM EMP
3 WHERE SAL = (SELECT MIN SAL FROM EMP
4 WHERE SAL>2000));
WHERE SAL = (SELECT MIN SAL FROM EMP
*
ERROR at line 3:
ORA-00904: "MIN": invalid identifier

SQL> SELECT LOC FROM DEPT


2 WHERE DEPTNO IN (SELECT DEPTNO FROM EMP
3 WHERE SAL < (SELECT MIN(SAL) FROM EMP
4 WHERE SAL>2000));

LOC
-------------
CHICAGO
DALLAS
NEW YORK

SQL> /* 14. DISPLAY THE LOCATION OF AN EMPLOYEE IN ACCOUNTING DEPARTMENT. */


SQL> SELECT LOC FROM DEPT
2 WHERE DEPTNO IN(SELECT DEPTNO FROM EMP
3 WHERE D
4
SQL> SELECT LOC FROM DEPT
2 WHERE DNAME='ACCOUNTING';

LOC
-------------
NEW YORK

SQL> /* 15. DISPLAY THE DEPARTMENT 'S LOCATION THAT IS HAVING GREATER THAN
FOUREMPLOYEES IN IT. */
SQL> SELECT LOC FROM DEPT
2 WHERE DEPTNO IN(SELECT DEPTNO FROM EMP
3 GROUPBY DEPTNO
4 HAVING COUNT(*)>=4);
GROUPBY DEPTNO
*
ERROR at line 3:
ORA-00907: missing right parenthesis

SQL> SELECT LOC FROM DEPT


2 WHERE DEPTNO IN(SELECT DEPTNO FROM EMP
3 GROUP BY DEPTNO
4 HAVING COUNT(*)>=4);

LOC
-------------
CHICAGO
DALLAS

SQL> /* 16. WRITE A QUERY TO DISPLAY ALL THE EMPLOYEE WHOSE JOB NOT SAME AS
ALLENAND SALARY IS GREATER THAN MARTIN. */
SQL> SELECT * FROM EMP
2 WHERE JOB=(SELECT JOB FROM EMP
3 WHERE ENAME='ALLEN) AND SAL>(SELECT SAL FROM EMP WHERE ENAME='MARTIN');
ERROR:
ORA-01756: quoted string not properly terminated

SQL> SELECT * FROM EMP


2 WHERE JOB=(SELECT JOB FROM EMP
3 WHERE ENAME='ALLEN') AND SAL>(SELECT SAL FROM EMP WHERE ENAME='MARTIN');

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

SQL> /* 17. DISPLAY ALL THE EMPLOYEES WHO IS HAVING LOCATION IS SAME AS
ADAM'SMANAGER? */
SQL> SELECT * FORM EMP
2 WHERE DEPTNO IN( SELECT DEPTNO FROM DEPT
3 WHERE
4
SQL> SELECT * FORM EMP
2 WHERE
3
SQL> SELECT *
2 FROM EMP
3 WHERE DEPTNO IN (
4 SELECT DEPTNO
5 FROM DEPT
6 WHERE LOC = (
7 SELECT LOC
8 FROM DEPT
9 WHERE DEPTNO = (
10 SELECT DEPTNO
11 FROM EMP
12 WHERE EMPNO = (
13 SELECT MGR
14 FROM EMP
15 WHERE ENAME = 'ADAMS'
16 )
17 )
18 )
19 );

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7369 SMITH CLERK 7902 17-DEC-80 800
20
7788 SCOTT ANALYST 7566 19-APR-87 3000
20

SQL> /* 18. DISPLAY THE JOB, MANAGER NUMBER OF EMPLOYEES WHO IS WORKING FOR JONES
*/
SQL> SELECT JOB,MGR FROM EMP
2 WHERE MGR = (SELECT MGR FROM EMP
3 WHERE EMPNO IN (SELECT EMPNO FROM EMP
4 WHER
5
SQL> SELECT JOB, MGR
2 FROM EMP
3 WHERE MGR = (
4 SELECT EMPNO
5 FROM EMP
6 WHERE ENAME = 'JONES'
7 );

JOB MGR
--------- ----------
ANALYST 7566
ANALYST 7566

SQL> /* 19. DISPLAY THE EMPLOYEE NAMES, HIRE DATE, COMMISSION OF FORD'S MANAGER */
SQL> SELECT * FROM EMP
2 WHERE
3
SQL> SELECT ENAME,COMM, HIREDATE FROM EMP
2 WHERE EMPNO=(SELECT MGR FROM EMP
3 WHERE ENAME='FORD');

ENAME COMM HIREDATE


---------- ---------- ---------
JONES 02-APR-81

SQL> /* 20. DISPLAY THE NUMBER OF EMPLOYEES WHO ARE GETTING SALARY LESS THAN
BLAKE'S MANAGER */
SQL> SELECT * FROM EMP
2 WHERE SAL<(SELECT SAL FROM EMP
3 WHERE EMPNO=(SELECT MGR FROM EMP
4 WHERE ENAME='BLAKE');
WHERE SAL<(SELECT SAL FROM EMP
*
ERROR at line 2:
ORA-00921: unexpected end of SQL command

SQL> SELECT COUNT(*)


2 FROM EMP
3 WHERE SAL < (
4 SELECT SAL
5 FROM EMP
6 WHERE EMPNO = (
7 SELECT MGR
8 FROM EMP
9 WHERE ENAME = 'BLAKE'
10 )
11 );

COUNT(*)
----------
13

SQL> /* 21. LIST EMPLOYEES WHO LOCATED IN CHICAGO AND THEIR COMMISSION IS ZERO */
SQL> SELECT * FROM EMP
2 WHERE DEPTNO IN ( SELECT LOC FROM DEPT
3 WHERE LOC='CHICAGO') AND COMM IS NULL;
WHERE DEPTNO IN ( SELECT LOC FROM DEPT
*
ERROR at line 2:
ORA-01722: invalid number

SQL> SELECT * FROM EMP


2 WHERE DEPTNO IN ( SELECT DEPTNO FROM DEPT
3 WHERE LOC='CHICAGO') AND COMM IS NULL;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7900 JAMES CLERK 7698 03-DEC-81 950
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

SQL> /* 22. LIST EMPLOYEES WHO WORK FOR SALES DEPARTMENT AND THEIR SALARY > AVG
SALARY OF THEIR DEPT */
SQL> SELECT

You might also like