搜索
数据库技术实验报告实验一创建表、更新表和实施数据完整性实验内容1．运行给定的SQLScript，建立数据库GlobalToyz。2．了解表的结构，建立所有表的关系图。3．利用系统预定义的存储过程sp_helpdb查看数据库的相关信息，例如所有者、大小、创建日期等。4．利用系统预定义的存储过程sp_helpconstraint查看所有表中出现的约束（包括Primarykey,Foreignkey,checkconstraint,default,unique）5．对表Toys实施下面数据完整性规则：（1）玩具的现有数量应在0到200之间；（2）玩具适宜的最低年龄缺省为1。6.列出表PickofMonth中的所有记录，并显示中文列标题。作答2.CategorycCategoryIdcCategoryvDescriptionCountrycCountryIdcCountryOrderDetailcOrderNocToyIdsiQtycGiftWrapcWrapperIdvMessagemToyCostOrderscOrderNodOrderDatecCartIdcShopperIdcShippingModeIdmShippingChargesmGiftWrapChargescOrderProcessedmTotalCostdExpDelDatePickOfMonthcToyIdsiMonthiYeariTotalSoldRecipientcOrderNovFirstNamevLastNamevAddresscCitycStatecCountryIdcZipCodecPhoneShipmentcOrderNodShipmentDatecDeliveryStatusdActualDeliveryDateShippingModecModeIdcModeiMaxDelDaysShippingRatecCountryIDcModeIdmRatePerPoundShoppercShopperIdcPasswordvFirstNamevLastNamevEmailIdvAddresscCitycStatecCountryIdcZipCodecPhonecCreditCardNovCreditCardTypedExpiryDateShoppingCartcCartIdcToyIdsiQtyToyBrandcBrandIdcBrandNameToyscToyIdvToyNamevToyDescriptioncCategoryIdmToyRatecBrandIdimPhotosiToyQohsiLowerAgesiUpperAgesiToyWeightvToyImgPathWrappercWrapperIdvDescriptionmWrapperRateimPhotovWrapperImgPath3.执行命令execsp_helpdbGlobalToyz4.依次执行命令：execsp_helpconstraintCategoryexecsp_helpconstraintCountryexecsp_helpconstraintOrderDetailexecsp_helpconstraintOrdersexecsp_helpconstraintPickOfMonthexecsp_helpconstraintRecipientexecsp_helpconstraintShipmentexecsp_helpconstraintShippingModeexecsp_helpconstraintShippingRateexecsp_helpconstraintShopperexecsp_helpconstraintShoppingCartexecsp_helpconstraintToyBrandexecsp_helpconstraintToysexecsp_helpconstraintWrapper5.（1）altertabletoysaddconstraintsiToyQoh_sizecheck(siToyQoh0andsiToyQoh200)（2）altertabletoysaddconstraintnewsiLowerAge_mincheck(siLowerAge=1)6.执行命令：selectcToyIdas'玩具id',siMonthas'出厂月份',iYearas'出厂年份',iTotalSoldas'出厂总数'fromPickOfMonth实验二查询、更新数据库实验内容1.显示属于California和Illinoi州的顾客的名、姓和emailID。2.显示定单号码、顾客ID，定单的总价值，并以定单的总价值的升序排列。3.显示在orderDetail表中vMessage为空值的行。4.显示玩具名字中有“Racer”字样的所有玩具的基本资料。5.根据2000年的玩具销售总数，显示“PickoftheMonth”玩具的前五名玩具的ID。6.根据OrderDetail表，显示玩具总价值大于￥50的定单的号码和玩具总价值。7.显示一份包含所有装运信息的报表，包括：OrderNumber,ShipmentDate,ActualDeliveryDate,DaysinTransit.(提示：DaysinTransit=ActualDeliveryDate–ShipmentDate)8.显示所有玩具的名称、商标和种类（ToyName,Brand,Category）。9.以下列格式显示所有购物者的名字和他们的简称：（Initials,vFirstName,vLastName）,例如AngelaSmith的Initials为A.S。10.显示所有玩具的平均价格，并舍入到整数。11.显示所有购买者和收货人的名、姓、地址和所在城市，要求显示结果中的重复记录。12.显示没有包装的所有玩具的名称。（要求用子查询实现）13.显示已收货定单的定单号码以及下定单的时间。（要求用子查询实现）14.显示一份基于Orderdetail的报表，包括cOrderNo,cToyId和mToyCost，记录以cOrderNo升序排列，并计算每一笔定单的玩具总价值。（提示：使用运算符COMPUTEBY）。15.把价格在$20以上的所有玩具的信息拷贝到称为PremiumToys的新表中。16.给id为‘000001’玩具的价格增加$1。17.删除“Largo”牌的所有玩具。作答1.selectvfirstname,vlastname,vemailidfromshopperwherecstate='California'orcstate='Illinoi'2.selectcOrderNo,cShopperId,mTotalCostfromOrdersorderbymTotalCost3.select*fromorderDetailwherevmessageisnull4.selectvToyDescriptionfromToyswherevToyNamelike'Racer'5．selecttop5cToyIdfromPickOfMonthwhereiyear=2000orderbyiTotalsolddesc6.selectcOrderNo,mToyCostfromOrderDetailwheremToyCost507.selectcOrderNo,dShipmentDate,dActualDeliveryDate,dActualDeliveryDate-dShipmentDateasDaysinTransitfromshipment8.selectvToyName,cBrandName,cCategoryfromToys,category,toybrandwhereToys.cbrandid=ToyBrand.cbrandidandCategory.cCategoryid=Toys.cCategoryid9.selectvFirstName,vLastName,substring(vFirstName,1,1)+'.'+substring(vLastName,1,1)asInitialsfromshopper10.selectround(avg(mtoyrate),0)asavgratefromtoys11.selecta.vFirstName,a.vLastName,a.vAddress,a.cCity,b.vFirstName,b.vLastName,b.vAddress,b.cCityfromshoppera,Recipientb,orderswhereorders.cShopperId=a.cShopperIdandorders.cOrderNo=b.cOrderNo12.selectvToyNamefromtoyswherecToyIdin(selectcToyIdfromOrderDetailwherecWrapperIdisnull)13.selectcOrderNo,dOrderDatefromorderswherecOrderNoin(selectcOrderNofromshipmentwheredActualDeliveryDateisnotnull)14.selectcOrderNo,cToyId,mToyCostfromorderdetailorderbycOrderNocomputesum(mToyCost)bycOrderNo15.ifexists(select*fromsysobjectswhere[name]='PremiumToys')droptablePremiumToysgoselect*intoPremiumToysfromToyswheremToyRate20select*fromPremiumToys16.updateToyssetmToyRate=mToyRate+1wherecToyId='000001'17.delete*fromToyswhereBrandName=’Largo’实验三：视图与触发器实验内容1.定义一个视图，包括购买者的姓名、所在州和他们所订购玩具的名称、价格和数量。2.基于（1）中定义的视图，查询显示所有California州的购买者的姓名和他们所订购玩具的名称及数量。3.编写一段程序，将每种玩具的价格提高￥0.5，直到玩具的平均价格接近$24.5为止。此外，任何玩具的最大价格不应超过$53。4.创建一个称为prcCharges的存储过程，它返回某个定单号的装运费用和包装费用。5.创建一个称为prcHandlingCharges的过程，它接收定单号并显示经营费用。PrchandlingCharges过程应使用prcCharges过程来得到装运费和礼品包装费。提示：经营费用=装运费+礼品包装费6.在OrderDetail上定义一个触发器，如果购物者改变了定单的数量，玩具的成本也自动地改变。（提示：Toycost=Quantity*ToyRate）作答1.CreateviewShopperx(vFirstName,vLastName,cState,vToyName,mToyRate,siQty)asselecta.vFirstName,a.vLastName,a.cState,b.vToyName,b.mToyRate,c.siQtyfromShoppera,Toysb,OrderDetailc,Ordersdwhere(a.cShopperId=d.cShopperIdandb.cToyId=c.cToyId)andc.cOrderNo=d.cOrderNo2.selectvFirstName,vLastName,vToyName,siQtyfromShopperxwherecState='California'3.UseGlobalToyzwhile(selectavg(mToyRate)fromtoys