DB2數(shù)據(jù)庫必須掌握的常用語句（五）
DB2數(shù)據(jù)庫必須掌握的常用語句：
1、 求每位客戶訂購的每種產(chǎn)品的總數(shù)量及平均單價，并按客戶號，產(chǎn)品號從小到大排列
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)品的訂單號
Select order_no
from sale_item
Group by order_no
Having count(*)>3
3、 查詢訂購的產(chǎn)品至少包含了訂單3號中所訂購產(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è)務員在1996/11/10這天所接每一張訂單的金額"的所有訂單，并顯示承接這些訂單的業(yè)務員和該訂單的金額
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è)務的員工的信息
Select *
From employee a
Where not exists
(select * from sales b where a.emp_no=b.sale_id)
6、 查詢來自上海市的客戶的姓名，電話、訂單號及訂單金額
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è)務員各個月的業(yè)績，并按業(yè)務員編號、月份降序排序
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)品編號、產(chǎn)品名稱，總數(shù)量及總金額，并按產(chǎn)品號從小到大排列
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'客戶的總訂購金額的客戶號，客戶名及其住址
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è)績***的的業(yè)務員號、業(yè)務員名及其總銷售金額
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)品的詳細清單，要求顯示出客戶號，客戶名，產(chǎn)品號，產(chǎn)品名，數(shù)量及單價
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)
