简单学SQL——练习题

练习：

1. 各部门工资最高的员工

练习所需的表
员工表

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

部门表

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

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

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

答案：

select  
d.name,
em.name,
em.salary
from employee as em
inner join department as d
on em.department_Id= d.id
where  em.salary in (select max(salary) from employee
					 group by department_Id)
order by salary desc;

2.换座位

问题：小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。其中纵列的id是连续递增的
小美想改变相邻俩学生的座位
换座位之前：

+---------+---------+
|    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) =0 then id-1
end 
) as 'id', student from  stu3
order by id;

3. 分数排名

问题：如果两个分数相同，则两个分数排名（Rank）相同
未排序：

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

排序后（序列号连续）

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

答案：

select score,dense_rank() over ( order by score desc) as R
from s;

排序后（序列号不连续）

答案：

select score,rank() over ( order by score desc)  as R
from s;

4.出现三次及以上的数字

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

查找所有至少连续出现三次的数字
答案(有bug，待完善):

select n1.num
from  numble as n1,numble as n2,numble as n3
where n1.id+1 = n2.id and n2.id +1=n3.id and n1.num =n2.num and n2.num = n3.num;

查询出现3次及以上的数字
答案：

select num
from 
(select num,count(num) as 'ConsecutiveNums'
from numble
group by num ) as new
where ConsecutiveNums >=3;

6.至少有五名直接下属的经理

+------+----------+-----------+----------+
|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       |
+------+----------+-----------+----------+

查询结果：

+-------+
| Name  |
+-------+
| John  |
+-------+

答案：

select em.name 
from (select ManagerId,count(ManagerId) as 'times'
from employee
group by ManagerId
) as new inner join employee as em
on new.ManagerId = em.id 
where times >=5;

7. 查询回答率最高的问题

数据表：

答案：

select  new.question_id,count(new.question_id) as 'answer_num'
from (select  action,question_id 
from question
where action= 'answer') as new
group by new.question_id
order by answer_num desc;

8. 各部门前3高工资的员工

数据表：

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
+----+-------+--------+--------------+

查询结果：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

答案（语句有改善空间）：

select Department,Employee,salary
from (
select Department,Employee,salary,
rank() over (PARTITION BY Department  order by salary desc)  as ranking
from (
select d.name as 'Department',em.name as 'Employee',em.salary as 'salary'
from employee as em inner join department as d
on d.id= em.department_Id ) as d_salary
)as display
where display.ranking <= 3;