DB2數(shù)據(jù)庫的常用語句之前已經(jīng)為大家介紹了很多，本文將繼續(xù)講述DB2數(shù)據(jù)庫常用語句。

1、統(tǒng)計(jì)表中員工的薪水在4000-6000之間的人數(shù)

select count(*)as 人數(shù)

from employee

where salary between 4000 and 6000

2、查詢表中的同一部門的職工的平均工資，但只查詢"住址"是"上海市"的員工

select avg(salary) avg_sal,dept

from employee

where addr like '上海市%'

group by dept

3、將表中住址為"上海市"的員工住址改為"北京市"

update employee

set addr like '北京市'

where addr like '上海市'

4、查找業(yè)務(wù)部或會計(jì)部的女員工的基本信息

select emp_no,emp_name,dept

from employee

where sex='F'and dept in ('業(yè)務(wù)','會計(jì)')

5、顯示每種產(chǎn)品的銷售金額總和，并依銷售金額由大到小輸出

select prod_id ,sum(qty*unit_price)

from sale_item

group by prod_id

order by sum(qty*unit_price) desc
 

6、選取編號界于'C0001'和'C0004'的客戶編號、客戶名稱、客戶地址

select CUST_ID,cust_name,addr

from customer

where cust_id between 'C0001' AND 'C0004'

7、計(jì)算出一共銷售了幾種產(chǎn)品

select count(distinct prod_id) as '共銷售產(chǎn)品數(shù)'

from sale_item

8、將業(yè)務(wù)部員工的薪水上調(diào)3%

update employee

set salary=salary*1.03

where dept='業(yè)務(wù)'

9、由employee表中查找出薪水最低的員工信息

select *

from employee

where salary=

(select min(salary )

from employee )

10、使用join查詢客戶姓名為"客戶丙"所購貨物的"客戶名稱","定單金額","定貨日期","電話號碼"

select a.cust_id,b.tot_amt,b.order_date,a.tel_no

from customer a join sales b

on a.cust_id=b.cust_id and cust_name like '客戶丙'

【編輯推薦】

1. DB2數(shù)據(jù)庫優(yōu)化超有用的幾條基本策略
2. DB2數(shù)據(jù)庫卸載的常用問題分析及方法
3. DB2數(shù)據(jù)庫所支持的外部文件格式