广州网站建设推广方法,网站建站网站 小说,怎么看网站是用什么系统做的,手机好在百度做网站吗教程链接#xff1a;Datawhale - 一个热爱学习的社区
换硬盘重装了系统#xff0c;后面应该也不会用到mysql#xff0c;不装环境了#xff0c;没有截图。
Section A
练习一: 各部门工资最高的员工#xff08;难度#xff1a;中等#xff09;
创建Employee 表#x… 教程链接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%20 THEN id-1 WHEN id(SELECT COUNT(*) FROM seat) THEN id1 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;
