SQL习题及答案

实验设有以下关系模式：S(SNO，SNAME，CITY)其中，S表示别SUPPLIER(供应者)，SNO为供应者代号，SNAME为供应者的名字，CITY为供应商所在的城市．主键为SNO。P(PNO，PNAME，COLOR，WEIGHT)其中，P表示PART(零件)，PNO为零件代号，PNAME为零件名，COLOR为零件颜色，WEIGHT为零件重量，主键为PNO。J(JNO，JNAME，CITY)其中，J表示JOB(工程)，JNO为工程编号，JNAME为工程名，CITY为工程所在城市，主键为JNO。SPJ(SNO，PNO，JNO，QTY)其中，SPJ表示供应关系，SNO是为指定工程提供零件的供应者代号，PNO是所提供的零件代号，JNO为工程编号，QTY表示提供的零件数量，主键为(SNO，PNO，JNO)，外部键分别为SNO，PNO，JNO。试做以下各题：1、用SQL的DDL语言创建S，P，J，SPJ四个基本表。2、按照下面表格提供的数据，用SQL的插入语句插入所有记录。3、给出下列各题的查询、存储等操作的语句序列，并且上机验证结果。1)取出所有工程的全部细节；2)取出所在城市为上海的所有工程的全部细节；3)取出重量最轻的那些零件的号码；4)取出为工程J1提供零件的供应者的代号；5)取出为工程J1提供零件P1的供应者的代号；6)取出由供应者S1提供零件的工程的名称；7)取出由供应者S1提供的零件的颜色；8)取出为工程J1和J2提供零件的供应者的代号；9)取出为工程J1提供红色零件的供应者的代号；10)取出为所在城市为上海的工程提供零件的供应者的代号；11)取出为所在城市为上海或北京的工程提供红色零件的供应者的代号；12)取出供应者与工程所在城市相同的供应者提供的零件的代号；13)取出上海的供应者提供给上海的任一工程的零件的代号；14)取出至少有一个和工程不在同一城市的供应者提供零件的工程的代号；15)取出上海供应者不提供任何零件的工程的代号；16)取出这样一些供应者的代号，他们能够提供至少一种由红色零件的供应者提供的零件；17)取出由供应者S1提供零件的工程的代号；18)取出所有这样的三元组＜CITY，PNO，CITY＞，使得第一个城市的供应者为第二个城市的工程提供零件；19)取出为所有工程提供同样零件的供应者的代号；20)取出提供给上海的所有工程的零件代号；21)取出至少需要供应者S1提供的所有零件的工程的代号；22)把所有红色零件改成橙色；23)删除所有红色零件和对应的SPJ记录；24)取出供应者Sl提供的代号为P1的零件总数；25)取出每个工程的供应商数；26)取出所需的零件总数大于1000的工程代号；1.CREATETABLES(SNOCHAR(5)PRIMARYKEY,SNAMECHAR(5),CITYCHAR(10));CREATETABLEP(PNOCHAR(5)PRIMARYKEY,PNAMECHAR(5),COLORCHAR(5),WEIGHTINT);CREATETABLEJ(JNOCHAR(5)PRIMARYKEY,JNAMECHAR(5),CITYCHAR(10));CREATETABLESPJ(SNOCHAR(5),PNOCHAR(5),JNOCHAR(5),QTYINT,CONSTRAINTPK_SPJPRIMARYKEY(SNO,PNO,JNO),CONSTRAINTFK_SNOFOREIGNKEY(SNO)REFERENCESS(SNO),CONSTRAINTFK_PNOFOREIGNKEY(PNO)REFERENCESP(PNO),CONSTRAINTFK_JNOFOREIGNKEY(JNO)REFERENCESJ(JNO));2.SELECT*FROMJ;3.SELECT*FROMJWHERECITY='上海';4.SELECTPNOFROMPWHEREWEIGHT=(SELECTMIN(WEIGHT)FROMP);5.SELECTDISTINCTSNOFROMSPJWHEREJNO='J1';6.SELECTSNOFROMSPJWHEREJNO='J1'ANDPNO='P1';7.SELECTDISTINCTJNAMEFROMJ,SPJWHEREJ.JNO=SPJ.JNOANDSPJ.SNO='S1';8.SELECTDISTINCTCOLORFROMP,SPJWHEREP.PNO=SPJ.PNOANDSPJ.SNO='S1;9.SELECTX.SNOFROMSPJXWHEREX.JNO='J1'ANDEXISTS(SELECT*FROMSPJYWHEREY.SNO=X.SNOANDY.JNO='J2');10.SELECTDISTINCTSNOFROMSPJ,PWHERESPJ.JNO='J1'ANDSPJ.PNO=P.PNOANDP.COLOR='红';11.SELECTDISTINCTSNOFROMSPJ,JWHERESPJ.JNO=J.JNOANDJ.CITY='上海';12.SELECTDISTINCTSPJ.SNOFROMSPJ,J,PWHERESPJ.JNO=J.JNOANDSPJ.PNO=P.PNOANDP.COLOR='红'AND(J.CITY='上海'ORJ.CITY='北京');13.SELECTDISTINCTP.PNOFROMS,J,P,SPJWHEREP.PNO=SPJ.PNOANDSPJ.SNO=S.SNOANDSPJ.JNO=J.JNOANDS.CITY=J.CITY;14.SELECTDISTINCTP.PNOFROMS,J,P,SPJWHEREP.PNO=SPJ.PNOANDSPJ.SNO=S.SNOANDSPJ.JNO=J.JNOANDS.CITY='上海'ANDJ.CITY='上海';15.SELECTDISTINCTJNOFROMJWHEREEXISTS(SELECT*FROMS,SPJWHERESPJ.SNO=S.SNOANDJ.JNO=SPJ.JNOANDJ.CITYS.CITY);16.SELECTDISTINCTJNOFROMJWHERENOTEXISTS(SELECT*FROMS,SPJWHEREJ.JNO=SPJ.JNOANDSPJ.SNO=S.SNOANDS.CITY='上海');17.SELECTDISTINCTSNOFROMSPJWHEREPNOIN(SELECTDISTINCTPNOFROMSPJWHERESNOIN(SELECTDISTINCTSNOFROMSPJ,PWHERESPJ.PNO=P.PNOANDP.COLOR='红'));18.SELECTDISTINCTJNOFROMSPJWHERESNO='S1';19.SELECTDISTINCTS.CITY,SPJ.PNO,J.CITYFROMS,J,SPJWHERES.SNO=SPJ.SNOANDJ.JNO=SPJ.JNOANDS.CITYJ.CITY;20.SELECTDISTINCTSNOFROMSWHERENOTEXISTS(SELECT*FROMJWHERENOTEXISTS(SELECT*FROMP,SPJWHERES.SNO=SPJ.SNOANDSPJ.PNO=P.PNOANDJ.JNO=SPJ.JNO));21.SELECTDISTINCTPNOFROMPWHERENOTEXISTS(SELECT*FROMJWHEREJ.CITY='上海'ANDNOTEXISTS(SELECT*FROMSPJWHERESPJ.PNO=P.PNOANDSPJ.JNO=J.JNO));22.SELECTDISTINCTJNOFROMSPJSXWHERENOTEXISTS(SELECT*FROMSPJSYWHERESY.SNO='S1'ANDNOTEXISTS(SELECT*FROMSPJSZWHERESY.PNO=SZ.PNOANDSX.JNO=SZ.JNO));23.UPDATEPSETCOLORE='橙'WHERECOLORE='红';24.DELETEFROMSPJWHERESPJ.PNOIN(SELECTPNOFROMPWHERECOLOR='红');DELETEFROMPWHERECOLOR='红';25.SELECTSUM(QTY)FROMSPJWHERESNO='S1'ANDPNO='P1';26.SELECTJNO,COUNT(DISTINCTSNO)FROMSPJGROUPBYJNO;27.SELECTJNOFROMSPJGROUPBYJNOHAVINGSUM(QTY)1000;1.//创建s表CREATETABLES(SNOCHAR(8)NOTNULLUNIQUE,SNAMEVARCHAR(20)NOTNULLUNIQUE,CITYVARCHAR(8),CONSTRAINTCSPRIMARYKEY(SNO));//创建p表CREATETABLEP(PNOCHAR(8)NOTNULLUNIQUE,PNAMEVARCHAR(20)NOTNULLUNIQUE,COLORCHAR(1),WEIGHTINT,CONSTRAINTCPPRIMARYKEY(PNO));//创建j表CREATETABLEJ(JNOCHAR(8)NOTNULLUNIQUE,JNAMEVARCHAR(20)NOTNULLUNIQUE,CITYVARCHAR(8),CONSTRAINTCJPRIMARYKEY(JNO));//创建spj表CREATETABLESPJ(SNOCHAR(8)NOTNULLNUIQUE,PNOCHAR(8)NOTNULLUNIQUE,JNOCHAR(8)NOTNULLUNIQUE,QTYINT,CONSTRAINTCSPJPRIMARYKEY(SON,PNO,JNO),CONSTRAINTCSPJFOREINGKEY(SNO)REFERENCESS(SNO),CONSTRAINTCSPJFOREINGKEY(PNO)REFERENCESP(PNO),CONSTRAINTCSPJFOREINGKEY(JNO)REFERENCESJ(JNO));2.//向s表插入数据INSERTINTOS(SNO,SNAME,CITY)VALUES(‘S1’,’N1’,’上海’);INSERTINTOS(SNO,SNAME,CITY)VALUES(‘S2’,’N2’,’北京’);INSERTINTOS(SNO,SNAME,CITY)VALUES(‘S3’,’N3’,’北京’);INSERTINTOS(SNO,SNAME,CITY)VALUES(‘S4’,’N4’,’上海’);INSERTINTOS(SNO,SNAME,CITY)VALUES(‘S5’,’N5’,’南京’);//向p表插入数据INSERTINTOP(PNO,PNAME,COLOR,WEIGHT)VALUES(‘P1’,’PN1’,’红’,’12’);INSERTINTOP(PNO,PNAME,COLOR,WEIGHT)VALUES(‘P2’,’PN2’,’绿’,’18’);INSERTINTOP(PNO,PNAME,COLOR,WEIGHT)VALUES(‘P3’,’PN3’,’蓝’,’20’);INSERTINTOP(PNO,PNAME,COLOR,WEIGHT)VALUES(‘P4’,’PN4’,’红’,’13’);INSERTINTOP(PNO,PNAME,COLOR,WEIGHT)VALUES(‘P5’,’PN5’,’蓝’,’11’);INSERTINTOP(PNO,PNAME,COLOR,WEIGHT)VALUES(‘P6’,’PN6’,’红’,’15’);//向j表插入数据INSERTINTOJ(JNO,JNAME,CITY)VALUES(‘J1’,’JN1’,’上海’);INSERTINTOJ(JNO,JNAME,CITY)VALUES(‘J2’,’JN2’,’广州’);INSERT