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

1 有哪些人的工资，位于所有人平均工资之上？

2 按照部门进行分组之后，每个部门工资最高的人？

3 求每个部门平均薪水的薪水等级是多少？

解法一

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

解法二

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 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 雇员中有哪些人是经理人
解法一

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

解法二

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

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

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

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

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

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

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

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

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

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

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

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 求平均薪水的等级最低的部门的部门名称

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 求部门经理人中平均薪水最低的部门名称

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

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

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