dokuwiki

select department_id, round(avg(SALARY), 2) sal, round(sum(SALARY), 2), count(*) as c from employees
group by department_id)
having avg(SALARY)>8000 -- NACHTTEIL!!! in der having clause kann man nicht auf Synonyme zugreifen
order by sal;

select * from (
select department_id, round(avg(SALARY), 2) sal, round(sum(SALARY), 2), count(*) as c from employees
group by department_id)
where sal>8000
order by sal;


-- alles auf einmal

select first_name, last_name, salary, davgs.department_id, avgs from 
   employees e, (select department_id, avg(salary) avgs  from employees 
                                group by department_id) davgs
   where davgs.department_id = e.department_id 
     and salary > avgs
   
-- zuerst table anlegen
create table avg_salaray as select department_id, avg(salary) avgs  from employees 
                                group by department_id
                       

-- nun ganz "normal" joinen

select first_name, last_name, salary, e.department_id, avgs from 
   employees e, avg_salaray
 where  salary > avgs
   and  avg_salaray.department_id = e.department_id                               

zusatzaufgabe diejenige mit der größten differenz per Department herausfinden!