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

核心提示： (select max(totamt)from (select sale_id,sum(tot_amt) totamtfrom salesgroup by sale_id) c)53、 查询每位客户所订购的每种产品的详细清单，要求显示出客户号，学习DB2数据库必须掌握的五十四条常用语句(1

(select max(totamt)

from (select sale_id,sum(tot_amt) totamt

from sales

group by sale_id) c)

53、 查询每位客户所订购的每种产品的详细清单，要求显示出客户号，客户名，产品号，产品名，数量及单价

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

54、 求各部门的平均薪水，要求按平均薪水从小到大排序

select dept,avg(salary)

from employee

group by dept

order by avg(salary)

26、选取编号界于'C0001'和'C0004'的客户编号、客户名称、客户地址

select CUST_ID,cust_name,addr

from customer

where cust_id between 'C0001' AND 'C0004'

27、计算出一共销售了几种产品

select count(distinct prod_id) as '共销售产品数'

from sale_item

28、将业务部员工的薪水上调3%

update employee

set salary=salary*1.03

where dept='业务'

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

select *

from employee

where salary=

(select min(salary )

from employee )

30、使用join查询客户姓名为"客户丙"所购货物的"客户名称","定单金额","定货日期","电话号码"

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

from customer a join sales b