数据库实验一

1、实验任务书（实验一）课程名称：数据库原理与技术实验报告要求：列出所有的SQL语句和源代码；程序要求有适当的注释；对数据完整性约束实施要求给出相应的测试用例实验报告提交电子档。实验内容：一：创建表和实施数据完整性1.运行给定的SQLScript，建立数据库GlobalToyz。日0ADMIRE(SQLServer12.0.413.0-ADMIREEric)日口轴库田a鑿漁据库田口舞库快曜日QGlobalToyz田口轴库关罢国日口表田1垂统表田口FileTables田dbo.Category口dbo.CountryE口dbo.OrderDetail田L3dbo.Orders田dbo.PickOfM

2、onth田dbo.RecipientUdbo.Shipment田Udbo.ShippingMode田Udbo.ShippingRate田dbo.ShopperUdbo_ShoppingCart田Udbo.ToyBrand田Udbo.Toys田口dbo.Wrapper田口视国田口同文词田口可编程性田口ServiceBrok已广田口菩储口安全性田IReportServer田ReportServerTempDB2了解表的结构。ToyBrandhi口口inqRteOniersPkkOfMcrrthOnerDctail3.利用系统预定义的存储过程sp_helpdb查看数据库的相关信息，例如所有者、大小、

3、创建日期等。execsp_helpdbG1obalTuyz)0%100%-3结果消息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利用系统预定义的存储过程sp_helpconstraint查看表中出现的约束(包括Primarykey,Foreignkey,checkconstraint,default,unique)1esecsp_helpconstraintCategory100%-结果爲消息10BjectN：diTiHfCategorycunstrairLt_i:ypecunstrairLt_rL：iJTie1jFRIMARYKEY(clustered)、Jct_fk(n/a)Tableisreferencedbyoreigjikey1GlobalToyz

5、.dto.Toys：FKToys_cCategoryl_21B61esecsp_helpconstfaintCountry100%-J结果消息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实施下面数据完整性规则：1（1）玩具的现有数量应在0到300之间EaltertabletoysaddconstraintsiToyQoh_sizechecksiToyQoh0andsiToyQoh=200_J1消息命令已成功宾成。2）玩具适宜的最低年龄缺省为1。altertabletoysaddconstraintdefLowerAgedefault1forsiLowe

7、rAge1EaltertabletoysaddconstraintnewsiLowerAge_minchecksiLowerAge，=110%j消息命令已成功完咸。6向表Orders中增加10条2016年1月的订单记录(注意Orders表与其它表的关联)。原表1Eselect*fromOrders.00%-二结果消息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结果消息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.创建一张表Orders_history,表的结构与Orders相同，将Orders表中2001年5月的订单记录复制到表Orders_history中。Orders表1Elselect*fromOrders100%二结果一、

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%二结果消息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二：查询、更新数据库1.显示属于California和Illinoi州的

27、顾客的名、姓和emailID。vFirstNamevLastNamevEmailld,cStatefromShopper/*wherecStatein(?California?Illinoi?);*/wherecState=?California?orcState=?Illinois?100%J结果消息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%二结果消息1cOrderITucShupperldmTi：it：dlCi：Et00000700000816.99200000900001026.99300000400000640.99400000800000953.98500000100000262.22600001000000367.97700000300000783.97800000200000596.50900000600001297.9710000005000002231.683.显示在orderDetail表中vMessage为空值的行。1Eselect*fromOrderDetailwherevMessageisnul1:|

31、100%结果消息cUrilerNucToylJsiQtycGiftttrapcWrapperidvMeeeageFTlToHZuEt10000010000072NNULLNULL39.9820000030000173ITNULLNULL71.9730000070000061NNULLNULL12.994.显示玩具名字中有“Racer”字样的所有玩具的基本资料。1Eselect*fromToyswh|erevToyNamelike?%Racer%?;100%二结果一、消息cToylJvTuyN：iiTiHvToyDescriptiutlcC1000027;X!jLIRacersSetThefas

32、t-fiacedacticmracingtrackistheultim.oc2000028DijtlhRacerAsetofiIiitlsBuggieewitharacingtrack.oc5.列出表PickofMonth中的所有记录，并显示中文列标题。1select2cToyldas玩具ID，3siMonthas，保质期，4iYearas*出厂年份*，5iTotalSoldas总誚售里，6fromPickOfMonth100%二结果消息玩具M保质期出厂年悄总誚售重10000011200010002000001220001230300000262000300040000035200020005

33、00000372000567060000053200040007000007420005000800000782000234090000119200056001000001232001250011000015220013100120000201020002300130000211120004500140000241200132001500002612200065006.根据2000年的玩具销售总数，显示“PickoftheMonth”玩具的前五名玩具的ID。SELECTTOP5cToyId,sum(iTotalSold)FROMPickOfMonthWHEREiYear=2000groupby

