Mysql（4）

写在前面：
时间原因没有实际操作，博客里内容都是抄的，助教不要看了，谢谢。后期有时间一定补上。

项目七

-- CREATE TABLE Employee(
--     Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
--     Name VARCHAR(10) NOT NULL,
--     Salary INT NOT NULL,
--     DepartmentId INT NOT NULL);

-- INSERT INTO Employee
--     VALUES (1, 'Joe', 70000, 1),
--             (2, 'Henry', 80000, 2),
--             (3, 'Sam', 60000, 2),
--             (4, 'Max', 90000, 1);

-- CREATE TABLE Department(
--     Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
--     Name VARCHAR(30) NOT NULL);

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


SELECT d.Name Department, e.Name Employee ,salary
FROM Employee e 
JOIN Department d
ON e.DepartmentId = d.id
WHERE salary IN
(SELECT MAX(salary)
FROM Employee
GROUP BY DepartmentId);

项目八

项目九

项目十

项目十一

SELECT Score,
(SELECT count(*) FROM Score AS s2 WHERE s2.Score > s1.Score)+1 AS Rank
FROM Score AS s1
ORDER BY Score DESC;