AdvancedSubqueries & EXISTS

Classic: employees earning more than their manager

The query

SQL
SELECT
  e.first_name || ' ' || e.last_name AS employee,
  e.salary AS employee_salary,
  m.first_name || ' ' || m.last_name AS manager,
  m.salary AS manager_salary,
  e.salary - m.salary AS difference
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary
ORDER BY difference DESC;
Tested against PostgreSQL 16

Note

Self-join classic interview question. Alias the same table twice (e=employee, m=manager).

Tables referenced