oracle 面试练习(六)

1 不许用组函数  求薪水的最高值:

   可建表 select  distinct (去重)  e1.sal from emp e1 join emp e2  on(e1.sal < e2.sal) ; 

                   此表中包含e1 中除最高值外的所有值.   从e1中选择不在此表中的值即可 .

 

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

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

     先求出每个部门的平均薪水, 部门编号 : select avg(sal)avg_sal ,deptno from emp group by deptno 

   求出此表中部门编号的最大值 : select max(avg_sal) from
                                                                          (select avg(sal)avg_sal ,deptno 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
                                           (select avg(sal)avg_sal ,deptno from emp group by deptno) )
                                                     /

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

        得到以上部门编号后 , 直接在dept 表中查询名称:

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

4求平均薪水等级最高的部门的部门名称

      步步为营:

                   首先求出每个部门的平均薪水等级:

                        select avg_sal,deptno,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 max(grade) from
                                 (
                                         select avg_sal,deptno,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 deptno from
                         (

                                          select avg_sal,deptno,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)

                          )
                       where grade =
                        (
                            select max(grade) from
                                 (
                                          select avg_sal,deptno,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)
                                  )
                          )

                   最后从dept表中查找这些部门的名称:

                      select dname from dept where deptno in

                 (

                                  select deptno from
                         (

                                          select avg_sal,deptno,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)

                          )
                       where grade =
                        (
                            select max(grade) from
                                 (
                                          select avg_sal,deptno,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_sal,deptno,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)

  这个表 . 为了简洁起见,可以将这个表建立然后引用:

  首先获得建表权限:

    conn sys/scott as sysdba;

  创建这个表,起名为 v$_dept_avg_sal_info :

     SQL>   create view v$_dept_avg_sal_info as
     2    select deptno,grade from
    3     (select deptno,avg(sal)avg_sal from emp group by deptno) t
    4     join salgrade s on (t.avg_sal between s.losal and s.hisal)
    5   /

   然后上述题目的答案可写为:

 1   select dname from
 2    v$_dept_avg_sal_info
 3    join dept on(v$_dept_avg_sal_info.deptno=dept.deptno)
 4    where grade =
 5   (
 6  select min(grade) from
 7    v$_dept_avg_sal_info
 8*  )
 SQL> /

 

组函数的嵌套:

  求最高的平均薪水时, 其实不必先求出平均薪水再求其最高值, 而是存在以下简便 写法:

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