Differences

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

--- kurs:corellated_subquery_vs_pseudotable [2012/09/25 17:22]
127.0.0.1 external edit
+++ kurs:corellated_subquery_vs_pseudotable [2013/05/15 14:13]
mh
@@ Line 55: / Line 55: @@
        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>
+==== den Angestellten mit der niedrigsten id und gleichem Nachnamen ====
+<code>
+select employee_id,
+       last_name
+from   employees e
+where  employee_id =
+  (select min(employee_id) from employees where last_name = e.last_name)
+order by employee_id;
 </code>

dokuwiki