sql server实验报告

select *

from employee a, OrderMaster b

where a.employeeNo = b.salerNo and orderSum<5000 order by orderSum desc

5.查询订购的数量没有超过10个的客户编号和客户名称

select customerNo, customerName from customer where customerNo in( select customerNo from orderMaster

where orderNo in( select orderNo from orderDetail where quantity<10))

6.查询订货金额最大的客户名称和总货款数（连接查询）

select a.customerNo 客户编号, customerName 客户名称, sumOrder 总货款 from Customer a, (

select customerNo, sumOrder from(

select customerNo, sum(orderSum) sumOrder from OrderMaster group by customerNo) b where b.sumOrder = ( select max(sumOrder) from(

select customerNo, sum(orderSum) sumOrder from OrderMaster

group by customerNo )c) ) d where a.customerNo = d.customerNo

6.查询订货金额最大的客户名称和总货款数（使用子查询）

select a.customerNo 客户编号, customerName 客户名称, sum(orderSum) 总货款 from Customer a, OrderMaster b where a.customerNo = b.customerNo

group by a.customerNo, customerName having sum(orderSum) = ( select max(sumOrder) from(

select customerNo, sum(orderSum) sumOrder from OrderMaster group by customerNo) c )

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

select a.customerNo 客户编号, customerName 客户名称, b.productNo 产品编号, productName 产品名称, quantity 数量, quantity*price 金额

from customer a, product b, OrderDetail c, OrderMaster d where a.customerNo = d.customerNo and d.orderNo =