Oracle经典练习题

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

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

select deptno, max(sal) maxSal from emp group by deptno

第二步：获取结果

select ename, sal, e.deptno from emp e join ( select deptno, max(sal) maxSal from emp group bydeptno) t on e.deptno = t.deptno and e.sal = t.maxSal

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

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

select deptno, avg(sal) avgSal from emp group by deptno

第二步：获取结果

select ename, sal from emp e join (select deptno, avg(sal) avgSal from emp group by deptno) t on e.deptno = t.deptno and e.sal > t.avgSal

3、取得部门中（所有人的）平均的薪水等级，如下：

第一步：获取每个员工的薪水等级

select deptno, grade from emp e join salgrade g on e.sal between g.LOSAL and g.HISAL

第二步：将第一步的结果用部门编号进行分组，得到结果

select deptno, avg(grade) from (select deptno, grade from emp e join salgrade g on e.sal between g.LOSAL and g.HISAL) group by deptno

4、不准用组函数（Max），取得最高薪水（给出两种解决方案）

第一种(rownum)：

● 将员工薪水降序排列

select * from emp order by sal desc

● 取得查询结果的第一条数据

select sal from ( select * from emp order by sal desc) where rownum = 1

第二种(自关联)

● 将Emp表当成2张表来用，进行比较，得到最大值以外的值。

select distinct e.sal from emp e join emp t on e.sal < t.sal

● 获取最大薪水

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

5、取得平均薪水最高的部门的部门编号（至少给出两种解决方案）

第一种：

select * from (select deptno from emp group by deptno order by avg(sal) desc) where rownum = 1

第二种：

● 获取每个部门的平均薪水

select deptno, avg(sal) from emp group by deptno

● 取得查询结果的最高数据

select max(avgSal) from (select deptno, avg(sal) avgSal from emp group by deptno)

● 取得结果

select deptno from (select deptno, avg(sal) avgSal from emp group by deptno) s join (select max (avgSal) maxAvgSal from (select deptno, avg(sal) avgSal from emp group by deptno)) t on s.avgSal = t.maxAvgSal

第三种(聚合函数可以嵌套使用)

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

6、取得平均薪水最高的部门的部门名称

● 参考上一题的结果(取得部门编号)

select deptno from emp group by deptno having avg(sal) = (select max(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))

7、求平均薪水的等级最低的部门的部门名称

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

select deptno, avg(sal) avgSal from emp group by deptno

第二步：获取每个部门的平均 薪水等级

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

第三步：取得最低的等级

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

第四步：获取部门编号

Select deptno from (select deptno, avgSal, grade from salgrade g join ( select deptno, avg(sal) avgSal from emp group by deptno ) t on t.avgSal between g.losal and g.hisal) where grade = (select min(grade) from (select deptno, avgSal, grade from salgrade g join ( select deptno, avg(sal) avgSal from emp group by deptno ) t on t.avgSal between g.losal and g.hisal))

第五步：取得部门名称

Select dname from dept where deptno in (Select deptno from (select deptno, avgSal, grade from salgrade g join ( select deptno, avg(sal) avgSal from emp group by deptno ) t on t.avgSal between g.losal and g.hisal) where grade = (select min(grade) from (select deptno, avgSal, grade from salgrade g join ( select deptno, avg(sal) avgSal from emp group by deptno ) t on t.avgSal between g.losal and g.hisal)))

8、取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的经理人姓名

第一步：取得所有经理的员工编号

select distinct mgr from emp where mgr is not null

第二步：取得普通员工的最高薪水

Select max(sal) maxSal from emp where empno not in (select distinct mgr from emp where mgr is not null )

第三步：获取结果

select ename , sal from emp where empno in (select distinct mgr from emp where mgr is not null) and sal > (Select max(sal) maxSal from emp where empno not in (select distinct mgr from emp where mgr is not null ))

9、取得薪水最高的前五名员工

select *
from 
(
 select rownum r, t.* 
 from 
  (
    Select ename, sal from emp order by sal desc
  )  t  
  where rownum <=5 
)where r>0

10、取得薪水最高的第六到第十名员工

select *
from 
(
 select rownum r, t.* 
 from 
  (
    Select ename, sal from emp order by sal desc
  )  t  
  where rownum <=10 
)where r>5

11、取得最后入职的5名员工

Select * from ( select ename, hiredate from emp order by hiredate desc ) where rownum <= 5