SQL练习题01

一.本次练习用到的三个表

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.01 sec)

dept表

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.01 sec)

salgrade表

+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.01 sec)

二.练习题

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

第一步：查出每个部门的最高薪水：

select deptno,max(sal) as maxsal
from emp
group by deptno;

结果：

+--------+---------+
| deptno | maxsal  |
+--------+---------+
|     10 | 5000.00 |
|     20 | 3000.00 |
|     30 | 2850.00 |
+--------+---------+
3 rows in set (0.00 sec)

第二步：将第一步的查询结果当作一张临时表t

将t和emp表连接，条件为：

e.deptno = d.deptno and e.sal = t.maxsal;

select e.ename,t.deptno,t.maxsal
from emp e
join (select deptno,max(sal) as maxsal from emp group by deptno) as t
on e.deptno = t.deptno and e.sal = t.maxsal;

结果：

+-------+--------+---------+
| ename | deptno | maxsal  |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+
4 rows in set (0.00 sec)

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

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

select deptno,avg(sal) as avgsal
from emp
group by deptno;

结果：

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

第二步：将第一步得到的查询结果当作临时表t

将表t与emp表连接，连接条件：

t.deptno = e.deptno and e.sal > t.avgsal

select e.ename,e.sal,t.deptno,t.avgsal
from emp e
join (select deptno,avg(sal) as avgsal from emp group by deptno) as t
on t.deptno = e.deptno and e.sal > t.avgsal;

结果：

+-------+---------+--------+-------------+
| ename | sal     | deptno | avgsal      |
+-------+---------+--------+-------------+
| ALLEN | 1600.00 |     30 | 1566.666667 |
| JONES | 2975.00 |     20 | 2175.000000 |
| BLAKE | 2850.00 |     30 | 1566.666667 |
| SCOTT | 3000.00 |     20 | 2175.000000 |
| KING  | 5000.00 |     10 | 2916.666667 |
| FORD  | 3000.00 |     20 | 2175.000000 |
+-------+---------+--------+-------------+
6 rows in set (0.00 sec)

3.取得每个部门薪水等级的均值

第一步：找出所有员工的薪水等级

select e.ename,e.deptno,e.sal,s.grade
from emp e
join salgrade s
on e.sal between s.losal and s.hisal
order by e.deptno;

结果：

+--------+--------+---------+-------+
| ename  | deptno | sal     | grade |
+--------+--------+---------+-------+
| KING   |     10 | 5000.00 |     5 |
| MILLER |     10 | 1300.00 |     2 |
| CLARK  |     10 | 2450.00 |     4 |
| SMITH  |     20 |  800.00 |     1 |
| ADAMS  |     20 | 1100.00 |     1 |
| SCOTT  |     20 | 3000.00 |     4 |
| FORD   |     20 | 3000.00 |     4 |
| JONES  |     20 | 2975.00 |     4 |
| MARTIN |     30 | 1250.00 |     2 |
| TURNER |     30 | 1500.00 |     3 |
| BLAKE  |     30 | 2850.00 |     4 |
| ALLEN  |     30 | 1600.00 |     3 |
| JAMES  |     30 |  950.00 |     1 |
| WARD   |     30 | 1250.00 |     2 |
+--------+--------+---------+-------+
14 rows in set (0.00 sec)

第二步：将第一步得到的结果接着按照部门分组，计算每个部门的薪资等级均值

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 |
+--------+----------+
|     10 |   3.6667 |
|     20 |   2.8000 |
|     30 |   2.5000 |
+--------+----------+
3 rows in set (0.00 sec)

4.取得每个部门平均薪水的等级

第一步：取得每个部门的平均薪水

select deptno,avg(sal) as avgsal
from emp
group by deptno;

结果：

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

第二步：将第一步得到的结果看作临时表t，将表t与salgrade表连接

select t.*,s.grade
from (select deptno,avg(sal) as avgsal from emp group by deptno) as t
join salgrade s
on t.avgsal between s.losal and s.hisal;

结果：

+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
+--------+-------------+-------+
3 rows in set (0.00 sec)

5.不使用组函数(MAX)，取得最高薪水(至少使用两种方式)

第一种：使用limit

将薪水降序排列，使用limit，只拿出第一个工资

select sal
from emp
order by sal desc
limit 1;

