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

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

《SQL数据库实验四复杂查询2解答Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《SQL数据库实验四复杂查询2解答Word文档下载推荐.docx（13页珍藏版）》请在冰豆网上搜索。

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.查询订单金额最高的订单编号、客户姓名、销售员名称和相应的订单金额。

SELECTc.orderNo,b.customerName,a.employeeName,orderSum

FROMEmployeea,Customerb,OrderMasterc

WHEREa.employeeNo=c.salerNoANDb.customerNo=c.customerNo

ANDorderSum=（SELECTMAX（orderSum）

FROMOrderMaster）

3.统计客户号为“C20050001”的客户的客户名、订单数、订货总额和平均订货金额。

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.统计每个客户的客户号、客户名、订单数、订货总额和平均订货金额。

SELECTb.customerNo客户号,customerName客户名,定单数=COUNT（a.orderNo）,

orderSum订货总额,（orderSum/（COUNT（a.orderNo）））平均订货金额

GROUPBYcustomerName,orderSum,b.customerNo

5.查询没有订购“52倍速光驱”或“17寸显示器”的客户编号、客户名称。

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寸显示器”的客户编号、客户名称。

SELECTDISTINCTd.customerNo,customerName

FROMCustomerc,OrderMasterd

WHEREc.customerNo=d.customerNoANDd.customerNo

IN（SELECTcustomerNo

FROMcustomer

WHEREcustomerNonotin

））））

7.查找订购了“32MDRAM”的商品的客户编号、客户名称、订货总数量和订货总金额。

SELECTb.customerNo,customerName,quantity,总金额=quantity*price

FROMOrderDetaila,Customerb,OrderMasterc,Productd

WHEREd.productNo=a.productNoANDa.orderNo=c.orderNo

ANDb.customerNo=c.customerNoANDproductName='

32MDRAM'

8.查询每个客户订购的商品编号、商品所属类别、商品数量及订货金额，结果显示客户名称、商品所属类别、商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出。

SELECTcustomerName,productClass,quantity,orderSum

ANDb.customerNo=c.customerNo

ORDERBYb.customerNo,orderSumDESC

9.按商品类别查询每类商品的订货平均单价在280元（含280元）以上的订货总数量、订货平均单价和订货总金额。

SELECTproductNo,订货总数量=SUM（quantity）,

订货平均单价=SUM（price）/COUNT（*）,订货总金额=sum（quantity*price）

FROMOrderDetail

GROUPBYproductNo

HAVINGSUM（price）/COUNT（*）>

=280

10.查找至少有2次销售的业务员名单、订单号和销售日期，并按业务员姓名升序排序输出。

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.查找销售金额最大的客户号、客户名称和总货贷额。

SELECTTOP1c.customerNo,customerName,MAX（总货贷额）总货贷额

FROM（SELECTcustomerNo,SUM（orderSum）总货贷额

GROUPBYcustomerNo）a,OrderMasterb,Customerc

WHEREa.customerNo=b.customerNoANDb.customerNo=c.customerNo

GROUPBYcustomerName,c.customerNo

ORDERBY总货贷额DESC

12.查找销售总额少于5000元的销售员编号、姓名和销售额。

SELECTsalerNo,employeeName,orderSum

FROMEmployeea,OrderMasterb

WHEREa.employeeNo=b.salerNo

GROUPBYorderSum,salerNo,employeeName

HAVINGorderSum<

5000

13.查找至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额，并按客户编号排序输出。

SELECTb.customerNo,customerName,d.productNo,productName,quantity,金额=quantity*price

FROMorderDetaila,Customerb,OrderMasterc,Productd

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*******”的商品的客户编号、客户姓名、商品编号、商品名称和销售数量，按客户编号排序输出。

SELECTc.customerNo,customerName,d.productNo,productName,quantity

ANDd.productNo='

P20070001'

ANDa.orderNoIN（SELECTorderNo

FROMOrderDetail

WHEREproductNo='

P20070002'

）

ORDERBYcustomerNo

15.计算每一商品每月的销售次数和销售金额总和，结果显示商品编号、销售月份、每月的销售次数和销售金额总和，并将结果首先按销售月份升序排序，再按订货金额降序输出。

selectproductNo,销售月份=month（orderDate）,销售次数=count（*）,销售金额总和=sum（quantity*price）

fromOrderDetaila,OrderMasterb

wherea.orderNo=b.orderNo

groupbyproductNo,month（orderDate）

orderbyMONTH（orderDate）,销售金额总和DESC