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;Note
“Self-join classic interview question. Alias the same table twice (e=employee, m=manager).