心得:
題目要求找出有哪些人的薪水比主管高,找出名字並修改欄位名稱為Employee
。
題目:
The
Employee
table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.
12345678 +----+-------+--------+-----------+| 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.
12345 +----------+| Employee |+----------+| Joe |+----------+
答案:
- Sub Select
123456# Write your MySQL query statement belowSELECT a.Name AS EmployeeFROM Employee AS aWHERE a.Salary > (SELECT z.SalaryFROM Employee AS zWHERE z.Id = a.ManagerId); - Join
123456# Write your MySQL query statement belowSELECT a.Name AS EmployeeFROM Employee AS aJOIN Employee AS bON a.ManagerId = b.IdWHERE a.Salary > b.Salary - From
12345# Write your MySQL query statement belowSELECT a.Name AS EmployeeFROM Employee AS a, Employee AS bWHERE a.ManagerId = b.IdAND a.Salary > b.Salary