dokuwiki

select last_name, department_id 
  from employees 
  where 
  department_id = 50 or department_id = 60 

select first_name, 
       last_name, 
       salary from employees
where salary >= 10000 and
      salary <  15000
order by salary desc
select first_name, 
       last_name, 
       salary from employees
where salary in('10000',
'11000',
'13000'
)
order by salary desc


select first_name, 
       last_name, 
       salary from employees
where salary not between 10000 and 14000
and salary not in (10000, 14000)
order by salary desc

null felder

select first_name vorname, 
       last_name,
       salary,
  --     salary * 2,
       salary + salary * coalesce(commission_pct, 0) auszahlung,
       coalesce(commission_pct, 0) cp
  from employees
  where salary + salary * coalesce(commission_pct, 0) > 10000
  order by cp asc
       -- commission_pct is null
       -- commission_pct in (0.15, 0.2, 0.3, null)

with emp_com as (
 select first_name, 
       last_name,
       hire_date,
       salary,                  
       coalesce(commission_pct, 0) comm_pct
     from employees
)
select first_name, 
       last_name,
       hire_date,
       salary,                  
       comm_pct,
       salary * comm_pct "der bonus"
  from emp_com

/* create view v_emp_com as 
 select first_name, 
       last_name,
       hire_date,
       salary+5 salary,                  
       coalesce(commission_pct, 0) comm_pct
     from employees
*/

select first_name, 
       last_name,
       hire_date,
       salary,                  
       comm_pct,
       salary * comm_pct "der bonus"
  from v_emp_com

select 
    employee_id,
    first_name,
    last_name,
    coalesce(to_char(commission_pct), 'n/a'),
    salary * coalesce(commission_pct, 0) as bonus
from hr.employees where 
employee_id in (144, 145)

select first_name,
       last_name,
       salary / 7,
       trunc(salary / 7, -3),
       round(salary / 7, 2)
from hr.employees
where salary > 12000