--求部门中哪些人的薪水最高

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)

--求部门平均薪水的等级

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)

--求部门平均的薪水等级

select avg(grade), deptno from
   (select deptno, ename, grade from emp join salgrade s 
      on( emp.sal between s.losal and s.hisal)) t
group by deptno

--雇员中哪些人是经理人

select ename from emp where empno in (select distinct mgr from emp)

--不准用组函数，求薪水的最高值（面试题）

select sal from emp where sal not in
   (select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal))

--求平均薪水最高的部门编号

select deptno, avg_sal from
   (select avg(sal) avg_sal , deptno from emp group by deptno)
where avg_sal = 
   (select max(avg_sal)from (select avg(sal) avg_sal from emp group by deptno))

---------------------------------------------------------------------------------

select deptno, avg_sal from
   (select avg(sal) avg_sal , deptno from emp group by deptno)
where avg_sal = 
   (select max(avg(sal))from emp group by deptno)--子函数嵌套（最多两层）

--求平均薪水最高的部门名称

select dname from dept where deptno = 
(select deptno from
   (select avg(sal) avg_sal , deptno from emp group by deptno)
where avg_sal = 
   (
      select max(avg_sal)from (select avg(sal) avg_sal from emp group by deptno))
   )

--求平均薪水的等级最低的部门的部门名称

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)
        )
    )

------------------------------------------------------------------------------

conn sys/123 as sysdba
grant create table, create view to scott;


 create view v$_dept_avg_sal_info as
   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)


select dname, t1.deptno, grade, avg_sal from 
   v$_dept_avg_sal_info t1
join dept on (t1.deptno = dept.deptno)
  where t1.grade = 
   ( select min(grade) from v$_dept_avg_sal_info )


--求部门经理人中平均薪水最低的部门名称(思考题)

select dname from dept where deptno =
 (
  select deptno from 
        (select deptno, avg(sal) avg_sal from emp 
      where empno in (select mgr from emp) group by deptno) 
  where avg_sal =(
      select min(avg_sal) from (
              select deptno, avg(sal) avg_sal from emp 
        where empno in (select mgr from emp) group by deptno
        ) 
    )
 )

--求比普通员工的最高薪水还要高的经理人名称

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)
    )

--求薪水最高的前5名雇员

select ename, sal, rownum r from 
   (select ename, sal from emp order by sal desc)
where rownum <=5

--求薪水最高的第6到第10名雇员

select ename, sal, rownum from
 (select ename,sal,rownum r from
    (select ename,sal from emp order by sal desc)
  )
where r >=6 and r <=10