USING QUARIES TO SOLVE SUBQUARIES
1) Employees who work with a given employee (same department) — Nested Subquery
SQL (prompt by name; exclude the employee):
SELECT empno, ename, deptno
FROM emp
WHERE deptno = (SELECT deptno FROM emp WHERE ename = '&EMP_NAME')
AND ename <> UPPER('&EMP_NAME');
2) Employees who earn more than the average salary — Nested Subquery
SELECT empno, ename, job, sal
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp)
ORDER BY sal DESC;
3) Show ENAME, JOB of employees who are Managers — EXISTS
“Manager” = someone who appears as MGR of at least one employee.
SELECT e.ename, e.job
FROM emp e
WHERE EXISTS (SELECT 1 FROM emp x WHERE x.mgr = e.empno)
ORDER BY e.ename;
4) Employees who earn less than the least salary of DEPTNO 10 — ALL operator
SELECT empno, ename, sal
FROM emp
WHERE sal < ALL (SELECT sal FROM emp WHERE deptno = 10)
ORDER BY sal;
5) Employees who have the same DEPTNO and MGR as a given employee, excluding that employee —
Nested Subquery
SELECT empno, ename, deptno, mgr
FROM emp
WHERE (deptno, mgr) = (SELECT deptno, mgr FROM emp WHERE empno = &EMP_NO)
AND empno <> &EMP_NO;
6) Empno & name of all employees who work in a department that has any employee with 'R' in the name
SELECT empno, ename
FROM emp
WHERE deptno IN (SELECT DISTINCT deptno
FROM emp
WHERE ename LIKE '%R%')
ORDER BY deptno, ename;
7) ename, deptno, job of employees who work in NEW YORK — Nested Subquery
SELECT ename, deptno, job
FROM emp
WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'NEW YORK')
ORDER BY ename;
8) Same as (7) but prompt for LOC
SELECT ename, deptno, job
FROM emp
WHERE deptno IN (SELECT deptno FROM dept WHERE loc = UPPER('&LOC'))
ORDER BY ename;
9) Name and salary of every employee who reports to KING — Nested Subquery
SELECT ename, sal
FROM emp
WHERE mgr = (SELECT empno FROM emp WHERE ename = 'KING')
ORDER BY sal DESC;
10) All employees working with JAMES (same department), excluding JAMES — Nested Subquery
SELECT empno, ename, deptno
FROM emp
WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'JAMES')
AND ename <> 'JAMES'
ORDER BY ename;
11) Employees who earn less than the average salary of their own
department
(Correlated subquery)
SELECT e.empno, e.ename, e.deptno, e.sal
FROM emp e
WHERE e.sal < (SELECT AVG(e2.sal) FROM emp e2 WHERE e2.deptno = e.deptno)
ORDER BY e.deptno, e.sal;
12) LOC and average salary of each location
(Scalar subquery — one scalar per row)
-- If each dept has one LOC (SCOTT schema):
SELECT d.loc,
(SELECT ROUND(AVG(e.sal), 2)
FROM emp e
WHERE e.deptno = d.deptno) AS avg_sal
FROM dept d
ORDER BY d.loc;
-- If you want true per-LOCATION avg across all depts sharing the same LOC:
SELECT dl.loc,
(SELECT ROUND(AVG(e.sal), 2)
FROM emp e
JOIN dept d2 ON d2.deptno = e.deptno
WHERE d2.loc = dl.loc) AS avg_sal
FROM (SELECT DISTINCT loc FROM dept) dl
ORDER BY dl.loc;
13) Show the least N salaries
(Inline view; supports ties with DENSE_RANK; prompts for &N)
SELECT empno, ename, sal
FROM (
SELECT empno, ename, sal,
DENSE_RANK() OVER (ORDER BY sal ASC) AS rnk
FROM emp
)
WHERE rnk <= &N
ORDER BY sal, ename;
14) Display the last N rows (most recently hired)
(Correlated subquery; prompts for &N)
-- "Last" interpreted as latest HIREDATE
SELECT e.empno, e.ename, e.hiredate
FROM emp e
WHERE &N > (
SELECT COUNT(*)
FROM emp x
WHERE x.hiredate > e.hiredate
)
ORDER BY e.hiredate DESC, e.empno DESC;
15) Employees working in DALLAS, sorted
(Scalar subquery)
SELECT e.empno, e.ename, e.job, e.deptno
FROM emp e
WHERE e.deptno = (SELECT d.deptno FROM dept d WHERE d.loc = 'DALLAS')
ORDER BY e.ename;
16) Employees whose salary < department average, and also show the
department average
(Inline view)
WITH dept_avg AS (
SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
)
SELECT e.empno, e.ename, e.deptno, e.sal, ROUND(a.avg_sal, 2) AS
dept_avg_sal
FROM emp e
JOIN dept_avg a ON a.deptno = e.deptno
WHERE e.sal < a.avg_sal
ORDER BY e.deptno, e.sal;
17) LOC of departments whose sum(sal) is less than the overall average
salary (across all employees)
(WITH clause)
WITH dept_sum AS (
SELECT d.deptno, d.loc, NVL(SUM(e.sal), 0) AS sum_sal
FROM dept d
LEFT JOIN emp e ON e.deptno = d.deptno
GROUP BY d.deptno, d.loc
),
overall_avg AS (
SELECT AVG(sal) AS avg_sal FROM emp
)
SELECT ds.loc
FROM dept_sum ds, overall_avg oa
WHERE ds.sum_sal < oa.avg_sal
ORDER BY ds.loc;
If your instructor defines “overall average” differently (e.g., average department-sum rather
than average employee salary), replace overall_avg with:
WITH dept_sum AS ( ... )
SELECT AVG(sum_sal) AS avg_dept_sum FROM dept_sum;
and compare ds.sum_sal < avg_dept_sum instead.
