Oracle第四天学习笔记(连接查询题目)

1[b][size=medium] 有哪些人的工资，位于所有人平均工资之上？[/size][/b]

2[b][size=medium] 按照部门进行分组之后，每个部门工资最高的人？[/size][/b]

3[b][size=medium] 求每个部门平均薪水的薪水等级是多少？[/size][/b]

[b]解法一[/b]

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

[b]解法二[/b]

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)

[b]解法三[/b]
思路 ： 先求出每个员工的薪水等级，再将薪水等级进行平均得出 部门平均薪水等级

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

4 [b][size=medium]雇员中有哪些人是经理人[/size][/b]
[b]解法一[/b]

select distinct m.ename from emp m
join emp e
on (m.empno=e.mgr);

[b]解法二[/b]

select ename from emp 
where empno = (select distinct mgr from emp);

5 [b][size=medium]不准用组函数，求薪水的最高值（面试题）[/size][/b]

select sal from emp 
where empno not in
(select distinct e.empno from emp e
join emp ee on (e.sal<ee.sal))

[b]思路 ：[/b] 先使用查找出的名单是除了工资最高的那个员工外的其它所有员工。

select distinct e.empno from emp e
join emp ee on (e.sal<ee.sal)

再从emp查，查找其中一条记录的empno not in 上头这张表中，那么就是工资最高的那条记录了

6 [b][size=medium]求平均薪水最高的部门的部门编号[/size][/b]

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

另一种比较简便的方法，使用的组函数嵌套，注意哦，组函数嵌套[b]最多只能套两层[/b]

  select deptno from emp
  group by deptno
  having avg(sal) =
    (select max(avg(sal)) from emp group by deptno)

7 [b][size=medium]求平均薪水最高的部门的部门名称[/size][/b]

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

使用组函数嵌套

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

8 [b][size=medium]求平均薪水的等级最低的部门的部门名称[/size][/b]

select d.deptno,d.dname,s.grade from dept d
join 
   (select deptno ,avg(sal) avg_sal from emp 
   group by deptno
   having avg(sal) =
     (
        select min(avg_sal) min_sal from
        (select avg(sal) avg_sal from emp group by deptno) 
     )
   ) t
on (t.deptno=d.deptno)
join 
   salgrade s 
on (t.avg_sal between s.losal and s.hisal)

使用组函数嵌套后

select d.deptno,d.dname,s.grade from dept d  
join   
   (select deptno ,avg(sal) avg_sal from emp   
   group by deptno  
   having avg(sal) =  
     (select min(avg(sal)) from emp group by deptno)  
   ) t  
on (t.deptno=d.deptno)  
join   
   salgrade s   
on (t.avg_sal between s.losal and s.hisal)

9 [b][size=medium]求部门经理人中平均薪水最低的部门名称[/size][/b]

10 [b][size=medium]求比普通员工的最高薪水还要高的经理人名称[/size][/b]

11 [b][size=medium]求薪水最高的前5名雇员[/size][/b]

12 [b][size=medium]求薪水最高的第6到第10名雇员[/size][/b]