1、模块一 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
2、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) 和
3、 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. 显示佣金(C
4、OMM)收入比工资 (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(
5、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
6、(*), 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 grou
7、p 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); 分析：当查询结果是
8、一行一列时，可以将此结果看做一个值，参与条件比较。 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员工工资相同的员工的姓名和工资 答：考察知识点：
9、子查询 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, dnam
10、e 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
11、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
12、 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 e.sal)+1 rank from emp e order by rank; 分析： 此题的要点在于理解 select count(*) from emp where sal e.sal+1 的含义，e.
13、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.hired
14、ate and e1.empno 或=的比较条件，因此不能够直接这样写 select e.* from (select * from emp order by sal desc) e where rownum 5 and rownum 或=比较条件： select * from ( select e.*,rownum rn from (select * from emp order by sal desc) e where rownum 5; 两次查询示例图如下：第一次取前 10 条，第二次排除前 5 条 22. 查询各部门工资最高的员工信息 答：考察知识点：子查询 select * from
15、 emp e where e.sal = (select max(sal) from emp where (deptno = e.deptno); 分析：要点同样是理解子查询 select max(sal) from emp where (deptno = e.deptno)获取当前部门（e.deptno）的最高工资，再将此最高值与当前工资（e.sal）进行比较。 思路 2： select e.* from (select max(sal) maxsal, deptno from emp group by deptno) b, emp e where e.deptno = b.deptno a
16、nd b.maxsal = e.sal; 将子查询看做一个临时表，临时表中有最高工资列 maxsal，以及 deptno 列，此临时表与真实表 emp 做表连接，连接条件为 emp 表中的工资要等于临时表的最高工资并且两表的部门编号要相等。 23. 查询每个部门工资最高的前 2 名员工 答：考察知识点:子查询 select * from emp e where (select count(*) from emp where sal e.sal and e.deptno = deptno) e.sal and e.deptno = deptno 的含义：求工资大于当前员工工资（e.sal ）并且
17、部门编号等于当前员工部门编号（e.deptno）的员工的个数，此个数+1 表示排名，= t2.minrank and t1.rn = t2.minrank and t1.rn 2; 分析：关键是理解连接条件 e.empno = mgr 是表示连接当前员工（e.empno）和他的下属（mgr） 25. 查询所有大于本部门平均工资的员工信息() 答：考察知识点：子查询 select * from emp e where sal (select avg(sal) from emp where (deptno = e.deptno) order by deptno; 分析：思路与 22 题相同。 26
18、. 查询平均工资最高的部门信息 答：考察知识点：子查询，组函数，连接查询 select d.*, avgsal from dept d, (select avg(sal) avgsal, deptno from emp group by deptno) se where avgsal = (select max(avg(sal) from emp group by deptno) and d.deptno = se.deptno; 分析： 步骤 1：求每个部门的平均工资： select avg(sal) avgsal, deptno from emp group by deptno; 步骤 2
19、：求最高的平均工资： select max(avg(sal) from emp group by deptno; 步骤 3：求平均工资最高的部门信息，连接步骤 1 产生的临时表与真实表 dept： select d.*, avgsal from dept d, (select avg(sal) avgsal, deptno from emp group by deptno) se where avgsal = (select max(avg(sal) from emp group by deptno) and d.deptno = se.deptno; 27. 查询大于各部门总工资的平均值的部
20、门信息 答：考察知识点：子查询，组函数，连接查询 select d.*,sumsal from dept d, (select sum(sal) sumsal, deptno from emp group by deptno) se where sumsal (select avg(sum(sal) from emp group by deptno) and se.deptno = d.deptno; 分析： 步骤 1：求每个部门总工资 select sum(sal) sumsal, deptno from emp group by deptno; 步骤 2：求每总工资平均值 select a
21、vg(sum(sal) from emp group by deptno; 步骤 3：求大于总工资平均值的部门信息，连接步骤 1 产生的临时表与真实表 dept： select d.*,sumsal from dept d, (select sum(sal) sumsal, deptno from emp group by deptno) se where sumsal (select avg(sum(sal) from emp group by deptno) and se.deptno = d.deptno; 28. 查询大于各部门总工资的平均值的部门下的员工信息(考察知识点:子查询,组函
22、数, 连接查询) 答：考察知识点：子查询，组函数，连接查询 select e.*,sumsal from emp e, (select sum(sal) sumsal, deptno from emp group by deptno) se where sumsal (select avg(sum(sal) from emp group by deptno) and se.deptno = e.deptno; 分析：类似于 26 题，27 题 29. 查询没有员工的部门信息 答：考察知识点：表连接 select d.* from dept d left join emp e on (e.dep
23、tno = d.deptno) where empno is null; 分析：利用了左外连接的特点，部门连接员工时，没有匹配记录的部门对应的员工编号列肯定为 null 30. 查询用户（users 表）huxz 所下所有订单编号，下单日期，总价格 (orders 表)，并包括订单中的商品数量（orderitem 表） ，名称（product 表） ，价格（ product 表） 答：考察知识点：多表连接 select u.username, o.orderid, o.orderdate, o.totalprice, p.productname, p.price, i.qty from use
24、rs u inner join orders o on (u.username = o.username) inner join orderitem i on (o.orderid = i.orderid) inner join product p on (p.productid = i.productid) where u.username = huxz; 31. 查询 100001 号商品被哪些顾客（ users 表）购买过，下单日期(orders 表) ，每人购买的数量（orderitem 表） ，购买时的价格（product 表） 答：考察知识点：多表连接 select u.usern
25、ame, o.orderdate, p.productname, p.price, i.qty from product p inner join orderitem i on (p.productid = i.productid) inner join orders o on (o.orderid = i.orderid) inner join users u on (u.username = o.username) where p.productid = 100001; 32. 查询出哪些商品从未被订购过 答：考察知识点：连接查询 select p.* from product p lef
26、t join orderitem i on(i. productid = p. productid) where i.orderitemid is null; 分析：同 29 题 33. 查询出被订购过 2 次以上的商品信息 答：考察知识点：连接查询，子查询 select p.* from product p where (select count(*) from orderitem where productid = p. productid) = 2; 分析：子查询 select count(*) from orderitem where productid = p. productid
27、的含义是：当前商品在 orderitem 表中出现的次数，即被订购的次数 Part III(面试题目) 01. tmp 表中有如下记录（建表 SQL 见 emp.sql） 要求结果格式为： 答：考察知识点：case when count 函数 select rq, count(case when shengfu=WIN then 1 else null end) WIN, count(case when shengfu=LOSE then 1 else null end) LOSE from tmp group by rq ; 分析： 要点：在计数时，配合 casewhen语句只统计取值为 W
28、IN 或 LOSE 的个数。case 语句返回不为 null 的值即会加入 count 计数，返回 null 则不会加入 count 计数。 02. 查询当前月有多少天 答：考察知识点 日期函数的灵活运用 select trunc(add_months(sysdate,1),month) - trunc(sysdate,month) from dual; 03. pages 表有四个字段，id, url,title,body。如图： 现要求将 url 匹配的排在最前，title 匹配的其次，body 匹配最后，没有任何字段匹配的，不返回。现要求查询所有匹配 baidu 的记录，最终查询结果如图
29、： 建表语句在 emp.sql 中 答：考察知识点：union select id,content from ( select id, 3 mark, url content from pages where url like %baidu% union select id, 2, title from pages where title like %baidu% union select id, 1, body from pages where body like %baidu% ) order by mark desc; 要点：union 可以用来合并多次查询结果。这里需要注意多次查询的结
30、果列的个数和类型必须相同，合并后的结果集也可以看做一张表，表的列的类型和名称由 union 的第一条查询结果来决定。 这里用到一个技巧：手工指定一个优先级 mark 列，最后根据 mark 列排序。 04. 现有 STUDENT(学生), COURSE(课程), SC(成绩)表,完成以下需求(建表语句在 emp.sql 中,综合考察) a) 查询选修课程为 web 的学员学号和姓名 答： select s.sid,s.name from student s inner join sc on(s.sid=sc.sid) inner join course c on (c.cid=sc.cid)
31、where c.name = web; 分析：课程与学生表没有直接联系，必须通过中间成绩表做 2 次表连接 b) 查询课程编号为 2 的学员姓名和单位 答： select s.name,s.dept from student s inner join sc on (s.sid=sc.sid) where sc.cid = 2; c) 查询不选修 4 号课程的学员姓名和单位 答： select name,dept from student where sid not in (select s.sid from student s left join sc on s.sid = sc.sid wh
32、ere cid = 4); 或： select name,dept from student where not exists(select sid from sc where sc.sid = s.sid and cid = 4); 分析：要点是先要查询出选修了 4 号课程的学员 id，再从所有学员中排除这些 id 的学员。方法 2 效率较高。 d) 查询选修全部课程的学员姓名和单位 答： select s.name,s.dept from student s where sid in (select sid from sc group by sid having (count(*) = (
33、select count(*) from course); 分析： 步骤 1：查询出所有课程的数目 select count(*) from course; 步骤 2：在成绩(sc)表，按学员 id 分组，看每组的个数，该个数等于步骤 1 课程总数的sid 即为选修了所有课程的学员 id select sid from sc group by sid having (count(*) = (select count(*) from course); 步骤 3：再根据该 sid 查询学员的详细信息 select s.name,s.dept from student s where sid in
34、(select sid from sc group by sid having (count(*) = (select count(*) from course); e) 查询选修课程超过 3 门的学员姓名和单位 答： select s.name,s.dept from student s where sid in (select sid from sc group by sid having (count(*) 3); f) 找出没有选修过 Teacher LI 讲授课程的所有学生姓名 答： select s.name from student s where sid not in (sel
35、ect sid from course c left join sc on (c.cid = sc.cid) where c.teacher=Teacher LI); g) 列出有二门以上(含两门) 不及格课程的学生姓名及其平均成绩 答： select s.*, b.avgsal from student s, (select sc.sid,avg(score) avgscore from sc , (select sid from sc where score =2) a where sc.sid = a.sid group by sc.sid) b where s.sid = b.sid;
36、 分析： 步骤 1：查询所有两门以上不及格的学员 id select sid from sc where score =2); 步骤 2：步骤 1 结果与真实表 sc 做连接，算平均成绩 select sc.sid,avg(score) avgscore from sc, (select sid from sc where score =2) a where sc.sid = a.sid group by sc.sid; 步骤 3：步骤 2 结果与真实表 student 做连接，查学员姓名 select s.*, b. avgscore from student s, (select sc.s
37、id,avg(score) avgscore from sc , (select sid from sc where score =2) a where sc.sid = a.sid group by sc.sid) b where s.sid = b.sid; 思路 2： 步骤 1：同上 步骤 2：步骤 1 结果与真实表 sc，student 共 3 张表做连接 select s.*,avg(sc.score) avgscore from student s, sc, (select sid from sc where score =2) a where s.sid = sc.sid and
38、 s.sid = a.sid; 步骤 3：可以发现，该结果中 sid, name, dept, age 都是取值都相同，按照这些列直接进行分组即可： select s.*,avg(sc.score) avgscore from student s, sc, (select sid from sc where score =2) a where s.sid = sc.sid and s.sid = a.sid group by s.sid, s.name, s.dept, s.age; h) 列出既学过 1 号课程，又学过 2 号课程的所有学生姓名 答： select s.name from s
39、tudent s inner join (select sc.sid from sc where sc.cid in (1,2) group by sid having (count(*) = 2) a on (s.sid = a.sid); 分析：要点是不仅要学过 1， 2 号课程 in (1,2)，并且要求同时学过此两门课 count(*) = 2 i) 列出 1 号课成绩比 2 号课成绩高的所有学生的学号，姓名和 1 号课和 2 号课的成绩 答： select s.sid, s.name, sc1.score, sc2.score from sc sc1,sc sc2,student s
40、 where s.sid = sc1.sid and sc1.sid = sc2.sid and sc1.cid = 1 and sc2.cid = 2 and sc1.score sc2.score; 分析：要点在于自连接，把成绩表拆成两张表来看，sc1 中只考虑 1 号课，sc2 中只考虑 2 号课且 sc1.score sc2.score；最后再考虑将结果与 student 表连接查询姓名。 05. 现有 test 表，表中数据如图所示： 要求按照格式如下输出结果： a) 连续的编号要求如下格式 b) 不连续的编号要求如下格式 答： a) 求连续的，考察知识点：rownum，子查询 分析：查看连续 id 与 rownum 之间的关系，运行 select id, rownum, id-rownum from test; 参考下图看出规律： 可以发现，id-rownum 取值相同的，就是那些 id 编号连续的。按照 id-rownum 分组并求每组的最大，最小值即可。 select a.* from (select min(id) begin, max(id) end from test group by (id - rownum) order by id - rownum) a; b) 不连续的，考察知识点，rownum，子查询 分析： 步骤一：
