复杂查询实验报告

1、. 复杂查询实验报告江培健 101404215 10计教（2）班(1) 查找有销售记录的客户编号、名称和订单总额。SELECT a.CustomerNo,CustomerName, sum(quantity*price) orderSumFROM OrderMaster a,OrderDetail b,Customer cWHERE b.orderNo=a.orderNo AND c.CustomerNo=a.CustomerNo GROUP BY a.CustomerNo,CustomerNameORDER BY a.CustomerNo,orderSum DESC(2) 在订单明细表中查询

2、订单金额最高的订单(3) SELECT top 1 orderNo,sum(quantity*price)订单金额FROM OrderDetailGROUP BY orderNoORDER BY 订单金额 DESC3查询没有订购商品的客户编号和客户名称SELECT CustomerNo,CustomerNameFROM Customer WHERE CustomerNo NOT IN (SELECT CustomerNo FROM OrderMaster)(4) (4) 找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。SELECT productNo ,

3、orderNo ,quantity ,quantity *price 订货金额FROM OrderDetail WHERE productNO IN(SELECT productNo FROM OrderDetail GROUP BY productNo HAVING count(*)=3)ORDER BY productNo desc (5) (5) 使用子查询查找16M DRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额(6)SELECT employeeName,case sex(7) when F then 女(8) when M then 男 end se

4、x,(9) orderDate,quantity,quantity*price 金额(10)FROM Employee a,OrderMaster b,OrderDetail c(11)WHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo(12) AND c.ProductNo IN (13) SELECT f.ProductNo(14) FROM OrderMaster d,OrderDetail e,Product f(15) WHERE d.orderNo=e.orderNo AND ProductName=32M DRAM)(6)(7

5、) (6) 查询OrderMaster表中订单金额最高的订单号及订单金额SELECT orderNo ,orderSum FROM OrderMaster WHERE orderSum =(SELECT max(orderSum ) FROM OrderMaster )(7) 计算出一共销售了几种商品SELECT COUNT(*)种类FROM (SELECT DISTINCT ProductNo FROM OrderDetail)a(8) 显示OrderDetail表中每种商品的订购金额总和，并且依据销售金额由大到小排序输出。SELECT productNo ,sum(quantity *pr

6、ice ) 订购金额FROM OrderDetail GROUP BY productNo ORDER BY 订购金额 DESC(9) 查找销售总额大于1000元的销售员编号、姓名和销售额SELECT salerNo,employeeName,sum(orderSum)TotalFROM OrderMaster a,Employee bWHERE employeeNo=salerNo AND orderSum1000GROUP BY salerNo,employeeName(10) 找出目前业绩未超过5000元的员工，并按销售业绩的降序排序输出SELECT employeeNo,employe

7、eName,SumOrderFROM (SELECT employeeNo,employeeName FROM Employee)x left joIN (SELECT salerNo,sum(sumOrder) SumOrder FROM (SELECT salerNo,sumOrder=quantity*price FROM OrderMaster a left outer joIN OrderDetail b on a.orderNo=b.orderNo)m GROUP BY salerNo)y on x.employeeNo=y.salerNo WHERE SumOrder(SELEC

8、T avg(salary) avgSalary FROM Employee WHERE department=业务科 or department=财务科 or department=办公室)(12) 计算每一种商品的销售数量、平均销售单价和总销售金额SELECT ProductNo,sum(quantity)销售数量,avg(price)平均销售单价,sum(quantity*price)总销售金额FROM OrderDetailGROUP BY ProductNo(13) 查找至少有3次销售的业务员名单和销售日期SELECT employeeName,orderDateFROM OrderM

9、aster a left joIN Employee b on b.employeeNo=a.salerNo WHERE salerNo=(SELECT salerNo FROM OrderMaster GROUP BY salerNo havINg COUNT(*)2)(14) 查询订单中所订购的商品数量没有超过10个的客户编号和客户名称SELECT x.CustomerNo,CustomerNameFROM OrderMaster x,Customer yWHERE x.CustomerNo=y.CustomerNo AND orderNo IN (SELECT orderNo FROM OrderDetail GROUP BY orderNo havINg sum(quantity)40000ORDER BY sumOrder DESC一、 (17) 求出每位客户的总订购金额，显示出客户号及总订购金额，并按总订购金额降序排列。SELECT x.CustomerNo,sum(quantity*price)订购金额FROM Customer x,(SELECT a.orderNo,quantity,price,CustomerNo FROM OrderDetail a left joIN OrderMaster b on a.orderNo=b