decode_求所得税.txt

orale培训教材包括了所有的sql说明和实例

--------------------------------------------------------------------
--最高税率：<1000,  税率 0。1000--2000 税率 0.1。2000--3000 税率 0.2，3000--4000 0.31
--4000以上0.5


insert into sm_emp values('aa1','aa1',null,'12');
insert into sm_emp values('aa3','aa3',3001,'12');
insert into sm_emp values('aa4','aa4',4001,'12');
insert into sm_emp values('aa5','aa5',5001,'12');

--基本查询 求sm_emp表中雇员的所得税
select name "姓名", salary "薪金",
               decode( trunc(salary/1000, 0), 0, 0.0,
                                          1, (salary-1000)*0.1,
                                          2, (salary-2000)*0.2 +1000*0.1,
                                          3, (salary-3000)*0.31+1000*0.2+1000*0.1,
					   (salary-4000)*0.5+1000*0.31+1000*0.2+1000*0.1) "所得税"
        from   sm_emp;

--------------------------------------------------------------------------
select name "姓名", salary "薪金",
               decode( trunc(nvl(salary,0)/1000, 0), 0, 0.0,
                                          1, (salary-1000)*0.1,
                                          2, (salary-2000)*0.2 +1000*0.1,
                                          3, (salary-3000)*0.31+1000*0.2+1000*0.1,
					   (salary-4000)*0.5+1000*0.31+1000*0.2+1000*0.1) "所得税"
        from   sm_emp;

-----1建立存储员工所得税的表格---sm_emp_tax,并插入纪录
create table sm_emp_tax(
id char(10),
tax number(6,2));

--insert 与子查询插入数据
insert into sm_emp_tax  (select empid,
decode( trunc(nvl(salary,0)/1000, 0), 0, 0.0,
                                          1, (salary-1000)*0.1,
                                          2, (salary-2000)*0.2 +1000*0.1,
                                          3, (salary-3000)*0.31+1000*0.2+1000*0.1,
					   (salary-4000)*0.5+1000*0.31+1000*0.2+1000*0.1) 
from sm_emp);


--2create 与子查询建立表并插入数据---------------------------------
create table sm_emp_tax as (
select empid, decode( trunc(nvl(salary,0)/1000, 0), 0, 0.0,
                                          1, (salary-1000)*0.1,
                                          2, (salary-2000)*0.2 +1000*0.1,
                                          3, (salary-3000)*0.31+1000*0.2+1000*0.1,
					   (salary-4000)*0.5+1000*0.31+1000*0.2+1000*0.1) tax from sm_emp);

--给sm_emp添加一个字段tax,更新tax为相应员工的所得税。
alter table sm_emp add tax number(8,6);

update sm_emp set tax=decode( trunc(nvl(salary,0)/1000, 0), 0, 0.0,
                                          1, (salary-1000)*0.1,
                                          2, (salary-2000)*0.2 +1000*0.1,
                                          3, (salary-3000)*0.31+1000*0.2+1000*0.1,
					   (salary-4000)*0.5+1000*0.31+1000*0.2+1000*0.1);

alter table sm_emp drop column tax;

----------------------------------------------建立空表-----------------------------
create table sm_emp_temp as (select * from sm_emp where 1=2);

注意：在实际开发中可以使用更复杂的查询从多表建立一个表。