中南大学大数据库试验盛津芳

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

2、。6.列出表PickofMonth中的所有记录，并显示中文列标题。作答CategorycCategoryIdcCategoryvDescriptionToys寸cToyIdjvToyNameJvToyDescriptionShopp in gCartcCartIdcToyIdsiQtyPickOfMo nthOrderDetailcOrderNoJcToyIdsiQtycGiftWrapcWrapperIdvMessagemToyCostcCategoryIdcToyIdsiMonthiYeariTotalSoldWrappervDescriptionmWrapperRateimPhotovW

3、rapperIm gPathOrderscOrderNodOrderDate cCartIdcShopperIdcShippingModeldmShippingChargesmGiftWrapChargescOrderProcessedmTotalCostdExpDelDate-21-mShipme nt| cOrderNo_| dShipmentDate| cDeliveryStatusdActualDeliveryDate-?ToyBra ndcBrandIdcBrandNam eRecipie ntcOrderNovFirstNamevLastNamevAddresscCitycStat

4、ecCountryIdcZipCodecPhoneCoun tryShoppercShopperIdcPasswordvFirstNamevLastNamevEmailIdvAddresscCitycStatecCountryIdcZipCodecPhonecCreditCardNovCreditCardTypedExpiryDate3.执行命令exec sp_helpdb GlobalToyz4.依次执行命令：execsp_helpconstraint Categoryexecsp_helpconstraint Countryexecsp_helpconstraint OrderDetail

5、execsp_helpconstraint Ordersexecsp_helpconstraint PickOfMonthexecsp_helpconstraint Recipientexecsp_helpconstraint Shipmentexecsp_helpconstraint ShippingModeexecsp_helpconstraint ShippingRateexecsp_helpconstraint Shopperexecsp_helpconstraint ShoppingCartexecsp_helpconstraint ToyBrandexecsp_helpconstr

6、aint Toysexecsp_helpconstraint Wrapper5.(1)alter table toysadd constraint siToyQoh_size check(siToyQoh0 and siToyQoh =1)6.执行命令：select cToyId as 玩具id,siMonth as 出厂月份,iYear as 出厂年份,iTotalSold as 出厂总数from PickOfMonth实验二 查询、更新数据库实验容1.显示属于California和Illinoi州的顾客的名、姓和emailID。2.显示定单、顾客ID,定单的总价值，并以定单的总价值的升序排

7、列。3.显示在orderDetail表中vMessage为空值的行。4.显示玩具名字中有“Racer字样的所有玩具的基本资料。5.根据2000年的玩具销售总数，显示“Pick of the Mo nth”玩具的前五名玩具的ID。6.根据OrderDetail表，显示玩具总价值大于Y50的定单的和玩具总价值。7.显示一份包含所有装运信息的报表，包括：Order Number, Shipment Date, ActualDelivery Date, Days in Tran sit.J提示：Days in Tran sit = Actual Delivery Date -Shipment Date

8、）8.显示所有玩具的名称、商标和种类（Toy Name, Brand, Categor）y。9.以下列格式显示所有购物者的名字和他们的简称：（Initials, vFirstName,vLastNam，例如Angela Smith的Initials为AS10.显示所有玩具的平均价格，并舍入到整数。11.显示所有购买者和收货人的名、姓、地址和所在城市，要求显示结果中的重 复记录。12.显示没有包装的所有玩具的名称。 （要求用子查询实现）13.显示已收货定单的定单以及下定单的时间。 （要求用子查询实现）14.显示一份基于Orderdetail的报表，包括cOrderNo,cToyld和mToyCo

9、st记录 以cOrderNo升序排列，并计算每一笔定单的玩具总价值。 （提示：使用运算 符COMPUTEBY。15.把价格在$20以上的所有玩具的信息拷贝到称为PremiumToys的新表中。16.给id为000001玩具的价格增加$1。17.删除“Largo”牌的所有玩具作答1.select vfirstname,vlastname,vemailidfrom shopper where cstate=Californiaor cstate=Illinoi2.select cOrderNo,cShopperId,mTotalCostfrom Ordersorder by mTotalCost3

10、.select *from orderDetailwhere vmessage is null4.select vToyDescriptionfrom Toyswhere vToyName like Racer5select top 5 cToyIdfrom PickOfMonthwhere iyear=2000order by iTotalsold desc6.select cOrderNo,mToyCostfrom OrderDetailwhere mToyCost507.select cOrderNo,dShipmentDate,dActualDeliveryDate,dActualDe

11、liveryDate-dShipmentDate as DaysinTransit from shipment8.select vToyName,cBrandName,cCategoryfrom Toys,category,toybrandwhere Toys.cbrandid=ToyBrand.cbrandidand Category.cCategoryid=Toys.cCategoryid9.select vFirstName,vLastName,substring(vFirstName,1,1)+ . +substring(vLastName,1,1) fromshopper10. se

