實驗五_復雜查詢答案(2012.4)

1、-(1）用子查詢查詢員工“張小娟”所做的訂單信息。（難易程度：易）select * from ordermasterwhere salerno in(select employeeno from employee where employeename='張小娟') -（2）查詢沒有訂購商品的且在北京地區(qū)的客戶編號，客戶名稱和郵政編碼，并按郵政編碼降序排序。（難易程度：易）select customerno,customername,zip from customer cwhere address='北京市' and not exists(select * fro

2、m ordermaster o where c.customerno=o.customerno)order by zip desc 方法二：select CustomerNo,CustomerName,Zipfrom Customer where Address='北京市' and CustomerNo not in (select CustomerNo from OrderMaster )order by Zip desc-（3）查詢訂購了“32M DRAM”商品的訂單編號，訂貨數(shù)量和訂貨單價。（難易程度：易）select orderno,quantity,price fro

3、m orderdetail where productno in(select productno from product where productname='32M DRAM') -（4）查詢與員工編號“E2008005”在同一個部門的員工編號，姓名，性別，所屬部門。（難易程度：易）select employeeno,employeename,sex,department from employeewhere department in(select department from employee where employeeno ='E2008005'

4、) and employeeno !='E2008005' -（5）查詢既訂購了P20050001商品，又訂購了P20070002商品的客戶編號，訂單編號和訂單金額。 （難易程度：中）（為了驗證查詢結果，改為：P2005001和 P2005002）方法一：select customerno,orderno,ordersum from ordermasterwhere orderno in(select orderno from orderdetail where productno = 'P2005001')and orderno in(select order

5、no from orderdetail where productno = 'P2005002') 方法二：可以使用表的自身連接。Select customerno, orderno, ordersumfrom ordermasterwhere orderno in(select a.orderno from orderdetail a,orderdetail bwhere ductno='P2005001' and ductno='P2005002' and a.orderno=b.orderno)- （6）查詢沒有 (訂購

6、“52倍速光驅”或“17寸顯示器”) 的客戶編號，客戶名稱。（為了能驗證查詢結果，52倍速光驅 改為 32M DRAM） （難易程度：難）（此題要注意題意的理解。） select CustomerNo,CustomerName from Customer where CustomerNo Not in (select CustomerNo from OrderMaster where OrderNo in (select distinct OrderNo from OrderDetail where ProductNo in (select ProductNo from Product whe

7、re ProductName in ( '32M DRAM' , '17寸顯示器' ) ) )用等價的exists謂詞如下：select CustomerNo,CustomerName from Customer cwhere not exists (select * from OrderMaster o where o.CustomerNo=c.CustomerNo and OrderNo in (select OrderNo from OrderDetail where ProductNo in (select ProductNo from Product

8、where ProductName in ('32M DRAM','17寸顯示器') ) )- （7）查詢訂單金額最高的訂單編號，客戶姓名，銷售員名稱和相應的訂單金額。（難易程度：中）select orderno,customername,employeename,ordersum from ordermaster o,customer c,employee ewhere o.customerno = c.customerno and o.salerno = e.employeenoand ordersum =(select max(ordersum) from

9、 ordermaster)或者Select top 1 * from (select OrderNo,CustomerName,EmployeeName,Ordersum from OrderMaster,Customer,Employeewhere OrderMaster.SalerNo=Employee.EmployeeNo and OrderMaster.CustomerNo=Customer.CustomerNo) v order by v.ordersum desc或者select v.OrderNo, CustomerName, EmployeeName, v.Ordersum f

10、rom Customer, Employee,(select top 1 * from ordermasterorder by ordersum desc ) as vwhere v.SalerNo=Employee.EmployeeNo and v.CustomerNo=Customer.CustomerNo- （8）查詢訂購了“52倍速光驅”商品的訂購數(shù)量，訂購平均價和訂購總金額。（難易程度：易） select sum(quantity),avg(price),sum(quantity*price)from orderdetailwhere productno = (select prod

11、uctno from product where productname='52倍速光驅')- （9）查詢訂購了“52倍速光驅”商品且訂貨數(shù)量界于24之間的訂單編號，訂貨數(shù)量和訂貨金額。 （難易程度：易）select orderno,sum(quantity),sum(quantity*price) 訂貨金額from orderdetailwhere productno in(select productno from product where productname='52倍速光驅')and quantity between 2 and 4group by

12、orderno - （10）在訂單主表中查詢每個業(yè)務員的訂單數(shù)量（難易程度：易）select salerno,count(*) from ordermastergroup by salerno - （11）統(tǒng)計在業(yè)務科工作且在1973年或1967年出生的員工人數(shù)和平均工資。（難易程度：易）select count(*)員工個數(shù),avg(salary) 平均工資 from employee where department='業(yè)務科' and year(birthday) in (1973,1967) - （12）在訂單明細表中統(tǒng)計每種商品的銷售數(shù)量和金額，并按銷售金額的升序排序

13、輸出。（難易程度：易）select productno,sum(quantity) 銷售數(shù)量,sum(quantity*price) 銷售金額from orderdetailgroup by productnoorder by sum(quantity*price) asc - （13）統(tǒng)計客戶號為“C20050001”的客戶的訂單數(shù)，訂貨總額和平均訂貨金額（難易程度：易）select count(*), sum(ordersum),avg(ordersum)from ordermaster where customerno ='C2005001' - （14）統(tǒng)計每個客戶的訂

14、單數(shù)，訂貨總額和平均訂貨金額。（難易程度：中）select customerno,count(orderno),sum(ordersum),avg(ordersum) from ordermaster group by customerno- （15）查詢訂單中至少包含3種（含3種）以上商品的訂單編號及訂購次數(shù)，且訂購的每種商品數(shù)量在3件（含3件）以上。 （題意有歧義，可不做）若（把訂購次數(shù)理解為商品的種數(shù)）select orderno,count(*) from orderdetailwhere quantity>=3group by ordernohaving count(*)>

15、;=3- （16）查找訂購了“32M DRAM”的商品的客戶編號，客戶名稱，訂貨總數(shù)量和訂貨總金額。（難易程度：中）select c.customerno, customername,sum(quantity),sum(ordersum)from customer c,ordermaster om,orderdetail odwhere om.customerno = c.customernoand om.orderno = od.ordernoand c.customerno in(select customerno from ordermasterwhere orderno in(sele

16、ct orderno from orderdetailwhere productno in(select productno from product where productname='32M DRAM') group by c.customerno, customername - （17）查詢每個客戶訂購的商品編號，商品所屬類別，商品數(shù)量及訂貨金額，結果顯示客戶名稱，商品所屬類別，商品數(shù)量及訂貨金額，并按客戶編號升序和按訂貨金額的降序排序輸出。（難易程度：中）select c.customerno,customername,productclass,sum(quantit

17、y),sum(quantity*price) 訂貨金額 from customer c join (ordermaster om join orderdetail od on(om.orderno=od.orderno) join product p on(ductno= ductno) on (om.customerno=c.customerno)group by c.customerno,customername,productclassorder by c.customerno, sum(quantity*price) desc - （18）按商品類別查詢每類商品的

18、訂貨平均單價在280元（含280元）以上的訂貨總數(shù)量，訂貨平均單價和訂貨總金額。（難易程度：中）select productclass, sum(quantity), avg(price), sum(quantity*price) from orderdetail, productwhere ductno=ductnogroup by productclasshaving avg(price)>=280 - （19）查找至少有2次銷售的業(yè)務員名稱和銷售日期。（難易程度：中）select employeename,orderdate fr

19、om ordermaster om,employee ewhere e.employeeno = om.salerno and salerno in(select salerno from ordermastergroup by salernohaving count(salerno)>=2)order by employeename - （20）查詢銷售金額最大的客戶名稱和總貨款額（難易程度：中）select customername,r.total 總貨款額from customer,(select top 1 customerno,sum(ordersum) total from

20、ordermastergroup by customerno )as rwhere customer.customerno = r.customerno - （21）查找銷售總額小于5000元的銷售員編號，姓名和銷售額（難易程度：中）select employeeno,employeename,r.total 銷售額from employee,(select salerno,sum(ordersum) total from ordermastergroup by salernohaving sum(ordersum)<5000 )as rwhere employee.employeeno

21、 =r.salerno - （22）查找至少訂購了3種商品的客戶編號，客戶名稱，商品編號，商品名稱，數(shù)量和金額。（難易程度：中）select Customer.CustomerNo,CustomerName,Product.ProductNo,ProductName,Qty,Ordersum from Customer,Product,OrderDetail,OrderMasterwhere Customer.CustomerNo=OrderMaster.CustomerNo and OrderMaster.OrderNo=OrderDetail.OrderNo and Product.Pro

22、ductNo=OrderDetail.ProductNo and OrderMaster.CustomerNo in ( select CustomerNofrom ( select CustomerNo,ProductNo from OrderMaster,OrderDetail where OrderMaster.OrderNo=OrderDetail.OrderNo) a group by a.CustomerNo having count(distinct ProductNo)>=3 )-（23）查找同時訂購了商品為“P20070002”和商品編號為“P20070001”的商品的

23、客戶編號，客戶姓名，商品編號，商品名稱和銷售數(shù)量，按客戶編號排序輸出。（難易程度：難）select ordermaster.customerno,CustomerName,Product.ProductNo,ProductName,Quantity from Customer,Product,ordermaster,orderdetailwhere Customer.customerno = ordermaster.Customerno and ordermaster.OrderNo = orderdetail.OrderNo and ductno = orderdeta

24、ductno and ordermaster.OrderNo in(select OrderNo from OrderDetail where ProductNo='P2007001' )and ordermaster.OrderNo in (select OrderNo from OrderDetail where ProductNo='P2007002')-（24）計算每一商品每月的銷售金額總和，并將結果首先按銷售月份然后按訂貨金額降序排序輸出。（難易程度：中）Select productno,month(Orderdate) salemonth

25、,count(*) countproduct,sum(quantity*price) totalfrom orderdetail as od,ordermaster as omwhere od.orderno = om.ordernogroup by productno, month(Orderdate)order by countproduct,total desc-（25）查詢訂購了“鍵盤”商品的客戶姓名，訂貨數(shù)量和訂貨日期（難易程度：中）select CustomerName,quantity,orderdatefrom Customer,OrderDetail,OrderMasterw

26、here Customer.Customerno = ordermaster.Customerno and ordermaster.orderno = OrderDetail.ordernoand productno in(select ProductNo from Product where ProductName like '鍵盤')-（26）查詢每月（輸入錯誤，應為“沒有”）訂購“鍵盤”商品的客戶名稱。（難易程度：易）select customernamefrom customerwhere customerno not in(select customernofrom

27、ordermaster om,orderdetail od,product pwhere om.orderno = od.orderno and ductno = ductnoand productname = '鍵盤')-（27）查詢至少銷售了5種商品的銷售員編號，姓名，商品名稱，數(shù)量及相應的單價，并按銷售員編號排序輸出。（難易程度：中）select Employee.EmployeeNo,EmployeeName,ProductName,Quantity,Price from Employee,OrderDetail,Product,OrderMast

28、erwhere Employee.EmployeeNo=OrderMaster.SalerNo and OrderMaster.OrderNO=OrderDetail.OrderNO and OrderMaster.SalerNo in(select e.SaleNo from (select ordermaster.orderno as orderno,ordermaster.salerno as saleno,ductno as productno from ordermaster,orderdetail where ordermaster.orderno = o