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

2017-01-01 21:42:52来源:CSDN作者:qq_36870779人点击

## 第一题:

( 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)))

## 第四题

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

51号之后入职的

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

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

## 第六题:

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))

## 第七组:

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)

)))