12、lect round(avg(mtoyrate),0) as avgratefrom toys11.select a.vFirstName,a.vLastName,a.vAddress,a.cCity,b.vFirstName,b.vLastName,b.vAddress,b.cCity from shopper a,Recipient b,orderswhere orders.cShopperId=a.cShopperIdand orders.cOrderNo=b.cOrderNo12.select vToyNamefrom toyswhere cToyId in (select cToyI

13、dfrom OrderDetailwhere cWrapperId is null )13.select cOrderNo,dOrderDatefrom orderswhere cOrderNo in (select cOrderNofrom shipment where dActualDeliveryDate is not null )14. select cOrderNo,cToyId,mToyCostfrom orderdetailorder by cOrderNocompute sum(mToyCost) by cOrderNo15. if exists(select * from s

14、ysobjects where name = PremiumToys) drop tableas InitialsPremiumToysgoselect * into PremiumToys from Toyswhere mToyRate 20select * from PremiumToys16. update Toys set mToyRate = mToyRate + 1 where cToyId = 00000117. delete *from Toyswhere BrandName=Largo实验三：视图与触发器实验容1.定义一个视图，包括购买者的、所在州和他们所订购玩具的名称、价格

15、和数量。2.基于（1）中定义的视图，查询显示所有California州的购买者的和他们所订购玩具的名 称及数量。3.编写一段程序，将每种玩具的价格提高Y0.5，直到玩具的平均价格接近$24.5为止。此外，任何玩具的最大价格不应超过$53。4.创建一个称为preCharges的存储过程，它返回某个定单号的装运费用和包装费用。5.创 建 一 个 称 为prcHandlingCharges的 过 程 ， 它 接 收 定 单 号 并 显 示 经 营 费 用 。PrehandlingCharges过程应使用preCharges过程来得到装运费和礼品包装费。 提示：经营费用=装运费+礼品包装费6.在Ord

16、erDetail上定义一个触发器，如果购物者改变了定单的数量，玩具的成本也自动 地改变。 （提示：Toyeost = Quantity * Toy Rate）作答1.Create viewShopperx(vFirstName,vLastName,eState,vToyName,mToyRate,siQty)asseleeta.vFirstName,a.vLastName,a.eState,b.vToyName,b.mToyRate,e.siQtyfrom Shopper a,Toys b,OrderDetail e,Orders dwhere (a.eShopperId=d.eShopper

17、Id and b.eToyId=e.eToyId) ande.eOrderNo=d.eOrderNo2.seleet vFirstName,vLastName,vToyName,siQtyfrom Shopperxwhere eState=California3.Use GlobalToyzwhile(seleet avg(mToyRate) from toys)53breakelseupdate toysset mToyRate=mToyRate+0.54.create proc prcChargescOrderNo char(6)asselect cOrderNo,mShippingCha

18、rges,mGiftWrapChargesfrom orderswhere cOrderNo=cOrderNo5.create proc prcHandingChargesOrderNo char(6) /定单号要长度为6的字符型数据ascreate table #temp( OrderNo char(6), mSChargesmoney, mGChargesmoney) insertinto #temp exec prcCharges OrderNo /使用存储过程prcCharges selectOrderNo,mSCharges+mGChargesas HandingCharges /经

19、营费用=装运费+礼品包装费from #temp6.create trigger change_toycost /新建触发器on OrderDetailfor updateasif update(siQty)beginupdate OrderDetailset mToyCost=siQty*mToyRatefrom OrderDetail,toysend实验四：事务与游标实验容1.名为prcGenOrder的存储过程产生存在于数据库中的定单号：CREATE PROCEDURE prcGenOrderOrderNo char(6) OUTPUTasSELECT OrderNo=Max(cOrder

20、No) FROM OrdersSELECT OrderNo=CASEWHEN OrderNo=0 and OrderNo=9 and OrderNo=99 and OrderNo=999 and OrderNo=9999 and OrderNo=99999 Then Convert(char,OrderNo+1) ENDRETURN当购物者确认定单时，应该出现下面的步骤： (1)用上面的过程产生定单号。(2)定单号，当前日期，购物车ID,和购物者ID应该加到Orders表中。(3)定单号，玩具ID，和数量应加到OrderDetail表中。(4)在OrderDetail表中更新玩具成本。 (提示

21、：Toy cost = Quantity * Toy Rate).将上述步骤定义为一个事务。编写一个过程以购物车ID和购物者ID为参数，实现这个 事务。2.编写一个程序显示每天的定单状态。 如果当天的定单值总合大于170，则显示“High sales”,否则显示”Low sales”.报告中要求列出日期、定单状态和定单总价值。作答1.create proc prcOrder CartID char(6),ShopperID char(6)asbegin transactiondeclare OrderNo char(6),OrderNo1 char (6),ToyID char (6),Qty

22、 char (6)exec prcGenOrder OrderNo outputselect OrderNo1=isnull( CONVERT(nvarchar, OrderNo), )create table #temp1 (OrderNo char (6),CartId char (6),ShopperId char (6),OrderDatedatetime)insert into #temp1 (OrderNo,CartId,ShopperId,OrderDate)values(OrderNo1,CartID,ShopperID,getdate()if rowcount=0beginrollback Transactionreturnendinsert into Orders (cOrderNo,cCartId,cShopperId,dOrderDate)select * from #temp1select ToyID=cToyId,Qty=siQty f