Oracle试题

08.显示与30号部门'MARTIN'员工工资相同的员工的姓名和工资
答：考察知识点：子查询
select ename, sal from emp
where sal = (select sal from emp where deptno=30 and ename='MARTIN');
分析：同第5题
09.查询所有工资高于平均工资(平均工资包括所有员工)的销售人员('SALESMAN')
select e.*, (select count(*) from emp where sal > e.sal)+1 rank from emp e order by rank;
分析：
此题的要点在于理解select count(*) from emp where sal > e.sal+1的含义，e.sal代表当前员工，
该子查询的含义就是求比当前员工工资高的人数个数：比此员工工资高的人数个数如果为
0，表示此人排名第一，比此员工工资高的人数个数如果为1，表示此人排名第二…所以该
子查询结果就表示排名。
17.求入职日期相同的(年月日相同)的员工
答：考察知识点：子查询
select * from emp e where (select count(*) from emp where e.hiredate=hiredate)>1;
between to_date('1981-5-1','yyyy-mm-dd') and to_date('1981-12-31','yyyy-mm-dd');
05.查询所有名字长度为4的员工的员工编号,姓名
答：
select * from emp where length(ename) = 4;
06.显示10号部门的所有经理('MANAGER')和20号部门的所有职员('CLERK')的详细信息
并取别名为rn，这时rn已经作为临时表中一个真实的列存在了，因此可以使用>或>=比较
答：考察知识点：子查询，rownum
select * from (select * from emp order by sal desc) where rownum <= 3;
分析：见21题要点一
16.按工资进行排名,排名从1开始,工资相同排名相同(如果两人并列第1则没有第2名,从第
三名继续排)
答：考察知识点：子查询
答：
select * from emp where to_char(hiredate, 'mm') = to_char(sysdate , 'mm');
11.把hiredate列看做是员工的生日,求下月过生日的员工(考察知识点:单行函数)
答：
select * from emp where to_char(hiredate, 'mm') = to_char(add_months(sysdate,1) , 'mm');
答：
select * from emp where deptno = 10 and job = 'MANAGER' or deptno = 20 and job ='CLERK';
07.显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息
答：考察知识点模糊查询
select * from emp where ename not like '%L%' or ename like '%SM%';
where rownum > 5 and rownum <=10;
因此需要将
select e.*,rownum rn from
(select * from emp order by sal desc) e
where rownum <=10
查询结果看做一个临时表，这个临时表除了有表e中的所有列之外，多添加一个rownum列
答：考察知识点：子查询，表连接
select * from (select count(*) c, deptno from emp group by deptno) e
inner join dept d on e.deptno = d.deptno;
分析：主要思路是要将子查询结果看做一个临时表，此临时表又可以与其他表做表连接
14.查询工资相同的员工的工资和姓名
答：考察知识点：子查询
select * from emp e where (select count(*) from emp where sal = e.sal group by sal) > 1;
分析：此题目类似于17题，见17题分析。
15.查询工资最高的3名员工信息
order by trunc(hiredate,'month');
Part II(
01.查询各个部门的平均工资
答：考察知识点：分组
select deptno,avg(sal) from emp group by deptno;
02.显示各种职位的最低工资
答：考察知识点：分组
select job,min(sal) from emp group by job;
12.求1982年入职的员工(考察知识点:单行函数)
答：
select * from emp where to_char(hiredate,'yyyy') = '1982';
13.求1981年下半年入职的员工(考察知识点:单行函数)
答：
select * from emp where hiredate
between to_date('1981-7-1','yyyy-mm-dd') and to_date('1982-1-1','yyyy-mm-dd') - 1;
19.查询每个部门,每种职位的最高工资
答：考察知识点：分组
select deptno, job, max(sal) from emp group by deptno, job order by deptno, job;
分析：要点是理解多列分组：部门与职位都相同的分为一组，求每组的最高工资，其实就是
表示每个部门,每种职位的最高工资
13.查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位
答：考察知识点：子查询
select job, count(*) from emp where sal > (select avg(sal) from emp) group by job;
分析：查询结果是一行一列，可以将查询结果看做一个值，进行条件比较
08.显示各个部门经理('MANAGER')的工资
答：
select sal from emp where job = 'MANAGER';
09.显示佣金(COMM)收入比工资()高的员工的详细信息
答：
select * from emp where comm > sal;
10.把hiredate列看做是员工的生日,求本月过生日的员工(考察知识点:单行函数)
14.求1981年各个月入职的的员工个数(考察知识点:组函数)
答：
select count(*), to_char(trunc(hiredate,'month'),'yyyy-mm')
from emp where to_char(hiredate,'yyyy')='1981'
group by trunc(hiredate,'month')
答：考察知识点：子查询
select * from emp where job='SALESMAN' and sal > (select avg(sal) from emp);
10.显示所有职员的姓名及其所在部门的名称和工资
答：考察知识点：表连接
select ename, job, dname from emp e, dept d where e.deptno = d.deptno;
11.查询在研发部('RESEARCH')工作员工的编号，姓名，工作部门，工作所在地
答：考察知识点：表连接
select empno,ename,dname,loc from emp e, dept d
where e.deptno = d.deptno and danme='RESEARCH';
12.查询各个部门的名称和员工人数
分析：常见的一个误解就是把此题当做自连接做：
select * from emp e1, emp e2 where e1.hhireiredate = e2.date and e1.empno <> e2.empno;
这样做的结果中对于只有两个日期相等的没有错误，查询结果有2条，但如果有三个日期相
等的查询结果就是6条，其中3条是重复的。
需要将
select * from emp order by sal desc
先排序之后的结果看做一个临时表，再对此临时表产生rownum编号。
要点二是rownum不能用作>或>=的比较条件，因此不能够直接这样写
select e.* from
(select * from emp order by sal desc) e