20240107 SQL基础50题打卡
20240107 SQL基础50题打卡
1978. 上级经理已离职的公司员工
表: Employees
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| employee_id | int |
| name | varchar |
| manager_id | int |
| salary | int |
+-------------+----------+
在 SQL 中，employee_id 是这个表的主键。
这个表包含了员工，他们的薪水和上级经理的id。
有一些员工没有上级经理（其 manager_id 是空值）。
查找这些员工的id，他们的薪水严格少于$30000 并且他们的上级经理已离职。当一个经理离开公司时，他们的信息需要从员工表中删除掉，但是表中的员工的manager_id 这一列还是设置的离职经理的id 。
返回的结果按照employee_id 从小到大排序。
查询结果如下所示：
示例：
输入：
Employees table:
+-------------+-----------+------------+--------+
| employee_id | name | manager_id | salary |
+-------------+-----------+------------+--------+
| 3 | Mila | 9 | 60301 |
| 12 | Antonella | null | 31000 |
| 13 | Emery | null | 67084 |
| 1 | Kalel | 11 | 21241 |
| 9 | Mikaela | null | 50937 |
| 11 | Joziah | 6 | 28485 |
+-------------+-----------+------------+--------+
输出：
+-------------+
| employee_id |
+-------------+
| 11 |
+-------------+解释：
薪水少于 30000 美元的员工有 1 号(Kalel) 和 11号 (Joziah)。
Kalel 的上级经理是 11 号员工，他还在公司上班(他是 Joziah )。
Joziah 的上级经理是 6 号员工，他已经离职，因为员工表里面已经没有 6 号员工的信息了，它被删除了。
题解：
# Write your MySQL query statement below
select employee_id
from Employees
where manager_id not in (select employee_id from Employees)and salary < 30000
order by employee_id;
626. 换座位
表: Seat
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| student | varchar |
+-------------+---------+
id 是该表的主键（唯一值）列。
该表的每一行都表示学生的姓名和 ID。
id 是一个连续的增量。
编写解决方案来交换每两个连续的学生的座位号。如果学生的数量是奇数，则最后一个学生的id不交换。
按 id 升序 返回结果表。
查询结果格式如下所示。
示例 1:
输入:
Seat 表:
+----+---------+
| id | student |
+----+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+----+---------+
输出:
+----+---------+
| id | student |
+----+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+----+---------+
解释:
请注意，如果学生人数为奇数，则不需要更换最后一名学生的座位。
题解：
# method 1
# Write your MySQL query statement below
select id, if(student2 is null,student,student2) as student
from ((select s1.id,s1.student,s2.id as id2, s2.student as student2from Seat as s1left join Seat as s2on s2.id - s1.id = 1where s1.id % 2 = 1) union(select s1.id,s1.student,s2.id as id2, s2.student as student2from Seat as s1left join Seat as s2on s2.id - s1.id = -1where s1.id % 2 = 0)order by id
) as result# method 2
SELECT(CASEWHEN MOD(id, 2) != 0 AND counts != id THEN id + 1WHEN MOD(id, 2) != 0 AND counts = id THEN idELSE id - 1END) AS id,student
FROMseat,(SELECTCOUNT(*) AS countsFROMseat) AS seat_counts
ORDER BY id ASC;#method 3
SELECT*
FROMseat s1LEFT JOINseat s2 ON (s1.id+1)^1-1 = s2.id
ORDER BY s1.id;知识点：
在 MySQL 中，COALESCE 是一种函数，用于返回参数列表中的第一个非 NULL 值。如果参数列表中的所有值都是 NULL，则 COALESCE 返回 NULL。
COALESCE 函数的语法如下：
COALESCE(value1, value2, ...)
其中，value1、value2 等为要检查的值，可以有多个参数，但至少需要有一个参数。COALESCE 会逐个检查参数，返回第一个非 NULL 的值。
下面是 COALESCE 函数的使用示例：
假设有一个包含订单信息的表 orders，其中有两个字段 order_id 和 shipping_date。如果某些订单尚未发货，shipping_date 列可能包含 NULL 值。我们可以使用 COALESCE 函数来提供一个备用日期，以便在 shipping_date 为 NULL 时提供默认值。
SELECT order_id, COALESCE(shipping_date, '2023-12-31') AS actual_shipping_date
FROM orders;
在这个示例中，如果 shipping_date 列中的值为 NULL，COALESCE 函数将返回 ‘2023-12-31’ 作为 actual_shipping_date 的值，否则将返回实际的 shipping_date 值。
总之，COALESCE 函数在 MySQL 中是一个很有用的工具，可以帮助处理 NULL 值，提供默认值或选择非 NULL 值。
1341. 电影评分
表：Movies
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| movie_id | int |
| title | varchar |
+---------------+---------+
movie_id 是这个表的主键(具有唯一值的列)。
title 是电影的名字。
表：Users
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| name | varchar |
+---------------+---------+
user_id 是表的主键(具有唯一值的列)。
表：MovieRating
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| movie_id | int |
| user_id | int |
| rating | int |
| created_at | date |
+---------------+---------+
(movie_id, user_id) 是这个表的主键(具有唯一值的列的组合)。
这个表包含用户在其评论中对电影的评分 rating 。
created_at 是用户的点评日期。
请你编写一个解决方案：
- 查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。
- 查找在
February 2020平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称。
字典序 ，即按字母在字典中出现顺序对字符串排序，字典序较小则意味着排序靠前。
返回结果格式如下例所示。
示例 1：
输入：
Movies 表：
+-------------+--------------+
| movie_id | title |
+-------------+--------------+
| 1 | Avengers |
| 2 | Frozen 2 |
| 3 | Joker |
+-------------+--------------+
Users 表：
+-------------+--------------+
| user_id | name |
+-------------+--------------+
| 1 | Daniel |
| 2 | Monica |
| 3 | Maria |
| 4 | James |
+-------------+--------------+
MovieRating 表：
+-------------+--------------+--------------+-------------+
| movie_id | user_id | rating | created_at |
+-------------+--------------+--------------+-------------+
| 1 | 1 | 3 | 2020-01-12 |
| 1 | 2 | 4 | 2020-02-11 |
| 1 | 3 | 2 | 2020-02-12 |
| 1 | 4 | 1 | 2020-01-01 |
| 2 | 1 | 5 | 2020-02-17 |
| 2 | 2 | 2 | 2020-02-01 |
| 2 | 3 | 2 | 2020-03-01 |
| 3 | 1 | 3 | 2020-02-22 |
| 3 | 2 | 4 | 2020-02-25 |
+-------------+--------------+--------------+-------------+
输出：
Result 表：
+--------------+
| results |
+--------------+
| Daniel |
| Frozen 2 |
+--------------+
解释：
Daniel 和 Monica 都点评了 3 部电影（"Avengers", "Frozen 2" 和 "Joker"） 但是 Daniel 字典序比较小。
Frozen 2 和 Joker 在 2 月的评分都是 3.5，但是 Frozen 2 的字典序比较小。
题解：
# Write your MySQL query statement below
# 评论电影数量最多且字典序较小的用户名
(select Users.name as resultsFROM MovieRatingJOIN Users ON MovieRating.user_id = Users.user_idGROUP BY MovieRating.user_idORDER BYcount(MovieRating.user_id) desc,Users.nameLIMIT 1
)
UNION all (
# 2020年2月份平均评分最高且字典序较小的电影名selectMovies.title as resultsFROM MovieRatingJOIN Movies ON MovieRating.movie_id = Movies.movie_idWHEREMovieRating.created_at >= '2020-02-01'AND MovieRating.created_at < '2020-03-01'GROUP BY MovieRating.movie_idORDER BYavg(MovieRating.rating) desc,Movies.titleLIMIT 1
)