Oracle笔记4

16：多表连接子查询：

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

            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)

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

        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

18：--雇员中有哪些人是经理人:

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

        select ename from emp where empno in (select distinct mgr from emp);//非常有效率！

19：--不用组函数求最高薪水

        select e1.sal from emp e1 join e2 on (e1.sal < e2.sal);

        select distinct e1.sal from emp e1 join e2 on (e1.sal < e2.sal);//去掉重复值！

        select distinct sal from emp where sal not in

        (select distinct e1.sal from emp e1 join e2 on (e1.sal < e2.sal));

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

        1、先求每个部门的平均薪水：

            select avg(sal),deptno from emp group by deptno;

        2、求平均薪水最大的那个值：

            select max(avg_sal) from

            (select avg(sal),deptno from emp group by deptno);

        3、根据值求编号：

            selectdeptno,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,deptno from emp group by deptno));

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

    select dname from dept where deptno =

    (

            selectdeptno,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,deptno from emp group by deptno));

     )