结果：

+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
1 row in set (0.00 sec)

第二种：使用自连接

第一步：将emp表分为a表和b表，找出a表中薪水比b表低的所有人，然后去重

select distinct a.sal 
from emp a
join emp b
on a.sal < b.sal;

结果：

+---------+
| sal     |
+---------+
|  800.00 |
| 1250.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 1300.00 |
| 1600.00 |
| 2850.00 |
| 2450.00 |
| 2975.00 |
| 3000.00 |
+---------+
11 rows in set (0.00 sec)

第二步：再找出不在上一步查询结果中的那个人的工资。

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.00 sec)

6.取得平均薪水最高的部门的部门编号(给出至少两种方案)

第一种：使用limit

第一步：先取得每个部门的平均薪资

select deptno,avg(sal) as avgsal from emp group by deptno;

结果：

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

第二步：将第一步得到的每个部门的平均薪资降序排列，用limit拿到第一个部门编号

select deptno from emp group by deptno order by avg(sal) desc limit 1;

结果：

+--------+
| deptno |
+--------+
|     10 |
+--------+
1 row in set (0.00 sec)

第二种：使用分组函数(MAX)

第一步：先取得每个部门的平均薪资

select deptno,avg(sal) as avgsal from emp group by deptno;

结果：

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

第二步：找出以上结果中avgsal最大的值

select max(t.avgsal) 
from (select deptno,avg(sal) as avgsal from emp group by deptno) as t;

结果：

+---------------+
| max(t.avgsal) |
+---------------+
|   2916.666667 |
+---------------+
1 row in set (0.00 sec)

第三步：再根据最大平均薪资值找到相应部门编号

select deptno
from emp 
group by deptno
having avg(sal) = (select max(t.avgsal) from (select deptno,avg(sal) as avgsal from emp group by deptno) as t);

结果：

+--------+
| deptno |
+--------+
|     10 |
+--------+
1 row in set (0.00 sec)

7.取得平均薪水最高的部门的部门名称

select d.dname,avg(e.sal) as avgsal
from emp e
join dept d
on e.deptno = d.deptno
#只有分组的字段才能跟在select后面
group by d.dname
order by avgsal desc 
limit 1;

结果：

+------------+-------------+
| dname      | avgsal      |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+
1 row in set (0.00 sec)

8.求平均薪水的等级最低的部门的部门名称

第一步：获取每个部门的平均薪水，按部门名称分组

select d.dname,avg(e.sal) as avgsal
from emp e
join dept d
on e.deptno = d.deptno
group by d.dname;

结果：

+------------+-------------+
| dname      | avgsal      |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
| RESEARCH   | 2175.000000 |
| SALES      | 1566.666667 |
+------------+-------------+
3 rows in set (0.02 sec)

第二步：将第一步得到的结果看作临时表t，查询每个部门的平均薪水对应的薪资等级

select t.*,s.grade
from (select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) as t
join salgrade s
on t.avgsal between s.losal and s.hisal;

结果：

+------------+-------------+-------+
| dname      | avgsal      | grade |
+------------+-------------+-------+
| SALES      | 1566.666667 |     3 |
| ACCOUNTING | 2916.666667 |     4 |
| RESEARCH   | 2175.000000 |     4 |
+------------+-------------+-------+
3 rows in set (0.00 sec)

第三步：获取平均薪资最低的薪资等级

平均薪资最低的薪资等级一定是最低的

先获取最低平均薪资

select avg(sal) as avgsal
from emp
group by deptno
order by avgsal asc
limit 1;

再获取相应的最低薪资等级

select s.grade
from salgrade s
join (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) as t
on t.avgsal between s.losal and s.hisal;

第四步：拿第二步中查询到的各部门平均薪资等级与第三步中查询到的最低薪资等级相比对

select t.*,s.grade
from (select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) as t
join salgrade s
on t.avgsal between s.losal and s.hisal
where s.grade = (select s.grade from salgrade s join (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) as t on t.avgsal between s.losal and s.hisal);

从而得到平均薪资等级最低的部门的部门名称：

+-------+-------------+-------+
| dname | avgsal      | grade |
+-------+-------------+-------+
| SALES | 1566.666667 |     3 |
+-------+-------------+-------+
1 row in set (0.01 sec)