Learn How To Write SQL Queries With Examples: #1

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

Here are some books I’d recommend to learn more about SQL:

Leave a Reply

Your email address will not be published. Required fields are marked *