中南大学 大数据库实验盛津芳.docx

1、中南大学 大数据库实验 盛津芳数据库技术实验报告实验一 创建表、更新表和实施数据完整性实验容1运行给定的SQL Script，建立数据库GlobalToyz。2了解表的结构，建立所有表的关系图。3利用系统预定义的存储过程sp_helpdb查看数据库的相关信息，例如所有者、大小、创建日期等。4利用系统预定义的存储过程sp_helpconstraint查看所有表中出现的约束（包括Primary key, Foreign key, check constraint, default, unique）5对表Toys实施下面数据完整性规则：（1）玩具的现有数量应在0到200之间；（2）玩具适宜的最低年龄

2、缺省为1。6. 列出表PickofMonth中的所有记录，并显示中文列标题。作答2.3. 执行命令exec sp_helpdb GlobalToyz4. 依次执行命令： exec sp_helpconstraint Categoryexec sp_helpconstraint Countryexec sp_helpconstraint OrderDetailexec sp_helpconstraint Ordersexec sp_helpconstraint PickOfMonthexec sp_helpconstraint Recipientexec sp_helpconstraint Sh

3、ipmentexec sp_helpconstraint ShippingModeexec sp_helpconstraint ShippingRateexec sp_helpconstraint Shopperexec sp_helpconstraint ShoppingCartexec sp_helpconstraint ToyBrandexec sp_helpconstraint Toysexec sp_helpconstraint Wrapper5. （1）alter table toysadd constraint siToyQoh_size check(siToyQoh0 and

4、siToyQoh =1)6. 执行命令：select cToyId as 玩具id,siMonth as 出厂月份,iYear as 出厂年份,iTotalSold as 出厂总数from PickOfMonth实验二 查询、更新数据库实验容1.显示属于California和Illinoi州的顾客的名、姓和emailID。2.显示定单、顾客ID，定单的总价值，并以定单的总价值的升序排列。3.显示在orderDetail表中vMessage为空值的行。4.显示玩具名字中有“Racer”字样的所有玩具的基本资料。5.根据2000年的玩具销售总数，显示“Pick of the Month”玩具的前五

5、名玩具的ID。6.根据OrderDetail表，显示玩具总价值大于￥50的定单的和玩具总价值。7.显示一份包含所有装运信息的报表，包括：Order Number, Shipment Date, Actual Delivery Date, Days in Transit. (提示：Days in Transit = Actual Delivery Date Shipment Date) 8.显示所有玩具的名称、商标和种类（Toy Name, Brand, Category）。9.以下列格式显示所有购物者的名字和他们的简称：（Initials, vFirstName, vLastName）,例如A

6、ngela Smith的Initials为A.S。10.显示所有玩具的平均价格，并舍入到整数。11.显示所有购买者和收货人的名、姓、地址和所在城市，要求显示结果中的重复记录。12.显示没有包装的所有玩具的名称。（要求用子查询实现）13.显示已收货定单的定单以及下定单的时间。（要求用子查询实现）14.显示一份基于Orderdetail的报表，包括cOrderNo,cToyId和mToyCost，记录以cOrderNo升序排列，并计算每一笔定单的玩具总价值。（提示：使用运算符COMPUTE BY）。15.把价格在$20以上的所有玩具的信息拷贝到称为PremiumToys的新表中。16.给id为00

7、0001玩具的价格增加$1。17.删除“Largo”牌的所有玩具。作答1.select vfirstname,vlastname,vemailidfrom shopper where cstate=Californiaor cstate=Illinoi2.select cOrderNo,cShopperId,mTotalCost from Ordersorder by mTotalCost3.select * from orderDetail where vmessage is null4.select vToyDescription from Toys where vToyName like

8、 Racer5select top 5 cToyIdfrom PickOfMonthwhere iyear=2000order by iTotalsold desc 6.select cOrderNo,mToyCostfrom OrderDetailwhere mToyCost507.select cOrderNo,dShipmentDate,dActualDeliveryDate, dActualDeliveryDate-dShipmentDate as DaysinTransitfrom shipment 8.select vToyName,cBrandName,cCategoryfrom

9、 Toys,category,toybrandwhere Toys.cbrandid=ToyBrand.cbrandid and Category.cCategoryid=Toys.cCategoryid9.select vFirstName,vLastName,substring(vFirstName,1,1)+ . +substring(vLastName,1,1) as Initials from shopper10.select round(avg(mtoyrate),0) as avgrate from toys11.select a.vFirstName,a.vLastName,a

10、.vAddress,a.cCity,b.vFirstName,b.vLastName,b.vAddress,b.cCityfrom shopper a,Recipient b,orderswhere orders.cShopperId=a.cShopperId and orders.cOrderNo=b.cOrderNo12.select vToyNamefrom toyswhere cToyId in (select cToyId from OrderDetail where cWrapperId is null )13.select cOrderNo,dOrderDatefrom orde

11、rswhere cOrderNo in (select cOrderNo from shipment where dActualDeliveryDate is not null )14. select cOrderNo,cToyId,mToyCostfrom orderdetailorder by cOrderNocompute sum(mToyCost) by cOrderNo15. if exists(select * from sysobjects where name = PremiumToys) drop table PremiumToys go select * into Prem

