史上最难oracle数据库练习题(附答案)
来源:互联网 发布:淘宝让上传身份证清关 编辑:程序博客网 时间:2024/04/25 23:01
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)
)))
第八题:
查询平均工资最高的地区的最早入职的员工
- 史上最难oracle数据库练习题(附答案)
- Oracle数据库笔试题(附答案)
- Oracle数据库笔试题(附答案)
- oracle数据库经典练习题及答案
- Oracle练习题及答案(一)
- Oracle练习题及答案(二)
- ORACLE练习题答案
- oracle练习题答案
- 跨越SQL45个练习题(附答案)
- Shell 练习题 41-50,内附答案
- JS初级实战练习题----附答案
- Java练习题第一套《附答案》
- oracle练习题和答案 笔记
- Oracle基本练习题及答案
- oracle笔试题及答案(基础练习题)
- SQL数据库查询练习题及答案(四十五道题)
- Oracle数据库面试练习题
- Oracle数据库面试练习题
- 函数方程
- leetcode/2017-1-1
- PHP 基础五
- .net窗体或控件透明效果
- 魅族手机权限开启方法5
- 史上最难oracle数据库练习题(附答案)
- 水位下降
- 数据库设计 从入门至初级
- Openstack 架构简述
- Android Studio: You need to use a Theme.AppCompat theme (or descendant) with this activity.
- <<大数据分析在流量调度的应用>>孙子荀
- 前端知识推荐
- java 生成验证码
- 手机端通过Es文件浏览器访问windows服务器用File Zilla Sever软件 分享的文件