Oracle 多表连接子查询

1.求部门中哪些人薪水最高：
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);
2.求部门平均薪水的等级：
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);
3.求部门平均的薪水等级：
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;
4.求哪些人是经理人：
select ename from emp where empno in (select distinct mgr from emp);
5.不准用聚集函数，求薪水的最高值：
select distinct sal
from emp
where sal not in
(
    select distinct e1.sal
    from emp e1 join emp e2 on (e1.sal < e2.sal)
);
6.求平均薪水最高部门的部门编号：
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
    )
);
7.求平均薪水最高部门的部门名称：
select deptno,dname
from dept
where deptno = (
    select deptno
    from (
        select deptno, avg(sal) avg_sal
        from emp
        group by deptno
    ) where avg_sal = (
        select max(avg_sal)