A correlated subquery references the outer query's row in its WHERE clause. Conceptually, how many times is the subquery logically evaluated, and why can't it always be cached like an uncorrelated subquery?
SELECT e.name
FROM employees e
WHERE e.salary > (
SELECT AVG(d.salary)
FROM employees d
WHERE d.dept_id = e.dept_id
);