How do you write a query to find employees whose salary is above the average of their own department?

Option 1 (Correlated Subquery): SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e.dept_id). Option 2 (JOIN with derived table): SELECT e.* FROM employees e JOIN (SELECT dept_id, AVG(salary) AS dept_avg FROM employees GROUP BY dept_id) da ON e.dept_id = da.dept_id WHERE e.salary > da.dept_avg. Option 3 (Window Function): SELECT * FROM (SELECT *, AVG