SPJ數(shù)據(jù)庫.docx

1、設(shè)有一個(gè)SPJ數(shù)據(jù)庫，包括S, P, J SJ四個(gè)關(guān)系模式：供應(yīng)商 S(SNO , SNAME , STATUS , CITY)零件 P(PNO , PNAME , COLOR , WEIGHT)工程 J(JNO , JNAME , CITY) SPJ(SNO , PNO , JNO , QTY)創(chuàng)建數(shù)據(jù)庫：create database SPJ;創(chuàng)建表：CREATE TABLE S ( SNO char ( 2) primarykey,SNAME varchar (20), STATUS char ( 4), CITY char (10)；create table P ( PNO char

2、(2) primarykey,PNAME varchar ( 20), COLOR char (4), WEIGHT int);J ( JNO char ( 2) primary JNAMEcreate table varchar (20), CITY charkey,(10)；SPJ ( SNO char ( 2), PNOcreate tablechar(2),JNOchar(2),插入數(shù)據(jù):QTYintinsertintoS values('S1'，'供應(yīng)商一'-良好'，insertintoS values('S2'，'供應(yīng)

3、商一j良好insertintoS values('S3',供應(yīng)曲二j良好insertintoS values('S4'，'供應(yīng)商四'優(yōu)秀,insertintoP values('P1'，管材'紅色20）;insertintoP values('P2'，'管線'綠色,30);insertintoP values('P3'/螺帽，紅色，10）；insertintoP values('P4'螺帽'更色,10),鄭州）；'天港（sKio） 上澤（pKi

4、o） 鄭州（UM。）ononondeletedeletedeletecascadecascadecascadeinsertintoJvalues('J1',1一建工程'/鄭州'）；insertintoJvalues('J2',二建工程鄭州）；insertintoJvalues('J3',三建工程'J天津'）；insertintoJvalues('J4'；四建工程'J天津'）；insertintoJvalues('J5'五建工程上海）；insertintoJvalues

5、('J6',六建工程'北京）insertintoSPJvalues('S1',P1','J1f,100);insertintoSPJvalues('S1',P2','JV,100);insertintoSPJvalues('S2',P1','JV,100);insertintoSPJvalues('S2','P2','J1',100);insertintoSPJvalues('S1',P1','J

6、3',100);insertintoSPJvalues('S1',P2','J3',100);insertintoSPJvalues('S2',P1','J3',100);insertintoSPJvalues('S2',P2','J3',100);insertintoSPJvalues('S3','P1','J2',100);insertintoSPJvalues('S3',P2','J

7、21,100);insertintoSPJvalues('S4',P3',J2',100);1)求供應(yīng)工程J1零件的供應(yīng)商號(hào)碼SNO: select distinct SNOfrom SPJWhere JNO='J1'2)求供應(yīng)工程JI零件 Pl的供應(yīng)商號(hào)碼SNO:select SNOfrom SPJWhere JNO='J1' andPNO='P1'3)求供應(yīng)工程JI紅色零件的供應(yīng)商號(hào)碼SNO:select SNO from SPJ, P Where SPJ. PNO=P. PNO and JNO='J1

8、' and P. COLOR-紅色'或者SELECT SNO /*這是嵌套查詢7 FROM SPJWHERE JNO='J1'AND PNO IN /*找出紅色零件的零件號(hào)碼PNO 7 (SELECT PNO FRoM P/*從P表中找*/WHERE COLOR ='紅色');4)求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號(hào)JNOselect distinct JNo from Jwhere JNo NoT IN ( select JNofrom SPJ, P, SWhere SPJ . SNO=S. SNO and SPJ . PNO=P. PN

9、O and P. COLOR士紅色'and S. CITY ='天津)； 或者： SELECTJNOFROM JWHERE NOT EXISTS( SELECT 1 FROM SPJ WHERE SPJ. JNO=J. JNO AND SNO IN /* 天津供應(yīng)商的 SNO7(SELECT SNO FROMSWHERE CITY ='天津')AND PNOIN /*紅色零件的PNO7(SELECT PNOFROM PWHERE COLOR紅色，);5)求至少用了供應(yīng)商S1所供應(yīng)的全部零件的工程號(hào)JNo要求這樣的工程X,使(y)p ->q為真。即，對(duì)于所有

10、的零件y,滿足邏輯蘊(yùn)涵p-q : P表示謂詞”供應(yīng)商S1供應(yīng)了零件y” ； q表示謂詞“工程X選用了零件y”。即，只要”供應(yīng)商S1供應(yīng)了零件y“為真，貝工程X選用了零件y”為真。邏輯蘊(yùn)涵可以轉(zhuǎn)換為等價(jià)形式： y(pq)它所表達(dá)的語義為：不存在這樣的零件y,供應(yīng)商S1供應(yīng)了 y,而工程X沒有選用y。SELECT DISTINCT JNOFROM SPJ AS SPJ1WHERE NOT EXISTS/*這是一個(gè)相關(guān)子查詢*/(SELECT 1/*父查詢和子查詢均引用了 SPJ表7FROM SPJ AS SPJ3/*用別名將父查詢與子查詢中的SPJ表區(qū)分開*/WHERE SNOS7 AND NOT

11、 EXISTS(SELECT 1FROM SPJ AS SPJ2WHERE SPJ2 . PNO=SPJ3 . PNOAND SPJ2 . JNO=SPJ1 . JNO);執(zhí)行下面操作：(1)把全部紅色零件的顏色改成藍(lán)色。UPDATE PSET COLOR紅色WHERE COLOR藍(lán)色(2) 從供應(yīng)商關(guān)系中刪除供應(yīng)商號(hào)是S2的記錄，并從供應(yīng)情況關(guān)系中刪除相應(yīng)的記錄。DELETEFROM SPJWHERE SNO='S2'DELETEFROMSWHERE SNO='S2'注意刪除順序，應(yīng)該先從Sn表中刪除供應(yīng)商S2所供應(yīng)零件的記錄,然后從S表中刪除S2。(3)請(qǐng)將

12、(S2，J6，P4 200)插入供應(yīng)情況關(guān)系。INSERT INTO SPJ ( SNO, JNO, PNO, QTY)VALUES ('S2', 'J6', 'P4', 200 );或INSERT INTO SPJVALUES ( £2', 'P4', 'J6', 200 );補(bǔ)充查詢:(1)找出所有供應(yīng)商的姓名和所在城市SELECT SNAME, CITYFROM S;(2)找出所有零件的名稱、顏色、重量SELECT PNAME, COLOR, WEIGHTFROM P;(3) 找出使用供應(yīng)商

13、S1所供應(yīng)零件的工程號(hào)碼SELECTJNOFROM SPJWHERE SNO='S1'(4) 找出工程項(xiàng)目J2使用的各種零件的名稱及其數(shù)量。SELECT P. PNAME, SPJ. QTYFROM P, SPJWHERE P. PNO=SPJ . PNO AND SPJ. JNO='J2'(5)找出上海廠商供應(yīng)的所有零件號(hào)碼。SELECT DISTINCT PNOFROM SPJWHERE SNOIN ( SELECT SNOFROMSWHERE CITY上海');(6)找出使用上海產(chǎn)的零件的工程名稱。SELECT distinct JNAMEFROM

14、 J, SPJ, SWHEREJ.JNO=SPJ.JNO AND SPJ.SNO=S.SNO AND S. CITY ='上海'；或SELECTJNAMEFROM JWHERE JNO IN ( SELECT JNOFROM SPJ, SWHERESPJ.SNO=S.SNO AND S.CITY=上海');(7)找出沒有使用天津產(chǎn)的零件的工程號(hào)碼。SELECTJNOFROM JWHERE NOT EXISTS( SELECT 1FROM SPJWHERE SPJ. JNO=J . JNO AND SNO IN( SELECT SNOFROMSWHERE CITY =&#

15、39;天津');或SELECTJNOFROM JWHERE NOT EXISTS ( SELECT 1FROM SPJ, SWHERE SPJ . JNO=J. JNO AND SPJ.SNO=S.SNO AND S. CITY ='天津');補(bǔ)充修改： 由S4供給J4的零件P4改為由S3供應(yīng)，請(qǐng)做必要的修改。insert into SPJ values ('S4', 'P4', 'J4', 200 );UPDATE SPJSET SN0='S3'WHERE SN0=S4' AND JN0='J4' AND PN0='P4'補(bǔ)充冊(cè)。除：從供應(yīng)商關(guān)系中刪除S4的記錄，并從供應(yīng)情況關(guān)系中刪除相應(yīng)的記錄。DELETEFROM SPJWHERE SNO='S4'DELETEFROMSWHERE SNOS4,；建立視圖：為三建工程項(xiàng)目建立一個(gè)供應(yīng)情況的視圖，包括供應(yīng)商代碼