复杂查询实验报告-(最新版).pdf

复杂查询实验报告 江培健 101404215 10 计教（2）班 (1) 查找有销售记录的客户编号、名称和订单总额 SELECT a.CustomerNo,CustomerName, sum(quantity*price) orderSum FROM OrderMaster a,OrderDetail b,Customer c WHERE b.orderNo=a.orderNo AND c.CustomerNo=a.CustomerNo GROUP BY a.CustomerNo,CustomerName ORDER BY a.CustomerNo,orderSum DESC (2) 在订单明细表中查询订单金额最高的订单 (3)SELECT top 1 orderNo,sum(quantity*price)订单金额 FROM OrderDetail GROUP BY orderNo ORDER BY 订单金额 DESC 3 查询没有订购商品的客户编号和客户名称 SELECT CustomerNo,CustomerName FROM Customer WHERE CustomerNo NOT IN (SELECT CustomerNo FROM OrderMaster) (4)(4) 找出至少被订购 3 次的商品编号、订单编号、订货数量和订货金额，并按订货数 量的降序排序输出。

SELECT productNo ,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 sex, (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)(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 *price ) 订购金额 FROM OrderDetail GROUP BY productNo ORDER BY 订购金额 DESC (9) 查找销售总额大于 1000 元的销售员编号、姓名和销售额 SELECT salerNo,employeeName,sum(orderSum)Total FROM OrderMaster a,Employee b WHERE employeeNo=salerNo AND orderSum1000 GROUP BY salerNo,employeeName (10) 找出目前业绩未超过 5000 元的员工，并按销售业绩的降序排序输出 SELECT employeeNo,employeeName,SumOrder FROM (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(SELECT avg(salary) avgSalary FROM Employee WHERE department=业 务 科 or department=财 务 科 or department=办公室) (12) 计算每一种商品的销售数量、平均销售单价和总销售金额 SELECT ProductNo,sum(quantity)销售数量,avg(price)平均销售单 价,sum(quantity*price)总销售金额 FROM OrderDetail GROUP BY ProductNo (13) 查找至少有 3 次销售的业务员名单和销售日期 SELECT employeeName,orderDate FROM OrderMaster 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,CustomerName FROM OrderMaster x,Customer y WHERE x.CustomerNo=y.CustomerNo AND orderNo IN (SELECT orderNo FROM OrderDetail GROUP BY orderNo havINg sum(quantity)40000 ORDER 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.orde rNo=b.orderNo WHERE a.orderNo=b.orderNo)y WHERE x.CustomerNo=y.CustomerNo GROUP BY x.CustomerNo ORDER BY 订购金额 DESC 实验感悟： 通过数据库实验复杂查询以后对于数据的查询有了更 深层次的认识，也深感自己的英语水平不足。