实验五大数据查询 - 复杂查询

实用标准文档

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子句中？

七、总结（实验过程的体会、心得和实验教与学之间还需改进的内容）

文案大全