Sql语句强化

欢迎进入sql加强学习

数据表

雇员表：记录了一个雇员的基本信息
EMP（雇员表）
NO 字段 类型 描述
1 EMPNO NUMBER(4) 雇员编号
2 ENAME VARCHAR2(10) 表示雇员姓名
3 JOB VARCHAR2(9) 表示工作职位
4 MGR NUMBER(4) 表示一个雇员的领导编号
5 HIREDATE DATE 表示雇佣日期
6 SAL NUMBER(7,2) 表示月薪，工资
7 COMM NUMBER(7,2) 表示奖金或佣金
8 DEPTNO NUMBER(2) 表示部门编号

部门表：表示一个部门的具体信息
DEPT（部门表）
NO 字段 类型 描述
1 DEPTNO NUMBER(2) 部门编号
2 DNAME VARCHAR2(14) 部门名称
3 LOC VARCHAR2(13) 部门位置

奖金表：表示一个雇员的工资及奖金。
BONUS（奖金表）
NO 字段 类型 描述
1 ENAME VARCHAR2(10) 雇员姓名
2 JOB VARCHAR2(9) 雇员工作
3 SAL NUMBER 雇员工资
4 COMM NUMBER 雇员奖金

一个公司是有等级制度，用此表表示一个工资的等级
SALGRADE（工资等级表）
NO 字段 类型 描述
1 GRADE NUMBER 等级名称
2 LOSAL NUMBER 此等级的最低工资
3 HISAL NUMBER 此等级的最高工资

SQL数据
create table emp(EMPNO int,ENAME varchar(10),JOB varchar(9),MGR int,HIREDATE date,SAL int,COMM int,DEPTNO int);
create table DEPT(DEPTNO int,DNAME varchar(14),LOC varchar(13));
create table BONUS(ENAME varchar(10),JOB varchar(9),SAL int,COMM int);
create table SALGRADE(GRADE int,LOSAL int,HISAL int);

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values(7369,‘SMITH’,‘CLERK’,7902,‘1980-12-17’,800,NULL,20),
(7499,‘ALLEN’,‘SALESMAN’,7698,‘1981-02-20’,1600,300,30),
(7521,‘WARD’,‘SALESMAN’,7698,‘1981-02-22’,1250,500,30),
(7566,‘JONES’,‘MANAGER’,7839,‘1981-04-02’,2975,NULL,20),
(7654,‘MARTIN’,‘SALESMAN’,7698,‘1981-09-28’,1250,1400,30),
(7698,‘BLAKE’,‘MANAGER’,7839,‘1981-05-01’,2850,NULL,30),
(7782,‘CLARK’,‘MANAGER’,7839,‘1981-07-09’,2450,NULL,10),
(7788,‘SCOTT’,‘ANALYST’,7566,‘1987-04-19’,3000,NULL,20),
(7839,‘KING’,‘PRESIDENT’,NULL,‘1981-12-17’,5000,NULL,10),
(7844,‘TURNER’,‘SALESMAN’,7698,‘1981-09-08’,1500,0,30),
(7876,‘ADAMS’,‘CLERK’,7788,‘1987-05-23’,1100,NULL,20),
(7900,‘JAMES’,‘CLERK’,7698,‘1981-12-03’,950,NULL,30),
(7902,‘FORD’,‘ANALYST’,7566,‘1981-12-03’,3000,NULL,20),
(7934,‘MILLER’,‘CLERK’,7782,‘1982-06-23’,1300,NULL,10);

INSERT INTO dept VALUES (10,‘ACCOUNTING’,‘NEW YORK’);
INSERT INTO dept VALUES (20,‘RESEARCH’,‘DALLAS’);
INSERT INTO dept VALUES (30,‘SALES’,‘CHICAGO’);
INSERT INTO dept VALUES (40,‘OPERATIONS’,‘BOSTON’);

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
第1条为orcale语法
第2条为mysql语句
模块一 SQL(DQL)
Key Point
l 基本SQL 查询
l 运算符与函数
l 子查询
l 连接查询
建表语句emp.sql
Part I(第一天)

1. 查询员工表所有数据, 并说明使用*的缺点
   答：
   select * from emp;

select * from emp;

使用*的缺点有
a) 查询出了不必要的列
b) 效率上不如直接指定列名
02. 查询职位(JOB)为’PRESIDENT’的员工的工资
答：
select * from emp where job = ‘PRESIDENT’;

