AI Hints
Homeโ€บSQL Tutorialโ€บInterview Questions, Part 1
SQL ยท Lesson 94 of 126

Employees and Managers

The self join question.

โฑ 2 min readโ–ถ Video at 2:14:20๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Here's another favourite. Find employees who earn more than their manager. It tests whether you know self joins.

Employees and Managers
From the video: Employees and Managers
  • Earn more than their manager?
  • Tests self joins
  • A favourite question

Earning more than the boss

Join staff to itself. E is the employee, M is their manager. Then compare the two salaries. Run it. No rows at all. At Helen's shop, nobody earns more than their manager. An empty result is still an answer. Say so confidently in an interview.

query.sqlsql
SELECT e.name   AS employee, e.salary,
       m.name   AS manager,  m.salary AS manager_salary
FROM staff AS e
JOIN staff AS m ON m.staff_id = e.manager_id
WHERE e.salary > m.salary;

A follow-up

A good follow-up is, who's closest to their manager's salary? Subtract, and sort. Run it. Priya Nair earns sixteen thousand less than Helen. That's the smallest gap.

query.sqlsql
SELECT e.name AS employee,
       m.salary - e.salary AS gap_to_manager
FROM staff AS e
JOIN staff AS m ON m.staff_id = e.manager_id
ORDER BY gap_to_manager
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  employee  โ”‚ gap_to_manager โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Priya Nair โ”‚ 16000          โ”‚
โ”‚ Marcus Lee โ”‚ 18000          โ”‚
โ”‚ Sara Odum  โ”‚ 22000          โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Self join, then compare columns from the two copies. And remember, an empty result can be the right answer.

Key takeaways
  • Self join
  • Compare e and m
  • Empty can be correct
Next up: Finding Duplicates โ€” GROUP BY with HAVING