存档在5月, 2010年

课程使用教材：《Oracle 10g SQL和PL/SQL编程指南》

一、 实验目的

《数据库开发技术》课程之实验三

连接查询，集合运算，子查询和 TOP-N 分析

1． 掌握分组查询，连接查询和集合运算的使用方法。

2． 掌握子查询的使用，包括在：

having 子句中；

from 子句中；

CREATE, UPDATA, INSERT, DELETE 语句中；

3． 掌握多列子查询和相关子查询的使用。。

4． 掌握 TOP-N 分析方法；

二、 实验内容和要求

第一部分：使用 SQL 语句完成以下查询要求。

1.    查询使用红色零件的工程名称。(考核多表（三表）连接操作) (分别使用相关的子查询、带 in 谓词的子

查询、带 exists 谓词的子查询和连接查询实现。注意体会不同实现方式的区别。实验报告要求写出其中

的两种方法)

/* 连接查询    */

select distinct jname from j,spj,p where p.pno=spj.pno and spj.jno=j.jno and color=’红’

jname

——–

弹簧厂

三建

一汽

造船厂

/*三层带 IN 谓词的嵌套查询    */

select jname from j where jno in                                        –这里上题有的 distinct 可以不需要

(select jno from spj where pno in

(select pno from p where color=’红’));

/* 带 exists 的子查询    */

Select jname from j where exists

(Select * from spj where jno=j.jno and exists

(select * from p

where pno=spj.pno and color=’红’));

/* 相关的子查询    */

Select jname from j where jno in

(Select jno from spj where jno=j.jno and ‘红’=

(select color from p

where pno=spj.pno));

.OR.

Select jname from j where jno in

(Select jno from spj where jno=j.jno and pno in

(select pno from p

where pno=spj.pno and color=’红’));

.OR.

Select jname from j where ‘红’ in

(Select color from p,spj

Where p.pno=spj.pno and jno=j.jno);

2.    *查询每一种零件被供应的次数，要求：

1）结果显示零件号、零件名称和被供应的次数。

2）零件表中的所有零件都要统计。

SELECT    P.PNO,    PNAME,    COUNT(SPJ.PNO)

FROM    P left outer join SPJ                          –必须使用外连接

ON    P.PNO = SPJ.PNO

GROUP BY    P.PNO,    PNAME

.OR.

SELECT      P.PNO,    PNAME,    COUNT(SPJ.PNO)    COUNT_P

FROM    P,    SPJ

WHERE      P.PNO = SPJ.PNO(+)

GROUP    BY    P.PNO,    PNAME;

显示效果形如下：

PNO PNAME             COUNT_P

— ———- ————–

P1  螺母                    4

P2  螺栓                    2

P3  螺丝刀                  5

P4  螺丝刀                  0

P5  凸轮                    3

P6  齿轮                    3

P7  把手                    0

已选择 7 行。

问题 1？GROUP BY P.PNO 还是 SPJ.PNO?

Answer: P.PNO

问题 2？GROUP BY P.PNO 和 GROUP BY P.PNO, PNAME 结果是否一样？

Answer: 一样,  如果只要求显示零件号。如果要求显示的是零件号和零件名称，则必须要用      GROUP  BY

P.PNO, PNAME。

问题 3？COUNT(SPJ.PNO)还是 COUNT(P.PNO)

Answer: COUNT(SPJ.PNO)，否则将那些没有供应过的零件也计算了一次，因为 P 中所有的零件都有机会出

现在查询结果中，不管有没有被供应过。

问题 4？如果查询结果不要求显示零件号，只要求显示零件名称和数量，以下这个 SQL 语句对吗？

SELECT      PNAME, COUNT(SPJ.PNO)

FROM  P,    SPJ

WHERE      P.PNO = SPJ.PNO(+)

GROUP BY    PNAME;

Answer: 不对，P 表的 PNAME 有可能重复

正确的还是要用：

SELECT        PNAME, COUNT(SPJ.PNO)

FROM  P,    SPJ

WHERE      P.PNO = SPJ.PNO(+)

