八.练习总结
练习一：找到员工表中工资最高的前三名，参考结果：
练习一参考结果:员工表中工资最高的前三名
-->答案：
SQL>select rownum, empno, ename, sal
from (select * from emp order by sal desc)
where rownum<=3;
=========================引申出分页的问题========================
--取8条数据，分成2页，现在取出第2页
SQL>select *
from (select rownum r, e1.*
from (select * from emp order by sal) e1
where rownum<=8)
where r>=5
练习二：找到员工表中薪水大于本部门平均薪水的员工。参考结果：
练习二参考结果:员工表中薪水大于本部门平均薪水的员工
-->答案：
SQL> select empno,ename,sal,avgsal
from emp e, (select deptno,avg(sal) avgsal
from emp
group by deptno) d
where e.deptno=d.deptno and e.sal> d.avgsal;
=========================引申出相关子查询========================
--相关子查询：将主查询中的某个值作为参数传递给子查询
--一般子查询会先查主查询再查子查询，但相关子查询例外，刚好相反
SQL> select empno, ename, sal, (select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal>(select avg(sal) from emp where deptno=e.deptno);
练习三：统计每年入职的员工个数，参考结果：
练习三参考结果:统计每年入职的员工个数
-->答案：
SQL> select count(*) total,
sum(decode(to_char(hiredate, 'yyyy'), '1980', 1)) "1980",
sum(decode(to_char(hiredate, 'yyyy'), '1981', 1)) "1981",
sum(decode(to_char(hiredate, 'yyyy'), '1982', 1)) "1982",
sum(decode(to_char(hiredate, 'yyyy'), '1987', 1)) "1987"
from emp;
