SQL练习题

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

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');
  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
--查找
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

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

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;

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

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

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

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; 

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

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 

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

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

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

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高工资的员工（难度：中等）

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

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

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

Section B 练习一：行转列

假设 A B C 三位小朋友期末考试成绩如下所示：

练习二：列转行
假设 A B C 三位小朋友期末考试成绩如下所示：

练习三：谁是明星带货主播？

假设，某平台2021年主播带货销售额日统计数据如下：

表名 anchor_sales

定义：如果某主播的某日销售额占比达到该平台当日销售总额的 90% 及以上，则称该主播为明星主播，当天也称为明星主播日。

请使用 SQL 完成如下计算：

a. 2021年有多少个明星主播日？

b. 2021年有多少个明星主播？