【DatawhaleSQL学习打卡】Task06:决胜秋招Section A

Section A

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

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

CREATE DATABASE IF NOT EXISTS exercises;
use exercises;
drop table if exists Employee;
create table Employee
(Id CHAR(1) NOT NULL,
Name VARCHAR(5) NOT NULL,
Salary CHAR(5),
DepartmentId CHAR(1) NOT NULL,
PRIMARY KEY (Id)
);
insert into Employee values(1, 'Joe' ,70000,1);
insert into Employee values(2,'Henry',80000,2);
insert into Employee values(3,'Sam',60000,2);
insert into Employee values(4,'Max',90000,1);

输出结果： 

2、创建Department 表，包含公司所有部门的信息。 

DROP TABLE IF EXISTS Department;
CREATE TABLE Department
(Id CHAR(1) NOT NULL,
Name VARCHAR(5) NOT NULL,
PRIMARY KEY (Id)
);
INSERT INTO Department VALUES (1, 'IT');
INSERT INTO Department VALUES (2, 'Sales');

输出结果：

 

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

解题思路：目标是选出每个部门工资最高的员工，先按DepartmentId 将Department表和Employee表连接起来，再从中根据E.DepartmentId=Emp.DepartmentId 将每个部门最高工资的员工筛选出来。

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

输出结果：

 

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

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

1、创建seat表

DROP TABLE IF EXISTS seat;
CREATE TABLE seat
(id CHAR(1) NOT NULL,
student VARCHAR(8) NOT NULL,
PRIMARY KEY(id));

INSERT INTO seat Values (1,'Abbot');
INSERT INTO seat Values (2,'Doris');
INSERT INTO seat Values (3,'Emerson');
INSERT INTO seat Values (4,'Green');
INSERT INTO seat Values (5,'James');

输出结果：  

2、改变相邻俩学生的座位，如果学生人数是奇数，则不需要改变最后一个同学的座位。

SELECT * FROM exercises.seat;
select p.id ,p.student
from(
		select id-1 as id,student from seat where mod(id,2)=0
		union
		select id+1 as id,student from seat where mod(id,2)=1 and id != (select count(*) from seat)
		union
		select id,student from seat where id = (select count(*) from seat)
    )as p
order by id;

输出结果：  

解题思路：所有偶数行前移1；所有奇数行后移1；最后一行为奇数行，不做移动

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

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

1、创建grade表

DROP TABLE IF EXISTS grade;
CREATE TABLE grade
(class CHAR(1) NOT NULL,
score_avg CHAR(2) NOT NULL,
PRIMARY KEY(class));

INSERT INTO grade VALUES (1,93);
INSERT INTO grade VALUES (2,93);
INSERT INTO grade VALUES (3,93);
INSERT INTO grade VALUES (4,91);

输出结果： 

 

2、根据分数进行降序排序，应用窗口函数。

SELECT * FROM exercises.grade;
SELECT class,
       score_avg,
       RANK() OVER w AS 'standard_rank',
       DENSE_RANK() OVER w AS 'dense_rank',
       ROW_NUMBER() OVER w AS 'row_number_rank'
FROM grade
WINDOW w AS (ORDER BY score_avg DESC);

输出结果： 

 

 

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

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

1、创建log表

DROP TABLE IF EXISTS log;
CREATE TABLE log
(Id CHAR(1) NOT NULL,
Num CHAR(1) NOT NULL,
PRIMARY KEY(Id));

INSERT INTO log VALUES (1,1);
INSERT INTO log VALUES (2,1);
INSERT INTO log VALUES (3,1);
INSERT INTO log VALUES (4,2);
INSERT INTO log VALUES (5,1);
INSERT INTO log VALUES (6,2);
INSERT INTO log VALUES (7,2);

输出结果： 

 

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

SELECT * FROM exercises.log;
select p.Num as ConsecutiveNums
from (select Id,Num,
						 lead(Num,1) over ()as Num2,
						 lead(Num,2) over ()as Num3
			from log)as p
where p.Num = p.Num2
	and p.Num = p.Num3;

输出结果： 

  

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

对于tree表，id是树节点的标识，p_id是其父节点的id。写一条查询语句打印节点id及对应的节点类型。按照节点id排序。

1、创建tree表

DROP TABLE IF EXISTS tree;
CREATE TABLE tree
(id CHAR(1) NOT NULL,
p_id CHAR(1),
PRIMARY KEY(id));

