The SQL Coding Exercise is from oj-leet Online Judge and you can submit your solution to this problem here: https://oj.leetcode.com/problems/employees-earning-more-than-their-managers/
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 | +----------+
The same table can be used twice, and then we have a straightforward solution:
select `a`.`Name` from `Employee` as `a`, `Employee` as `b` where `a`.`Salary` > `b`.`Salary` and `a`.`ManagerId` = `b`.`Id`
You could also use [left join] which gives the same result:
select a.Name from Employee a inner join Employee b on a.ManagerId = b.Id where a.Salary > b.Salary
–EOF (The Ultimate Computing & Technology Blog) —
GD Star Rating
loading...
255 wordsloading...
Last Post: Avoid Magic Numbers by using Enum in C/C++
Next Post: SQL Coding Exercise - Duplicate Emails