数据库A2013期末卷

12012-2013-05075386356-浙江理工大学2013—2013学年第2学期《数据库原理与应用》期末试卷（A）卷本人郑重承诺：本人已阅读并且透彻地理解《浙江理工大学考场规则》，愿意在考试中自觉遵守这些规定，保证按规定的程序和要求参加考试，如有违反，自愿按《浙江理工大学学生违纪处分规定》有关条款接受处理。承诺人签名：学号：班级：一、程序阅读题40%二、程序设计题60%附加题总分123456712345678一、程序阅读题（本题共40分）已知数据表Test1和Test2的定义如下。其数据分别如表1和表2所示（表中未列出的数据需要自己计算得出），写出下列各个小题中SELECT语句的输出结果。CREATETABLEtest1(ProductIDchar(2)Primarykey,Productnamevarchar(20),Categorychar(1),Pricemoney,RowIDintidentity(20,2))GOCREATETABLEtest2(OrderIDchar(4),OrderDatedatetime,Qtyintdefault(0),ProductIDchar(2),PrimaryKey(OrderID,ProductIDDESC))GOCREATEVIEWview1ASSELECTa.ProductID,Productname,OrderID,OrderDate,Convert(varchar(10),OrderDate,120)as'OrderDay',Qty*Priceas'Amt'FROMtest2aJOINtest1bona.ProductID=b.ProductIDGO22012-2013-05075386356---第1小题(6分)CREATEPROCEDUREpro1@strnvarchar(20)ASSELECTProductID,ProductName,OrderID,OrderDay,AmtFROMview1WHEREProductnamelike@strandDATEDIFF(month,OrderDate,GETDATE())=6GOExecutePro1'%[^ns]o%'GO--第2小题(6分);WITHtmpAS(SELECTCategory,COUNT(*)asNumberFROMtest1GROUPBYCategoryWITHRollup)SELECTCASECategoryWHEN'R'THEN'Land'WHEN'T'THEN'Fruit'WHEN'M'THEN'Wave'ELSE'*S*'ENDas'Category',NumberFROMtmpGO32012-2013-05075386356---第3小题(6分)DECLARE@tTABLE(Pidchar(2),Qtyint)INSERTINTO@t(Pid,Qty)SELECTProductID,SUM(qty)FROMtest2GROUPBYProductIDHAVINGSUM(qty)15SELECTa.Pid,b.Productnameas'Pname',QtyFROM@ta,test1bWHEREb.ProductID=a.PidGO--第4小题(6分)ALTERTABLEtest1AddnewColintGOUPDATEtest1setnewCol=(SELECTSUM(qty)fromtest2WHEREProductID=test1.ProductID)SELECT*FROMtest1WHEREnewCol(SELECTAVG(newCol)FROMtest1)GO42012-2013-05075386356----第5小题(6分)SELECTOrderID,OrderDay,Productname,Amt,0asFlag1,0asFlag2INTOtest3FROMview1UNIONALLSELECT'','*S*',Productname,SUM(Amt),0,1FROMview1GROUPBYProductnameWITHROLLUPGOUPDATEtest3SETOrderday='*T*',Productname='',Flag1=1WHEREProductnameISNULLSELECTOrderID,Orderday,Productname,AmtFROMtest3WHEREProductname'ON'ORProductname=''ORDERBYFlag1,Productname,Flag2GO--第6小题(6分)CREATEFUNCTIONfn1(@v1nvarchar(10))RETURNSvarchar(20)ASBEGINDECLARE@svarchar(20),@nint,@s1varchar(30)SELECT@n=count(*)fromtest2whereProductID=@v1SELECT@s1=ProductnameFROMtest1whereProductID=@v1IF@n0BEGINIF@n1set@s=@s1+Space(1)+LTRIM(STR(@n,6))+'ts'elseset@s=@s1+Space(1)+'1t'ENDELSEIF@n=0set@s=@s1+space(1)+'*'RETURN(@s)ENDGOSELECTProductID,dbo.fn1(ProductID)asFrequencyFROMtest1ORDERBYProductID52012-2013-05075386356---第7小题(4分)ALTERTABLEtest2ADDnewColintGODECLAREc1cursorFORSELECTproductid,qtyFROMtest2ORDERBYProductID,OrderDateOPENc1DECLARE@qtyint,@qty1int,@idnvarchar(2),@id1nvarchar(2),@pricemoneyFetchNextFROMc1into@id1,@qty1Set@id=''Set@qty=0WHILE@@fetch_status=0BEGINIF@id@id1beginSet@id=@id1Set@qty=@qty1endELSESet@qty=@qty+@qty1UPDATEtest2SetnewCol=@qtyWHERECURRENTofc1FetchNextFROMc1into@id1,@qty1ENDDEALLOCATEc1SELECTProductID,OrderID,Convert(varchar(10),OrderDate,120)as'Orderdate',Qty,newColFROMtest2WHEREProductIDlike'C[36]'ORDERBYProductID,OrderDateGO62012-2013-05075386356-72012-2013-05075386356-表2.Test1表数据表3.Test2表数据ProductIDProductNameCategoryPriceOrderIDOrderDateQtyProductIDW1OkraR6.0015642012-08-1200:00:00.0007W1W2PepperR7.0018922012-09-2200:00:00.0008W2W3SoybeanT8.0028352012-10-1200:00:00.0009W3W4PotatoT9.0028352012-10-1200:00:00.0008W4W5MelonT10.0032122012-11-2200:00:00.0007W5W6TaroM9.0038512012-12-2200:00:00.0005W5W7LeekM8.0038512012-12-2200:00:00.0006W6W8CeleryM7.0039422013-01-2500:00:00.0007W339422013-01-2500:00:00.0006W442332013-02-2300:00:00.0008W142332013-02-2300:00:00.0009W348262013-03-0600:00:00.0007W548262013-03-0600:00:00.0005W648262013-03-0600:00:00.0006W855692013-03-1200:00:00.0008W2二、程序设计题（本题共60分）数据库mySales用来存放某企业产品销售数据，Products表存放产品信息，Categories表存放产品类别，Customers表存放客户信息，Orders表存放订单信息，OrderItems表存放订单明细信息，其表结构见表3~表7。试编写T-SQL语句，完成以下各项功能（注意必须按试卷中规定的列名答题，不能篡改列名，否则答题无效。临时表可自行定义）：82012-2013-05075386356-表3.Products表结构列名类型长度规则中文说明PF1整型4Identity列，主键产品编码PF2字符型50非空产品名称PF3整型4非空产品类别PF4字符型20非空规格型号PF5数值型8,2成本单价表4.Categories表结构列名类型长度规则中文说明DF1整型4主键类别编码DF2字符型50非空类别名称DF3字符型255类别描述表5.Customers表结构列名类型长度规则中文说明KF1字符型5主键客户编码KF2字符型60非空客户名称KF3字符型60客户地址KF4字符型20所在地区KF5字符型20所在国家表6.Orders表结构列名类型长度规则中文说明SF1整型4Identity列，主键订单编号SF2字符型5非空，外键客户编码SF3日期型8非空订单日期SF4日期型8非空要货日期SF5字符型20运输方式SF6数值型8,2运输费用表7.OrderItems表结构列名类型长度规则中文说明WF1整型4外键订单编号WF2整型4外键产品编码WF3数值型8,2两位小数，单价大于0销售单价WF4数值型8非空，默认为0销售数量WF5数值型6,2大于等于0，小于1。0表示不打折。折扣率WF6数值型10,2计算列。由销售量、单价和折扣率自动计算得到，保留两位小数。销售金额92012-2013-05075386356-1．根据OrderItems表的结构和各个约束条件，编写创建该表所需的T-SQL语句，并建立由订单编号和产品编号组成的组合主键。（8分）2．已知某数据检索的关系代数表达式如下，写出与其对应的SQL语句。（6分）Π2(Customers)—Π2(σ2=’tofu’(Products)OrderItemsσ3=’2009-01-01’^3=’2009-12-31’(Orders)Customers)102012-2013-05075386356-3．已知SQL查询语句如下，写出与其对应的关系代数表达式。（6分）SELECTSF1,SF2,SF3FROMOrdersWHERESF1IN(SELECTa.WF1FROMOrderItemsASaJOINOrderItemsASbONa.WF1=b.WF1anda.WF2b.WF2)andYear(SF2)=2010andMonth(SF2)=94．根据各表数据，统计名称为B'sBeverages的这个客户2008年度购买哪些产品的次数最多，列出这些产品的名称。（8分，提示：可使用子查询）112012-2013-05075386356-5.编写一个存储过程，输入一个产品名称，输出该产品的销售额在它所属同类产品销售额中的排名名次及所占的百分比（要求保留两位小数）。（7分，提示：可使用排名函数）6．创建一个用户定义函数，输入一个1~100的整数n，输出返回销售额最大的前n%个客户的名称及其销售额。（8分，提示可使用ceiling函数）122012-2013-05075