Oracle学习笔记8

一．常见问题
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.lowsal and s.highsal);
3）求部门平均的薪水等级
Select deptno, avg(grade) from
(select deptno, ename, grade from emp join salgrade s on (emp.sal between s. lowsal and s.highsal)) 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 from (select avg(sal) avgsal, deptno from emp group by deptno)
Where avgsal = (select max(avgsal) from (select avg(sal) avgsal, deptno from emp group by deptno));
7）求平均薪水最高的部门名称
Select dname from dept where deptno =
         (
         Select deptno from (select avg(sal) avgsal, deptno from emp group by deptno)
Where avgsal = (select max(avgsal) from
(select avg(sal) avgsal, deptno from emp group by deptno)
);
);
8）求平均薪水的等级最低的部门的部门名称
select deptname
from (
  select deptno, grade
  from (
select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on
                       (t.avg_sal between s.lowsal and s.highsal)
     ) t1 join dept d on (t1.deptno = d.deptno);
where t1.grade =
         (
       select min(grade)
         from (
&