Oracle练习

一.--找到员工表中工资最高的前三名(降序排序)
select * from emp order by sal desc;
/*
       rownum : 伪列, 系统自动生成的一列, 用来表示行号
       
       rownum是Oracle中特有的用来表示行号的, 默认值/起始值是 1 ,在每查询出结果之后,再添加1
       
       rownum最好不能做大于号判断,可以做小于号判断
       
       SQL执行顺序
       from .. where ..group by..having .. select..rownum..order by
*/
Select rownum,e1.* from emp e1;

--查询rownum大于2的所有记录 ,
select rownum,e1.* from emp e1 where rownum > 2;  --没有任何记录

--查询rownum大于等于1的所有记录 
select rownum,e1.* from emp e1 where rownum >=1;
 
--查询rownum < 6 的所有记录
select rownum,e1.* from emp e1 where rownum < 6;

--rownum 排序
Select rownum,e1.* from emp e1 order by sal;

--找到员工表中工资最高的前三名
select e1.* from emp e1 order by sal desc;
--将上面的结果当作一张表处理,再查询
select rownum, t1.* from (select e1.* from emp e1 order by sal desc) t1;

--只要显示前三条记录
select rownum, t1.* from (select e1.* from emp e1 order by sal desc) t1 where rownum < 4;


二.--找到员工表中薪水大于本部门平均薪水的员工
--1.分组统计部门平均薪水
select deptno,avg(sal) avgsal from emp group by deptno;
--2.员工工资 > 本部门平均工资
select * from emp e1,(select deptno,avg(sal) avgsal from emp group by deptno) t1 
where e1.deptno = t1.deptno and e1.sal > t1.avgsal;
/*
      关联子查询 , 非关联子查询
*/
select * from emp e where sal > (select avg(sal) from emp e2 group by deptno having e.deptno=e2.deptno);



/*
   三.统计每年入职的员工个数
*/
select hiredate from emp;
--只显示年
select to_char(hiredate,'yyyy') from emp;
--分组统计
select  to_char(hiredate,'yyyy') yy,count(1) cc from emp group by  to_char(hiredate,'yyyy');

select yy
from 
(select  to_char(hiredate,'yyyy') yy,count(1) cc from emp group by  to_char(hiredate,'yyyy')) tt;

select case yy when '1987' then cc end
from 
(select  to_char(hiredate,'yyyy') yy,count(1) cc from emp group by  to_char(hiredate,'yyyy')) tt;

select case yy when '1987' then cc end "1987"
from 
(select  to_char(hiredate,'yyyy') yy,count(1) cc from emp group by  to_char(hiredate,'yyyy')) tt;

--去除行记录中的空值
select sum(case yy when '1987' then cc end) "1987"
from 
(select  to_char(hiredate,'yyyy') yy,count(1) cc from emp group by  to_char(hiredate,'yyyy')) tt;

--统计员工的总数
select sum(cc) "TOTAL"
from 
(select  to_char(hiredate,'yyyy') yy,count(1) cc from emp group by  to_char(hiredate,'yyyy')) tt;

--将1987 和TOTAL 合并在一起
select
      sum(cc) "TOTAL",
      sum(case yy when '1987' then cc end) "1987"
from
      (select  to_char(hiredate,'yyyy') yy,count(1) cc from emp group by  to_char(hiredate,'yyyy')) tt;

--显示所有年份的结果
select
      sum(cc) "TOTAL",
      sum(case yy when '1980' then cc end) "1980",
      sum(case yy when '1981' then cc end) "1981",
      sum(case yy when '1982' then cc end) "1982",
      sum(case yy when '1987' then cc end) "1987"
from
      (select  to_char(hiredate,'yyyy') yy,count(1) cc from emp group by  to_char(hiredate,'yyyy')) tt;