第3章習(xí)題答案

1、文檔可能無(wú)法思考全面，請(qǐng)瀏覽后下載! 第3章：習(xí)題答案3用SQL語(yǔ)句建立第2章習(xí)題5中的四個(gè)表。設(shè)有一個(gè)SPJ數(shù)據(jù)庫(kù)，包括S，P，J，SPJ四個(gè)關(guān)系模式：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),

2、city char(10) );10 / 11注：S表、P表、J表的主鍵可直接在列后加Primary Key ，參見(jiàn)P85SPJ表的建表語(yǔ)句如下：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);給表格插入數(shù)據(jù)：insert into sselect 'S1

3、','精益','20','天津'unionselect 'S2','盛錫','10','北京'unionselect 'S3','東方紅','30','北京'unionselect 'S4','豐泰盛','20','天津'unionselect 'S5','為民','30','上海'in

4、sert into pselect 'P1','螺母','紅',12unionselect 'P2','螺栓','綠',17unionselect 'P3','螺絲刀','藍(lán)',14unionselect 'P4','螺絲刀','紅',14unionselect 'P5','凸輪','藍(lán)',40unionselect 'P6','齒輪

5、','紅',30insert into jselect 'J1','三建','北京'unionselect 'J2','一汽','長(zhǎng)春'unionselect 'J3','彈簧廠','天津'unionselect 'J4','造船廠','天津'unionselect 'J5','機(jī)車(chē)廠','唐山'unionselect 'J6

6、','無(wú)線電廠','常州'unionselect 'J7','半導(dǎo)體廠','南京'insert into spjselect 'S1','P1','J1',200unionselect 'S1','P1','J3',100unionselect 'S1','P1','J4',700unionselect 'S1','P2','

7、J2',100unionselect 'S2','P3','J1',400unionselect 'S2','P3','J2',200unionselect 'S2','P3','J4',500unionselect 'S2','P3','J5',400unionselect 'S2','P5','J1',400unionselect 'S2

8、','P5','J2',100unionselect 'S3','P1','J1',200unionselect 'S3','P3','J1',200unionselect 'S4','P5','J1',100unionselect 'S4','P6','J3',300unionselect 'S4','P6','J4'

9、;,200unionselect 'S5','P2','J4',100unionselect 'S5','P3','J1',200unionselect 'S5','P6','J2',200unionselect 'S5','P6','J4',5004針對(duì)上題中建立的四個(gè)表，試用SQL語(yǔ)句完成第2章習(xí)題5中的查詢。（1）求供應(yīng)工程J1零件的供應(yīng)商號(hào)碼SNO：select distinct sno from

