1.case
SQL> SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10* 2 salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;
3 4 5 6
LAST_NAME JOB_ID SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
OConnell SH_CLERK 2600 2600
Grant SH_CLERK 2600 2600
Whalen AD_ASST 4400 4400
Hartstein MK_MAN 13000 13000
Fay MK_REP 6000 6000
Mavris HR_REP 6500 6500
Baer PR_REP 10000 10000
Higgins AC_MGR 12008 12008
Gietz AC_ACCOUNT 8300 8300
King AD_PRES 24000 24000
Kochhar AD_VP 17000 17000
2.decode
SQL> SELECT last_name, salary,
DECODE (TRUNC(salary/2000, 0),
2 3 0, 0.00,
1, 0.09,
2, 0.20,
3, 0.30,
4, 0.40,
5, 0.42,
6, 0.44,
0.45) TAX_RATE
FROM employees
WHERE department_id = 80;
4 5 6 7 8 9 10 11 12
LAST_NAME SALARY TAX_RATE
------------------------- ---------- ----------
Russell 14000 .45
Partners 13500 .44
Errazuriz 12000 .44
Cambrault 11000 .42
Zlotkey 10500 .42
Tucker 10000 .42
Bernstein 9500 .4
Hall 9000 .4
Olsen 8000 .4
Cambrault 7500 .3
Tuvault 7000 .3