MySQL任务3

项目十: 各部门工资最高的员工

要求：

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

+----+-------+--------+--------------+

| Id | Name  | Salary | DepartmentId |

+----+-------+--------+--------------+

| 1  | Joe   | 70000  | 1            |

| 2  | Henry | 80000  | 2            |

| 3  | Sam   | 60000  | 2            |

| 4  | Max   | 90000  | 1            |

+----+-------+--------+--------------+

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

+----+----------+

| Id | Name     |

+----+----------+

| 1  | IT       |

| 2  | Sales    |

+----+----------+

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

+------------+----------+--------+

| Department | Employee | Salary |

+------------+----------+--------+

| IT         | Max      | 90000  |

| Sales      | Henry    | 80000  |

+------------+----------+--------+

SELECT d.Name Department,e.Name Employee,e.Salary  
FROM Department d,Employee e
WHERE e.DepartmentId = d.Id
AND e.Salary=(
SELECT max(Salary) 
FROM Employee 
WHERE DepartmentId=d.Id); 

项目十一: 换座位

要求：

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

 请创建如下所示seat表：

示例：

+---------+---------+

|    id   | student |

+---------+---------+

|    1    | Abbot   |

|    2    | Doris   |

|    3    | Emerson |

|    4    | Green   |

|    5    | Jeames  |

+---------+---------+

假如数据输入的是上表，则输出结果如下：

+---------+---------+

|    id   | student |

+---------+---------+

|    1    | Doris   |

|    2    | Abbot   |

|    3    | Green   |

|    4    | Emerson |

|    5    | Jeames  |

+---------+---------+

注意：

如果学生人数是奇数，则不需要改变最后一个同学的座位。

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

项目十二: 分数排名

要求：

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

创建以下score表：

+----+-------+

| Id | Score |

+----+-------+

| 1  | 3.50  |

| 2  | 3.65  |

| 3  | 4.00  |

| 4  | 3.85  |

| 5  | 4.00  |

| 6  | 3.65  |

+----+-------+

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

+-------+------+

| Score | Rank |

+-------+------+

| 4.00  | 1    |

| 4.00  | 1    |

| 3.85  | 2    |

| 3.65  | 3    |

| 3.65  | 3    |

| 3.50  | 4    |

+-------+------+

SELECT score,(SELECT COUNT(DISTINCT(score)) FROM scores WHERE score>=s.score ) AS rank
FROM scores s
ORDER BY score DESC;

项目十三：连续出现的数字

要求：

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

+----+-----+

| Id | Num |

+----+-----+

| 1  |  1  |

| 2  |  1  |

| 3  |  1  |

| 4  |  2  |

| 5  |  1  |

| 6  |  2  |

| 7  |  2  |

+----+-----+

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。



+-----------------+

| ConsecutiveNums |

+-----------------+

| 1               |

+-----------------+

SELECT DISTINCT(L1.Num) ConsecutiveNums 
FROM Logs L1,Logs L2,Logs L3
WHERE L1.Id = L2.Id -1 AND L1.Id = L3.Id -2
AND L1.Num = L2.Num AND L2.Num = L3.Num;

项目十四：树节点

要求：

对于 tree 表，id 是树节点的标识，p_id 是其父节点的 id。

+----+------+

| id | p_id |

+----+------+

| 1  | null |

| 2  | 1    |

| 3  | 1    |

| 4  | 2    |

| 5  | 2    |

+----+------+

每个节点都是以下三种类型中的一种：

Leaf: 如果节点是根节点。
Root: 如果节点是叶子节点。
Inner: 如果节点既不是根节点也不是叶子节点。
写一条查询语句打印节点id及对应的节点类型。按照节点id排序。上面例子的对应结果为：

+----+------+

| id | Type |

+----+------+

| 1  | Root |

| 2  | Inner|

| 3  | Leaf |

| 4  | Leaf |

| 5  | Leaf |

+----+------+

说明

节点’1’是根节点，因为它的父节点为NULL，有’2’和’3’两个子节点。
节点’2’是内部节点，因为它的父节点是’1’，有子节点’4’和’5’。
节点’3’，‘4’，'5’是叶子节点，因为它们有父节点但没有子节点。
下面是树的图形：

        1

      /   \

    2       3

  /   \

4       5

SELECT id, IF(ISNULL(p_id), 'Root', IF(id IN (SELECT p_id FROM tree), 'Inner', 'Leaf')) AS TYPE 
FROM tree 
ORDER BY id;

项目十五：至少有五名直接下属的经理

要求：

Employee 表包含所有员工及其上级的信息。每位员工都有一个Id，并且还有一个对应主管的Id（ManagerId）。

+------+----------+-----------+----------+

|Id    |Name 	  |Department |ManagerId |

+------+----------+-----------+----------+

|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个下属的主管。对于上面的表，结果应输出：

+-------+

| Name  |

+-------+

| John  |

+-------+

注意:

没有人向自己汇报。

SELECT a.name Employee
FROM Employee AS a,Employee AS b
WHERE a.ManagerId = b.Id AND a.Salary > b.Salary