学习DB2数据库必须掌握的五十四条常用语句

核心提示： 41、 查询在每张订单中订购金额超过4000元的客户名及其地址Select cust_name,addr from customer a,sales bwhere a.cust_id=b.cust_id and tot_amt>400042、 求出每位客户的总订购金额，显示出客户号及

41、 查询在每张订单中订购金额超过4000元的客户名及其地址

Select cust_name,addr from customer a,sales b

where a.cust_id=b.cust_id and tot_amt>4000

42、 求出每位客户的总订购金额，显示出客户号及总订购金额，并按总订购金额降序排列

Select cust_id,sum(tot_amt) from sales

Group by cust_id

Order by sum(tot_amt) desc

43、 求每位客户订购的每种产品的总数量及平均单价，并按客户号，产品号从小到大排列

Select cust_id,prod_id,sum(qty),sum(qty*unit_price)/sum(qty)

From sales a, sale_item b

Where a.order_no=b.order_no

Group by cust_id,prod_id

Order by cust_id,prod_id

44、 查询订购了三种以上产品的订单号

Select order_no

from sale_item

Group by order_no

Having count(*)>3

45、 查询订购的产品至少包含了订单3号中所订购产品的订单

Select distinct order_no

From sale_item a

Where order_no<>'3'and not exists (

Select * from sale_item b where order_no ='3' and not exists

(select * from sale_item c where c.order_no=a.order_no and c.prod_id=b.prod_id))

46、 在sales表中查找出订单金额大于"E0013业务员在1996/11/10这天所接每一张订单的金额"的所有订单，并显示承接这些订单的业务员和该订单的金额

Select sale_id,tot_amt from sales

where tot_amt>all(select tot_amt

from sales

where sale_id='E0013' and order_date='1996-11-10')