Oracle数据库练习题

1.取得每个部门最高薪水的人员名称

第一步：取得每个部门的最高薪水

select max(sal) from emp group by deptno;

第二步：根据第一步的结果和员工表进行关联，获取人员名称

select a.empno,a.ename,a.sal,a.deptno from emp a join (select deptno, max(sal) max_sal from emp group by deptno ) b on a.deptno= b.deptno and a.sal = b.max_sal;

2.哪些人的薪水在部门的平均薪水之上

      第一步：获取每个部门的平均薪水

      Select deptno, avg(sal) avgSal from emp group bydeptno

      第二步：根据第一步的结果和员工表进行关联。获取人员名称

3.取得部门中（所有人的）平均的薪水等级

第一步：获得所有人的薪水等级

select e.empno,e.ename,e.deptno,g.grade from emp e join salgrade g on e.sal  between g.losal and g.hisal 

第二步：将第一步的结果用部门进行分组。然后获取等级的平均值

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

4.不准用组函数（Max），取得最高薪水

第一种方法：

第一步：将员工薪水降序排列

select empno,ename,sal from emp order by sal desc

第二步：取得第一条数据

select a.empno,a.ename,a.sal from (select empno,ename,sal from emp order by sal desc) a where rownum < = 1;

第二种方法：

第一步：将emp表当做2张表来处理。使用的笛卡尔乘积的方法进行比较。得到最大值以外的值。
select distinct a.sal from emp a join emp b on a.sal < b.sal 
第二步：获得最大值
select a.sal,a.empno,a.ename from emp a where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal );
第三种方法：
第一步：将emp表当做2张表来处理。使用的笛卡尔乘积的方法进行比较。从大到小排序
select distinct a.sal from  emp a join emp b on a.sal > b.sal 
第二步：取得第一条数据
select  sal from (select distinct a.sal from  emp a join emp b on a.sal > b.sal ) where rownum <= 1

5,取得平均薪水最高的部门的部门编号。
第一种方法：
第一步：取得各部门的平均薪水。按desc排序。取得最大值
select e.deptno, avg(e.sal) avg_sal from emp e group by e.deptno  order by avg_sal desc ；
第二步：取得第一条数据
select a.deptno, a.avg_sal from (select e.deptno, avg(e.sal) avg_sal from emp e group by e.deptno  order by avg_sal desc ) a where rownum < =1;
第二种方法：
第一步：取得各部门的平均薪水
select avg(sal) avg_sal,e.deptno from emp e group by deptno;
第二步：使用聚合函数获得平均薪水最大值
select max(avg_sal) from (select  avg(sal) avg_sal,e.deptno from emp e group by deptno)
第三步：将第一步和第二步获取的结果进行关联
select m.deptno from 
(select  avg(sal) avg_sal,e.deptno from emp e group by deptno) m 
right join 
(select max(avg_sal) max_sal from (select  avg(sal) avg_sal,e.deptno from emp e group by deptno)) n
on m.avg_sal = n.max_sal
第三种方法：
第一步：获得各部门的最大值
select max(avg(sal)) from emp m group by deptno;
第二步：使用having过滤。获得最大值
select a.deptno,avg(sal) avg_sal from emp a group by deptno   having avg(sal)  = (select max(avg(sal)) from emp m group by deptno);
6.取得平均薪水最高的部门的部门名称

第一种方法：

第一步：取得各部门的平均薪水

select e.deptno ,avg(sal) avg_sal from emp e group by deptno order by avg_sal desc;

第二步：取得各部门的平均薪水最大值

select m.deptno, avg_sal from (select e.deptno ,avg(sal) avg_sal from em