[go: up one dir, main page]
Scribd
Upload
2K views4 pages

Sub Queries Ans

This document contains 20 SQL queries with varying levels of complexity. The queries retrieve employee, department, and other data from database tables. They use functions like SELECT, FROM, WHERE, IN, LIKE, MAX, MIN, AVG, and comparisons to filter records and return requested fields. The goal is to display specific records that meet the criteria defined in each query.

Uploaded by

Vijay Jadhav
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2K views4 pages

Sub Queries Ans

This document contains 20 SQL queries with varying levels of complexity. The queries retrieve employee, department, and other data from database tables. They use functions like SELECT, FROM, WHERE, IN, LIKE, MAX, MIN, AVG, and comparisons to filter records and return requested fields. The goal is to display specific records that meet the criteria defined in each query.

Uploaded by

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

1.

DISPLAY ALL THE EMPLOYEES WHOSE DEPARTMET


NAMES ENDING 'S'
SELECT * FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE DNAME LIKE '%S');

2.QUERY TO DISPLAY THE EMPLOYEE NAMES WHO IS


HAVING MAXIMUM SALARY IN DEPT NAME
"ACCOUNTING"

SELECT * FROM EMP WHERE SAL IN(SELECT MAX(SAL) FROM EMP WHERE DEPTNO IN(SELECT
DEPTNO FROM DEPT WHERE DNAME='ACCOUNTING'));

3.QUERY TO DISPLAY THE DEPT NAME WHO IS HAVING


HIGHEST COMMISSION

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

4. QUERY TO DISPLAY THE EMPLOYEE NAMES WHOSE


DEPARTMENT NAME HAS 2ND CHARACTER AS 'O'.
SELECT * FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE DNAME LIKE '_O%');

5. QUERY TO DISPLAY ALL THE EMPLOYEES WHO’S DEPT


NUMBER IS SAME AS SCOTT.
SELECT * FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM EMP WHERE ENAME='SCOTT');

6. QUERY TO DISPLAY ALL THE EMPLOYEES IN


'OPERATIONS AND ACCOUNTING' DEPT.

SELECT * FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE DNAME
IN('ACCOUNTING','OPERATIONS'));

7.LIST THE EMPLOYEES WHO HAS SALARY GREATER


THAN MILLER.

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

8. WRITE A QUERY TO DISPLAY ALL THE EMPLOYEE


WHOSE JOB NOT SAME AS ALLEN AND SALARY IS
GREATER THAN MARTIN.
SELECT * FROM EMP WHERE JOB NOT IN(SELECT JOB FROM EMP WHERE ENAME='ALLEN') AND SAL
>(SELECT SAL FROM EMP WHERE ENAME='MARTIN');

9. DISPLAY ALL THE EMPLOYEES WHO IS HAVING


LOCATION IS SAME AS ADAM'S MANAGER?

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

10. 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');

11. DISPLAY EMPLOYEE DETAILS WHO ARE REPORTING TO


BLAKE AND
HAVE COMMISSION WITHOUT USING NULL OR NOT NULL.

SELECT * FROM EMP WHERE MGR IN(SELECT EMPNO FROM EMP WHERE ENAME='BLAKE') AND
COMM > 0;

SELECT * FROM EMP WHERE MGR IN(SELECT EMPNO FROM EMP WHERE ENAME='BLAKE') AND
COMM >=0;

12: 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.
SELECT * FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES') AND
HIREDATE<(SELECT MAX(HIREDATE) FROM EMP) AND COMM > 0;

13: DISPLAY DEPARTMENT NAMES OF EMPLOYEE'S WHOSE


SALARY IS
GREATER THAN AVERAGE SALARY OF ALL THE CLERK'S
SELECT DNAME FROM DEPT WHERE DEPTNO IN(SELECT DEPTNO FROM EMP WHERE SAL>(SELECT
AVG(SAL) FROM EMP WHERE JOB='CLERK'));

14: QUERY TO DISPLAY EMPLOYEE NAMES WHO IS


HAVING MINIMUM
SALARY IN DEPARTMENT RESEARCH.
SELECT * FROM EMP WHERE SAL=( SELECT MIN(SAL) FROM EMP WHERE DEPTNO=(SELECT DEPTNO
FROM DEPT WHERE DNAME='RESEARCH'));

15: WRITE A QUERY TO DISPLAY EMPLOYEE NAME,


JOB,LOCATION OF
ALL EMPLOYEES WHO ARE WORKING AS MANAGER AND
WORKS AT
CHICAGO.
SELECT

16: LIST EMPLOYEES WHO HAVE COMMISSION GREATER


THAN
MAXIMUM SALARY OF ALL THE SALESMAN AND WHO DO
NOT REPORT
TO KING
SELECT * FROM EMP WHERE COMM > (SELECT MAX(SAL) FROM EMP WHERE JOB='SALESMAN')
AND MGR<>(SELECT EMPNO FROM EMP WHERE ENAME='KING');

17: DISPLAY THE NAMES OF EMPLOYEES WHO EARN


HIGHEST SALARY
IN THEIR RESPECTIVE JOBS.

SELECT * FROM EMP WHERE SAL IN(SELECT MAX(SAL) FROM EMP WHERE JOB IN('CLERK'))OR SAL
IN(SELECT MAX(SAL) FROM EMP WHERE JOB IN('SALESMAN')) OR SAL
IN(SELECT MAX(SAL) FROM EMP WHERE JOB IN('ANALYST'));
18: DISPLAY THE EMPLOYEE NUMBER AND NAME OF
EMPLOYEE
WORKING AS CLERK AND EARNING HIGHEST SALARY
AMONG CLERKS.
SELECT EMPNO,ENAME FROM EMP WHERE JOB='CLERK' AND SAL IN(SELECT MAX(SAL) FROM EMP
WHERE JOB IN('CLERK'));

19: LIST THE EMPLOYEE DEPTNAME AND LOC OF ALL THE


EMPLOYEES
WHO ARE CLERK ,REPORTING TO BLAKE AND SALARY IS
LESSER THAN
MARTIN SALARY.
SELECT DEPTNO,LOC FROM DEPT WHERE DEPTNO IN( SELECT DEPTNO FROM EMP WHERE
JOB='CLERK' AND MGR=(SELECT EMPNO FROM EMP WHERE ENAME='BLAKE') AND SAL < (SELECT
SAL FROM EMP WHERE ENAME='MARTIN'));

20: DISPLAY NUMBER OF EMPLOYEES WHOSE


COMMISSION IS MORE
THAN SALARY.
SELECT

You might also like