wonderful-sql Task06
Section A
练习一: 各部门工资最高的员工（难度：中等）
- 创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
CREATE TABLE `Employee` (
`Id` INT PRIMARY KEY,
`Name` VARCHAR ( 20 ),
`Salary` INT,
`DepartmentId` INT
);
INSERT INTO `Employee`
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 ( 20 )
);
INSERT INTO `Department`
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 Employee GROUP BY DepartmentId );
练习二: 换座位（难度：中等）
小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的id是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
请创建如下所示seat表：
CREATE TABLE seat (
id INT PRIMARY KEY,
student VARCHAR ( 20 )
);
INSERT INTO seat
VALUES
( 1, "Abbot" ),
( 2, "Doris" ),
( 3, "Emerson" ),
( 4, "Green" ),
( 5, "Jeames" );
假如数据输入的是上表，则输出结果如下：
UPDATE seat s1,
seat s2
SET s1.student = s2.student,
s2.student = s1.student
WHERE
s1.id = s2.id - 1
AND s1.id % 2 = 1;
SELECT
*
FROM
seat;
练习三: 分数排名（难度：中等）
假设在某次期末考试中，二年级四个班的平均成绩分别是 93、93、93、91。
CREATE TABLE grade (
class INT,
score_avg INT
);
INSERT INTO grade ( class, score_avg )
VALUES
( 1, 93 ),
( 2, 93 ),
( 3, 93 ),
( 4, 91 );
目前有如下三种排序结果，请根据查询结果书写出查询用 sql
SELECT
class,
score_avg,
RANK() OVER ( ORDER BY score_avg DESC ) AS rank1,
DENSE_RANK() OVER ( ORDER BY score_avg DESC ) AS rank2,
ROW_NUMBER() OVER ( ORDER BY score_avg DESC ) AS rank3
FROM
grade
ORDER BY
class;
练习四：连续出现的数字（难度：中等）
编写一个 SQL 查询，查找所有至少连续出现三次的数字。
CREATE TABLE `Logs` ( `Id` INT, `Num` INT );
INSERT INTO `Logs`
VALUES
( 1, 1 ),
( 2, 1 ),
( 3, 1 ),
( 4, 2 ),
( 5, 1 ),
( 6, 2 ),
( 7, 2 );
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。
SELECT DISTINCT Num AS ConsecutiveNums
FROM (
SELECT *,
@counter := IF(@prevNum = Num, @counter + 1, 1) AS counter,
@prevNum := Num
FROM Logs, (SELECT @prevNum := NULL, @counter := 0) AS vars
ORDER BY Id
) AS t
WHERE counter >= 3;
练习五：树节点 （难度：中等）
对于tree表，id是树节点的标识，p_id是其父节点的id。
CREATE TABLE tree ( id INT, p_id INT );
INSERT INTO tree
VALUES
( 1, NULL ),
( 2, 1 ),
( 3, 1 ),
( 4, 2 ),
( 5, 2 );
每个节点都是以下三种类型中的一种：
- Root: 如果节点是根节点。
- Leaf: 如果节点是叶子节点。
- Inner: 如果节点既不是根节点也不是叶子节点。
写一条查询语句打印节点id及对应的节点类型。按照节点id排序。上面例子的对应结果为：
SELECT
t.id,
CASE
WHEN t.p_id IS NULL THEN
'Root'
WHEN EXISTS ( SELECT * FROM tree WHERE p_id = t.id ) THEN
'Inner' ELSE 'Leaf'
END AS Type
FROM
tree t;
说明
- 节点’1’是根节点，因为它的父节点为NULL，有’2’和’3’两个子节点。
- 节点’2’是内部节点，因为它的父节点是’1’，有子节点’4’和’5’。
- 节点’3’，‘4’，'5’是叶子节点，因为它们有父节点但没有子节点。
下面是树的图形：
1
/ \
2 3
/ \
4 5
注意
如果一个树只有一个节点，只需要输出根节点属性。
练习六：至少有五名直接下属的经理 （难度：中等）
Employee表包含所有员工及其上级的信息。每位员工都有一个Id，并且还有一个对应主管的Id（ManagerId）。
CREATE TABLE Employee ( Id INT, `Name` VARCHAR ( 50 ), Department VARCHAR ( 50 ), ManagerId INT );
INSERT INTO Employee ( Id, `Name`, Department, ManagerId )
VALUES
( 101, 'John', 'A', NULL ),
( 102, 'Dan', 'A', 101 ),
( 103, 'James', 'A', 101 ),
( 104, 'Amy', 'A', 101 ),
( 105, 'Anne', 'A', 101 ),
( 106, 'Ron', 'B', 101 );
针对Employee表，写一条SQL语句找出有5个下属的主管。对于上面的表，结果应输出：
注意:
没有人向自己汇报。
浙公网安备 33010602011771号