SQL数据库实验四复杂查询2解答.docx
《SQL数据库实验四复杂查询2解答.docx》由会员分享，可在线阅读，更多相关《SQL数据库实验四复杂查询2解答.docx（13页珍藏版）》请在冰豆网上搜索。
SQL数据库实验四复杂查询2解答
实验报告
学号：
姓名：
项目名称：
实验四复杂查询
（2）
一一、实验目的
（1）熟练掌握SQL语句的使用。
（2）熟练使用SQL语句进行复杂的连接操作。
二、实验内容及要求
导入实验三中保存的订单数据库OrderDB.mdf。
完成之后，进行以下的实验。
写出相对应的SQL语句，并给出查询结果。
1．统计订单主表的订单金额。
（用订单明细表中的订单汇总金额更新订单主表相应的订单金额属性）
2．查询订单金额最高的订单编号、客户姓名、销售员名称和相应的订单金额。
3．统计客户号为“C20050001”的客户的客户名、订单数、订货总额和平均订货金额。
（表头用中文显示）
4．统计每个客户的客户号、客户名、订单数、订货总额和平均订货金额。
（表头用中文显示）
5．查询没有订购“52倍速光驱”或“17寸显示器”的客户编号、客户名称。
（包括没有订购任何商品的客户）
6．查询有订购商品，但没有订购“52倍速光驱”或“17寸显示器”的客户编号、客户名称。
7．查找订购了“32MDRAM”的商品的客户编号、客户名称、订货总数量和订货总金额。
8．查询每个客户订购的商品编号、商品所属类别、商品数量及订货金额，结果显示客户名称、商品所属类别、商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出。
9．按商品类别查询每类商品的订货平均单价在280元（含280元）以上的订货总数量、订货平均单价和订货总金额。
10．查找至少有2次销售的业务员名单、订单号和销售日期，并按业务员姓名升序排序输出。
日期按yyyy-mm-dd显示。
11．查找销售金额最大的客户号、客户名称和总货贷额。
12．查找销售总额少于5000元的销售员编号、姓名和销售额。
13．查找至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额，并按客户编号排序输出。
14．查找同时订购了商品编号为“P2*******”和商品编号为“P2*******”的商品的客户编号、客户姓名、商品编号、商品名称和销售数量，按客户编号排序输出。
15．计算每一商品每月的销售次数和销售金额总和，结果显示商品编号、销售月份、每月的销售次数和销售金额总和，并将结果首先按销售月份升序排序，再按订货金额降序输出。
三、实验结果
1.统计订单主表的订单金额。
SQL语句：
UPDATEOrderMaster
SETorderSum=金额
FromOrderMastera,（SELECTorderNo,SUM（quantity*price）金额
FROMOrderDetail
GROUPBYorderNo）b
WHEREa.orderNo=b.orderNo
查询结果：
2.查询订单金额最高的订单编号、客户姓名、销售员名称和相应的订单金额。
SQL语句：
SELECTc.orderNo,b.customerName,a.employeeName,orderSum
FROMEmployeea,Customerb,OrderMasterc
WHEREa.employeeNo=c.salerNoANDb.customerNo=c.customerNo
ANDorderSum=（SELECTMAX（orderSum）
FROMOrderMaster）
查询结果：
3.统计客户号为“C20050001”的客户的客户名、订单数、订货总额和平均订货金额。
（表头用中文显示）
SQL语句：
SELECTcustomerName客户名,定单数=COUNT（a.orderNo）,
orderSum订货总额,（orderSum/COUNT（a.orderNo））平均订货金额
FROMOrderDetaila,Customerb,OrderMasterc
WHEREa.orderNo=c.orderNoANDb.customerNo=c.customerNo
ANDc.customerNo='C20050001'
GROUPBYcustomerName,orderSum
查询结果：
4.统计每个客户的客户号、客户名、订单数、订货总额和平均订货金额。
（表头用中文显示）
SQL语句：
SELECTb.customerNo客户号,customerName客户名,定单数=COUNT（a.orderNo）,
orderSum订货总额,（orderSum/（COUNT（a.orderNo）））平均订货金额
FROMOrderDetaila,Customerb,OrderMasterc
WHEREa.orderNo=c.orderNoANDb.customerNo=c.customerNo
GROUPBYcustomerName,orderSum,b.customerNo
查询结果：
5.查询没有订购“52倍速光驱”或“17寸显示器”的客户编号、客户名称。
（包括没有订购任何商品的客户）
SQL语句：
SELECTcustomerNo,customerName
FROMcustomer
WHEREcustomerNonotin
（SELECTa.customerNo
FROMcustomera,ordermasterb
WHEREa.customerNo=b.customerNo
andorderNoin
（SELECTorderNo
FROMorderdetail
WHEREproductNoin
（SELECTproductNo
FROMproduct
WHEREproductName='52倍速光驱'or
productName='17寸显示器'）））
查询结果：
6.查询有订购商品，但没有订购“52倍速光驱”或“17寸显示器”的客户编号、客户名称。
SQL语句：
SELECTDISTINCTd.customerNo,customerName
FROMCustomerc,OrderMasterd
WHEREc.customerNo=d.customerNoANDd.customerNo
IN（SELECTcustomerNo
FROMcustomer
WHEREcustomerNonotin
（SELECTa.customerNo
FROMcustomera,ordermasterb
WHEREa.customerNo=b.customerNo
andorderNoin
（SELECTorderNo
FROMorderdetail
WHEREproductNoin
（SELECTproductNo
FROMproduct
WHEREproductName='52倍速光驱'or
productName='17寸显示器'））））
查询结果：
7.查找订购了“32MDRAM”的商品的客户编号、客户名称、订货总数量和订货总金额。
SQL语句：
SELECTb.customerNo,customerName,quantity,总金额=quantity*price
FROMOrderDetaila,Customerb,OrderMasterc,Productd
WHEREd.productNo=a.productNoANDa.orderNo=c.orderNo
ANDb.customerNo=c.customerNoANDproductName='32MDRAM'
查询结果：
8.查询每个客户订购的商品编号、商品所属类别、商品数量及订货金额，结果显示客户名称、商品所属类别、商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出。
SQL语句：
SELECTcustomerName,productClass,quantity,orderSum
FROMOrderDetaila,Customerb,OrderMasterc,Productd
WHEREd.productNo=a.productNoANDa.orderNo=c.orderNo
ANDb.customerNo=c.customerNo
ORDERBYb.customerNo,orderSumDESC
查询结果：
9.按商品类别查询每类商品的订货平均单价在280元（含280元）以上的订货总数量、订货平均单价和订货总金额。
SQL语句：
SELECTproductNo,订货总数量=SUM（quantity）,
订货平均单价=SUM（price）/COUNT（*）,订货总金额=sum（quantity*price）
FROMOrderDetail
GROUPBYproductNo
HAVINGSUM（price）/COUNT（*）>=280
查询结果：
10.查找至少有2次销售的业务员名单、订单号和销售日期，并按业务员姓名升序排序输出。
日期按yyyy-mm-dd显示。
SQL语句：
SELECTemployeeNo,employeeName,orderNo,
convert（char（10）,orderDate,120）orderDate
FROMEmployeea,OrderMasterb,
（SELECTsalerNo
FROMOrderMaster
GROUPBYsalerNo
HAVINGCOUNT（*）>=2）c
WHEREa.employeeNo=b.salerNoANDb.salerNo=c.salerNo
GROUPBYemployeeNo,employeeName,orderNo,orderDate
ORDERBYemployeeName
查询结果：
11.查找销售金额最大的客户号、客户名称和总货贷额。
SQL语句：
SELECTTOP1c.customerNo,customerName,MAX（总货贷额）总货贷额
FROM（SELECTcustomerNo,SUM（orderSum）总货贷额
FROMOrderMaster
GROUPBYcustomerNo）a,OrderMasterb,Customerc
WHEREa.customerNo=b.customerNoANDb.customerNo=c.customerNo
GROUPBYcustomerName,c.customerNo
ORDERBY总货贷额DESC
查询结果：
12.查找销售总额少于5000元的销售员编号、姓名和销售额。
SQL语句：
SELECTsalerNo,employeeName,orderSum
FROMEmployeea,OrderMasterb
WHEREa.employeeNo=b.salerNo
GROUPBYorderSum,salerNo,employeeName
HAVINGorderSum<5000
查询结果：
13.查找至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额，并按客户编号排序输出。
SQL语句：
SELECTb.customerNo,customerName,d.productNo,productName,quantity,金额=quantity*price
FROMorderDetaila,Customerb,OrderMasterc,Productd
WHEREd.productNo=a.productNoANDa.orderNo=c.orderNo
ANDb.customerNo=c.customerNo
ANDb.customerNo
IN（SELECTcustomerNo
FROM（SELECTcustomerNo,COUNT（DISTINCTproductNo）product
FROM（SELECTcustomerNo,productNo
FROMorderDetaile,OrderMasterf
WHEREe.orderNo=f.orderNo）g
GROUPBYcustomerNo
HAVINGCOUNT（DISTINCTproductNo）>=3）h）
查询结果：
14.查找同时订购了商品编号为“P2*******”和商品编号为“P2*******”的商品的客户编号、客户姓名、商品编号、商品名称和销售数量，按客户编号排序输出。
SQL语句：
SELECTc.customerNo,customerName,d.productNo,productName,quantity
FROMOrderDetaila,Customerb,OrderMasterc,Productd
WHEREd.productNo=a.productNoANDa.orderNo=c.orderNo
ANDb.customerNo=c.customerNo
ANDd.productNo='P20070001'
ANDa.orderNoIN（SELECTorderNo
FROMOrderDetail
WHEREproductNo='P20070002'）
ORDERBYcustomerNo
查询结果：
15.计算每一商品每月的销售次数和销售金额总和，结果显示商品编号、销售月份、每月的销售次数和销售金额总和，并将结果首先按销售月份升序排序，再按订货金额降序输出。
SQL语句：
selectproductNo,销售月份=month（orderDate）,销售次数=count（*）,销售金额总和=sum（quantity*price）
fromOrderDetaila,OrderMasterb
wherea.orderNo=b.orderNo
groupbyproductNo,month（orderDate）
orderbyMONTH（orderDate）,销售金额总和DESC
查询结果：
升级会员 