實驗五數(shù)據(jù)查詢復(fù)雜查詢

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

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

2、 并按郵政編碼降序排序。 （ 3）查詢訂購了“ 32M DRAM ”商品的訂單編號，訂貨數(shù)量和訂貨單價。 （4）查詢與員工編號” E2008005”在同一個部門的員工編號，姓名，性別， 所屬部門。 （ 5）查詢既訂購了 P 商品，又訂購了 P 商品的客戶編號，訂單編號和訂單 金額 （6）查詢沒有訂購“ 52 倍速光驅(qū)”或“ 17寸顯示器”的客戶編號，客戶名 稱。 （ 7）查詢訂單金額最高的訂單編號，客戶姓名，銷售員名稱和相應(yīng)的訂單 金額。 8）查詢訂購了“ 52 倍速光驅(qū)”商品的訂購數(shù)量，訂購平均價和訂購總金 額。 （ 9） 查詢訂購了 “ 52 倍速光驅(qū)” 商品且訂貨

3、數(shù)量界于 2~4 之間的訂單編號， 訂貨數(shù)量和訂貨金額。 （ 10）在訂單主表中查詢每個業(yè)務(wù)員的訂單數(shù)量 （ 11） 統(tǒng)計在業(yè)務(wù)科工作且在 1973 年或 1967 年出生的員工人數(shù)和平均工資。 （ 12）在訂單明細(xì)表中統(tǒng)計每種商品的銷售數(shù)量和金額，并按銷售金額的升 序排序輸出。 （ 13）統(tǒng)計客戶號為“ C”的客戶的訂單數(shù)，訂貨總額和平均訂貨金額 （ 14）統(tǒng)計每個客戶的訂單數(shù)，訂貨總額和平均訂貨金額。 （ 15） 查詢訂單中至少包含 3 種（含 3 種） 以上商品的訂單編號及訂購次數(shù)， 且訂購的商品數(shù)量在 3 件（含 3 件）以上。 （ 16）查找訂購了“ 32M DR

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

5、客戶編號，客戶名稱，商品編號，商品名 稱，數(shù)量和金額。 （ 23）查找同時訂貝^了商品為“ P”和商品編號為“ P”的商品的客戶編號， 客戶姓名，商品編號，商品名稱和銷售數(shù)量，按客戶編號排序輸出。 （ 24）計算每一商品每月的銷售金額總和，并將結(jié)果首先按銷售月份然后按 訂貨金額降序排序輸出。 （ 25）查詢訂購了“鍵盤”商品的客戶姓名，訂貨數(shù)量和訂貨日期 （ 26）查詢每月訂購“鍵盤”商品的客戶名稱。 （ 27）查詢至少銷售了 5 種商品的銷售員編號，姓名，商品名稱，數(shù)量及相 應(yīng)的單價，并按銷售員編號排序輸出。 （ 28）查詢沒有訂購商品的客戶編號和客戶名稱。 （ 29）查

6、詢至少包含了“世界技術(shù)開發(fā)公司”所訂購的商品的客戶編號，客 戶名稱，商品編號，商品名稱，數(shù)量和金額。 五、實驗步驟 請完成實驗內(nèi)容，并寫出具體的實驗步驟 ( 1) 用子查詢查詢員工 “張小娟 ” 所做的訂單信息 。 select orderMaster.* from employee ,orderMaster where orderMaster .SaleNo =employeeNo and employeeName in (select from employeewhere employeeName=張小娟) ( 2) 查詢沒有訂購商品的且在北京地區(qū)的客戶編號 ， 客戶名稱和郵政

7、編 碼 ， 并按郵政編碼降序排序 。 select distinct , , from customer ,orderMaster where customer . customerNo not in(selectorderMaster .customerNo from orderMaster)and = 北京市 order by customer.Zip desc ( 3) 查詢訂購了 “32M DRAM ”商品的訂單編號 ， 訂貨數(shù)量和訂貨單價 。 select orderDetail .OrderNo ,orderDetail .Qty ,orderDetail .Price

8、 from orderDetail ,product where =product .ProductNo and ProductName =32M DRAM (4)查詢與員工編號 E2008005”在同一個部門的員工編號，姓名，性別， 所屬部門 。 select employee .employeeNo ,employee .employeeName ,case when Mthen 男when Fthen 女end , employee .Department from employee where =(selectemployee .Department from employ

9、ee where employee .employeeNo =E2008005) ( 5) 查詢既訂購了 P 商品 ， 又訂購了 P 商品的客戶編號 ， 訂單編號和訂 單金額 select customer.customerno,ordermaster.orderno ,ordermaster.ordersum from ordermaster,customer,orderdetail where =ordermaster.customerno and ordermaster.orderno =orderdetail .orderno and orderdetail .product

