复杂查询参考案.docVIP

数据库原理实验教材实验答案 实验三 在订单明细表中查询订单金额最高的订单。 select orderNo,sum(quantity*price) orderSum from OrderDetail group by orderNo having sum(quantity*price)= (select max(orderSum) from (select orderNo,sum(quantity*price) orderSum from OrderDetail group by orderNo) b) 找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。 SELECT a.productNo,orderNo,quantity,(quantity*price) money FROM OrderDetail a, (SELECT productNo FROM OrderDetail GROUP BY productNo HAVING count(*)=3) b WHERE a.productNo =b.productNo ORDER BY a.productNo,quantity DESC 查找销售总额少于5000元的销售员编号、姓名和销售额。 select a.employeeNo,a.employeeName,sum(quantity*price) sunmoney from Employee a,OrderDetail b,OrderMaster c where a.employeeNo=c.salerNo and b.orderNo=c.orderNo group by a.employeeNo,a.employeeName having sum(quantity*price)5000 找出目前业绩未超过5000元的员工，并按销售业绩的降序排序输出。 select employeeNo,employeeName,orderSum from Employee a,(select salerNo,sum(orderSum) orderSum from OrderMaster group by salerNo having sum(orderSum)5000) b where a.employeeNo=b.salerNo order by orderSum desc  查询订购的商品数量没有超过10个的客户编号和客户名称。 SELECT a.CustomerNo,CustomerName FROM Customer a WHERE a.CustomerNo IN ( SELECT CustomerNo FROM OrderMaster b,OrderDetail c WHERE b.orderNo=c.orderNo GROUP BY CustomerNo HAVING sum(quantity)10) 查找订货金额最大的客户名称和总货款。 SELECT customerName ,sum(orderSum) FROM OrderMaster a,Customer b WHERE a.customerNo=b.customerNo GROUP BY a.customerNo,customerName HAVING sum(orderSum)=(SELECT max(orderSum) FROM(SELECT customerNo,sum(orderSum) orderSum FROM OrderMaster GROUP BY customerNo)c) 查找至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额。 SELECT a.CustomerNo,CustomerName,b.ProductNo, ProductName,quantit