数据库A2013期末卷

1、精选优质文档-倾情为你奉上浙江理工大学20132013学年第2学期数据库原理与应用期末试卷（A）卷本人郑重承诺：本人已阅读并且透彻地理解浙江理工大学考场规则，愿意在考试中自觉遵守这些规定，保证按规定的程序和要求参加考试，如有违反，自愿按浙江理工大学学生违纪处分规定有关条款接受处理。 承诺人签名： 学号： 班级： 一、程序阅读题40%二、程序设计题60%附加题总分123456712345678 一、程序阅读题（本题共40分）已知数据表Test1和Test2的定义如下。其数据分别如表1和表2所示（表中未列出的数据需要自己计算得出），写出下列各个小题中SELECT语句的输出结果。CREATE TAB

2、LE test1 ( ProductID char(2) Primary key, Productname varchar(20), Category char(1), Price money, RowID int identity(20,2) )GOCREATE TABLE test2 ( OrderID char(4), OrderDate datetime, Qty int default(0), ProductID char(2), Primary Key(OrderID, ProductID DESC) )GOCREATE VIEW view1 AS SELECT a.Product

3、ID,Productname,OrderID,OrderDate,Convert(varchar(10),OrderDate,120) as 'OrderDay',Qty*Price as 'Amt' FROM test2 a JOIN test1 b on a.ProductID=b.ProductIDGO-第1小题(6分)CREATE PROCEDURE pro1 str nvarchar(20) AS SELECT ProductID,ProductName,OrderID,OrderDay,Amt FROM view1 WHERE Productname