10、no=P2007002 intersect select customer.customerno,ordermaster.orderno ,ordermaster.ordersum from ordermaster,customer,orderdetail where =ordermaster.customerno and ordermaster.orderno =orderdetail .orderno and orderdetail .productno=P2005001 ( 6) 查詢沒有訂購 “52倍速光驅(qū) ”或 “17寸顯示器 ”的客戶編號 ， 客戶名稱 。 select

11、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 not in （52倍速光驅(qū) ,17 寸顯示器 ） ( 7)

12、查詢訂單金額最高的訂單編號 ， 客戶姓名 ， 銷售員名稱和相應(yīng)的訂 單金額 。 select ordermaster.orderno ,customer.customername,employee .employeename,ord ermaster.ordersum from employee ,ordermaster,customer where employee .employeeno=ordermaster.salenoand customer.customerno =ordermaster.customerno and ordermaster.ordersum =（sele

13、ct MAX （ordermaster.ordersum ）from ordermaster） ( 8) 查詢訂購了 “52倍速光驅(qū) ”商品的訂購數(shù)量 ， 訂購平均價和訂購總金 額。 select SUM （orderdetail .qty ）aS訂購數(shù)量’,AVG ）aS訂購平均價’,SUM （ordermaster.ordersum）as訂購總金額from orderdetail ,ordermaster,product where orderdetail .orderno =ordermaster.orderno and orderdetail .productno =pr

14、oduct .productno and product .productname=52 倍速光驅(qū) ( 9) 查詢訂購了 “52倍速光驅(qū) ”商品且訂貨數(shù)量界于 ~4 之間的訂單編號 ， 訂貨數(shù)量和訂貨金額 。 select ,orderdetail .qty ,ordermaster.ordersum from ordermaster,orderdetail ,product where orderdetail .orderno =ordermaster.orderno and product .productno = and product .productname =52 倍速

15、光驅(qū) and orderdetail .qty between 2 and 4 ( 10) 在訂單主表中查詢每個業(yè)務(wù)員的訂單數(shù)量 select ,COUNT (*)as訂單數(shù)量from ordermastergroup by saleno ( 11) 統(tǒng)計在業(yè)務(wù)科工作且在年或年出生的員工人數(shù)和平均工資 。 select COUNT (*) as員工人數(shù),AVG (employee .salary )as平均工資from employee ( 12) 在訂單明細(xì)表中統(tǒng)計每種商品的銷售數(shù)量和金額 ， 并按銷售金額的 升序排序輸出 。 select orderdetail .produ

16、ctno ,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)計客戶號為 “C” 的客戶的訂單數(shù) ， 訂貨總額和平均訂貨金額 select customer.customerno ,COUNT (orderdetail .orderno )as訂單數(shù),SUM (o

17、rdermaster.ordersum )as訂貨總額,AVG (ordermaster.ordersum)as平均 訂單金額 from orderdetail ,ordermaster,customerwhere orderdetail .orderno =ordermaster.orderno and =customer .customerno and customer.customerno =C2005001 group by customer.customerno 訂貨總額和平均訂貨金額 14) 統(tǒng)計每個客戶的訂單數(shù) select customer.customerno

18、,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) 查詢訂單中至少包含種 ( 含種 ) 以上商品的訂單編號及訂購次數(shù) ， 且訂購的商品數(shù)量

19、在件 ( 含件 ) 以上 。 select orderno ,count(*) from orderdetail where orderno in( select from orderdetail group by orderno having COUNT (orderno)>=3) group by orderno ( 16) 查找訂購了 “32M DRAM ”的商品的客戶編號 ， 客戶名稱 ， 訂貨總數(shù) 量和訂貨總金額 。 select ,customernameSUM (qty)as 訂貨總數(shù)量 ,SUM (ordermaster .ordersum)as訂貨總金額 fro

20、m product,orderdetail,customer,ordermasterwhere customer .customerno =ordermaster.customernoandordermaster.orderno =orderdetail .orderno and =orderdetail .productno and product .productname =32M DRAM group by ,customername ( 17) 查詢每個客戶訂購的商品編號 ， 商品所屬類別 ， 商品數(shù)量及訂貨金 額 ， 結(jié)果顯示客戶名稱 ， 商品所屬類別 ， 商品數(shù)量及 訂貨金

21、額 ， 并按客戶編號升序和按訂貨金額的降序排序輸出 。 select customername,,,orderdetail .qty ,orderdetail .price *orderdetail .qty from orderdetail ,product ,customer ,ordermaster where =ordermaster.customernoand ordermaster.orderno =orderdetail .orderno and orderdetail .productno =product .productno order by asc ,orderde

