实验五复杂查询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 productnofrom orderdetailgroup by productnohaving 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)<=10(6)查找订货金额最大的客户名称和总货款。

select customerno 客户名称,sum(ordersum)总货款from ordermastergroup by customernohaving sum(ordersum)=(select max(sumorder)from(select customerno,sum(ordersum)as sumorderfrom ordermastergroup by customerno)b)(7)查找至少订购了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额。

select a.customerno 客户编号,customername 客户名称,b.productno 商品编号,productname 商品名称,quantity 数量,quantity*price 金额from customer a,product b,ordermaster c,orderdetail dwhere a.customerno=c.customerno and b.productno=d.productno andc.orderno=d.ordernoand d.orderno in(select ordernofrom orderdetailgroup by ordernohaving count(productno)>=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 salesumfrom ordermastergroup 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.addressfrom 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 awhere a.customerno=b.customerno)(15)查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。

select customerno,orderno,ordersumfrom ordermasterwhere customerno in(select customernofrom ordermaster a,orderdetail b,product cwhere a.orderno=b.orderno andb.productno=c.productno and productname='52倍速光驱')and customerno in(select customernofrom ordermaster a,orderdetailb,product cwhere b.productno=c.productno andproductname='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可以用连接运算或集合运算I N来实现，而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 ANDc.productNo=d.productNoAND EXISTS(SELECT salerNoFROM OrderMaster e,OrderDetail fWHERE e.orderNo=f.orderNo AND a.employeeNo=salerNoGROUP BY salerNoHAVING count(distinct productNo)>=5)ORDER BY salerNo1. 首先将表Employee a, OrderMaster b, OrderDetail c, Product d进行连接2. 对连接后的记录，取出员工编号，判断是否至少销售了5种商品3. 如果是，将salerNo, employeeName, productName, quantity, price这五个值作为输出结果4．如果不是，舍弃该连接记录5. 取下一条连接记录，转2，直到所有的连接记录处理完毕6. 最将结果输出③存在量词一般用在相关子查询中，请分别给出存在量词用在相关子查询和非相关子查询的查询例子。