MySQL基础练习题：习题2-3

这部分主要是为了帮助大家回忆回忆MySQL的基本语法，数据库来自于MySQL的官方简化版，题目也是网上非常流行的35题。这些基础习题基本可以涵盖面试中需要现场写SQL的问题。上期帮助大家建立数据库，导入数据，接下来让我们继续练习。

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

分析一下

其实这道题和上一题非常像，只是从一个最高变成了平均，依旧是只需要emp一张表，然后join的时候要用>判断。

步骤一： 先求出每个部门的平均薪水

(root@localhost) [employees]>select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.26 sec)(root@localhost) [employees]>

步骤二： 自己和自己join然后部门号相同，但是工资>平均工资

(root@localhost) [employees]>select a.deptno,a.ename,a.sal,b.avgsal from emp a join (select deptno,avg(sal) as avgsal from emp group by deptno) b on a.sal>b.avgsal and a.deptno=b.deptno;
+--------+-------+---------+-------------+
| deptno | ename | sal     | avgsal      |
+--------+-------+---------+-------------+
|     30 | ALLEN | 1600.00 | 1566.666667 |
|     20 | JONES | 2975.00 | 2175.000000 |
|     30 | BLAKE | 2850.00 | 1566.666667 |
|     20 | SCOTT | 3000.00 | 2175.000000 |
|     10 | KING  | 5000.00 | 2916.666667 |
|     20 | FORD  | 3000.00 | 2175.000000 |
+--------+-------+---------+-------------+
6 rows in set (0.01 sec)(root@localhost) [employees]>

取得部门中所有人的平均的薪水等级

接下来就会复杂一些，我们看看两张表如何查询数据

分析一下

部门中这个好理解，就是同一部门的所有人，那什么是平均薪水等级？

就好比张三：1 然后李四：2，俩人一平均 1.5

那就好办了先计算每个人的薪水等级，然后根据部门分组，由于使用了分组函数，所以只能显示参加分组的字段和分组函数

步骤一： 计算每个人的薪水等级

(root@localhost) [employees]>select a.deptno,a.ename,a.sal,b.grade from emp a join salgrade b on a.sal between b.losal and b.hisal order by a.deptno;
+--------+--------+---------+-------+
| deptno | ename  | sal     | grade |
+--------+--------+---------+-------+
|     10 | CLARK  | 2450.00 |     4 |
|     10 | KING   | 5000.00 |     5 |
|     10 | MILLER | 1300.00 |     2 |
|     20 | SIMITH |  800.00 |     1 |
|     20 | JONES  | 2975.00 |     4 |
|     20 | SCOTT  | 3000.00 |     4 |
|     20 | ADAMS  | 1100.00 |     1 |
|     20 | FORD   | 3000.00 |     4 |
|     30 | ALLEN  | 1600.00 |     3 |
|     30 | WARD   | 1250.00 |     2 |
|     30 | MARTIN | 1250.00 |     2 |
|     30 | BLAKE  | 2850.00 |     4 |
|     30 | TURNER | 1500.00 |     3 |
|     30 | JAMES  |  950.00 |     1 |
+--------+--------+---------+-------+
14 rows in set (0.04 sec)(root@localhost) [employees]>

我这里又按照部门编号升序排序。

步骤二，join，group再求均值

(root@localhost) [employees]>select a.deptno,avg(grade) as AvgSalGrade from emp a join salgrade b on a.sal between b.losal and b.hisal group by a.deptno;
+--------+-------------+
| deptno | AvgSalGrade |
+--------+-------------+
|     20 |      2.8000 |
|     30 |      2.5000 |
|     10 |      3.6667 |
+--------+-------------+
3 rows in set (0.04 sec)(root@localhost) [employees]>