2024年复杂查询参考答案

数据库原理试验教材试验答案试验三在订单明细表中查询订单金额最高的订单1select orderNo,sumquantity*price orderSumfrom OrderDetailgroup byorderNohaving sumquantity*price=select maxorderSumfromselect orderNo,sumquantity*price orderSumfrom OrderDetailgroup byorderNo b找出最少被订购次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降23序排序输出SELECT a.productNo,orderNo,quantity,quantity*price moneyFROMOrderDetail a,SELECT productNoFROMOrderDetailGROUP BYproductNoHAVING count*=3bWHERE a.productNo=b.productNo输出成果.假如不是，舍弃该连接统计4取下一条连接统计，转直到所有的连接统计处理完成5,2,最后将成果输出

6.

③存在量词一般用在有关子查询中，请分别给出存在量词用在有关子查询和非有关子查询的查询例子答有关子查询SELECT studentName,classNoFROM StudentxWHERE EXISTSSELECT*FROM Scorea,Course bWHERE a.courseNo=b.courseNoAND a.studentNo=x.studentNo ANDcourseName=,操作系统，非有关子查询SELECT studentNo,classNoFROM StudentWHERENOT EXISTSSELECT*FROM Student王红WHERE studentName=ORDER BYa.productNo,quantity DESC⑶查找销售总额少于元的销售员编号、姓名和销售额5000select a.employeeNo,a.employeeName sumquantity*price sunmoneyzbfromEmployee a,OrderDetail rOrderMaster cwhere a.employeeNo=c.salerNo andb.orderNo=c.orderNogroup bya.employeeNo,a.employeeNamehaving sumquantity*price5000找出目前业绩未超出元的员工，并按销售业绩的降序排序输出45000select employeeNo,employeeName,orderSumfrom Employee a,select salerNo,sumorderSum orderSumfromOrderMaster groupby salerNohavingsumorderSum5000bwhere a.employeeNo=b.salerNoorder byorderSum desc查询订购的商品数量没有超出个的客户编号和客户名称510SELECT a•CustomerNo,CustomerNameFROM Customer aWHERE a.CustomerNo INSELECT CustomerNoFROM OrderMaster b,OrderDetail cWHEREb.orderNo=c.orderNoGROUP BYCustomerNoHAVING sumquantity10查找订货金额最大的客户名称和总货款6SELECT customerNamesumorderSumrFROM OrderMaster a,Customer b㊀WHERE a.custom rNo=b.CustomerNoGROUP BYa.CustomerNo,customerNameHAVING sumorderSum=SELECT maxorderSumFROMSELECTCustomerNo,sumorderSum orderSumFROMOrderMasterGROUP BYCustomerNoc查找最少订购了种商品的客户编号、客户名称、商品编号、商品名称、数量和金73额SELECT a.CustomerNo,CustomerName,b.ProductNo,ProductName,quantity,sumquantity*price sumFROMCustomera,Product b,OrderMasterc,OrderDetail dWHEREa.CustomerNo=c.CustomerNo andc.orderNo=d.orderNoand b.ProductNo=d.ProductNo andEXISTSSELECT CustomerNoFROMOrderMaster e,OrderDetail fWHERE e.orderNo=f.orderNo anda.customerNo=e.CustomerNoGROUP BYCustomerNoHAVING countdistinctProductNo=3GROUP BYa.CustomerNo,CustomerName,b.ProductNo,ProductName,quantity找出目前销售业绩超出元的业务员编号及销售业绩，并按销售业绩从大到小排序84000SELECT employeeNo,sumOrderFROM Employeea,SELECT salerNosumorderSum sumOrderzFROMOrderMasterGROUP BYsalerNo bWHEREa.employeeNo=b.salerNo ANDsum0rder4000ORDER BYsumOrder DESC求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排9歹限SELECT customerNo,productNo,sumquantity quantitys,sumquantity*price/sumquantity avgpriceFROMOrderMaster a,OrderDetail bWHEREa.orderNo=b.orderNoGROUP BYcustomerNo,productNoORDER BYcustomerNo,productNo查询业绩最佳的的业务员号、业务员名及其总销售金额10SELECT salerNo,employeeName,sumorderSumFROM Employeea OrderMaster bzWHERE a.employeeNo=b.salerNoGROUP BYsalerNo,employeeNamehaving sumorderSum=select maxorderSumfromselect sumorderSumorderSumfrom OrderMastergroupby salerNox查询订购的商品最少包括了订单中所订购商品的订单11“03010001”SELECTFROM OrderMaster aWHERE notexistsselect1fromOrderDetaily whereorderNo=03010001*and notexistsselect*fromOrderDetailz wherey.productNo=z.productNo and・a orderNo=z.orderNo查询总订购金额超出客户的总订购金额的客户号、客户名及其住址12“C0002”SELECT a.customerNo,customerName,addressFROM OrderMastera,Customer b㊀WHEREa.custom rNo=b.customerNoGROUP BYa.customerNo,customerName,addressHAVING sumorderSum=SELECT sumorderSumFROMOrderMaster1WHERE customerNo=C0002GROUP BYcustomerNo查询总销售金额最高的销售员编号、订单编号、订单日期和订单金额13SELECT salerNo,b.orderNo,orderDate,orderSumFROM EmployeeOrderMaster bWHEREa.employeeNo=b.salerNoand orderSum=select maxorderSumfromOrderMaster用存在量词查找没有订货统计的客户名称14SELECT customerNameFROMCustomer cWHERENOT EXISTSSELECT*FROMOrderMasteraWHERE a.customerNo=c.customerNo查询既订购了倍速光驱”商品，又订购了寸显示器”商品的客户编15“52“17号、订单编号和订单金额Select customerNo,orderNo orderSumzfromOrderMasterwhere customerNoinselect customerNofrom OrderMastera,OrderDetail Product cwhere a.orderNo=b-orderNo andb.productNo=c.productNo and倍速光百区11productName=52and customerNoin selectcustomerNofromOrderMastera,OrderDetail b,Productcwherea.orderNo=b.orderNo and寸・b productNo=c.productNo andproductName=17显示器1求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排16列SELECT customerNo,productNo^sumquantity quantitys,sumquantity*price/sumquantity avgpriceFROMOrderMastera,OrderDetail bWHEREa.orderNo=b.orderNoGROUP BYcustomerNo,productNoORDER BYcustomerNo,productNo试验问题17