12、iumToys from Toys where mToyRate 20 select * from PremiumToys16. update Toys set mToyRate = mToyRate + 1 where cToyId = 000001 17. delete * from Toys where BrandName=Largo实验三：视图与触发器实验容1.定义一个视图，包括购买者的、所在州和他们所订购玩具的名称、价格和数量。2.基于（1）中定义的视图，查询显示所有California州的购买者的和他们所订购玩具的名称及数量。3.编写一段程序，将每种玩具的价格提高￥0.5，直到玩具

13、的平均价格接近$24.5为止。此外，任何玩具的最大价格不应超过$53。4.创建一个称为prcCharges的存储过程，它返回某个定单号的装运费用和包装费用。5.创建一个称为prcHandlingCharges的过程，它接收定单号并显示经营费用。PrchandlingCharges过程应使用prcCharges过程来得到装运费和礼品包装费。提示：经营费用=装运费+礼品包装费6.在OrderDetail上定义一个触发器，如果购物者改变了定单的数量，玩具的成本也自动地改变。（提示：Toy cost = Quantity * Toy Rate）作答1.Create viewShopperx(vFirs

14、tName,vLastName,cState,vToyName,mToyRate,siQty)asselect a.vFirstName,a.vLastName,a.cState,b.vToyName,b.mToyRate,c.siQtyfrom Shopper a,Toys b,OrderDetail c,Orders dwhere (a.cShopperId=d.cShopperId and b.cToyId=c.cToyId) and c.cOrderNo=d.cOrderNo2.select vFirstName,vLastName,vToyName,siQtyfrom Shopper

15、xwhere cState=California3.Use GlobalToyzwhile(select avg(mToyRate) from toys)53 break else update toys set mToyRate=mToyRate+0.54.create proc prcCharges cOrderNo char(6) as select cOrderNo,mShippingCharges,mGiftWrapChargesfrom orderswhere cOrderNo=cOrderNo5.create proc prcHandingCharges OrderNo char

16、(6) /定单号要长度为6的字符型数据as create table #temp( OrderNo char(6), mSCharges money, mGCharges money)insert into #temp exec prcCharges OrderNo /使用存储过程prcChargesselect OrderNo,mSCharges+mGCharges as HandingCharges /经营费用=装运费+礼品包装费from #temp6.create trigger change_toycost /新建触发器on OrderDetailfor updateasif upda

17、te(siQty)begin update OrderDetail set mToyCost=siQty*mToyRate from OrderDetail,toys end实验四：事务与游标实验容1.名为prcGenOrder的存储过程产生存在于数据库中的定单号： CREATE PROCEDURE prcGenOrder OrderNo char(6) OUTPUT as SELECT OrderNo=Max(cOrderNo) FROM Orders SELECT OrderNo= CASE WHEN OrderNo=0 and OrderNo=9 and OrderNo=99 and O

18、rderNo=999 and OrderNo=9999 and OrderNo=99999 Then Convert(char,OrderNo+1) END RETURN当购物者确认定单时，应该出现下面的步骤：（1）用上面的过程产生定单号。（2）定单号，当前日期，购物车ID，和购物者ID应该加到Orders表中。（3）定单号，玩具ID，和数量应加到OrderDetail表中。（4）在OrderDetail表中更新玩具成本。（提示：Toy cost = Quantity * Toy Rate）.将上述步骤定义为一个事务。编写一个过程以购物车ID和购物者ID为参数，实现这个事务。2.编写一个程序显

19、示每天的定单状态。如果当天的定单值总合大于170，则显示“High sales”,否则显示”Low sales”.报告中要求列出日期、定单状态和定单总价值。作答1.create proc prcOrder CartID char(6),ShopperID char(6)asbegin transaction declare OrderNo char(6),OrderNo1 char (6),ToyID char (6),Qty char (6) exec prcGenOrder OrderNo output select OrderNo1=isnull( CONVERT(nvarchar, O

20、rderNo), ) create table #temp1 (OrderNo char (6),CartId char (6),ShopperId char (6),OrderDate datetime) insert into #temp1 (OrderNo,CartId,ShopperId,OrderDate)values(OrderNo1,CartID,ShopperID,getdate()if rowcount=0 begin rollback Transaction return end insert into Orders (cOrderNo,cCartId,cShopperId

21、,dOrderDate) select * from #temp1 select ToyID=cToyId,Qty=siQty from ShoppingCartwhere cCartId=CartID if rowcount=0 begin rollback Transaction return end insert into OrderDetail (cOrderNo,cToyId,siQty) values(OrderNo1,ToyID,Qty) update OrderDetail set mToyCost=siQty*mToyRate from OrderDetail,Toysif

22、rowcount=0 begin rollback Transaction returnendcommit transactiongo2.declare TotalCost money /定义一个变量TotalCost保存当天定单总值select Orders.dOrderDate,Orders.cOrderProcessed,sum(mToyCost) as TotalCostfrom Orders,OrderDetailwhere Orders.cOrderNo=OrderDetail.cOrderNo and Orders.dOrderDate=getdate() group by Orders.dOrderDate,Orders.cOrderProcessed select TotalCost=sum(mTotalCost)from Orders,OrderDetailwhere Orders.cOrderNo=OrderDetail.cOrderNo and Orders.dOrderDate=getdate() /选出当天的所有定单if TotalCost 170 print High Saleselse print Low Sales