2014年信息技术考核数据库试题

模块一 SQL(DQL)

l 基本SQL 查询 l 运算符与函数 l 子查询 l 连接查询

建表语句emp.sql Part I(第一天)

01. 查询员工表所有数据, 并说明使用*的缺点 答：

select * from emp; 使用*的缺点有

a) 查询出了不必要的列

b) 效率上不如直接指定列名

02. 查询职位(JOB)为'PRESIDENT'的员工的工资 答：

select * from emp where job = 'PRESIDENT'; 03. 查询佣金(COMM)为0 或为NULL 的员工信息 答：重点是理解0 与null 的区别

select * from emp where comm = 0 or comm is null;

04. 查询入职日期在1981-5-1 到1981-12-31 之间的所有员工信息 答：通过此题掌握常用日期函数 select * from emp where hiredate

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')的详细信息 答：

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%'; 08. 显示各个部门经理('MANAGER')的工资 答：

select sal from emp where job = 'MANAGER';

09. 显示佣金(COMM)收入比工资(SAL)高的员工的详细信息 答：

select * from emp where comm > sal;

10. 把hiredate 列看做是员工的生日,求本月过生日的员工(考察知识点:单行函数) 答：

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'); 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; 14. 求1981 年各个月入职的的员工个数(考察知识点:组函数) 答：

select count(*), to_char(trunc(hiredate,'month'),'yyyy-mm') from emp where to_char(hiredate,'yyyy')='1981' group by trunc(hiredate,'month') 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; 03. 按照入职日期由新到旧排列员工信息 答：考察知识点：排序

select * from emp order by hiredate desc;

04. 查询员工的基本信息，附加其上级的姓名 答：考察知识点：自连接

select e.*, e2.ename from emp e, emp e2 where e.mgr = e2.empno; 05. 显示工资比'ALLEN'高的所有员工的姓名和工资 答：考察知识点：子查询

select * from emp where sal > (select sal from emp where ename='ALLEN'); 分析：当查询结果是一行一列时，可以将此结果看做一个值，参与条件比较。 06. 显示与'SCOTT'从事相同工作的员工的详细信息 答：考察知识点：子查询

select * from emp where job = (select * from emp where ename='SCOTT'); 分析：同第5 题

07. 显示销售部('SALES')员工的姓名 答：考察知识点：连接查询

select ename from emp e, dept d where e.deptno = d.deptno and d.dname='SALES'; 08. 显示与30 号部门'MARTIN'员工工资相同的员工的姓名和工资 答：考察知识点：子查询 select ename, sal from emp

where sal = (select sal from emp where deptno=30 and ename='MARTIN');

分析：同第5 题

09. 查询所有工资高于平均工资(平均工资包括所有员工)的销售人员('SALESMAN') 答：考察知识点：子查询

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 (select count(*) c, deptno from emp group by deptno) e inner join dept d on e.deptno = d.deptno;

分析：主要思路是要将子查询结果看做一个临时表，此临时表又可以与其他表做表连接 13. 查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位 答：考察知识点：子查询

select job, count(*) from emp where sal > (select avg(sal) from emp) group by job; 分析：查询结果是一行一列，可以将查询结果看做一个值，进行条件比较 14. 查询工资相同的员工的工资和姓名 答：考察知识点：子查询

select * from emp e where (select count(*) from emp where sal = e.sal group by sal) > 1; 分析：此题目类似于17 题，见17 题分析。 15. 查询工资最高的3 名员工信息 答：考察知识点：子查询，rownum

select * from (select * from emp order by sal desc) where rownum <= 3;

分析：见21 题要点一

16. 按工资进行排名,排名从1 开始,工资相同排名相同(如果两人并列第1 则没有第2 名,从第三名继续排)

答：考察知识点：子查询

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; 分析：常见的一个误解就是把此题当做自连接做：

select * from emp e1, emp e2 where e1.hiredate = e2.hiredate and e1.empno <> e2.empno; 这样做的结果中对于只有两个日期相等的没有错误，查询结果有2 条，但如果有三个日期

相等的查询结果就是6 条，其中3 条是重复的。

要点也是理解子查询的含义select count(*) from emp where e.hiredate=hiredate，代表取得与当

前员工入职日期相等的人数个数，如果个数大于1 表示此日期有相等的。 18. 查询每个部门的最高工资 答：考察知识点：分组

select deptno, max(sal) maxsal from emp group by deptno order by deptno; 19. 查询每个部门,每种职位的最高工资 答：考察知识点：分组

select deptno, job, max(sal) from emp group by deptno, job order by deptno, job;

分析：要点是理解多列分组：部门与职位都相同的分为一组，求每组的最高工资，其实就是 表示每个部门,每种职位的最高工资

20. 查询每个员工的信息及工资级别(用到表Salgrade) 答：考察知识点：不等值连接 select * from salgrade;

select e.*, sg.grade from emp e, salgrade sg where sal between losal and hisal;

21. 查询工资最高的第6-10 名员工 答：考察知识点：子查询, rownum select * from (

select e.*,rownum rn from

(select * from emp order by sal desc) e where rownum <=10) where rn > 5; 分析：

要点一是rownum 不能直接和order by 连用，因为rownum 先产生，order by 后执行，因此

需要将

select * from emp order by sal desc

先排序之后的结果看做一个临时表，再对此临时表产生rownum 编号。 要点二是rownum 不能用作>或>=的比较条件，因此不能够直接这样写 select e.* from

(select * from emp order by sal desc) e where rownum > 5 and rownum <=10; 因此需要将

select e.*,rownum rn from

(select * from emp order by sal desc) e where rownum <=10

查询结果看做一个临时表，这个临时表除了有表e 中的所有列之外，多添加一个rownum 列 并取别名为rn，这时rn 已经作为临时表中一个真实的列存在了，因此可以使用>或>=比较 条件：

select * from (

select e.*,rownum rn from

(select * from emp order by sal desc) e where rownum <=10)