第3章習(xí)題答案

1、第3章：習(xí)題答案3. 用SQL語句建立第2章習(xí)題5中的四個(gè)表。設(shè)有一個(gè)SPJ數(shù)據(jù)庫，包括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),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);給表格插入數(shù)據(jù)：insert into sselect SI,精益,20,天津unionselect S2,

3、盛錫， 10,北京unionselect S3,東方紅，30,北京unionselect S4,豐泰盛， 20,天津unionselect為民：30：上海insert into pselect螺母，紅，12unionselect P2,螺栓，綠，17unionselect P3,螺絲刀，藍(lán)，14unionselect P4,螺絲刀，紅，14unionselect P5 , * 凸輪，藍(lán)，40unionselect，P6，齒輪，紅，30insert into jselect Jl,三建，北京unionselect J2 汽，長春unionselect J3,彈簧廠,天津unionselect J

4、4,造船廠，天津 unionselect J5,機(jī)車廠,唐山unionselect J6,無線電廠，常州unionselect 9 半導(dǎo)體廠南京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. 針對(duì)上題中建立的四個(gè)表，試用SQL語句完成第2章習(xí)題5 中的查詢。（1）求供應(yīng)工程J1零件的供應(yīng)商號(hào)碼SNO：

6、select distinct sno from spjwhere jno二JT（2）求供應(yīng)工程JI零件Pl的供應(yīng)商號(hào)碼SNO：select distinct sno from spjwhere jno= JT and pno=，PV（3）求供應(yīng)工程JI零件為紅色的供應(yīng)商號(hào)碼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) 求沒有使用天津供應(yīng)商生產(chǎn)

7、的紅色零件的工程號(hào)J0解：使用多重嵌套查詢select jno from j where not exists(select * from spjwhere =and snoin(selectsnofroms wherecity二天津)and pnoin(selectpnofromp wherecolor二紅)或者：子查詢中使用多表聯(lián)接，使用exists謂詞select jno from j where not exists(select * from spj , s ,p whereandandand二天津and =，紅)或者：子查詢中使用多表聯(lián)接，使用in謂詞 select jno fro

8、m jwherejno not in(select jno from spj, s, pwhere = and =and 二天津and 二紅)5. 針對(duì)習(xí)題3中的四個(gè)表試用SQL語言完成以下各項(xiàng)操作:(1) 找岀所有供應(yīng)商的姓名和所在城市。SELECT SNAME, CITY FROM S(2) 找出所有零件的名稱、顏色、重量。SELECT PNAME, COLOR, TOIGHT FROM P(3) 找岀使用供應(yīng)商S1所供應(yīng)零件的工程號(hào)碼。SELECT DISTINCT JNO FROM SPJ WHERE SNO二SI(4) 找出工程項(xiàng)目J2使用的各種零件的名稱及其數(shù)量。SELECT PN

9、AME, QTY FROM SPJ, PWHERE 二 AXD = J2(5) 找出上海廠商供應(yīng)的所有零件號(hào)碼。SELECT distinct PXO FROM SPJ, S WHERE = AND CITY二上海 或者：SELECT distinct PXO FROM SPJWHERE sno in (select sno from s where CITY=上海)(6) 找出使用上海產(chǎn)的零件的工程名稱。SELECT distinct JNAME FROM SPJ,S,JWHERE 二 AND 二上海，AND 二或者：SELECT JNAME FROM JWHERE jno in ( sel

10、ect jno from spj, sWhere 二 AND 二上海)(7) 找出沒有使用天津產(chǎn)的零件的工程號(hào)碼。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é)果中有JI，是因?yàn)镾PJ表中有(S2,P3,J1)滿足條件。而J1顯然不是答案，因?yàn)橛蠸PJ表中(S1,P1,J1)或者：select distinct

11、jno from jwhere not exists(select * from spj, swhere city=天津 and = and =(8) 把全部紅色零件的顏色改成藍(lán)色。UPDATE P SET COLOR二藍(lán)WHERE COLOR二紅(9) 由S5供給J4的零件P6改為由S3供應(yīng)。UPDATE SPJ SET SNO二S3 WHERE SNO二S5 AND JNO二J4 AXD PNO二P6(10) 從供應(yīng)商關(guān)系中刪除供應(yīng)商號(hào)是S2的記錄，并從供應(yīng)情況關(guān)系中刪除相應(yīng) 的記錄。DELETE FROM SPJ WHERE SNO二S2DELETE FROM S WHERE SNO二S

12、2注意：刪除順序，應(yīng)該先刪除spj表的記錄，再刪除s表的記錄。(11) 請(qǐng)將(S2, J6, P4, 200)插入供應(yīng)情況關(guān)系。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子句沒有指定列名時(shí)，插入的屬性值順序要和表定義中的一致。11.請(qǐng)為三建工程項(xiàng)目建立一個(gè)供應(yīng)情況的視圖，包括供應(yīng)商代碼(SNO)、零件 代碼(PN0)、供應(yīng)數(shù)量(QTY) o針對(duì)該視圖VSP完成下列查詢：(1) 找出三建工程項(xiàng)目使用的各種零件代碼及其數(shù)量。(2) 找出供應(yīng)商S1的供應(yīng)情況。解：CREATE VIEW VSP ASSELECT SNO, PNO, QTY FROM SPJ, JWHERE 二 AND =三建，或者：CREATE VIEW VSP ASSEL