Oracle 练习题集

本练习题集使用的是用户是Oracle12c的c##scott，分别有表BONUS,DEPT,EMP,SALGRADE四张表。

一、查询公司各个工资等级雇员的数量、平均工资。

（涉及知识点：表的连接，分组统计查询(group by)，数值函数(round),通用函数（decode））

select decode(s.grade,1,'E等级',2,'D等级',3,'C等级',4,'B等级',5,'A等级') 工资等级,count(s.grade) 人数,round(avg(e.sal),2) 平均工资
from emp e,salgrade s
where e.sal between s.losal and s.hisal
group by s.grade;

运行结果如图：

二、查询每个部门的名称、部门人数、部门平均工资、平均服务年限。

（涉及知识点：表的连接(右外连接)，分组统计查询(group by)，数值函数(round)，日期函数(months_between)）

(1)第一步，求出部门名称、部门人数、部门平均工资

select d.dname 部门名称,count(e.empno) 部门人数,round(avg(e.sal),2) 部门平均工资
from emp e,dept d
where e.deptno=d.deptno
group by d.dname;

(2)第二步，在第一步的基础上，求出平均服务年限

select d.dname 部门名称,count(e.empno) 部门人数,round(avg(e.sal),2) 部门平均工资,round(avg(months_between(sysdate,hiredate)/12),2) 平均服务年限
from emp e,dept d
where e.deptno=d.deptno
group by d.dname;

(3)第三部，因为部门表有四个部门，然而第二步只求出了三个部门，所以需要使用右外连接，把没有雇员的部门给显示出来。

select d.dname 部门名称,count(e.empno) 部门人数,round(avg(e.sal),2) 部门平均工资,round(avg(months_between(sysdate,hiredate)/12),2) 平均服务年限
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.dname;

运行该结果如图：

三、查询出在1981年雇佣的全部雇员的编号、姓名、雇佣日期（年月日）、工作、领导姓名、雇员月工资、雇员年工资（基本工资+奖金）、雇员工资等级、部门编号、部门位置，并且要求这些雇员的月工资在1500~3500之间，将最后的结果按照年工资的降序排列，如果年工资相等，则按照工作时间进行排序。

1.先求出每个雇员对应的领导编号、领导姓名（注意KING是没有领导的，所以需要使用左外连接，这样就能显示出KING）

select e.empno 雇员编号,e.ename 雇员姓名,m.empno 领导编号,m.ename 领导姓名
from emp e,emp m
where e.mgr=m.empno(+);

2.求出在1982年雇佣的全部雇员编号、姓名、雇佣日期（按年-月-日）、工作、领导姓名、雇员月工资、雇员年工资。

select e.empno 雇员编号,e.ename 雇员姓名,TO_CHAR(e.hiredate,'yyyy-mm-dd') 雇佣日期,e.job 工作,m.ename 领导姓名,e.sal 雇员月工资,(e.sal+nvl(e.comm,0))*12 雇员年收入
from emp e,emp m
where e.mgr=m.empno（+） and to_char(e.hiredate,'yyyy')='1981';

3.在2基础上，求出部门名称、部门位置

select e.empno 雇员编号,e.ename 雇员姓名,TO_CHAR(e.hiredate,'yyyy-mm-dd') 雇佣日期,e.job 工作,m.ename 领导姓名
,e.sal 雇员月工资,(e.sal+nvl(e.comm,0))*12 雇员年收入,d.dname 部门名称,d.loc 部门位置
from emp e,emp m,dept d
where e.mgr=m.empno（+） 
  and to_char(e.hiredate,'yyyy')='1981'
  and e.deptno=d.deptno;

4.在3基础上，求出雇员工资等级，并且这些雇员的月工资基本在1500~3500之间

select e.empno 雇员编号,e.ename 雇员姓名,TO_CHAR(e.hiredate,'yyyy-mm-dd') 雇佣日期,e.job 工作,m.ename 领导姓名
,e.sal 雇员月工资,(e.sal+nvl(e.comm,0))*12 雇员年收入,decode(s.grade,1,'E等级',2,'D等级',3,'C等级',4,'B等级',5,'A等级') 工资等级,d.dname 部门名称,d.loc 部门位置
from emp e,emp m,dept d,salgrade s
where e.mgr=m.empno（+） 
  and to_char(e.hiredate,'yyyy')='1981'
  and e.deptno=d.deptno
  and e.sal between s.losal and s.hisal
  and e.sal between 1500 and 3500;

5.在4基础上过添加排序

select e.empno 雇员编号,e.ename 雇员姓名,TO_CHAR(e.hiredate,'yyyy-mm-dd') 雇佣日期,e.job 工作,m.ename 领导姓名
,e.sal 雇员月工资,(e.sal+nvl(e.comm,0))*12 雇员年收入,decode(s.grade,1,'E等级',2,'D等级',3,'C等级',4,'B等级',5,'A等级') 工资等级,d.dname 部门名称,d.loc 部门位置
from emp e,emp m,dept d,salgrade s
where e.mgr=m.empno（+） 
  and to_char(e.hiredate,'yyyy')='1981'
  and e.deptno=d.deptno
  and e.sal between s.losal and s.hisal
  and e.sal between 1500 and 3500
order by 雇员年收入,e.job;

输出结果如图：

四、统计出领取雇佣金与不领取雇佣金的雇员的平均工资、平均够用年限、雇佣人数。

第一步，先统计出领取佣金的雇员的平均工资、雇佣年限、雇员人数

select '领取雇佣金', count(*) 雇员人数, round(avg(e.sal),2) 平均工资,avg(round(months_between(sysdate,e.hiredate)/12,2)) 雇佣平均年限
from emp e
where e.comm is not null;

第二步，统计出不领取佣金的雇员的平均工资、雇佣年限、雇员人数

select '不领取雇佣金', count(*) 雇员人数, round(avg(e.sal),2) 平均工资,avg(round(months_between(sysdate,e.hiredate)/12,2)) 雇佣平均年限
from emp e
where e.comm is null;

第三步，只需将上述两张表使用UNION合并起来即可。

select '领取雇佣金', count(*) 雇员人数, round(avg(e.sal),2) 平均工资,avg(round(months_between(sysdate,e.hiredate)/12,2)) 雇佣平均年限
from emp e
where e.comm is not null
union
select '不领取雇佣金', count(*) 雇员人数, round(avg(e.sal),2) 平均工资,avg(round(months_between(sysdate,e.hiredate)/12,2)) 雇佣平均年限
from emp e
where e.comm is null;

五、查询出每个部门的详细信息（部门标号、名称、位置、部门人数、平均工资、总工资、最高工资、最低工资）

第一步，求出部门编号、名称、位置、部门人数(注意要使用右外连接)

select d.deptno 部门编号,d.dname 部门名称,d.loc 部门位置，count(e.empno) 部门人数
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc;

第二步，在一基础上，求出每个部门的平均工资、总工资、最高工资、最低工资

select d.deptno 部门编号,d.dname 部门名称,d.loc 部门位置，
count(e.empno) 部门人数,round(avg(e.sal),2) 平均工资,sum(e.sal) 总工资,max(e.sal) 最高工资,min(e.sal) 最低工资
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc;

第三步，在二的基础上，用nvl函数消除null值。

select d.deptno 部门编号,d.dname 部门名称,d.loc 部门位置，
count(e.empno) 部门人数,nvl(round(avg(e.sal),2),0) 平均工资,nvl(sum(e.sal),0) 总工资,nvl(max(e.sal),0) 最高工资,nvl(min(e.sal),0) 最低工资
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc;