實(shí)驗(yàn)五數(shù)據(jù)查詢

《實(shí)驗(yàn)五數(shù)據(jù)查詢》由會(huì)員分享，可在線閱讀，更多相關(guān)《實(shí)驗(yàn)五數(shù)據(jù)查詢（11頁(yè)珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

1、實(shí)驗(yàn)五數(shù)據(jù)查詢——復(fù)雜查詢 一. 實(shí)驗(yàn)?zāi)康? 1. 掌握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)，客戶名稱和郵政編碼，并按郵政

2、編碼降 序排序。 (3) 查詢訂購(gòu)了 “32MDRAM”商品的訂單編號(hào)，訂貨數(shù)量和訂貨單價(jià)。 (4) 查詢與員工編號(hào)“E2008005”在同一個(gè)部門(mén)的員工編號(hào)，姓名，性別，所屬部門(mén)。 (5) 查詢既訂購(gòu)了 P20050001商品，又訂購(gòu)了 P20070002商品的客戶編號(hào)，訂單編號(hào)和訂單 金額 (6) 查詢沒(méi)有訂購(gòu)“52倍速光驅(qū)”或“17寸顯示器”的客戶編號(hào)，客戶名稱。 (7) 查詢訂單金額最高的訂單編號(hào)，客戶姓名，銷(xiāo)售員名稱和相應(yīng)的訂單金額。 (8) 查詢訂購(gòu)了 “52倍速光驅(qū)”商品的訂購(gòu)數(shù)量，訂購(gòu)平均價(jià)和訂購(gòu)總金額。 (9) 查詢訂購(gòu)了 “52倍速光驅(qū)”商品且訂貨數(shù)量界于2?

3、4之間的訂單編號(hào)，訂貨數(shù)量和訂貨 金額0 (10) 在訂單主表中查詢每個(gè)業(yè)務(wù)員的訂單數(shù)量 (11) 統(tǒng)計(jì)在業(yè)務(wù)科工作且在1973年或1967年出生的員工人數(shù)和平均工資。 (12) 在訂單明細(xì)表中統(tǒng)計(jì)每種商品的銷(xiāo)售數(shù)量和金額，并按銷(xiāo)售金額的升序排序輸出。 (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)，客戶名稱，訂貨總

4、數(shù)量和訂貨總金額。 (17) 查詢每個(gè)客戶訂購(gòu)的商品編號(hào)，商品所屬類(lèi)別，商品數(shù)量及訂貨金額，結(jié)果顯示客戶名 稱，商品所屬類(lèi)別，商品數(shù)量及訂貨金額，并按客戶編號(hào)升序和按訂貨金額的降序排序輸出。 (18) 按商品類(lèi)別查詢每類(lèi)商品的訂貨平均單價(jià)在280元(含280元)以上的訂貨總數(shù)量，訂 貨平均單價(jià)和訂貨總金額。 (19) 查找至少有2次銷(xiāo)售的業(yè)務(wù)員名稱和銷(xiāo)售日期。 (20) 查詢銷(xiāo)售金額最大的客戶名稱和總貨款額 (21) 查找銷(xiāo)售總額小于5000元的銷(xiāo)售員編號(hào)，姓名和銷(xiāo)售額 (22) 查找至少訂購(gòu)了 3種商品的客戶編號(hào)，客戶名稱，商品編號(hào)，商品名稱，數(shù)量和金額。 (23) 查找同時(shí)

5、訂購(gòu)了商品為“P20070002”和商品編號(hào)為“P20070001”的商品的客戶編號(hào)， 客戶姓名，商品編號(hào)，商品名稱和銷(xiāo)售數(shù)量，按客戶編號(hào)排序輸出。 (24) 計(jì)算每一商品每月的銷(xiāo)售金額總和，并將結(jié)果首先按銷(xiāo)售月份然后按訂貨金額降序排序 輸出。 (25) 查詢訂購(gòu)了 “鍵盤(pán)”商品的客戶姓名，訂貨數(shù)量和訂貨日期 (26) 查詢每月訂購(gòu)“鍵盤(pán)”商品的客戶名稱。 (27) 查詢至少銷(xiāo)售了 5種商品的銷(xiāo)售員編號(hào)，姓名，商品名稱，數(shù)量及相應(yīng)的單價(jià)，并按銷(xiāo) 售員編號(hào)排序輸出。 (28) 查詢沒(méi)有訂購(gòu)商品的客戶編號(hào)和客戶名稱。 (29) 查詢至少包含了“世界技術(shù)開(kāi)發(fā)公司”所訂購(gòu)的商品的客戶編

