Subqueries

-- Single-row
SELECT last_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

-- Multi-row: IN, ANY, ALL
SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);
SELECT * FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG');

-- Multi-column (pairwise)
SELECT * FROM employees WHERE (department_id, salary) IN (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id);

-- Correlated
SELECT e.last_name, e.salary FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees x WHERE x.department_id = e.department_id);

-- EXISTS / NOT EXISTS
SELECT d.department_name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id);

-- Scalar subquery in SELECT
SELECT d.department_name, (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id) emp_cnt FROM departments d;

-- Inline view
SELECT * FROM (SELECT department_id, AVG(salary) avg_sal FROM employees GROUP BY department_id) WHERE avg_sal > 8000;

-- Subqueries in DML and DDL
UPDATE employees SET salary = (SELECT AVG(salary) FROM employee
…