What does this correlated subquery return for each department — and what is the key behavioral trait that makes it 'correlated'?
SELECT e.name, e.salary, e.dept_id
FROM emp e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM emp e2
WHERE e2.dept_id = e.dept_id
);