①存在量词与集合运算、连接运算和全称量词之间的关系怎样？它们能够相互替代吗？IN给出你的理由答存在量词能够用连接运算或集合运算来实现，而中没有全称量词，只EXISTS INSQL能用存在量词和取非运算来实现;

②请写出例的执行过程

2.51［例］查询最少销售了种商品的销售员编号、姓名、商品名称、数量及对应的单价，

2.515并按销售员编号排序输出分析

①结构一个子查询，针对外查询中的每个销售员，判断其是否销售了种以上的商品,使5用有关子查询语句为

②SQLSELECT salerNo,employeeName,productName,quantity,priceFROM Employeea,OrderMaster b,OrderDetail c,Product dWHEREa.employeeNo=salerNo ANDb.orderNo=c.orderNo ANDc.productNo=d.productNoAND EXISTSSELECTsalerNoFROM OrderMastere,OrderDetail/WHEREe.orderNo=/.orderNo ANDo.employeeNo=salerNoGROUP BYsalerNoHAVING countdistinctproductNo=5ORDER BYsalerNo答.首先将表进行连接1Employeea,OrderMasterb,OrderDetail c,Product d.对连接后的统计，取出员工编号，判断是否最少销售了种商品25假如是，将这五个值作为

3.salerNo,employeeName,productName,quantity,price。