查询练习

1，求部门平均薪水，按照部门分组求平均值，并显示平均值的薪水等级

select a.deptno, a.avgsal, SALGRADE.grade 
from 
(select deptno, avg(sal) avgsal 
from EMP
group by deptno) as a 
inner join SALGRADE
on a.avgsal between SALGRADE.losal and SALGRADE.hisal;

2，使用自连接查询最高薪水

select *
from EMP
where EMP.sal not in 
(select a.sal 
from EMP as a 
inner join EMP as b 
on a.sal < b.sal)

3，取出平均薪水最高的部门编号

select deptno, avg(sal) avgsal 
from EMP  
group by deptno 
having avgsal = 
(select max(avgsal) maxsal 
from 
(select deptno, avg(sal) avgsal 
from EMP 
group by deptno) as a)