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

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

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

3、作且在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 DRAM ”的商品的客戶編號，客戶名稱，訂貨總數(shù)量和訂貨總金額。（ 17） 查詢每個客戶訂購的商品編號， 商品所屬類別， 商品數(shù)量及訂貨金額，結(jié)果顯示客戶名稱， 商品所屬類別，

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

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

6、mployee ,orderMaster where orderMaster .SaleNo =employeeNo and employeeName in(select from employeewhere employeeName=張小娟)( 2) 查詢沒有訂購商品的且在北京地區(qū)的客戶編號， 客戶名稱和郵政編碼 ， 并按郵政編碼降序排序。select distinct , , from customer ,orderMaster where customer .customerNo not in(selectorderMaster .customerNo from orderMaster)

7、and =北京市 order by customer.Zip desc( 3) 查詢訂購了“32M DRAM ”商品的訂單編號， 訂貨數(shù)量和訂貨單價。select orderDetail .OrderNo ,orderDetail .Qty ,orderDetail .Price from orderDetail ,product where=product .ProductNo and ProductName =32M DRAM(4)查詢與員工編號 E2008005”在同一個部門的員工編號，姓名，性別，所屬部門 。select employee .employeeNo ,employee .

8、employeeName ,case when Mthen男when Fthen 女end ,employee .Department from employee where =(selectemployee .Department from employee whereemployee .employeeNo =E2008005)( 5) 查詢既訂購了 P 商品 ， 又訂購了 P 商品的客戶編號， 訂單編號和訂單金額select customer.customerno,ordermaster.orderno ,ordermaster.ordersum from ordermaster,cus

9、tomer,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 =o

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

11、d orderdetail .productno =product .productno and product .productname notin （52倍速光驅(qū) ,17 寸顯示器 ）( 7) 查詢訂單金額最高的訂單編號， 客戶姓名 ， 銷售員名稱和相應(yīng)的訂單金額 。selectordermaster.orderno ,customer.customername,employee .employeename,ordermaster.ordersum from employee ,ordermaster,customer whereemployee .employeeno=ordermast

12、er.salenoand customer.customerno =ordermaster.customerno and ordermaster.ordersum =（select MAX（ordermaster.ordersum ）from ordermaster）( 8) 查詢訂購了“52倍速光驅(qū) ”商品的訂購數(shù)量， 訂購平均價和訂購總金額。select SUM （orderdetail .qty ）aS訂購數(shù)量,AVG ）aS訂購平均價,SUM（ordermaster.ordersum）as訂購總金額from orderdetail ,ordermaster,productwhere o

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

14、nd product .productno = and product .productname =52 倍速光驅(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ù)量和金額， 并

15、按銷售金額的升序排序輸出 。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)計客戶號為“C” 的客戶的訂單數(shù)， 訂貨總額和平均訂貨金額select customer.customerno ,COUNT (orde

16、rdetail .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)計每個客戶的訂單數(shù)select cust

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

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

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

20、序輸出 。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

21、 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ù)員名稱和銷售日期 。select employee .employeename,orde

22、rmaster .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

23、 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) 查找銷售總額小于元的銷售員

24、編號， 姓名和銷售額select ,employeename,a from employee,(select saleno,SUM (ordersum) a from ordermastergroup by saleno)bwhere =employeeno and a=3 ) order by customerno(23)查找同時訂購了商品為P”和商品編號為P”的商品的客戶編號,客戶姓名 ， 商品編號 ，商品名稱和銷售數(shù)量， 按客戶編號排序輸出 。select,customername, ,productname, from customer,product, orderdetail,ord

25、ermasterwhere =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

26、=and orderdetail .productno = and =P2007001) order by( 24) 計算每一商品每月的銷售金額總和 ， 并將結(jié)果首先按銷售月份然后按訂貨金額降序排序輸出 。select ,ordermaster.orderdate,sum(ordersum) as銷售金額總和fromordermastergroup by ordermaster.customerno, order by ordermaster.orderdate,銷售金額總和 desc( 25) 查詢訂購了 “鍵盤 ”商品的客戶姓名 ， 訂貨數(shù)量和訂貨日期select ,ordermaster

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

28、rom customer,ordermaster,orderdetail,product whereordermaster.customerno= andordermaster.orderno =orderdetail .orderno and=and productname=鍵盤)( 27) 查詢至少銷售了種商品的銷售員編號， 姓名 ， 商品名稱 ， 數(shù)量及相應(yīng)的單價 ， 并按銷售員編號排序輸出 。select,employeename,productname,orderdetail .price from employee,product, orderdetail,ordermasterw

29、here = 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) 查詢沒有訂購商品的客戶編號和客戶名稱。select from customer a where not exists( select * from ordermaster where =(

30、29) 查詢至少包含了 “世界技術(shù)開發(fā)公司 ”所訂購的商品的客戶編號， 客戶名稱 ， 商品編號 ， 商品名稱 ， 數(shù)量和金額。select ,customername, ,productname,orderdetail .qty ,qty*price from customer,product ,orderdetail ,ordermasterwhere =ordermaster.customerno andordermaster.orderno=orderdetail .orderno and orderdetail .productno = and customer.customernam

31、e 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 .

32、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ū)別它們的異同?用實(shí)例說明。3在分組聚集操作中，為什么在查詢列中，除了聚集函數(shù)運(yùn)算外，其它表達(dá)式必須包含在group by 子句中？七、總結(jié) （ 實(shí)驗(yàn)過程的體會、心得和實(shí)驗(yàn)教與學(xué)之間還需改進(jìn)的內(nèi)容）