實(shí)驗(yàn)容大小、創(chuàng)建日期等。4．利用系統(tǒng)預(yù)定義的存儲(chǔ)過(guò)程sp_helpconstraint查看所有表中出現(xiàn)的約束(包括Primarykey,Foreignkey,checkconstraint,default,unique)(2)玩具適宜的最低年齡缺省為1。6.列出表PickofMonth中的所有記錄，并顯示中文列標(biāo)題。作答CategoryToysPickOfMonthPickOfMonthShoppingCartOrderDetailOrderDetailWrapperWrapperRecipientShippingModeOrdersCountryCountryShippingRateShopperShipmentShipment3.執(zhí)行命令execsp_helpdbGlobalToyz4.依次執(zhí)行命令：execsp_helpconstraintCategoryexecsp_helpconstraintCountryexecsp_helpconstraintOrderDetailexecsp_helpconstraintOrdersexecsp_helpconstraintPickOfMonthexecsp_helpconstraintRecipientexecsp_helpconstraintShipmentexecsp_helpconstraintShippingModeexecsp_helpconstraintShippingRateexecsp_helpconstraintShopperexecsp_helpconstraintShoppingCartexecsp_helpconstraintToyBrandexecsp_helpconstraintToysexecsp_helpconstraintWrapper(1)altertabletoysaddconstraintsiToyQoh_sizecheck(siToyQoh>0andsiToyQoh<200)(2)altertabletoysaddconstraintnewsiLowerAge_mincheck(siLowerAge>=1)6.執(zhí)行命令：selectcToyIdas'玩具id',siMonthas'出廠月份',iYearas'出廠年份',iTotalSoldas'出廠總數(shù)'fromPickOfMonth實(shí)驗(yàn)二查詢、更新數(shù)據(jù)庫(kù)實(shí)驗(yàn)容1.顯示屬于California和Illinoi州的顧客的名、姓和emailID。2.顯示定單、顧客ID，定單的總價(jià)值，并以定單的總價(jià)值的升序排列。3.顯示在orderDetail表中vMessage為空值的行。4.顯示玩具名字中有“Racer”字樣的所有玩具的基本資料。5.根據(jù)2000年的玩具銷售總數(shù)，顯示“PickoftheMonth”玩具的前五名玩具的6.根據(jù)OrderDetail表，顯示玩具總價(jià)值大于￥50的定單的和玩具總價(jià)值。7.顯示一份包含所有裝運(yùn)信息的報(bào)表，包括：OrderNumber,ShipmentDate,ActualDeliveryDate,DaysinTransit.(提示：DaysinTransit=ActualDeliveryDate–ShipmentDate)8.顯示所有玩具的名稱、商標(biāo)和種類(ToyName,Brand,Category)。9.以下列格式顯示所有購(gòu)物者的名字和他們的簡(jiǎn)稱：(Initials,vFirstName,vLastName),例如AngelaSmith的Initials為A.S。10.顯示所有玩具的平均價(jià)格，并舍入到整數(shù)。11.顯示所有購(gòu)買者和收貨人的名、姓、地址和所在城市，要求顯示結(jié)果中的重12.顯示沒(méi)有包裝的所有玩具的名稱。(要求用子查詢實(shí)現(xiàn))13.顯示已收貨定單的定單以及下定單的時(shí)間。(要求用子查詢實(shí)現(xiàn))14.顯示一份基于Orderdetail的報(bào)表，包括cOrderNo,cToyId和mToyCost，記錄以cOrderNo升序排列，并計(jì)算每一筆定單的玩具總價(jià)值。(提示：使用運(yùn)算15.把價(jià)格在$20以上的所有玩具的信息拷貝到稱為PremiumToys的新表中。16.給id為‘000001’玩具的價(jià)格增加$1。17.刪除“Largo”牌的所有玩具。作答1.selectvfirstname,vlastname,vemailidfromshopperwherecstate='California'orcstate='Illinoi'2.selectcOrderNo,cShopperId,mTotalCostfromOrdersorderbymTotalCost3.select*fromorderDetailwherevmessageisnull4.selectvToyDescriptionfromToyswherevToyNamelike'Racer'5．selecttop5cToyIdfromPickOfMonthwhereiyear=2000orderbyiTotalsolddesc6.selectcOrderNo,mToyCostfromOrderDetailwheremToyCost>507.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')droptablePremiumToysselect*intoPremiumToysfromToyswheremToyRate>20select*fromPremiumToys16.updateToyssetmToyRate=mToyRate+1wherecToyId='000001'17.delete*fromToyswhereBrandName=’Largo’實(shí)驗(yàn)三：視圖與觸發(fā)器實(shí)驗(yàn)容1.定義一個(gè)視圖，包括購(gòu)買者的、所在州和他們所訂購(gòu)?fù)婢叩拿Q、價(jià)格和數(shù)量。2.基于(1)中定義的視圖，查詢顯示所有California州的購(gòu)買者的和他們所訂購(gòu)?fù)婢叩拿绦?，將每種玩具的價(jià)格提高￥0.5，直到玩具的平均價(jià)格接近$24.5為止。此格不應(yīng)超過(guò)$53。prcCharges。營(yíng)費(fèi)用=裝運(yùn)費(fèi)+禮品包裝費(fèi)6.在OrderDetail上定義一個(gè)觸發(fā)器，如果購(gòu)物者改變了定單的數(shù)量，玩具的成本也自動(dòng)作答1.CreateviewShopperx(vFirstName,vLastName,cState,vToyName,mToyRate,siQty)sselecta.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)<24.5beginif(selectmax(mToyRate)fromtoys)>53breakelseupdatetoyssetmToyRate=mToyRate+0.54.createprocprcChargescOrderNochar(6)sselectcOrderNo,mShippingCharges,mGiftWrapChargesfromorderswherecOrderNo=cOrderNo5.createprocprcHandingChargesOrderNochar(6)//定單號(hào)要長(zhǎng)度為6的字符型數(shù)據(jù)sinsertintotempexecprcChargesOrderNo使用存儲(chǔ)過(guò)程prcChargesselectOrderNo,mSCharges+mGChargesasHandingCharges//經(jīng)營(yíng)費(fèi)用=裝運(yùn)費(fèi)+禮品包裝費(fèi)from#temp6.createtriggerchange_toycost//新建觸發(fā)器onOrderDetailforupdatesifupdate(siQty)beginupdateOrderDetailsetmToyCost=siQty*mToyRatefromOrderDetail,toysnd實(shí)驗(yàn)四：事務(wù)與游標(biāo)實(shí)驗(yàn)容erCREATEPROCEDUREprcGenOrderOrderNochar(6)OUTPUTSELECTOrderNo=Max(cOrderNo)FROMOrdersSELECTOrderNo=WHENOrderNo>=0andOrderNo<9Then‘00000’+Convert(char,OrderNo+1)WHENOrderNo>=9andOrderNo<99Then‘0000’+Convert(char,OrderNo+1)WHENOrderNo>=99andOrderNo<999Then‘000’+Convert(char,OrderNo+1)WHENOrderNo>=999andOrderNo<9999Then‘00’+Convert(char,OrderNo+1)WHENOrderNo>=9999andOrderNo<99999Then‘0’+Convert(char,OrderNo+1)WHENOrderNo>=99999ThenConvert(char,OrderNo+1)(1)用上面的過(guò)程產(chǎn)生定單號(hào)。(2)定單號(hào)，當(dāng)前日期，購(gòu)物車ID，和購(gòu)物者ID應(yīng)該加到Orders表中。(3)定單號(hào)，玩具ID，和數(shù)量應(yīng)加到OrderDetail表中。步驟定義為一個(gè)事務(wù)。編寫一個(gè)過(guò)程以購(gòu)物車ID和購(gòu)物者ID為參數(shù)，實(shí)現(xiàn)這個(gè)作答createprocprcOrderCartIDchar(6),ShopperIDchar(6)sbegintransactiondeclareOrderNochar(6),OrderNo1char(6),ToyIDchar(6),QtycharexecprcGenOrderOrderNooutputselectOrderNo1=isnull(CONVERT(nvarchar,OrderNo),'<NULL>')createtable#temp1(OrderNochar(6),CartIdchar(6),ShopperIdchar(6),OrderDatedatetime)insertinto#temp1(OrderNo,CartId,ShopperId,OrderDate)values(OrderNo1,CartID,ShopperID,getdate())ifrowcount=0beginrollbackTransactionreturnndinsertintoOrders(cOrderNo,cCartId,cShopperId,dOrderDate)select*from#temp1selectToyID=cToyId,Qty=siQtyfromShoppingCartwherecCartId=CartIDifrowcount=0beginrollbackTransactionretu