Lesson 04

select to_char( salary, '$99,999,00') Salary
from employees
where last_name = 'Ernst';

select last_name, to_char( hire_date, 'DD-Mon-YYYY')
from employees
where hire_date < to_date('01-Jan-90','DD-Mon-RR');

select last_name,
  upper(concat(substr(last_name, 1, 8), '_US'))
from employees
where department_id = 60;

select last_name, salary, NVL(commission_pct, 0 ),
  (salary*12) + (salary*12*NVL(commission_pct, 0 )) AN_SAL
from employees;

select last_name, salary, commission_pct,
  NVL2(commission_pct, 'SAL+COMM', 'SAL') income 
from employees where department_id IN (50 , 80);

select first_name, length(first_name) "expr1",
  last_name, length (last_name) "expr2",
  NULLIF(length(first_name), length(last_name)) result
from employees;

select last_name, employee_id,
  coalesce(to_char(commission_pct), to_char(manager_id),
    'No commission and no manager')
from employees;

select last_name, job_id, salary,
  case job_id when 'IT_PROG' then 1.10* salary
              when 'ST_CLERK' then 1.15*salary
              when 'SA_REP' then 1.20*salary
  else        salary end "revised_salary"
from employees;

select last_name, job_id, salary,
  DECODE(job_id, 'IT_PROG',  1.10*salary,
                  'ST_CLERK', 1.15*salary,
                  'SA_REP',   1.20*salary,
        salary) REVISED_SALARY
from   employees;

select last_name, salary,
  DECODE (TRUNC(salary/2000, 0),
                          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;