祝锡永数据库第七章习题答案Word格式文档下载.docx
《祝锡永数据库第七章习题答案Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《祝锡永数据库第七章习题答案Word格式文档下载.docx(13页珍藏版)》请在冰豆网上搜索。
selectamtfromtmpwherecategoryname=@cname
executep1'
Confections'
/*2*/
dropprocedurep2
createprocedurep2@pnamevarchar(80)
as
withtmpas(
selecta.productid,productname,RANK()over(orderbysum(amount))as'
rankid'
groupbya.ProductID,productname)
selectrankidfromtmpwhereProductName=@pname
executep2'
Tofu'
/*3*/
dropprocedurep3
createprocedurep3@tablenamevarchar(40),@cnamevarchar(40)
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'
+'
*'
set@sql=@sql+'
else'
selectdata_type,character_maximum_lengthfrominformation_schema.columnswheretable_name='
execute(@sql)
executep3'
Products'
'
ProductName'
/*4*/
dropfunctionf1
createfunctionf1(@cnamevarchar(40),@yearint)
returns@t1table(cnamevarchar(40),monthint,numint,amtmoney)
begin
selectcompanyname,month(orderdate)as'
xmonth'
count(*)as'
num'
sum(amount)as'
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
select*from.dbo.f1('
AlfredsFutterkiste'
2009)
/*5*/
dropfunctionf2
createfunctionf2(@datedatetime)
returns@t1table(orderidint,customeridvarchar(10),employeeidvarchar(10),orderdatedatetime,requireddatedatetime,invoivedatedatetime,shippeddatedatetime,shipperidint,freightdecimal(12,2))
begin
insertinto@t1
select*fromorderswhereinvoicedate<
=@dateandshippeddate>
@date
select*from.dbo.f2(2008-12-30)
/*6*/
dropfunctionf3
createfunctionf3(@cidvarchar(10),@pidint)
returnsint
declare@nint
select@n=numfrom(selectcustomerid,count(*)as'
fromorderitemsa
joinordersbona.orderid=b.orderidwherecustomerid=@cidandproductid=@pid
groupbycustomerid)asp
return@n
selectcustomeridfromcustomers
wheremySales.dbo.f3(CustomerID,12)>
selectproductidfromProductswhere.dbo.f3('
ANTON'
productid)>
/*7*/
dropfunctionf4
createfunctionf4(@date1datetime,@date2datetime)
returns@ttable(productidint,productnamevarchar(100),quantityvarchar(40),unitpricemoney,supplieridint,categoryidint)
selectproductid,sum(amount)as'
whereorderdatebetween@date1and@date2
groupbyproductid)
insertinto@t
selectproductid,productname,quantityperunit,unitprice,supplierid,categoryidfromproducts
whereproductidin(selecttop10percentproductidfromtmporderbyamtdesc)
selectdistinctcustomeridfromorderswhereorderidin(selectOrderIDfromOrderItemswhereProductIDin(selectProductIDfrom.dbo.f4('
2009-1-1'
2009-6-30'
)))
/*8*/
dropfunctionf5
createfunctionf5(@pricemoney)
returnsvarchar(20)
declare@svarchar(20)
if(@price>
=0.01and@price<
=10)
set@s='
inexpensive'
elseif(@price>
=10.01and@price<
=20)
moderate'
=20.01and@price<
=30)
semi-expensive'
=30.01and@price<
=50)
expensive'
else
veryexpensive'
return@s
dropfunctionf6
createfunctionf6(@sidint)
returns@ttable(productidint,pricerangevarchar(30))
insertinto@t
selectproductid,mysales.dbo.f5(unitprice)fromproductswheresupplierid=@sid
return
select*from.dbo.f6
(2)
/*9*/
droptablemycustomers
droptablemyorderitems
select*intomycustomersfromCustomers
select*intomyorderitemsfromOrderItems
altertablemycustomers
addamountmoney
updatemycustomerssetamount=(selectSUM(amount)fromOrderItemsajoinOrdersbona.OrderID=b.OrderIDwhere
b.CustomerID=mycustomers.customerid)
droptriggert1
createtriggert1onmyorderitemsforupdateas
updatemycustomerssetamount=amount-(selectsum(amount)fromdeletedajoinordersbona.orderid=b.orderid
whereb.customerid=mycustomers.id)
updatemycustomersseta