SQL数据库实验四复杂查询2解答.docx
- 文档编号:3939536
- 上传时间:2022-11-26
- 格式:DOCX
- 页数:13
- 大小:714.86KB
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
查询结果:
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 数据库 实验 复杂 查询 解答