sql server实验报告

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