SQL for beginners : finding out Employees Earning More Than Their Managers

Leetcode question no. 181

 181. Employees Earning More Than Their Managers

SQL Schema

Table: Employee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| salary      | int     |
| managerId   | int     |
+-------------+---------+
id is the primary key column for this table.
Each row of this table indicates the ID of an employee, their name, salary, and the ID of their manager.

 

Write an SQL query to find the employees who earn more than their managers.

Return the result table in any order.

The query result format is in the following example.

 

Example 1:

Input: 
Employee table:
+----+-------+--------+-----------+
| id | name  | salary | managerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | Null      |
| 4  | Max   | 90000  | Null      |
+----+-------+--------+-----------+
Output: 
+----------+
| Employee |
+----------+
| Joe      |
+----------+
Explanation: Joe is the only employee who earns more than his manager.

Here's the solution :

# Write your MySQL query statement below

SELECT e1.name as Employee

From Employee e1,

         Employee e2

Where e1.salary > e2.salary

and e2.id = e1.managerId

Explanation :

Here we create two objects e1 - refers to employee

                                 and     e2 - refers to manager

Further according to condition, Employee salary more than Manager which is shown by WHERE clause as :

 Where e1.salary > e2.salary

We need another AND statement to tell the query that managerId for employee refers to id for managers which is shown by :

AND e2.id = e1.managerId

Happy Learning !