《實驗五數(shù)據(jù)查詢》由會員分享，可在線閱讀，更多相關(guān)《實驗五數(shù)據(jù)查詢（11頁珍藏版）》請在裝配圖網(wǎng)上搜索。

1、實驗五數(shù)據(jù)查詢復雜查詢一. 實驗目的1. 掌握SQLServe查詢語句的基本語法2. 熟練使用SQL的Select語句對多表進行查詢3. 熟練掌握并運用SQLServer所提供的函數(shù)4. 熟練使用SQL語句進行復雜的連接操作二、實驗環(huán)境(實驗的軟件.硬件環(huán)境)硬件：PC機軟件:SQL2000三、實驗指導說明請復習相關(guān)的查詢知識點并完成如下內(nèi)容。四、實驗內(nèi)容1.在訂單數(shù)據(jù)庫oiderDB中，完成如下的查詢：(1) 用子查詢查詢員工“張小娟”所做的訂單信息。(2) 查詢沒有訂購商品的且在北京地區(qū)的客戶編號，客戶名稱和郵政編碼，并按郵政編碼降 序排序。(3) 查詢訂購了 “32MDRAM”商品的訂單

2、編號，訂貨數(shù)量和訂貨單價。(4) 查詢與員工編號“E2008005”在同一個部門的員工編號，姓名，性別，所屬部門。(5) 查詢既訂購了 P20050001商品，又訂購了 P20070002商品的客戶編號，訂單編號和訂單 金額(6) 查詢沒有訂購“52倍速光驅(qū)”或“17寸顯示器”的客戶編號，客戶名稱。(7) 查詢訂單金額最高的訂單編號，客戶姓名，銷售員名稱和相應的訂單金額。(8) 查詢訂購了 “52倍速光驅(qū)”商品的訂購數(shù)量，訂購平均價和訂購總金額。(9) 查詢訂購了 “52倍速光驅(qū)”商品且訂貨數(shù)量界于24之間的訂單編號，訂貨數(shù)量和訂貨 金額0(10) 在訂單主表中查詢每個業(yè)務(wù)員的訂單數(shù)量(11)

3、 統(tǒng)計在業(yè)務(wù)科工作且在1973年或1967年出生的員工人數(shù)和平均工資。(12) 在訂單明細表中統(tǒng)計每種商品的銷售數(shù)量和金額，并按銷售金額的升序排序輸出。(13) 統(tǒng)計客戶號為“C20050001”的客戶的訂單數(shù)，訂貨總額和平均訂貨金額(14) 統(tǒng)計每個客戶的訂單數(shù)，訂貨總額和平均訂貨金額。(15) 查詢訂單中至少包含3種(含3種)以上商品的訂單編號及訂購次數(shù)，且訂購的商品數(shù)量在3件(含3件)以上。(16) 查找訂購了 “32MDRAM”的商品的客戶編號，客戶名稱，訂貨總數(shù)量和訂貨總金額。(17) 查詢每個客戶訂購的商品編號，商品所屬類別，商品數(shù)量及訂貨金額，結(jié)果顯示客戶名稱，商品所屬類別，商品

4、數(shù)量及訂貨金額，并按客戶編號升序和按訂貨金額的降序排序輸出。(18) 按商品類別查詢每類商品的訂貨平均單價在280元(含280元)以上的訂貨總數(shù)量，訂 貨平均單價和訂貨總金額。(19) 查找至少有2次銷售的業(yè)務(wù)員名稱和銷售日期。(20) 查詢銷售金額最大的客戶名稱和總貨款額(21) 查找銷售總額小于5000元的銷售員編號，姓名和銷售額(22) 查找至少訂購了 3種商品的客戶編號，客戶名稱，商品編號，商品名稱，數(shù)量和金額。(23) 查找同時訂購了商品為“P20070002”和商品編號為“P20070001”的商品的客戶編號，客戶姓名，商品編號，商品名稱和銷售數(shù)量，按客戶編號排序輸出。(24) 計

