数据库：Oracle中经典的关于平均薪水的笔试题

select sum(e.comm), count(e.comm), avg(e.comm) from emp e;
select sum(e.comm), count(nvl(comm,0)), avg(e.comm) from emp e;
select sum(nvl(comm,0)), count(e.comm), avg(e.comm) from emp e;

 

 

--求平均薪水最高的部门的信息
select deptno
  from emp
 group by deptno
having avg(sal) = (select max(avg(sal)) from emp group by deptno);
--求部门平均薪水的等级
select deptno, grade, avg_sal
  from (select deptno, avg(sal) avg_sal from emp group by deptno) t
  join salgrade
    on t.avg_sal >= salgrade.losal
   and t.avg_sal <= salgrade.hisal;
--求部门平均的薪水等级
select deptno, avg(grade)
  from (select ename, deptno, grade  from emp
          join salgrade
            on emp.sal between salgrade.losal and salgrade.hisal)
 group by deptno
--求平均薪水的等级最低的部门名称
select deptno, grade
  from (select deptno, grade, avg_sal
          from (select deptno, avg(sal) avg_sal 

                 from emp
                 group by deptno) t
          join salgrade
            on t.avg_sal >= salgrade.losal
           and t.avg_sal <= salgrade.hisal)
 where avg_sal =
       (select min(avg_sal)
          from (select deptno, grade, avg_sal
                  from (select deptno, avg(sal) avg_sal
                          from emp
                         group by deptno) t
                  join salgrade
                    on t.avg_sal >= salgrade.losal
                   and t.avg_sal <= salgrade.hisal))
--求部门经理人中平均薪水最低的部门名称
--求比普通员工的最高薪水还要高的经理人名称
  select ename, sal
          from (select distinct e2.ename, e2.sal
            from emp e1
            join emp e2
              on e1.mgr = e2.empno)
         where sal > (select max(sal)
                        from emp
                       where ename not in
                             (select distinct e2.ename
                                from emp e1
                                join emp e2
                                  on e1.mgr = e2.empno));
                                 
--求薪水最高的前5名雇员                                 
select ename, sal from (select * from emp order by sal desc) where rownum < 6
--求薪水最高的前6到10名雇员       
select ename,
       sal from（
        select ename, sal, rownum rn
          from (select * from emp order by sal desc) ）
         where rn > 5
           and rn < 11
          
--找出部门编号为20的所有员工中收入最高的职员
select ename
  from emp
 where sal >= (select max(sal) from emp where deptno = 20)
   and deptno = 20;