练习一: 各部门工资最高的员工（难度：中等）
SELECT Department,Name,Salary
FROM Employee as e1
left join Department as d1
on e1.DepartmentId=d1.Id
WHERE Salary in
(SELECT MAX(Salary) as max_salary_dept
FROM Employee as e1
left join Department as d1
on e1.DepartmentId=d1.Id
Group BY Department);
练习二: 换座位（难度：中等）
SELECT new_id as id ,student
CASE WHEN MOD(id,2)>0 THEN id-1ELSE id-1 END AS new_id
FROM seat
ORDER BY new_id ASC;
练习三: 分数排名（难度：中等）
SELECT Score,DENSE_RANK() OVER (ORDER BY score DESC) as Rank
FROM Scores;
练习四：连续出现的数字（难度：中等）
SELECT NUM AS ConsecutiveNums
FROM Logs
GROUP BY NUM
HAVING COUNT(NUM) >=3;
练习五：树节点 （难度：中等）
SELECT idCASE WHEN id IS NULL THEN 'Root')WHEN id in (select distinct id from tree) and id in (select distinct p_id from tree) THEN 'Inner'ELSE 'Leaf'END AS TypeFROM tree;
练习六：至少有五名直接下属的经理 （难度：中等）
SELECT Name
FROM Employee
WHERE Id in
(SELECT ManageId
FROM Employee
GROUP BY ManageId
HAVING COUNT(Id) =5);
练习七: 分数排名 （难度：中等）
SELECT Score,RANK() OVER (ORDER BY Score DESC) as Rank
FROM Scores;
练习九：各部门前3高工资的员工（难度：中等）
SELECT Name as Department,Name as Employee,Salary
FROM Employee as e1
LEFT JOIN Department as d1
ON e1.DepartmentId=d1.Id
WHERE e1.Id in
(SELECT Id
FROM
(SELECT Id,ROW_NUMBER OVER(PARTITION BY DepartmentORDER BY Salary DESC） as dept_rank
FROM Employee)
WHERE dept_rank IN (1,2,3));
练习十：平面上最近距离 (难度: 困难）
SELECT FORMAT(distance,2) as shortest
FROM
(
SELECT POWER(POWER((sx-ex),2)+POWER((sy-ey),2),0.5) as distance
FROM
(
SELECT t1.x as sx,t1.y as sy,t2.x as ex,t2.y as ey
FROM point_2d as t1,point_2d as t2
)
WHERE dIstance >0
ORDER BY dIstance ASC
LIMIT 1
);
练习十一：行程和用户（难度：困难）
SELECT table1.Request_at,FORMAT(ban_num/total,2) AS Cancellation Rate
FROM
(SELECT Request_at as Day,COUNT(Id) as total
FROM Trips AS t1
INNER JOIN Users AS u1
ON t1.Client_Id=u1.Users_Id
WHERE Banned='No' AND (Request_at BETWEEN '2013-10-01' AND '2013-10-03')
GROUP BY Request_at) AS table1
LEFT JOIN
(SELECT Request_at as Day,COUNT(Id) as ban_num
FROM Trips AS t1
INNER JOIN Users AS u1
ON t1.Client_Id=u1.Users_Id
WHERE Banned='No' AND Status like 'cancelled%' AND (Request_at BETWEEN '2013-10-01' AND '2013-10-03')
GROUP BY Request_at)
AS table2
Task 6:综合练习