SQL: subqueries and common table expressions
A subquery is a SELECT used inside another statement. A scalar subquery returns one value, such as the highest salary, and can be used wherever a value is allowed. A common table expression (CTE), introduced by WITH, gives an intermediate result a name so that a complex query can be read in steps, from the inside out.
SQL
CREATE TABLE emp (name TEXT, dept TEXT, salary INTEGER);
INSERT INTO emp VALUES ('Ada', 'Eng', 120), ('Bo', 'Ops', 90), ('Cy', 'Eng', 100), ('Di', 'Ops', 80), ('Ed', 'Eng', 140);
WITH dept_avg AS (
SELECT dept, AVG(salary) AS avg_salary FROM emp GROUP BY dept
)
SELECT e.name, e.dept, e.salary
FROM emp e
JOIN dept_avg d ON d.dept = e.dept
WHERE e.salary > d.avg_salary
ORDER BY e.name;
SELECT name FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);