Sub Queries
Single row:
1. Employees whose salary is greater than the salary of 'JONES'.
SELECT*
FROM emp
WHERE sal > (SELECT sal
FROM emp
WHERE ename = 'JONES')
2. Name and salary of employees whose salary is greater than the salary of the employee with empno 7566.
SELECT ename, sal
FROM emp
WHERE sal > (SELECT sal
FROM emp
WHERE empno = 7566)
3. Name, job, and salary of the employee with the minimum salary.
SELECT ename, job, sal
FROM emp
WHERE sal = (SELECT min(sal)
FROM emp)
4. Department numbers and their minimum salary for departments whose minimum salary is greater than the
minimum salary in department 20.
SELECT deptno, MIN(sal)
FROM emp
GROUP BY deptno
HAVING MIN(sal) > (SELECT MIN(sal)
FROM emp
WHERE deptno = 20)
5. Details of employees who have the same job as the employee with empno 7369 but whose salary is greater
than the salary of the employee with empno 7876.
SELECT *
FROM emp
WHERE job = (SELECT job
FROM emp
WHERE empno = 7369) AND
sal > (SELECT sal
FROM emp
WHERE empno = 7876)
Multiple row:
1. Employee numbers and names of employees whose salary matches the minimum salary of any department.
SELECT empno, ename
FROM emp
WHERE sal IN (SELECT MIN(sal)
FROM emp
GROUP BY deptno)
2. Employee numbers and names of employees whose salary does not match the minimum salary of any
department.
SELECT empno , ename
FROM emp
WHERE sal NOT IN (SELECT MIN(sal)
FROM emp
GROUP BY deptno)
3. Employee numbers and names of employees whose salary is greater than the minimum salary of all
departments.
SELECT empno , ename
FROM emp
WHERE sal > ALL (SELECT min(sal)
FROM emp
GROUP BY deptno)
4. Employee numbers and names of employees whose salary is less than the minimum salary of all departments.
SELECT empno , ename
FROM emp
WHERE sal < ALL (SELECT min(sal)
FROM emp
GROUP BY deptno)
5. Details of employees whose salary is greater than the average salary of all departments.
SELECT *
FROM emp
WHERE sal > ALL (SELECT avg(sal)
FROM emp
GROUP BY deptno)
6. Details of employees whose salary is less than the salary of all salesmen.
SELECT *
FROM emp
WHERE sal < ALL (SELECT sal
FROM emp
WHERE job = 'SALESMAN')
7. Names of employees whose salary matches the minimum salary of any department.
SELECT ename
FROM emp
WHERE sal IN (SELECT MIN(sal)
FROM emp
GROUP BY deptno)
8. Employees (excluding clerks) whose salary is less than any of the salaries of employees working as CLERK.
Display the employee number, name, and job.
SELECT empno, ename, job
FROM emp
WHERE sal < ANY (SELECT sal
FROM emp
WHERE job = 'CLERK')
AND job <> 'CLERK'
