Oracle_sql_(7)

68.求部门中哪些人的薪水最高
select ename, sal
  from emp
  join (select max(sal) max_sal, deptno from emp group by deptno) t
    on (emp.sal = t.max_sal and emp.deptno = t.deptno); 

69.求部门平均薪水的 等级 （先取平均薪水再等级 整数）
select deptno, avg_sal, grade
  from (select deptno, avg(sal) avg_sal from emp group by deptno) t
  join salgrade s
    on (t.avg_sal between s.losal and s.hisal);

70.求部门平均的 薪水等级 (先等级后平均值 小数)
select deptno, avg(grade)
   from (select deptno, ename, grade
          from emp
          join salgrade s
            on emp.sal between s.losal and s.hisal) t
group by deptno;

71.雇员中有哪些人是经理人(不是领导empno没有在mgr出现)
select ename from emp where empno in (select distinct mgr from emp);

72.不准用组（聚合）函数，求薪水的最高值(面试题)
select distinct sal
  from emp
 where sal not in
       (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));

73.求平均薪水最高的部门的部门编号
select deptno, avg_sal
  from (select deptno, avg(sal) avg_sal from emp group by deptno)
 where avg_sal =
       (select max(avg_sal)
          from (select deptno, avg(sal) avg_sal from emp group by deptno));

74.求平均薪水的等级最低的部门的部门名
select dname, t1.deptno, grade, avg_sal
  from (select deptno, grade, avg_sal
          from (select deptno, avg(sal) avg_sal from emp group by deptno) t
          join salgrade s
            on (t.avg_sal between s.losal and s.hisal)) t1
  join dept
    on (t1.deptno = dept.deptno)
where t1.grade =
       (select min(grade)
          from (select deptno, grade, avg_sal
                  from (select deptno, avg(sal) avg_sal
                          from emp
                         group by deptno) t
                  join salgrade s
                    on (t.avg_sal between s.losal and s.hisal)));

75.求比普通员工的最高薪水还要高的经理人(领导，不是boss)的名称(思考题)
select ename from emp
where empno in (select distinct mgr from emp where mgr is not null)
   and sal >
(select max(sal) from emp
where empno not in (select distinct mgr from emp where mgr is not null));

76.求薪水最高的前5名雇员(重点掌握)
select ename, sal
  from (select ename, sal from emp order by sal desc)
where rownum <= 5;

77.求薪水最高的第6到第10名雇员(重点掌握)
select ename, sal
  from (select ename, sal, rownum r
          from (select ename, sal from emp order by sal desc))
 where r >= 6
   and r <= 10;