數(shù)據(jù)庫實(shí)驗(yàn) 第二章課后題 SPJ表 SQL數(shù)據(jù)查詢

針對(duì)數(shù)據(jù)庫系統(tǒng)概論第四版第二章SQL實(shí)現(xiàn)--數(shù)據(jù)庫系統(tǒng)原理表以及數(shù)據(jù)createtableS(SNOvarchar(8)notnullprimarykey,SNAMEnvarchar(20)unique,STATUSint,CITYnvarchar(20));createtableP(PNOvarchar(8)notnullprimarykey,PNAMEnvarchar(20),COLORnvarchar(10),WEIGHTint);createtableJ(JNOvarchar(8)notnullprimarykey,JNAMEnvarchar(30),CITYnvarchar(20));createtableSPJ(SNOvarchar(8),PNOvarchar(8),JNOvarchar(8),QTYint,primarykey(SNO,PNO,JNO),foreignkey(SNO)referencesS(SNO),foreignkey(PNO)referencesP(PNO),foreignkey(JNO)referencesJ(JNO));deletefroms;deletefromJ;deletefromP;deletefromSPJ;--S表：供應(yīng)商表；insertintosvalues('Sl'，精益',20,'天津');insertintosvalues('S2'，盛錫',10,'北京');insertintosvalues('S3','東方紅',30,'北京');insertintosvalues('S4','豐泰盛',20,'天津');insertintosvalues('S5'，為民',30,'上海');--P表：零件表：insertintoPvalues('Pl'，螺母','紅',12);insertintoPvalues('P2',螺絲','綠',17);insertintoPvalues('P3'，螺絲刀','藍(lán)',14);insertintoPvalues('P4',螺絲刀','紅',14);insertintoPvalues('P5','凸輪','藍(lán)',40);insertintoPvalues('P6',齒輪','紅',30);--J表：工程項(xiàng)目表：insertintoJvalues('J1','三建','北京');insertintoJvalues('J2','一汽','長(zhǎng)春');insertintoJvalues('J3'，'彈簧廠','天津');insertintoJvalues('J4'，造船廠','天津');insertintoJvalues('J5',機(jī)車廠','唐山');insertintoJvalues('J6','無線電廠','常州');insertintoJvalues('J7'，半導(dǎo)體廠','南京');--SPJ表：供應(yīng)情況表：insertintoSPJvalues('S1','P1','J1',200);insertintoSPJvalues('S1','P1','J3',100);insertintoSPJvalues('S1','P1','J4',700);insertintoSPJvalues('S1','P2','J2',100);insertintoSPJvalues('S2','P3','J1',400);insertintoSPJvalues('S2','P3','J2',200);insertintoSPJvalues('S2','P3','J4',500);insertintoSPJvalues('S2','P3','J5',400);insertintoSPJvalues('S2','P5','J1',400);insertintoSPJvalues('S2','P5','J2',100);insertintoSPJvalues('S3','P1','J1',200);insertintoSPJvalues('S3','P3','J1',200);insertintoSPJvalues('S4','P5','J1',100);insertintoSPJvalues('S4','P6','J3',300);insertintoSPJvalues('S4','P6','J4',200);insertintoSPJvalues('S5','P2','J4',100);insertintoSPJvalues('S5','P3','J1',200);insertintoSPJvalues('S5','P6','J2',200);insertintoSPJvalues('S5','P6','J4',500);insertintoSPJvalues('S1','P1','J2',5000);-①、求供應(yīng)工程J1零件的供應(yīng)商的號(hào)碼SNO：selectdistinctSNO'供應(yīng)商號(hào)碼'fromSPJwhereJNO='J1';-②、求供應(yīng)工程J1零件P1的供應(yīng)商號(hào)碼:SNO:selectdistinctSNO'供應(yīng)商號(hào)碼'fromSPJwhereJNO='J1'andPNO='P1';-③、求供應(yīng)工程J1零件為紅色的供應(yīng)商號(hào)碼SNO：selectdistinctSNO'供應(yīng)商號(hào)碼'fromSPJwhereJNO='J1'andexists(select*fromPwhereSPJ.PNO=P.PNOandp.COLOR=紅'');-④、求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號(hào)JNO：--selectdistinctSPJ.SNOfromS,P,SPJwhereS.SNO=SPJ.SNOandP.PNO=SPJ.PNOandP.COLOR='紅'andS.CITY='天津';selectdistinctJNO'工程號(hào)'fromSPJwhereSNOnotin(selectdistinctSPJ.SNOfromS,P,SPJwhereS.SNO=SPJ.SNOandP.PNO=SPJ.PNOandP.COLOR='紅'andS.CITY='天津');--⑤、求至少用了供應(yīng)商S1供應(yīng)的所有零件的工程號(hào)JNO：-也就是說：不存在一個(gè)JNO（工程號(hào)），它S1供應(yīng)的零件它沒有使用。--或者說：不存在一個(gè)JNO（工程號(hào)），不存在某個(gè)S1供應(yīng)的零件，在此工程的零件列表中不存在。select*fromSPJ;--供應(yīng)商S1供應(yīng)的所有零件為：selectdistinctPNOfromSPJwhereSNO='S1';selectdistinctJNOfromSPJwherePNO='P1'andJNOin（selectJNOfromSPJwherePNO='P2'）;--第一個(gè)notexists對(duì)應(yīng)的是不存在，第二個(gè)notexists對(duì)應(yīng)的是供應(yīng)商S1供應(yīng)的零件它沒有使用；selectdistinctJNOfromSPJwherenotexists(select*fromSPJSPJ1whereSNO='S1'andnotexists(select*fromSPJSPJ2whereSPJ2.PNO=SPJ1.PNOandSPJ2.JNO=SPJ.JNO));--第三章：第5題：--(1)找出所有供應(yīng)商的姓名和所在城市；selectSNAME,CITYfromS;--(2)找出所有零件的名稱、顏色、重量；selectPNAME,COLOR,WEIGHTfromP;--(3)找出使用供應(yīng)商S1供應(yīng)的所有零件的工程號(hào)碼；selectdistinctJNOfromSPJwherenotexists(select*fromSPJSPJ1whereSNO='S1'andnotexists(select*fromSPJSPJ2whereSPJ2.PNO=SPJ1.PNOandSPJ2.JNO=SPJ.JNO));--(4)找出工程項(xiàng)目J2使用的各種零件的名稱和重量；selectP.PNAME,P.WEIGHTfromSPJ,PwhereJNO='J2'andP.PNO=SPJ.PNO;--(5)找出上海廠商供應(yīng)的所有零件號(hào)碼；selectdistinctPNOfromSPJ,SwhereS.CITY='上海'andSPJ.SNO=S.SNO--(6)找出使用上海產(chǎn)的零件的工程名稱；selectdistinctJ.JNAMEfromSPJ,JwhereJ.JNO=SPJ.JNOandSPJ.SNOin(selectSNOfromSwhereCITY='上海');--(7)找出沒有使用天津產(chǎn)的零件的工程號(hào)碼；selectdistinctJNOfromSPJwhereexists(select*fromSwhereS.SNO=SPJ.SNOandS.CITY!='天津');--(8)把全部紅色零件的顏色改為藍(lán)色；--select*fromP;updatePsetCOLOR='藍(lán)'whereCOLOR='紅';--(9)由S5供給J4的零件P6改為由S3供應(yīng)，請(qǐng)做出必要的修改。--select*fromSPJwhereSNO='S3'andPNO='P6'andJNO='J4';updateSPJsetSNO='S3'whereSNO='S5'