第3章习题答案

1、第3章：习题答案3. 用SQL语句建立第2章习题5中的四个表。设有一个SPJ数据库，包括S, P, J, SPJ四个关系模式:create table s(sno char(3) primary key,sname char(10),status char(2),city char(10);create table p(pno char (3) primary key,pname char(10),color char(4),weight int);create table j(jno char(3) primary key,jname char(10),city char (10);注：S表

2、、P表、J表的主键可直接在列后加Primary Key ,参 见P85SPJ表的建表语句如下：create table spj(sno char (3),pno char(3),jno char(3),qty int,primary key(sno, pno,jno),foreign key (sno) references s (sno),foreign key (pno) references p (pno),foreign key (jno) references j (jno);给表格插入数据：insert into sselect SI,精益,20,天津unionselect S2,

3、盛锡， 10,北京unionselect S3,东方红，30,北京unionselect S4,丰泰盛， 20,天津unionselect为民：30：上海insert into pselect螺母，红，12unionselect P2,螺栓，绿，17unionselect P3,螺丝刀，蓝，14unionselect P4,螺丝刀，红，14unionselect P5 , * 凸轮，蓝，40unionselect，P6，齿轮，红，30insert into jselect Jl,三建，北京unionselect J2 汽，长春unionselect J3,弹簧厂,天津unionselect J

4、4,造船厂，天津 unionselect J5,机车厂,唐山unionselect J6,无线电厂，常州unionselect 9 半导体厂南京insert into spjselect S1J P1J JT, 200 unionselect SIJ P1J J3，100unionselectJ4700unionselect，Sr/P2，,，J2，,100unionselect S2Jl,400 unionselect S2J P3， J2，200 unionselect S2,P3,J4,500unionselectJ5,400unionselect，S2，/P5，Jl400unionsel

5、ectJ2,100unionselect S3J Pl,JT, 200unionselect S3,P3J Jl,200 unionselect S4,P5,Jl,100 unionselect S4J P6, J3，300 unionselect S4,P6,J4,200unionselect S5J P2J J4，100 unionselect，S5，/P3，/Jl200 unionselect S5J P6, J2, 200unionselect S5,P6J J4,5004. 针对上题中建立的四个表，试用SQL语句完成第2章习题5 中的查询。（1）求供应工程J1零件的供应商号码SNO：

6、select distinct sno from spjwhere jno二JT（2）求供应工程JI零件Pl的供应商号码SNO：select distinct sno from spjwhere jno= JT and pno=，PV（3）求供应工程JI零件为红色的供应商号码SNO使用连接查询select sno from spj, pwhere = and jno= JT and color二红，或者：用子查询select sno from spjwhere jno二JT andpno in (select pno from p where color=，红)(4) 求没有使用天津供应商生产

7、的红色零件的工程号J0解：使用多重嵌套查询select jno from j where not exists(select * from spjwhere =and snoin(selectsnofroms wherecity二天津)and pnoin(selectpnofromp wherecolor二红)或者：子查询中使用多表联接，使用exists谓词select jno from j where not exists(select * from spj , s ,p whereandandand二天津and =，红)或者：子查询中使用多表联接，使用in谓词 select jno fro

8、m jwherejno not in(select jno from spj, s, pwhere = and =and 二天津and 二红)5. 针对习题3中的四个表试用SQL语言完成以下各项操作:(1) 找岀所有供应商的姓名和所在城市。SELECT SNAME, CITY FROM S(2) 找出所有零件的名称、颜色、重量。SELECT PNAME, COLOR, TOIGHT FROM P(3) 找岀使用供应商S1所供应零件的工程号码。SELECT DISTINCT JNO FROM SPJ WHERE SNO二SI(4) 找出工程项目J2使用的各种零件的名称及其数量。SELECT PN

9、AME, QTY FROM SPJ, PWHERE 二 AXD = J2(5) 找出上海厂商供应的所有零件号码。SELECT distinct PXO FROM SPJ, S WHERE = AND CITY二上海 或者：SELECT distinct PXO FROM SPJWHERE sno in (select sno from s where CITY=上海)(6) 找出使用上海产的零件的工程名称。SELECT distinct JNAME FROM SPJ,S,JWHERE 二 AND 二上海，AND 二或者：SELECT JNAME FROM JWHERE jno in ( sel

10、ect jno from spj, sWhere 二 AND 二上海)(7) 找出没有使用天津产的零件的工程号码。select distinct jno from jwhere jno not in(select distinct jno from spj,swhere city=天津 and -注意：以下这种方法适用于spj表中JNO是唯一的情况:SELECT distinct JNO FROM SPJ, S WHERE 二 AND 天津结果中有JI，是因为SPJ表中有(S2,P3,J1)满足条件。而J1显然不是答案，因为有SPJ表中(S1,P1,J1)或者：select distinct

11、jno from jwhere not exists(select * from spj, swhere city=天津 and = and =(8) 把全部红色零件的颜色改成蓝色。UPDATE P SET COLOR二蓝WHERE COLOR二红(9) 由S5供给J4的零件P6改为由S3供应。UPDATE SPJ SET SNO二S3 WHERE SNO二S5 AND JNO二J4 AXD PNO二P6(10) 从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应 的记录。DELETE FROM SPJ WHERE SNO二S2DELETE FROM S WHERE SNO二S

12、2注意：删除顺序，应该先删除spj表的记录，再删除s表的记录。(11) 请将(S2, J6, P4, 200)插入供应情况关系。INSERT INTO SPJ (sno, jno, pno, qty) VALUES ( S2， J6, P4, 200)注意：当into子句指定列名时，插入的属性值顺序要和into子句中的一致。insert into spj values ( S2 , P4, J6 , 200)注意：当into子句没有指定列名时，插入的属性值顺序要和表定义中的一致。11.请为三建工程项目建立一个供应情况的视图，包括供应商代码(SNO)、零件 代码(PN0)、供应数量(QTY) o针对该视图VSP完成下列查询：(1) 找出三建工程项目使用的各种零件代码及其数量。(2) 找出供应商S1的供应情况。解：CREATE VIEW VSP ASSELECT SNO, PNO, QTY FROM SPJ, JWHERE 二 AND =三建，或者：CREATE VIEW VSP ASSEL