數(shù)據(jù)庫實驗一

1、實驗任務(wù)書（實驗一）課程名稱：數(shù)據(jù)庫原理與技術(shù)實驗報告要求：列出所有的SQL語句和源代碼；程序要求有適當(dāng)?shù)淖⑨專粚?shù)據(jù)完整性約束實施要求給出相應(yīng)的測試用例實驗報告提交電子檔。實驗內(nèi)容：一：創(chuàng)建表和實施數(shù)據(jù)完整性1.運行給定的SQLScript，建立數(shù)據(jù)庫GlobalToyz。日0ADMIRE(SQLServer12.0.413.0-ADMIREEric)日口軸庫田a鑿漁據(jù)庫田口舞庫快曜日QGlobalToyz田口軸庫關(guān)罷國日口表田1垂統(tǒng)表田口FileTables田dbo.Category口dbo.CountryE口dbo.OrderDetail田L(fēng)3dbo.Orders田dbo.PickOfM

2、onth田dbo.RecipientUdbo.Shipment田Udbo.ShippingMode田Udbo.ShippingRate田dbo.ShopperUdbo_ShoppingCart田Udbo.ToyBrand田Udbo.Toys田口dbo.Wrapper田口視國田口同文詞田口可編程性田口ServiceBrok已廣田口菩儲口安全性田IReportServer田ReportServerTempDB2了解表的結(jié)構(gòu)。ToyBrandhi口口inqRteOniersPkkOfMcrrthOnerDctail3.利用系統(tǒng)預(yù)定義的存儲過程sp_helpdb查看數(shù)據(jù)庫的相關(guān)信息，例如所有者、大小、

3、創(chuàng)建日期等。execsp_helpdbG1obalTuyz)0%100%-3結(jié)果消息rL：dinedB_sizeownerdtidcre逗七edstatuscomp逗七ibi1it/_level1GlotuilTciyz5.23MBAIUnEEEric704212016St：tuE=OHLI.120n：iJTiHfileidilerL：dJTiPi1e：n-oupsizem:axsizen-owthusage1GlubTciyz1C:Progr：iiTi.FJJJiIARY4288KBUrilimited1024KBdataonly2GlubTijyzJug2C:Frogi_：=jTi.NUL

4、L1072KB2147483648KB10%1ugurJ-y4利用系統(tǒng)預(yù)定義的存儲過程sp_helpconstraint查看表中出現(xiàn)的約束(包括Primarykey,Foreignkey,checkconstraint,default,unique)1esecsp_helpconstraintCategory100%-結(jié)果爲(wèi)消息10BjectN：diTiHfCategorycunstrairLt_i:ypecunstrairLt_rL：iJTie1jFRIMARYKEY(clustered)、Jct_fk(n/a)Tableisreferencedbyoreigjikey1GlobalToyz