22、tail . price *orderdetail .qty desc ( 18) 按商品類別查詢每類商品的訂貨平均單價在元 ( 含元 ) 以上的訂貨 總數(shù)量 ， 訂貨平均單價和訂貨總金額 。 select COUNT (qty)aS訂貨總數(shù)量’,AVG (price )aS訂貨評價單價 ,SUM (ordersum )aS訂貨總金額from product, ordermastera, orderdetail b where = and product .productno = group by having AVG (price )>=280 ( 19) 查找至少有次銷售的業(yè)務(wù)員

23、名稱和銷售日期 。 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,or

24、dermaster,(select top 1 ordermaster.customerno,SUM asb from ordermaster group by customerno)a where = and =customer .customerno group by customername selectcustomername, MAX from customer,(selectcustomerno,sum(ordersum)a from ordermaster group by customerno)b group by customername where customer .c

25、ustomerno = group by customername ( 21) 查找銷售總額小于元的銷售員編號 ， 姓名和銷售額 select ,employeename,a from employee,(select saleno,SUM (ordersum) a from ordermastergroup by saleno)b where =employeeno and a<5000 ( 22) 查找至少訂購了種商品的客戶編號 ， 客戶名稱 ， 商品編號 ， 商品名 稱 ， 數(shù)量和金額 。 select,,,,, * as金額’from customer,product,o

26、rderdetail,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)查找同時訂購了商品為 P”和商品編號為P”的商品的客戶編號,客

27、戶姓名 ， 商品編號 ， 商品名稱和銷售數(shù)量 ， 按客戶編號排序輸出 。 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

28、.productno = and =P2007002 intersect select from customer,product, orderdetail,ordermasterwhere =ordermaster.customernoand = and orderdetail .productno = and =P2007001) order by ( 24) 計算每一商品每月的銷售金額總和 ， 并將結(jié)果首先按銷售月份然后 按訂貨金額降序排序輸出 。 select ,ordermaster.orderdate,sum(ordersum) as銷售金額總和from order

29、master group by ordermaster.customerno, order by ordermaster.orderdate,銷售金額 總和 desc ( 25) 查詢訂購了 “鍵盤 ”商品的客戶姓名 ， 訂貨數(shù)量和訂貨日期 select ,,ordermaster.orderdatefrom customer,orderdetail ,ordermaster, product where =ordermaster.customernoandorderdetail .orderno =ordermaster.orderno and =orderdetail .prod

30、uctno and productname=鍵盤 ( 26) 查詢沒有訂購 “鍵盤 ”商品的客戶名稱 。 select distinct customernamefrom customer where customernamenot in( select distinct customernamefrom customer,ordermaster,orderdetail,product where ordermaster.customerno= andordermaster.orderno =orderdetail .orderno and =and productname=鍵盤)

31、( 27) 查詢至少銷售了種商品的銷售員編號 ， 姓名 ， 商品名稱 ， 數(shù)量及相 應(yīng)的單價 ， 并按銷售員編號排序輸出 。 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

32、 group by employee .employeeno having COUNT>=5) order by employeeno ( 28) 查詢沒有訂購商品的客戶編號和客戶名稱 。 select from customer a where not exists( select * from ordermaster where = ( 29) 查詢至少包含了 “世界技術(shù)開發(fā)公司 ”所訂購的商品的客戶編號 ， 客 戶名稱 ， 商品編號 ， 商品名稱 ， 數(shù)量和金額 。 select ,customername, ,productname,orderdetail .qty ,qt

33、y*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

34、from customer custoemrb,orderdetail ,ordermaster,product productb where =世界技術(shù)開發(fā)公司 and custoemrb .customerno =ordermaster.customernoand ordermaster.orderno =orderdetail .orderno and orderdetail .productno =a where not exists (select*from (select customername,orderdetail .productno from customer cu

35、stoemrb,orderdetail ,ordermaster,product productb where custoemrb .customerno=ordermaster.customernoand ordermaster.orderno =orderdetail .orderno and orderdetail .productno = b where = and = ))) 六、思考題： 1 ．存在量詞與集合運算 in 、連接運算和全程量詞之間的關(guān)系如何？他們可 以互相替換嗎？給出你的理由 2 . Where子句和having子句都是用于指定查詢條件的，請區(qū)別它們的異同? 用實例說明。 3．在分組聚集操作中，為什么在查詢列中，除了聚集函數(shù)運算外，其它表 達(dá)式必須包含在 group by 子句中？ 七、總結(jié) （ 實驗過程的體會、心得和實驗教與學(xué)之間還需改進(jìn)的內(nèi)容 ）