6、號(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 veeNamem J (selectemployee.employeeNamefLomemployeewhereemployeeName=^ 長(zhǎng) <]\ 娟') (2) 查詢沒(méi)有訂購(gòu)商品的且在北京地區(qū)的客戶編號(hào)，客戶名稱和郵政編碼，并按郵政編碼 降序排序。

7、 selectdistinctcustomer.CustomerNo,customer.customerName,customer.Zipfromcustomei\orderMa steivdierecu stonier. ciisTomeiNoiiotuKselectojrderMastei.ciistomeFNofromoiderMasteQaiidciistome 匸 addr=‘北京市 'orderbycustomerZipdesc (3) 查詢訂購(gòu)了-32MDRAM-商品的訂單編號(hào)，訂貨數(shù)量和訂貨單價(jià)。 selectojrdeiDetail.OrderNoQTderDetai

8、l.Qiy.oTderDetaiLP 門(mén) cefiomordeiDetaiLpTodiictwheieoideT Detail.ProductNo =product.ProductNoandProductName=132MDRAlVr (4) 查詢與員工編號(hào)“E2008005”在同一個(gè)部門(mén)的員工編號(hào)，姓名，性別，所屬部門(mén)。 tM,then,? 'when'F'tlien'女'end, employee.Depairmentfromemployeewhereemployee.Depaftment=(selectemployee.Depaftmentfio memployeewliere

9、 employee.employeeNo-E2008005,) (5) 查詢既訂購(gòu)了 P20050001商品，又訂購(gòu)了 P20070002商品的客戶編號(hào)，訂單編號(hào)和訂 單金額 selectciistoinei.ciistomemo.ojrdemiastei.ojrde 門(mén) lo.oTdeimaste 匸 oideisiimfromoTde 門(mén) naste 匚 customer; ofderdetailwliefeeustomei\customenio=ofdennasteEcustomenioandordermasteEordeiiio=ordefdet ail.ordenioando

10、iderdetail.pioductno=,P2007002, mtersect P20050011 (6) 查詢沒(méi)有訂購(gòu)“52倍速光驅(qū)”或T7寸顯示器”的客戶編號(hào)，客戶名稱。 selectdistinctcustomei\customenioxustomei\customernamefLomcustomei;ofderdetaiLofdermastei\ product^diereorderdetail.ordenio=ofdermastei\ofdenioandordermaster.customenio=customef.cust omenioandofderdetail.pr

11、oductno=product.productnoandproduct.productnamenotin(,52 倍速光 9區(qū) T17寸顯示器) (7) 查詢訂單金額最高的訂單編號(hào)，客戶姓名，銷(xiāo)售員名稱和相應(yīng)的訂單金額。 selectojrdennasteEordejriJLO.ciistoiner.ciisTome 門(mén) lame.employee.einployeename.oTdeimaste 匸 ordeisu mfromeinployee.ojrdennastecciistoinefwheieeinployee.employeeiiOhOJLde 門(mén) nastei.saleiio

12、aiidciisto mei.ciistoine 門(mén) io=ojL?deimastef?ci】stomeinoaiidojL?deimastef?OJL?deisiim=(selectMAX(OTdei*masteF?Oj：d ersum)fromordennaster) (8) 查詢訂購(gòu)了“52倍速光驅(qū)”裔品的訂購(gòu)數(shù)量，訂購(gòu)平均價(jià)和訂購(gòu)總金額。 selectSUM(orderdetail.qty)as,訂購(gòu)數(shù)量\AVG(ordennastei\ordersum)as,訂購(gòu)平均價(jià) ；SUM(OTdermastejLOTdersuni)as‘ 訂購(gòu)總金額 ￡?omoi?deTdetaiL

