MySQL综合练习

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

USE shop;
--建表
CREATE TABLE Employee ( 
	Id INT ( 4 ) PRIMARY KEY, 
	`Name` VARCHAR ( 10 ), 
	Salary INT ( 8 ), 
	DepartmentId INT ( 4 ) );
--插入数据
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 ( 4 ) PRIMARY KEY, 
	`Name` VARCHAR ( 10 ) );
--插入数据
INSERT INTO Department
VALUES
	( 1, 'IT' ),
	( 2, 'Sales' );
SELECT
	*
FROM
	Employee;
-- 查询
SELECT
	b.`Name` AS Department,
	a.`Name` AS Employee,
	a.Salary AS Salary 
FROM
	Employee a
	INNER JOIN Department b ON a.DepartmentId = b.Id 
WHERE
	( a.DepartmentId, a.Salary ) IN ( SELECT DepartmentId, max( salary ) AS Salary FROM Employee GROUP BY DepartmentId );

结果：

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

// 建表略
select (case 
	    when mod(id,2) != 0 and id != counts then id+1
        when mod(id,2) != 0 and id = counts then id
        else id-1 end) as id,student
from 
    seat,
    (select count(*)as counts from seat) as seat_counts
order by id;

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

-- 建表	
CREATE TABLE Scores ( Id INT ( 4 ) PRIMARY KEY, Score DOUBLE(3,2) );
-- 插入数据
INSERT INTO Scores
VALUES
	( 1, 3.50 ),
	( 2, 3.65 ),
	( 3, 4.00 ),
	( 4, 3.85 ),
	( 5, 4.00 ),
	( 6, 3.65 );
-- 查询
SELECT
	Score,
	DENSE_RANK ( ) OVER ( ORDER BY Score DESC ) AS `Rank` 
FROM
	Scores;

结果：

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

select distinct l1.Num as ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
    WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num;

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

SELECT
	Score,
	RANK ( ) OVER ( ORDER BY Score DESC ) AS `Rank` 
FROM
	Scores;

结果：