数据库实验答案.docx
《数据库实验答案.docx》由会员分享，可在线阅读，更多相关《数据库实验答案.docx（14页珍藏版）》请在冰点文库上搜索。
数据库实验答案
实验三：
复杂查询
（1）连接查询
a等值连接：
求s表和j表的相同城市的等值连接。
selects.*,j.*
froms,j
wheres.city=j.city
b自然连接：
查询所有的供应明细，要求显示供应商、零件和工程的名称，
并按照供应、工程、零件排序。
selectsname,jname,pname
froms,p,j,spj
wheres.sno=spj.snoandp.pno=spj.pnoandj.jno=spj.jno
orderbys.sno,j.jno,p.pno
c、笛卡尔积：
求s和p表的笛卡尔积
selects.*,p.*
froms,p
d、左连接：
求j表和spj表的左连接。
法一：
selectj.*,sno,pno,qty
fromj,spj
wherej.jno*=spj.jno
法二：
selectj.*,sno,pno,qty
fromjleftouterjoinspjonj.jno=spj.jno
e、右连接：
求spj表和j表的右连接。
法一：
selectj.*,sno,pno,qty
fromj,spj
wherespj.jno=*j.jno
法二：
selectj.*,sno,pno,qty
fromspjrightouterjoinjonspj.jno=j.jno
f、自连接：
s表的自连接
selects1.sno,s2.sname,s2.city
fromss1,ss2
wheres1.sno=s2.snoands2.city='天津'
（2）嵌套查询：
a、in连接谓词查询：
♦查询没有使用天津供应商供应的红色零件的工程名称。
用关系演算ALPHA语言表达的语句如下：
RANGESPJSPJX
SSX
PPX
GETW（J.JNO）:
¬SPJX（SPJX..JNO=J.JNO
SX（SX.SNO=SPJX.SNOSX.CITY=’天津’）
PX（PX.PNO=SPJX.PNOPX.COLOR=’红’））
对工程表J中的每一个JNO进行如下判断：
检查SPJ中是否存在这样的元组，其JNO=J.JNO，并且所用的零件是天津供应商供应的，且是红色零件。
如果SPJ中不存在这样的元组，则该工程号JNO满足条件，放入结果表中；如果SPJ中存在这样的元组，则该工程号JNO不满足条件，不放入结果表中。
检查工程表中下一个JNO，直到所有JNO检查完毕。
可以将ALPHA语言转化为相应的SQL语句：
selectjno,jname
fromj
wherenotexists
（select*
fromspj
wherespj.jno=j.jnoandsnoin
（selectsno
froms
wherecity='天津'）andpnoin
（selectpno
fromp
wherecolor='红'））
另一种解法：
不用in也可以实现：
selectjno,jname
fromj
wherenotexists
（select*
froms,p,spj
wherej.jno=spj.jnoand
s.sno=spj.snoand
p.pno=spj.pnoand
city='天津'and
color='红'）
♦查询供应了1000个以上零件的供应商名称。
（having）
selectsno,sname
froms
wheresnoin
（selectsno
fromspj
groupbyspj.sno
havingsum（qty）>1000）
b、比较运算符：
求重量大于所有零件平均重量的零件名称。
selectpname
fromp
whereweight>
（selectavg（weight）
fromp）
c、Exists连接谓词：
♦查询供应J1的所有的零件都是红色的供应商名称。
即对spj中的每一个供应商做这样的检查：
不存在这样的供应记录，该供应商给J1供应了某种零件，而这种零件不是红色的。
满足这样条件的供应商输出。
selectsno,sname
froms
wheresnoin
（selectdistinctsno
fromspjx
wherenotexists
（select*
fromspjy
wherey.sno=x.snoandy.jno='j1'andnotexists
（select*
fromp
wherep.pno=y.pnoandcolor='红'）））
♦至少用了供应商S1所供应的全部零件的工程号JNO。
即对spj中的每一个工程做这样的检查：
不存在零件使得供应商s1供应了该零件，而工程j没有选用该零件。
selectdistinctjno
fromspjspjx
wherenotexists
（select*
fromspjspjy
wheresno='s1'andnotexists
（select*
fromspjspjz
wherespjz.pno=spjy.pnoand
spjz.jno=spjx.jno））
（3）分组查询：
a、求各种颜色零件的平均重量
selectcolor,AVG（weight）
fromp
groupbycolor
b、求北京供应商和天津供应商的总个数
selectcity,count（*）asnumber
froms
wherecity='北京'orcity='天津'
groupbycity
c、求各供应商供应的零件总数
selectsno,sum（qty）
fromspj
groupbysno
d、求各供应商供应给各工程的零件总数
selectsno,jno,sum（qty）asqty
fromspj
groupbysno,jno
e、求使用了100个以上P1零件的工程名称。
selectjno,sum（qty）asqty
fromspj
wherepno='p1'
groupbyjno
havingsum（qty）>100
selectjno,jname
fromj
wherejnoin
（selectjno
fromspj
wherepno='p1'
groupbyjno
havingsum（qty）>100）
f、求各工程使用的各城市供应的零件总数。
selectspj.jno,s.city,sum（qty）
fromspj,s
wheres.sno=spj.sno
groupbyspj.jno,s.city
实验四：
视图的定义与使用
（1）定义如下视图：
a、查询北京的供应商的编号、名称和城市。
createviews_bj（sno,sname,city）
as（selectsno,sname,city
froms
wherecity='北京'）
b、查询S1供应商的所有供应明细。
createviews1_spj
as
selectsno,pno,jno,qty
fromspj
wheresno='s1'
c、查询各工程名称使用的各种颜色零件的个数。
createviewjno_pcolor_qty（jno,color,qty）
as
selectspj.jno,color,sum（qty）
fromp,j,spj
wherep.pno=spj.pnoand
j.jno=spj.jno
groupbyspj.jno,color
createviewjname_pcolor_qty（jname,color,qty）
as
selectjname,color,qty
fromj,jno_pcolor_qty
wherej.jno=jno_pcolor_qty.jno
（2）查询上面定义的视图。
思考：
上述哪些视图可以用来更新记录？
select*
froms_bj
select*
froms1_spj
select*
fromjname_pcolor_qty
实验五：
触发器
为S表的删除操作定义一个触发器，在删除一个供应商记录时，将这个供应商的所有供应情况从spj表中删除。
思考：
如何通过系统的设置实现类似的功能，而不需触发器？
createtriggert_delSSpjons
fordelete
as
begin
delete
fromspj
wherespj.sno=（selectsnofromdeleted）
end
思考：
通过设置外键约束，并设置级联删除。
实验六：
编写一个存储过程，定义四个参数接收供应商的四个属性的值，然后插入S表中。
创建：
createprocp_insertS
@p_snochar（3）,
@p_snamechar（10）,
@p_statusint,
@p_citychar（10）
asinsertintos（sno,sname,status,city）
values（@p_sno,@p_sname,@p_status,@p_city）
执行：
execp_insertS's10','耐火厂',20,'洛阳'
删除：
dropprocp_insertS
升级会员 