Leetcode MySQL cases

Problem 175 - Combine Two Tables

Question:

Table: Person

1
2
3
4
5
6
7
8
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId is the primary key column for this table.

Table: Address

1
2
3
4
5
6
7
8
9
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AddressId is the primary key column for this table.

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

1
FirstName, LastName, City, State

Solution

This is a easy question that interferes with LEFT JOIN technique, FYI, please notice the word ON

1
2
3
4
5
6
SELECT FirstName, LastName, City, State
FROM
Person a
LEFT JOIN
Address b
ON a.PersonId = b.PersonId;

Problem 176 - Second Highest Salary

Question:

Write a SQL query to get the second highest salary from the Employee table.

1
2
3
4
5
6
7
+----+--------+
| 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.

1
2
3
4
5
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+

Solution:

recommended way.

Runtime: faster than 92%

Memory: Less than 100%

1
2
3
SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

other solution which might be slower for using the DISTINCT

Runtime: faster than 39%

Memory: Less than 100%

1
2
3
4
5
6
7
SELECT 
CASE WHEN COUNT(a.SecondHighestSalary) > 0 THEN a.SecondHighestSalary ELSE null END AS SecondHighestSalary
FROM
(SELECT
DISTINCT Salary AS SecondHighestSalary
FROM
Employee ORDER BY Salary DESC LIMIT 1,1) a;

Problem 178 - Rank Scores

Question:

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no “holes” between ranks.

1
2
3
4
5
6
7
8
9
10
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+

For example, given the above Scores table, your query should generate the following report (order by highest score):

1
2
3
4
5
6
7
8
9
10
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+

Solution:

1
2


Problem 181 - Employees Earning More Than Their Managers

Question:

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

1
2
3
4
5
6
7
8
+----+-------+--------+-----------+
| 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.

1
2
3
4
5
+----------+
| Employee |
+----------+
| Joe |
+----------+

Solution:

Runtime: faster than 93.96%

Memory: Less than 100%

1
2
3
4
5
6
7
SELECT a.Name AS Employee
FROM
(SELECT * FROM Employee WHERE ManagerId IS NOT NULL) a
LEFT JOIN
Employee b
ON a.ManagerId = b.Id
WHERE a.Salary > b.Salary;