Differences

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

--- kurs:einfache_selects [2010/05/18 11:15]
127.0.0.1 external edit
+++ kurs:einfache_selects [2020/05/05 12:06] (current)
admin
@@ Line 1: / Line 1: @@
 <code>
-select last_name, department_id from employees where department_id = 50 or department_id = 60
+select last_name, department_id
+  from employees
+  where
+  department_id = 50 or department_id = 60
 </code>
+<code>
+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
+</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>

dokuwiki

User Tools

Site Tools

Differences

Page Tools