数据库实验报告复杂查询

1实验五复杂查询1实验目的与要求(1)熟练掌握SQL语句的使用。(2)熟练使用SQL语句进行连接操作。2实验内容(1)在订单明细表中查询订单金额最高的订单。实验脚本：selecttop1orderNo订单编号,sum(quantity*price)订单总额fromOrderDetailgroupbyorderNoorderby订单总额desc实验结果图-1（1）查询结果(2)找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。实验脚本：selectproductNo商品编号,orderNo订单编号,quantity订货数量,quantity*price订单总额fromOrderDetailwhereproductNoin(selectproductNofromOrderDetailgroupbyproductNohavingcount(*)=3)orderby订货数量desc实验结果2图-2（2）查询结果(3)查找销售总额少于5000元的销售员编号、姓名和销售额。实验脚本：updateOrderMastersetorderSum=sum2fromOrderMastera,(selectorderNo,sum(quantity*price)sum2fromOrderDetailgroupbyorderNo)bwherea.orderNo=b.orderNoselectsalerNo销售员编号,employeeName销售员姓名,sum(orderSum)销售额fromOrderMaster,EmployeewheresalerNo=employeeNogroupbysalerNo,employeeNamehavingsum(orderSum)5000实验结果图-3（3）查询结果(4)找出目前业绩未超过5000元的员工，并按销售业绩的降序排序输出。实验脚本：SELECTemployeeNo员工编号,employeeName员工姓名,SumOrder业绩FROM(SELECTemployeeNo,employeeNameFROMEmployee)xleftjoIN(SELECTsalerNo,sum(sumOrder)SumOrderFROM(SELECTsalerNo,sumOrder=quantity*priceFROMOrderMasteraleftouterjoINOrderDetailbona.orderNo=b.orderNo)mGROUPBYsalerNo)yonx.employeeNo=y.salerNo3WHERESumOrder5000ORDERBYSumOrderDESC实验结果图-4（4）查询结果(5)查询订购的商品数量没有超过10个的客户编号和客户名称。实验脚本：selectc.customerNo客户编号,customerName客户名称fromCustomerc,(selectcustomerNofromOrderMastera,OrderDetailbwherea.orderNo=b.orderNogroupbycustomerNohavingsum(quantity)=10)dwherec.customerNo=d.customerNo实验结果图-5（5）查询结果(6)查找订货金额最大的客户名称和总货款。实验脚本：selecta.customerNo客户编号,b.C_s总货款fromCustomerasa,(selecttop1customerNo,sum(orderSum)C_sfromOrderMastergroupbycustomerNoorderbysum(orderSum)desc)asbwherea.customerNo=b.customerNo实验结果：图-6（6）查询结果(7)查找至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额。实验脚本：SELECTa.CustomerNo客户编号,CustomerName客户名称,b.ProductNo商品编号,ProductName商品名称,quantity数量,sum(quantity*price)金额4FROMCustomera,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,quantityORDERBYa.CustomerNo,金额DESC实验结果：5图-7（7）查询结果(8)找出目前销售业绩超过4000元的业务员编号及销售业绩，并按销售业绩从大到小排序。实验脚本：selectsalerNo销售员编号,sum(orderSum)销售额fromOrderMastergroupbysalerNohavingsum(orderSum)4000orderby销售额desc实验结果：图-8（8）查询结果(9)求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。实验脚本：selectcustomerNo客户编号,productNo商品号,sum(quantity)总数量,avg(price)平均价格fromOrderDetaila,OrderMasterbwherea.orderNo=b.orderNogroupbycustomerNo,productNoorderbycustomerNo,productNo6实验结果：图-9（9）查询结果(10)查询业绩最好的的业务员号、业务员名及其总销售金额。实验脚本：selectsalerNo业务员号,employeeName业务员名,b.O_s总销售金额fromEmployee,(selecttop1salerNo,sum(orderSum)O_sfromOrderMastergroupbysalerNoorderbyO_sdesc)bwhereemployeeNo=b.salerNo实验结果：图-10（10）查询结果(11)查询订购的商品至少包含了订单“200803010001”中所订购商品的订单。实验脚本：selectorderNo订单编号,customerNo客户号,salerNo业务员编号,orderDate订单日期,orderSum订单金额,invoiceNo发票号码fromOrderMasterawhereexists(select*fromOrderDetailbwhereorderNo='200803010001'andexists(select*fromOrderDetailwhereorderNo=a.orderNoandproductNo=b.productNo))实验结果：7图-11（11）查询结果(12)查询总订购金额超过“C20070002”客户的总订购金额的客户号、客户名及其住址。实验脚本：selectc.customerNo客户号,b.customerName客户名,address住址fromOrderMasterc,Customerbwherec.customerNo=b.customerNogroupbyc.customerNo,b.customerName,addresshavingsum(c.orderSum)(selectsum(orderSum)fromOrderMasterwherecustomerNo='C20070002'groupbycustomerNo)实验结果：图-12（12）查询结果(13)查询总销售金额最高的销售员编号、订单编号、订单日期和订单金额。实验脚本：selectb.salerNo业务员号,a.orderNo订单编号,a.orderDate订单日期,a.orderSum总销售金额fromEmployee,OrderMastera,(selecttop1salerNo,sum(orderSum)O_sfromOrderMastergroupbysalerNoorderbyO_sdesc)bwhereemployeeNo=b.salerNoandemployeeNo=a.salerNo实验结果：8图-13（13）查询结果(14)用存在量词查找没有订货记录的客户名称。实验脚本：selecta.customerName客户名称fromCustomerawherenotexists(select*fromOrderMasterbwherea.customerNo=b.customerNo)实验结果：图-14（14）查询结果(15)查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。实验脚本：selectcustomerNo客户编号,orderNo订单编号,orderSum订单金额fromOrderMasterwhereorderNoin(selectorderNofromOrderDetailwhereproductNoin(selectproductNofromProductwhereproductName='52倍速光驱'))andorderNoin(selectorderNofromOrderDetailwhereproductNoin(selectproductNofromProductwhereproductName='17寸显示器'))实验结果：未查找到与要求符合的信息。图-15（15）查询结果为空(16)求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。实验脚本：selectcustomerNo客户编号,productNo商品号,sum(quantity)总数量,avg(price)平均价格fromOrderDetaila,OrderMasterbwherea.orderNo=b.orderNo9groupbycustomerNo,productNoorderbycustomerNo,productNo实验结果：图-16（16）查询结果(17)实验问题：①存在量词与集合运算IN、连接运算和全称量词之间的关系如何？它们可以互相替换吗？给出你的理由。答：存在量词EXISTS可以用连接运算或集合运算IN来实现，而SQL中没有全称量词，只能用存在量词和取非运算来实现。②请写出例2.51的执行过程。1）首先取将OrderMaster表和OrderDetail表建立自然连接；2）执行子查询，查询至少销售了5种商品的销售员编号；3）将结果结合中的元组作为一个新关系输出。③存在量词一般用在相关子查询中，请分别给出存在量词用在相关子查询和非相关子查询的查询例子。相关子查询：SELECTstudentName,classNoFROMStudentxWHEREEXISTS(SELECT*FROMScorea,CoursebWHEREa.courseNo=b.courseNoANDa.studentNo=x.studentNoANDcourseName=’操作系统’）非相关子查询：SELECTstudentNo,classNoFROMStudentWHERENOTEXISTS10(SELECT*FROMStudentWHEREstudentName=‘王红’)