第六章：决胜秋招

Section A

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

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

CREATE TABLE Employee
(Id  VARCHAR(3),
Name VARCHAR(20),
Salary INTEGER,
DepartmentId VARCHAR(3),
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');

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

CREATE TABLE Department
(Id  VARCHAR(3),
Name VARCHAR(20),
PRIMARY KEY (Id));

INSERT INTO Department VALUES('1', 'IT');
INSERT INTO Department VALUES('2', 'Sales');

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

 

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

建立座位表：

CREATE TABLE seat
(id  INTEGER,
student VARCHAR(20),
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, 'Jeames');

换座位：

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

-- 建立分数表
CREATE TABLE score
(class  VARCHAR(3),
score_avg INTEGER,
PRIMARY KEY (class));

INSERT INTO score VALUES('1', 93);
INSERT INTO score VALUES('2', 93);
INSERT INTO score VALUES('3', 93);
INSERT INTO score VALUES('4', 93);

-- 目前有如下三种排序结果，请根据查询结果书写出查询用 sql
SELECT class,score_avg
      ,RANK() OVER (ORDER BY score_avg) AS rank1
      ,DENSE_RANK() OVER (ORDER BY score_avg) AS rank2
      ,ROW_NUMBER() OVER (ORDER BY score_avg) AS rank3
  FROM score;  

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

select distinct num as ConsecutiveNums
from
(select p1.id, p1.num, p2.num as next_num, p3.num as next_next_num
from problem4 p1, problem4 p2, problem4 p3
where p1.id = p2.id-1 and p2.id = p3.id-1) as p4
where p4.num=p4.next_num and p4.num=p4.next_next_num;

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

(select pslist.id, 'Root' as Type
from
	(select tree.id, tree.p_id, scount.son_number
	from tree
	left join 
		(select p_id, count(*) as son_number
		from tree
		group by p_id) as scount
	on tree.id=scount.p_id) as pslist
where pslist.p_id is null and pslist.son_number > 0)

union

(select pslist.id, 'Inner' as Type
from
	(select tree.id, tree.p_id, scount.son_number
	from tree
	left join 
		(select p_id, count(*) as son_number
		from tree
		group by p_id) as scount
	on tree.id=scount.p_id) as pslist
where pslist.p_id >0 and pslist.son_number > 0)

union

(select pslist.id, 'Leaf' as Type
from
	(select tree.id, tree.p_id, scount.son_number
	from tree
	left join 
		(select p_id, count(*) as son_number
		from tree
		group by p_id) as scount
	on tree.id=scount.p_id) as pslist
where pslist.p_id >0 and pslist.son_number is null);

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

Select problem6.Name
from problem6, 
	(select ManagerId, count(*) as count
	from problem6
	group by ManagerId) as manager
where problem6.Id = manager.ManagerId and manager.count>=5;

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

create view a as 
(select slist.question_id, answer_count/show_count as answer_rate
	from 
		(select question_id, count(*) as show_count
		from problem7
		where action = 'show'
		group by question_id) as slist
	inner join
		(select question_id, count(*) as answer_count
		from problem7
		where action = 'answer'
		group by question_id) as alist
	on slist.question_id=alist.question_id);

select question_id from a
where answer_rate = (select max(answer_rate) from a);

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

select Department.Name as Department, ranked_list.Name as Employee, ranked_list.Salary
from
	(select DepartmentId, Name, Salary,
			rank() over (partition by DepartmentId
						order by Salary) as ranking
	from Employee) as ranked_list,
	Department
where ranked_list.ranking<4 and Department.Id = ranked_list.DepartmentId
order by Department, Salary Desc;

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

SELECT ROUND(min(SQRT(POW(p1.x - p2.x, 2) + POW((p1.y - p2.y), 2))), 2) as "shortest"
FROM point_2d as p1
         CROSS JOIN point_2d as p2
WHERE p1.x < p2.x
   or p1.y < p2.y;

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

create view clean_list as 
	(select * from Trips
	where Client_Id <>
		(Select Users_Id as banned_id
		from Users
		where Banned = 'Yes'));

Select p1.Request_at as Day, p2.canceled_trip/p1.total_trip as Cancellation_Rate
from 
	(select Request_at, count(*) as total_trip
	from clean_list
	group by Request_at) p1,

	(select Request_at, count(*) as canceled_trip
	from clean_list
	where Status = 'cancelled_by_driver' or Status = 'cancelled_by_client'
	group by Request_at) p2
where p1.Request_at = p2.Request_at;