复杂查询参考答案

1、数据库原理实验教材实验答案实验三(1) 在订单明细表中查询订单金额最高的订单。 select orderNo,sum(quantity*price) orderSumfrom OrderDetailgroup by orderNohaving sum(quantity*price)= (select max(orderSum) from (select orderNo,sum(quantity*price) orderSum from OrderDetail group by orderNo) b)(2) 找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。

2、 SELECT ductNo,orderNo,quantity,(quantity*price) moneyFROM OrderDetail a, (SELECT productNo FROM OrderDetail GROUP BY productNo HAVING count(*)>=3) bWHERE ductNo =ductNo ORDER BY ductNo,quantity DESC(3) 查找销售总额少于5000元的销售员编号、姓名和销售额。 select a.employeeNo,a.employeeName,sum(quantit

3、y*price) sunmoneyfrom Employee a,OrderDetail b,OrderMaster cwhere a.employeeNo=c.salerNo and b.orderNo=c.orderNogroup by a.employeeNo,a.employeeNamehaving sum(quantity*price)<5000(4) 找出目前业绩未超过5000元的员工，并按销售业绩的降序排序输出。 select employeeNo,employeeName,orderSumfrom Employee a,(select salerNo,sum(orderS

4、um) orderSum from OrderMaster group by salerNo having sum(orderSum)<5000) bwhere a.employeeNo=b.salerNoorder by orderSum desc(5) 查询订购的商品数量没有超过10个的客户编号和客户名称。 SELECT a.CustomerNo,CustomerNameFROM Customer aWHERE a.CustomerNo IN ( SELECT CustomerNo FROM OrderMaster b,OrderDetail c WHERE b.orderNo=c.

5、orderNo GROUP BY CustomerNo HAVING sum(quantity)<10)(6) 查找订货金额最大的客户名称和总货款。 SELECT customerName ,sum(orderSum) FROM OrderMaster a,Customer bWHERE a.customerNo=b.customerNo GROUP BY a.customerNo,customerNameHAVING sum(orderSum)=(SELECT max(orderSum) FROM(SELECT customerNo,sum(orderSum) orderSum FRO

6、M OrderMaster GROUP BY customerNo)c)(7) 查找至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额。 SELECT a.CustomerNo,CustomerName,b.ProductNo, ProductName,quantity,sum(quantity*price) sumFROM Customer a,Product b,OrderMaster c,OrderDetail dWHERE a.CustomerNo=c.CustomerNo and c.orderNo=d.orderNo and b.ProductNo=d.Prod

7、uctNo and EXISTS ( SELECT CustomerNo FROM OrderMaster e,OrderDetail f WHERE e.orderNo=f.orderNo and a.customerNo=e.customerNo GROUP BY CustomerNo HAVING count(distinct ProductNo)>=3)GROUP BY a.CustomerNo,CustomerName,b.ProductNo, ProductName,quantity(8) 找出目前销售业绩超过4000元的业务员编号及销售业绩，并按销售业绩从大到小排序。 SE

8、LECT employeeNo , sumOrder FROM Employee a,(SELECT salerNo,sum(orderSum) sumOrder FROM OrderMaster GROUP BY salerNo) bWHERE a.employeeNo=b.salerNo AND sumOrder>4000ORDER BY sumOrder DESC(9) 求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。 SELECT customerNo,productNo,sum(quantity) quantitys, (sum(quantity*p

9、rice)/sum(quantity) avgpriceFROM OrderMaster a,OrderDetail bWHERE a.orderNo=b.orderNoGROUP BY customerNo,productNoORDER BY customerNo,productNo(10) 查询业绩最好的的业务员号、业务员名及其总销售金额。 SELECT salerNo,employeeName,sum(orderSum)FROM Employee a,OrderMaster bWHERE a.employeeNo=b.salerNoGROUP BY salerNo,employeeNam

10、ehaving sum(orderSum) = (select max(orderSum) from (select sum(orderSum) orderSum from OrderMaster group by salerNo) x)(11) 查询订购的商品至少包含了订单“200803010001”中所订购商品的订单。 SELECT *FROM OrderMaster aWHERE not exists (select * from OrderDetail y where orderNo='200803010001' and not exists (select * fro

11、m OrderDetail z where ductNo=ductNo and a.orderNo=z.orderNo)(12) 查询总订购金额超过“C20070002”客户的总订购金额的客户号、客户名及其住址。 SELECT a.customerNo,customerName,addressFROM OrderMaster a,Customer bWHERE a.customerNo=b.customerNoGROUP BY a.customerNo,customerName,addressHAVING sum(orderSum)>=(SELECT sum(orde

12、rSum) FROM OrderMaster WHERE customerNo='C20070002' GROUP BY customerNo)(13) 查询总销售金额最高的销售员编号、订单编号、订单日期和订单金额。 SELECT salerNo,b.orderNo,orderDate, orderSumFROM Employee a,OrderMaster bWHERE a.employeeNo=b.salerNo and orderSum =(select max(orderSum) from OrderMaster)(14) 用存在量词查找没有订货记录的客户名称。 SEL

13、ECT customerNameFROM Customer cWHERE NOT EXISTS (SELECT * FROM OrderMaster a WHERE a.customerNo=c.customerNo )(15) 查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。 Select customerNo,orderNo,orderSumfrom OrderMaster where customerNo in (select customerNo from OrderMaster a,OrderDetail b,Product c wher

14、e a.orderNo=b.orderNo and ductNo=ductNo and productName='52倍速光驱') and customerNo in (select customerNo from OrderMaster a,OrderDetail b,Product c where a.orderNo=b.orderNo and ductNo=ductNo and productName='17寸显示器')(16) 求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。 SELEC

15、T customerNo,productNo,sum(quantity) quantitys, (sum(quantity*price)/sum(quantity) avgpriceFROM OrderMaster a,OrderDetail bWHERE a.orderNo=b.orderNoGROUP BY customerNo,productNoORDER BY customerNo,productNo(17) 实验问题： 存在量词与集合运算IN、连接运算和全称量词之间的关系如何？它们可以互相替换吗？给出你的理由。答：存在量词EXISTS可以用连接运算或集合运算IN来实现，而SQL中没有

16、全称量词，只能用存在量词和取非运算来实现； 请写出例2.51的执行过程。例2.51 查询至少销售了5种商品的销售员编号、姓名、商品名称、数量及相应的单价，并按销售员编号排序输出。分析： 构造一个子查询，针对外查询中的每个销售员，判断其是否销售了5种以上的商品，使用相关子查询。 SQL语句为：SELECT salerNo, employeeName, productName, quantity, priceFROM Employee a, OrderMaster b, OrderDetail c, Product dWHERE a.employeeNo=salerNo AND b.orderNo

17、=c.orderNo AND ductNo=ductNo AND EXISTS( SELECT salerNo FROM OrderMaster e, OrderDetail f WHERE e.orderNo=f.orderNo AND a.employeeNo=salerNo GROUP BY salerNo HAVING count(distinct productNo )>=5 )ORDER BY salerNo答：1. 首先将表Employee a, OrderMaster b, OrderDetail c, Product d进行连接 2. 对连接后的记录，取出员工编号，判断是否至少销售了5种商品 3. 如果是，将salerNo, employeeName, productName, quantity, price这五个值作为输出结果 4如果不是，舍弃该连接记录 5. 取下一条连接记录，转2，直到所有的连接记录处理完毕 6. 最后将结果输出 存在量词一般用在相关子查询中，请分别给出存在量词用在相关子查询和非相关子查询的查询