习题答案.docx
《习题答案.docx》由会员分享，可在线阅读，更多相关《习题答案.docx（9页珍藏版）》请在冰豆网上搜索。
习题答案
第3章：
习题答案
3．用SQL语句建立第2章习题5中的四个表。
设有一个SPJ数据库，包括S，P，J，SPJ四个关系模式：
createtables
（snochar（3）primarykey,
snamechar（10）,
statuschar
（2）,
citychar（10）
）;
createtablep
（pnochar（3）primarykey,
pnamechar（10）,
colorchar（4）,
weightint
）;
createtablej
（jnochar（3）primarykey,
jnamechar（10）,
citychar（10）
）;
注：
S表、P表、J表的主键可直接在列后加PrimaryKey，参见P85
SPJ表的建表语句如下：
createtablespj
（snochar（3）,
pnochar（3）,
jnochar（3）,
qtyint,
primarykey（sno,pno,jno）,
foreignkey（sno）referencess（sno）,
foreignkey（pno）referencesp（pno）,
foreignkey（jno）referencesj（jno）
）;
给表格插入数据：
insertintos
select'S1','精益','20','天津'
union
select'S2','盛锡','10','北京'
union
select'S3','东方红','30','北京'
union
select'S4','丰泰盛','20','天津'
union
select'S5','为民','30','上海'
insertintop
select'P1','螺母','红',12
union
select'P2','螺栓','绿',17
union
select'P3','螺丝刀','蓝',14
union
select'P4','螺丝刀','红',14
union
select'P5','凸轮','蓝',40
union
select'P6','齿轮','红',30
insertintoj
select'J1','三建','北京'
union
select'J2','一汽','长春'
union
select'J3','弹簧厂','天津'
union
select'J4','造船厂','天津'
union
select'J5','机车厂','唐山'
union
select'J6','无线电厂','常州'
union
select'J7','半导体厂','南京'
insertintospj
select'S1','P1','J1',200
union
select'S1','P1','J3',100
union
select'S1','P1','J4',700
union
select'S1','P2','J2',100
union
select'S2','P3','J1',400
union
select'S2','P3','J2',200
union
select'S2','P3','J4',500
union
select'S2','P3','J5',400
union
select'S2','P5','J1',400
union
select'S2','P5','J2',100
union
select'S3','P1','J1',200
union
select'S3','P3','J1',200
union
select'S4','P5','J1',100
union
select'S4','P6','J3',300
union
select'S4','P6','J4',200
union
select'S5','P2','J4',100
union
select'S5','P3','J1',200
union
select'S5','P6','J2',200
union
select'S5','P6','J4',500
4．针对上题中建立的四个表，试用SQL语句完成第2章习题5中的查询。
（1）求供应工程J1零件的供应商号码SNO：
selectdistinctsnofromspj
wherejno='J1'
（2）求供应工程J1零件P1的供应商号码SNO：
selectdistinctsnofromspj
wherejno='J1'andpno='P1'
（3）求供应工程J1零件为红色的供应商号码SNO
使用连接查询
selectsnofromspj,p
where=andjno='J1'andcolor='红'
或者：
用子查询
selectsnofromspj
wherejno='J1'and
pnoin（selectpnofrompwherecolor='红'）
（4）求没有使用天津供应商生产的红色零件的工程号JNO
解：
使用多重嵌套查询
selectjnofromj
wherenotexists
（select*fromspj
where=
andsnoin
（selectsnofromswherecity='天津'）
andpnoin
（selectpnofrompwherecolor='红'）
）
或者：
子查询中使用多表联接，使用exists谓词
selectjnofromjwherenotexists
（select*fromspj,s,p
where=
and=and=
and='天津'and='红'）
或者：
子查询中使用多表联接，使用in谓词
selectjnofromj
wherejnonotin
（selectjnofromspj,s,p
where=and=
and='天津'and='红'）
5．针对习题3中的四个表试用SQL语言完成以下各项操作：
（1）找出所有供应商的姓名和所在城市。
SELECTSNAME,CITYFROMS
（2）找出所有零件的名称、颜色、重量。
SELECTPNAME,COLOR,WEIGHTFROMP
（3）找出使用供应商S1所供应零件的工程号码。
SELECTDISTINCTJNOFROMSPJWHERESNO='S1'
（4）找出工程项目J2使用的各种零件的名称及其数量。
SELECTPNAME,QTYFROMSPJ,P
WHERE=AND='J2'
（5）找出上海厂商供应的所有零件号码。
SELECTdistinctPNOFROMSPJ,SWHERE=ANDCITY='上海'
或者：
SELECTdistinctPNOFROMSPJ
WHEREsnoin（selectsnofromswhereCITY='上海'）
（6）找出使用上海产的零件的工程名称。
SELECTdistinctJNAMEFROMSPJ,S,J
WHERE=AND='上海'AND=
或者：
SELECTJNAMEFROMJ
WHEREjnoin（selectjnofromspj,s
Where=AND='上海'）
（7）找出没有使用天津产的零件的工程号码。
selectdistinctjnofromj
wherejnonotin
（selectdistinctjnofromspj,s
wherecity='天津'and=
注意:
以下这种方法适用于spj表中JNO是唯一的情况:
SELECTdistinctJNOFROMSPJ,SWHERE=AND<>'天津'
结果中有J1，是因为SPJ表中有（S2,P3,J1）满足条件。
而J1显然不是答案，因为有SPJ表中（S1,P1,J1）。
或者：
selectdistinctjnofromj
wherenotexists
（select*fromspj,s
wherecity='天津'and=and=
（8）把全部红色零件的颜色改成蓝色。
UPDATEPSETCOLOR='蓝'WHERECOLOR='红'
（9）由S5供给J4的零件P6改为由S3供应。
UPDATESPJSETSNO='S3'WHERESNO='S5'ANDJNO='J4'ANDPNO='P6'
（10）从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录。
DELETEFROMSPJWHERESNO='S2'
DELETEFROMSWHERESNO='S2'
注意：
删除顺序，应该先删除spj表的记录，再删除s表的记录。
（11）请将（S2，J6，P4，200）插入供应情况关系。
INSERTINTOSPJ（sno,jno,pno,qty）VALUES（'S2','J6','P4',200）
注意：
当into子句指定列名时，插入的属性值顺序要和into子句中的一致。
insertintospjvalues（'S2','P4','J6',200）
注意：
当into子句没有指定列名时，插入的属性值顺序要和表定义中的一致。
11．请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）。
针对该视图VSP完成下列查询：
（1）找出三建工程项目使用的各种零件代码及其数量。
（2）找出供应商S1的供应情况。
解：
CREATEVIEWVSPAS
SELECTSNO,PNO,QTYFROMSPJ,J
WHERE=AND='三建'
或者：
CREATEVIEWVSPAS
SELECTSNO,PNO,QTYFROMSPJ
WHEREJNO=（selectjnofromjwhere='三建'）
针对该视图完成查询：
（1）找出三建工程项目使用的各种零件代码及其数量。
SELECTPNO,sum（QTY）FROMVSPgroupbyPNO
（2）找出供应商S1的供应情况。
SELECTdistinct*FROMVSPWHERESNO='S1'
升级会员 