oracle一道简单面试题和一道深入面试题

练习使用的是Oracle提供的scott 用户中的表

–找到员工表中薪水大于本部门平均薪水的员工

oracle提供的部门表,但是部门里没有平均工资,所以我们不链接它提供的表,自己查一张表出来
找出所有部门,及平均工资
select deptno, avg(sal) from emp group by deptno;

员工根据部门链接刚刚查询的表,比较工资,大于的话就打印
select *
  from emp e, (select deptno, avg(sal) avgSal from emp group by deptno) d
 where e.deptno = d.deptno   --判断部门
   and e.sal > d.avgSal;    --判断工资 

–统计每年入职员工的个数

--统计每年入职员工的个数
1.查询年 
select to_char(hiredate,'yyyy') year from emp;

2.根据年分组,然后统计数量
select to_char(hiredate,'yyyy')  year ,count(*) 人数 from emp group by to_char(hiredate,'yyyy');

实际上已经完成了 , 但是要求我们达到以下的效果

我们先把1987竖起来,

select case year
         when '1987' then
          人数
       end
  from (select to_char(hiredate, 'yyyy') year, count(*) 人数
          from emp
         group by to_char(hiredate, 'yyyy'));

结果如下

我们把多行变成一行,使用聚合函数,聚合函数会忽略空值
所以随便使用一个聚合函数就行,,

select avg(case year
         when '1987' then
          人数
       end)  year
  from (select to_char(hiredate, 'yyyy') year, count(*) 人数
          from emp
         group by to_char(hiredate, 'yyyy'));

运行结果如下

题目还需要我们扩充一列总人数

select sum(人数) total,
        avg(case year
         when '1987' then
          人数
       end)  year
  from (select to_char(hiredate, 'yyyy') year, count(*) 人数
          from emp
         group by to_char(hiredate, 'yyyy'));

运行结果如下

最后终极版的代码如下 ,

select sum(人数) total,
        avg(case year  when '1980' then 人数 end) "1980",
        avg(case year  when '1981' then 人数 end) "1981",
        avg(case year  when '1982' then 人数 end) "1982" , 
        avg(case year  when '1987' then 人数 end) "1987"
  from (select to_char(hiredate, 'yyyy') year, count(*) 人数
          from emp
         group by to_char(hiredate, 'yyyy'));