The goal is to turn data into information, and information into insight.
Carly Fiorina (Former CEO of Hewlett-Packard)
Question Source: LeetCode
Solution Language: MySQL
This Q&A series will cover data questions from LeetCode and present my solutions to them. Please feel free to comment with your suggestions if you feel that these problems may be solved in a more optimized manner.
Question (LeetCode Question #176, Level: Easy)
Write a SQL query to get the second highest salary from the Employee table.
+----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+
For example, given the above Employee table, the query should return ‘200' as the second highest salary. If there is no second-highest salary, then the query should return null.
+---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+
Solution
Select Max(Salary) as SecondHighestSalary
From Employee
Where Salary not in (Select Max(Salary) From Employee)
Question (LeetCode Question #181, Level: Easy)
Write a SQL query that finds out employees who earn 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 | +----------+
Solution
SELECT e.Name AS Employee
FROM employee e JOIN employee m
ON e.ManagerID = m.Id AND e.Salary > m.Salary
Alternative Approach (Faster Query):
select e.Name as Employee
from (select e.* , m.Salary as MgrSalary
from employee e join employee m
on e.ManagerID = m.Id) AS temp
WHERE temp.Salary > temp.Salary