1、（1）玩具的现有数量应在0到200之间；（2）玩具适宜的最低年龄缺省为1。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 Shipmen
2、texec 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 siToyQ
3、oh =1）查询、更新数据库1. 显示属于California和Illinoi州的顾客的名、姓和emailID。2. 显示定单号码、顾客ID，定单的总价值，并以定单的总价值的升序排列。3. 显示在orderDetail表中vMessage为空值的行。4. 显示玩具名字中有“Racer”字样的所有玩具的基本资料。5. 列出表PickofMonth中的所有记录，并显示中文列标题。6. 根据2000年的玩具销售总数，显示“Pick of the Month”玩具的前五名玩具的ID。7. 根据OrderDetail表，显示玩具总价值大于￥50的定单的号码和玩具总价值。8. 显示一份包含所有装运信息的报
4、表，包括：Order Number, Shipment Date, Actual Delivery Date, Days in Transit. （提示：Days in Transit = Actual Delivery Date Shipment Date） 9. 显示所有玩具的名称、商标和种类（Toy Name, Brand, Category）。10. 以下列格式显示所有购物者的名字和他们的简称：（Initials, vFirstName, vLastName）,例如Angela Smith的Initials为A.S。11. 显示所有玩具的平均价格，并舍入到整数。12. 显示所有购买者和
5、收货人的名、姓、地址和所在城市，要求显示结果中的重复记录。13. 显示没有包装的所有玩具的名称。（要求用子查询实现）14. 显示已收货定单的定单号码以及下定单的时间。15. 显示一份基于Orderdetail的报表，包括cOrderNo,cToyId和mToyCost，记录以cOrderNo升序排列，并计算每一笔定单的玩具总价值。（提示：使用运算符COMPUTE BY）。16. 把价格在$20以上的所有玩具的信息拷贝到称为PremiumToys的新表中。17. 给id为000001玩具的价格增加$1。18. 删除“Largo”牌的所有玩具。答1. select vfirstname,vlast
6、name,vemailidfrom shopper where cstate=Californiaor cstate=Illinoi2. select cOrderNo,cShopperId,mTotalCostfrom Ordersorder by mTotalCost3. select *from orderDetailwhere vmessage is null4. select vToyDescriptionfrom Toyswhere vToyName like Racer5、select cToyId as 玩具 id,siMonth as 出厂月份,iYear as 出厂年份,i
7、TotalSold as 出厂总数from PickOfMonth6select top 5 cToyIdwhere iyear=2000order by iTotalsold desc7.select cOrderNo,mToyCostfrom OrderDetailwhere mToyCost508. select cOrderNo,dShipmentDate,dActualDeliveryDate,dActualDeliveryDate-dShipmentDate as DaysinTransitfrom shipment9. select vToyName,cBrandName,cCa
8、tegoryfrom Toys,category,toybrandwhere Toys.cbrandid=ToyBrand.cbrandidand Category.cCategoryid=Toys.cCategoryid10. select vFirstName,vLastName,substring（vFirstName,1,1）+ . +substring（vLastName,1,1） as Initialsfrom shopper11. select round（avg（mtoyrate）,0） as avgratefrom toys12. select a.vFirstName,a.
9、vLastName,a.vAddress,a.cCity,b.vFirstName,b.vLastName,b.vAddress,b.cCityfrom shopper a,Recipient b,orderswhere orders.cShopperId=a.cShopperIdand orders.cOrderNo=b.cOrderNo13 select vToyNamewhere cToyId in （select cToyIdwhere cWrapperId is null ）14. .select cOrderNo,dOrderDatefrom orderswhere cOrderN
10、o in （select cOrderNowhere dActualDeliveryDate is not null ）15. select cOrderNo,cToyId,mToyCostfrom orderdetailorder by cOrderNocompute sum（mToyCost） by cOrderNo16. if exists（select * from sysobjects where name = PremiumToys）drop table PremiumToysgoselect * into PremiumToys from Toyswhere mToyRate 20select * from PremiumToys17. update Toys set mToyRate = mToyRate + 1 where cToyId = 00000118. delete *where BrandName=Largo
