c.orderNo and c.productNo = b.productNo and exists( select customerNo
from OrderMaster e,OrderDetail f
where e.customerNo = a.customerNo and e.orderNo = f.orderNo
group by customerNo having count(productNo)>=3)
8.找出目前销售业绩超过4000元的业务员编号和销售业绩，并按销售业绩从大到小排序
select a.employeeNo, sum(orderSum) from employee a, OrderMaster b where a.employeeNo = b.salerNo group by a.employeeNo having sum(orderSum)>4000
order by sum(orderSum) desc
9.求每位客户订购的每种商品的总数及平均单价，并按客户号、商品号从小到大排序
select distinct a.customerNo 客户编号, ProductNo 商品编号, sum(quantity) 商品总数, avg(price) 平均单价 from customer a, OrderDetail b, OrderMaster c where a.customerNo = c.customerNo and b.orderNo =
c.orderNo
group by a.customerNo,ProductNo order by a.customerNo, productNo
10.查询业绩最好的业务员编号，业务员姓名及其总销售金额
select employeeNo 业务员编号, employeeName 业务员姓名, max(orderSum)销售金额 from Employee a, OrderMaster b where a.employeeNo = b.salerNo group by employeeNo, employeeName
having max(orderSum) in( select max(sumOrder) from(
select orderNo, max(orderSum) sumOrder from OrderMaster group by orderNo) c)
11．查询订购的商品至少包含了订单２００８０３０１０００１中所订购的商品的订单
select *
from OrderMaster a where not exists( select *
from OrderDetail b