5、.dto.Toys：FKToys_cCategoryl_21B61esecsp_helpconstfaintCountry100%-J結(jié)果消息1OBjectN：ajTieCoutltryconstrainypmconstF注int_rL：iiTiEdelete_actionupdate_actionEtatuE_erLaBledsta1FFLEfilARiKEY(clusterhi!)c_pk(n./a.)(.n/a.)t.n./a.)(.n/12TableiereferenceilByforei：|tlkmyG1obalToyz.dto.Recipient:FK_Kecipi_cCdutl_

6、.G1obalToyz.dtu.ShippingRate:FK_ShippingR_cCo.3G1ob：ilToyz.dBo.Shopper:FK_Shopper_cCoizrLtr_1.其他的表也是用同樣的方法查詢對表Toys實施下面數(shù)據(jù)完整性規(guī)則：1（1）玩具的現(xiàn)有數(shù)量應(yīng)在0到300之間EaltertabletoysaddconstraintsiToyQoh_sizechecksiToyQoh0andsiToyQoh=200_J1消息命令已成功賓成。2）玩具適宜的最低年齡缺省為1。altertabletoysaddconstraintdefLowerAgedefault1forsiLowe

7、rAge1EaltertabletoysaddconstraintnewsiLowerAge_minchecksiLowerAge，=110%j消息命令已成功完咸。6向表Orders中增加10條2016年1月的訂單記錄(注意Orders表與其它表的關(guān)聯(lián))。原表1Eselect*fromOrders.00%-二結(jié)果消息1cOrderNodOrderDatecCartldcShupperldeShippitl勸【口deld0000012001-05-2000:00:00.0000000020000020120000022001-05-2000:00:00.000000001000005023000

8、0032001-05-2000:00:00.0000000030000070140000042001-05-2000:00:00.0000000040000060150000052001-05-2100:00:00.0000000050000020360000062001-05-2100:00:00.0000000030000120370000072001-05-2200:00:00.0000000020000080180000082001-05-2200:00:00.0000000020000090390000092001-05-2200:00:00.00000000400001002100

9、000102001-05-2200:00:00.00000000500000302插入操作：insertintoOrders(cOrderNo,dOrderDate,cCartId,cShopperId,cShippingModeId,mShippingCharges,mGiftWrapCharges,cOrderProcessed,mTotalCost,dExpDelDate)values(000011,2016-04-0100:00:00.000,000008,000010,01,15.89,4.5,Y,19.8,2016-05-2400:00:00.000)insertintoOrder

10、s(cOrderNo,dOrderDate,cCartId,cShopperId,cShippingModeId,mShippingCharges,mGiftWrapCharges,cOrderProcessed,mTotalCost,dExpDelDate)values(000012,2016-04-0300:00:00.000,000009,000011,02,17.98,2.9,Y,58.1,2016-03-0500:00:00.000)insertintoOrders(cOrderNo,dOrderDate,cCartId,cShopperId,cShippingModeId,mShi

11、ppingCharges,mGiftWrapCharges,cOrderProcessed,mTotalCost,dExpDelDate)values(000013,2016-04-0600:00:00.000,000012,000015,01,10.20,7.1,N,42.8,2016-03-0500:00:00.000)insertintoOrders(cOrderNo,dOrderDate,cCartId,cShopperId,cShippingModeId,mShippingCharges,mGiftWrapCharges,cOrderProcessed,mTotalCost,dExp

12、DelDate)values(000014,2016-04-0200:00:00.000,000013,000019,03,11.70,4.1,Y,47.3,2016-03-0500:00:00.000)insertintoOrders(cOrderNo,dOrderDate,cCartId,cShopperId,cShippingModeId,mShippingCharges,mGiftWrapCharges,cOrderProcessed,mTotalCost,dExpDelDate)values(000015,2016-04-0400:00:00.000,000008,000017,02

13、,19.60,7.4,Y,50.2,2016-03-0500:00:00.000)insertintoOrders(cOrderNo,dOrderDate,cCartId,cShopperId,cShippingModeId,mShippingCharges,mGiftWrapCharges,cOrderProcessed,mTotalCost,dExpDelDate)values(000016,2016-04-0700:00:00.000,000005,000012,03,15.41,6.1,N,43.8,2016-03-0500:00:00.000)insertintoOrders(cOr

14、derNo,dOrderDate,cCartId,cShopperId,cShippingModeId,mShippingCharges,mGiftWrapCharges,cOrderProcessed,mTotalCost,dExpDelDate)values(000017,2016-04-0900:00:00.000,000002,000014,03,15.73,5.9,Y,98.8,2016-03-0500:00:00.000)insertintoOrders(cOrderNo,dOrderDate,cCartId,cShopperId,cShippingModeId,mShipping

15、Charges,mGiftWrapCharges,cOrderProcessed,mTotalCost,dExpDelDate)values(000018,2016-04-0600:00:00.000,000002,000011,01,20.42,7.2,N,112.8,2016-03-0500:00:00.000)insertintoOrders(cOrderNo,dOrderDate,cCartId,cShopperId,cShippingModeId,mShippingCharges,mGiftWrapCharges,cOrderProcessed,mTotalCost,dExpDelD

16、ate)values(000019,2016-04-0900:00:00.000,000006,000012,02,27.32,8.8,N,158.6,2016-03-0500:00:00.000)insertintoOrders(cOrderNo,dOrderDate,cCartId,cShopperId,cShippingModeId,mShippingCharges,mGiftWrapCharges,cOrderProcessed,mTotalCost,dExpDelDate)values(000020,2016-04-0400:00:00.000,000004,000011,02,14

17、.06,2.3,Y,20.8,2016-05-2400:00:00.000)Sselect*fromOrd已匸總00%-J結(jié)果消息cUrderKodlJrderllatecC：ai-tIdcShopperldeShippinibjdeldmSkipp.mGi.cUrderProcessedrTiTot：ilCo10000012001-05-2000:00:00.0000000020000021116.001.25Y62.2220000022001-05-2000:00:00.000OliijOlil000005028.002.OilY96.5030000LI32001-05-2000:00:0

18、0.0000000LI3LILI00LI70112.000.00Y83.9740000LI42001-05-2000:00:00.0000000LI4LILI00LI6014.001.00Y40.9950000052001-05-210i：i：0i：i：0i：i.0i：ii：i0000050000020390.007.75Y231.6860000062001-05-2100:00:00.0000000030000120340.004.00Y97.9770000072001-05-2200:00:00.0000000020000081114.00LI.00Y16.9980000082001-05

19、-2200:00:00.0000000020000090320.LIO2.OilY53.9890000092001-05-2200:00:00.0000000LI4LILI0010028.002.00Y26.99100000102001-05-2200:00:00.0000000LI5LILI00LI30220.004.00Y67.9711OOOOll2016-04-010i：i：0i：i：0i：i.0i：ii：i000008|：|：|：|：I1I：|0115.894.50Y19.SO120000122016-04-0300:00:00.0000000090000110217.982.90Y5

20、8.10130000132016-04-0600:00:00.0000000120000150110.207.10H42.80140000142016-04-0200:00:00.0000000130000190311.704.IllY47.30150000152016-04-0400:00:00.00000000811000170219.607.40Y50.20160000162016-04-0700:00:00.0000000LI5LI000120315.416.10N43.SO170000172016-04-0900:00:00.0000000LI2LI000140315.735.90Y

21、98.80180000182016-04-0600:00:00.0000000020000110120.427.20H112.80190000192016-04-0900:00:00.0000000060000120227.328.80H158.60200000202016-04-0400:00:00.000000004LlllljOll0214.062.30Y20.807.創(chuàng)建一張表Orders_history,表的結(jié)構(gòu)與Orders相同，將Orders表中2001年5月的訂單記錄復(fù)制到表Orders_history中。Orders表1Elselect*fromOrders100%二結(jié)果一、

22、消息cOrilerNodflrderDatecC：ii_tIdcShopperlil10000012001-05-2000:00:00.00000000200000220000022001-05-2000:00:00.00000000100000530000032001-05-2000:00:00.00000000300000740000042001-05-2000:00:00.00000000400000650000052001-05-2100:00:00.00000000500000260000062001-05-2100:00:00.00000000300001270000072001-

23、05-2200:00:00.00000000200000880000082001-05-2200:00:00.00000000200000990000092001-05-2200:00:00.000000004000010100000102001-05-2200:00:00.000000005000003110000112016-04-0100:00:00.000000008000010120000122016-04-0300:00:00.000000009000011130000132016-04-0600:00:00.000000012000015140000142016-04-0200:

24、00:00.000000013000019150000152016-04-0400:00:00.000000008000017160000162016-04-0700:00:00.000000005000012170000172016-04-0900:00:00.000000002000014180000182016-04-0600:00:00.000000002000011190000192016-04-0900:00:00.000000006000012200000202016-04-0400:00:00.000000004000011操作后1Eselect*2into0rders_his

25、tory3fromOrders4whereCONVERT(varchar(10,dOrderDate.120)like?2001-05-_%i消息r（10行受戢響）1Eselect*fromOrdershistory00%二結(jié)果消息cUrderNuilOrderllatecC：ftldcShopperld10000012001-05-2000:00:00.00000000200000220000022001-05-2000:00:00.00000000100000530000032001-05-2000:00:00.00000000300000740000042001-05-2000:00:0

26、0.00000000400000650000052001-05-2100:00:00.00000000500000260000062001-05-2100:00:00.00000000300001270000072001-05-2200:00:00.00000000200000880000082001-05-2200:00:00.00000000200000990000092001-05-2200:00:00.000000004000010100000102001-05-2200:00:00.000000005000003二：查詢、更新數(shù)據(jù)庫1.顯示屬于California和Illinoi州的

27、顧客的名、姓和emailID。vFirstNamevLastNamevEmailld,cStatefromShopper/*wherecStatein(?California?Illinoi?);*/wherecState=?California?orcState=?Illinois?100%J結(jié)果消息1vFirEtN：djnevL：Etlf：dJTievEmailldcStateB：=LfbaraJoLlTleonb：rB:fajspeedmai1.camCaliEurnia2CatherineRubartscatherinerSCaliturnia3CynthiaMillercynthi:

28、djriSqmailcomCaliEurnia4DavidMooredavidmCaliturnia5Deb口rallTaylordeBurLtSqmail.cumIllinuie6JuEEphMartinezjueepluiiSCaliturnia7M：i-yAllenm：iryaIllinois8MichelleHerrL：rLilezmichellpLlSepee弘ai1.curnIllirLuie9FatriciaWrigktpatreciawspe日C：1iturnia10FaulLopeipaiillSqmail.cumCaliEurnia11RichardHillrich：riU

29、7L5Illinois12RobertScottRutertsECaliturnia13SaraLEere:r：LlEqmail.cumCaliturnia14DavidCuoperdav1ilcSspeedmai1.cumC：iliturnia15JohnDor：=lxljutiXLilSqmai1.cumCaliturnia16HpatkerL：TLili5heathprliSIllinois2.顯示定單號碼、顧客ID,定單的總價值，并以定單的總價值的升序排列。1EselectcOrderNo,cShopperldmTotalCostfromOrdersorderbymTotalCost1

30、00%二結(jié)果消息1cOrderITucShupperldmTi：it：dlCi：Et00000700000816.99200000900001026.99300000400000640.99400000800000953.98500000100000262.22600001000000367.97700000300000783.97800000200000596.50900000600001297.9710000005000002231.683.顯示在orderDetail表中vMessage為空值的行。1Eselect*fromOrderDetailwherevMessageisnul1:|

31、100%結(jié)果消息cUrilerNucToylJsiQtycGiftttrapcWrapperidvMeeeageFTlToHZuEt10000010000072NNULLNULL39.9820000030000173ITNULLNULL71.9730000070000061NNULLNULL12.994.顯示玩具名字中有“Racer”字樣的所有玩具的基本資料。1Eselect*fromToyswh|erevToyNamelike?%Racer%?;100%二結(jié)果一、消息cToylJvTuyN：iiTiHvToyDescriptiutlcC1000027;X!jLIRacersSetThefas

32、t-fiacedacticmracingtrackistheultim.oc2000028DijtlhRacerAsetofiIiitlsBuggieewitharacingtrack.oc5.列出表PickofMonth中的所有記錄，并顯示中文列標(biāo)題。1select2cToyldas玩具ID，3siMonthas，保質(zhì)期，4iYearas*出廠年份*，5iTotalSoldas總誚售里，6fromPickOfMonth100%二結(jié)果消息玩具M(jìn)保質(zhì)期出廠年悄總誚售重10000011200010002000001220001230300000262000300040000035200020005

33、00000372000567060000053200040007000007420005000800000782000234090000119200056001000001232001250011000015220013100120000201020002300130000211120004500140000241200132001500002612200065006.根據(jù)2000年的玩具銷售總數(shù)，顯示“PickoftheMonth”玩具的前五名玩具的ID。SELECTTOP5cToyId,sum(iTotalSold)FROMPickOfMonthWHEREiYear=2000groupby

34、ctoyidOrderbysum(iTotalSold)desc1234Eselecttop5*|fromPickOfMonthwhereiYear=2000orderbyiTotalSolddesc:100%J結(jié)果消息cTuyldsiMonthiYeariTut：=l1Suld10000261220006500200000372000567030000119200056004000007420005000500002111200045007.根據(jù)OrderDetail表，顯示玩具總價值大于50的定單的號碼和玩具總價值。SELECTOrderNumber=cOrderNo,TotalCosto

35、fToyforanOrder=sum(mToyCost)fromorderdetailgroupbycOrderNohavingsum(mToyCost)501-selectcToyldmToyCost2fromOrderDetail3wheremToyCost50100%結(jié)果A消息cTuyldmT口yCoet?00001686.500001771.9700003071.988.顯示一份包含所有裝運信息的報表，包括：OrderNumber,ShipmentDate,ActualDeliveryDate,DaysinTransit.(提示：DaysinTransit=ActualDelivery

36、Date-ShipmentDate)12EselectasJDaysinTransit/cOrderNo,dShipmentDatedActualDe1iveryDatedatediff(DAY,dShipmentDatedAutualDe1iveryDatefromShipment100%二結(jié)果消息cOrderITaiJShipmerLtDateilActu：ilDeliveryDateDaysinTraiit10000012001-05-2300:00:00.0002001-05-2400:00:00.000120000022001-05-2300:00:00.0002001-05-230

37、0:00:00.000030000032001-05-2300:00:00.000NULLNULL40000042001-05-2400:00:00.0002001-05-2600:00:00.000250000052001-05-2400:00:00.0002001-05-2500:00:00.000160000062001-05-2200:00:00.0002001-05-2300:00:00.000170000072001-05-2500:00:00.000NULLNULL80000082001-05-2400:00:00.0002001-05-2400:00:00.0000900000

38、92001-05-2400:00:00.0002001-05-2500:00:00.0001100000102001-05-2600:00:00.0002001-05-2800:00:00.00029.顯示所有玩具的名稱、商標(biāo)和種類(ToyName,Brand,Category)。1HselectvT日jtl已3cBit日ndN日jtl已?cCategutryfroiTLTuis?T025r：iiid3CategutrywhereToys.cBr：iiidId=T03!r:iiidncBr:iiidldandT033ncCateg0dCateg0ryacCateg0d:100%-J結(jié)果消息vT

39、i：iyN：dJTiecBr：iTLilff：dJTiHcCategory1RubBytheWhaleBoBByActivity2WaterCh：王tlxleISystemBoBByActivity3F：rachu七已andRocketTheBernieKidsActiity4SuperIlnlugHLATitOBILActivity5L1ghtShijWL：iJTipBoBByDolls6G1：eeDecijratiunL：rgijDolls7TieDyeKitFr：rLceE-FriceDolls8AliceinWLinder13TlABoBByDolls9G1:dinoruUEDullB

40、oBByDolls10SleepingBeautyDullLATitOBILDolls11FetLuvingDullBoBByDolls12BeautifullHairDoilTheBernieKidsDuIIe13FlowerLuvingDullL：rgijDolls14Victori:tlDullhijUEeTheBernieKidsDolls15KitchenSetFr：rLceE-FriceFretendFlay1f:;ChildrenzE；edr口umLATitOEILFre七也丘Flav10.以下列格式顯示所有購物者的名字和他們的簡稱：(Initials,vFirstName,vL

41、astName),例如AngelaSmith的Initials為A.S。E-selectLEFTwF讓stName1)+?.?+LEFTvLastName,1as?Initials?,vFirstName,vLastNamefromShopper100%二結(jié)果消息Initi：dlsvFirEtN：djrievLaEtlT：diTie1A.SArLgelaSmith2E.JB:fb：=Lt-aJuIltleorL3B.WBettyWilli:dins4c.JC：fi：lJuneE5C.RCatherineRuberts6C.BCh：iflP5BrowrL7C.DChristupherDavie8

42、C.MCynizhiaMiller9D.WDanielWilson10D.MDavidMuore11.顯示所有玩具的平均價格，并舍入到整數(shù)。原價：操作之后1234-selectcast(ROUND(AVG(mToyRate),0)asint)as平均價格fromToys100%二結(jié)果消息平均價格11如12.顯示所有購買者(Shopper)和收貨人(Recipient)的名、姓、地址和所在城市，要求顯示結(jié)果中的重復(fù)記錄。Shopper表信息：ElselectvFirstNamevLastNamevAddress,cCityfromShopper100%二結(jié)果消息vFirEtlT：dirievLa

43、EtN：diTievAdilfSEEcCity1AiLgelaSmith16223Rail!：itlchCcmi-tWuudbridge2B:fb：=Lt_aJuIltleon227BeachAve.Eurixiyvale3BettyWilli:inis1TreadRoadVirginiaBeach4C：folJonee765-FurlingRuadApt112BaurLS5CatkerirLHRoBerts55LISBAquilirLHCurn-1S：TlJu5h6ChilesBrown7822S.G1itzyAvermeMaitl：rLil7Clu-istupherDavie489611t

44、hSTHillAvenue8CynthiaMiller98066VitaryStormStreetMuunFork9DanielWilsun4642Fwiph北alDriveBrecksville10DavidMuufh880SJovialityDrieS：tlFl：diTion11DeburahTaylor2199-FairfaxDriveLitertnn，rills12IlurLTLiArLilMfsurL79300r：TLgHSt.LasVegas13DoruthyThomas678East56thStreet-#12NewYork14ElizabethJackson598ApexAve

45、rLue#2SaintFaul15Fr：iTLceETurner2562EastwoodDenton16HelenWhiteFleetStreetFuintFle：E：dXLt17JamesHarris3456Mt.Reg：aleDrive1509Alex:iiLilfia18JeriXLiterM：rtin981276thStreetBruoklnTL19JessicaThumpSuTl565FettleSt.Ai-lirLgtun20JoKnG：rcia2234BKingHem-yDriveHarri“urg21JusephM：ii-tirLPz995RirnlStKeetLumaLind

46、a22JoycePhillips535Darwin.AvenueBridgeville23K：ferLRutinson343_AFluwersFerryRuadMaristta24KimberlyCl：fk79HollerV：dlleyRoadLittleRock25Lam-aRudrigumz3242LimestoneW豈M【arie11a26LirulaLewis1524FatagorLiaL：tlhFl：Tlu27LisaLee18927SLilstorLHAveIJSeattle28MargaretWalker405SEAden#101Fullman29M：fiaHall936Midl

47、andDrive?Ruchester30M：ryAllen1202HeathcliDriveUrb:ina-:1Mi門卜1：=1口1Ynnn:丁44FIi1hFi=：TTA11Mnri十：himhtv/SiLI+T亠*Recipient表信息：1ElselectvFirstNamevLastNamevAddress,cCityfromRecipient100%-二結(jié)果消息vFirEtlLdiTievLaEtlT：djrievAdilresscCity1Earljara.JuIltlson227BeachAve.EuTcnyv:J,p2CatherirLHRuberts5508AquilirLe

48、CourtS：tlJaee3CtcristopherDavie489611thSTHillAvernie4JerLTLierM：ftin981276thStreetEr0口klyn5Ear：=lt：JuIltleon227BeachAve.EuxLnyvale6DorLTLaAtliIhKsuTl79300r：TLgHSt.LasVegas7Lam-aRddriz3242LimestoneWaM：rie11a8MichpllpHhITliiTliImZ1353F；已;mlmNapervillegDavidMuore8808JuvialityDriveS：tlR：diTiun10EettyWil

49、liame1TreadRoadVirginiaEeach提取兩表相同信息方法一：12345670g101112|100%EselectdistinctShoppervFirstNameShoppervLastNameShoppervAddressShoppercCityfromShopperjoinRecipientonShoppervFirstNameRecipientvFirstNameandShoppervLastNameRecipientvLastNameandShoppervAddressRecipientvAddressandShoppercCityRecipientcCity二結(jié)

50、果消息vFirEtlTamevLastNamevAddresscCity1B：=Lt_baraJuIltleun27EeachAve.EuTLXiyvale2BettyWilli:inis1TreadRoadVirginiaEeach3CatherineRoBertE5508AquilirLHCuiji-tS：iTlJaee4ChristopherDavis4896UthSTHillAverLiiH5DavidMoore8808Juvi：alityDriveS：tlR：diTiun6IlurLTLaATLilprsurL7930OrangeSt.LasVegas7JeriiLierM：rtin

51、981276thStreetBrooklTL8Lam-aRodri:zniez3242LimestuneWie11a9MichelleHerrL：TLilez1353Re：dlmLesNaperville方法二：12345E-selectvFirstNamevLastNamevAddresscCityfromShopperintersectselectvFirstNamevLastNamevAddresscCityfromRecipient100%囲結(jié)果-J）消息vFirEtlT：dJTievLaEtN：iiTievAdilresscCity1B：=Lfb：=LraJ(jhlL呂uTl227B

52、hachAve.Eurinyvale2BettyWilli:dine1TreadRoadVirginiaBe：ch3CatkerirLHRuBerts55OSAquilirLHCurn-1S：dTLJu5a4ChristupherDavie489611thSTHillAverLue5DavidMoore8808JovialityDriveS：diLR：iitiun6DuriTLaArLilereotl7930OrangeSt.LasVegas7JeriXLiterM：ftin981276thStreetBruuklTL8Lam-aRudr1：iiei3242LimestuneW:迎ie11a9

53、MichslleHerrL：dXLilez1353Re：dlmL：d;eENaperville顯示沒有包裝的所有玩具的名稱。（要求用子查詢實現(xiàn)）首先分析數(shù)據(jù)庫關(guān)系圖OrderDetailcOrderNocToyldsiQtycGiftWrapcWrapperldvMessagemToyCost1WrappercWrapperldvD已scriptionmWrapperRateimPhotovWrapperlmgPath發(fā)現(xiàn)OrderDetail這張表可以將Toys和Wrapper這兩張表聯(lián)系起來selectvToyNamefromtoyswherecToyIdin(selectcToyIdfr

54、omorderdetailwherecWrapperIdisNULL)于是運用兩層子查詢：1-selectvToyName2fromToys3wherecToyldnotin4(selectcToyld5fromOrderDetail6wherecWrapperldin7(selectcWrapperld8fromWrapper100%二結(jié)果A消息vTi：iylT：dJTiHWaterCh：=LTLTLelSystemF:al-achutearidRucketSuperDeliigpLightShowL：diTipG1assDecoratiunTieHyeKitG1:dinordueDoilS

55、leepingBeautyDollyPetLuvingDoilFlowerLwingDullHureeryMyFirstFlastiligktElectrunicSafeX!30RacersSetDutlhRacerSpir：LZuomWay17LargmDuckISBabyMinnim顯示已收貨定單的定單號碼以及下定單的時間。（要求用子查詢實現(xiàn)）訂單號碼和下單時間在表Orders中1Eselect*fromOrders100%即結(jié)果消息1cUrderNi：iilUrderllatHcC：rtIdci0000012001-05-2000:00:00.000000002020000022001-

56、05-2000:00:00.0000000010收貨狀態(tài)在表Shipment中1Eselect*fromShipment100%囪結(jié)果J消息cOrilHi-NudShipmentDateelleliveryStatus10000012001-05-2300:00:00.000a20000022001-05-2300:00:00.00030000032001-05-2300:00:00.000E用一層子查詢實現(xiàn)123456EselectcOrderNodOrderDatefromOrderswherecOrderNoinselectcOrderNofromShipmentwherecDe1ive

57、ryStatus=?d?);100%-囪結(jié)果消息1cOrderNodOrderDate0000012001-05-2000:00:00.00020000022001-05-2000:00:00.00030000042001-05-2000:00:00.00040000052001-05-2100:00:00.00050000062001-05-2100:00:00.00060000082001-05-2200:00:00.00070000092001-05-2200:00:00.00080000102001-05-2200:00:00.00015.顯示一份基于Orderdetail的報表，包括

58、cOrderNo,cToyId和mToyCost,記錄以cOrderNo升序排列，并計算每一筆定單的玩具總價值。SELECTcOrderNo,cToyId,mToyCostFROMOrderDetailORDERBYcOrderNoCOMPUTESUM(mToyCost)BYcOrderNo;1EselectcOrderNocToyldmToyCost2mToyCost+siQtyas玩具總價值3fromOrderDetail4orderbycOrderNo100%結(jié)果J消息1cUrderNocToyldmToyCost玩具總價值00000100000739.9879.962000001000

59、00814.9914.99300000200001686.50173.00400000300001771.97215.91500000400003035.9935.99600000500000135.96143.84700000500002425.9925.99800000500003071.98143.96900000600001323.9947.981000000600001729.9829.981100000700000612.9912.991200000800002331.9863.961300000900001816.9916.991400001000002017.9835.9615

60、00001000002125.9925.9916.給id為000001玩具的價格增加$1。原表1Eselect*fromToys100%二結(jié)果J消息cToyldvTuylfamevTuyDescrip.cCateguryldmTuyRate1000001IRobByt.AgiantBlu.0019.992000002WaterC.Childrenen.00134.99操作后：1ElupdateToys2setmToyRate=mToyRate+13|wherecToyld=?OODOOT100%_J）消息（1行受影響）1Elselect*fromToys100%結(jié)杲消息cTuyldvTuyNa