数据库实验答案Word下载.docx
《数据库实验答案Word下载.docx》由会员分享,可在线阅读,更多相关《数据库实验答案Word下载.docx(14页珍藏版)》请在冰点文库上搜索。
d、左连接:
求j表和spj表的左连接。
法一:
selectj.*,sno,pno,qty
fromj,spj
wherej.jno*=spj.jno
法二:
fromjleftouterjoinspjonj.jno=spj.jno
e、右连接:
求spj表和j表的右连接。
wherespj.jno=*j.jno
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也可以实现:
froms,p,spj
wherej.jno=spj.jnoand
s.sno=spj.snoand
p.pno=spj.pnoand
city='
and
color='
)
♦查询供应了1000个以上零件的供应商名称。
(having)
selectsno,sname
froms
wheresnoin
groupbyspj.sno
havingsum(qty)>
1000)
b、比较运算符:
求重量大于所有零件平均重量的零件名称。
selectpname
fromp
whereweight>
(selectavg(weight)
fromp)
c、Exists连接谓词:
♦查询供应J1的所有的零件都是红色的供应商名称。
即对spj中的每一个供应商做这样的检查:
不存在这样的供应记录,该供应商给J1供应了某种零件,而这种零件不是红色的。
满足这样条件的供应商输出。
(selectdistinctsno
fromspjx
fromspjy
wherey.sno=x.snoandy.jno='
j1'
andnotexists
fromp
wherep.pno=y.pnoandcolor='
)))
♦至少用了供应商S1所供应的全部零件的工程号JNO。
即对spj中的每一个工程做这样的检查:
不存在零件使得供应商s1供应了该零件,而工程j没有选用该零件。
selectdistinctjno
fromspjspjx
fromspjspjy
wheresno='
s1'
andnotexists
fromspjspjz
wherespjz.pno=spjy.pnoand
spjz.jno=spjx.jno))
(3)分组查询:
a、求各种颜色零件的平均重量
selectcolor,AVG(weight)
groupbycolor
b、求北京供应商和天津供应商的总个数
selectcity,count(*)asnumber
wherecity='
北京'
orcity='
groupbycity
c、求各供应商供应的零件总数
selectsno,sum(qty)
fromspj
groupbysno
d、求各供应商供应给各工程的零件总数
selectsno,jno,sum(qty)asqty
groupbysno,jno
e、求使用了100个以上P1零件的工程名称。
selectjno,sum(qty)asqty
wherepno='
p1'
groupbyjno
100
wherejnoin
(selectjno
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
b、查询S1供应商的所有供应明细。
createviews1_spj
as
selectsno,pno,jno,qty
c、查询各工程名称使用的各种颜色零件的个数。
createviewjno_pcolor_qty(jno,color,qty)
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)
selectjname,color,qty
fromj,jno_pcolor_qty
wherej.jno=jno_pcolor_qty.jno
(2)查询上面定义的视图。
思考:
上述哪些视图可以用来更新记录?
select*
froms_bj
froms1_spj
fromjname_pcolor_qty
实验五:
触发器
为S表的删除操作定义一个触发器,在删除一个供应商记录时,将这个供应商的所有供应情况从spj表中删除。
如何通过系统的设置实现类似的功能,而不需触发器?
createtriggert_delSSpjons
fordelete
begin
delete
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