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

select emp_name 姓名, 性别= case a.sex when 'm' then '男'

when 'f' then '女'

else '未'

end,

销售日期= isnull(convert(char(10),c.order_date,120),'日期不详'),

qty 数量, qty*unit_price as 金额

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

where d.prod_name='16M DRAM' and d.prod_id=c.prod_id and

a.emp_no=b.sale_id and b.order_no=c.order_no

14、查找每个人的销售记录，要求显示销售员的编号、姓名、性别、产品名称、数量、单价、金额和销售日期

select emp_no 编号,emp_name 姓名, 性别= case a.sex when 'm' then '男'

when 'f' then '女'

else '未'

end,

prod_name 产品名称,销售日期= isnull(convert(char(10),c.order_date,120),'日期不详'),

qty 数量, qty*unit_price as 金额

from employee a left outer join sales b on a.emp_no=b.sale_id , sale_item c,product d

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

15、查找销售金额最大的客户名称和总货款

select cust_name,d.cust_sum

from customer a,

(select cust_id,cust_sum

from (select cust_id, sum(tot_amt) as cust_sum

from sales

group by cust_id ) b

where b.cust_sum =

( select max(cust_sum)

from (select cust_id, sum(tot_amt) as cust_sum

from sales

group by cust_id ) c )

) d