3. 查询佣金(COMM)为0 或为NULL 的员工信息
   答：重点是理解0 与null 的区别
   select * from emp where comm = 0 or comm is null;
4. 查询入职日期在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’);（Oracle语法）

Select * from emp where hiredate
Between ‘1981-5-1’ and ‘1982-12-31’ (mysql语法)
05. 查询所有名字长度为4 的员工的员工编号,姓名
答：
select * from emp where length(ename) = 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’;

select * from emp where deptno = 10 and job = ‘MANAGER’ or deptno = 20 and job =‘CLERK’;

7. 显示姓名中没有’L’字的员工的详细信息或含有’SM’字的员工信息
   答：考察知识点模糊查询
   select * from emp where ename not like ‘%L%’ or ename like ‘%SM%’;

select * from emp where ename not like ‘%L%’ or ename like ‘%SM%’;

8. 显示各个部门经理(‘MANAGER’)的工资
   答：
   select sal from emp where job = ‘MANAGER’;

select sal from emp where job = ‘MANAGER’;
09. 显示佣金(COMM)收入比工资(SAL)高的员工的详细信息
答：
select * from emp where comm > sal;

select * from emp where comm > sal;
10. 把hiredate 列看做是员工的生日,求本月过生日的员工(考察知识点:单行函数)
答：
select * from emp where to_char(hiredate, ‘mm’) = to_char(sysdate , ‘mm’);

select * from emp where DATE_FORMAT(HIREDATE,’%m’)=DATE_FORMAT(now(),’%m’)
11. 把hiredate 列看做是员工的生日,求下月过生日的员工(考察知识点:单行函数)
答：
select * from emp where to_char(hiredate, ‘mm’) = to_char(add_months(sysdate,1) , ‘mm’);

select * from emp where DATE_FORMAT(HIREDATE,’%m’)=DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 MONTH),’%m’);
12. 求1982 年入职的员工(考察知识点:单行函数)
答：
select * from emp where to_char(hiredate,‘yyyy’) = ‘1982’;

select * from emp where DATE_FORMAT(HIREDATE,’%Y’)=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;

select * from emp where hiredate
between DATE(‘1981-7-1’) and DATE_SUB(‘1982-1-1’,INTERVAL 1 DAY);

select * from emp where hiredate
between ‘1981-7-1’ and ‘1982-1-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’);

select count(*), date_format(HIREDATE,’%Y-%m’)
from emp where date_format(hiredate,’%Y’)=‘1981’
group by date_format(HIREDATE,’%m’)
order by date_format(HIREDATE,’%m’)
Part II(第二天)

1. 查询各个部门的平均工资
   答：考察知识点：分组
   select deptno,avg(sal) from emp group by deptno;

select deptno,avg(sal) from emp group by deptno;
02. 显示各种职位的最低工资
答：考察知识点：分组
select job,min(sal) from emp group by job;

select job,min(sal) from emp group by job;
03. 按照入职日期由新到旧排列员工信息
答：考察知识点：排序
select * from emp order by hiredate desc;

select * from emp order by hiredate desc;
04. 查询员工的基本信息，附加其上级的姓名
答：考察知识点：自连接
select e.*, e2.ename from emp e, emp e2 where e.mgr = e2.empno;

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

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

select * from emp where job = (select job from emp where ename=‘SCOTT’);
分析：同第5 题
07. 显示销售部(‘SALES’)员工的姓名
答：考察知识点：连接查询
select ename from emp e, dept d where e.deptno = d.deptno and d.dname=‘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’);

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

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;

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 dname=‘RESEARCH’;

select empno,ename,dname,loc from emp e, dept d
where e.deptno = d.deptno and dname=‘RESEARCH’;

12. 查询各个部门的名称和员工人数
    答：考察知识点：子查询，表连接
    select * from (select count(*) c, deptno from emp group by deptno) e
    inner join dept d on e.deptno = d.deptno;

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;

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;

select * from emp e where (select count(*) from emp where sal = e.sal group by sal) > 1;

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

select * from (select @rownum:=@rownum+1 AS rownum,e.* from (SELECT @rownum:=0) r,emp e order by sal desc) b where b.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 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 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;

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;

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;

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;

select * from (
select * from
(select a.,@rownum:=@rownum+1 as rownum from (select @rownum:=0) b,emp a order by a.sal desc) c
where rownum <=10) d
where rownum > 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)
where rn > 5;
两次查询示例图如下：第一次取前10 条，第二次排除前5 条
22. 查询各部门工资最高的员工信息
答：考察知识点：子查询
select * from emp e where e.sal = (select max(sal) from emp where (deptno = e.deptno));

