User Tools

Site Tools


kurs:einfache_selects
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
kurs/einfache_selects.txt · Last modified: 2014/09/10 21:22 (external edit)