MySql 练习题

1、取得每个部门最高薪水的人员名称
2、哪些人的薪水在部门的平均薪水之上
3、取得部门中（所有人的）平均的薪水等级
4、不准用组函数（Max），取得最高薪水（给出两种解决方案）
5、取得平均薪水最高的部门的部门编号
6、取得平均薪水最高的部门的部门名称
7、求平均薪水的等级最低的部门的部门名称
8、取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名
9、取得薪水最高的前五名员工
10、取得薪水最高的第六到第十名员工
11、取得最后入职的 5 名员工

题目所用数据如下：

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

1、取得每个部门最高薪水的人员名称

mysql> select e.ename, e.sal, e.deptno from emp e join (select deptno, max(sal) as sal from emp group by deptno) d on e.sal = d.sal and e.deptno = d.deptno;
+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| BLAKE | 2850.00 |     30 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+
4 rows in set (0.00 sec)

2、哪些人的薪水在部门的平均薪水之上

先取出每个部门的平均工资：

mysql> select deptno, avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

将以上查询结果当做d表，d和emp表连接，条件：部门编号相同，并且emp的sal大于t表的avgsal。

mysql> select e.ename, e.sal from emp e join (select deptno, avg(sal) as avgsal from emp group by deptno) d on e.deptno = d.deptno where e.sal > d.avgsal;
+-------+---------+
| ename | sal     |
+-------+---------+
| ALLEN | 1600.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)

3、取得部门中（所有人的）平均的薪水等级

先查出每个人对应的薪水等级：

mysql> select e.deptno, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
+--------+-------+
| deptno | grade |
+--------+-------+
|     20 |     1 |
|     30 |     3 |
|     30 |     2 |
|     20 |     4 |
|     30 |     2 |
|     30 |     4 |
|     10 |     4 |
|     20 |     4 |
|     10 |     5 |
|     30 |     3 |
|     20 |     1 |
|     30 |     1 |
|     20 |     4 |
|     10 |     2 |
+--------+-------+
14 rows in set (0.00 sec)

再分组取平均即可。

mysql> select e.deptno, avg(s.grade) as avggrade from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;
+--------+----------+
| deptno | avggrade |
+--------+----------+
|     20 |   2.8000 |
|     30 |   2.5000 |
|     10 |   3.6667 |
+--------+----------+
3 rows in set (0.01 sec)

4、不准用组函数（Max），取得最高薪水（给出两种解决方案）

方法一：all函数

mysql> select sal from emp where sal >= all(select sal from emp);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
1 row in set (0.01 sec)

方法二：降序排序，取第一个。

mysql> select sal from emp order by sal desc limit 1;
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
1 row in set (0.00 sec)

方法三：表的自连接

mysql> select sal from emp where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
1 row in set (0.01 sec)

5、取得平均薪水最高的部门的部门编号

方法一：找出每个部门的平均薪水，降序排序，取第一个即可

mysql> select deptno from (select deptno, avg(sal) as avgsal from emp group by deptno order by avgsal desc) a limit 1;
+--------+
| deptno |
+--------+
|     10 |
+--------+
1 row in set (0.00 sec)

方法二：
先找出每个部门的平均薪水

mysql> select deptno, avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

再找出以上结果中avgsal最大的值。

mysql> select deptno, avg(sal) as avgsal from emp group by deptno having avgsal = (select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)

最后取deptno即可：

mysql> select deptno from (select deptno, avg(sal) as avgsal from emp group by deptno having avgsal = (select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t)) a;
+--------+
| deptno |
+--------+
|     10 |
+--------+
1 row in set (0.00 sec)

6、取得平均薪水最高的部门的部门名称

mysql> select d.dname, avg(e.sal) avgsal from emp e join dept d on e.deptno = d.deptno group by e.deptno order by avgsal desc limit 1;
+------------+-------------+
| dname      | avgsal      |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+
1 row in set (0.00 sec)

7、求平均薪水的等级最低的部门的部门名称

第一步：找出每个部门的平均薪水

mysql> select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

第二步：找出每个部门的平均薪水的等级

mysql> select t.deptno, t.avgsal, s.grade from (select deptno, avg(sal) as avgsal from emp e join salgrade s on e.sal between s.losal and s.hisal group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
+--------+-------------+-------+
3 rows in set (0.00 sec)

第三步：升序排序，取第一个。

mysql> select t.deptno, t.avgsal, s.grade from (select deptno, avg(sal) as avgsal from emp e join salgrade s on e.sal between s.losal and s.hisal group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal order by grade limit 1;
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     30 | 1566.666667 |     3 |
+--------+-------------+-------+
1 row in set (0.00 sec)

1、取得每个部门最高薪水的人员名称

2、哪些人的薪水在部门的平均薪水之上

3、取得部门中（所有人的）平均的薪水等级

4、不准用组函数（Max），取得最高薪水（给出两种解决方案）

5、取得平均薪水最高的部门的部门编号

6、取得平均薪水最高的部门的部门名称

7、求平均薪水的等级最低的部门的部门名称

8、取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名

9、取得薪水最高的前五名员工

10、取得薪水最高的第六到第十名员工

11、取得最后入职的 5 名员工