Explain correlated vs non-correlated subqueries with examples.

Short answer: Non-correlated runs once and is independent of the outer row. Correlated references outer columns and conceptually runs per outer row. EXISTS often uses correlation efficiently.

Sample solution

-- Non-correlated
SELECT * FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

-- Correlated: employees earning above their department average
SELECT e.*
FROM Employees e
WHERE e.Salary > (
    SELECT AVG(e2.Salary)
    FROM Employees e2
    WHERE e2.DepartmentId = e.DepartmentId
);

Mention that correlated subqueries can be rewritten with JOINs/windows for clarity/performance.