数据库实验（六）复杂查询

1、在订单明细表中查询订单金额最高的订单。

命令：

select orderNO,sum(quantity*price) as ordersum

from OrderDetail a group by orderNo

having sum(quantity*price)=

(select max(ordersum) from (select orderNO,sum(quantity*price) as ordersum

from OrderDetail group by orderNo)a)

结果截图：

2、 找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。

select orderNo,a.productNo,quantity,quantity*price ordersum

from OrderDetail a,(select productNo from OrderDetail group by productNo having COUNT(*)>=3) b

where a.productNo=b.productNo

order by quantity desc

3、查询订单中所订购的商品数量没有超过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)

4、查找至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额。

SELECT a.CustomerNo,CustomerName,b.ProductNo,

ProductName,quantity,sum(quantity*price)

FROM Customer a,Product b,OrderMaster c,OrderDetail d

WHERE a.CustomerNo=c.CustomerNo and c.orderNo=d.orderNo

and b.ProductNo=d.ProductNo and

EXISTS (

SELECT CustomerNo

FROM OrderMaster e,OrderDetail f

WHERE e.orderNo=f.orderNo and a.customerNo=e.customerNo

GROUP BY CustomerNo

HAVING count(distinct ProductNo)>=3)

GROUP BY a.CustomerNo,CustomerName,b.ProductNo,

ProductName,quantity

5.查询销售金额最高的销售员编号、订单编号、订单日期和订单金额。

SELECT salerNo,b.orderNo,orderDate, orderSum

FROM Employee a,OrderMaster b

WHERE a.employeeNo=b.salerNo

and orderSum =(select max(orderSum)

from OrderMaster)

6、求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。

select customerNo,productNo,sum(quantity) sumquantity,sum(price*quantity)/sum(quantity) avgprice

from OrderDetail a,OrderMaster b

where a.orderNo=b.orderNo

group by customerNO,productNo

order by customerNo,productNo