13、ojrdemiastei;pjrodiictwhei?eojL?deKle{ail?OKlemo=ordennasTei??OTdeinoaiidojrdeTdetai =,52 倍速光驅(qū), (9) 查詢訂購(gòu)了“52倍速光驅(qū)”商品且訂貨數(shù)量界于、4之間的訂單編號(hào)，訂貨數(shù)量和訂貨 金額。 selectojrdeTdeTaiLojrdewLO.OTdeideuuLqty.ojrde 門(mén) nastei.ojLdeisiiinfroinoKieimastecoTderdelaiLpiodii ctwhere ofderdetail.orderiio=ordennaster.ordenioand

14、piodiict.productno=ofdefdetail.pioductnoandproduct. pfoductname=,52 倍速光 ^,andordefdetail.qtybetweeii2and4 (10) 在訂單主表中查詢每個(gè)業(yè)務(wù)員的訂單數(shù)量 selectoidermastei\saleno.COUNT(*)astiJ^g$W,fLomordermastefgroupbysaleno (11) 統(tǒng)計(jì)在業(yè)務(wù)科工作且在年或年出生的員工人數(shù)和平均工資。 selectCOUNT(*)as'員工人數(shù),AVG( employee, salaiyjas'平均工資坨?oineinplo

15、yee (12) 在訂單明細(xì)表中統(tǒng)計(jì)每種商品的銷(xiāo)售數(shù)量和金額，并按銷(xiāo)售金額的升序排序輸出。 selectoidefdetail.productno.SUM(ofdefdetail.qty)as,銷(xiāo)售數(shù)量 精心整理 \SUM(orderdetail.price*orderdetail.qty)asl 金額 ,fiomorderdetailgroupbyorderdetail.productnoofderbySUM(orderdetail.price*orderdetail.qty) (13) 統(tǒng)計(jì)客戶號(hào)為“C20050001”的客戶的訂單數(shù)，訂貨總額和平均訂貨金額 selectcu

16、stomer.customenio.COUNT(ofdefdetail.orderno)as,n^gJ :SUM(oj?demiasteF?oi:dei?siim)as'訂貨總額\AVG(ofdermastei\ofdersum)as,平均訂單金額 'fromoideTdetaiLojrdemiastmiisTomerwhefeoKieKlegil.oKiemohOKleimastetordejLnoimdojrdemia stef.customenio=customer.customenioandcustomer.customenio=,C2005001,groupbycustomer.

17、cus tomemo (14) 統(tǒng)計(jì)每個(gè)客戶的訂單數(shù)，訂貨總額和平均訂貨金額。 selectcustomer.customenio.COUNT(ofdefdetail.orderno)as,n^gJ :SUM(oj?demiasteF?oi:dei?siim)as'訂貨總額\AVG(ofdermastei\ofdersum)as,平均訂單金額 (15) 查詢訂單中至少包含種(含種)以上商品的訂單編號(hào)及訂購(gòu)次數(shù)，且訂購(gòu)的商品數(shù) 量在件(含件)以上。 selectordenio,count(*)fromorderdetailwhereordeniom(selectorderdeta

18、il.ordenio fiomofderdetailgroupbyoidenioliavingCOUNT(ofdenio)>=3)groupbyorderno (16) 查找訂購(gòu)了32MDRAM”的商品的客戶編號(hào)，客戶名稱，訂貨總數(shù)量和訂貨總金額。 selectciistomer.customenioxustomeniame.SUM(qty)asiniS/^^W \SUM(ordennaster.ordersum)as,訂貨總金額, fromprodiic 匸 ordeide{aiLciisi:omei;OTdeimasWi?whereciistoinei??ciisToinejri]

19、LO=ojrdermastei??ciisi:ome 門(mén) 1 oandordermaster.oiderno =orderdetail.ordenioaiidproduct.productiio=orderdetail.productnoandproduct.productname-32M DRAM* groupbycu stonier, cu stomernoxustomemame (17) 查詢每個(gè)客戶訂購(gòu)的商品編號(hào)，商品所屬類(lèi)別，商品數(shù)量及訂貨金額，結(jié)果顯示客 戶名稱，商品所屬類(lèi)別，商品數(shù)量及 訂貨金額，并按客戶編號(hào)升序和按訂貨金額的降序排序輸出。 selectcustome

20、mame,product.productno.product.productclass,orderdetail.qty,orderdetail.price*ord erdetail.qtv fi?oinordeTdei:aiLpjLodiict;ciisi:omeTQTdeimasWi?whefeciistoinei??ciisToinejriio=ordermastei??ciisK)mem oand oidemiaster.oidenio=orderdetail.ofdenioandorderdetail.productno=product.pioductnoorderbycust om

