實(shí)驗(yàn)五 數(shù)據(jù)查詢復(fù)雜查詢一、實(shí)驗(yàn)?zāi)康?.掌握SQLServer查詢語(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)說明請(qǐng)復(fù)習(xí)相關(guān)的查詢知識(shí)點(diǎn)并完成如下內(nèi)容。四、實(shí)驗(yàn)內(nèi)容1 在訂單數(shù)據(jù)庫(kù)orderDB 中，完成如下的查詢：（ 1）用子查詢查詢員工“張小娟”所做的訂單信息。（ 2）查詢沒有訂購(gòu)商品的且在北京地區(qū)的客戶編號(hào)，客戶名稱和郵政編碼，并按郵政編碼降序排序。（ 3）查詢訂購(gòu)了“32M DRAM ”商品的訂單編號(hào)，訂貨數(shù)量和訂貨單價(jià)。（4）查詢與員工編號(hào)” E2008005”在同一個(gè)部門的員工編號(hào)，姓名，性別， 所屬部門。（ 5）查詢既訂購(gòu)了P 商品，又訂購(gòu)了 P 商品的客戶編號(hào)，訂單編號(hào)和訂單金額（6）查詢沒有訂購(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ù)量和訂貨金額。（ 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)為“ C”的客戶的訂單數(shù)，訂貨總額和平均訂貨金額（ 14）統(tǒng)計(jì)每個(gè)客戶的訂單數(shù)，訂貨總額和平均訂貨金額。（ 15） 查詢訂單中至少包含3 種（含 3 種） 以上商品的訂單編號(hào)及訂購(gòu)次數(shù)，且訂購(gòu)的商品數(shù)量在3 件（含 3 件）以上。（ 16）查找訂購(gòu)了“32M DRAM ”的商品的客戶編號(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í)訂貝了商品為“ P”和商品編號(hào)為“ P”的商品的客戶編號(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）查詢沒有訂購(gòu)商品的客戶編號(hào)和客戶名稱。（ 29）查詢至少包含了“世界技術(shù)開發(fā)公司”所訂購(gòu)的商品的客戶編號(hào)，客 戶名稱，商品編號(hào)，商品名稱，數(shù)量和金額。五、實(shí)驗(yàn)步驟請(qǐng)完成實(shí)驗(yàn)內(nèi)容，并寫出具體的實(shí)驗(yàn)步驟( 1) 用子查詢查詢員工 “張小娟 ” 所做的訂單信息 。select orderMaster.* from employee ,orderMaster where orderMaster .SaleNo =employeeNo and employeeName in(select from employeewhere employeeName=張小娟)( 2) 查詢沒有訂購(gòu)商品的且在北京地區(qū)的客戶編號(hào)， 客戶名稱和郵政編碼 ， 并按郵政編碼降序排序。select distinct , , from customer ,orderMaster where customer .customerNo not in(selectorderMaster .customerNo from orderMaster)and =北京市 order by customer.Zip desc( 3) 查詢訂購(gòu)了“32M DRAM ”商品的訂單編號(hào)， 訂貨數(shù)量和訂貨單價(jià)。select orderDetail .OrderNo ,orderDetail .Qty ,orderDetail .Price from orderDetail ,product where=product .ProductNo and ProductName =32M DRAM(4)查詢與員工編號(hào) E2008005”在同一個(gè)部門的員工編號(hào)，姓名，性別，所屬部門 。select employee .employeeNo ,employee .employeeName ,case when Mthen男when Fthen 女end ,employee .Department from employee where =(selectemployee .Department from employee whereemployee .employeeNo =E2008005)( 5) 查詢既訂購(gòu)了 P 商品 ， 又訂購(gòu)了 P 商品的客戶編號(hào)， 訂單編號(hào)和訂單金額select customer.customerno,ordermaster.orderno ,ordermaster.ordersum from ordermaster,customer,orderdetail where =ordermaster.customerno and ordermaster.orderno =orderdetail .orderno andorderdetail .productno=P2007002intersectselect customer.customerno,ordermaster.orderno ,ordermaster.ordersum from ordermaster,customer,orderdetail where =ordermaster.customerno and ordermaster.orderno =orderdetail .orderno andorderdetail .productno=P2005001( 6) 查詢沒有訂購(gòu)“52倍速光驅(qū)”或 “17寸顯示器”的客戶編號(hào)， 客戶名稱 。select distinct customer .customerno ,customer .customernamefrom customer,orderdetail ,ordermaster,product where orderdetail .orderno =ordermaster.orderno and ordermaster.customerno=customer.customerno and orderdetail .productno =product .productno and product .productname notin （52倍速光驅(qū) ,17 寸顯示器 ）( 7) 查詢訂單金額最高的訂單編號(hào)， 客戶姓名 ， 銷售員名稱和相應(yīng)的訂單金額 。selectordermaster.orderno ,customer.customername,employee .employeename,ordermaster.ordersum from employee ,ordermaster,customer whereemployee .employeeno=ordermaster.salenoand customer.customerno =ordermaster.customerno and ordermaster.ordersum =（select MAX（ordermaster.ordersum ）from ordermaster）( 8) 查詢訂購(gòu)了“52倍速光驅(qū) ”商品的訂購(gòu)數(shù)量， 訂購(gòu)平均價(jià)和訂購(gòu)總金額。select SUM （orderdetail .qty ）aS訂購(gòu)數(shù)量,AVG ）aS訂購(gòu)平均價(jià),SUM（ordermaster.ordersum）as訂購(gòu)總金額from orderdetail ,ordermaster,productwhere orderdetail .orderno =ordermaster.orderno and orderdetail .productno=product .productno and product .productname=52 倍速光驅(qū) ( 9) 查詢訂購(gòu)了“52倍速光驅(qū)”商品且訂貨數(shù)量界于 4 之間的訂單編號(hào)，訂貨數(shù)量和訂貨金額。select ,orderdetail .qty ,ordermaster.ordersum fromordermaster,orderdetail ,product whereorderdetail .orderno =ordermaster.orderno and product .productno = and product .productname =52 倍速光驅(qū) and orderdetail .qty between 2 and 4( 10) 在訂單主表中查詢每個(gè)業(yè)務(wù)員的訂單數(shù)量select ,COUNT (*)as訂單數(shù)量from ordermastergroup by saleno( 11) 統(tǒng)計(jì)在業(yè)務(wù)科工作且在年或年出生的員工人數(shù)和平均工資。select COUNT (*) as員工人數(shù),AVG (employee .salary )as平均工資from employee( 12) 在訂單明細(xì)表中統(tǒng)計(jì)每種商品的銷售數(shù)量和金額， 并按銷售金額的升序排序輸出 。select orderdetail .productno ,SUM (orderdetail .qty )as銷售數(shù)量,SUM (orderdetail .price *orderdetail .qty )as 金額 from orderdetail group by orderdetail .productno order by SUM(orderdetail .price * orderdetail .qty )( 13) 統(tǒng)計(jì)客戶號(hào)為“C” 的客戶的訂單數(shù)， 訂貨總額和平均訂貨金額select customer.customerno ,COUNT (orderdetail .orderno )as訂單數(shù),SUM(ordermaster.ordersum )as訂貨總額,AVG (ordermaster.ordersum)as平均訂單金額 from orderdetail ,ordermaster,customerwhere orderdetail .orderno=ordermaster.orderno and =customer .customerno andcustomer.customerno =C2005001 group by customer.customerno訂貨總額和平均訂貨金額14) 統(tǒng)計(jì)每個(gè)客戶的訂單數(shù)select customer.customerno ,COUNT (orderdetail .orderno )as訂單數(shù),SUM(ordermaster.ordersum )as訂貨總額,AVG (ordermaster.ordersum)as平均 訂單金額 from orderdetail ,ordermaster,customerwhere orderdetail .orderno =ordermaster.orderno and =customer .customerno group by customer.customerno( 15) 查詢訂單中至少包含種( 含種 ) 以上商品的訂單編號(hào)及訂購(gòu)次數(shù)，且訂購(gòu)的商品數(shù)量在件( 含件 ) 以上 。select orderno ,count(*) from orderdetail where orderno in( selectfrom orderdetail group by orderno having COUNT (orderno)>=3) group by orderno( 16) 查找訂購(gòu)了“32M DRAM ”的商品的客戶編號(hào)， 客戶名稱 ， 訂貨總數(shù)量和訂貨總金額。select ,customernameSUM (qty)as 訂貨總數(shù)量,SUM (ordermaster .ordersum)as訂貨總金額from product,orderdetail,customer,ordermasterwhere customer .customerno =ordermaster.customernoandordermaster.orderno=orderdetail .orderno and =orderdetail .productno and product .productname =32M DRAM group by ,customername( 17) 查詢每個(gè)客戶訂購(gòu)的商品編號(hào)， 商品所屬類別 ， 商品數(shù)量及訂貨金額 ， 結(jié)果顯示客戶名稱， 商品所屬類別 ， 商品數(shù)量及訂貨金額 ， 并按客戶編號(hào)升序和按訂貨金額的降序排序輸出 。select customername,orderdetail .qty ,orderdetail .price *orderdetail .qty from orderdetail ,product ,customer ,ordermaster where =ordermaster.customernoandordermaster.orderno =orderdetail .orderno and orderdetail .productno =product .productno order by asc,orderdetail . price *orderdetail .qty desc( 18) 按商品類別查詢每類商品的訂貨平均單價(jià)在元 ( 含元 ) 以上的訂貨總數(shù)量 ， 訂貨平均單價(jià)和訂貨總金額。select COUNT (qty)aS訂貨總數(shù)量,AVG (price )aS訂貨評(píng)價(jià)單價(jià),SUM (ordersum )aS訂貨總金額from product, ordermastera, orderdetail b where = and product .productno = group by having AVG (price )>=280( 19) 查找至少有次銷售的業(yè)務(wù)員名稱和銷售日期 。select employee .employeename,ordermaster .orderdate from employee , ordermasterwhere employee.employeeno =ordermaster. salenoand employeeno in (selectsalenofrom ordermaster group by saleno having COUNT (*)>= 2) order by employeename( 20) 查詢銷售金額最大的客戶名稱和總貨款額select ,max from customer,ordermaster,(select top 1 ordermaster.customerno,SUM asb from ordermaster group by customerno)a where = and =customer .customerno group by customernameselectcustomername, MAX from customer,(selectcustomerno,sum(ordersum)a from ordermaster group by customerno)b group by customername where customer .customerno = group by customername( 21) 查找銷售總額小于元的銷售員編號(hào)， 姓名和銷售額select ,employeename,a from employee,(select saleno,SUM (ordersum) a from ordermastergroup by saleno)bwhere =employeeno and a<5000( 22) 查找至少訂購(gòu)了種商品的客戶編號(hào)， 客戶名稱 ， 商品編號(hào) ， 商品名稱 ， 數(shù)量和金額。select,* as金額from customer,product,orderdetail,ordermasterwhere =ordermaster.customernoand = and orderdetail .productno =and in (select from customer,product,orderdetail,ordermasterwhere = ordermaster.customernoand = and orderdetail .productno =group by having count>=3 ) order by customerno(23)查找同時(shí)訂購(gòu)了商品為P”和商品編號(hào)為P”的商品的客戶編號(hào),客戶姓名 ， 商品編號(hào) ，商品名稱和銷售數(shù)量， 按客戶編號(hào)排序輸出 。select,customername, ,productname, from customer,product, orderdetail,ordermasterwhere =ordermaster.customernoand =and orderdetail .productno = and in(select from customer,product,orderdetail,ordermasterwhere =ordermaster.customernoand =and orderdetail .productno = and =P2007002intersectselect from customer,product,orderdetail,ordermasterwhere =ordermaster.customernoand =and orderdetail .productno = and =P2007001) order by( 24) 計(jì)算每一商品每月的銷售金額總和 ， 并將結(jié)果首先按銷售月份然后按訂貨金額降序排序輸出 。select ,ordermaster.orderdate,sum(ordersum) as銷售金額總和fromordermastergroup by ordermaster.customerno, order by ordermaster.orderdate,銷售金額總和 desc( 25) 查詢訂購(gòu)了 “鍵盤 ”商品的客戶姓名 ， 訂貨數(shù)量和訂貨日期select ,ordermaster.orderdatefrom customer,orderdetail ,ordermaster, product where =ordermaster.customernoandorderdetail .orderno =ordermaster.orderno and=orderdetail .productno and productname=鍵盤( 26) 查詢沒有訂購(gòu) “鍵盤 ”商品的客戶名稱。select distinct customernamefrom customer where customernamenot in( select distinct customernamefrom customer,ordermaster,orderdetail,product whereordermaster.customerno= andordermaster.orderno =orderdetail .orderno and=and productname=鍵盤)( 27) 查詢至少銷售了種商品的銷售員編號(hào)， 姓名 ， 商品名稱 ， 數(shù)量及相應(yīng)的單價(jià) ， 并按銷售員編號(hào)排序輸出 。select,employeename,productname,orderdetail .price from employee,product, orderdetail,ordermasterwhere = and = and =product .productno and in(select from employee,product, orderdetail,ordermasterwhere = and = and =product .productno group by employee .employeeno having COUNT>=5)order by employeeno( 28) 查詢沒有訂購(gòu)商品的客戶編號(hào)和客戶名稱。select from customer a where not exists( select * from ordermaster where =( 29) 查詢至少包含了 “世界技術(shù)開發(fā)公司 ”所訂購(gòu)的商品的客戶編號(hào)， 客戶名稱 ， 商品編號(hào) ， 商品名稱 ， 數(shù)量和金額。select ,customername, ,productname,orderdetail .qty ,qty*price from customer,product ,orderdetail ,ordermasterwhere =ordermaster.customerno andordermaster.orderno=orderdetail .orderno and orderdetail .productno = and customer.customername in(select customernamefrom customer where not exists(select*from (selectcustomername,orderdetail .productno from customer custoemrb,orderdetail ,ordermaster,product productbwhere =世界技術(shù)開發(fā)公司and custoemrb .customerno=ordermaster.customernoandordermaster.orderno =orderdetail .orderno and orderdetail .productno =a where not exists (select*from (select customername,orderdetail .productno from customer custoemrb,orderdetail ,ordermaster,product productb where custoemrb .customerno=ordermaster.customernoand ordermaster.orderno =orderdetail .orderno and orderdetail .productno = b where = and = )六、思考題：1 存在量詞與集合運(yùn)算in 、連接運(yùn)算和全程量詞之間的關(guān)系如何？他們可以互相替換嗎？給出你的理由2 . Where子句和having子句都是用于指定查詢條件的，請(qǐng)區(qū)別它們的異同?用實(shí)例說明。3在分組聚集操作中，為什么在查詢列中，除了聚集函數(shù)運(yùn)算外，其它表達(dá)式必須包含在group by 子句中？七、總結(jié) （ 實(shí)驗(yàn)過程的體會(huì)、心得和實(shí)驗(yàn)教與學(xué)之間還需改進(jìn)的內(nèi)容）