kurs:corellated_subquery_vs_pseudotable
Differences
This shows you the differences between two versions of the page.
|
kurs:corellated_subquery_vs_pseudotable [2012/12/18 10:24] mh |
kurs:corellated_subquery_vs_pseudotable [2014/09/10 21:22] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | <code> | ||
| - | /* | ||
| - | create table dep_avg as | ||
| - | select d.department_id, | ||
| - | department_name, | ||
| - | avg(salary) davg | ||
| - | from employees e join departments d on | ||
| - | d.department_id = e.department_id | ||
| - | group by | ||
| - | d.department_id, | ||
| - | department_name | ||
| - | */ | ||
| - | |||
| - | -- select * from dep_avg | ||
| - | |||
| - | select first_name, last_name, department_name, salary from employees e, departments d | ||
| - | where e.department_id = d.department_id and | ||
| - | salary > (select davg from dep_avg where department_id = d.department_id) | ||
| - | --and department_name = 'IT' | ||
| - | |||
| - | |||
| - | select first_name, last_name, department_name, salary from employees e, departments d | ||
| - | where e.department_id = d.department_id and | ||
| - | salary > (select avg(salary) from | ||
| - | -- employees ei join departments di on di.department_id = ei.department_id | ||
| - | employees ei, departments di | ||
| - | where | ||
| - | di.department_id = ei.department_id and | ||
| - | -- di.department_id = d.department_id | ||
| - | di.department_name = d.department_name | ||
| - | ) | ||
| - | |||
| - | select first_name, last_name, department_name, salary, | ||
| - | round(davg), round(salary-davg) mehr from | ||
| - | employees e, dep_avg d where | ||
| - | e.department_id = d.department_id and | ||
| - | salary > davg | ||
| - | order by mehr desc | ||
| - | |||
| - | select first_name, last_name, department_name, salary, | ||
| - | round(davg), round(salary-davg) mehr from | ||
| - | employees e, | ||
| - | -- hier stellen wir uns das avg salary pro department zusammen | ||
| - | (select d.department_id, | ||
| - | department_name, | ||
| - | avg(salary) davg | ||
| - | from employees e join departments d on | ||
| - | d.department_id = e.department_id | ||
| - | group by | ||
| - | d.department_id, | ||
| - | department_name ) d | ||
| - | -- ende avg sal per dep | ||
| - | where | ||
| - | e.department_id = d.department_id and | ||
| - | salary > davg | ||
| - | order by mehr desc | ||
| - | </code> | ||
| - | |||
| - | ==== Finde den Angestellten mit dem höchsten Gehalt pro Department ==== | ||
| - | |||
| - | <code> | ||
| - | SELECT first_name, last_name, salary, dep_outer.DEPARTMENT_NAME, dep_outer.department_id | ||
| - | FROM EMPLOYEES join DEPARTMENTS dep_outer | ||
| - | on (dep_outer.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID) | ||
| - | where | ||
| - | salary = (select max(salary) from employees where department_id = dep_outer.department_id) | ||
| - | order by dep_outer.department_id, salary | ||
| - | </code> | ||
kurs/corellated_subquery_vs_pseudotable.txt · Last modified: 2014/09/10 21:22 (external edit)
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