INSERT INTO tree VALUES (1,NULL);
INSERT INTO tree VALUES (2,1);
INSERT INTO tree VALUES (3,1);
INSERT INTO tree VALUES (4,2);
INSERT INTO tree VALUES (5,2);

输出结果：

 

 2、解题思路：查询结果分三种情况，root、inner、leaf，故采用case查询语句。当父节点为空时，该节点为根节点root；当节点没有子节点，即不在p_id字段的节点，即为叶节点leaf；其它节点即为中间节点Inner。注:如果not in 后面跟的是子查询，子查询中只要包含一个null的返回值，则会造成整个not in字句返回空值，查询不会返回任何结果

SELECT * FROM exercises.tree;
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;

输出结果：

 

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

Employee5表包含所有员工及其上级的信息。每位员工都有一个Id，并且还有一个对应主管的Id（ManagerId）。针对Employee5表，写一条SQL语句找出有5个下属的主管。

1、创建员工信息表employee5

DROP TABLE IF EXISTS Employee5;
CREATE TABLE Employee5
(Id VARCHAR(6) NOT NULL,
Name VARCHAR(5) NOT NULL,
Department CHAR(1) NOT NULL,
ManagerId VARCHAR(6),
PRIMARY KEY (Id));
INSERT INTO Employee5 VALUES ('101', 'John', 'A', NULL);
INSERT INTO Employee5 VALUES ('102', 'Dan', 'A', '101');
INSERT INTO Employee5 VALUES ('103', 'James', 'A', '101');
INSERT INTO Employee5 VALUES ('104', 'Amy', 'A', '101');
INSERT INTO Employee5 VALUES ('105', 'Anne', 'A', '101');
INSERT INTO Employee5 VALUES ('106', 'Ron', 'B', '101');

 输出结果：

 2、在子查询中使用聚合函数作为窗口函数实现

SELECT * FROM exercises.Employee5;
SELECT Name 
FROM Employee5 
WHERE Id IN (SELECT ManagerID
             FROM (SELECT Id,
                          Name,
                          ManagerId, 
                          COUNT(Id) OVER (PARTITION BY ManagerId ORDER BY ManagerId) AS num_same_manager
			  FROM Employee5) AS temp 
	     WHERE num_same_manager >= 5);

输出结果：

 

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

求出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语句找出回答率最高的 question_id。

1、创建survey_log表格

DROP TABLE IF EXISTS survey_log;
CREATE TABLE survey_log
(uid CHAR(1) NOT NULL,
action VARCHAR(6),
question_id CHAR(3) NOT NULL,
answer_id VARCHAR(12),
q_num CHAR(1) NOT NULL,
timestamp CHAR(3));

INSERT INTO survey_log VALUES (5,'SHOW',285,NULL,1,123);
INSERT INTO survey_log VALUES (5,'ANSWER',285,'124124',1,124);
INSERT INTO survey_log VALUES (5,'SHOW',369,NULL,2,125);
INSERT INTO survey_log VALUES (5,'SKIP',369,NULL,2,126);
INSERT INTO survey_log VALUES (6,'SHOW',285,NULL,1,123);
INSERT INTO survey_log VALUES (6,'SKIP',285,NULL,1,124);

输出结果：

 

2、解题思路：首先是进行分组，分别统计出各个问题action各个类别的数目，再进行计算回答率进行排序。以问题id（question_id)进行分组，然后分别计算各个问题（分组）的回答情况，进行降序排序，输出第一行记录，即为最高回答率。注：回答率=action为(answer) 个数除以action为(show)个数。

SELECT * FROM exercises.survey_log;
select question_id as survey_log
from survey_log
group by question_id
order by count(answer_id)/count(action='show')desc
limit 1;

输出结果：

 

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

将练习一中的 employee 表清空，重新插入以下数据（也可以复制练习一中的 employee 表，再插入第5、第6行数据），编写一个 SQL查询，找出每个部门工资前三高的员工。此外，请考虑实现各部门前N高工资的员工功能。

1、重新创建employee7表，在employee表内新增两个值

DROP TABLE IF EXISTS employee7;
create table employee7
(Id CHAR(1) NOT NULL,
Name VARCHAR(5) NOT NULL,
Salary CHAR(5),
DepartmentId CHAR(1) NOT NULL,
PRIMARY KEY (Id)
);
 
