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 [Link], [Link]
FROM emp e
WHERE EXISTS (SELECT 1 FROM emp x WHERE [Link] = [Link])
ORDER BY [Link];
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 [Link], [Link], [Link], [Link]
FROM emp e
WHERE [Link] < (SELECT AVG([Link]) FROM emp e2 WHERE [Link] = [Link])
ORDER BY [Link], [Link];
12) LOC and average salary of each location
(Scalar subquery — one scalar per row)
-- If each dept has one LOC (SCOTT schema):
SELECT [Link],
(SELECT ROUND(AVG([Link]), 2)
FROM emp e
WHERE [Link] = [Link]) AS avg_sal
FROM dept d
ORDER BY [Link];
-- If you want true per-LOCATION avg across all depts sharing the same LOC:
SELECT [Link],
(SELECT ROUND(AVG([Link]), 2)
FROM emp e
JOIN dept d2 ON [Link] = [Link]
WHERE [Link] = [Link]) AS avg_sal
FROM (SELECT DISTINCT loc FROM dept) dl
ORDER BY [Link];
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 [Link], [Link], [Link]
FROM emp e
WHERE &N > (
SELECT COUNT(*)
FROM emp x
WHERE [Link] > [Link]
)
ORDER BY [Link] DESC, [Link] DESC;
15) Employees working in DALLAS, sorted
(Scalar subquery)
SELECT [Link], [Link], [Link], [Link]
FROM emp e
WHERE [Link] = (SELECT [Link] FROM dept d WHERE [Link] = 'DALLAS')
ORDER BY [Link];
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 [Link], [Link], [Link], [Link], ROUND(a.avg_sal, 2) AS
dept_avg_sal
FROM emp e
JOIN dept_avg a ON [Link] = [Link]
WHERE [Link] < a.avg_sal
ORDER BY [Link], [Link];
17) LOC of departments whose sum(sal) is less than the overall average
salary (across all employees)
(WITH clause)
WITH dept_sum AS (
SELECT [Link], [Link], NVL(SUM([Link]), 0) AS sum_sal
FROM dept d
LEFT JOIN emp e ON [Link] = [Link]
GROUP BY [Link], [Link]
),
overall_avg AS (
SELECT AVG(sal) AS avg_sal FROM emp
)
SELECT [Link]
FROM dept_sum ds, overall_avg oa
WHERE ds.sum_sal < oa.avg_sal
ORDER BY [Link];
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.