实验五-复杂查询-实验报告.docx

实验五 复杂查询1 实验目的与要求(1) 熟练掌握SQL语句的使用。(2) 熟练使用SQL语句进行连接操作。2 实验内容(1) 在订单明细表中查询订单金额最高的订单。select a.*from orderdetail a,ordermaster bwhere a.orderno=b.orderno and ordersum=(select max(ordersum) from ordermaster)(2) 找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。select productno 商品编号,orderno 订单编号,quantity 订货数量,quantity*price 订货金额from orderdetailwhere productno in(select productno from orderdetail group by productno having count(*)=3)order by quantity desc(3) 查找销售总额少于5000元的销售员编号、姓名和销售额。select a.salerno 销售员编号,b.employeename 姓名,sum(ordersum) 销售额from ordermaster a,employee bwhere a.salerno=b.employeenogroup by a.salerno,b.employeenamehaving sum(ordersum)5000(4) 找出目前业绩未超过5000元的员工，并按销售业绩的降序排序输出。select salerno 销售员编号,sum(ordersum) 销售业绩from ordermastergroup by salernohaving sum(ordersum)=5000order by sum(ordersum) desc(5) 查询订购的商品数量没有超过10个的客户编号和客户名称。select a.customerno 客户编号,customername 客户名称from customer a,ordermaster b,orderdetail cwhere a.customerno=b.customerno and b.orderno=c.ordernogroup by a.customerno,customernamehaving sum(quantity)=3)order by a.customerno(8) 找出目前销售业绩超过4000元的业务员编号及销售业绩，并按销售业绩从大到小排序。select salerno 销售员编号,sum(ordersum) 销售业绩from ordermastergroup by salernohaving sum(ordersum)4000order by sum(ordersum) desc(9) 求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。select customerno 客户编号,productno 商品编号,sum(quantity) 总数量,avg(price) 平均单价from ordermaster a,orderdetail bwhere a.orderno=b.ordernogroup by customerno,productnoorder by customerno,productno(10) 查询业绩最好的的业务员号、业务员名及其总销售金额。select salerno 业务员号, employeename 业务员名,sum(ordersum) 销售金额from ordermaster a,employee bwhere a.salerno=b.employeenogroup by salerno,employeenamehaving sum(ordersum)=(select max(salesum) from (select salerno,sum(ordersum)as salesum from ordermaster group by salerno)c)(11) 查询订购的商品至少包含了订单“200803010001”中所订购商品的订单。SELECT a.*FROM OrderMaster a,OrderDetail bWHERE a.orderNo =b.orderNo and productNo IN( SELECT productNo FROM OrderDetailWHERE orderNo=200803010001)(12) 查询总订购金额超过“C20070002”客户的总订购金额的客户号、客户名及其住址。select a.customerNo,a.customerName,a.address from customer a,(select customerNo, sum(orderSum)sumorder from orderMaster group by customerNo) bwhere a.customerNo=b.customerNoand b.sumorder(select sum(orderSum)sumorderfrom orderMasterwhere customerNo=C20070002 group by customerNo)(13) 查询总销售金额最高的销售员编号、订单编号、订单日期和订单金额。select salerno,b.orderno,orderdate,ordersumfrom employee a,ordermaster bwhere a.employeeno=b.salernoand ordersum=(select max(ordersum) from ordermaster)(14) 用存在量词查找没有订货记录的客户名称。select customernamefrom customer bwhere not exists(select * from ordermaster a where a.customerno=b.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 ductno=ductno and productname=52倍速光驱) and customerno in (select customerno from ordermaster a,orderdetail b,product c where ductno=ductno and productname=17寸显示器)(16) 求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。select customerno,productno,sum(quantity) 数量,(sum(quantity*price)/sum(quantity) 平均单价from ordermaster a,orderdetail bwhere a.orderno=b.ordernogroup by customerno,productnoorder by customerno,productno(17) 实验问题： 存在量词与集合运算IN、连接运算和全称量词之间的关系如何？它们可以互相替换吗？给出你的理由。答：存在量词EXISTS可以用连接运算或集合运算IN来实现，而SQL中没有全称量词，只能用存在量词和取非运算来实现； 请写出例2.51的执行过程。SELECT salerNo,employeeName,productName,quantity,priceFROM Employee a,OrderMaster b, OrderDetail c,Product dWHERE a.employeeNo=salerNo AND b.orderNo=c.orderNo AND ductNo=ductNoAND EXISTS(SELECT salerNoFROM OrderMaster e,OrderDetail fWHERE e.orderNo=f.orderNo AND a.employeeNo=salerNoGROUP BY salerNoHAVING count(distinct productNo)=5)ORDER BY salerNo1.首先将表Employeea,OrderMasterb,OrderDetailc,Productd进行连接2.对连接后的记录，取出员工编号，判断是否至少销售了5种商品3.如果是，将salerNo,employeeName,productName,quantity,price这五个值作为输出结果4如果不是，舍弃该连接记录5.取下一条连接记录，转2，直到所有的连接记录处理完毕6.最将结果输出 存在量词一般用在相关子查询中，请分别给出存在量词用在相关子查询和非相关子查询的查询例子。相关子查询：SELECT studentName,classNoFROM StudentxWHERE EXISTS(