實(shí)驗(yàn)五 數(shù)據(jù)查詢——復(fù)雜查詢.doc

數(shù)據(jù)庫(kù)原理實(shí)驗(yàn)學(xué)號(hào)：123012008xxx 姓名：xxx 班級(jí)（x）小班 日期：2010年 月 日實(shí)驗(yàn)五 數(shù)據(jù)查詢復(fù)雜查詢一、實(shí)驗(yàn)?zāi)康?1掌握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)說(shuō)明請(qǐng)復(fù)習(xí)相關(guān)的查詢知識(shí)點(diǎn)并完成如下內(nèi)容。四、實(shí)驗(yàn)內(nèi)容1在訂單數(shù)據(jù)庫(kù)orderDB中，完成如下的查詢：（1）用子查詢查詢員工“張小娟”所做的訂單信息。（2）查詢沒(méi)有訂購(gòu)商品的且在北京地區(qū)的客戶編號(hào)，客戶名稱和郵政編碼，并按郵政編碼降序排序。（3）查詢訂購(gòu)了“32M DRAM”商品的訂單編號(hào)，訂貨數(shù)量和訂貨單價(jià)。（4）查詢與員工編號(hào)“E2008005”在同一個(gè)部門的員工編號(hào)，姓名，性別，所屬部門。（5）查詢既訂購(gòu)了P20050001商品，又訂購(gòu)了P20070002商品的客戶編號(hào)，訂單編號(hào)和訂單金額（6）查詢沒(méi)有訂購(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)為“C20050001”的客戶的訂單數(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í)訂購(gòu)了商品為“P20070002”和商品編號(hào)為“P20070001”的商品的客戶編號(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）查詢沒(méi)有訂購(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 employee.employeeName from employee where employeeName =張小娟)（2）查詢沒(méi)有訂購(gòu)商品的且在北京地區(qū)的客戶編號(hào)，客戶名稱和郵政編碼，并按郵政編碼降序排序。select distinct customer.CustomerNo ,customer.customerName ,customer.Zip from customer ,orderMaster where customer .customerNo not in(select orderMaster .customerNo from orderMaster )and customer.addr=北京市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 orderDetail.ProductNo =product .ProductNo and ProductName =32M DRAM（4）查詢與員工編號(hào)“E2008005”在同一個(gè)部門的員工編號(hào)，姓名，性別，所屬部門。select employee .employeeNo ,employee .employeeName ,case employee.Sex when Mthen 男when Fthen 女end ,employee .Department from employee where employee.Department =(select employee .Department from employee where employee .employeeNo =E2008005)（5）查詢既訂購(gòu)了P20050001商品，又訂購(gòu)了P20070002商品的客戶編號(hào)，訂單編號(hào)和訂單金額select customer .customerno,ordermaster .orderno ,ordermaster .ordersum from ordermaster ,customer,orderdetail where customer.customerno =ordermaster .customerno and ordermaster .orderno =orderdetail .orderno and orderdetail .productno=P2007002intersectselect customer .customerno,ordermaster .orderno ,ordermaster .ordersum from ordermaster ,customer,orderdetail where customer.customerno =ordermaster .customerno and ordermaster .orderno =orderdetail .orderno and orderdetail .productno=P2005001（6）查詢沒(méi)有訂購(gòu)“52倍速光驅(qū)”或“17寸顯示器”的客戶編號(hào)，客戶名稱。select distinct customer .customerno ,customer .customername from 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）查詢訂單金額最高的訂單編號(hào)，客戶姓名，銷售員名稱和相應(yīng)的訂單金額。select ordermaster .orderno ,customer .customername ,employee .employeename ,ordermaster .ordersum from employee ,ordermaster,customer where employee .employeeno =ordermaster .saleno and 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 (ordermaster.ordersum )as訂購(gòu)平均價(jià),SUM (ordermaster .ordersum )as訂購(gòu)總金額 from orderdetail ,ordermaster ,product where 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.orderno ,orderdetail .qty ,ordermaster .ordersum from ordermaster,orderdetail ,product whereorderdetail .orderno =ordermaster .orderno and product .productno =ductno and product .productname =52倍速光驅(qū)and orderdetail .qty between 2 and 4（10）在訂單主表中查詢每個(gè)業(yè)務(wù)員的訂單數(shù)量select ordermaster.saleno ,COUNT (*)as 訂單數(shù)量from ordermaster group 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)為“C20050001”的客戶的訂單數(shù)，訂貨總額和平均訂貨金額select customer .customerno ,COUNT (orderdetail .orderno )as 訂單數(shù),SUM (ordermaster .ordersum )as 訂貨總額,AVG (ordermaster .ordersum )as 平均訂單金額 from orderdetail ,ordermaster ,customer where orderdetail .orderno =ordermaster .orderno and ordermaster.customerno =customer .customerno and customer .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 ,customer where orderdetail .orderno =ordermaster .orderno and ordermaster.customerno =customer .customerno group by customer .customerno （15）查詢訂單中至少包含種（含種）以上商品的訂單編號(hào)及訂購(gòu)次數(shù)，且訂購(gòu)的商品數(shù)量在件（含件）以上。select orderno ,count(*)from orderdetail where orderno in(select orderdetail.orderno from orderdetail group by orderno having COUNT(orderno)=3) group by orderno （16）查找訂購(gòu)了“32M DRAM”的商品的客戶編號(hào)，客戶名稱，訂貨總數(shù)量和訂貨總金額。select customer.customerno,customername,SUM(qty)as 訂貨總數(shù)量,SUM(ordermaster .ordersum)as 訂貨總金額from product,orderdetail,customer,ordermaster where customer .customerno =ordermaster .customerno and ordermaster .orderno=orderdetail .orderno and ductno =orderdetail .productno and product .productname =32M DRAMgroup by customer.customerno ,customername（17）查詢每個(gè)客戶訂購(gòu)的商品編號(hào)，商品所屬類別，商品數(shù)量及訂貨金額，結(jié)果顯示客戶名稱，商品所屬類別，商品數(shù)量及訂貨金額，并按客戶編號(hào)升序和按訂貨金額的降序排序輸出。select customername,ductno,ductclass,orderdetail .qty ,orderdetail .price *orderdetail .qty from orderdetail ,product ,customer ,ordermaster where customer.customerno =ordermaster .customerno and ordermaster .orderno =orderdetail .orderno and orderdetail .productno =product .productno order by customer.customerno 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, ordermaster a,orderdetail b where a.orderno =b.orderno and product .productno =ductno group by ductclass having AVG (price )=280（19）查找至少有次銷售的業(yè)務(wù)員名稱和銷售日期。select employee .employeename ,ordermaster .orderdate from employee ,ordermaster where employee .employeeno =ordermaster.saleno and employeeno in (select saleno from ordermaster group by saleno having COUNT (*)=2) order by employeename （20）查詢銷售金額最大的客戶名稱和總貨款額select customer.customername,max(a.b) from customer,ordermaster,(select top 1 ordermaster .customerno,SUM(ordermaster.ordersum) as b from ordermaster group by customerno )awhere customer.customerno =ordermaster.customerno and a.customerno =customer .customerno group by customername select customername,MAX(b.a) from customer,(select customerno,sum(ordersum )a from ordermaster group by customerno)b group by customername where customer .customerno =b.customerno group by customername （21）查找銷售總額小于元的銷售員編號(hào)，姓名和銷售額select employee.employeeno,employeename,a from employee,(select saleno,SUM(ordersum) a from ordermaster group by saleno )b where b.saleno =employeeno and a=3 ) order by customerno（23）查找同時(shí)訂購(gòu)了商品為“P20070002”和商品編號(hào)為“P20070001”的商品的客戶編號(hào)，客戶姓名，商品編號(hào)， 商品名稱和銷售數(shù)量，按客戶編號(hào)排序輸出。select customer.customerno,customername,ductno ,productname,orderdetail.qty from customer,product,orderdetail,ordermaster where customer.customerno=ordermaster .customerno and ordermaster.orderno=orderdetail.orderno and orderdetail .productno =ductno and customer.customerno in(select customer.customerno from customer,product,orderdetail,ordermaster where customer.customerno=ordermaster .customerno and ordermaster.orderno=orderdetail.orderno and orderdetail .productno =ductno and ductno =P2007002intersectselect customer.customerno from customer,product,orderdetail,ordermaster where customer.customerno=ordermaster .customerno and ordermaster.orderno=orderdetail.orderno and orderdetail .productno =ductno and ductno =P2007001) order by customer.customerno （24）計(jì)算每一商品每月的銷售金額總和，并將結(jié)果首先按銷售月份然后按訂貨金額降序排序輸出。select ordermaster.customerno ,ordermaster .orderdate,sum(ordersum) as 銷售金額總和 from ordermaster group by ordermaster .customerno,ordermaster.orderdate order by ordermaster .orderdate,銷售金額總和 desc（25）查詢訂購(gòu)了“鍵盤”商品的客戶姓名，訂貨數(shù)量和訂貨日期select customer.customername,orderdetail.qty,ordermaster .orderdate from customer,orderdetail,ordermaster,product where customer.customerno=ordermaster .customerno and orderdetail .orderno =ordermaster .orderno ductno =orderdetail .productno and productname=鍵盤（26）查詢沒(méi)有訂購(gòu)“鍵盤”商品的客戶名稱。select distinct customername from customer where customername not in(select distinct customername from customer,ordermaster,orderdetail,product whereordermaster .customerno =customer.customerno and ordermaster .orderno =orderdetail .orderno and ductno =ductno and productname=鍵盤)（27）查詢至少銷售了種商品的銷售員編號(hào)，姓名，商品名稱，數(shù)量及相應(yīng)的單價(jià)，并按銷售員編號(hào)排序輸出。select employee.employeeno,employeename,productname,orderdetail.qty,orderdetail .price from employee,product,orderdetail,ordermaster where employee.employeeno =ordermaster.saleno and ordermaster.orderno =orderdetail.orderno and ductno =product .productno and employee.employeeno in(select employee.employeeno from employee,product,orderdetail,ordermaster where employee.employeeno =ordermaster.saleno and ordermaster.orderno =orderdetail.orderno and ductno =product .productno group by employee .employeeno having COUNT(employee.employeeno)=5)order by employeeno （28）查詢沒(méi)有訂購(gòu)商品的客戶編號(hào)和客戶名稱。select a.customerno from customer a where not exists(select *from ordermaster where a.customerno =ordermaster.customerno)（29）查詢至少包含了“世界技術(shù)開發(fā)公司”所訂購(gòu)的商品的客戶編號(hào)，客戶名稱，商品編號(hào)，商品名稱，數(shù)量和金額。select customer.customerno ,customername,ductno ,product