Task06：综合练习

练习⼀: 各部⻔⼯资最⾼的员⼯（难度：中等）
创建Employee 表，包含所有员⼯信息，每个员⼯有其对应的 Id, salary 和 department Id。

select d.Name as Department,e.Name as Employee ,e.Salary 
from Employee as e join Department as d on e.DepartmentId = d.Id 
where (e.DepartmentId ,e.Salary) in 
(select DepartmentId ,max(Salary) as Salary  
from Employee group by DepartmentId);

练习二: 换座位（难度：中等） 力扣#626
小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

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

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

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

SELECT
    (CASE
        WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
        WHEN MOD(id, 2) != 0 AND counts = id THEN id
        ELSE id - 1
    END) AS id,
    student
FROM
    seat,
    (SELECT
        COUNT(*) AS counts
    FROM
        seat) AS seat_counts
ORDER BY id ASC;

练习三: 分数排名（难度：中等） 力扣#178
编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

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 a.Score DESC

练习四：连续出现的数字（难度：中等） #180
编写一个 SQL 查询，查找所有至少连续出现三次的数字。

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;

练习五：树节点 （难度：中等） #608
对于tree表，id是树节点的标识，p_id是其父节点的id。

SELECT p_id AS f,COUNT(id) AS branch
FROM tree
GROUP BY p_id;
  DELETE FROM tree WHERE id in (2,3,4,5);

SELECT T.id,(CASE WHEN T.p_id IS NULL THEN 'Root' WHEN P.branch >0 THEN 'Leaf' ELSE 'Inner' END) AS 'Type'
FROM tree T LEFT JOIN (SELECT p_id AS f,COUNT(id) AS branch FROM tree GROUP BY p_id) P
ON T.Id = P.f;

练习六：至少有五名直接下属的经理 （难度：中等） #570
Employee表包含所有员工及其上级的信息。每位员工都有一个Id，并且还有一个对应主管的Id（ManagerId）。

SELECT
	a.Name
FROM
	Employee a
JOIN
	(SELECT
			ManagerId
		FROM
			Employee
		GROUP BY ManagerId
		HAVING COUNT(*) >= 5) b
ON a.Id = b.ManagerId;

练习七: 分数排名 （难度：中等）
练习三的分数表，实现排名功能，但是排名需要是非连续的，如下

练习八：查询回答率最高的问题 （难度：中等）(https://blog.csdn.net/weixin_43760925/article/details/99579654)
求出survey_log表中回答率最高的问题，表格的字段有：uid, action, question_id, answer_id, q_num, timestamp。
uid是用户id；action的值为：“show”， “answer”， “skip”；当action是"answer"时，answer_id不为空，相反，当action是"show"和"skip"时为空（null）；q_num是问题的数字序号。
写一条sql语句找出回答率最高的问题。

SELECT survey_log
FROM(
			SELECT t.survey_log, dense_rank() over (ORDER BY ratio DESC) as 'Rank'
			FROM(
						SELECT s.question_id as survey_log,
						(CASE
						WHEN SUM(s.action = 'answer') = 0 then 0
						else SUM(s.action = 'answer') / COUNT(*)
						END) as ratio
						FROM survey_log as s
			GROUP BY uid, question_id) as t
) as p
WHERE p.Rank = 1;

练习九：各部门前3高工资的员工（难度：中等）
将项目7中的employee表清空，重新插入以下数据（其实是多插入5,6两行）：

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);

练习十：平面上最近距离 (难度: 困难）
point_2d表包含一个平面内一些点（超过两个）的坐标值（x，y）。
写一条查询语句求出这些点中的最短距离并保留2位小数。

SELECT MIN(distance) as shortest
FROM(
			SELECT POW ((p1.x - p2.x),2) + POW((p1.y - p2.y), 2)  as distance
			FROM point_2d as p1
			INNER JOIN point_2d as p2
			ON !(p1.x = p2.x and p1.y = p2.y)
) as p;

练习十一：行程和用户（难度：困难）
Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。

SELECT
    Request_at AS DAY,
    ROUND( SUM( CASE WHEN `Status` LIKE 'cancelled%' THEN 1 ELSE 0 END ) / COUNT( * ), 2 ) AS cancelled_rate 
FROM
    trips
    LEFT JOIN ( SELECT Users_Id, Banned FROM users ) client ON trips.Client_Id = client.Users_Id
    LEFT JOIN ( SELECT Users_Id, Banned FROM users ) driver ON trips.Driver_Id = driver.Users_Id
    WHERE client.Banned = 'NO' AND driver.Banned = 'NO'
    GROUP BY Request_at
    ORDER BY Request_at;

后面几道题目的参考