34、ctoyidOrderbysum(iTotalSold)desc1234Eselecttop5*|fromPickOfMonthwhereiYear=2000orderbyiTotalSolddesc:100%J结果消息cTuyldsiMonthiYeariTut：=l1Suld10000261220006500200000372000567030000119200056004000007420005000500002111200045007.根据OrderDetail表，显示玩具总价值大于50的定单的号码和玩具总价值。SELECTOrderNumber=cOrderNo,TotalCosto

35、fToyforanOrder=sum(mToyCost)fromorderdetailgroupbycOrderNohavingsum(mToyCost)501-selectcToyldmToyCost2fromOrderDetail3wheremToyCost50100%结果A消息cTuyldmT口yCoet?00001686.500001771.9700003071.988.显示一份包含所有装运信息的报表，包括：OrderNumber,ShipmentDate,ActualDeliveryDate,DaysinTransit.(提示：DaysinTransit=ActualDelivery

36、Date-ShipmentDate)12EselectasJDaysinTransit/cOrderNo,dShipmentDatedActualDe1iveryDatedatediff(DAY,dShipmentDatedAutualDe1iveryDatefromShipment100%二结果消息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.显示所有玩具的名称、商标和种类(ToyName,Brand,Category)。1HselectvT日jtl已3cBit日ndN日jtl已?cCategutryfroiTLTuis?T025r：iiid3CategutrywhereToys.cBr：iiidId=T03!r:iiidncBr:iiidldandT033ncCateg0dCateg0ryacCateg0d:100%-J结果消息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%二结果消息Initi：dlsvFirEtN：djrievLaEtlT：diTie1A.SArLgelaSmith2E.JB:fb：=Lt-aJuIltleorL3B.WBettyWilli:dins4c.JC：fi：lJuneE5C.RCatherineRuberts6C.BCh：iflP5BrowrL7C.DChristupherDavie8

42、C.MCynizhiaMiller9D.WDanielWilson10D.MDavidMuore11.显示所有玩具的平均价格，并舍入到整数。原价：操作之后1234-selectcast(ROUND(AVG(mToyRate),0)asint)as平均价格fromToys100%二结果消息平均价格11如12.显示所有购买者(Shopper)和收货人(Recipient)的名、姓、地址和所在城市，要求显示结果中的重复记录。Shopper表信息：ElselectvFirstNamevLastNamevAddress,cCityfromShopper100%二结果消息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%-二结果消息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二结

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

51、981276thStreetBrooklTL8Lam-aRodri:zniez3242LimestuneWie11a9MichelleHerrL：TLilez1353Re：dlmLesNaperville方法二：12345E-selectvFirstNamevLastNamevAddresscCityfromShopperintersectselectvFirstNamevLastNamevAddresscCityfromRecipient100%囲结果-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显示没有包装的所有玩具的名称。（要求用子查询实现）首先分析数据库关系图OrderDetailcOrderNocToyldsiQtycGiftWrapcWrapperldvMessagemToyCost1WrappercWrapperldvD已scriptionmWrapperRateimPhotovWrapperlmgPath发现OrderDetail这张表可以将Toys和Wrapper这两张表联系起来selectvToyNamefromtoyswherecToyIdin(selectcToyIdfr

54、omorderdetailwherecWrapperIdisNULL)于是运用两层子查询：1-selectvToyName2fromToys3wherecToyldnotin4(selectcToyld5fromOrderDetail6wherecWrapperldin7(selectcWrapperld8fromWrapper100%二结果A消息vTi：iylT：dJTiHWaterCh：=LTLTLelSystemF:al-achutearidRucketSuperDeliigpLightShowL：diTipG1assDecoratiunTieHyeKitG1:dinordueDoilS

55、leepingBeautyDollyPetLuvingDoilFlowerLwingDullHureeryMyFirstFlastiligktElectrunicSafeX!30RacersSetDutlhRacerSpir：LZuomWay17LargmDuckISBabyMinnim显示已收货定单的定单号码以及下定单的时间。（要求用子查询实现）订单号码和下单时间在表Orders中1Eselect*fromOrders100%即结果消息1cUrderNi：iilUrderllatHcC：rtIdci0000012001-05-2000:00:00.000000002020000022001-

56、05-2000:00:00.0000000010收货状态在表Shipment中1Eselect*fromShipment100%囱结果J消息cOrilHi-NudShipmentDateelleliveryStatus10000012001-05-2300:00:00.000a20000022001-05-2300:00:00.00030000032001-05-2300:00:00.000E用一层子查询实现123456EselectcOrderNodOrderDatefromOrderswherecOrderNoinselectcOrderNofromShipmentwherecDe1ive

57、ryStatus=?d?);100%-囱结果消息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%结果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%二结果J消息cToyldvTuylfamevTuyDescrip.cCateguryldmTuyRate1000001IRobByt.AgiantBlu.0019.992000002WaterC.Childrenen.00134.99操作后：1ElupdateToys2setmToyRate=mToyRate+13|wherecToyld=?OODOOT100%_J）消息（1行受影响）1Elselect*fromToys100%结杲消息cTuyldvTuyNa