Follow me on GitHub

实验发表的日志

《数据库(oracle 10g)开发技术》课程实验及参考答案

课程使用教材:《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);

说说这几天的事

这几天好久没写日志啦,一是觉得没什么特别的事情值得记录的,另一方面,这几天忙着整这个博客程序,也没什么时间。就这下一次性解决吧。

首先是昨天的党支部会议,关于系里13个预备党员到期转正的,最终以全部转正的结果结束,首先恭喜那些顺利转正的同志了。。。  然后就是今天的班搓,还有值得一提的就是今天是本学期所有课程结束的日子,呵呵,今天微机原理的实验操作阶段也终于算是过了,报告交了,期待一个比较好的结果吧。。 本学期微机原理这门课真是让Kainy感到了危机感,,, 居然被老师用“我不入地狱谁入地狱”在课上侃了一番。汗。。。  哎。。。 另外一件算是好事吧。科协方面干部考核结果中,看到自己出现在了“获得团委的先进科技积极分子”的行列。呵呵,也不枉这学期开头的努力啦,不过也正是这时候才发现,放在外网的科协网站“由于备案原因”被停了,这空间的问题也等待解决咯。。。下学期还有校科技节要忙了,,不过忙过那段,估计也是身退的时候了,07级还在科协里的Kainy算属少部分了。 More >

移动设备浏览当前页