复杂查询参考答案

数据库原理实验教材实验答案实验三(1)在订单明细表中查询订单金额最高的订单。selectorderNo,sum(quantity*price)orderSumfromOrderDetailgroupbyorderNohavingsum(quantity*price)=(selectmax(orderSum)from(selectorderNo,sum(quantity*price)orderSumfromOrderDetailgroupbyorderNo)b)(2)找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。SELECTa.productNo,orderNo,quantity,(quantity*price)moneyFROMOrderDetaila,(SELECTproductNoFROMOrderDetailGROUPBYproductNoHAVINGcount(*)=3)bWHEREa.productNo=b.productNoORDERBYa.productNo,quantityDESC(3)查找销售总额少于5000元的销售员编号、姓名和销售额。selecta.employeeNo,a.employeeName,sum(quantity*price)sunmoneyfromEmployeea,OrderDetailb,OrderMastercwherea.employeeNo=c.salerNoandb.orderNo=c.orderNogroupbya.employeeNo,a.employeeNamehavingsum(quantity*price)5000(4)找出目前业绩未超过5000元的员工，并按销售业绩的降序排序输出。selectemployeeNo,employeeName,orderSumfromEmployeea,(selectsalerNo,sum(orderSum)orderSumfromOrderMastergroupbysalerNohavingsum(orderSum)5000)bwherea.employeeNo=b.salerNoorderbyorderSumdesc(5)查询订购的商品数量没有超过10个的客户编号和客户名称。SELECTa.CustomerNo,CustomerNameFROMCustomeraWHEREa.CustomerNoIN(SELECTCustomerNoFROMOrderMasterb,OrderDetailcWHEREb.orderNo=c.orderNoGROUPBYCustomerNoHAVINGsum(quantity)10)(6)查找订货金额最大的客户名称和总货款。SELECTcustomerName,sum(orderSum)FROMOrderMastera,CustomerbWHEREa.customerNo=b.customerNoGROUPBYa.customerNo,customerNameHAVINGsum(orderSum)=(SELECTmax(orderSum)FROM(SELECTcustomerNo,sum(orderSum)orderSumFROMOrderMasterGROUPBYcustomerNo)c)(7)查找至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额。SELECTa.CustomerNo,CustomerName,b.ProductNo,ProductName,quantity,sum(quantity*price)sumFROMCustomera,Productb,OrderMasterc,OrderDetaildWHEREa.CustomerNo=c.CustomerNoandc.orderNo=d.orderNoandb.ProductNo=d.ProductNoandEXISTS(SELECTCustomerNoFROMOrderMastere,OrderDetailfWHEREe.orderNo=f.orderNoanda.customerNo=e.customerNoGROUPBYCustomerNoHAVINGcount(distinctProductNo)=3)GROUPBYa.CustomerNo,CustomerName,b.ProductNo,ProductName,quantity(8)找出目前销售业绩超过4000元的业务员编号及销售业绩，并按销售业绩从大到小排序。SELECTemployeeNo,sumOrderFROMEmployeea,(SELECTsalerNo,sum(orderSum)sumOrderFROMOrderMasterGROUPBYsalerNo)bWHEREa.employeeNo=b.salerNoANDsumOrder4000ORDERBYsumOrderDESC(9)求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。SELECTcustomerNo,productNo,sum(quantity)quantitys,(sum(quantity*price)/sum(quantity))avgpriceFROMOrderMastera,OrderDetailbWHEREa.orderNo=b.orderNoGROUPBYcustomerNo,productNoORDERBYcustomerNo,productNo(10)查询业绩最好的的业务员号、业务员名及其总销售金额。SELECTsalerNo,employeeName,sum(orderSum)FROMEmployeea,OrderMasterbWHEREa.employeeNo=b.salerNoGROUPBYsalerNo,employeeNamehavingsum(orderSum)=(selectmax(orderSum)from(selectsum(orderSum)orderSumfromOrderMastergroupbysalerNo)x)(11)查询订购的商品至少包含了订单“200803010001”中所订购商品的订单。SELECT*FROMOrderMasteraWHEREnotexists(select*fromOrderDetailywhereorderNo='200803010001'andnotexists(select*fromOrderDetailzwherey.productNo=z.productNoanda.orderNo=z.orderNo))(12)查询总订购金额超过“C20070002”客户的总订购金额的客户号、客户名及其住址。SELECTa.customerNo,customerName,addressFROMOrderMastera,CustomerbWHEREa.customerNo=b.customerNoGROUPBYa.customerNo,customerName,addressHAVINGsum(orderSum)=(SELECTsum(orderSum)FROMOrderMasterWHEREcustomerNo='C20070002'GROUPBYcustomerNo)(13)查询总销售金额最高的销售员编号、订单编号、订单日期和订单金额。SELECTsalerNo,b.orderNo,orderDate,orderSumFROMEmployeea,OrderMasterbWHEREa.employeeNo=b.salerNoandorderSum=(selectmax(orderSum)fromOrderMaster)(14)用存在量词查找没有订货记录的客户名称。SELECTcustomerNameFROMCustomercWHERENOTEXISTS(SELECT*FROMOrderMasteraWHEREa.customerNo=c.customerNo)(15)查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。SelectcustomerNo,orderNo,orderSumfromOrderMasterwherecustomerNoin(selectcustomerNofromOrderMastera,OrderDetailb,Productcwherea.orderNo=b.orderNoandb.productNo=c.productNoandproductName='52倍速光驱')andcustomerNoin(selectcustomerNofromOrderMastera,OrderDetailb,Productcwherea.orderNo=b.orderNoandb.productNo=c.productNoandproductName='17寸显示器')(16)求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。SELECTcustomerNo,productNo,sum(quantity)quantitys,(sum(quantity*price)/sum(quantity))avgpriceFROMOrderMastera,OrderDetailbWHEREa.orderNo=b.orderNoGROUPBYcustomerNo,productNoORDERBYcustomerNo,productNo(17)实验问题：①存在量词与集合运算IN、连接运算和全称量词之间的关系如何？它们可以互相替换吗？给出你的理由。答：存在量词EXISTS可以用连接运算或集合运算IN来实现，而SQL中没有全称量词，只能用存在量词和取非运算来实现；②请写出例2.51的执行过程。[例2.51]查询至少销售了5种商品的销售员编号、姓名、商品名称、数量及相应的单价，并按销售员编号排序输出。分析：①构造一个子查询，针对外查询中的每个销售员，判断其是否销售了5种以上的商品，使用相关子查询。②SQL语句为：SELECTsalerNo,employeeName,productName,quantity,priceFROMEmployeea,OrderMasterb,OrderDetailc,ProductdWHEREa.employeeNo=salerNoANDb.orderNo=c.orderNoANDc.productNo=d.productNoANDEXISTS(SELECTsalerNoFROMOrderMastere,OrderDetailfWHEREe.orderNo=f.orderNoANDa.employeeNo=salerNoGROUPBYsalerNoHAVINGcount(distinctproductNo)=5)ORDERBYsalerNo答：1.首先将表Employeea,OrderMasterb,OrderDetailc,Productd进行连接2.对连接后的记录，取出员工编号，判断是否至少销售了5种商品3.如果是，将salerNo,employeeName,productName,quantity,price这五个值作为输出结果4．如果不是，舍弃该连接记录5.取下一条连接记录，转2，直到所有的连接记录处理完毕6.最后将结果输出③存在量词一般用在相关子查询中，请分别给出存在量词用在相关子查询和非相关子查询的查询例子。答：相关子查询：SELECTstudentName,classNoFROMStudentxWHEREEXISTS(SELECT*FROMScorea,CoursebWHEREa.courseNo=b.courseNoANDa.studentNo=x.studentNoANDcourseName=’操作系统’）非相关子查询