一些简单的SQL查询练习

表结构

员工表：

部门表：

薪水等级表：

 

练习题目

1.统计各部门员工数量 显示部门名称

思路：在连接两张表时，为了保证即便没有员工，部门也一定显示，因此用部门表左外连接员工表（或者员工表右外连接部门表）。

select dept.deptno, dname, count(empno) as count
from dept left outer join emp
on emp.deptno = dept.deptno
group by deptno

查询结果：

 

2.取得每个部门最高薪水的员工名称

思路：首先查询出各部门的最高薪水，再将其作为临时表d，和员工表e内连接，连接条件为部门和薪水都相同。最后为了可读性，按照部门编号排序。

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

查询结果：

 

3.薪水在部门平均薪水之上的员工

思路：首先查询出各部门的平均薪水，再将其作为临时表a，和员工表e内连接，连接条件为部门相同，然后只需要通过where语句筛选工资高于平均工资的员工即可。最后为了可读性，按照部门编号排序。

select e.ename, e.sal, e.deptno, a.avgsal
from (select deptno, avg(sal) as avgsal from emp group by deptno) a join emp e
on e.deptno = a.deptno
where sal > avgsal
order by deptno

查询结果：

 

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

思路：首先连接emp表和salgrade表，连接条件为sal处于losal和hisal之间，此时我们获得了各员工对应的薪水等级，然后只需要通过部门分组，获得平均薪水即可。

select deptno, avg(grade) as avggrade
from emp join salgrade
on emp.sal between salgrade.losal and salgrade.hisal
group by deptno

查询结果：

 

5.取得最高薪水的三种方案

方案一：max函数

select max(sal) as maxsal
from emp

方案二：降序+limit

select sal as maxsal
from emp
order by sal desc
limit 1

方案三：表的自连

select sal as maxsal
from emp
where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal)

查询结果：

 

 6.取得平均薪水最高的部门的部门编号

思路：首先查询各部门平均薪水，然后在此基础上使用max函数或者降序+limit。

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

查询结果：

 

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

思路：只需在前一题的基础上连接部门表，连接条件为部门编号相同。

select dname, max(avgsal)
from (select deptno, avg(sal) as avgsal from emp group by deptno) a join dept
on a.deptno = dept.deptno

查询结果：