Suppose you have two tables, Employee and Department. Verbally explain how you would find the highest paid employee in each department.

I would use a window function to rank employees by salary within each department. The query would look like:

SELECT employee_id, name, department_id, salary

FROM (

SELECT e.*, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn

FROM Employee e

) t

WHERE rn = 1;

This returns the top-paid employee per department. If multiple employees share the same highest salary, you could use RANK() instead of ROW_NUMBER() to include all ties.