Oracle教程
Oracle练习题

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 

全部教程