(202307)wonderful-sql:决胜秋招(task6)

 教程链接：Datawhale - 一个热爱学习的社区

换硬盘重装了系统，后面应该也不会用到mysql，不装环境了，没有截图。

Section A

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

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

CREATE TABLE Employee (Id INT PRIMARY KEY,Name VARCHAR(255),Salary INT,DepartmentId INT,CONSTRAINT fk_departmentFOREIGN KEY (DepartmentId)REFERENCES Department(Id)
);INSERT INTO Employee (Id, Name, Salary, DepartmentId) VALUES (1, 'Joe', 70000, 1),(2, 'Henry', 80000, 2),(3, 'Sam', 60000, 2),(4, 'Max', 90000, 1);

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

CREATE TABLE Department (Id INT PRIMARY KEY,Name VARCHAR(255)
);INSERT INTO Department (Id, Name) VALUES (1, 'IT'),(2, 'Sales');

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

SELECT d.Name AS Department, e.Name AS Employee, e.Salary
FROM Employee e
JOIN Department d ON e.DepartmentId = d.Id
WHERE (e.DepartmentId, e.Salary) IN (SELECT DepartmentId, MAX(Salary)FROM EmployeeGROUP BY DepartmentId
);

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

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

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

SELECT class, score_avg, RANK() OVER (ORDER BY score_avg DESC) AS rank1, RANK() OVER (PARTITION BY score_avg ORDER BY class) AS rank2, RANK() OVER (ORDER BY score_avg) AS rank3 
FROM (SELECT class, AVG(score) AS score_avg FROM scores GROUP BY class
) AS t
ORDER BY class;

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

SELECT DISTINCT Num AS ConsecutiveNums
FROM (SELECT Num, ROW_NUMBER() OVER (PARTITION BY Num ORDER BY Id) AS row_num, ROW_NUMBER() OVER (PARTITION BY Num ORDER BY Id) - Id AS diffFROM Logs
) AS t
WHERE diff = 2
ORDER BY ConsecutiveNums;

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

SELECT Name
FROM Employee
WHERE ManagerId IS NULL
GROUP BY Name
HAVING COUNT(*) = 5;