GROUP BY      P.PNO,    PNAME;

3.    查询 student 表中各系学生数占全校学生人数的百分比

SELECT    A.sdept, ROUND((A.s_sum / B.s_total)*100,1)    “%Student”

FROM        (SELECT    sdept, COUNT(sno)    s_sum

FROM          student

GROUP      BY      sdept)    A,

(SELECT    COUNT(sno)    s_total

FROM          student)    B;

.OR.

SELECT    sdept, ROUND(count(sno) / (select count(sno) from student)*100,1)    “%Student”

FROM    student

GROUP      BY      sdept;

SDEPT        %Student

———- ———-

CS               33.3

FL               22.2

IS               22.2

MA               22.2

已选择 4 行。

4.    查询工资高于本部门平均工资的员工信息。

select *

from emp outer

where sal>(select avg(sal)

from    emp

where    outer.deptno=emp.deptno

group by deptno)

.OR.

select *

from emp outer

where sal>(select avg(sal)

from    emp

where    outer.deptno=emp.deptno)

5.    显示与 30 部门任何雇员薪水及岗位相匹配的雇员的姓名、部门编号、薪水及佣金。

SELECT    ename, deptno, sal, comm

FROM    emp

WHERE    sal    IN   (SELECT    sal

FROM        emp

WHERE    deptno = 30)

AND    job    IN    (SELECT    job

FROM      emp

WHERE    deptno = 30);

6.    *显示部门的雇员比部门 20 的雇员更多的部门的名称。

Select dname

From emp,dept

Where emp.deptno=dept.deptno

Group by emp.deptno,dname

Having count(empno)>(select count(empno)

From emp

Where deptno=20);

DNAME

————–

SALES

.OR.

已选择 1 行。

Select dname

From dept

Where deptno in

(select deptno from emp

Group by deptno

Having count(empno)>(select count(empno)

From emp

Where deptno=20));

7.    查询其他系中比计算机科学系某一学生年龄大的学生姓名与年龄，用两种方法实现，写其中一种即可。

SELECT Sname, Sage FROM Student

WHERE Sage > ANY ( SELECT Sage

FROM Student

WHERE Sdept=’CS’) AND Sdept<>’CS’;

/*    方法二： */

SELECT Sname, Sage FROM Student

WHERE Sage > ( SELECT min(Sage)

FROM Student

WHERE Sdept=’CS’) AND Sdept<>’CS’;

SNAME                      SAGE

——————– ———-

张军                         21

8.    *查询至少选修了学生 95002 选修的全部课程的学生学号(不包括 95002 学生本身)。

SELECT DISTINCT Sno    FROM SC SCX

WHERE sno<>’95002’ and NOT EXISTS        — 不存在

(SELECT * FROM SC SCY    — 95002 学生选修的课程

WHERE SCY.Sno=’95002′ AND

NOT EXISTS        — X 学生没有选修同一课程

( SELECT *    FROM SC SCZ

WHERE SCZ.Sno=SCX.Sno

AND SCZ.Cno=SCY.Cno));

SNO

———-

95001

附加题：

1.   *查询工资第 3 低的员工的工号、姓名和薪水（假设 SAL 子段作了惟一限制。）（提示：使用 from 子查

询语句、内嵌视图和 ROWNUM 伪列）

select empno,ename,sal

from    (select empno,ename,sal from

(select empno,ename,sal from emp

order by sal)

where rownum<=3 order by sal desc)

where rownum<=1;

EMPNO ENAME             SAL

———- ———- ———-

7876 ADAMS            1100

已选择 1 行。

.OR.

select empno,ename,sal

from (Select rownum topn,empno,ename,sal

From (select empno,ename,sal from emp

Order by sal)

Where rownum<=3) a

where topn=3;

.OR.

select empno,ename,sal

from (Select rownum,empno,ename,sal

from emp

Order by sal)

Where rownum<=3

Minus

select empno,ename,sal

from (Select rownum,empno,ename,sal

from emp

Order by sal)

Where rownum<=2;

.OR.

select empno,ename,sal from        —    此法的前题是工资值是不雷同的，这种解法适合于查找一列中