insert into employee7 values(1,'Joe',70000,1);
insert into employee7 values(2,'Henry',80000,1);
insert into employee7 values(3,'Sam',60000,1);
insert into employee7 values(4,'Max',90000,1);
insert into employee7 values(5,'Janet',69000,1);
insert into employee7 values(6,'Randy',85000,1);

输出结果：

 

2、编写一个 SQL查询，找出每个部门工资前三高的员工，此外，请考虑实现各部门前N高工资的员工功能。解题思路：分别查询各部门员工工资逆序排序结果，取各部门工资前三的记录的并，得到结果。

SELECT * FROM exercises.employee7;
(SELECT D.Name AS Department,
       E.Name AS Employee,
       E.Salary
FROM Employee7 E INNER JOIN Department D
ON E.DepartmentId = D.Id
WHERE D.name = 'Sales'
ORDER BY E.Salary
LIMIT 3)
UNION
(SELECT D.Name AS Department,
       E.Name AS Employee,
       E.Salary
FROM Employee7 E INNER JOIN Department D
ON E.DepartmentId = D.Id
WHERE D.name = 'IT'
ORDER BY E.Salary
LIMIT 3);

输出结果：

 

tips：两集合相并(union)时，order by只能有一个，且写在最后，否则就会报错，但union前后集合用括号括起来后不存在此问题。limit函数同上。

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

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

1、创建point_2d表：

DROP TABLE IF EXISTS point_2d;
CREATE TABLE point_2d
(x FLOAT NOT NULL,
y FLOAT not NULL,
PRIMARY KEY(x,y)
);

INSERT INTO point_2d VALUES (-1,-1);
INSERT INTO point_2d VALUES (0,0);
INSERT INTO point_2d VALUES (-1,-2); 

输出结果：

  

2、最短距离是1，从点（-1，-1）到点（-1，-2）。

解题思路：计算任意两点之间的距离（使用两个完全相同的表格），采用round,sqrt,power函数相同点不须进行距离的计算对计算结果进行排序，找出距离最短的点。

SELECT * FROM exercises.point_2d;
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;

输出结果：

 

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

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）保留两位小数。

1、创建Trips表

DROP TABLE if EXISTS Trips;
CREATE TABLE Trips
(Id INT,
Client_Id INT,
Driver_Id INT,
City_Id INT,
Status VARCHAR(30),
Request_at DATE,
PRIMARY KEY (Id)
);

INSERT INTO Trips VALUES (1, 1, 10, 1, 'completed', '2013-10-1');
INSERT INTO Trips VALUES (2, 2, 11, 1, 'cancelled_by_driver', '2013-10-1');
INSERT INTO Trips VALUES (3, 3, 12, 6, 'completed', '2013-10-1');
INSERT INTO Trips VALUES (4, 4, 13, 6, 'cancelled_by_client', '2013-10-1');
INSERT INTO Trips VALUES (5, 1, 10, 1, 'completed', '2013-10-2');
INSERT INTO Trips VALUES (6, 2, 11, 6, 'completed', '2013-10-2');
INSERT INTO Trips VALUES (7, 3, 12, 6, 'completed', '2013-10-2');
INSERT INTO Trips VALUES (8, 2, 12, 12, 'completed', '2013-10-3');
INSERT INTO Trips VALUES (9, 3, 10, 12, 'completed', '2013-10-3');
INSERT INTO Trips VALUES (10, 4, 13, 12, 'cancelled_by_driver', '2013-10-3');

输出结果：

 

 2、创建Users表

DROP TABLE if EXISTS Users ;
CREATE TABLE Users 
(Users_Id  INT,
 Banned    VARCHAR(30),
 Role      VARCHAR(30),
PRIMARY KEY (Users_Id)
);

INSERT INTO Users VALUES (1,    'No',  'client');
INSERT INTO Users VALUES (2,    'Yes', 'client');
INSERT INTO Users VALUES (3,    'No',  'client');
INSERT INTO Users VALUES (4,    'No',  'client');
INSERT INTO Users VALUES (10,   'No',  'driver');
INSERT INTO Users VALUES (11,   'No',  'driver');
INSERT INTO Users VALUES (12,   'No',  'driver');
INSERT INTO Users VALUES (13,   'No',  'driver');

输出结果：

  

 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;

输出结果：