复杂查询参考答案_第1页
复杂查询参考答案_第2页
复杂查询参考答案_第3页
复杂查询参考答案_第4页
复杂查询参考答案_第5页
全文预览已结束

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

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. 最后将结果输出 存在量词一般用在相关子查询中,请分别给出存在量词用在相关子查询和非相关子查询的查询

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论