--分页查询（三种情况）
--1.取前X条数据
--rownum的关键字，代表行号，是系统自动生成的,是从1开始分配的
select e.* from emp e where rownum <= 5
--2.取中间X条数据
select t01.*
from (select e.*, rownum r from emp e) t01
where t01.r > 5
and t01.r <= 10
--3.对数据要进行排序，然后再分页
--搜索出来薪资最高的前五名员工
select t01.*
from (select e.* from emp e order by e.sal desc) t01
where rownum < 6
--取出薪资最高的6~10名
select tt01.* from (select t01.*, rownum r
from (select e.* from emp e order by e.sal desc) t01) tt01
where tt01.r > 5
and tt01.r < 11
--1.求平均薪水最高的部门的部门编号
select e.deptno, avg(e.sal)
from emp e
group by e.deptno
having avg(e.sal) >= all (select avg(e.sal) from emp group by e.deptno)
--用行号来解决
select *
from (select e.deptno, avg(e.sal) vsal
from emp e
group by e.deptno
order by vsal desc) t01
where rownum = 1
--2.求部门平均薪水的等级
select t01.deptno, t01.vsal, sg.grade
from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t01
join salgrade sg
on t01.vsal between sg.losal and sg.hisal
--3.求部门平均的薪水等级
select e.deptno,avg( sg.grade) vgrade
from emp e
join salgrade sg
on e.sal between sg.losal and sg.hisal
group by e.deptno