DataWhale SQL组队学习——综合练习

最新推荐文章于 2022-09-27 14:22:39 发布

Smile_L77

最新推荐文章于 2022-09-27 14:22:39 发布

阅读量139

点赞数

分类专栏： DA

本文链接：https://blog.csdn.net/Shirley_L77/article/details/111723300

版权

DA 专栏收录该内容

15 篇文章 1 订阅

订阅专栏

1.练习一: 各部门工资最高的员工（难度：中等）

创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
在这里插入图片描述

--创建表
CREATE TABLE Employee
( Id varchar(4) NOT NULL ,
  Name varchar(15) NOT NULL,
  Salary integer NOT NULL,
  DepartmentId varchar(4) NOT NULL, 
  primary key (Id)
  );
  -- 插入数据
  insert into Employee 
  values
  ('1', 'Joe', 70000, '1'),
  ('2', 'Henry', 80000, '2'),
  ('3', 'Sam', 60000, '2'),
  ('4', 'Max', 90000, '1');

创建Department 表，包含公司所有部门的信息。
在这里插入图片描述

--和上面的一样
CREATE TABLE Department (
Id VARCHAR(1) NOT NULL,
Name VARCHAR(225) NOT NULL,
PRIMARY KEY(Id)
);
 
INSERT INTO Department VALUES
('1','IT'),
('2','Sales');
 
SELECT * 
FROM Department

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

SELECT D.name AS Department，
E.Name AS Employee,
E.Salary 
FROM Employee AS E
RIGHT JOIN Department AS D
ON d.Id=e.DepartmentId
LEFT JOIN (SELECT Max(Salary) Salary
,DepartmentId 
FROM Employee 
GROUP BY Employee.DepartmentId) A
ON A.DepartmentId = E.DepartmentId
WHERE E.Salary = A.Salary

练习二: 换座位（难度：中等）

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。其中纵列的id是连续递增的，小美想改变相邻俩学生的座位。帮她写一个 SQL query 来输出小美想要的结果。
请创建如下所示seat表：

示例：
在这里插入图片描述假如数据输入的是上表，则输出结果如下：

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

SELECT 

	(CASE id % 2 
	 WHEN 0 THEN id - 1
	 WHEN 1 AND id != (SELECT MAX(id) FROM seat) THEN id + 1
	 ELSE id END) AS id, student
FROM seat
ORDER BY id;

练习三: 分数排名（难度：中等）

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。
创建以下score表：
在这里插入图片描述例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

--窗口函数
SELECT Score, DENSE_RANK() OVER(ORDER BY Score DESC) AS RANK
FROM Scores;

练习四：连续出现的数字（难度：中等）

编写一个 SQL 查询，查找所有至少连续出现三次的数字。
在这里插入图片描述
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

SELECT A.num as ConsecutiveNums
FROM Logs AS A
LEFT JOIN Logs B on A.id = B.id + 1
LEFT JOIN Logs C on A.id = C.id + 2  
where A.num =B.num and C.num = C.num;

练习五：树节点（难度：中等）

对于tree表，id是树节点的标识，p_id是其父节点的id。
在这里插入图片描述每个节点都是以下三种类型中的一种：

Root: 如果节点是根节点。
Leaf: 如果节点是叶子节点。
Inner: 如果节点既不是根节点也不是叶子节点。
写一条查询语句打印节点id及对应的节点类型。按照节点id排序。上面例子的对应结果为：
在这里插入图片描述说明：

节点’1’是根节点，因为它的父节点为NULL，有’2’和’3’两个子节点。
节点’2’是内部节点，因为它的父节点是’1’，有子节点’4’和’5’。
节点’3’，‘4’，'5’是叶子节点，因为它们有父节点但没有子节点。
下面是树的图形：
在这里插入图片描述注意：
如果一个树只有一个节点，只需要输出根节点属性。

SELECT id,
CASE WHEN t.p_id IS NULL THEN 'Root' 
     WHEN t.id IN(SELECT p_id FROM tree ) THEN 'Inner'
     ELSE 'Leaf' 
     END AS Type
FROM  tree t

练习六：至少有五名直接下属的经理（难度：中等）

Employee表包含所有员工及其上级的信息。每位员工都有一个Id，并且还有一个对应主管的Id（ManagerId）。
在这里插入图片描述
针对Employee表，写一条SQL语句找出有5个下属的主管。对于上面的表，结果应输出：注意:没有人向自己汇报。

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

练习七: 分数排名（难度：中等）

练习三的分数表，实现排名功能，但是排名需要是非连续的，如下：
在这里插入图片描述

--窗口函数
select Score,rank() over(order by Score desc) as Rank
from Scores

练习八：查询回答率最高的问题（难度：中等）

求出survey_log表中回答率最高的问题，表格的字段有：uid, action, question_id, answer_id, q_num, timestamp。

uid是用户id；action的值为：“show”， “answer”， “skip”；当action7是"answer"时，answer_id不为空，相反，当action是"show"和"skip"时为空（null）；q_num是问题的数字序号。

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

举例：
输入：
在这里插入图片描述
输出
说明

问题285的回答率为1/1，然而问题369的回答率是0/1，所以输出是285。

**注意：**最高回答率的意思是：同一个问题出现的次数中回答的比例。

SELECT question_id as survey_log
FROM
(
	SELECT question_id,
		SUM(case when action = "answer" THEN 1 ELSE 0 END) as num_answers,
		SUM(case when action = "show" THEN 1 ELSE 0 END) as num_shows
	FROM survey_log
	GROUP BY question_id
) as tbl
ORDER BY (num_answers / num_shows) DESC
LIMIT 1;

练习九：各部门前3高工资的员工（难度：中等）

将项目7中的employee表清空，重新插入以下数据（其实是多插入5,6两行）：
在这里插入图片描述
编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：

此外，请考虑实现各部门前N高工资的员工功能。

SELECT D.Name AS Department, E.Name AS Employee, E.Salary
	FROM Employee E
	JOIN Department D ON E.DepartmentId = D.Id
	WHERE 3 > (SELECT COUNT(DISTINCT E1.Salary) 
			   FROM Employee E1
               WHERE E1.Salary >E.Salary 
                   AND E.DepartmentId =E1.DepartmentId);

练习十：平面上最近距离 (难度: 困难）

point_2d表包含一个平面内一些点（超过两个）的坐标值（x，y）。

写一条查询语句求出这些点中的最短距离并保留2位小数。
在这里插入图片描述最短距离是1，从点（-1，-1）到点（-1，-2）。所以输出结果为：

注意：所有点的最大距离小于10000。

练习十一：行程和用户（难度：困难）

Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。在这里插入图片描述
Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。

写一段 SQL 语句查出2013年10月1日至2013年10月3日期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。
在这里插入图片描述

Smile_L77

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
DataWhale SQL组队学习——综合练习

1.练习一: 各部门工资最高的员工（难度：中等）创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。创建Department 表，包含公司所有部门的信息。编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。练习二: 换座位（难度：中等）小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们
复制链接

扫一扫