select * from 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 and b.maxsal = e.sal;

select e.* from (select max(sal) maxsal, deptno from emp group by deptno) b, emp e
where e.deptno = b.deptno and 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) < 2
order by deptno, sal desc;

select * from emp e where
(select count() from emp where sal > e.sal and e.deptno = deptno) < 2
order by deptno, sal desc;
分析：此题类似于第16 题，需要理解select count() from emp where sal > e.sal and e.deptno =
deptno 的含义：求工资大于当前员工工资（e.sal）并且部门编号等于当前员工部门编号
（e.deptno）的员工的个数，此个数+1 表示排名，< 2 表示取前两名。
思路2：
使用oracle 提供的分析函数rank：
select * from (
select rank() over (partition by deptno order by sal desc) rank, e.* from emp e
) where rank < 3;
rank 函数的作用是产生排名，与普通函数不同，高亮部分都是函数语法部分，其中over 是
关键字，总体意思是指按部门编号分组（partition by deptno），按工资降序（order by sal desc）
排名。
思路3：
步骤1：按照部门，工资降序排列，并产生编号
select e.,rownum rn from (select * from emp order by deptno,sal desc) e;
步骤2：在此基础上再按照部门编号分组，求每组的编号的最小值
select min(rn) minrank,deptno from
(select e.,rownum rn from (select * from emp order by deptno,sal desc) e)
group by deptno;
步骤3：将两步产生的结果看做是临时表分别称为t1，t2，做连接，连接条件时t1 表中
部门编号等于t2 部门编号且t1.rn >= t2.minrank and t1.rn <= t2.minrank+1
select t1.* from
(select e.,rownum rn from (select * from emp order by deptno,sal desc) e) t1,
(select min(rn) minrank,deptno from
(select e.,rownum rn from (select * from emp order by deptno,sal desc) e)
group by deptno) t2
where t1.deptno = t2.deptno and t1.rn >= t2.minrank and t1.rn <= t2.minrank+1;
24. 查询出有3 个以上下属的员工信息
答：考察知识点：自连接,子查询
select * from emp e where
(select count(*) from emp where e.empno = mgr) > 2;

select * from emp e where
(select count(*) from emp where e.empno = mgr) > 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;

