SQL习题及答案讲解.docx
《SQL习题及答案讲解.docx》由会员分享，可在线阅读，更多相关《SQL习题及答案讲解.docx（11页珍藏版）》请在冰豆网上搜索。
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,SPJWHERE
P.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.CITY<>S.CITY）;
16.
SELECTDISTINCTJNOFROMJWHERENOTEXISTS
（SELECT*FROMS,SPJWHEREJ.JNO=SPJ.JNOANDSPJ.SNO=S.SNOANDS.CITY='上海'）;
17.
SELECTDISTINCTSNOFROMSPJWHEREPNOIN
（SELECTDISTINCTPNOFROMSPJWHERESNOIN
（SELECTDISTINCTSNOFROMSPJ,P
WHERESPJ.PNO=P.PNOANDP.COLOR='红'））;
18.
SELECTDISTINCTJNOFROMSPJWHERESNO='S1';
19.
SELECTDISTINCTS.CITY,SPJ.PNO,J.CITYFROMS,J,SPJWHERES.SNO=SPJ.SNOANDJ.JNO=SPJ.JNOANDS.CITY<>J.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’,’广州’）;
INSERTINTOJ（JNO,JNAME,CITY）VALUES（‘J3’,’JN3’,’南京’）;
INSERTINTOJ（JNO,JNAME,CITY）VALUES（‘J4’,’JN4’,’南京’）;
INSERTINTOJ（JNO,JNAME,CITY）VALUES（‘J5’,’JN5’,’上海’）;
INSERTINTOJ（JNO,JNAME,CITY）VALUES（‘J6’,’JN6’,’武汉’）;
INSERTINTOJ（JNO,JNAME,CITY）VALUES（‘J7’,’JN7’,’上海’）;
//向表spj中插入数据
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S1’,’P1’,’J1’,’200’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S1’,’P1’,’J4’,’700’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S2’,’P3’,’J1’,’400’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S2’,’P3’,’J2’,’200’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S2’,’P3’,’J3’,’200’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S2’,’P3’,’J4’,’500’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S2’,’P3’,’J5’,’600’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S2’,’P3’,’J6’,’400’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S2’,’P3’,’J7’,’800’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S2’,’P5’,’J2’,’100’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S3’,’P3’,’J1’,’200’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S3’,’P4’,’J2’,’500’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S4’,’P6’,’J7’,’300’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S5’,’P2’,’J2’,’200’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S5’,’P2’,’J4’,’100’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S5’,’P5’,’J5’,’500’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S5’,’P5’,’J7’,’100’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S5’,’P6’,’J2’,’200’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S5’,’P1’,’J4’,’1000’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S5’,’P3’,’J4’,’1200’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S5’,’P4’,’J4’,’800’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S5’,’P5’,’J4’,’400’）;
INSERTINTOSPJ（SNO,PNO,JNO,QTY）VALUES（‘S5’,’P6’,’J4’,’500’）;
3.1
SELECT*FROMJ;
3.2
SELECT*FROMJWHERECITYLIKE‘上海’;
3.3
SELECTPNOFROMPWHEREWEIGHT=（SELECTMAX（WEIGHT）FROMP）;
3.4
SELECTSNOFROMSPJWHEREJNOIN（SELECTJNOFROMJWHEREJNO=‘J1’）;
3.5
SELECTSNOFROMSPJWHEREJNO=‘J1’ANDPNO=‘P1’;
3.6
SELECTJNAMEFROMJ,SPJWHEREJ.JNO=SPJ.JNOANDSPJ.SNO=‘S1’;
3.7
SELECTCOLORFROMP,SPJWHEREP.PNO=SPJ.PNOANDSPJ.SNO=‘S1’;
3.8
SELECTDISTINCTSNOFROMSPJWHEREJNO=‘J1’ORJNO=‘J2’;
3.9
SELECTSNOFROMSPJ,PWHEREP.PNO=SPJ.PNOANDSPJ.JNO=‘J1’ANDP.COLOR=‘红’’
3.10
SELECTSNOFROMSPJ,JWHEREJ.JNO=SPJ.JNOANDJ.CITY=‘上海’;
3.11
SELECTSNOFROMSPJ,J,P
WHERESPJ.PNO=P.PNOANDJ.JNO=SPJ.JNOANDP.COLOR=‘红’;
3.12
SELECTSPJ.PNOFROMSPJ,S,J
WHERES.SNO=SPJ.SNOANDJ.JNO=SPJ.JNOANDS.CITY=J.CITY;
3.13
SELECTSPJ.PNOFROMSPJ,S,J
WHERES.SNO=SPJ.SNOANDJ,JNO=SPJ.JNOANDS.CITY=‘上海’ANDJ.CITY=‘上海’;
3.14
SELECTSPJ.JNOFROMSPJ,S,J
WHERES.SNO=SPJ.SNOANDJ.JNO=SPJ.JNOANDS.CITY<>J.CITY;
3.15
SELECTJNOFROMSPJWHEREJNONOTIN
（SELECTSPJ.JNOFROMS,SPJWHERES.SNO=SPJ.SNOANDS.CITY=‘上海’）;
3.16
SELECTSPJ.SNOFROMSPJ,PWHERESPJ.PNOIN
（SELECTSPJ.PNOFROMSPJ,S,P
WHERES.SNO=SPJ.SNOANDP.PNO=SPJ.PNOANDP.COLOR=‘红’）;
3.17
SELECTSPJ.JNOFROMS,P,SPJ
WHERES.SNO=SPJ.SNOANDJ.JNO=SPJ.JNO;
3.18
SELECTS.CITY,J.CITYFROMS,J,SPJ
WHERES.SNO=SPJ.SNOANDJ.JNO=SPJ.JNO;
3.19
SELECTS.CITY,SPJ.PNO,J.CITYFROMS,J,SPJ
WHERES,=SPJ.SNOANDJ.JNO=SPJ.JNO;
3.20
SELECTS.CITY,SPJ.PNO,J.CITYFROMSPJ,S,J
WHERES.SNO=SPJ.SNOANDJ.JNO=SPJ.JNOANDS.CITY<>J.CITY;
3.21
SELECTJNOFROMSPJASSPJ1
WHERENOTEXISTS（
SELECT1FROMSPJASSPJ2
WHERESNO=‘S1’ANDNOTEXISTS
（SELECT1FROMSPJASSPJ3
WHERESPJ3.PNO=SPJ2.PNOANDSPJ3.JNO=SPJ1.JNO））;
3.22
UPDATEPSETCOLOR=‘红’WHERECOLOR=‘橙’;
3.23
DELETEFROMSPJ,P
WHERESPJ.PNO=P.PNOANDP.COLOR=‘红’;
DELETEFROMPWHERECOLOR=‘红’;
3.24
SELECTSPJ.SNO,SPJ,PNO,SUM（QTY）FROMSPJ
WHERESPJ.SNO=‘S1’ANDSPJ.PNO=‘P1’GROUPBYSPJ.SNO,SPJ,PNO;
3.25
SELECTSPJ.SNO,COUNT（*）FROMSPJGROUPBYSPJ.SNO;
3.26
SELECTJNOFROMSPJGROUPBYSNOHAVINGSUM（QTY）>1000;
升级会员 