decode_求所得税.txt

来自「orale培训教材包括了所有的sql说明和实例」· 文本 代码 · 共 39 行

--------------------------------------------------------------------
--最高税率：<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');

--建立雇员工资单：显示雇员编号，名称，工资，所得税，税后工资。
--一个view
create or replace view emp_salary as
select empid,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) tax , 
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) shijisal
        from   sm_emp;

--两个view
create or replace view emp_sal1 as
select empid,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) tax
from sm_emp;

create or replace view emp_salary2 as
select empid,name,salary , tax,salary-tax shijisal from emp_sal1;