User Tools

Site Tools


kurs:einfache_selects

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
kurs:einfache_selects [2011/12/16 13:28]
mh
kurs:einfache_selects [2020/05/05 12:06] (current)
admin
Line 30: Line 30:
 order by salary desc order by salary desc
 </​code>​ </​code>​
 +
 +null felder
 +
 +<​code>​
 +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)
 +</​code>​
 +
 +<​code>​
 +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
 +</​code>​
 +
 +<​code>​
 +/* 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
 +</​code>  ​
 +
 +
 +<​code>​
 +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)
 +</​code>​
 +
 +<​code>​
 +select first_name,
 +       ​last_name,​
 +       ​salary / 7,
 +       ​trunc(salary / 7, -3),
 +       ​round(salary / 7, 2)
 +from hr.employees
 +where salary > 12000
 +</​code>​
 +
kurs/einfache_selects.1324038521.txt.gz ยท Last modified: 2014/09/10 21:22 (external edit)