—  第 N 大值或第 N 小值

(select emp.*,dense_rank() over (order by sal) rank from emp)

where rank = 3;

第二部分：

自己设计实例完成在 CREATE, UPDATA, INSERT, DELETE 语句中使用子查询的实验；

三、 实验报告

根据实验内容要求写出相应的  SQL  语句，并记录实验过程中遇到的困难和解决问题的方

法。(实验报告中不要求写加*题目的实验过程和内容)

实验参考数据：

CREATE TABLE STUDENT(

SNO

NUMBER(5) PRIMARY KEY  ,

SNAME      VARCHAR2(20)       ,

SSEX

CHAR(2)

,

SAGE

NUMBER(3)     ,

SDEPT        VARCHAR2(10)       );

CREATE TABLE COURSE(

CNO

NUMBER(4) PRIMARY KEY,

CNAME     VARCHAR2(30),

CPNO       NUMBER(4),

CCREDIT     NUMBER(2),

CLIMIT      NUMBER(4));

CREATE TABLE SC(

SNO

CNO

NUMBER(5)  ,

NUMBER(4)  ,

GRADE      NUMBER(3,1)     ,

PRIMARY KEY(SNO,CNO));

INSERT INTO STUDENT VALUES(95001,’李明勇’,’男’,20,’CS’);

INSERT INTO STUDENT VALUES(95002,’刘晨’,’女’,19,’IS’);

INSERT INTO STUDENT VALUES(95003,’王名’,’女’,18,’MA’);

INSERT INTO STUDENT VALUES(95004,’张立’,’男’,19,’CS’);

INSERT INTO STUDENT VALUES(95005,’张军’,’男’,21,’MA’);

INSERT INTO STUDENT VALUES(95006,’王张凤’,’女’,19,’FL’);

INSERT INTO STUDENT VALUES(95007,’王敬’,’女’,18,’IS’);

INSERT INTO STUDENT VALUES(95008,’张名惠’,’男’,19,’FL’);

COMMIT;

INSERT INTO COURSE VALUES(1,’数据库’,5,5,10);

INSERT INTO COURSE VALUES(2,’数学’,NULL,3,10);

INSERT INTO COURSE VALUES(3,’信息系统’,1,4,12);

INSERT INTO COURSE VALUES(4,’操作系统’,6,4,12);

INSERT INTO COURSE VALUES(5,’数据结构’,7,5,16);

INSERT INTO COURSE VALUES(6,’数据处理’,NULL,3,15);

INSERT INTO COURSE VALUES(7,’PASCAL 语言’,6,2,NULL);

COMMIT;

INSERT INTO SC VALUES(95001,5,92);

INSERT INTO SC VALUES(95002,3,80);

INSERT INTO SC VALUES(95001,1,58);

INSERT INTO SC VALUES(95002,2,90);

INSERT INTO SC VALUES(95003,3,NULL);

INSERT INTO SC VALUES(95001,3,70);

INSERT INTO SC VALUES(95002,1,84.5);

INSERT INTO SC VALUES(95003,2,67);

INSERT INTO SC VALUES(95007,2,NULL);

INSERT INTO SC VALUES(95008,1,34.5);

INSERT INTO SC VALUES(95001,2,85);

COMMIT;

create table stuscore(cname varchar2(30),grade NUMBER(3,1), sno number(5));

insert into stuscore values(‘数据库’,78,95001);

insert into stuscore values(‘信息系统’,80,95001);

insert into stuscore values(‘操作系统’,90,95001);

insert into stuscore values(‘数据库’,79,95002);

insert into stuscore values(‘信息系统’,82,95002);

insert into stuscore values(‘操作系统’,92,95002);

COMMIT;

INSERT INTO SC VALUES(95007,2,45);

INSERT INTO SC VALUES(95007,1,44);

INSERT INTO SC VALUES(95007,3,64);

INSERT INTO SC VALUES(95007,4,34);

INSERT INTO SC VALUES(95007,5,50);

INSERT INTO SC VALUES(95007,6,20);