SQL数据库实验四复杂查询2解答Word文档下载推荐.docx

1、2 查询订单金额最高的订单编号、客户姓名、销售员名称和相应的订单金额。3 统计客户号为“C20050001”的客户的客户名、订单数、订货总额和平均订货金额。（表头用中文显示）4 统计每个客户的客户号、客户名、订单数、订货总额和平均订货金额。5 查询没有订购“52倍速光驱”或“17寸显示器”的客户编号、客户名称。（包括没有订购任何商品的客户）6 查询有订购商品，但没有订购“52倍速光驱”或“17寸显示器”的客户编号、客户名称。 7 查找订购了 “32M DRAM”的商品的客户编号、客户名称、订货总数量和订货总金额。8 查询每个客户订购的商品编号、商品所属类别、商品数量及订货金额，结果显示客户名称

2、、商品所属类别、商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出。9 按商品类别查询每类商品的订货平均单价在280元（含280元）以上的订货总数量、订货平均单价和订货总金额。10 查找至少有2次销售的业务员名单、订单号和销售日期，并按业务员姓名升序排序输出。日期按yyyy-mm-dd显示。11 查找销售金额最大的客户号、客户名称和总货贷额。12 查找销售总额少于5000元的销售员编号、姓名和销售额。13 查找至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额，并按客户编号排序输出。14 查找同时订购了商品编号为“P2*”和商品编号为“P2*”的商品的客户编号、

3、客户姓名、商品编号、商品名称和销售数量，按客户编号排序输出。15 计算每一商品每月的销售次数和销售金额总和，结果显示商品编号、销售月份、每月的销售次数和销售金额总和，并将结果首先按销售月份升序排序，再按订货金额降序输出。三、实验结果1.统计订单主表的订单金额。SQL语句：UPDATE OrderMasterSET orderSum=金额From OrderMaster a,（SELECT orderNo,SUM（quantity*price） 金额 FROM OrderDetail GROUP BY orderNo） bWHERE a.orderNo=b.orderNo查询结果：2.查询订单金

4、额最高的订单编号、客户姓名、销售员名称和相应的订单金额。SELECT c.orderNo,b.customerName,a.employeeName,orderSumFROM Employee a,Customer b,OrderMaster cWHERE a.employeeNo=c.salerNo AND b.customerNo=c.customerNo AND orderSum=（SELECT MAX（orderSum） FROM OrderMaster ） 3.统计客户号为“C20050001”的客户的客户名、订单数、订货总额和平均订货金额。SELECT customerName 客

5、户名,定单数=COUNT（a.orderNo）, orderSum 订货总额,（orderSum/COUNT（a.orderNo） 平均订货金额FROM OrderDetail a,Customer b,OrderMaster cWHERE a.orderNo=c.orderNo AND b.customerNo=c.customerNo AND c.customerNo=C20050001GROUP BY customerName,orderSum4.统计每个客户的客户号、客户名、订单数、订货总额和平均订货金额。SELECT b.customerNo 客户号,customerName 客户名

6、,定单数=COUNT（a.orderNo）, orderSum 订货总额,（orderSum/（COUNT（a.orderNo） 平均订货金额GROUP BY customerName,orderSum,b.customerNo5.查询没有订购“52倍速光驱”或“17寸显示器”的客户编号、客户名称。SELECT customerNo,customerNameFROM customer WHERE customerNo not in （SELECT a.customerNo FROM customer a,ordermaster b WHERE a.customerNo=b.customerNo

7、 and orderNo in （SELECT orderNo FROM orderdetail WHERE productNo in （SELECT productNo FROM product WHERE productName=52倍速光驱 or productName=17寸显示器）6.查询有订购商品，但没有订购“52倍速光驱”或“17寸显示器”的客户编号、客户名称。SELECT DISTINCT d.customerNo,customerNameFROM Customer c,OrderMaster dWHERE c.customerNo=d.customerNo AND d.cus

8、tomerNo IN （SELECT customerNo FROM customer WHERE customerNo not in ）7.查找订购了 “32M DRAM”的商品的客户编号、客户名称、订货总数量和订货总金额。SELECT b.customerNo,customerName,quantity,总金额=quantity*priceFROM OrderDetail a,Customer b,OrderMaster c,Product dWHERE d.productNo=a.productNo AND a.orderNo=c.orderNo AND b.customerNo=c.c

9、ustomerNo AND productName=32M DRAM8.查询每个客户订购的商品编号、商品所属类别、商品数量及订货金额，结果显示客户名称、商品所属类别、商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出。SELECT customerName,productClass,quantity,orderSum AND b.customerNo=c.customerNo ORDER BY b.customerNo,orderSum DESC 9.按商品类别查询每类商品的订货平均单价在280元（含280元）以上的订货总数量、订货平均单价和订货总金额。SELECT product

10、No,订货总数量=SUM（quantity）, 订货平均单价=SUM（price）/COUNT（*）,订货总金额=sum（quantity*price）FROM OrderDetailGROUP BY productNoHAVING SUM（price）/COUNT（*）=28010.查找至少有2次销售的业务员名单、订单号和销售日期，并按业务员姓名升序排序输出。SELECT employeeNo,employeeName,orderNo, convert（char（10）,orderDate,120） orderDateFROM Employee a,OrderMaster b, （SELEC

11、T salerNo FROM OrderMaster GROUP BY salerNo HAVING COUNT（*）=2） cWHERE a.employeeNo=b.salerNo AND b.salerNo=c.salerNoGROUP BY employeeNo,employeeName,orderNo,orderDateORDER BY employeeName11.查找销售金额最大的客户号、客户名称和总货贷额。SELECT TOP 1 c.customerNo,customerName,MAX（总货贷额） 总货贷额FROM （SELECT customerNo,SUM（orderS

12、um） 总货贷额 GROUP BY customerNo） a,OrderMaster b,Customer cWHERE a.customerNo=b.customerNo AND b.customerNo=c.customerNo GROUP BY customerName,c.customerNoORDER BY 总货贷额DESC12.查找销售总额少于5000元的销售员编号、姓名和销售额。SELECT salerNo,employeeName,orderSumFROM Employee a,OrderMaster bWHERE a.employeeNo=b.salerNoGROUP BY

13、 orderSum,salerNo,employeeNameHAVING orderSum=3） h）14.查找同时订购了商品编号为“P2*”和商品编号为“P2*”的商品的客户编号、客户姓名、商品编号、商品名称和销售数量，按客户编号排序输出。SELECT c.customerNo,customerName,d.productNo,productName,quantity AND d.productNo=P20070001 AND a.orderNo IN（SELECT orderNo FROM OrderDetail WHERE productNo=P20070002）ORDER BY customerNo15.计算每一商品每月的销售次数和销售金额总和，结果显示商品编号、销售月份、每月的销售次数和销售金额总和，并将结果首先按销售月份升序排序，再按订货金额降序输出。select productNo,销售月份=month（orderDate）,销售次数=count（*）,销售金额总和=sum（quantity*price）from OrderDetail a,OrderMaster bwhere a.orderNo=b.orderNogroup by productNo,month（orderDate）order by MONTH（orderDate）,销售金额总和DESC