實(shí)驗(yàn)五數(shù)據(jù)查詢復(fù)雜查詢一. 實(shí)驗(yàn)?zāi)康?. 掌握SQLServe查詢語(yǔ)句的基本語(yǔ)法2. 熟練使用SQL的Select語(yǔ)句對(duì)多表進(jìn)行查詢3. 熟練掌握并運(yùn)用SQLServer所提供的函數(shù)4. 熟練使用SQL語(yǔ)句進(jìn)行復(fù)雜的連接操作二、實(shí)驗(yàn)環(huán)境(實(shí)驗(yàn)的軟件.硬件環(huán)境)硬件：PC機(jī)軟件:SQL2000三、實(shí)驗(yàn)指導(dǎo)說(shuō)明請(qǐng)復(fù)習(xí)相關(guān)的查詢知識(shí)點(diǎn)并完成如下內(nèi)容。四、實(shí)驗(yàn)內(nèi)容1.在訂單數(shù)據(jù)庫(kù)oiderDB中，完成如下的查詢：(1) 用子查詢查詢員工“張小娟”所做的訂單信息。(2) 查詢沒(méi)有訂購(gòu)商品的且在北京地區(qū)的客戶編號(hào)，客戶名稱和郵政編碼，并按郵政編碼降 序排序。(3) 查詢訂購(gòu)了 “32MDRAM”商品的訂單編號(hào)，訂貨數(shù)量和訂貨單價(jià)。(4) 查詢與員工編號(hào)“E2008005”在同一個(gè)部門的員工編號(hào)，姓名，性別，所屬部門。(5) 查詢既訂購(gòu)了 P20050001商品，又訂購(gòu)了 P20070002商品的客戶編號(hào)，訂單編號(hào)和訂單 金額(6) 查詢沒(méi)有訂購(gòu)“52倍速光驅(qū)”或“17寸顯示器”的客戶編號(hào)，客戶名稱。(7) 查詢訂單金額最高的訂單編號(hào)，客戶姓名，銷售員名稱和相應(yīng)的訂單金額。(8) 查詢訂購(gòu)了 “52倍速光驅(qū)”商品的訂購(gòu)數(shù)量，訂購(gòu)平均價(jià)和訂購(gòu)總金額。(9) 查詢訂購(gòu)了 “52倍速光驅(qū)”商品且訂貨數(shù)量界于24之間的訂單編號(hào)，訂貨數(shù)量和訂貨 金額0(10) 在訂單主表中查詢每個(gè)業(yè)務(wù)員的訂單數(shù)量(11) 統(tǒng)計(jì)在業(yè)務(wù)科工作且在1973年或1967年出生的員工人數(shù)和平均工資。(12) 在訂單明細(xì)表中統(tǒng)計(jì)每種商品的銷售數(shù)量和金額，并按銷售金額的升序排序輸出。(13) 統(tǒng)計(jì)客戶號(hào)為“C20050001”的客戶的訂單數(shù)，訂貨總額和平均訂貨金額(14) 統(tǒng)計(jì)每個(gè)客戶的訂單數(shù)，訂貨總額和平均訂貨金額。(15) 查詢訂單中至少包含3種(含3種)以上商品的訂單編號(hào)及訂購(gòu)次數(shù)，且訂購(gòu)的商品數(shù)量在3件(含3件)以上。(16) 查找訂購(gòu)了 “32MDRAM”的商品的客戶編號(hào)，客戶名稱，訂貨總數(shù)量和訂貨總金額。(17) 查詢每個(gè)客戶訂購(gòu)的商品編號(hào)，商品所屬類別，商品數(shù)量及訂貨金額，結(jié)果顯示客戶名稱，商品所屬類別，商品數(shù)量及訂貨金額，并按客戶編號(hào)升序和按訂貨金額的降序排序輸出。(18) 按商品類別查詢每類商品的訂貨平均單價(jià)在280元(含280元)以上的訂貨總數(shù)量，訂 貨平均單價(jià)和訂貨總金額。(19) 查找至少有2次銷售的業(yè)務(wù)員名稱和銷售日期。(20) 查詢銷售金額最大的客戶名稱和總貨款額(21) 查找銷售總額小于5000元的銷售員編號(hào)，姓名和銷售額(22) 查找至少訂購(gòu)了 3種商品的客戶編號(hào)，客戶名稱，商品編號(hào)，商品名稱，數(shù)量和金額。(23) 查找同時(shí)訂購(gòu)了商品為“P20070002”和商品編號(hào)為“P20070001”的商品的客戶編號(hào)，客戶姓名，商品編號(hào)，商品名稱和銷售數(shù)量，按客戶編號(hào)排序輸出。(24) 計(jì)算每一商品每月的銷售金額總和，并將結(jié)果首先按銷售月份然后按訂貨金額降序排序 輸出。(25) 查詢訂購(gòu)了 “鍵盤”商品的客戶姓名，訂貨數(shù)量和訂貨日期(26) 查詢每月訂購(gòu)“鍵盤”商品的客戶名稱。(27) 查詢至少銷售了 5種商品的銷售員編號(hào)，姓名，商品名稱，數(shù)量及相應(yīng)的單價(jià)，并按銷售員編號(hào)排序輸出。(28) 查詢沒(méi)有訂購(gòu)商品的客戶編號(hào)和客戶名稱。(29) 查詢至少包含了“世界技術(shù)開(kāi)發(fā)公司”所訂購(gòu)的商品的客戶編號(hào)，客戶名稱，商品編號(hào),商品名稱，數(shù)量和金額。五. 實(shí)驗(yàn)步驟請(qǐng)完成實(shí)驗(yàn)內(nèi)容，并寫(xiě)出具體的實(shí)驗(yàn)步驟(1) 用子查詢查詢員工“張小娟"所做的訂單信息。 selectorderMaster?fiomemployee,orderMastei-whereorderMaster.SaleNo=employeeNoandemplo veeNamemJ(selectemployee.employeeNamefLomemployeewhereemployeeName= 長(zhǎng) < 娟')(2) 查詢沒(méi)有訂購(gòu)商品的且在北京地區(qū)的客戶編號(hào)，客戶名稱和郵政編碼，并按郵政編碼降序排序。selectdistinctcustomer.CustomerNo,customer.customerName,customer.ZipfromcustomeiorderMa steivdierecu stonier.ciisTomeiNoiiotuKselectojrderMastei.ciistomeFNofromoiderMasteQaiidciistome 匸 addr=北京市 'orderbycustomerZipdesc(3) 查詢訂購(gòu)了-32MDRAM-商品的訂單編號(hào)，訂貨數(shù)量和訂貨單價(jià)。selectojrdeiDetail.OrderNoQTderDetail.Qiy.oTderDetaiLP 門 cefiomordeiDetaiLpTodiictwheieoideT Detail.ProductNo=product.ProductNoandProductName=132MDRAlVr(4) 查詢與員工編號(hào)“E2008005”在同一個(gè)部門的員工編號(hào)，姓名，性別，所屬部門。tM,then,®'when'F'tlien'女'end,employee.Depairmentfromemployeewhereemployee.Depaftment=(selectemployee.Depaftmentfio memployeewliereemployee.employeeNo-E2008005,)(5) 查詢既訂購(gòu)了 P20050001商品，又訂購(gòu)了 P20070002商品的客戶編號(hào)，訂單編號(hào)和訂單金額selectciistoinei.ciistomemo.ojrdemiastei.ojrde 門 lo.oTdeimaste 匸 oideisiimfromoTde 門 naste 匚 customer; ofderdetailwliefeeustomeicustomenio=ofdennasteEcustomenioandordermasteEordeiiio=ordefdet ail.ordenioandoiderdetail.pioductno=,P2007002,mtersectP20050011(6) 查詢沒(méi)有訂購(gòu)“52倍速光驅(qū)”或T7寸顯示器”的客戶編號(hào)，客戶名稱。selectdistinctcustomeicustomenioxustomeicustomernamefLomcustomei;ofderdetaiLofdermastei productdiereorderdetail.ordenio=ofdermasteiofdenioandordermaster.customenio=customef.customenioandofderdetail.productno=product.productnoandproduct.productnamenotin(,52 倍速光 9區(qū)T17寸顯示器)(7) 查詢訂單金額最高的訂單編號(hào)，客戶姓名，銷售員名稱和相應(yīng)的訂單金額。selectojrdennasteEordejriJLO.ciistoiner.ciisTome 門 lame.employee.einployeename.oTdeimaste 匸 ordeisu mfromeinployee.ojrdennastecciistoinefwheieeinployee.employeeiiOhOJLde 門 nastei.saleiioaiidciisto mei.ciistoine 門 io=ojLdeimastefci】stomeinoaiidojLdeimastefOJLdeisiim=(selectMAX(OTdei*masteFOj：d ersum)fromordennaster)(8) 查詢訂購(gòu)了“52倍速光驅(qū)”裔品的訂購(gòu)數(shù)量，訂購(gòu)平均價(jià)和訂購(gòu)總金額。selectSUM(orderdetail.qty)as,訂購(gòu)數(shù)量AVG(ordennasteiordersum)as,訂購(gòu)平均價(jià)；SUM(OTdermastejLOTdersuni)as 訂購(gòu)總金額£omoideTdetaiLojrdemiastei;pjrodiictwheieojLdeKleailOKlemo=ordennasTeiOTdeinoaiidojrdeTdetai=,52 倍速光驅(qū),(9) 查詢訂購(gòu)了“52倍速光驅(qū)”商品且訂貨數(shù)量界于、4之間的訂單編號(hào)，訂貨數(shù)量和訂貨 金額。selectojrdeTdeTaiLojrdewLO.OTdeideuuLqty.ojrde 門 nastei.ojLdeisiiinfroinoKieimastecoTderdelaiLpiodii ctwhereofderdetail.orderiio=ordennaster.ordenioandpiodiict.productno=ofdefdetail.pioductnoandproduct. pfoductname=,52 倍速光 ,andordefdetail.qtybetweeii2and4(10) 在訂單主表中查詢每個(gè)業(yè)務(wù)員的訂單數(shù)量selectoidermasteisaleno.COUNT(*)astiJg$W,fLomordermastefgroupbysaleno(11) 統(tǒng)計(jì)在業(yè)務(wù)科工作且在年或年出生的員工人數(shù)和平均工資。selectCOUNT(*)as'員工人數(shù),AVG( employee, salaiyjas'平均工資坨oineinployee(12) 在訂單明細(xì)表中統(tǒng)計(jì)每種商品的銷售數(shù)量和金額，并按銷售金額的升序排序輸出。selectoidefdetail.productno.SUM(ofdefdetail.qty)as,銷售數(shù)量精心整理SUM(orderdetail.price*orderdetail.qty)asl 金額,fiomorderdetailgroupbyorderdetail.productnoofderbySUM(orderdetail.price*orderdetail.qty)(13) 統(tǒng)計(jì)客戶號(hào)為“C20050001”的客戶的訂單數(shù)，訂貨總額和平均訂貨金額selectcustomer.customenio.COUNT(ofdefdetail.orderno)as,ngJ:SUM(oj?demiasteFoi:deisiim)as'訂貨總額AVG(ofdermasteiofdersum)as,平均訂單金額'fromoideTdetaiLojrdemiastmiisTomerwhefeoKieKlegil.oKiemohOKleimastetordejLnoimdojrdemia stef.customenio=customer.customenioandcustomer.customenio=,C2005001,groupbycustomer.cus tomemo(14) 統(tǒng)計(jì)每個(gè)客戶的訂單數(shù)，訂貨總額和平均訂貨金額。selectcustomer.customenio.COUNT(ofdefdetail.orderno)as,ngJ:SUM(oj?demiasteFoi:deisiim)as'訂貨總額AVG(ofdermasteiofdersum)as,平均訂單金額(15) 查詢訂單中至少包含種(含種)以上商品的訂單編號(hào)及訂購(gòu)次數(shù)，且訂購(gòu)的商品數(shù)量在件(含件)以上。selectordenio,count(*)fromorderdetailwhereordeniom(selectorderdetail.ordenio fiomofderdetailgroupbyoidenioliavingCOUNT(ofdenio)>=3)groupbyorderno(16) 查找訂購(gòu)了32MDRAM”的商品的客戶編號(hào)，客戶名稱，訂貨總數(shù)量和訂貨總金額。selectciistomer.customenioxustomeniame.SUM(qty)asiniS/WSUM(ordennaster.ordersum)as,訂貨總金額,fromprodiic 匸 ordeideaiLciisi:omei;OTdeimasWiwhereciistoineiciisToinejriLO=ojrdermasteiciisi:ome 門 1 oandordermaster.oiderno=orderdetail.ordenioaiidproduct.productiio=orderdetail.productnoandproduct.productname-32M DRAM*groupbycu stonier, cu stomernoxustomemame(17) 查詢每個(gè)客戶訂購(gòu)的商品編號(hào)，商品所屬類別，商品數(shù)量及訂貨金額，結(jié)果顯示客戶名稱，商品所屬類別，商品數(shù)量及訂貨金額，并按客戶編號(hào)升序和按訂貨金額的降序排序輸出。selectcustomemame,product.productno.product.productclass,orderdetail.qty,orderdetail.price*ord erdetail.qtv fioinordeTdei:aiLpjLodiict;ciisi:omeTQTdeimasWiwhefeciistoineiciisToinejriio=ordermasteiciisK)mem oandoidemiaster.oidenio=orderdetail.ofdenioandorderdetail.productno=product.pioductnoorderbycust omer.customemoasc.orderdetail.pnce*ordefdetail.qtydesc(18) 按商品類別查詢每類商品的訂貨平均單價(jià)在元(含元)以上的訂貨總數(shù)量，訂貨平均單價(jià)和訂貨總金額。selectCOUNT(qty)as'訂貨總數(shù) MAVG(pnce)as,iJ 貨評(píng)價(jià)單價(jià)',SUM(ordersum)as'訂貨總金額omproduct.oidermastera,ofderdetailbwherea.ordenio=b.ordenioandpfoduct.pfoductno=b.prodiictnogroupbyproduct.pfodiic tclasshavingAVG(pnce)>=2 80(19) 查找至少有次銷售的業(yè)務(wù)員名稱和銷售日期。selectemployee.employeename.ofdennaster.oiderdatefromemployee.ordeniiasterwhereemployee. employeeno=ordermastef.salenoandemployeenoin(selectsalenofiwiordeniiasteigroupbysalenohavingCOUNT(*)>=2)order byemployeename(20) 查詢銷售金額最大的客戶名稱和總貨款額selectcustomeEcustomeniameanax(a.b)fiomciistomeiofdermastei(selecttoplordermaster.custom eino.SUM(OKle 門 nastetoTdeisimJas'iyfLomoTdejrmasteEggiipbyciisKmie 門 io)awherecustomeEciistomenio=ordermaster.customenioanda.ciistomerno=customer.customeniogrou pbycustomeniameselecteustomeniame,MAX(b.a)fiomcustomei;(selectcustomenio,sum(ordersum)afi-omordermaster groupbycu stomemo)bgroupbycu stomemamewherecustomer. cu stomemo=b. cu stomernogroupbycu stomemame(21) 查找銷售總額力于元的銷售員編號(hào)，姓名和銷售額selecteiiiployee.employeeno,employeename,afLomemployee.(selectsaleiio.SUM(ofdersum)afLom ordemiastergroupbysaleno)bwhereb.saleno=employeenoanda<5000(22) 查找至少訂購(gòu)了種商品的客戶編號(hào)，客戶名稱，商品編號(hào)，商品名稱，數(shù)量和金額。精心整理selectcustomer.customemo,customer.customeniame,product.productno,product.productiiame,orde rdetail.qty,ofderdetail.pnceordeidetail.qtyas,fiomcustomeipfoduct.ordefdetaiLordermasteivTherecustomer.customeiiio= ofdennaster.ciistonienioandofdennaster.ordenio=orderdetail.ofdernoandorderdetail.pioductno=pr oduct.productnoandciistomer.customernom(selectciistomer.customeniofLomcustomeipfoduct.ofdefdetaiLordermasteivdierecustomer.customemo=ofdennaster.ciistonienioandofdennaster.ordenio=orderdetail.ofdernoandorderdetail.pioductno=pr oduct.productnogroupbycustomer.customernohavingcount( customer. customenio)>=3)orderbycustomenio(23) 查找同時(shí)訂購(gòu)了商品為“P20070002”和商品編號(hào)為“P20070001”的商品的客戶編號(hào)，客戶姓名，商品編號(hào)，商品名稱和銷售數(shù)量，按客戶編號(hào)排序輸出。selectcustomer.customenioxustomeniame.pfoduct.pfoductno.productnameordefdetail.qtyfiomcu stomer.product,ojLdejrdetaiLoTdeimasteiwhereciistoineEciistoinejriiOhOKlerinasteT.ciistomeELioaiidoKieimastei. order no=orderdeta ll.oidernoandorderdetail.pfoductno=pfoduct.productnoandcustomer.customernoin (selectcustomer.customeniofromcustomeiproduct.ofderdetaiLofdermasterwhereciistomeECustomerno=oidermasteicustomenioandofdermastei order no=orderdeta ll.oidernoandorderdetail.productno=pioduct.productnoandproduct.productno=lP2007002,mtersectselectcustomer.customeniofLomcustomeEproduct, ofderdetaiLofdermasterwhereciistomeEcustomerno=oidermasteicustomenioandofdermastei order no=orderdeta ll.oidernoandofderdetail.pioductno=pfoduct.pfoductnoandpfoduct.productno=,P200700r)orderbycustomer. customemo(24) 計(jì)算每一商品每月的銷售金額總和，并將結(jié)果首先按銷售月份然后按訂貨金額降序排序輸出。selectojrdemiasteiciisk)inemoQKlei:masi:eiOTdeKlaesi】m(oideFsi】m)as* 肖售金額總和'fromoidermasteFggiipbyordemiastei.ciislomemo.oTdemiasTei.oTdeKlateoTdeTbyojrdemiasteiojrdeTdae* 肖售金額總 和 desc(25) 查詢訂購(gòu)了“鍵盤”商品的客戶姓名，訂貨數(shù)量和訂貨日期selectciistomei.ciistomemameQrdejLdelaiLqty.oKleifmastei.oKleKlatefroinciistomeaoKlejrdeTaiLoKle rmastei;product4ierecustomeEcustomenio=oidermasteEcustomenioaiidofdeidetail.ofderno=ordermaster. ordenioandproduct.productno=orderdetail.productnoaiidproductiiame=lfi,(26) 查詢沒(méi)有訂購(gòu)“鍵盤”商品的客戶名稱。selectdistinctcustomeniamefiomcustomeivTherecustomeniamenotin(selecidishnctciistoine 門 laineflomciistoine 匚 ojLdermasteT.ordejrdeTaiLpjLodiicrwheTe ofdennaster.ciistomenio=ciistomef.customenioandofdennaster.ordenio=ofderdetail.ordernoandpr oduct.productno=ofderdetail.productnoandproductname=,S,)(27) 查詢至少銷售了種商品的銷售員編號(hào)，姓名，商品名稱，數(shù)量及相應(yīng)的單價(jià)，并按銷售員編號(hào)排序輸出。selectemployee.employeeno,employeename,productname,orderdetail.qty,orderdetail.pricefromem ployee,product,ordefdetaiLofdermasterwhereemployee.employeeno=ordermaster.salenoandofdennaster.ofdenio= orderdetail.orderno(selectemployee.employeenofiomemployee.product. ordefdetaiLofdermasterwhereemployee.employeeno=ordermaster.salenoandofdennaster.ofdenio= orderdetail.ordernoandofderdetail.pioductno=prodiict.pfoductnogroupbyemployee.employeenohavingCOUNT(empl oyee.employeeno)>=5) orderbyemployeeno(28) 查詢沒(méi)有訂購(gòu)商品的客戶編號(hào)和客戶名稱。selecta.ciistomernofromcustomerawherenotexists( selecfromojLdeimasterwheTea.ciistoine 門 io=oi:demiaserciisromenio)(29) 查詢至少包含了“世界技術(shù)開(kāi)發(fā)公司”所訂購(gòu)的商品的客戶編號(hào)，客戶名稱，商品編號(hào)，商品名稱，數(shù)量和金額Oselectcustomer.customenioxustomeniame.pfoduct.pfoductno.productname.ordeidetail.qty.qty*pn cefromordemiaster.ordenio=orderdetail.ordenioandorderdetail.productno=product.pioductiioandcustomer.customernamein selectcustomeniamefromcustomefwherenotexists(select*fLom(selectcustomeniame.ofdeidetail.productnofiomcustomercustoenHb.ofdeidetaiLofder masteiproductproductbwheTeciistoemrb.ciistoineniame世界技術(shù)開(kāi)發(fā)公司,andcustoenub.customenio=ordermaster.customernoandofdennaster.oidenio=ordefdetail.ofdenioandoidefdetail.pfoductno=pfoductb.productno)awhere notexists(select*from(selectcustomeniame.ofderdetail.pfoductnofiomcustomercustoenub.ofdefde taiLordermasteiproductproductbwherecustoenKb.customerno=ordermaster.customenioandoidermasteiofderno=oiderdetail.ordeni oandorderdetail.productno=productb.productno)bwherecustomer.customeiiiame=b.customeniameandb.productno=a.pfoductno)六、思考題：1. 存在量詞與集合運(yùn)算m、連接運(yùn)算和全程量詞之間的關(guān)系如何？他們可以互相替換嗎？給 出你的理由2. Wheie子句和having子句都是用于指定查詢條件的，請(qǐng)區(qū)別它們的異同？用實(shí)例說(shuō)明。3. 在分組聚集操作中，為什么在查詢列中，除了聚集函數(shù)運(yùn)算外，其它表達(dá)式必須包含在gioupbv子句中?七、總結(jié)（實(shí)驗(yàn)過(guò)程的體會(huì)、心得和實(shí)驗(yàn)教與學(xué)之間還需改進(jìn)的內(nèi)容）