实验任务书地点 ：计算机楼四楼实验室时间安排 ：实验报告要求 ：1.列出所有的SQL 语句和源代码；2.程序要求有适当的注释；3.对数据完整性约束实施、实验三、实验四和实验五要求给出相应的测试用例。实验内容 ：use globaltoyz go - 实验一：创建表、更新表和实施数据完整性- -1运行给定的 SQL Script，建立数据库GlobalToyz。-2创建所有表的关系图。-3列出所有表中出现的约束（包括 Primary key, Foreign key, check constraint, default, unique）execsp_helpconstraint toys -4对Recipient表和 Country表中的 cCountryId属性定义一个用户自定义数据类型，并将该属性的类型定义为这个自定义数据类型。-5把价格在 $20 以上的所有玩具的材料拷贝到称为PremiumToys的新表中。ifexists(select*from sysobjects where name = PremiumToys) droptable PremiumToys go select*into PremiumToys from Toys where mToyRate 20 select*from PremiumToys -6对表 Toys 实施下面数据完整性规则：（）玩具的现有数量应在到之间；（）玩具适宜的最低年龄缺省为。altertableToys addconstraint ck_toyQoh check( siToyQoh 0 and siToyQoh 0 ) createdefault siToyWeight as 1 -8给id 为,? 玩具的价格增加$1 。update Toys set mToyRate = mToyRate + 1 where cToyId = 000001 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 8 页 - - - - - - - - - select*from Toys - 玩具 ( 单价在这 ) select*from Orders - 订单 ( 总价在这包含运费包装费) select*from OrderDetail - 订单细节 (玩具总价 ) select*from shopper - 购物者select*from Category - 玩具种类select*from Country - 国家select*from PickOfMonth - 玩具生产日期及销售量select*from Recipient - 收货人select*from Shipment - 货运详情select*from ShippingMode - 货运类型select*from ShippingRate - 货运收费详情select*from ShoppingCart - 货运订单select*from ToyBrand - 玩具商标select*from Wrapper - 玩具包装- 实验二：查询数据库- -1. 显示属于 California和Illinoi州的顾客的名、姓和emailID。select vFirstName, vLastName, vEmailId from shopper where cState =Californiaor cCity = Illinoi -2. 显示定单号码、商店ID ，定单的总价值，并以定单的总价值的升序排列。select cOrderNo, cShopperId, mShippingCharges from Orders orderbymShippingCharges -3. 显示在 orderDetail表中 vMessage为空值的行。select*from OrderDetail where vMessage isNULL- 不能用 vMessage = null -4. 显示玩具名字中有“Racer”字样的所有玩具的材料。select*from Toys where vToyName like%Racer% -5. 根据年的玩具销售总数，显示“Pick of the Month”玩具的前五名玩具的ID 。selecttop5 cToyId fromPickOfMonth whereiYear = 2000 orderby iTotalSold DESC -6. 根据 OrderDetail表，显示玩具总价值大于￥的定单的号码和玩具总价值。select cOrderNo, mToyCost from OrderDetail where mToyCost 50 -7. 显示一份包含所有装运信息的报表，包括：Order Number, Shipment Date, - Actual Delivery Date, Days in Transit. - (提示： Days in Transit = Actual Delivery Date Shipment Date) ifexists(select*from sysobjects where name = shippingment) droptable shippingment select cOrderNo asOrder Number, dShipmentDate asShipment Date, dActualDeliveryDate asActual Delivery Date, 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 8 页 - - - - - - - - - convert( int, dActualDeliveryDate - dShipmentDate) as Days in Transitinto shippingment from shipment select*from shippingment -8. 显示所有玩具的名称、商标和种类（Toy Name, Brand, Category）。selectvToyName as Toy Name , cBrandName as Brand, cCategory as Category from Toys, ToyBrand, Category where Toys. cCategoryId = Category. cCategoryId and Toys. cBrandId = ToyBrand. cBrandId -9. 显示玩具的名称和所有玩具的购物车ID 。如果玩具不在购物车中，则显示NULL 值。select vToyName, cCartId from ShoppingCart rightouterjoin Toys on ShoppingCart. cToyId=Toys . cToyId -10. 以下列格式显示所有购物者的名字和他们的简称：（Initials, vFirstName, vLastName）, 例如 Angela Smith的Initials为A.S 。select(left(vFirstName, 1)+ .+left(vLastName, 1)as Initials,vFirstName, vLastName from Shopper -11. 显示所有玩具的平均价格，并舍入到整数。select cToyId, vToyName , round( avg ( mToyRate),1) asavgpricefrom Toys groupby cToyId, vToyName -12. 显示所有购买者和收货人的名、姓、地址和所在城市。select vfirstname , vlastname, vaddress, ccity from shopper union select vfirstname , vlastname, vaddress, ccity from recipient -13. 显示没有包装的所有玩具的名称。（要求用子查询实现）select vToyName from Toys where cToyId in( select cToyId from OrderDetail where cGiftWrap = N) -14. 显示已发货定单的定单号码以及下定单的时间。（要求用子查询实现）selectcOrderNo, dOrderDate fromOrders wherecCartId in( selectcCartId from ShoppingCart) - 实验三：视图与触发器- -1. 定义一个视图，包括购买者的姓名、所在州和他们所订购玩具的名称、价格和数量。createview shopper_vw as 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 8 页 - - - - - - - - - select vfirstname , vlastname, cState, vToyName , mToyCost, siQty fromShopper, Toys , Orders, OrderDetail where Shopper. cShopperId = Orders. cShopperId and orders. cOrderNo =OrderDetail. cOrderNo and OrderDetail. cToyId = Toys. cToyId select*from shopper_vw -2. 基于（）中定义的视图，查询显示所有California州的购买者的姓名和他们所订购玩具的名称及数量。select vfirstname , vlastname, vToyName , siQty from shopper_vw where cState =California -3. 视图定义如下：- CREATE VIEW vwOrderWrapper - AS - SELECT cOrderNo, cToyId, siQty, vDescription, mWrapperRate - FROM OrderDetail JOIN Wrapper - ON OrderDetail.cWrapperId = Wrapper.cWrapperId - 以下更新命令，在更新siQty和mWrapperRate属性使用了以下更新命令时出现错误：- UPDATE vwOrderWrapper - SET siQty = 2, mWrapperRate = mWrapperRate + 1 - FROM vwOrderWrapper - WHERE cOrderNo = ,?- 修改更新命令，以更新基表中的值。-4. 在OrderDetail上定义一个触发器，如果购物者改变了定单的数量，玩具的成本也自动地改变。- （提示： Toy cost = Quantity * Toy Rate）createtrigger OrderDetail_trg on OrderDetail forupdate as declare siQty int declare mToyCost money declare ToyRate money select ToyRate = mToyRate from Toys select siQty = siQty from inserted set mToyCost = siQty * ToyRate update OrderDetail set mToycost = mToyCost go - 实验四：存储过程名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 8 页 - - - - - - - - - - -1. 编写一段程序，将每种玩具的价格提高￥.5 ，- 直到玩具的平均价格接近$24.5为止。 round(avg(mToyRate) - 24.5,1) = 0 - 此外，任何玩具的最大价格不应超过$53 。select*from toys createproc avgpriceup_proc as declare mToyRate money while( round( selectavg ( mToyRate)from toys)- 24.5 , 1) 0 ) begin update Toys set mToyRate = mToyRate + 0.5 select mToyRate = mToyRate from Toys if( mToyRate - 53 0 ) break end go exec avgpriceup_proc select*from toys -2. 创建一个称为 prcCharges的存储过程，它返回某个定单号的装运费用和包装费用。alterproc prcCharges corderno char( 6), mshippingcharges moneyoutput, mgiftwrapcharges moneyoutput as select mshippingcharges = mshippingcharges, mgiftwrapcharges =mgiftwrapcharges from Orders where cOrderNo = corderno go declare mshippingcharges money declare mgiftwrapcharges money exec prcCharges 000001, mshippingcharges output, mgiftwrapcharges output print 装运费用 :+ convert( varchar( 10 ),mshippingcharges)print 包装费用 :+ convert( varchar( 10 ),mgiftwrapcharges)-3. 创建一个称为 prcHandlingCharges的过程，它接收定单号并显示经营费用。-PrchandlingCharges过程应使用 prcCharges过程来得到装运费和礼品包装费。- 提示：经营费用= 装运费 +礼品包装费createproc prcHandlingCharges orderno char( 6), runcost moneyoutput as declare mshippingcharges money 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 8 页 - - - - - - - - - declare mgiftwrapcharges money exec prcCharges 000001, mshippingcharges output, mgiftwrapcharges output set runcost = mshippingcharges + mgiftwrapcharges go declare runcost money exec prcHandlingCharges 000001, runcost output print 经营费用 :+ convert( varchar( 10 ),runcost) - 实验五：事务与游标-1. 名为 prcGenOrder的存储过程产生存在于数据库中的定单号：CREATEPROCEDURE 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 OrderNo=999 and OrderNo=9999 and OrderNo=99999 ThenConvert( char, OrderNo +1) END RETURN - 当购物者确认定单时，应该出现下面的步骤：- （）用上面的过程产生定单号。-declare OrderNo char(6) -exec prcGenOrder orderno output -print 下一个订单号： + orderno - （）定单号，当前日期，购物车ID ，和购物者ID 应该加到 Orders表中。- （）定单号，玩具ID ，和数量应加到OrderDetail表中。- （）在 OrderDetail表中更新玩具成本。（提示：Toy cost = Quantity * Toy Rate ）. - 将上述步骤定义为一个事务。编写一个过程以购物车ID 和购物者 ID 为参数，实现这个事务。begintransaction declare count int- 用来记录用户错误号名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 8 页 - - - - - - - - - set count = 0 - 初始化declare OrderNo char( 6) exec prcGenOrder orderno output set count = count +ERROR - 记录上条语句是否产生错误declare ccartid char( 6) declare cshopperid char( 6) declare toyid char( 6) declare siqty int declare mToyRate money set ccartid = 000009- 自定义购物车ID set cshopperid = 000007- 自定义购物者ID set toyid = 000008- 自定义玩具 ID set siqty = 2 - 自定义玩具数量select mToyRate = mToyrate from Toys where cToyId = toyid insertinto Orders values( OrderNo , GETDATE(),ccartid, cshopperid,null,null,null,null,null,null) set count = count +ERROR - 记录上条语句是否产生错误insertinto OrderDetail values( OrderNo , toyid, siqty,null,null,null,mToyRate * siqty) set count = count +ERROR - 记录上条语句是否产生错误if( count 0 ) begin print 发生错误 rollback end else commit select*from Orders select*from OrderDetail -2. 编写一个程序显示每天的定单状态。如果当天的定单值总合大于，- 则显示 “High sales”, 否则显示 ”Low sales ”.报告中要求列出日期、定单状态和定单总价值createproc typeorder datetime datetime as declare totalcost money set totalcost = ( selectSUM( mTotalCost)from Orders where dOrderDate =datetime) if( totalcost 170 ) printHigh sales 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 8 页 - - - - - - - - - else printlow sales exec typeorder 2010-12-11- 输入你要查询的日期名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 8 页 - - - - - - - - -