资源描述：
《复杂查询实验资料报告材料.doc》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、复杂查询实验报告江培健10140421510计教（2）班(1)查找有销售记录的客户编号、名称和订单总额。SELECTa.CustomerNo,CustomerName,sum(quantity*price)orderSumFROMOrderMastera,OrderDetailb,CustomercWHEREb.orderNo=a.orderNoANDc.CustomerNo=a.CustomerNoGROUPBYa.CustomerNo,CustomerNameORDERBYa.CustomerNo,orderSumDESC(2)
2、在订单明细表中查询订单金额最高的订单(3)SELECTtop1orderNo,sum(quantity*price)订单金额FROMOrderDetailGROUPBYorderNoORDERBY订单金额DESC3查询没有订购商品的客户编号和客户名称SELECTCustomerNo,CustomerNameFROMCustomerWHERECustomerNoNOTIN(SELECTCustomerNoFROMOrderMaster)(1)(4)找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。
3、SELECTproductNo,orderNo,quantity,quantity*price订货金额FROMOrderDetailWHEREproductNOIN(SELECTproductNoFROMOrderDetailGROUPBYproductNoHAVINGcount(*)>=3)ORDERBYproductNodesc(1)(5)使用子查询查找16MDRAM的销售情况，要求显示相应的销售员的、性别，销售日期、销售数量和金额(6)SELECTemployeeName,casesex(7)when'F'then'女'(8)
4、when'M'then'男'endsex,(9)orderDate,quantity,quantity*price金额(10)FROMEmployeea,OrderMasterb,OrderDetailc(11)WHEREa.employeeNo=b.salerNoANDb.orderNo=c.orderNo(12)ANDc.ProductNoIN((13)SELECTf.ProductNo(14)FROMOrderMasterd,OrderDetaile,Productf(15)WHEREd.orderNo=e.orderNoAN
5、DProductName='32MDRAM')(1)(2)(6)查询OrderMaster表中订单金额最高的订单号及订单金额SELECTorderNo,orderSumFROMOrderMasterWHEREorderSum=(SELECTmax(orderSum)FROMOrderMaster)(7)计算出一共销售了几种商品SELECTCOUNT(*)种类FROM(SELECTDISTINCTProductNoFROMOrderDetail)a(8)显示OrderDetail表中每种商品的订购金额总和，并且依据销售金额由大到小排序
6、输出。SELECTproductNo,sum(quantity*price)订购金额FROMOrderDetailGROUPBYproductNoORDERBY订购金额DESC(9)查找销售总额大于1000元的销售员编号、和销售额SELECTsalerNo,employeeName,sum(orderSum)TotalFROMOrderMastera,EmployeebWHEREemployeeNo=salerNoANDorderSum>1000GROUPBYsalerNo,employeeName(10)找出目前业绩未超过5000
7、元的员工，并按销售业绩的降序排序输出SELECTemployeeNo,employeeName,SumOrderFROM(SELECTemployeeNo,employeeNameFROMEmployee)xleftjoIN(SELECTsalerNo,sum(sumOrder)SumOrderFROM(SELECTsalerNo,sumOrder=quantity*priceFROMOrderMasteraleftouterjoINOrderDetailbona.orderNo=b.orderNo)mGROUPBYsalerNo)
8、yonx.employeeNo=y.salerNoWHERESumOrder<5000ORDERBYSumOrderDESC(11)在Employee表中查询薪水超过员工平均薪水的员工信息SELECT*FROMEmployeeWHER
