User Tools

Site Tools


kurs:select_mit_group_by_subquery

Differences

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

Link to this comparison view

kurs:select_mit_group_by_subquery [2014/09/10 21:22] (current)
Line 1: Line 1:
 +<​code>​
 +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 ​                              
 +</​code> ​
 +
 +zusatzaufgabe diejenige mit der größten differenz per Department herausfinden!
kurs/select_mit_group_by_subquery.txt · Last modified: 2014/09/10 21:22 (external edit)