资源描述:
《数据库系统原理设计实验教程实验2详解.doc》由会员上传分享,免费在线阅读,更多相关内容在行业资料-天天文库。
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)在订单明细表中查询订单金额最高的订单。selecttop1o
2、rderNo,sum(quantity*price)orderSumfromOrderDetailgroupbyorderNoorderbyorderSumdesc或:selectorderNo,sum(quantity*price)orderSumfromOrderDetailgroupbyorderNohavingsum(quantity*price)=(selectmax(orderSum)from(selectorderNo,sum(quantity*price)orderSumfromOrderDetai
3、lgroupbyorderNo)b)(3)查询没有订购商品的客户编号和客户名称。SELECTa.customerNo,customerNameFROMCustomeraWHEREa.customerNoNOTIN(SELECTcustomerNoFROMOrderMaster)(3)找出至少被订购3次的商品编号、订单编号、订货数量和订货金额,并按订货数量的降序排序输出。SELECTa.productNo,orderNo,quantity,(quantity*price)moneyFROMOrderDetaila,(
4、SELECTproductNoFROMOrderDetailGROUPBYproductNoHAVINGcount(*)>=3)bWHEREa.productNo=b.productNoORDERBYa.productNo,quantityDESC(4)使用子查询查找16MDRAM的销售情况,要求显示相应的销售员的姓名、性别,销售日期、销售数量和金额,其中性别用“男”、“女”表示。SELECTemployeeName,casesexwhen'F'then'女'when'M'then'男'endsex,orderDa
5、te,quantity,quantity*price金额FROMEmployeea,OrderMasterb,OrderDetailcWHEREa.employeeNo=b.salerNoANDb.orderNo=c.orderNoANDc.ProductNoIN(SELECTf.ProductNoFROMOrderMasterd,OrderDetaile,ProductfWHEREd.orderNo=e.orderNoANDProductName='32MDRAM')(5)查询OrderMaster表中订单金额最
6、高的订单号及订单金额。selectorderNo,orderSumfromOrderMasterwhereorderSum=(selectmax(orderSum)fromOrderMaster)(6)计算出一共销售了几种商品。selectcount(distinctproductNo)fromOrderDetail(7)显示OrderDetail表中每种商品的订购金额总和,并且依据销售金额由大到小排序输出。selectproductNo,sum(quantity*price)summoneyfromOrderDe
7、tailgroupbyproductNoorderbysummoneydesc(8)查找销售总额大于1000元的销售员编号、姓名和销售额。selecta.employeeNo,a.employeeName,sum(quantity*price)sunmoneyfromEmployeea,OrderDetailb,OrderMastercwherea.employeeNo=c.salerNoandb.orderNo=c.orderNogroupbya.employeeNo,a.employeeNamehavingsu
8、m(quantity*price)>1000(9)找出目前业绩未超过5000元的员工,并按销售业绩的降序排序输出。selectemployeeNo,employeeName,orderSumfromEmployeea,(selectsalerNo,sum(orderSum)orderSumfromOrderMastergroupbysalerNohaving