复杂查询参考答案.doc

数据库原理实验教材实验答案实验三(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次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。 SELECT a.productNo,orderNo,quantity,(quantity*price) moneyFROM OrderDetail a, (SELECT productNo FROM OrderDetail GROUP BY productNo HAVING count(*)=3) bWHERE a.productNo =b.productNo ORDER BY a.productNo,quantity DESC(3) 查找销售总额少于5000元的销售员编号、姓名和销售额。 select a.employeeNo,a.employeeName,sum(quantity*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(orderSum) 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.orderNo GROUP BY CustomerNo HAVING sum(quantity)=3)GROUP BY a.CustomerNo,CustomerName,b.ProductNo, ProductName,quantity(8) 找出目前销售业绩超过4000元的业务员编号及销售业绩，并按销售业绩从大到小排序。 SELECT employeeNo , sumOrder FROM Employee a,(SELECT salerNo,sum(orderSum) sumOrder FROM OrderMaster GROUP BY salerNo) bWHERE a.employeeNo=b.salerNo AND sumOrder4000ORDER BY sumOrder DESC(9) 求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。 SELECT 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(10) 查询业绩最好的的业务员号、业务员名及其总销售金额。 SELECT salerNo,employeeName,sum(orderSum)FROM Employee a,OrderMaster bWHERE a.employeeNo=b.salerNoGROUP BY salerNo,employeeNamehaving 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 * from OrderDetail z where y.productNo=z.productNo 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(orderSum) 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) 用存在量词查找没有订货记录的客户名称。 SELECT 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 where a.orderNo=b.orderNo and b.productNo=c.productNo and productName=52倍速光驱) and customerNo in (select customerNo from OrderMaster a,OrderDetail b,Product c where a.orderNo=b.orderNo and b.productNo=c.productNo and productName=17寸显示器)(16) 求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。 SELECT 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中没有全称量词，只能用存在量词和取非运算来实现； 请写出例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=c.orderNo AND c.productNo=d.productNo 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. 最后将结果输出 存在量词一般用在相关子查询中，请分别给出存在量词用在相关子查询和非相关子查询的查询例子。答：相关子查询：SELECT studentName,classNo FROM Student x WHERE EXISTS (SELECT * FROM Score a,Course b WHERE a.courseNo=b.courseNo AND a.studentNo=x.studentNo AND courseName=操作系统）非相关子查询：SELECT studentNo,classNoFROM StudentWHERE NOT EXISTS (SELECT * FROM Student WHERE studentName=王红)