10、 spjwhere jno='J1'（2）求供應(yīng)工程J1零件P1的供應(yīng)商號(hào)碼SNO：select distinct sno from spjwhere jno='J1' and pno='P1'（3）求供應(yīng)工程J1零件為紅色的供應(yīng)商號(hào)碼SNO使用連接查詢select sno from spj,pwhere spj.pno=p.pno and jno='J1' and color='紅'或者：用子查詢select sno from spjwhere jno='J1' and pno in (selec

11、t pno from p where color='紅')（4）求沒(méi)有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號(hào)JNO解：使用多重嵌套查詢select jno from jwhere not exists( select * from spj where spj.jno=j.jno and sno in (select sno from s where city='天津') and pno in (select pno from p where color='紅')或者：子查詢中使用多表聯(lián)接，使用exists謂詞select jno from j wh

12、ere not exists( select * from spj , s ,pwhere spj.jno=j.jno and s.sno=spj.sno and p.pno=spj.pno and s.city='天津' and p.color='紅' )或者：子查詢中使用多表聯(lián)接，使用in謂詞select jno from jwhere jno not in( select jno from spj,s,pwhere spj.sno=s.sno and spj.pno=p.pnoand s.city='天津' and p.color='

13、;紅' )5針對(duì)習(xí)題3中的四個(gè)表試用SQL語(yǔ)言完成以下各項(xiàng)操作： (1)找出所有供應(yīng)商的姓名和所在城市。 SELECT SNAME,CITY FROM S(2)找出所有零件的名稱(chēng)、顏色、重量。SELECT PNAME,COLOR,WEIGHT FROM P (3)找出使用供應(yīng)商S1所供應(yīng)零件的工程號(hào)碼。 SELECT DISTINCT JNO FROM SPJ WHERE SNO='S1'(4)找出工程項(xiàng)目J2使用的各種零件的名稱(chēng)及其數(shù)量。SELECT PNAME,QTY FROM SPJ,P WHERE P.PNO=SPJ.PNO AND SPJ.JNO='J

14、2' (5)找出上海廠商供應(yīng)的所有零件號(hào)碼。SELECT distinct PNO FROM SPJ,S WHERE S.SNO=SPJ.SNO AND CITY='上海'或者：SELECT distinct PNO FROM SPJWHERE sno in (select sno from s where CITY='上海')(6) 找出使用上海產(chǎn)的零件的工程名稱(chēng)。SELECT distinct JNAME FROM SPJ,S,JWHERE S.SNO=SPJ.SNO AND S.CITY='上海' AND J.JNO=SPJ.JNO

15、或者：SELECT JNAME FROM JWHERE jno in ( select jno from spj,sWhere S.SNO=SPJ.SNO AND S.CITY='上海')(7)找出沒(méi)有使用天津產(chǎn)的零件的工程號(hào)碼。select distinct jno from jwhere jno not in(select distinct jno from spj,swhere city='天津' and s.sno=spj.sno)注意: 以下這種方法適用于spj表中JNO是唯一的情況:SELECT distinct JNO FROM SPJ,S WHE

16、RE S.SNO=SPJ.SNO AND S.CITY<>'天津'結(jié)果中有J1，是因?yàn)镾PJ表中有（S2,P3,J1）滿足條件。而J1顯然不是答案，因?yàn)橛蠸PJ表中（S1,P1,J1）?；蛘撸簊elect distinct jno from jwhere not exists(select * from spj,swhere city='天津' and s.sno=spj.sno and j.jno=spj.jno)(8)把全部紅色零件的顏色改成藍(lán)色。UPDATE P SET COLOR='藍(lán)' WHERE COLOR='紅&

17、#39;(9)由S5供給J4的零件P6改為由S3供應(yīng)。 UPDATE SPJ SET SNO='S3' WHERE SNO='S5' AND JNO='J4' AND PNO='P6'(10)從供應(yīng)商關(guān)系中刪除供應(yīng)商號(hào)是S2的記錄，并從供應(yīng)情況關(guān)系中刪除相應(yīng)的記錄。DELETE FROM SPJ WHERE SNO='S2' DELETE FROM S WHERE SNO='S2'注意：刪除順序，應(yīng)該先刪除spj表的記錄，再刪除s表的記錄。 (11)請(qǐng)將(S2，J6，P4，200)插入供應(yīng)情況關(guān)系。

18、INSERT INTO SPJ(sno,jno,pno,qty) VALUES('S2','J6','P4',200)注意：當(dāng)into子句指定列名時(shí)，插入的屬性值順序要和into子句中的一致。insert into spj values('S2','P4','J6',200)注意：當(dāng)into子句沒(méi)有指定列名時(shí)，插入的屬性值順序要和表定義中的一致。11請(qǐng)為三建工程項(xiàng)目建立一個(gè)供應(yīng)情況的視圖，包括供應(yīng)商代碼(SNO)、零件代碼(PNO)、供應(yīng)數(shù)量(QTY)。針對(duì)該視圖VSP完成下列查詢：(1)找出三建工程項(xiàng)目使用的各種零件代碼及其數(shù)量。(2)找出供應(yīng)商S1的供應(yīng)情況。解：CREATE VIEW VSP AS SELECT SNO,PNO,QTY FROM SPJ,J WHERE SPJ.JNO=J.JNO AND J.