DB2數(shù)據(jù)庫必須掌握的常用語句：

1、 求每位客戶訂購的每種產(chǎn)品的總數(shù)量及平均單價(jià)，并按客戶號(hào)，產(chǎn)品號(hào)從小到大排列

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

2、 查詢訂購了三種以上產(chǎn)品的訂單號(hào)

Select order_no

from sale_item

Group by order_no

Having count(*)>3

3、 查詢訂購的產(chǎn)品至少包含了訂單3號(hào)中所訂購產(chǎn)品的訂單

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))

4、 在sales表中查找出訂單金額大于"E0013業(yè)務(wù)員在1996/11/10這天所接每一張訂單的金額"的所有訂單，并顯示承接這些訂單的業(yè)務(wù)員和該訂單的金額

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')

5、 查詢末承接業(yè)務(wù)的員工的信息

Select *

From employee a

Where not exists

(select * from sales b where a.emp_no=b.sale_id)

6、 查詢來自上海市的客戶的姓名，電話、訂單號(hào)及訂單金額

Select cust_name,tel_no,order_no,tot_amt

From customer a ,sales b

Where a.cust_id=b.cust_id and addr='上海市'

7、 查詢每位業(yè)務(wù)員各個(gè)月的業(yè)績(jī)，并按業(yè)務(wù)員編號(hào)、月份降序排序

Select sale_id,month(order_date), sum(tot_amt)

from sales

group by sale_id,month(order_date)

order by sale_id,month(order_date) desc

8、 求每種產(chǎn)品的總銷售數(shù)量及總銷售金額，要求顯示出產(chǎn)品編號(hào)、產(chǎn)品名稱，總數(shù)量及總金額，并按產(chǎn)品號(hào)從小到大排列

Select a.prod_id,prod_name,sum(qty),sum(qty*unit_price)

From sale_item a,product b

Where a.prod_id=b.prod_id

Group by a.prod_id,prod_name

Order by a.prod_id

9、 查詢總訂購金額超過'C0002'客戶的總訂購金額的客戶號(hào)，客戶名及其住址

Select cust_id, cust_name,addr

From customer

Where cust_id in (select cust_id from sales

Group by cust_id

Having sum(tot_amt)>

(Select sum(tot_amt) from sales where cust_id='C0002'))

10、 查詢業(yè)績(jī)***的的業(yè)務(wù)員號(hào)、業(yè)務(wù)員名及其總銷售金額

select emp_no,emp_name,sum(tot_amt)

from employee a,sales b

where a.emp_no=b.sale_id

group by emp_no,emp_name

having sum(tot_amt)=

(select max(totamt)

from (select sale_id,sum(tot_amt) totamt

from sales

group by sale_id) c)

11、 查詢每位客戶所訂購的每種產(chǎn)品的詳細(xì)清單，要求顯示出客戶號(hào)，客戶名，產(chǎn)品號(hào)，產(chǎn)品名，數(shù)量及單價(jià)

select a.cust_id, cust_name,c.prod_id,prod_name,qty, unit_price

from customer a,sales b, sale_item c ,product d

where a.cust_id=b.cust_id and b.order_no=c.order_no and c.prod_id=d.prod_id

12、 求各部門的平均薪水，要求按平均薪水從小到大排序

select dept,avg(salary)

from employee

group by dept

order by avg(salary)