dokuwiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
<code>
select level, lpad('-', level*2-2, '=') as position, 
       employee_id as emp_id, 
       last_name as emp_name, 
       prior employee_id as man_id, 
       prior last_name as man_name,
       salary - prior salary as diff
       from employees
--where salary - prior salary > 0
connect by prior employee_id = manager_id
start with employee_id = 100

</code>

ast abschneiden

<code>
select level, lpad('-', level*2-2, '=') as position, 
       employee_id as emp_id, 
       last_name as emp_name, 
       prior employee_id as man_id, 
       prior last_name as man_name,
       salary - prior salary as diff
       from employees
--where salary - prior salary > 0
-- where prior employee_id != 108
connect by prior employee_id = manager_id and employee_id != 108
start with employee_id = 101
</code>

<code>
select hd, count(hd) from (
  select extract(year from hire_date) hd  from employees
  --union
  --select level+1985 i from dual connect by level < 20
  )
group by hd 
order by hd


select jahr, count(hire_date) from 
(select level+1985 jahr from dual connect by level < 20) left join employees
on  extract(year from hire_date) = jahr
group by jahr
order by jahr
</code>

<code>
select prior salary - salary, salary, last_name from (
  select rownum rn, last_name, salary from (
    select last_name, salary from employees order by salary desc
  )
) connect by prior rn = rn-1 
start with rn = 1
</code>

=== Ähnliches Resultat, plain SQL ===

<code>
select a.first_name, 
        a.last_name, a.salary, 
        max(b.salary), (max(b.salary)-a.salary)
from employees a join employees b
on a.salary > b.salary
and  a.employee_id <> b.employee_id
group by a.first_name, a.last_name, a.salary
order by a.salary desc;
</code>

=== Hierchie in einer Zeile ===
<code>
select first_name || ' ' || last_name employee_name,       
      level, lpad('>', level * 3, '*') p,
       sys_connect_by_path ( first_name || ' ' || last_name, '/') h
from employees e 
connect by prior employee_id = manager_id
start with employee_id = (select employee_id from employees where manager_id is null) -- = 100
</code>
dokuwiki

User Tools

Site Tools

Page Tools
