复杂查询参考答案(最新整理)

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

2、。select ductno,orderno,quantity,(quantity*price) money from orderdetail a, (select productnofrom orderdetail group by productno having count(*)=3) bwhere ductno =ductno order by ductno,quantity desc(3) 查找销售总额少于 5000 元的销售员编号、姓名和销售额。select a.employeeno,a.employeename,sum(quantity*p

3、rice) sunmoney from employee a,orderdetail b,ordermaster cwhere a.employeeno=c.salerno and b.orderno=c.orderno group by a.employeeno,a.employeenamehaving sum(quantity*price)5000(4) 找出目前业绩未超过 5000 元的员工，并按销售业绩的降序排序输出。select employeeno,employeename,ordersumfrom employee a,(select salerno,sum(ordersum)

4、ordersumfrom ordermaster group by salernohaving sum(ordersum)5000) b where a.employeeno=b.salernoorder by ordersum desc(5) 查询订购的商品数量没有超过 10 个的客户编号和客户名称。select a.customerno,customername from customer awhere a.customerno in ( select customernofrom ordermaster b,orderdetail c where b.orderno=c.ordernog

5、roup by customerno having sum(quantity)=3) group by a.customerno,customername,b.productno,productname,quantity(8) 找出目前销售业绩超过 4000 元的业务员编号及销售业绩，并按销售业绩从大到小排序。select employeeno , sumorderfrom employee a,(select salerno,sum(ordersum) sumorderfrom ordermaster group by salerno) bwhere a.employeeno=b.saler

6、no and sumorder4000 order by sumorder desc(9) 求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。select customerno,productno,sum(quantity) quantitys, (sum(quantity*price)/sum(quantity) avgpricefrom ordermaster a,orderdetail b where a.orderno=b.ordernogroup by customerno,productno order by customerno,productno(10

7、) 查询业绩最好的的业务员号、业务员名及其总销售金额。select salerno,employeename,sum(ordersum) from employee a,ordermaster bwhere a.employeeno=b.salerno group by salerno,employeenamehaving sum(ordersum) = (select max(ordersum)from (select sum(ordersum) ordersum from ordermastergroup by salerno) x)(11) 查询订购的商品至少包含了订单“20080301

8、0001”中所订购商品的订单。select *from ordermaster a where not exists(select *from orderdetail ywhere orderno=200803010001 and not exists (select *from orderdetail zwhere ductno=ductno and a.orderno=z.orderno)(12) 查询总订购金额超过“c20070002”客户的总订购金额的客户号、客户名及其住址。select a.customerno,customername,address from

9、ordermaster a,customer bwhere a.customerno=b.customernogroup by a.customerno,customername,address having sum(ordersum)=(select sum(ordersum)from ordermasterwhere customerno=c20070002 group by customerno)(13) 查询总销售金额最高的销售员编号、订单编号、订单日期和订单金额。select salerno,b.orderno,orderdate, ordersum from employee a,

10、ordermaster bwhere a.employeeno=b.salernoand ordersum =(select max(ordersum) from ordermaster)(14) 用存在量词查找没有订货记录的客户名称。select customername from customer c where not exists(select * from ordermaster awhere a.customerno=c.customerno )(15) 查询既订购了“52 倍速光驱”商品，又订购了“17 寸显示器”商品的客户编号、订单编号和订单金额。select customer

11、no,orderno,ordersum from ordermasterwhere customerno in (select customernofrom ordermaster a,orderdetail b,product cwhere a.orderno=b.orderno and ductno=ductno and productname=52倍速光驱)and customerno in (select customernofrom ordermaster a,orderdetail b,product c where a.orderno=b.orderno an

12、ductno=ductno and productname=17寸显示器)(16) 求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。select customerno,productno,sum(quantity) quantitys, (sum(quantity*price)/sum(quantity) avgpricefrom ordermaster a,orderdetail b where a.orderno=b.ordernogroup by customerno,productno order by customerno,produ

13、ctno(17) 实验问题： 存在量词与集合运算 in、连接运算和全称量词之间的关系如何？它们可以互相替换吗？给出你的理由。答：存在量词 exists 可以用连接运算或集合运算 in 来实现，而 sql 中没有全称量词， 只能用存在量词和取非运算来实现； 请写出例 2.51 的执行过程。例 2.51 查询至少销售了 5 种商品的销售员编号、姓名、商品名称、数量及相应的单价， 并按销售员编号排序输出。分析： 构造一个子查询，针对外查询中的每个销售员，判断其是否销售了 5 种以上的商品， 使用相关子查询。 sql 语句为：select salerno, employeename, productn

14、ame, quantity, price from employee a, ordermaster b, orderdetail c, product dwhere a.employeeno=salerno and b.orderno=c.orderno and ductno=ductno and exists(select salernofrom ordermaster e, orderdetail fwhere e.orderno=f.orderno and a.employeeno=salerno group by salernohaving count(distin

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

16、例子。答：相关子查询：select studentname,classno from student xwhere exists(select * from score a,course b where a.courseno=b.coursenoand a.studentno=x.studentno and coursename=操作系统）非相关子查询：select studentno,classno from studentwhere not exists(select *from studentwhere studentname=王红)“”“”at the end, xiao bian g

17、ives you a passage. minand once said, people who learn to learn are very happy people. in every wonderful life, learning is an eternal theme. as a professional clerical and teaching position, i understand the importance of continuous learning, life is diligent, nothing can be gained, only continuous learning can achieve better self. only by constantly learning and mastering the latest relevant knowledge