-
Notifications
You must be signed in to change notification settings - Fork 46
/
Copy pathemployees-earning-more-than-their-managers.sql
58 lines (46 loc) · 1.37 KB
/
employees-earning-more-than-their-managers.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
/*
https://leetcode.com/problems/employees-earning-more-than-their-managers/description/
The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.
+----------+
| Employee |
+----------+
| Joe |
+----------+
*/
/* V0 */
select
a.Name as Employee
from
Employee a,
Employee b
where
b.Id = a.ManagerId
and
a.Salary > b.Salary
/* V0' */
SELECT e.Name AS Employee
FROM Employee e
WHERE e.Salary >
(SELECT e2.Salary
FROM Employee e2
WHERE e.ManagerId = e2.Id )
/* V1 */
SELECT a.Name as Employee
FROM Employee a
LEFT JOIN Employee b ON a.ManagerId = b.Id
WHERE a.Salary > b.Salary
/* V2 */
SELECT Name AS Employee
FROM Employee e
WHERE e.ManagerId IS NOT NULL AND e.Salary > (SELECT Salary
FROM Employee
WHERE e.ManagerId = Id)