复杂查询参考答案Word文件下载.docx
《复杂查询参考答案Word文件下载.docx》由会员分享,可在线阅读,更多相关《复杂查询参考答案Word文件下载.docx(7页珍藏版)》请在冰豆网上搜索。
=3)b
WHEREa.productNo=b.productNo
ORDERBYa.productNo,quantityDESC
(3)查找销售总额少于5000元的销售员编号、姓名和销售额。
selecta.employeeNo,a.employeeName,sum(quantity*price)sunmoney
fromEmployeea,OrderDetailb,OrderMasterc
wherea.employeeNo=c.salerNoandb.orderNo=c.orderNo
groupbya.employeeNo,a.employeeName
havingsum(quantity*price)<
5000
(4)找出目前业绩未超过5000元的员工,并按销售业绩的降序排序输出。
selectemployeeNo,employeeName,orderSum
fromEmployeea,(selectsalerNo,sum(orderSum)orderSum
fromOrderMaster
groupbysalerNo
havingsum(orderSum)<
5000)b
wherea.employeeNo=b.salerNo
orderbyorderSumdesc
(5)查询订购的商品数量没有超过10个的客户编号和客户名称。
SELECTa.CustomerNo,CustomerName
FROMCustomera
WHEREa.CustomerNoIN(SELECTCustomerNo
FROMOrderMasterb,OrderDetailc
WHEREb.orderNo=c.orderNo
GROUPBYCustomerNo
HAVINGsum(quantity)<
10)
(6)查找订货金额最大的客户名称和总货款。
SELECTcustomerName,sum(orderSum)
FROMOrderMastera,Customerb
WHEREa.customerNo=b.customerNo
GROUPBYa.customerNo,customerName
HAVINGsum(orderSum)=(SELECTmax(orderSum)
FROM(SELECTcustomerNo,sum(orderSum)orderSum
FROMOrderMaster
GROUPBYcustomerNo)c)
(7)查找至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额。
SELECTa.CustomerNo,CustomerName,b.ProductNo,
ProductName,quantity,sum(quantity*price)sum
FROMCustomera,Productb,OrderMasterc,OrderDetaild
WHEREa.CustomerNo=c.CustomerNoandc.orderNo=d.orderNo
andb.ProductNo=d.ProductNoand
EXISTS(
SELECTCustomerNo
FROMOrderMastere,OrderDetailf
WHEREe.orderNo=f.orderNoanda.customerNo=e.customerNo
GROUPBYCustomerNo
HAVINGcount(distinctProductNo)>
=3)
GROUPBYa.CustomerNo,CustomerName,b.ProductNo,
ProductName,quantity
(8)找出目前销售业绩超过4000元的业务员编号及销售业绩,并按销售业绩从大到小排序。
SELECTemployeeNo,sumOrder
FROMEmployeea,(SELECTsalerNo,sum(orderSum)sumOrder
GROUPBYsalerNo)b
WHEREa.employeeNo=b.salerNoANDsumOrder>
4000
ORDERBYsumOrderDESC
(9)求每位客户订购的每种商品的总数量及平均单价,并按客户号、商品号从小到大排列。
SELECTcustomerNo,productNo,sum(quantity)quantitys,
(sum(quantity*price)/sum(quantity))avgprice
FROMOrderMastera,OrderDetailb
WHEREa.orderNo=b.orderNo
GROUPBYcustomerNo,productNo
ORDERBYcustomerNo,productNo
(10)查询业绩最好的的业务员号、业务员名及其总销售金额。
SELECTsalerNo,employeeName,sum(orderSum)
FROMEmployeea,OrderMasterb
WHEREa.employeeNo=b.salerNo
GROUPBYsalerNo,employeeName
havingsum(orderSum)=(selectmax(orderSum)
from(selectsum(orderSum)orderSum
groupbysalerNo)x)
(11)查询订购的商品至少包含了订单“200803010001”中所订购商品的订单。
SELECT*
FROMOrderMastera
WHEREnotexists
(select*
fromOrderDetaily
whereorderNo='
200803010001'
andnotexists
fromOrderDetailz
wherey.productNo=z.productNoand
a.orderNo=z.orderNo))
(12)查询总订购金额超过“C20070002”客户的总订购金额的客户号、客户名及其住址。
SELECTa.customerNo,customerName,address
WHEREa.customerNo=b.customerNo
GROUPBYa.customerNo,customerName,address
HAVINGsum(orderSum)>
=(SELECTsum(orderSum)
FROMOrderMaster
WHEREcustomerNo='
C20070002'
GROUPBYcustomerNo)
(13)查询总销售金额最高的销售员编号、订单编号、订单日期和订单金额。
SELECTsalerNo,b.orderNo,orderDate,orderSum
andorderSum=(selectmax(orderSum)
fromOrderMaster)
(14)用存在量词查找没有订货记录的客户名称。
SELECTcustomerName
FROMCustomerc
WHERENOTEXISTS
(SELECT*FROMOrderMastera
WHEREa.customerNo=c.customerNo)
(15)查询既订购了“52倍速光驱”商品,又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。
SelectcustomerNo,orderNo,orderSum
fromOrderMaster
wherecustomerNoin
(selectcustomerNo
fromOrderMastera,OrderDetailb,Productc
wherea.orderNo=b.orderNoandb.productNo=c.productNoand
productName='
52倍速光驱'
)
andcustomerNoin(selectcustomerNo
wherea.orderNo=b.orderNoand
b.productNo=c.productNoand
productName='
17寸显示器'
(16)求每位客户订购的每种商品的总数量及平均单价,并按客户号、商品号从小到大排列。
(17)实验问题:
①存在量词与集合运算IN、连接运算和全称量词之间的关系如何?
它们可以互相替换吗?
给出你的理由。
答:
存在量词EXISTS可以用连接运算或集合运算IN来实现,而SQL中没有全称量词,只能用存在量词和取非运算来实现;
②请写出例2.51的执行过程。
[例2.51]查询至少销售了5种商品的销售员编号、姓名、商品名称、数量及相应的单价,并按销售员编号排序输出。
分析:
①构造一个子查询,针对外查询中的每个销售员,判断其是否销售了5种以上的商品,使用相关子查询。
②SQL语句为:
SELECTsalerNo,employeeName,productName,quantity,price
FROMEmployeea,OrderMasterb,OrderDetailc,Productd
WHEREa.employeeNo=salerNoANDb.orderNo=c.orderNoANDc.productNo=d.productNo
ANDEXISTS(
SELECTsalerNo
FROMOrderMastere,OrderDetailf
WHEREe.orderNo=f.orderNoANDa.employeeNo=salerNo
GROUPBYsalerNo
HAVINGcount(distinctproductNo)>
=5)
ORDERBYsalerNo
1.首先将表Employeea,OrderMasterb,OrderDetailc,Productd进行连接
2.对连接后的记录,取出员工编号,判断是否至少销售了5种商品
3.如果是,将salerNo,employeeName,productName,quantity,price这五个值作为输出结果
4.如果不是,舍弃该连接记录
5.取下一条连接记录,转2,直到所有的连接记录处理完毕
6.最后将结果输出
③存在量词一般用在相关子查询中,请分别给出存在量词用在相关子查询和非相关子查询的查询例子。
相关子查询:
SELECTstudentName,classNo
FROMStudentx
WHEREEXISTS
(SELECT*FROMScorea,Courseb
WHEREa.courseNo=b.courseNo
ANDa.studentNo=x.studentNoANDcourseName=’操作系统’)
非相关子查询:
SELECTstudentNo,classNo
FROMStudent
(SELECT*
FROMStudent
WHEREstudentName=‘王红’)
WelcomeTo
Download!
!
欢迎您的下载,资料仅供参考!