《實驗五數(shù)據(jù)查詢》由會員分享，可在線閱讀，更多相關(guān)《實驗五數(shù)據(jù)查詢（11頁珍藏版）》請在裝配圖網(wǎng)上搜索。
1、實驗五數(shù)據(jù)查詢——復雜查詢
一. 實驗?zāi)康?
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ū)的客戶編號，客戶名稱和郵政編碼，并按郵政
2、編碼降 序排序。
(3) 查詢訂購了 “32MDRAM”商品的訂單編號，訂貨數(shù)量和訂貨單價。
(4) 查詢與員工編號“E2008005”在同一個部門的員工編號，姓名，性別，所屬部門。
(5) 查詢既訂購了 P20050001商品，又訂購了 P20070002商品的客戶編號，訂單編號和訂單 金額
(6) 查詢沒有訂購“52倍速光驅(qū)”或“17寸顯示器”的客戶編號，客戶名稱。
(7) 查詢訂單金額最高的訂單編號，客戶姓名，銷售員名稱和相應(yīng)的訂單金額。
(8) 查詢訂購了 “52倍速光驅(qū)”商品的訂購數(shù)量，訂購平均價和訂購總金額。
(9) 查詢訂購了 “52倍速光驅(qū)”商品且訂貨數(shù)量界于2?
3、4之間的訂單編號，訂貨數(shù)量和訂貨 金額0
(10) 在訂單主表中查詢每個業(yè)務(wù)員的訂單數(shù)量
(11) 統(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”的商品的客戶編號，客戶名稱，訂貨總
4、數(shù)量和訂貨總金額。
(17) 查詢每個客戶訂購的商品編號，商品所屬類別，商品數(shù)量及訂貨金額，結(jié)果顯示客戶名
稱，商品所屬類別，商品數(shù)量及訂貨金額，并按客戶編號升序和按訂貨金額的降序排序輸出。
(18) 按商品類別查詢每類商品的訂貨平均單價在280元(含280元)以上的訂貨總數(shù)量，訂 貨平均單價和訂貨總金額。
(19) 查找至少有2次銷售的業(yè)務(wù)員名稱和銷售日期。
(20) 查詢銷售金額最大的客戶名稱和總貨款額
(21) 查找銷售總額小于5000元的銷售員編號，姓名和銷售額
(22) 查找至少訂購了 3種商品的客戶編號，客戶名稱，商品編號，商品名稱，數(shù)量和金額。
(23) 查找同時
5、訂購了商品為“P20070002”和商品編號為“P20070001”的商品的客戶編號，
客戶姓名，商品編號，商品名稱和銷售數(shù)量，按客戶編號排序輸出。
(24) 計算每一商品每月的銷售金額總和，并將結(jié)果首先按銷售月份然后按訂貨金額降序排序 輸出。
(25) 查詢訂購了 “鍵盤”商品的客戶姓名，訂貨數(shù)量和訂貨日期
(26) 查詢每月訂購“鍵盤”商品的客戶名稱。
(27) 查詢至少銷售了 5種商品的銷售員編號，姓名，商品名稱，數(shù)量及相應(yīng)的單價，并按銷
售員編號排序輸出。
(28) 查詢沒有訂購商品的客戶編號和客戶名稱。
(29) 查詢至少包含了“世界技術(shù)開發(fā)公司”所訂購的商品的客戶編
6、號，客戶名稱，商品編號,
商品名稱，數(shù)量和金額。
五. 實驗步驟
請完成實驗內(nèi)容，并寫出具體的實驗步驟
(1) 用子查詢查詢員工“張小娟"所做的訂單信息。 selectorderMaster?fiomemployee,orderMastei-whereorderMaster.SaleNo=employeeNoandemplo veeNamem
J
(selectemployee.employeeNamefLomemployeewhereemployeeName=^ 長 <]\ 娟')
(2) 查詢沒有訂購商品的且在北京地區(qū)的客戶編號，客戶名稱和郵政編碼，并按郵政編碼
降序排序。
7、
selectdistinctcustomer.CustomerNo,customer.customerName,customer.Zipfromcustomei\orderMa steivdierecu stonier.
ciisTomeiNoiiotuKselectojrderMastei.ciistomeFNofromoiderMasteQaiidciistome 匸 addr=‘北京市 'orderbycustomerZipdesc
(3) 查詢訂購了-32MDRAM-商品的訂單編號，訂貨數(shù)量和訂貨單價。
selectojrdeiDetail.OrderNoQTderDetai
8、l.Qiy.oTderDetaiLP 門 cefiomordeiDetaiLpTodiictwheieoideT Detail.ProductNo
=product.ProductNoandProductName=132MDRAlVr
(4) 查詢與員工編號“E2008005”在同一個部門的員工編號，姓名，性別，所屬部門。
tM,then,?
'when'F'tlien'女'end,
employee.Depairmentfromemployeewhereemployee.Depaftment=(selectemployee.Depaftmentfio memployeewliere
9、
employee.employeeNo-E2008005,)
(5) 查詢既訂購了 P20050001商品，又訂購了 P20070002商品的客戶編號，訂單編號和訂
單金額
selectciistoinei.ciistomemo.ojrdemiastei.ojrde 門 lo.oTdeimaste 匸 oideisiimfromoTde 門 naste 匚 customer; ofderdetailwliefeeustomei\customenio=ofdennasteEcustomenioandordermasteEordeiiio=ordefdet ail.ordenioando
10、iderdetail.pioductno=,P2007002,
mtersect
P20050011
(6) 查詢沒有訂購“52倍速光驅(qū)”或T7寸顯示器”的客戶編號，客戶名稱。
selectdistinctcustomei\customenioxustomei\customernamefLomcustomei;ofderdetaiLofdermastei\ product^diereorderdetail.ordenio=ofdermastei\ofdenioandordermaster.customenio=customef.cust
omenioandofderdetail.pr
11、oductno=product.productnoandproduct.productnamenotin(,52 倍速光 9區(qū)
T17寸顯示器)
(7) 查詢訂單金額最高的訂單編號，客戶姓名，銷售員名稱和相應(yīng)的訂單金額。
selectojrdennasteEordejriJLO.ciistoiner.ciisTome 門 lame.employee.einployeename.oTdeimaste 匸 ordeisu mfromeinployee.ojrdennastecciistoinefwheieeinployee.employeeiiOhOJLde 門 nastei.saleiio
12、aiidciisto mei.ciistoine 門 io=ojL?deimastef?ci】stomeinoaiidojL?deimastef?OJL?deisiim=(selectMAX(OTdei*masteF?Oj：d ersum)fromordennaster)
(8) 查詢訂購了“52倍速光驅(qū)”裔品的訂購數(shù)量，訂購平均價和訂購總金額。
selectSUM(orderdetail.qty)as,訂購數(shù)量\AVG(ordennastei\ordersum)as,訂購平均價
；SUM(OTdermastejLOTdersuni)as‘ 訂購總金額
￡?omoi?deTdetaiL
13、ojrdemiastei;pjrodiictwhei?eojL?deKle{ail?OKlemo=ordennasTei??OTdeinoaiidojrdeTdetai
=,52 倍速光驅(qū),
(9) 查詢訂購了“52倍速光驅(qū)”商品且訂貨數(shù)量界于、4之間的訂單編號，訂貨數(shù)量和訂貨 金額。
selectojrdeTdeTaiLojrdewLO.OTdeideuuLqty.ojrde 門 nastei.ojLdeisiiinfroinoKieimastecoTderdelaiLpiodii ctwhere
ofderdetail.orderiio=ordennaster.ordenioand
14、piodiict.productno=ofdefdetail.pioductnoandproduct. pfoductname=,52 倍速光 ^,andordefdetail.qtybetweeii2and4
(10) 在訂單主表中查詢每個業(yè)務(wù)員的訂單數(shù)量
selectoidermastei\saleno.COUNT(*)astiJ^g$W,fLomordermastefgroupbysaleno
(11) 統(tǒng)計在業(yè)務(wù)科工作且在年或年出生的員工人數(shù)和平均工資。
selectCOUNT(*)as'員工人數(shù),AVG( employee, salaiyjas'平均工資坨?oineinplo
15、yee
(12) 在訂單明細表中統(tǒng)計每種商品的銷售數(shù)量和金額，并按銷售金額的升序排序輸出。
selectoidefdetail.productno.SUM(ofdefdetail.qty)as,銷售數(shù)量
精心整理
\SUM(orderdetail.price*orderdetail.qty)asl 金額
,fiomorderdetailgroupbyorderdetail.productnoofderbySUM(orderdetail.price*orderdetail.qty)
(13) 統(tǒng)計客戶號為“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)計每個客戶的訂單數(shù)，訂貨總額和平均訂貨金額。
selectcustomer.customenio.COUNT(ofdefdetail.orderno)as,n^gJ
:SUM(oj?demiasteF?oi:dei?siim)as'訂貨總額\AVG(ofdermastei\ofdersum)as,平均訂單金額
(15) 查詢訂單中至少包含種(含種)以上商品的訂單編號及訂購次數(shù)，且訂購的商品數(shù)
量在件(含件)以上。
selectordenio,count(*)fromorderdetailwhereordeniom(selectorderdeta
18、il.ordenio fiomofderdetailgroupbyoidenioliavingCOUNT(ofdenio)>=3)groupbyorderno
(16) 查找訂購了32MDRAM”的商品的客戶編號，客戶名稱，訂貨總數(shù)量和訂貨總金額。
selectciistomer.customenioxustomeniame.SUM(qty)asiniS/^^W
\SUM(ordennaster.ordersum)as,訂貨總金額,
fromprodiic 匸 ordeide{aiLciisi:omei;OTdeimasWi?whereciistoinei??ciisToinejri]
19、LO=ojrdermastei??ciisi:ome 門 1 oandordermaster.oiderno
=orderdetail.ordenioaiidproduct.productiio=orderdetail.productnoandproduct.productname-32M DRAM*
groupbycu stonier, cu stomernoxustomemame
(17) 查詢每個客戶訂購的商品編號，商品所屬類別，商品數(shù)量及訂貨金額，結(jié)果顯示客
戶名稱，商品所屬類別，商品數(shù)量及
訂貨金額，并按客戶編號升序和按訂貨金額的降序排序輸出。
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) 按商品類別查詢每類商品的訂貨平均單價在元(含元)以上的訂貨總數(shù)量，訂貨平
均單價和訂貨總金額。
selectCOUNT(qty)as'訂貨總數(shù) M\AVG(pnce)as,iJ 貨評價單價',SUM(ordersum)as'訂貨總金額
^omproduct.oidermastera,
ofderdetailbwherea.ordenio=b.ordenioandpfoduct.pfoductno=b.prodiictnogroupbyproduct.pfodiic
22、tclasshavingAVG(pnce)>=2 80
(19) 查找至少有次銷售的業(yè)務(wù)員名稱和銷售日期。
selectemployee.employeename.ofdennaster.oiderdatefromemployee.ordeniiasterwhereemployee. employeeno=ordermastef.
salenoandemployeenoin(selectsalenofiwiordeniiasteigroupbysalenohavingCOUNT(*)>=2)order byemployeename
(20) 查詢銷售金額最大的客戶名稱和總貨款額
se
23、lectcustomeEcustomeniameanax(a.b)fiomciistomei\ofdermastei\(selecttoplordermaster.custom eino.SUM(OKle 門 nastetoTdeisimJas'iyfLomoTdejrmasteEggiipbyciisKmie 門 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) 查找銷售總額力\于元的銷售員編號，姓名和銷售額
selecteiiiployee.employeeno,employeename,afLomemployee.(selectsaleiio.SUM(ofdersum)afLom ordemiastergroupbysal
25、eno)b
whereb.saleno=employeenoanda<5000
(22) 查找至少訂購了種商品的客戶編號，客戶名稱，商品編號，商品名稱，數(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) 查找同時訂購了商品為“P20070002”和商品編號為“P20070001”的商品的客戶編號，
客戶姓名，商品編號，
商品名稱和銷售數(shù)量，按客戶編號排序輸出。
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) 計算每一商品每月的銷售金額總和，并將結(jié)果首先按銷售月份然后按訂貨金額降序
排序輸出。
selectojrdemiastei??ciisk)inemoQKlei:masi:ei??OTdeKlaesi】m(oi?deFsi】m)as* 肖售金額總和
'fromoidermasteF
ggiipbyordemi
31、astei.ciislomemo.oTdemiasTei.oTdeKlateoTdeTbyojrdemiasteiojrdeTdae* 肖售金額總 和 desc
(25) 查詢訂購了“鍵盤”商品的客戶姓名，訂貨數(shù)量和訂貨日期
selectciistomei.ciistomemameQrdejLdelaiLqty.oKleifmastei.oKleKlatefroinciistomeaoKlejrdeTaiLoKle rmastei;
product^4ierecustomeEcustomenio=oidermasteEcustomenioaiidofdeidetail.ofderno=o
32、rdermaster. ordenioand
product.productno=orderdetail.productnoaiidproductiiame=l^fi,
(26) 查詢沒有訂購“鍵盤”商品的客戶名稱。
selectdistinctcustomeniamefiomcustomeivTherecustomeniamenotin(
selecidishnctciistoine 門 laineflomciistoine 匚 ojLdermasteT.ordejrdeTaiLpjLodiicrwheTe ofdennaster.ciistomenio=ciistomef.cust
33、omenioandofdennaster.ordenio=ofderdetail.ordernoandpr oduct.productno
=ofderdetail.productnoandproductname=,^S,)
(27) 查詢至少銷售了種商品的銷售員編號，姓名，商品名稱，數(shù)量及相應(yīng)的單價，并按
銷售員編號排序輸出。
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) 查詢沒有訂購商品的客戶編號和客戶名稱。
selecta.ciistomernofromcustomerawherenotexists( selec^fromojLdeimasterwheTea.ciistoine 門 io=oi:demias{er?ciisromenio)
(29) 查詢至少包含了“世界技術(shù)開發(fā)公司”所訂購的商品的客戶編號，客戶名稱，商品編
號，商品名稱，數(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ù)開發(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. 存在量詞與集合運算m、連接運算和全程量詞之間的關(guān)系如何？他們可以互相替換嗎？給 出你的理由
2. Wheie子句和having子句都是用于指定查詢條件的，請區(qū)別它們的異同？用實例說明。
3. 在分組聚集操作中，為什么在查詢列中，除了聚集函數(shù)運算外，其它表達式必須包含在
gioupbv子句中?
七、總結(jié)（實驗過程的體會、心得和實驗教與學之間還需改進的內(nèi)容）
實驗五數(shù)據(jù)查詢