4、 like str and DATEDIFF(month,OrderDate,GETDATE()<=6GOExecute Pro1 '%nso%'GO-第2小题(6分); WITH tmp AS(SELECT Category,COUNT(*) as Number FROM test1 GROUP BY Category WITH Rollup)SELECT CASE CategoryWHEN 'R' THEN 'Land'WHEN 'T' THEN 'Fruit'WHEN 'M' THEN

5、'Wave'ELSE '*S*' END as 'Category',Number FROM tmpGO-第3小题(6分)DECLARE t TABLE (Pid char(2),Qty int)INSERT INTO t (Pid,Qty) SELECT ProductID,SUM(qty) FROM test2GROUP BY ProductID HAVING SUM(qty)>15SELECT a.Pid,b.Productname as 'Pname',Qty FROM t a,test1 bWHERE b.Prod

6、uctID=a.PidGO-第4小题(6分) ALTER TABLE test1 Add newCol intGOUPDATE test1 set newCol=(SELECT SUM(qty) from test2 WHERE ProductID=test1.ProductID )SELECT * FROM test1 WHERE newCol<(SELECT AVG(newCol) FROM test1)GO-第5小题(6分) SELECT OrderID,OrderDay,Productname,Amt,0 as Flag1,0 as Flag2 INTO test3 FROM v

7、iew1UNION ALLSELECT '','*S*',Productname,SUM(Amt),0,1 FROM view1 GROUP BY Productname WITH ROLLUPGOUPDATE test3 SET Orderday='*T*',Productname='',Flag1=1 WHERE Productname IS NULLSELECT OrderID,Orderday,Productname,Amt FROM test3 WHERE Productname>'ON' OR P

8、roductname='' ORDER BY Flag1,Productname,Flag2GO-第6小题(6分) CREATE FUNCTION fn1 (v1 nvarchar(10)RETURNS varchar(20) ASBEGIN DECLARE s varchar(20),n int,s1 varchar(30) SELECT n=count(*) from test2 where ProductID=v1 SELECT s1=Productname FROM test1 where ProductID=v1 IF n>0 BEGIN IF n>1 s

9、et s=s1+Space(1)+LTRIM(STR(n,6)+'ts' else set s=s1+Space(1)+'1t' END ELSE IF n=0 set s=s1+space(1)+'*' RETURN(s) ENDGOSELECT ProductID,dbo.fn1(ProductID) as Frequency FROM test1 ORDER BY ProductID-第7小题(4分) ALTER TABLE test2 ADD newCol intGODECLARE c1 cursor FOR SELECT product

10、id,qty FROM test2ORDER BY ProductID,OrderDateOPEN c1DECLARE qty int,qty1 int,id nvarchar(2),id1 nvarchar(2),price moneyFetch Next FROM c1 into id1,qty1Set id='' Set qty=0WHILE fetch_status=0BEGIN IF id<>id1 begin Set id=id1 Set qty=qty1 end ELSE Set qty=qty+qty1 UPDATE test2 Set newCol

11、=qty WHERE CURRENT of c1 Fetch Next FROM c1 into id1,qty1ENDDEALLOCATE c1SELECT ProductID,OrderID,Convert(varchar(10),OrderDate,120) as 'Orderdate',Qty,newColFROM test2 WHERE ProductID like 'C36' ORDER BY ProductID,OrderDateGO表2. Test1表数据 表3. Test2表数据ProductIDProductNameCategoryPrice

12、OrderIDOrderDateQtyProductIDW1OkraR6.0015642012-08-12 00:00:00.0007W1W2PepperR7.0018922012-09-22 00:00:00.0008W2W3SoybeanT8.0028352012-10-12 00:00:00.0009W3W4PotatoT9.0028352012-10-12 00:00:00.0008W4W5MelonT10.0032122012-11-22 00:00:00.0007W5W6TaroM9.0038512012-12-22 00:00:00.0005W5W7LeekM8.00385120

13、12-12-22 00:00:00.0006W6W8CeleryM7.0039422013-01-25 00:00:00.0007W339422013-01-25 00:00:00.0006W442332013-02-23 00:00:00.0008W142332013-02-23 00:00:00.0009W348262013-03-06 00:00:00.0007W548262013-03-06 00:00:00.0005W648262013-03-06 00:00:00.0006W855692013-03-12 00:00:00.0008W2二、程序设计题（本题共60分）数据库mySal

14、es用来存放某企业产品销售数据，Products表存放产品信息，Categories表存放产品类别，Customers表存放客户信息，Orders表存放订单信息，OrderItems表存放订单明细信息，其表结构见表3表7。试编写T-SQL语句，完成以下各项功能（注意必须按试卷中规定的列名答题，不能篡改列名，否则答题无效。临时表可自行定义）：表3. Products表结构列名类型长度规则中文说明PF1整型4Identity列，主键产品编码PF2字符型50非空产品名称PF3整型4非空产品类别PF4字符型20非空规格型号PF5数值型8, 2成本单价表4. Categories表结构列名类型长度规则中

15、文说明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外键产品编码

16、WF3数值型8,2两位小数，单价大于0销售单价WF4数值型8非空，默认为0销售数量WF5数值型6,2大于等于0，小于1。0表示不打折。折扣率WF6数值型10,2计算列。由销售量、单价和折扣率自动计算得到，保留两位小数。销售金额1根据OrderItems表的结构和各个约束条件，编写创建该表所需的T-SQL语句，并建立由订单编号和产品编号组成的组合主键。（8分）2已知某数据检索的关系代数表达式如下，写出与其对应的SQL语句。（6分）2(Customers) 2(2=tofu(Products)OrderItems3>=2009-01-01 3<=2009-12-31(Orders) C

17、ustomers)3已知SQL查询语句如下，写出与其对应的关系代数表达式。（6分）SELECT SF1,SF2,SF3 FROM Orders WHERE SF1 IN (SELECT a.WF1 FROM OrderItems AS aJOIN OrderItems AS b ON a.WF1=b.WF1 and a.WF2<>b.WF2 )and Year(SF2)=2010 and Month(SF2)=94根据各表数据，统计名称为B's Beverages的这个客户2008年度购买哪些产品的次数最多，列出这些产品的名称。 （8分，提示：可使用子查询）5. 编写一个存

18、储过程，输入一个产品名称，输出该产品的销售额在它所属同类产品销售额中的排名名次及所占的百分比（要求保留两位小数）。（7分，提示：可使用排名函数）6创建一个用户定义函数，输入一个1100的整数n，输出返回销售额最大的前n%个客户的名称及其销售额。（8分，提示可使用ceiling函数）7. 建立一个触发器，当Customers表中插入一条记录时，触发器自动对该记录的正确性进行验证，并根据验证结果作出拒绝插入记录或予以提示警告等处理。记录正确性验证规则如下：（9分, 提示：使用inserted表及Charindex、ASCII等字符串函数）a) 客户编码互不重复，并由5个字母组成，前两个字符与客户名称的前两个字符相同，后三个字符分别从客户名称第三个字符之后的非空格字符中自左至右随机不重复提取。当客户编码不满足上述条件时，拒绝插入该记录。b) 当客户“所在国家”为USA时，其“所在城市”