Differences

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

--- kurs:corellated_subquery_vs_pseudotable [2010/10/28 15:22]
mh created
+++ kurs:corellated_subquery_vs_pseudotable [2014/09/10 21:22] (current)
@@ 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