SPJ数据库

设有一个SPJ数据库，包括S，P，J，SPJ四个关系模式：供应商S(SNO,SNAME,STATUS,CITY)零件P(PNO,PNAME,COLOR,WEIGHT)工程J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY)创建数据库：createdatabaseSPJ;创建表：CREATETABLES(SNOchar(2)primarykey,SNAMEvarchar(20),STATUSchar(4),CITYchar(10));createtableP(PNOchar(2)primarykey,PNAMEvarchar(20),COLORchar(4),WEIGHTint);createtableJ(JNOchar(2)primarykey,JNAMEvarchar(20),CITYchar(10));createtableSPJ(SNOchar(2),PNOchar(2),JNOchar(2),QTYint,primarykey(SNO,PNO,JNO),foreignkey(SNO)referencesS(SNO)ondeletecascade,foreignkey(PNO)referencesP(PNO)ondeletecascade,foreignkey(JNO)referencesJ(JNO)ondeletecascade);插入数据：insertintoSvalues('S1','供应商一','良好','郑州');insertintoSvalues('S2','供应商二','良好','天津');insertintoSvalues('S3','供应商三','良好','上海');insertintoSvalues('S4','供应商四','优秀','郑州');insertintoPvalues('P1','管材','红色',20);insertintoPvalues('P2','管线','绿色',30);insertintoPvalues('P3','螺帽','红色',10);insertintoPvalues('P4','螺帽','黄色',10);insertintoJvalues('J1','一建工程','郑州');insertintoJvalues('J2','二建工程','郑州');insertintoJvalues('J3','三建工程','天津');insertintoJvalues('J4','四建工程','天津');insertintoJvalues('J5','五建工程','上海');insertintoJvalues('J6','六建工程','北京');insertintoSPJvalues('S1','P1','J1',100);insertintoSPJvalues('S1','P2','J1',100);insertintoSPJvalues('S2','P1','J1',100);insertintoSPJvalues('S2','P2','J1',100);insertintoSPJvalues('S1','P1','J3',100);insertintoSPJvalues('S1','P2','J3',100);insertintoSPJvalues('S2','P1','J3',100);insertintoSPJvalues('S2','P2','J3',100);insertintoSPJvalues('S3','P1','J2',100);insertintoSPJvalues('S3','P2','J2',100);insertintoSPJvalues('S4','P3','J2',100);1）求供应工程J1零件的供应商号码SNO：selectdistinctSNOfromSPJwhereJNO='J1';2）求供应工程J1零件P1的供应商号码SNO：selectSNOfromSPJwhereJNO='J1'andPNO='P1';3）求供应工程J1红色零件的供应商号码SNO：selectSNOfromSPJ,PwhereSPJ.PNO=P.PNOandJNO='J1'andP.COLOR='红色';或者SELECTSNO/*这是嵌套查询*/FROMSPJWHEREJNO='J1'ANDPNOIN/*找出红色零件的零件号码PNO*/(SELECTPNOFROMP/*从P表中找*/WHERECOLOR='红色');4）求没有使用天津供应商生产的红色零件的工程号JNOselectdistinctJNOfromJwhereJNONOTIN(selectJNOfromSPJ,P,SwhereSPJ.SNO=S.SNOandSPJ.PNO=P.PNOandP.COLOR='红色'andS.CITY='天津');或者：SELECTJNOFROMJWHERENOTEXISTS(SELECT1FROMSPJWHERESPJ.JNO=J.JNOANDSNOIN/*天津供应商的SNO*/(SELECTSNOFROMSWHERECITY='天津')ANDPNOIN/*红色零件的PNO*/(SELECTPNOFROMPWHERECOLOR='红色'));5）求至少用了供应商S1所供应的全部零件的工程号JNO要求这样的工程x,使(y)p→q为真。即,对于所有的零件y,满足逻辑蕴涵p→q：p表示谓词供应商S1供应了零件y；q表示谓词工程x选用了零件y。即,只要供应商S1供应了零件y为真,则工程x选用了零件y为真。逻辑蕴涵可以转换为等价形式：﹁y(p∧﹁q)它所表达的语义为：不存在这样的零件y,供应商S1供应了y,而工程x没有选用y。SELECTDISTINCTJNOFROMSPJASSPJ1WHERENOTEXISTS/*这是一个相关子查询*/(SELECT1/*父查询和子查询均引用了SPJ表*/FROMSPJASSPJ3/*用别名将父查询与子查询中的SPJ表区分开*/WHERESNO='S1'ANDNOTEXISTS(SELECT1FROMSPJASSPJ2WHERESPJ2.PNO=SPJ3.PNOANDSPJ2.JNO=SPJ1.JNO));执行下面操作：（1）把全部红色零件的颜色改成蓝色。UPDATEPSETCOLOR='红色'WHERECOLOR='蓝色';（2）从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录。DELETEFROMSPJWHERESNO='S2';DELETEFROMSWHERESNO='S2';注意删除顺序,应该先从sn表中删除供应商S2所供应零件的记录,然后从S表中删除S2。（3）请将(S2，J6，P4，200)插入供应情况关系。INSERTINTOSPJ(SNO,JNO,PNO,QTY)VALUES('S2','J6','P4',200);或INSERTINTOSPJVALUES('S2','P4','J6',200);---------------------------------------------------------------------------------------------------------------------------------补充查询：（1）找出所有供应商的姓名和所在城市SELECTSNAME,CITYFROMS;（2）找出所有零件的名称、颜色、重量SELECTPNAME,COLOR,WEIGHTFROMP;（3）找出使用供应商S1所供应零件的工程号码SELECTJNOFROMSPJWHERESNO='S1';（4）找出工程项目J2使用的各种零件的名称及其数量。SELECTP.PNAME,SPJ.QTYFROMP,SPJWHEREP.PNO=SPJ.PNOANDSPJ.JNO='J2';（5）找出上海厂商供应的所有零件号码。SELECTDISTINCTPNOFROMSPJWHERESNOIN(SELECTSNOFROMSWHERECITY='上海');（6）找出使用上海产的零件的工程名称。SELECTdistinctJNAMEFROMJ,SPJ,SWHEREJ.JNO=SPJ.JNOANDSPJ.SNO=S.SNOANDS.CITY='上海';或SELECTJNAMEFROMJWHEREJNOIN(SELECTJNOFROMSPJ,SWHERESPJ.SNO=S.SNOANDS.CITY='上海');（7）找出没有使用天津产的零件的工程号码。SELECTJNOFROMJWHERENOTEXISTS(SELECT1FROMSPJWHERESPJ.JNO=J.JNOANDSNOIN(SELECTSNOFROMSWHERECITY='天津'));或SELECTJNOFROMJWHERENOTEXISTS(SELECT1FROMSPJ,SWHERESPJ.JNO=J.JNOANDSPJ.SNO=S.SNOANDS.CITY='天津');补充修改：由S4供给J4的零件P4改为由S3供应,请做必要的修改。insertintoSPJvalues('S4','P4','J4',200);UPDATESPJSETSNO='S3'WHERESNO='S4'ANDJNO='J4'ANDPNO='P4';补充删除：从供应商关系中删除S4的记录,并从供应情况关系中删除相应的记录。DELETEFROMSPJWHERESNO='S4';DELETEFROMSWHERESNO='S4';------------------------------------------------------------------------------------------------------------------------------------------------------------------建立视图：为三建工程项目建立一个供应情况的视图，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。CREATEVIEWV_SPJASSELECTSNO,PNO,QTYFROMSPJWHEREJNO=(SELECTJNOFROMJWHEREJNAME='三建工程');针对该视图VSP完成下列查询：(1)找出三建工程项目使用的各种零件代码及其数量。SELECTPNO,QTYFROMV_SPJ;(2)找出供应商S1的供应情况。SELECTPNO,QTYFROMV_SPJWHERESNO='S1';