51CTO下载-db2常用语句总结(新人必读)

1、db2常用语句总结时间：2008-05-05 00:40来源:coderarea网络搜集 作者：点击：369次1、查找员工的编号、姓名、部门和出生日期，如果出生日期为空值，显示日期 不详，并按部门排序输出，日期格式为yyyy-mm-ddo selectemp no,emp name, dept, isnull(convcrt(char(10), birthday, 120),1、查找员工的编号、姓名、部门和出生fl期，如果出生日期为空值， 显示日期不详，并按部门排序输出，日期格式为yyyy-nini-ddo selectemp_no, emp_name, dept, isnull(conver

2、t (char (10),birthday,120),5 日 期不详')birthdayfrom employee order by dept2、查找与喻自强在同一个单位的员工姓名、性别、部门和职称 select emp_no, emp_name,dept,title from employee where emp name'喻 自强，and dept in (select dept from employee where emp_name="喻 自 强')3、按部门进行汇总，统计每个部门的总工资 select dept, sum(salary) from e

3、mployee group by dept4、查找商品名称为14寸显示器商品的销售情况，显示该商品的编号、销售 数量、单价和金额select a. prod id, qty, unit price, unit_price*qty totpricefrom sale_item a, product bwhere a. prod_id=b. prod_id and prod_name二t4 寸显示器'5、在销售明细表中按产品编号进行汇总，统计每种产品的销售数量和金额 select prod id, sum (qty) totqty, sum(qty*unit_price) totprice

4、 from sale_item group by prod_id6、使用convert函数按客户编号统计每个客户1996年的订单总金额 select cust_id, sum(tot_amt) totprice from sales where convert(char(4), order_date, 120)=' 1996， group by cust_id7、查找有销售记录的客户编号、名称和订单总额 select a. cust_id, cust_name, sum(tot_amt) totprice from customet a, sales b where a. cust_i

5、d=b. cust_id group by a. cust id, cust name8、查找在1997年中有销售记录的客户编号、名称和订单总额 select a. cust_id, cust_name, sum(tot_amt) totprice from customer a,sales b where a. cust_id=b. cust_id and convert(char(4), order_date, 120)二t997， group by a. cust_id，cust_name9、查找一次销售最大的销售记录 select order no, cust id, sale id,

6、 tot_amt from sales where tot_amt二(select max(tot_amt) from sales)10、查找至少有3次销售的业务员名单和销售日期 select emp_name, order_date from employee a, sales b where emp_no=sale_id and a. emp_no in (select sale_id from sales group by sale_id having count (*)>=3) order by emp_name11、用存在量词查找没有订货记录的客户名称 select cust_

7、name from customer a where not exists(select * from sales b where a. cust id二b. cust id)12、使用左外连接查找每个客户的客户编号、名称、订货口期、订单金额订 货fi期不要显示时间，fi期格式为yyyy-mm-dd按客户编号排序，同一客户再按 订单降序排序输出 selecta. cust id, cust name, convert(char(10), order date, 120), tot amtfrom customer a left outer join sales b on a. cust_id二

8、b. cust_id order by a. cust_id, tot_amt desc13、查找16m dram的销售情况，要求显示相应的销售员的姓名、性别，销 售口期、销售数量和金额，其屮性别用男、女表示select emp name 姓名，性别二 case a. sex when ' then '男' when ' f' then '女else '未end,销售日期二 isnull (convert (char 仃0), c. order date, 120),，日期不详 ) "qty 数量，qty*unit_price

9、as 金额from employee a, sales b, sale_item c, product dwhere d. prod_name=，16m dram' and d. prod_id=c. prod_id anda. emp_no=b. sale_id and b. order_no=c. order_no14、查找每个人的销售记录，要求显示销售员的编号、姓名、性别、产品名 称、数量、单价、金额和销售日期select emp no 编号，emp name 姓名，性另case a. sex when，nv then，男when ' f， then '女el s

10、e '未end,prod_name产品名称，销售日期二isnull (convert (char (10), c. order date, 120),'日期不详'),qty 数量，qty*unit_price as 金额from employee a 1 eft outer join sales b on a. emp no二b.sale_id ,sale_item c,product dwhere d. prod_id=c. prod_id and b. order_no=c. order_no15、查找销售金额最大的客户名称和总货款 select cust_name

11、, d. cust_sum from customer a, (select cust_id, cust_suni from (select custid, sum(totamt) as custsum from sales group by cust_id ) b where b.cust_sum = (select max(cust_sum) from (select custid, sum(totamt) as custsum from sales group by cust_id ) c ) )d where a. cust id=d.cust id26、选取编号界于'c000

12、1'和'c0004'的客户编号、客户名称、客户地址。select cust_id, cust_name, addrfrom customerwhere custid between 'c0001' and 'c0004'27、计算出一共销售了儿种产品。select count (distinct prod_id) as '共销售产品数' from sale_item28、将业务部员工的薪水上调3%。update employeeset salary二salary*1. 03 where dept=，业务29>由emp

13、loyee表中查找出薪水最低的员工信息。select *from employeewhere salary=(select min(salary ) from employee )30、使用join查询客户姓名为客户丙所购货物的客户名称，定单金额 ，定货日期,屯话号码select a. cust_id, b. tot_amt, b. order_date, a. tel_nofrom customer a join sales bon a. cust_id=b. cust_id and cust_name 1 ike '客户丙，31、由sales表中查找出订单金额大于e0013业务员在

14、1996/10/15这天所 接每一张订单的金额的所有订单。select *from saleswhere tot_amt>all (select tot_amt from saleswhere sale_id=，e0013, and order_date=，1996/10/15,)order by tot_amt32、计算'p0001'产品的平均销售单价select avg(unit_price)from sale_item where prod_id=, p000t33、找出公司女员工所接的定单 select sale id,tot amt from sales whe

15、re saleid in(select sale_id from employee where sexf)34、找出同一天进入公司服务的员工 select a. emp_no, a. emp_name, a. date_hired from employee a join employee b on (a. cmp_no!=b. cmp_no and a. datc_hircd=b. datc_hircd) order by a. date_hired35、找出目前业绩超过232000元的员工编号和姓名。select emp_no, emp_namefrom employeewhere emp

16、_no in(select saleidfrom salesgroup by sale_id having sum(tot_amt)<232000)16、查找销售总额少于1000元的销售员编号、姓名和销售额 select emp_no, emp_name, d. sale_sumfrom employee a, (select sale_id, sale_sum from (select sale_id, sum(tot_amt) as sale_sum from sales group by sale_id ) b where b. sale sum <1000 )dwhere

17、a. emp_no=d. sale_id17、查找至少销售了 3种商品的客户编号、客户名称、商品编号、商品名称、 数量和金额 selecta. cust_id, cust_name, b. prod id, prod name, d. qty, d. qty*d. unit_price from customer a, product b, sales c, sale_item dwhere a. cust id二c. cust id and d id二b. prod id and c.order no二d.order no and a. cust id in ( select cu

18、st_idfrom (select cust_id, count(distinct prod_id) prodid from (select cust_id, prod_idfrom sales e, sale_item fwhere e.order_no=f. order_no) ggroup by cust_idhaving count (distinct prod_id)>=3) h )18、查找至少与世界技术开发公司销售相同的客户编号、名称和商品编号、 商品名称、数量和金额 selecta. cust_id, cust_name, d. prod_id, prod_name, q

19、ty, qty*unit_pricc from customer a, product b, sales c, sale_item d where a. cust_id=c. cust id and d. prod id二d id andc. order_no=d. order_no and not exists(select f. *from customer x 、 sales e, sale_item f where cust_name二'世界技术开发公司，and x. cust_id=e. cust_id and e. order_no=f. order_no and not exists(select g. *from sale_item g, sales hwhere g. prod_id 二 f. prod_id and g. order_no=h. order_no andh. cust id二a. cust id)19、查找表中所有姓刘的职工的工号，部门，薪水 select emp_no, emp_name, dept, salary from employee where emp