SQL习题及答案.docx
《SQL习题及答案.docx》由会员分享，可在线阅读，更多相关《SQL习题及答案.docx（15页珍藏版）》请在冰豆网上搜索。
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;
升级会员 