资源描述：
《数据库系统原理与设计实验教程实验2》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、实验二答案(1)查找有销售记录的客户编号、名称和订单总额。selecta.customerNo,a.customerName,b.orderNo,sum(quantity*price)fromCustomera,OrderMasterb,OrderDetailcwherea.customerNo=b.customerNoandb.orderNo=c.orderNogroupbya.customerNo,a.customerName,b.orderNo(2)在订单明细表中查询订单金额最高的订单。selecttop1orderNo,sum(quantity*price)orderSumfr
2、omOrderDetailgroupbyorderNoorderbyorderSumdesc或：selectorderNo,sum(quantity*price)orderSumfromOrderDetailgroupbyorderNohavingsum(quantity*price)=(selectmax(orderSum)from(selectorderNo,sum(quantity*price)orderSumfromOrderDetailgroupbyorderNo)b)(3)查询没有订购商品的客户编号和客户名称。SELECTa.customerNo,customerNameF
3、ROMCustomeraWHEREa.customerNoNOTIN(SELECTcustomerNoFROMOrderMaster)(3)找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。SELECTa.productNo,orderNo,quantity,(quantity*price)moneyFROMOrderDetaila,(SELECTproductNoFROMOrderDetailGROUPBYproductNoHAVINGcount(*)>=3)bWHEREa.productNo=b.productNoORDERBYa.produc
4、tNo,quantityDESC(4)使用子查询查找16MDRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用“男”、“女”表示。SELECTemployeeName,casesexwhen'F'then'女'when'M'then'男'endsex,orderDate,quantity,quantity*price金额FROMEmployeea,OrderMasterb,OrderDetailcWHEREa.employeeNo=b.salerNoANDb.orderNo=c.orderNoANDc.ProductNoIN(SELECTf.Pro
5、ductNoFROMOrderMasterd,OrderDetaile,ProductfWHEREd.orderNo=e.orderNoANDProductName='32MDRAM')(5)查询OrderMaster表中订单金额最高的订单号及订单金额。selectorderNo,orderSumfromOrderMasterwhereorderSum=(selectmax(orderSum)fromOrderMaster)(6)计算出一共销售了几种商品。selectcount(distinctproductNo)fromOrderDetail(7)显示OrderDetail表中每种商
6、品的订购金额总和，并且依据销售金额由大到小排序输出。selectproductNo,sum(quantity*price)summoneyfromOrderDetailgroupbyproductNoorderbysummoneydesc(8)查找销售总额大于1000元的销售员编号、姓名和销售额。selecta.employeeNo,a.employeeName,sum(quantity*price)sunmoneyfromEmployeea,OrderDetailb,OrderMastercwherea.employeeNo=c.salerNoandb.orderNo=c.order
7、Nogroupbya.employeeNo,a.employeeNamehavingsum(quantity*price)>1000(9)找出目前业绩未超过5000元的员工，并按销售业绩的降序排序输出。selectemployeeNo,employeeName,orderSumfromEmployeea,(selectsalerNo,sum(orderSum)orderSumfromOrderMastergroupbysalerNohaving