21、er.customemoasc .orderdetail.pnce*ordefdetail.qtydesc (18) 按商品類(lèi)別查詢每類(lèi)商品的訂貨平均單價(jià)在元(含元)以上的訂貨總數(shù)量，訂貨平 均單價(jià)和訂貨總金額。 selectCOUNT(qty)as'訂貨總數(shù) M\AVG(pnce)as,iJ 貨評(píng)價(jià)單價(jià)',SUM(ordersum)as'訂貨總金額 ^omproduct.oidermastera, ofderdetailbwherea.ordenio=b.ordenioandpfoduct.pfoductno=b.prodiictnogroupbyproduct.pfodiic

22、tclasshavingAVG(pnce)>=2 80 (19) 查找至少有次銷(xiāo)售的業(yè)務(wù)員名稱和銷(xiāo)售日期。 selectemployee.employeename.ofdennaster.oiderdatefromemployee.ordeniiasterwhereemployee. employeeno=ordermastef. salenoandemployeenoin(selectsalenofiwiordeniiasteigroupbysalenohavingCOUNT(*)>=2)order byemployeename (20) 查詢銷(xiāo)售金額最大的客戶名稱和總貨款額 se

23、lectcustomeEcustomeniameanax(a.b)fiomciistomei\ofdermastei\(selecttoplordermaster.custom eino.SUM(OKle 門(mén) nastetoTdeisimJas'iyfLomoTdejrmasteEggiipbyciisKmie 門(mén) io)a wherecustomeEciistomenio=ordermaster.customenioanda.ciistomerno=customer.customeniogrou pbycustomeniame selecteustomeniame,MAX(b.a)fio

24、mcustomei;(selectcustomenio,sum(ordersum)afi-omordermaster groupbycu stomemo)bgroupbycu stomemame wherecustomer. cu stomemo=b. cu stomernogroupbycu stomemame (21) 查找銷(xiāo)售總額力\于元的銷(xiāo)售員編號(hào)，姓名和銷(xiāo)售額 selecteiiiployee.employeeno,employeename,afLomemployee.(selectsaleiio.SUM(ofdersum)afLom ordemiastergroupbysal

25、eno)b whereb.saleno=employeenoanda<5000 (22) 查找至少訂購(gòu)了種商品的客戶編號(hào)，客戶名稱，商品編號(hào)，商品名稱，數(shù)量和金額。 精心整理 selectcustomer.customemo,customer.customeniame,product.productno,product.productiiame,orde rdetail.qty, ofderdetail.pnce^ordeidetail.qtyas^^ ,fiomcustomei\pfoduct.ordefdetaiLordermasteivTherecustomer.custom

26、eiiio= ofdennaster.ciistonienioandofdennaster.ordenio=orderdetail.ofdernoandorderdetail.pioductno=pr oduct.productno andciistomer.customernom(selectciistomer.customeniofLomcustomei\pfoduct.ofdefdetaiLorderma steivdierecustomer.customemo= ofdennaster.ciistonienioandofdennaster.ordenio=orderdetail.

27、ofdernoandorderdetail.pioductno=pr oduct.productno groupbycustomer.customernohavingcount( customer. customenio)>=3)orderbycustomenio (23) 查找同時(shí)訂購(gòu)了商品為“P20070002”和商品編號(hào)為“P20070001”的商品的客戶編號(hào)， 客戶姓名，商品編號(hào)， 商品名稱和銷(xiāo)售數(shù)量，按客戶編號(hào)排序輸出。 selectcustomer.customenioxustomeniame.pfoduct.pfoductno.productname^ordefdeta

28、il.qtyfiomcu stomer.product, ojLdejrdetaiLoTdeimasteiwhereciistoineEciistoinejriiOhOKlerinasteT.ciistomeELioaiidoKieimastei. order no=orderdeta ll.oiderno andorderdetail.pfoductno=pfoduct.productnoandcustomer.customernoin (selectcustomer.customeniofromcustomei\product. ofderdetaiLofdermasterwhere

29、ciistomeECustomerno=oidermastei\customenioandofdermastei\ order no=orderdeta ll.oiderno andorderdetail.productno=pioduct.productnoandproduct.productno=lP2007002, mtersect selectcustomer.customeniofLomcustomeEproduct, ofderdetaiLofdermasterwhereciistomeEcustomerno=oidermastei\customenioandofdermas

30、tei\ order no=orderdeta ll.oiderno andofderdetail.pioductno=pfoduct.pfoductnoandpfoduct.productno=,P200700r)orderbycustomer. customemo (24) 計(jì)算每一商品每月的銷(xiāo)售金額總和，并將結(jié)果首先按銷(xiāo)售月份然后按訂貨金額降序 排序輸出。 selectojrdemiastei??ciisk)inemoQKlei:masi:ei??OTdeKlaesi】m(oi?deFsi】m)as* 肖售金額總和 'fromoidermasteF ggiipbyordemi

31、astei.ciislomemo.oTdemiasTei.oTdeKlateoTdeTbyojrdemiasteiojrdeTdae* 肖售金額總 和 desc (25) 查詢訂購(gòu)了“鍵盤(pán)”商品的客戶姓名，訂貨數(shù)量和訂貨日期 selectciistomei.ciistomemameQrdejLdelaiLqty.oKleifmastei.oKleKlatefroinciistomeaoKlejrdeTaiLoKle rmastei; product^4ierecustomeEcustomenio=oidermasteEcustomenioaiidofdeidetail.ofderno=o

32、rdermaster. ordenioand product.productno=orderdetail.productnoaiidproductiiame=l^fi, (26) 查詢沒(méi)有訂購(gòu)“鍵盤(pán)”商品的客戶名稱。 selectdistinctcustomeniamefiomcustomeivTherecustomeniamenotin( selecidishnctciistoine 門(mén) laineflomciistoine 匚 ojLdermasteT.ordejrdeTaiLpjLodiicrwheTe ofdennaster.ciistomenio=ciistomef.cust

33、omenioandofdennaster.ordenio=ofderdetail.ordernoandpr oduct.productno =ofderdetail.productnoandproductname=,^S,) (27) 查詢至少銷(xiāo)售了種商品的銷(xiāo)售員編號(hào)，姓名，商品名稱，數(shù)量及相應(yīng)的單價(jià)，并按 銷(xiāo)售員編號(hào)排序輸出。 selectemployee.employeeno,employeename,productname,orderdetail.qty,orderdetail.pricefromem ployee,product, ordefdetaiLofdermaster

34、whereemployee.employeeno=ordermaster.salenoandofdennaster.ofdenio= orderdetail.orderno (selectemployee.employeenofiomemployee.product. ordefdetaiLofdermasterwhereemployee.employeeno=ordermaster.salenoandofdennaster.ofdenio= orderdetail.orderno andofderdetail.pioductno=prodiict.pfoductnogroupbyem

35、ployee.employeenohavingCOUNT(empl oyee.employeeno)>=5) orderbyemployeeno (28) 查詢沒(méi)有訂購(gòu)商品的客戶編號(hào)和客戶名稱。 selecta.ciistomernofromcustomerawherenotexists( selec^fromojLdeimasterwheTea.ciistoine 門(mén) io=oi:demias{er?ciisromenio) (29) 查詢至少包含了“世界技術(shù)開(kāi)發(fā)公司”所訂購(gòu)的商品的客戶編號(hào)，客戶名稱，商品編 號(hào)，商品名稱，數(shù)量和金額O selectcustomer.custome

36、nioxustomeniame.pfoduct.pfoductno.productname.ordeidetail.qty.qty*pn cefrom ordemiaster.ordenio =orderdetail.ordenioandorderdetail.productno=product.pioductiioandcustomer.customernamein selectcustomeniamefromcustomefwhere notexists (select*fLom(selectcustomeniame.ofdeidetail.productnofiomcusto

37、mercustoenHb.ofdeidetaiLofder mastei\productproductb wheTeciistoemrb.ciistoineniame—世界技術(shù)開(kāi)發(fā)公司 ,andcustoenub.customenio=ordermaster.customernoand ofdennaster.oidenio=ordefdetail.ofdenioandoidefdetail.pfoductno=pfoductb.productno)awhere notexists(select*from(selectcustomeniame.ofderdetail.pfoductnof

38、iomcustomercustoenub.ofdefde taiLordermastei\productproductb wherecustoenKb.customerno=ordermaster.customenioandoidermastei\ofderno=oiderdetail.ordeni oandorderdetail.productno=productb.productno)b wherecustomer.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)容）