LeetCode上的SQL面试题

不管使用何种语言进行项目开发，都避免不了接触数据库，接触SQL语句，下面的这18道SQL语句面试题摘自力扣，比较具有代表性。

1. 查找重复的电子邮箱
2. 大的国家
3. 交换性别
4. 有趣的电影
5. 组合两个表
6. 超过经理收入的员工
7. 重新格式化部门表
8. 从不订购的客户
9. 删除重复的电子邮箱
10. 上升的温度
11. 超过5名学生的课
12. 第二高的薪水
13. 换座位
14. 分数排名
15. 连续出现的数字
16. 第N高的薪水
17. 部门工资最高的员工
18. 部门工资前三高的所有员工

1 查找重复的电子邮箱

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

示例：

根据以上输入，你的查询应返回以下结果：

电子邮箱都是小写字母。

题解：

SELECT
	Email
FROM
	Person
GROUP BY
	Email
HAVING
	count(Email) > 1

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/duplicate-emails

2 大的国家

这里有张World表

如果一个国家的面积超过300万平方公里，或者人口超过2500万，那么这个国家就是大国家。

编写一个SQL查询，输出表中所有大国家的名称、人口和面积。

例如，根据上表，我们应该输出:

题解：

SELECT
	NAME,
	population,
	area
FROM
	World
HAVING
	area > 3000000
OR population >= 25000000

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/big-countries

3 交换性别

给定一个 salary 表，如下所示，有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。

注意，只能写一个 Update 语句，请不要编写任何 Select 语句。
例如：

运行你所编写的更新语句之后，将会得到以下表：

题解：

UPDATE salary
SET sex = CASE sex
WHEN 'm' THEN
	'f'
ELSE
	'm'
END;

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/swap-salary

4 有趣的电影

某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，专门有个 LED显示板
做电影推荐，上面公布着影评和相关电影描述。

作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为
奇数 的影片，结果请按等级 rating 排列。

例如，下表 cinema：

对于上面的例子，则正确的输出是为：

题解：

SELECT
	*
FROM
	cinema
WHERE
	description != 'boring'
AND id % 2 = 1
ORDER BY
	rating DESC

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/not-boring-movies

5 组合两个表

表1：Person

表2：Address

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：

题解：

SELECT
	FirstName,
	LastName,
	City,
	State
FROM
	Person
LEFT JOIN Address ON Person.PersonId = Address.PersonId

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/combine-two-tables/

6 超过经理收入的员工

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

题解：

SELECT
	e1.NAME Employee
FROM
	Employee e1
WHERE
	Salary >= (
		SELECT
			e2.Salary
		FROM
			Employee e2
		WHERE
			e1.ManagerId = e2.Id
	)

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/employees-earning-more-than-their-managers

7 重新格式化部门表

部门表 Department：

编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应每个月的收入（revenue）列。

查询结果格式如下面的示例所示：

题解：

SELECT 
    `id`,
    max(IF(`month` = 'Jan', revenue, NULL)) AS "Jan_Revenue",
    max(IF(`month` = 'Feb', revenue, NULL)) AS "Feb_Revenue",
    max(IF(`month` = 'Mar', revenue, NULL)) AS "Mar_Revenue",
    max(IF(`month` = 'Apr', revenue, NULL)) AS "Apr_Revenue",
    max(IF(`month` = 'May', revenue, NULL)) AS "May_Revenue",
    max(IF(`month` = 'Jun', revenue, NULL)) AS "Jun_Revenue",
    max(IF(`month` = 'Jul', revenue, NULL)) AS "Jul_Revenue",
    max(IF(`month` = 'Aug', revenue, NULL)) AS "Aug_Revenue",
    max(IF(`month` = 'Sep', revenue, NULL)) AS "Sep_Revenue",
    max(IF(`month` = 'Oct', revenue, NULL)) AS "Oct_Revenue",
    max(IF(`month` = 'Nov', revenue, NULL)) AS "Nov_Revenue",
    max(IF(`month` = 'Dec', revenue, NULL)) AS "Dec_Revenue"
FROM
    Department
GROUP BY `id`;

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/reformat-department-table

8 从不订购的客户

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
Customers表：

Orders表：

例如给定上述表格，你的查询应返回：

题解：

SELECT
	NAME Customers
FROM
	Customers
LEFT JOIN Orders ON Customers.Id = Orders.CustomerId
WHERE
	CustomerId IS NULL

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/customers-who-never-order

9 删除重复的电子邮箱

编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小的那个。

例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行：

题解：

DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/delete-duplicate-emails

10 上升的温度

给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。

例如，根据上述给定的 Weather 表格，返回如下 Id：

题解：

SELECT
    weather.id AS 'Id'
FROM
    weather
        JOIN
    weather w ON DATEDIFF(weather.date, w.date) = 1
        AND weather.Temperature > w.Temperature;

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/rising-temperature

11 超过5名学生的课

有一个 courses 表 ，有：student (学生) 和 class (课程)。
请列出所有超过或等于5名学生的课

例如：

应该输出

题解：

SELECT
	class
FROM
	courses
GROUP BY
	class
HAVING
	count(DISTINCT student) >= 5

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/classes-more-than-5-students

12 第二高的薪水

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary）

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

题解：

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary;

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/second-highest-salary

13 换座位

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

示例：

假如数据输入的是上表，则输出结果如下：

注意：
如果学生人数是奇数，则不需要改变最后一个同学的座位。

题解：

SELECT
    *
FROM
    seat s1
        LEFT JOIN
    seat s2 ON (s1.id+1)^1-1 = s2.id
ORDER BY s1.id;

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/exchange-seats

14 分数排名

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

题解：

SELECT
	a.Score AS score,
	(
		SELECT
			count(DISTINCT b.Score)
		FROM
			Scores b
		WHERE
			b.Score >= a.Score
	) AS rank
FROM
	Scores a
ORDER BY
	Score DESC;

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/rank-scores

15 连续出现的数字

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

题解：

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num;

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/consecutive-numbers

16 第N高的薪水

编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

题解：

CREATE FUNCTION getNthHighestSalary (N INT) RETURNS INT
BEGIN
DECLARE c INT DEFAULT
IF (N > 0, N - 1, 1);
RETURN (
	SELECT
		Salary
	FROM
		Employee
	GROUP BY
		Salary
	ORDER BY
		Salary DESC
	LIMIT c,
	1
);
END

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/nth-highest-salary

17 部门工资最高的员工

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

Department 表包含公司所有部门的信息。

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

题解：

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	);

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/department-highest-salary

18 部门工资前三高的所有员工

Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。

Department 表包含公司所有部门的信息。

编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

解释：

IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。

题解：

SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        );

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/department-top-three-salaries

结语

本人所有博客仅用于学习记录，不做任何商业用途，如涉及侵权，还请联系删除，感谢阅读，欢迎留言，一起进步~