5、算每一商品每月的銷售金額總和，并將結(jié)果首先按銷售月份然后按訂貨金額降序排序 輸出。(25) 查詢訂購了 “鍵盤”商品的客戶姓名，訂貨數(shù)量和訂貨日期(26) 查詢每月訂購“鍵盤”商品的客戶名稱。(27) 查詢至少銷售了 5種商品的銷售員編號，姓名，商品名稱，數(shù)量及相應的單價，并按銷售員編號排序輸出。(28) 查詢沒有訂購商品的客戶編號和客戶名稱。(29) 查詢至少包含了“世界技術(shù)開發(fā)公司”所訂購的商品的客戶編號，客戶名稱，商品編號,商品名稱，數(shù)量和金額。五. 實驗步驟請完成實驗內(nèi)容，并寫出具體的實驗步驟(1) 用子查詢查詢員工“張小娟所做的訂單信息。 selectorderMaster?fiom

6、employee,orderMastei-whereorderMaster.SaleNo=employeeNoandemplo veeNamemJ(selectemployee.employeeNamefLomemployeewhereemployeeName= 長 =3)groupbyorderno(16) 查找訂購了32MDRAM”的商品的客戶編號，客戶名稱，訂貨總數(shù)量和訂貨總金額。selectciistomer.customenioxustomeniame.SUM(qty)asiniS/WSUM(ordennaster.ordersum)as,訂貨總金額,fromprodiic 匸 or

7、deideaiLciisi:omei;OTdeimasWiwhereciistoineiciisToinejriLO=ojrdermasteiciisi:ome 門 1 oandordermaster.oiderno=orderdetail.ordenioaiidproduct.productiio=orderdetail.productnoandproduct.productname-32M DRAM*groupbycu stonier, cu stomernoxustomemame(17) 查詢每個客戶訂購的商品編號，商品所屬類別，商品數(shù)量及訂貨金額，結(jié)果顯示客戶名稱，商品所屬類別，商品數(shù)

8、量及訂貨金額，并按客戶編號升序和按訂貨金額的降序排序輸出。selectcustomemame,product.productno.product.productclass,orderdetail.qty,orderdetail.price*ord erdetail.qtv fioinordeTdei:aiLpjLodiict;ciisi:omeTQTdeimasWiwhefeciistoineiciisToinejriio=ordermasteiciisK)mem oandoidemiaster.oidenio=orderdetail.ofdenioandorderdetail.product

9、no=product.pioductnoorderbycust omer.customemoasc.orderdetail.pnce*ordefdetail.qtydesc(18) 按商品類別查詢每類商品的訂貨平均單價在元(含元)以上的訂貨總數(shù)量，訂貨平均單價和訂貨總金額。selectCOUNT(qty)as訂貨總數(shù) MAVG(pnce)as,iJ 貨評價單價,SUM(ordersum)as訂貨總金額omproduct.oidermastera,ofderdetailbwherea.ordenio=b.ordenioandpfoduct.pfoductno=b.prodiictnogroupb

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

11、貨款額selectcustomeEcustomeniameanax(a.b)fiomciistomeiofdermastei(selecttoplordermaster.custom eino.SUM(OKle 門 nastetoTdeisimJasiyfLomoTdejrmasteEggiipbyciisKmie 門 io)awherecustomeEciistomenio=ordermaster.customenioanda.ciistomerno=customer.customeniogrou pbycustomeniameselecteustomeniame,MAX(b.a)fiomc

12、ustomei;(selectcustomenio,sum(ordersum)afi-omordermaster groupbycu stomemo)bgroupbycu stomemamewherecustomer. cu stomemo=b. cu stomernogroupbycu stomemame(21) 查找銷售總額力于元的銷售員編號，姓名和銷售額selecteiiiployee.employeeno,employeename,afLomemployee.(selectsaleiio.SUM(ofdersum)afLom ordemiastergroupbysaleno)bwher

13、eb.saleno=employeenoanda=3)orderbycustomenio(23) 查找同時訂購了商品為“P20070002”和商品編號為“P20070001”的商品的客戶編號，客戶姓名，商品編號，商品名稱和銷售數(shù)量，按客戶編號排序輸出。selectcustomer.customenioxustomeniame.pfoduct.pfoductno.productnameordefdetail.qtyfiomcu stomer.product,ojLdejrdetaiLoTdeimasteiwhereciistoineEciistoinejriiOhOKlerinasteT.cii

