Database¶
175. Combine Two Tables¶
Table: Person
| Column Name | Type |
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
PersonId is the primary key column for this table.
Table: Address
| 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:
FirstName, LastName, City, State
Answer:
SELECT Person.FirstName, Person.LastName, Address.City, Address.State FROM Person LEFT JOIN Address
ON Person.PersonId = Address.PersonId
考察了 LEFT JOIN 的用法。
176. Second Highest Salary¶
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 second highest salary is 200. If there is no second highest salary, then the query should return null.
SELECT (
SELECT Salary FROM Employee GROUP BY Salary ORDER BY Salary DESC LIMIT 1 OFFSET 1
) AS SecondHighestSalary
复杂度应该为线性。或者使用 MAX 函数也可以。
177. Nth Highest Salary¶
Write a SQL query to get the nth highest salary from the Employee table.
| Id | Salary |
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.
Answer:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE offset INT;
SELECT N - 1 INTO offset;
RETURN (
SELECT (
SELECT Salary FROM Employee GROUP BY Salary ORDER BY Salary DESC LIMIT 1 OFFSET offset
) AS NthHighestSalary
);
END
OFFSET 后面必须跟一个已经计算出来的变量，而不能是表达式。
178. Rank Scores¶
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.
| 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):
Answer:
SELECT
Score, Rank
FROM
(
SELECT
Score,
@rowNum:=IF(Score < @prevVal,
@rowNum + 1,
@rowNum) AS Rank,
@prevVal:=Score
FROM
Scores,
(SELECT @rowNum:=1) x,
(SELECT @prevVal:=0) y
ORDER BY Score DESC
) tmp
以上语句用两个变量实现了 dense_rank() 函数的功能。
180. Consecutive Numbers¶
Write a SQL query to find all numbers that appear at least three times consecutively.
| Id | Num |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.
Answer:
SELECT DISTINCT Num AS ConsecutiveNums
FROM (
SELECT
Num,
@times:=IF(Num = @preVal,
@times + 1,
1) AS TIMES,
@preVal:=Num
FROM
Logs,
(SELECT @times:=0) x,
(SELECT @preVal:=0) y
ORDER BY Id
) tmp
WHERE TIMES > 2
该题与上一题类似，需要用到变量。
181. 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 |
Answer:
SELECT X.Name as Employee
FROM Employee AS X JOIN Employee AS Y ON X.ManagerId =Y.Id
WHERE X.Salary > Y.Salary
Only beats 40%, 希望能知道更快的查询方法。
182. Duplicate Emails¶
Write a SQL query to find all duplicate emails in a table named Person.
| Id | |
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
For example, your query should return the following for the above table:
| a@b.com |
Note: All emails are in lowercase.
Answer:
SELECT Email FROM Person GROUP BY Email HAVING COUNT(*) > 1
只是简单考察了 GROUP BY 及 HAVING 的用法。只超过了20%的人，有理由怀疑分布的测试用例和环境变化很大。
183. Customers Who Never Order¶
Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything.
Table: Customers.
| Id | Name |
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
Table: Orders.
| Id | CustomerId |
| 1 | 3 |
| 2 | 1 |
Using the above tables as example, return the following:
| Customers |
| Henry |
| Max |
Answer:
SELECT Customers.Name AS Customers FROM
Customers LEFT JOIN (
SELECT DISTINCT CustomerId FROM Orders
) Buy ON Customers.Id = Buy.CustomerId
WHERE Buy.CustomerId IS NULL
使用 LEFT JOIN 实现了集合减法。
184. Department Highest Salary¶
The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.
| Id | Name | Salary | DepartmentId |
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
The Department table holds all departments of the company.
| Id | Name |
| 1 | IT |
| 2 | Sales |
Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.
| Department | Employee | Salary |
| IT | Max | 90000 |
| Sales | Henry | 80000 |
Answer:
SELECT Department.Name AS Department, Employee.Name AS Employee, Employee.Salary AS Salary FROM (
(SELECT DepartmentId, MAX(Salary) AS Salary FROM Employee GROUP BY DepartmentId) HighestSalary
JOIN Employee
ON HighestSalary.Salary = Employee.Salary AND HighestSalary.DepartmentId = Employee.DepartmentId
JOIN Department
ON Employee.DepartmentId = Department.Id )
这道题的情景是开窗函数的经典使用场景，否则就要使用 JOIN 方法，注意使用的 JOIN 类型和命名空间。
185. Department Top Three Salaries¶
The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.
| Id | Name | Salary | DepartmentId |
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
The Department table holds all departments of the company.
| Id | Name |
| 1 | IT |
| 2 | Sales |
Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows.
| Department | Employee | Salary |
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
SELECT Department.Name AS Department, RankedEmployee.Name AS Employee, RankedEmployee.Salary
FROM (
SELECT
Name, Salary, DepartmentId,
@denseRank:=IF(DepartmentId = @preDepartmentId,
IF(Salary < @preSalary,
@denseRank + 1,
@denseRank),
1) AS Rank,
@preDepartmentId:=DepartmentId,
@preSalary:=Salary
FROM
Employee,
(SELECT @denseRank:=1，@preDepartmentId:=-1，@preSalary:=-1) x
ORDER BY DepartmentId, Salary DESC
) RankedEmployee
JOIN Department
ON RankedEmployee.DepartmentId = Department.Id
WHERE RankedEmployee.Rank < 4
Beats 99%, haha~
196. Delete Duplicate Emails¶
Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.
| Id | |
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
Id is the primary key column for this table. For example, after running your query, the above Person table should have the following rows:
| Id | |
| 1 | john@example.com |
| 2 | bob@example.com |
DELETE e FROM Person AS e
WHERE e.Id NOT IN (
SELECT Id
FROM (
SELECT MIN(Id) AS Id FROM Person
GROUP BY EMAIL
) x
)
Wrap your subquery up in an additional subquery (here named x) and MySQL will happily do what you ask, But this shouldn't work. Or create a temp table do make sense.
197. Rising Temperature¶
Given a Weather table, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.
| Id(INT) | Date(DATE) | Temperature(INT) |
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
For example, return the following Ids for the above Weather table:
| Id |
| 2 |
| 4 |
SELECT p.Id
FROM Weather AS p
JOIN Weather AS q
ON p.Date = DATE_ADD(q.Date, INTERVAL 1 DAY) AND p.Temperature > q.Temperature
262. Trips and Users¶
The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).
The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).
| Users_Id | Banned | Role |
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
Write a SQL query to find the cancellation rate of requests made by unbanned client between Oct 1, 2013 and Oct 3, 2013. For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.
- find out the requests made by unbanned clients ( because the trip is requested by client)
- calculate the cancellation ratio (either by client or driver)
| Day | Cancellation Rate |
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
SELECT Trips.Request_at AS Day,
CAST(SUM(IF((Trips.Status IN ('cancelled_by_client', 'cancelled_by_driver')),
1, 0)
)/COUNT(*) AS DECIMAL(5,2)) AS 'Cancellation Rate'
FROM Trips
JOIN Users ON Trips.Client_Id = Users.Users_Id
WHERE Request_at BETWEEN '2013-10-01' AND '2013-10-03'
AND Users.Banned = 'No'
GROUP BY Request_at
这道题主要考的是聚合函数的正确使用，属于常见的应用。