Day6:练习

练习1：各部门工资最高的员工

题目：

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

思路

先关联表，再使用子查询将每个部门工资最高的员工信息筛选出来

代码

SELECT 
     Dep.Name AS Department
    ,Emp.Name AS Employee
    ,Emp.Salary AS Salary
FROM
    Employee AS Emp
INNER JOIN
    Department AS Dep
ON Emp.DepartmentId=Dep.Id
WHERE Emp.Salary IN (
    SELECT 
        MAX(Salary) 
    FROM 
        Employee AS E
    WHERE 
        E.DepartmentId=Emp.DepartmentId
        );

结果展示

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gweFRS6S-1656415514647)(en-resource://database/1250:1)]

练习2：换座位

题目：

改变相邻俩学生的座位。

思路

所有偶数行前移1；所有奇数行后移1；最后一行为奇数行，不做移动

代码

SELECT s.id,s.student
FROM 
(
SELECT id+1 AS id,student FROM seat WHERE MOD(id,2)=1 AND id!=(SELECT count(*) FROM seat)
UNION
SELECT id-1 AS id,student FROM seat WHERE MOD(id,2)=0
UNION
SELECT id,student FROM seat WHERE id = (SELECT COUNT(*) FROM seat)
) AS s
ORDER BY id;	

练习3：分数排名

题目：

二年级四个班的平均成绩分别是 93、93、93、91，请问可以实现几种排名结果？分别使用了什么函数？排序结果是怎样的？（只考虑降序）

思路

这里主要考察窗口函数的三个专用排序(RANK、DENSE_RANK)算法,注意是降序。

代码

SELECT  class
       ,score_avg 
       ,RANK() OVER (ORDER BY score_avg) AS ranking
       ,DENSE_RANK() OVER (ORDER BY score_avg) AS dense_ranking
       ,ROW_NUMBER() OVER (ORDER BY score_avg) AS row_num
  FROM score 
	ORDER BY score_avg DESC;  

练习4：连续出现的数字

题目：

查找所有至少连续出现三次的数字

思路

代码

练习5：树叶

题目：

写一条查询语句打印节点id及对应的节点类型

思路

有三种类型，且是三种互斥关系，故采用case语句

代码

SELECT id,
CASE 
	WHEN p_id IS NULL THEN 'Root'
	WHEN id NOT IN (SELECT P_id FROM tree AS p1 where p_id is NOT NULL) THEN 'Leaf'
	ELSE 'Inner'
END AS Type
FROM tree;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hrPyuwfd-1656415514649)(en-resource://database/1252:1)]

练习6：至少有五名直接下属的经理

题目：

针对Employee表，写一条SQL语句找出有5个下属的主管

思路

ManagerId是主管的id，只要找出manageid列的id数超过5次及以上，就可以判断对应的id有5个下属，然后

代码

SELECT `name` 
FROM employee2
WHERE 
Id IN(SELECT managerid
FROM(SELECT managerid,COUNT(managerid) AS count_ld
FROM employee2 AS em1
GROUP BY managerid 
HAVING count_ld >=5) AS em);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-H8UxPWMP-1656415514649)(en-resource://database/1254:1)]

练习7：查询回答率最高的问题

题目：

写一条sql语句找出回答率最高的 question_id。

思路

按照question_id分组计算回答率然后降序排序，取第一个。

代码

SELECT question_id,COUNT(answer_id) AS count_answer,COUNT(action) AS count_action
FROM survey_log
GROUP BY question_id
ORDER BY COUNT(answer_id)/COUNT(action) DESC
LIMIT 1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AumUfjW5-1656415514650)(en-resource://database/1256:1)]

练习8：各部门前3高工资的员工

题目：

实现各部门前N高工资的员工功能。

思路

共有两个部门，可以先分别按照部门分组，每个分组取前三，再将表联合。

代码

(SELECT de.`name` AS department,em.name AS employee,em.salary
FROM employee9 as em
LEFT JOIN
department AS de
ON de.id = em.departmentid
WHERE de.`name`='IT'
ORDER BY department,salary DESC
LIMIT 3)
UNION
(SELECT de.`name` AS department,em.name AS employee,em.salary
FROM employee9 as em
LEFT JOIN
department AS de
ON de.id = em.departmentid
WHERE de.`name`='Sales'
ORDER BY department,salary DESC
LIMIT 3);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aycq3STA-1656415514650)(en-resource://database/1258:0)]

练习9：平面上最近距离

题目：

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

思路

使用两个相同的表，对x、y坐标轴进行距离计算，计算时剔除相同坐标。’

代码

select p1.x,p1.y,p2.x,p2.y,
			 round(sqrt(power(p1.x-p2.x,2)+power(p1.y-p2.y,2)),2) as shortest
from point_2d as p1,point_2d as p2
where p1.x != p2.x
or   p1.y != p2.y
order by shortest
LIMIT 1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gnLJKJhO-1656415514651)(en-resource://database/1260:0)]

练习10：行程和用户

题目：

写一段 SQL 语句查出2013年10月1日至2013年10月3日期间非禁止用户的取消率。

思路

表Trips与表Users进行关联，查询所有非禁止用户的出租车行程信息；通过request_at进行分组；通过count(if())语句，分别统计各日期非禁止用户取消订单数和各日期总订单数，并计算比率，保留两位小数

代码

SELECT
	t.Request_at DAY,
	round( sum( CASE WHEN t.STATUS LIKE 'cancelled%' THEN 1 ELSE 0 END )/ count(*), 2 ) AS 'Cancellation Rate' 
FROM
	Trips t
	INNER JOIN Users u ON u.Users_Id = t.Client_Id 
	AND u.Banned = 'No' 
WHERE
	t.Request_at BETWEEN '2013-10-01' 
	AND '2013-10-03' 
GROUP BY
	t.Request_at;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q7dNX0Jb-1656415514651)(en-resource://database/1262:0)]