select * from emp e where sal >
(select avg(sal) from emp where (deptno = e.deptno))
order by deptno;
分析：思路与22 题相同。
26. 查询平均工资最高的部门信息
答：考察知识点：子查询，组函数，连接查询
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：求最高的平均工资：
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. 查询大于各部门总工资的平均值的部门信息
答：考察知识点：子查询，组函数，连接查询
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 avg(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. 查询大于各部门总工资的平均值的部门下的员工信息(考察知识点:子查询,组函数,连接
查询)
答：考察知识点：子查询，组函数，连接查询
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.deptno = 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 users 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.username, 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 left 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 的含义是：当前商
品在orderitem 表中出现的次数，即被订购的次数
Part III(面试题目)

1. 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 ;
   分析：
   要点：在计数时，配合case…when…语句只统计取值为WIN 或LOSE 的个数。case 语句返
   回不为null 的值即会加入count 计数，返回null 则不会加入count 计数。
2. 查询当前月有多少天
   答：考察知识点 日期函数的灵活运用
   select trunc(add_months(sysdate,1),‘month’) - trunc(sysdate,‘month’) from dual;
3. pages 表有四个字段，id, url,title,body。如图：
   现要求将url 匹配的排在最前，title 匹配的其次，body 匹配最后，没有任何字段匹配的，不返回。现要求查询所有匹配baidu 的记录，最终查询结果如图：
   建表语句在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 可以用来合并多次查询结果。这里需要注意多次查询的结果列的个数和类型必
   须相同，合并后的结果集也可以看做一张表，表的列的类型和名称由union 的第一条查询结
   果来决定。
   这里用到一个技巧：手工指定一个优先级mark 列，最后根据mark 列排序。
4. 现有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)
   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 where cid = 4);
   或：
   select name,dept from student s
   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() = (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
   (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
   (select sid from course c left join sc on (c.cid = sc.cid) where c.teacher=‘Teacher LI’);
   g) 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
   答：
   select s., b. avgscore from student s,
   (select sc.sid,avg(score) avgscore from sc ,
   (select sid from sc where score < 60 group by sid having(count() >=2)) a
   where sc.sid = a.sid group by sc.sid) b where s.sid = b.sid;

分析：
步骤1：查询所有两门以上不及格的学员id
select sid from sc where score < 60 group by sid having(count() >=2);
步骤2：步骤1 结果与真实表sc 做连接，算平均成绩
select sc.sid,avg(score) avgscore from sc,
(select sid from sc where score < 60 group by sid having(count() >=2)) a
where sc.sid = a.sid group by sc.sid;
步骤3：步骤2 结果与真实表student 做连接，查学员姓名
select s., b. avgscore from student s,
(select sc.sid,avg(score) avgscore from sc ,
(select sid from sc where score < 60 group by sid having(count() >=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 < 60 group by sid having(count() >=2)) a
where s.sid = sc.sid and 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 < 60 group by sid having(count() >=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 student 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
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，子查询
分析：
步骤一：
查询有上一条记录的个数
select id, (select count() from test where id+1 = t1.id) from test t1;
查询有下一条记录的个数
select id, (select count() from test where id-1 = t1.id) from test t1;
分析结果可知，个数为0 的即为我们所需要的。
步骤二：
select id, rownum r1 from test t1 where (select count() from test where id+1 = t1.id) = 0;
select id, rownum r2 from test t1 where (select count() from test where id-1 = t1.id) = 0;
分析结果可知，要求如上图格式的数据将查询1 中的r1-1 = 查询2 中的r2 列即可：
select b.id begin, a.id end from
(select id, rownum r1 from test t1 where (select count() from test where id+1 = t1.id) = 0) a,
(select id, rownum r2 from test t1 where (select count() from test where id-1 = t1.id) = 0) b
where r1-1=r2;
06. 根据EMP 表数据产生如下格式的报表（统计各部门，各职位的人数）
答：方法1 考察知识点case
select deptno,
count(case when job = ‘PRESIDENT’ then 1 else null end) PRESIDENT,
count(case when job = ‘MANAGER’ then 1 else null end) MANAGER,
count(case when job = ‘CLERK’ then 1 else null end) CLERK,
count(case when job = ‘SALESMAN’ then 1 else null end) SALESMAN,
count(case when job = ‘ANALYST’ then 1 else null end) ANALYST
from emp group by deptno order by deptno;
方法2 考察知识点：自连接
select d.deptno,
count(distinct PRESIDENT.empno) PRESIDENT,
count(distinct MANAGER.empno) MANAGER,
count(distinct CLERK.empno) CLERK,
count(distinct SALESMAN.empno) SALESMAN,
count(distinct ANALYST.empno) ANALYST from dept d
left join emp PRESIDENT
on (d.deptno=PRESIDENT.deptno and PRESIDENT.job=‘PRESIDENT’)
left join emp MANAGER
on (d.deptno=MANAGER.deptno and MANAGER.job=‘MANAGER’)
left join emp CLERK
on (d.deptno=CLERK.deptno and CLERK.job=‘CLERK’)
left join emp SALESMAN
on (d.deptno=SALESMAN.deptno and SALESMAN.job=‘SALESMAN’)
left join emp ANALYST
on (d.deptno=ANALYST.deptno and ANALYST.job=‘ANALYST’)
group by d.deptno order by d.deptno;
分析：通过dept 表多次左外连接emp 表，比如说
select d.deptno, d.dname, e.empno, e.job from dept d
left join emp e on (d.deptno = e.deptno and e.job=‘CLERK’);
结果如下：
可以看出这是求出每个部门职位为CLERK 的员工，将此结果按deptno 分组求个数：
select d.deptno, count(empno) CLERK from dept d
left join emp e on (d.deptno = e.deptno and e.job=‘CLERK’)
group by d.deptno order by d.deptno;
其中CLERK 列即为最终结果所需列。
如此类推，连接一次，求出一列，但需要注意，多表连接后，最后结果中会有重复记录，因
此使用count(distinct empno)排除重复记录后再计算个数才为正确结果。
07. 根据EMP 表数据产生如下格式的报表(统计各职位,各部门的人数)(06 题的变体)
答：方法1 考察知识点case
select job,
count(case when deptno = 10 then 1 else null end) “10”,
count(case when deptno = 20 then 1 else null end) “20”,
count(case when deptno = 30 then 1 else null end) “30”
from emp group by job order by job;
思路：同第06 题，注意列别名如果为数字开头必须使用双引号。
方法2 考察知识点：自连接
select e.job, count(distinct d10.empno) “10”,count(distinct d20.empno) “20”, count(distinct
d30.empno) “30” from
(select job from emp group by job) e
left join emp d10 on (e.job=d10.job and d10.deptno = 10)
left join emp d20 on (e.job=d20.job and d20.deptno = 20)
left join emp d30 on (e.job=d30.job and d30.deptno = 30) group by e.job order by job;
08. 按照如下格式显示7369 号员工的信息
答：考察知识点 UNION
select empno, ‘ENAME’ as KEY, ename VALUE from emp where empno = 7369
union
select empno, ‘JOB’, job from emp where empno = 7369
union
select empno, ‘HIREDATE’, to_char(hiredate,‘yyyy-mm-dd’) a from emp where empno = 7369
union
select empno, ‘MGR’, to_char(mgr) from emp where empno = 7369
union
select empno, ‘SAL’, to_char(sal) from emp where empno = 7369
union
select empno, ‘COMM’, to_char(comm) from emp where empno = 7369
union
select empno, ‘DEPTNO’, to_char(deptno) from emp where empno = 7369;
分析：使用UNION 可以将多次查询结果连接起来，要注意，每条查询的列的个数和数据类
型必须一致。因此在查询时都使用了to_char 函数将第二列同一转换为字符型。
Part IV(扩展知识点)

1. 分级查询
   Oracle 提供其他数据库没有的分级查询操作：
   例如：希望通过一次查询以树状结构显示EMP 表中的所有上下级关系
   select level, lpad(’ ‘,level-1) || empno empno, ename, mgr,deptno from emp start with
   empno=7839 connect by prior empno = mgr;
   level 是ORACLE 关键字表示分级级别，其中lpad(’ ‘,level-1) 函数是根据level 的值生成
   level-1 个空格
   又如：希望通过某个员工回溯它的所有上级，包括上级的上级
   select level, lpad(’ ',level-1) || empno, ename, mgr, deptno from emp start with empno=7369
   connect by empno = prior mgr;
2. CUBE（立方查询）
   如果想统计EMP 表中的所有职员数，每个部门的职员数，每种职位的职员数，每个部
   门每种职位的职员数，使用普通分组查询需要查询4 次。但利用ORACLE 的增强语法，
   可以非常方便的完成此类查询（经常用于生成报表）
   例如：
   select count(*),deptno,job,grouping_id(deptno,job) from emp group by cube (deptno,job)
   order by grouping_id(deptno,job) ;
   可以生成如下形式的报表：
3. 如何考察查询效率

1. 在SQL-PLUS 中执行 set autotrace on explain
2. 执行查询
   返回结果中cost（成本）与bytes（字节数）都是越低越好。

4. 闪回查询
   ORACLE 的特点还有能够更快速的恢复之前误操作的数据，这是通过闪回日志完成的
   例如：查询20 分钟之前的emp 表
   select * from emp as of timestamp sysdate - interval ‘20’ minute;
   再如：恢复5 分钟之前的7369 号员工姓名
   update emp e set ename =
   (select ename from emp
   as of timestamp systimestamp - interval ‘5’ minute where empno=e.empno )
   where empno=7369;
   甚至可以查询及恢复被删除的表
   select * from user_recyclebin;
   flashback table 表 to before drop;
5. 正则表达式
   ORACLE 在建表或查询时提供正则表达式支持：
   例如：查询名字以S 作为开头字母的员工
   select * from emp where regexp_like(ename ,’^S’);
   例如：替换电话号码显示方式
   select regexp_replace(‘123.321.1234’, ‘([0-9]{3}).([0-9]{3}).([0-9]{4})’, ‘(\1) \2-\3’)
   from dual;
   例如：取得email 地址中的用户名
   select regexp_substr(‘yihang@163.com’, ‘^[@]+’) from dual;
   例如：取得email 地址中的域名
   select regexp_substr(‘yihang@163.com’, ‘[^@]+$’) from dual;
6. 如何加注释
   建表时给表和列加注释是一个比较好的数据库编程习惯
   例如：表加注释
   comment on table 表 is ‘表注释’;
   例如：列加注释
   comment on column 表.列 is ‘列注释’;
   例如：查表注释
   select * from user_tab_comments where table_name = 表名;
   例如：查列注释
   select * from user_col_comments where table_name = 表名;__