祝锡永数据库第七章习题答案.docx
- 文档编号:2466635
- 上传时间:2022-10-29
- 格式:DOCX
- 页数:13
- 大小:17.35KB
祝锡永数据库第七章习题答案.docx
《祝锡永数据库第七章习题答案.docx》由会员分享,可在线阅读,更多相关《祝锡永数据库第七章习题答案.docx(13页珍藏版)》请在冰豆网上搜索。
祝锡永数据库第七章习题答案
/*第七章习题*/
/*1*/
dropprocedurep1
go
createprocedurep1@cnamevarchar(50)
as
;withtmpas(
selectb.categoryid,categoryname,SUM(amount)as'amt'fromOrderItemsa
joinProductsbona.ProductID=b.ProductID
joinCategoriesconb.CategoryID=c.CategoryID
groupbyb.CategoryID,CategoryName)
selectamtfromtmpwherecategoryname=@cname
go
executep1'Confections'
/*2*/
dropprocedurep2
go
createprocedurep2@pnamevarchar(80)
as
;withtmpas(
selecta.productid,productname,RANK()over(orderbysum(amount))as'rankid'fromOrderItemsa
joinProductsbona.ProductID=b.ProductID
groupbya.ProductID,productname)
selectrankidfromtmpwhereProductName=@pname
go
executep2'Tofu'
/*3*/
dropprocedurep3
go
createprocedurep3@tablenamevarchar(40),@cnamevarchar(40)
as
declare@sqlvarchar(2000)
set@sql='ifnotexists(selectdata_type,character_maximum_lengthfromINFORMATION_SCHEMA.COLUMNSwhereTABLE_NAME='''
set@sql=@sql+@tablename+'''andCOLUMN_NAME='''
set@sql=@sql+@cname+''')'+CHAR(13)
set@sql=@sql+char(9)+'print'''+'*'+''''+CHAR(13)
set@sql=@sql+'else'+CHAR(13)
set@sql=@sql+char(9)+'selectdata_type,character_maximum_lengthfrominformation_schema.columnswheretable_name='''
set@sql=@sql+@tablename+'''andCOLUMN_NAME='''
set@sql=@sql+@cname+''''
execute(@sql)
go
executep3'Products','ProductName'
/*4*/
dropfunctionf1
go
createfunctionf1(@cnamevarchar(40),@yearint)
returns@t1table(cnamevarchar(40),monthint,numint,amtmoney)
as
begin
;withtmpas(
selectcompanyname,month(orderdate)as'xmonth',count(*)as'num',sum(amount)as'amt'fromorderitemsa
joinordersbona.orderid=b.orderid
joincustomersconb.customerid=c.customerid
whereyear(orderdate)=@yearandcompanyname=@cname
groupbycompanyname,month(orderdate))
insertinto@t1(cname,month,num,amt)
selectcompanyname,xmonth,num,amtfromtmp
return
end
go
select*from.dbo.f1('AlfredsFutterkiste',2009)
/*5*/
dropfunctionf2
go
createfunctionf2(@datedatetime)
returns@t1table(orderidint,customeridvarchar(10),employeeidvarchar(10),orderdatedatetime,requireddatedatetime,invoivedatedatetime,shippeddatedatetime,shipperidint,freightdecimal(12,2))
as
begin
insertinto@t1
select*fromorderswhereinvoicedate<=@dateandshippeddate>@date
return
end
go
select*from.dbo.f2(2008-12-30)
/*6*/
dropfunctionf3
go
createfunctionf3(@cidvarchar(10),@pidint)
returnsint
as
begin
declare@nint
select@n=numfrom(selectcustomerid,count(*)as'num'fromorderitemsa
joinordersbona.orderid=b.orderidwherecustomerid=@cidandproductid=@pid
groupbycustomerid)asp
return@n
end
go
selectcustomeridfromcustomers
wheremySales.dbo.f3(CustomerID,12)>0
selectproductidfromProductswhere.dbo.f3('ANTON',productid)>0
/*7*/
dropfunctionf4
go
createfunctionf4(@date1datetime,@date2datetime)
returns@ttable(productidint,productnamevarchar(100),quantityvarchar(40),unitpricemoney,supplieridint,categoryidint)
as
begin
;withtmpas(
selectproductid,sum(amount)as'amt'fromorderitemsa
joinordersbona.orderid=b.orderid
whereorderdatebetween@date1and@date2
groupbyproductid)
insertinto@t
selectproductid,productname,quantityperunit,unitprice,supplierid,categoryidfromproducts
whereproductidin(selecttop10percentproductidfromtmporderbyamtdesc)
return
end
go
selectdistinctcustomeridfromorderswhereorderidin(selectOrderIDfromOrderItemswhereProductIDin(selectProductIDfrom.dbo.f4('2009-1-1','2009-6-30')))
/*8*/
dropfunctionf5
go
createfunctionf5(@pricemoney)
returnsvarchar(20)
as
begin
declare@svarchar(20)
if(@price>=0.01and@price<=10)
set@s='inexpensive'
elseif(@price>=10.01and@price<=20)
set@s='moderate'
elseif(@price>=20.01and@price<=30)
set@s='semi-expensive'
elseif(@price>=30.01and@price<=50)
set@s='expensive'
else
set@s='veryexpensive'
return@s
end
go
dropfunctionf6
go
createfunctionf6(@sidint)
returns@ttable(productidint,pricerangevarchar(30))
as
begin
insertinto@t
selectproductid,mysales.dbo.f5(unitprice)fromproductswheresupplierid=@sid
return
end
go
select*from.dbo.f6
(2)
/*9*/
droptablemycustomers
go
droptablemyorderitems
go
select*intomycustomersfromCustomers
select*intomyorderitemsfromOrderItems
altertablemycustomers
addamountmoney
updatemycustomerssetamount=(selectSUM(amount)fromOrderItemsajoinOrdersbona.OrderID=b.OrderIDwhere
b.CustomerID=mycustomers.customerid)
droptriggert1
go
createtriggert1onmyorderitemsforupdateas
begin
updatemycustomerssetamount=amount-(selectsum(amount)fromdeletedajoinordersbona.orderid=b.orderid
whereb.customerid=mycustomers.id)
updatemycustomersseta
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 祝锡永 数据库 第七 习题 答案