๐ Question 5: Find Employees Who Earn More Than Their Manager
Suppose you have an Employee table:
Employee
โข employee_id
โข employee_name
โข manager_id
โข salary
Example:
employee_id | employee_name | manager_id | salary
------------|---------------|------------|-------
1 | Amit | NULL | 100000
2 | Rahul | 1 | 120000
3 | Priya | 1 | 90000
4 | Neha | 2 | 130000
5 | Raj | 2 | 110000
โ Find employees whose salary is greater than their manager's salary.
โโโโโโโโโโโโโโโโโโ
๐ก Approach
The Employee table contains both employees and managers.
So, we need to join the table with itself.
This is called a Self Join.
๐ SQL Solution
SELECT
e.employee_name AS employee,
e.salary AS employee_salary,
m.employee_name AS manager,
m.salary AS manager_salary
FROM Employee e
JOIN Employee m
ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;
โโโโโโโโโโโโโโโโโโ
๐ How does it work?
We use the Employee table twice:
"e" โ represents the employee
"m" โ represents the manager
The join condition:
e.manager_id = m.employee_id
connects each employee with their manager.
Then:
WHERE e.salary > m.salary
keeps only employees whose salary is higher than their manager's.
Expected result:
employee | employee_salary | manager | manager_salary
---------|-----------------|---------|---------------
Rahul | 120000 | Amit | 100000
Neha | 130000 | Rahul | 120000
โโโโโโโโโโโโโโโโโโ
๐ฏ Interview Tip
Whenever the same table contains a relationship such as:
โข Employee โ Manager
โข Employee โ Mentor
โข Employee โ Supervisor
โข Employee โ Parent
think about using a Self Join.
A very common interview pattern is:
FROM Employee e
JOIN Employee m
ON e.manager_id = m.employee_id
The key is to understand what each table alias represents before writing the query.
Double Tap โค๏ธ For More