发布时间 : 星期日 文章实验五大数据查询 - 复杂查询更新完毕开始阅读16010100370cba1aa8114431b90d6c85ed3a8817
实用标准文档
customer.customerno=ordermaster .customerno and ordermaster.orderno=orderdetail.orderno
and orderdetail .productno =product.productno and
product.productno ='P2007001') order by customer.customerno
（24）计算每一商品每月的销售金额总和，并将结果首先按销售月份然后按订货金额降序排序输出。 select
ordermaster.customerno ,ordermaster .orderdate,sum(ordersum) as '销售金额总和' from ordermaster
group by ordermaster .customerno,ordermaster.orderdate order by ordermaster .orderdate,销售金额总和 desc
（25）查询订购了“键盘”商品的客户姓名，订货数量和订货日期 select
customer.customername,orderdetail.qty,ordermaster .orderdate from customer,orderdetail,ordermaster,
product where customer.customerno=ordermaster .customerno and orderdetail .orderno =ordermaster .orderno and
product.productno =orderdetail .productno and productname='键盘'
（26）查询没有订购“键盘”商品的客户名称。
文案大全
实用标准文档
select distinct customername from customer where customername not in(
select distinct customername from
customer,ordermaster,orderdetail,product where ordermaster .customerno =customer.customerno and
ordermaster .orderno =orderdetail .orderno and product.productno =orderdetail.productno and productname='键盘')
（27）查询至少销售了种商品的销售员编号，姓名，商品名称，数量及相应的单价，并按销售员编号排序输出。 select
employee.employeeno,employeename,productname,orderdetail.qty,orderdetail .price from employee,product,
orderdetail,ordermaster where employee.employeeno
=ordermaster.saleno and ordermaster.orderno =orderdetail.orderno and orderdetail.productno =product .productno and employee.employeeno in
(select employee.employeeno from employee,product, orderdetail,ordermaster where employee.employeeno
=ordermaster.saleno and ordermaster.orderno =orderdetail.orderno and orderdetail.productno =product .productno group by employee .employeeno having COUNT(employee.employeeno)>=5)
文案大全
实用标准文档
order by employeeno
（28）查询没有订购商品的客户编号和客户名称。
select a.customerno from customer a where not exists( select *from ordermaster where a.customerno =ordermaster.customerno)
（29）查询至少包含了“世界技术开发公司”所订购的商品的客户编号，客户名称，商品编号，商品名称，数量和金额。 select
customer.customerno ,customername,product.productno ,productname,orderdetail .qty ,qty*price from
customer,product ,orderdetail ,ordermaster where customer.customerno =ordermaster .customerno and ordermaster .orderno
=orderdetail .orderno and orderdetail .productno =product.productno and customer .customername in (
select customername from customer where not exists
(select *from (select customername,orderdetail .productno from customer custoemrb,orderdetail ,ordermaster ,product productb where custoemrb.customername ='世界技术开发公司'and
文案大全
实用标准文档
custoemrb .customerno =ordermaster .customerno and ordermaster .orderno =orderdetail .orderno and orderdetail .productno =productb.productno)a where not exists (select *from (select
customername,orderdetail .productno from customer custoemrb,orderdetail ,ordermaster ,product productb where custoemrb .customerno =ordermaster .customerno and ordermaster .orderno =orderdetail .orderno and orderdetail .productno =productb.productno) b
where customer.customername =b.customername and b.productno =a.productno )))
六、思考题：
1．存在量词与集合运算in、连接运算和全程量词之间的关系如何？他们可以互相替换吗？给出你的理由
2．Where子句和having子句都是用于指定查询条件的，请区别它们的异同？用实例说明。
3．在分组聚集操作中，为什么在查询列中，除了聚集函数运算外，其它表达式必须包含在group by子句中？
七、总结（实验过程的体会、心得和实验教与学之间还需改进的内容）
文案大全