史上最难oracle数据库练习题（附答案）

oracle数据库练习题，所有练习数据都来自于安装oracle数据库后，自带的那几张表，EMP,DEPT等。

部分题目答案已给出，若有异议可以私聊一起探讨。

第一题:

统计薪资大于  薪资最高的员工所在部门的平均工资和薪资最低的员工所在部门的平均工资的平均工资的员工信息。

最终答案： select * from emp where sal >

( select avg (sal) from emp where deptno=(select deptno from emp where sal=(select min(sal) from emp)))

and sal>(select avg (sal) from emp where deptno=(select deptno from emp where sal=(select max(sal) from emp)))

第二题:

统计出 薪资和平均薪资最低的部门中薪资最高的员工的薪资 相差500之内的全部员工的信息

选做:把"最低"换成"处于正中间"

第三题:

查询部门名称不是research,职位是manager,且薪资大于平均薪资的员工(包含ename hiredait loc三个字段)

第四题

找出5月1号之后入职的并且薪资高于NEW YORK地区的平均薪资的员工的具体信息(部门信息代替部门编号)按薪资降序列出  ,第四组的题目

最终答案：select * from emp where (to_char(hiredate,'mmdd') in (select * from (select to_char(hiredate,'mmdd') dd from emp) where dd>501))

and (sal>(select avg(sal) from emp where deptno = (select deptno from dept where loc='NEW YORK'))) order by sal;

5月1号之后入职的

select * from (select to_char(hiredate,'mmdd') dd from emp) where dd>501

薪资高于BOSTON地区的平均薪资

select avg(sal) from emp where deptno = (select deptno from dept where loc='NEW YORK')

第五题:

查询各个部门   { 工资小于 【纽约员工平均工资】}且{年资大于30年}   的员工人数（输出字段至少包含   【 loc  |  dname |  人数 】这三个字段）

第六题:

查询出入职时间最早的员工所在部门的最高薪资的员工名字,工作职位.工作地点

最终答案：select ename,job,(select loc from dept where deptno=(select deptno from emp where to_char(hiredate,'yyyymmdd') = (select min(to_char(hiredate,'yyyymmdd')) from emp))) from emp e

where e.sal=(select max(sal) from emp where deptno=(select deptno from emp where to_char(hiredate,'yyyymmdd') = (select min(to_char(hiredate,'yyyymmdd')) from emp)));

select * from emp

入职时间最早的员工所在部门的最高薪资

select deptno from emp where to_char(hiredate,'yyyymmdd') = (select min(to_char(hiredate,'yyyymmdd')) from emp)

select max(sal) from emp where deptno=(select deptno from emp where to_char(hiredate,'yyyymmdd') = (select min(to_char(hiredate,'yyyymmdd')) from emp))

第七题:

统计出  薪资位于平均工资最高的部门与 平均薪资最低的部门之间 的入职日期最晚的员工的工作城市的所有员工的平均工资

最终答案：select avg(sal) from emp

where deptno in (select deptno from dept where loc=(select loc from dept where deptno=(select deptno from emp where to_char(hiredate,'yyyymmdd') = (select min(to_char(hiredate,'yyyymmdd')) from emp where sal between (select min(avg(sal)) from emp group by deptno) and (select max(avg(sal)) from emp group by deptno)

))))

1.平均工资最高的部门 平均薪资最低的部门

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

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

2.入职日期最晚的员工

select min(to_char(hiredate,'yyyymmdd')) from emp where sal between (select min(avg(sal)) from emp group by deptno) and (select max(avg(sal)) from emp group by deptno)

3.找出所在城市

select loc from dept where deptno=(select deptno from emp where to_char(hiredate,'yyyymmdd') = (select min(to_char(hiredate,'yyyymmdd')) from emp where sal between (select min(avg(sal)) from emp group by deptno) and (select max(avg(sal)) from emp group by deptno)

))

select deptno from dept where loc=(select loc from dept where deptno=(select deptno from emp where to_char(hiredate,'yyyymmdd') = (select min(to_char(hiredate,'yyyymmdd')) from emp where sal between (select min(avg(sal)) from emp group by deptno) and (select max(avg(sal)) from emp group by deptno)

)))

第八题:

查询平均工资最高的地区的最早入职的员工