14、stomeELioaiidoKieimastei. order no=orderdeta ll.oidernoandorderdetail.pfoductno=pfoduct.productnoandcustomer.customernoin (selectcustomer.customeniofromcustomeiproduct.ofderdetaiLofdermasterwhereciistomeECustomerno=oidermasteicustomenioandofdermastei order no=orderdeta ll.oidernoandorderdetail.produ

15、ctno=pioduct.productnoandproduct.productno=lP2007002,mtersectselectcustomer.customeniofLomcustomeEproduct, ofderdetaiLofdermasterwhereciistomeEcustomerno=oidermasteicustomenioandofdermastei order no=orderdeta ll.oidernoandofderdetail.pioductno=pfoduct.pfoductnoandpfoduct.productno=,P200700r)orderbyc

16、ustomer. customemo(24) 計算每一商品每月的銷售金額總和，并將結(jié)果首先按銷售月份然后按訂貨金額降序排序輸出。selectojrdemiasteiciisk)inemoQKlei:masi:eiOTdeKlaesi】m(oideFsi】m)as* 肖售金額總和fromoidermasteFggiipbyordemiastei.ciislomemo.oTdemiasTei.oTdeKlateoTdeTbyojrdemiasteiojrdeTdae* 肖售金額總 和 desc(25) 查詢訂購了“鍵盤”商品的客戶姓名，訂貨數(shù)量和訂貨日期selectciistomei.ciisto

17、memameQrdejLdelaiLqty.oKleifmastei.oKleKlatefroinciistomeaoKlejrdeTaiLoKle rmastei;product4ierecustomeEcustomenio=oidermasteEcustomenioaiidofdeidetail.ofderno=ordermaster. ordenioandproduct.productno=orderdetail.productnoaiidproductiiame=lfi,(26) 查詢沒有訂購“鍵盤”商品的客戶名稱。selectdistinctcustomeniamefiomcusto

18、meivTherecustomeniamenotin(selecidishnctciistoine 門 laineflomciistoine 匚 ojLdermasteT.ordejrdeTaiLpjLodiicrwheTe ofdennaster.ciistomenio=ciistomef.customenioandofdennaster.ordenio=ofderdetail.ordernoandpr oduct.productno=ofderdetail.productnoandproductname=,S,)(27) 查詢至少銷售了種商品的銷售員編號，姓名，商品名稱，數(shù)量及相應的單價，

19、并按銷售員編號排序輸出。selectemployee.employeeno,employeename,productname,orderdetail.qty,orderdetail.pricefromem ployee,product,ordefdetaiLofdermasterwhereemployee.employeeno=ordermaster.salenoandofdennaster.ofdenio= orderdetail.orderno(selectemployee.employeenofiomemployee.product. ordefdetaiLofdermasterwher

20、eemployee.employeeno=ordermaster.salenoandofdennaster.ofdenio= orderdetail.ordernoandofderdetail.pioductno=prodiict.pfoductnogroupbyemployee.employeenohavingCOUNT(empl oyee.employeeno)=5) orderbyemployeeno(28) 查詢沒有訂購商品的客戶編號和客戶名稱。selecta.ciistomernofromcustomerawherenotexists( selecfromojLdeimasterwh

21、eTea.ciistoine 門 io=oi:demiaserciisromenio)(29) 查詢至少包含了“世界技術(shù)開發(fā)公司”所訂購的商品的客戶編號，客戶名稱，商品編號，商品名稱，數(shù)量和金額Oselectcustomer.customenioxustomeniame.pfoduct.pfoductno.productname.ordeidetail.qty.qty*pn cefromordemiaster.ordenio=orderdetail.ordenioandorderdetail.productno=product.pioductiioandcustomer.customernam

22、ein selectcustomeniamefromcustomefwherenotexists(select*fLom(selectcustomeniame.ofdeidetail.productnofiomcustomercustoenHb.ofdeidetaiLofder masteiproductproductbwheTeciistoemrb.ciistoineniame世界技術(shù)開發(fā)公司,andcustoenub.customenio=ordermaster.customernoandofdennaster.oidenio=ordefdetail.ofdenioandoidefdeta

23、il.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. 存在量詞與集合運算m、連接運算和全程量詞之間的關(guān)系如何？他們可以互相替換嗎？給 出你的理由2. Wheie子句和having子句都是用于指定查詢條件的，請區(qū)別它們的異同？用實例說明。3. 在分組聚集操作中，為什么在查詢列中，除了聚集函數(shù)運算外，其它表達式必須包含在gioupbv子句中?七、總結(jié)（實驗過程的體會、心得和實驗教與學之間還需改進的內(nèi)容）