Subqueries – Unterabfragen in Oracle SQL

Fach: Informationstechnologie – Datenbanksysteme
Schulstufe: 11. Schulstufe – HTL Informatik
Voraussetzungen: DQL – SELECT, WHERE, GROUP BY, Joins
Autor: HTL Pinkafeld – IF/IT

1. Was sind Subqueries?

1.1 Definition

Eine Subquery (auch: Unterabfrage, verschachtelte Abfrage) ist eine SELECT-Anweisung, die innerhalb einer anderen SQL-Anweisung steht. Die innere Abfrage wird zuerst ausgeführt, ihr Ergebnis wird dann von der äußeren Abfrage verwendet.

SELECT ENAME, SAL
FROM   EMP
WHERE  SAL > (SELECT AVG(SAL) FROM EMP);
--            ──────────────────────────
--                  Subquery (innere Abfrage)
-- ──────────────────────────────────────────
--             Hauptabfrage (äußere Abfrage)

1.2 Warum Subqueries?

Manche Fragen lassen sich nur mit Subqueries elegant beantworten:

"Wer verdient mehr als der Durchschnitt?"
→ Durchschnitt ist erst nach dem Lesen aller Zeilen bekannt
→ Subquery berechnet Durchschnitt zuerst, Hauptabfrage filtert dann

"Welche Mitarbeiter arbeiten in derselben Abteilung wie SCOTT?"
→ Abteilung von SCOTT ist erst nach dem Lesen der EMP-Zeile bekannt
→ Subquery ermittelt die Abteilung, Hauptabfrage filtert

1.3 Einordnung

Subquery-Typen nach Ergebnis:
├── Einzeilig (Single-Row):   gibt genau 1 Zeile zurück  → =, <, >, <=, >=, <>
├── Mehrzeilig (Multi-Row):   gibt mehrere Zeilen zurück → IN, NOT IN, ANY, ALL
└── Mehrspaltig (Multi-Col):  gibt mehrere Spalten zurück → (col1, col2) IN (...)

Subquery-Typen nach Position:
├── In WHERE:         häufigste Verwendung
├── In HAVING:        Gruppen mit Aggregaten filtern
├── In FROM:          Inline View (temporäre Tabelle)
├── In SELECT:        skalare Subquery (ein Wert pro Zeile)
└── Korreliert:       referenziert Spalte der äußeren Abfrage

1.4 Syntaxregeln

-- Subquery immer in runde Klammern
WHERE SAL > (SELECT AVG(SAL) FROM EMP)

-- Subquery hat eigenes ORDER BY nur mit FETCH / ROWNUM
-- (einfaches ORDER BY in Subquery ist sinnlos und meist verboten)

-- Subquery kann selbst wieder Subqueries enthalten (Verschachtelung)
WHERE DEPTNO = (SELECT DEPTNO
                FROM   EMP
                WHERE  ENAME = 'SCOTT')

2. Einfache Subquery in WHERE

2.1 Einzeilige Subquery mit =

Die häufigste Anwendung: Ein konkreter Wert wird durch eine Subquery ermittelt.

-- Wer verdient genau so viel wie SCOTT?
SELECT ENAME, SAL
FROM   EMP
WHERE  SAL = (SELECT SAL
              FROM   EMP
              WHERE  ENAME = 'SCOTT');

-- Ergebnis:
-- ENAME  SAL
-- SCOTT  3000
-- FORD   3000

-- Wer arbeitet in derselben Abteilung wie JONES?
SELECT ENAME, DEPTNO
FROM   EMP
WHERE  DEPTNO = (SELECT DEPTNO
                 FROM   EMP
                 WHERE  ENAME = 'JONES')
  AND  ENAME <> 'JONES';

2.2 Einzeilige Subquery mit Aggregatfunktion

-- Mitarbeiter die mehr als den Durchschnitt verdienen
SELECT ENAME, SAL
FROM   EMP
WHERE  SAL > (SELECT AVG(SAL) FROM EMP)
ORDER BY SAL DESC;

-- Ergebnis:
-- ENAME   SAL
-- KING   5000
-- FORD   3000
-- SCOTT  3000
-- JONES  2975
-- BLAKE  2850
-- CLARK  2450

-- Frühest eingestellter Mitarbeiter
SELECT ENAME, HIREDATE
FROM   EMP
WHERE  HIREDATE = (SELECT MIN(HIREDATE) FROM EMP);
-- SMITH   17.12.1980

2.3 Einzeilige Subquery mit mehreren Klauseln

-- Mitarbeiter die mehr verdienen als der Durchschnitt ihrer eigenen Abteilung
-- (Vorgriff auf korrelierte Subquery – hier mit Subquery im WHERE)

-- Erst: Durchschnittsgehalt der Abteilung 20
SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 20;   -- 2175

-- Dann: Mitarbeiter der Abteilung 20 über diesem Wert
SELECT ENAME, SAL
FROM   EMP
WHERE  DEPTNO = 20
  AND  SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 20);

2.4 Fehler bei einzeiligen Subqueries

-- FEHLER: Subquery gibt mehrere Zeilen zurück, = erwartet genau eine!
SELECT ENAME FROM EMP
WHERE  SAL = (SELECT SAL FROM EMP WHERE JOB = 'MANAGER');
-- ORA-01427: single-row subquery returns more than one row

-- LÖSUNG: IN statt = verwenden (siehe Kapitel 4)
SELECT ENAME FROM EMP
WHERE  SAL IN (SELECT SAL FROM EMP WHERE JOB = 'MANAGER');

-- FEHLER: Subquery gibt keine Zeile zurück
SELECT ENAME FROM EMP
WHERE  SAL = (SELECT SAL FROM EMP WHERE ENAME = 'NIEMAND');
-- Kein Fehler, aber auch kein Ergebnis (NULL-Vergleich)

3. Subquery mit Vergleichsoperatoren

3.1 Alle Vergleichsoperatoren

Einzeilige Subqueries können mit allen Vergleichsoperatoren kombiniert werden:

-- Mehr als der am wenigsten verdienende MANAGER
SELECT ENAME, SAL, JOB
FROM   EMP
WHERE  SAL > (SELECT MIN(SAL)
              FROM   EMP
              WHERE  JOB = 'MANAGER');

-- Weniger als der am meisten verdienende CLERK
SELECT ENAME, SAL
FROM   EMP
WHERE  SAL < (SELECT MAX(SAL)
              FROM   EMP
              WHERE  JOB = 'CLERK');

-- Eingestellt nach dem letzten SALESMAN
SELECT ENAME, HIREDATE, JOB
FROM   EMP
WHERE  HIREDATE > (SELECT MAX(HIREDATE)
                   FROM   EMP
                   WHERE  JOB = 'SALESMAN')
ORDER BY HIREDATE;

3.2 Ungleich (<>)

-- Mitarbeiter in einer anderen Abteilung als KING
SELECT ENAME, DEPTNO
FROM   EMP
WHERE  DEPTNO <> (SELECT DEPTNO
                  FROM   EMP
                  WHERE  ENAME = 'KING');

4. Mehrwertige Subqueries – IN und NOT IN

4.1 IN mit Subquery

Wenn die Subquery mehrere Zeilen zurückgibt, muss IN statt = verwendet werden:

-- Mitarbeiter die in Abteilungen mit Standort DALLAS oder CHICAGO arbeiten
SELECT ENAME, DEPTNO
FROM   EMP
WHERE  DEPTNO IN (SELECT DEPTNO
                  FROM   DEPT
                  WHERE  LOC IN ('DALLAS', 'CHICAGO'));

-- Mitarbeiter die denselben Beruf haben wie jemand aus Abteilung 10
SELECT ENAME, JOB
FROM   EMP
WHERE  JOB IN (SELECT JOB
               FROM   EMP
               WHERE  DEPTNO = 10)
  AND  DEPTNO <> 10;

4.2 NOT IN mit Subquery

-- Mitarbeiter die KEINEN Vorgesetzten aus Abteilung 10 haben
SELECT e.ENAME, e.MGR
FROM   EMP e
WHERE  e.MGR NOT IN (SELECT EMPNO
                     FROM   EMP
                     WHERE  DEPTNO = 10);

⚠️ Falle mit NOT IN und NULL:

-- GEFÄHRLICH: Wenn die Subquery NULL-Werte enthält, liefert NOT IN immer leer!
SELECT ENAME FROM EMP
WHERE  EMPNO NOT IN (SELECT MGR FROM EMP);
-- MGR enthält NULL (KING hat keinen Vorgesetzten)
-- NOT IN mit NULL → kein Ergebnis!

-- LÖSUNG: NULL-Werte in der Subquery ausschließen
SELECT ENAME FROM EMP
WHERE  EMPNO NOT IN (SELECT MGR FROM EMP WHERE MGR IS NOT NULL);

4.3 Mehrspaltiger IN-Vergleich

Oracle erlaubt den Vergleich mehrerer Spalten gleichzeitig:

-- Mitarbeiter mit demselben Beruf UND Gehalt wie ein Mitarbeiter aus Abt. 30
SELECT ENAME, JOB, SAL
FROM   EMP
WHERE  (JOB, SAL) IN (SELECT JOB, SAL
                      FROM   EMP
                      WHERE  DEPTNO = 30);

5. ANY und ALL Operatoren

5.1 ANY – mindestens eine Bedingung erfüllt

> ANY bedeutet: größer als mindestens einen Wert der Subquery (= größer als das Minimum):

-- Mehr verdienen als IRGENDEIN CLERK
SELECT ENAME, SAL, JOB
FROM   EMP
WHERE  SAL > ANY (SELECT SAL
                  FROM   EMP
                  WHERE  JOB = 'CLERK')
  AND  JOB <> 'CLERK';

-- Äquivalent zu:
WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE JOB = 'CLERK')

ANY-Äquivalente:

5.2 ALL – alle Bedingungen erfüllt

> ALL bedeutet: größer als alle Werte der Subquery (= größer als das Maximum):

-- Mehr verdienen als ALLE MANAGER
SELECT ENAME, SAL, JOB
FROM   EMP
WHERE  SAL > ALL (SELECT SAL
                  FROM   EMP
                  WHERE  JOB = 'MANAGER');

-- Äquivalent zu:
WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE JOB = 'MANAGER')
-- Ergebnis: nur KING (5000 > 2975)

ALL-Äquivalente:

5.3 Gegenüberstellung ANY / ALL

-- Gehaltsklasse CLERK: 800, 950, 1100, 1300

-- > ANY: größer als mindestens einer → > 800 (Minimum)
WHERE SAL > ANY (SELECT SAL FROM EMP WHERE JOB = 'CLERK')

-- > ALL: größer als alle → > 1300 (Maximum)
WHERE SAL > ALL (SELECT SAL FROM EMP WHERE JOB = 'CLERK')

6. Subquery in der HAVING-Klausel

HAVING filtert Gruppen – Subqueries in HAVING vergleichen Aggregatwerte mit berechneten Referenzwerten:

-- Abteilungen deren Durchschnittsgehalt höher ist als das Gesamtdurchschnitt
SELECT DEPTNO,
       ROUND(AVG(SAL), 2) AS avg_gehalt
FROM   EMP
GROUP BY DEPTNO
HAVING AVG(SAL) > (SELECT AVG(SAL) FROM EMP)
ORDER BY avg_gehalt DESC;

-- Ergebnis:
-- DEPTNO  AVG_GEHALT
-- ------  ----------
--     10     2916.67
--     20     2175.00
-- (Abt. 30 mit 1566.67 liegt unter dem Gesamtdurchschnitt von 2073.21)

-- Berufe mit mehr Mitarbeitern als der Durchschnitt aller Berufsgruppen
SELECT JOB, COUNT(*) AS anzahl
FROM   EMP
GROUP BY JOB
HAVING COUNT(*) > (SELECT AVG(cnt)
                   FROM   (SELECT COUNT(*) AS cnt
                           FROM   EMP
                           GROUP BY JOB))
ORDER BY anzahl DESC;

7. Korrelierte Subqueries

7.1 Was ist eine korrelierte Subquery?

Bei einer korrelierten Subquery referenziert die innere Abfrage eine Spalte der äußeren Abfrage. Die Subquery wird dadurch für jede Zeile der äußeren Abfrage neu ausgeführt:

-- Mitarbeiter die mehr verdienen als der Durchschnitt ihrer eigenen Abteilung
SELECT e.ENAME, e.DEPTNO, e.SAL
FROM   EMP e
WHERE  e.SAL > (SELECT AVG(i.SAL)
                FROM   EMP i
                WHERE  i.DEPTNO = e.DEPTNO)  -- e.DEPTNO: Verweis auf äußere Abfrage!
ORDER BY e.DEPTNO, e.SAL DESC;

Ausführungsablauf:

Für jede Zeile in EMP (äußere Abfrage):
  1. Lies e.DEPTNO dieser Zeile (z.B. 20)
  2. Führe Subquery aus: SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 20
  3. Vergleiche e.SAL mit dem Ergebnis
  4. Zeile ausgeben wenn Bedingung wahr

7.2 Weiteres Beispiel: Rang in der Abteilung

-- Mitarbeiter die das höchste Gehalt in ihrer Abteilung haben
SELECT e.ENAME, e.DEPTNO, e.SAL
FROM   EMP e
WHERE  e.SAL = (SELECT MAX(i.SAL)
                FROM   EMP i
                WHERE  i.DEPTNO = e.DEPTNO)
ORDER BY e.DEPTNO;

-- Ergebnis:
-- ENAME  DEPTNO  SAL
-- KING       10  5000
-- FORD       20  3000
-- SCOTT      20  3000
-- BLAKE      30  2850

7.3 Korrelierte Subquery mit Aggregat

-- Für jeden Mitarbeiter: Durchschnittsgehalt der Abteilung als Zusatzspalte
-- (besser als Inline View gelöst – hier als korrelierte Variante)
SELECT e.ENAME,
       e.SAL,
       e.DEPTNO,
       (SELECT ROUND(AVG(i.SAL), 2)
        FROM   EMP i
        WHERE  i.DEPTNO = e.DEPTNO) AS abt_durchschnitt
FROM   EMP e
ORDER BY e.DEPTNO, e.SAL DESC;

8. EXISTS und NOT EXISTS

8.1 EXISTS – Existenzprüfung

EXISTS prüft ob die Subquery mindestens eine Zeile zurückgibt. Sobald eine Zeile gefunden wird, bricht Oracle die Suche ab (effizient!):

-- Abteilungen die mindestens einen Mitarbeiter haben
SELECT d.DEPTNO, d.DNAME
FROM   DEPT d
WHERE  EXISTS (SELECT 1
               FROM   EMP e
               WHERE  e.DEPTNO = d.DEPTNO);

-- Ergebnis: 10, 20, 30 (Abt. 40 hat keine Mitarbeiter)

Konvention: In der Subquery von EXISTS schreibt man oft SELECT 1 oder SELECT NULL statt SELECT * – die konkreten Spaltenwerte interessieren nicht, nur ob Zeilen existieren.

8.2 NOT EXISTS

-- Abteilungen ohne Mitarbeiter
SELECT d.DEPTNO, d.DNAME
FROM   DEPT d
WHERE  NOT EXISTS (SELECT 1
                   FROM   EMP e
                   WHERE  e.DEPTNO = d.DEPTNO);

-- Ergebnis: 40   OPERATIONS

8.3 EXISTS vs. IN – der Unterschied

-- Beide liefern dasselbe Ergebnis:
-- Variante 1: IN
SELECT ENAME FROM EMP
WHERE  DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS');

-- Variante 2: EXISTS
SELECT e.ENAME FROM EMP e
WHERE  EXISTS (SELECT 1
               FROM   DEPT d
               WHERE  d.DEPTNO = e.DEPTNO
                 AND  d.LOC = 'DALLAS');

Wann welches?

Faustregel: Bei NOT IN immer überlegen ob NOT EXISTS die sicherere Wahl ist – wegen des NULL-Problems.

9. Subquery in der FROM-Klausel – Inline View

9.1 Was ist ein Inline View?

Eine Subquery in der FROM-Klausel verhält sich wie eine temporäre Tabelle – sie existiert nur für die Dauer der Abfrage:

-- Inline View: Durchschnittsgehalt pro Abteilung als "Tabelle"
SELECT e.ENAME,
       e.SAL,
       avg_abt.durchschnitt,
       e.SAL - avg_abt.durchschnitt AS differenz
FROM   EMP e
       JOIN (SELECT DEPTNO,
                    ROUND(AVG(SAL), 2) AS durchschnitt
             FROM   EMP
             GROUP BY DEPTNO)   avg_abt
         ON e.DEPTNO = avg_abt.DEPTNO
ORDER BY e.DEPTNO, differenz DESC;

9.2 Top-N-Abfragen mit ROWNUM

Eine klassische Oracle-Anwendung von Inline Views: ROWNUM kann erst nach dem Sortieren angewendet werden, weil ROWNUM vor ORDER BY vergeben wird:

-- FALSCH: ROWNUM wird vor ORDER BY vergeben!
SELECT ENAME, SAL
FROM   EMP
WHERE  ROWNUM <= 3
ORDER BY SAL DESC;
-- Gibt 3 beliebige Zeilen, dann sortiert – nicht die 3 höchsten!

-- RICHTIG: Zuerst sortieren (Inline View), dann ROWNUM
SELECT ENAME, SAL
FROM   (SELECT ENAME, SAL
        FROM   EMP
        ORDER BY SAL DESC)
WHERE  ROWNUM <= 3;

-- Ergebnis: Die 3 am besten bezahlten Mitarbeiter
-- KING   5000
-- FORD   3000
-- SCOTT  3000

-- Top-5 Mitarbeiter nach Gehalt mit Rang
SELECT ROWNUM AS rang, ENAME, SAL
FROM   (SELECT ENAME, SAL
        FROM   EMP
        ORDER BY SAL DESC)
WHERE  ROWNUM <= 5;

-- Ab Oracle 12c: eleganter mit FETCH FIRST
SELECT ENAME, SAL
FROM   EMP
ORDER BY SAL DESC
FETCH FIRST 5 ROWS ONLY;

9.3 Komplexe Aggregationen mit Inline View

-- Abteilung mit dem höchsten Durchschnittsgehalt
SELECT *
FROM   (SELECT DEPTNO,
               ROUND(AVG(SAL), 2) AS avg_sal
        FROM   EMP
        GROUP BY DEPTNO
        ORDER BY avg_sal DESC)
WHERE  ROWNUM = 1;

-- Mitarbeiteranzahl pro Gehaltsklasse und Abteilung
SELECT d.DNAME, sub.grade, sub.anzahl
FROM   DEPT d
       JOIN (SELECT e.DEPTNO,
                    s.GRADE,
                    COUNT(*) AS anzahl
             FROM   EMP e
                    JOIN SALGRADE s ON e.SAL BETWEEN s.LOSAL AND s.HISAL
             GROUP BY e.DEPTNO, s.GRADE) sub
         ON d.DEPTNO = sub.DEPTNO
ORDER BY d.DNAME, sub.grade;

10. Subquery in der SELECT-Liste

10.1 Skalare Subquery

Eine skalare Subquery in der SELECT-Liste gibt genau einen Wert pro Zeile zurück:

-- Für jeden Mitarbeiter: Durchschnittsgehalt der gesamten Firma als Vergleich
SELECT ENAME,
       SAL,
       (SELECT ROUND(AVG(SAL), 0) FROM EMP) AS firma_durchschnitt,
       SAL - (SELECT ROUND(AVG(SAL), 0) FROM EMP) AS differenz
FROM   EMP
ORDER BY differenz DESC;

-- Anzahl Mitarbeiter der Abteilung als zusätzliche Spalte
SELECT e.ENAME,
       e.DEPTNO,
       e.SAL,
       (SELECT COUNT(*)
        FROM   EMP i
        WHERE  i.DEPTNO = e.DEPTNO) AS kollegen_in_abt
FROM   EMP e
ORDER BY e.DEPTNO, e.ENAME;

10.2 Einschränkungen

-- Skalare Subquery MUSS genau 1 Zeile zurückgeben
-- Mehrere Zeilen → ORA-01427
-- Keine Zeilen → NULL (kein Fehler)

-- Skalare Subquery mit mehr als einer Spalte → ORA-00913
SELECT ENAME,
       (SELECT ENAME, SAL FROM EMP WHERE EMPNO = 7839)   -- FEHLER!
FROM   EMP;

11. Subqueries vs. Joins

11.1 Wann was verwenden?

Viele Abfragen können sowohl mit Subquery als auch mit Join gelöst werden:

-- AUFGABE: Name und Abteilungsname aller Mitarbeiter aus DALLAS

-- Variante 1: JOIN
SELECT e.ENAME, d.DNAME
FROM   EMP e JOIN DEPT d ON e.DEPTNO = d.DEPTNO
WHERE  d.LOC = 'DALLAS';

-- Variante 2: Subquery
SELECT ENAME
FROM   EMP
WHERE  DEPTNO IN (SELECT DEPTNO
                  FROM   DEPT
                  WHERE  LOC = 'DALLAS');
-- Nachteil: DNAME nicht direkt verfügbar (nur mit Subquery in SELECT-Liste)

11.2 Entscheidungshilfe

11.3 Performance-Hinweis

-- Subquery wird für jede Zeile einmal ausgeführt (unkorrelliert: einmal gesamt)
-- Korrelierte Subquery wird für JEDE Zeile der äußeren Abfrage ausgeführt!
-- Bei großen Tabellen: Inline View oder JOIN oft effizienter

-- Langsam (korrelierte Subquery, n Ausführungen):
WHERE SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = e.DEPTNO)

-- Schneller (Inline View, 1 Ausführung):
FROM EMP e JOIN (SELECT DEPTNO, AVG(SAL) AS avg_sal
                 FROM EMP GROUP BY DEPTNO) a
           ON e.DEPTNO = a.DEPTNO
WHERE e.SAL > a.avg_sal

12. Zusammenfassung und Ausblick

Überblick aller Subquery-Varianten

-- Einzeilige Subquery in WHERE (= < > <= >= <>)
WHERE SAL > (SELECT AVG(SAL) FROM EMP)

-- Mehrwertige Subquery in WHERE (IN / NOT IN)
WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS')

-- ANY / ALL
WHERE SAL > ANY (SELECT SAL FROM EMP WHERE JOB = 'CLERK')
WHERE SAL > ALL (SELECT SAL FROM EMP WHERE JOB = 'MANAGER')

-- Subquery in HAVING
HAVING AVG(SAL) > (SELECT AVG(SAL) FROM EMP)

-- Korrelierte Subquery
WHERE SAL = (SELECT MAX(SAL) FROM EMP i WHERE i.DEPTNO = e.DEPTNO)

-- EXISTS / NOT EXISTS
WHERE EXISTS     (SELECT 1 FROM EMP e WHERE e.DEPTNO = d.DEPTNO)
WHERE NOT EXISTS (SELECT 1 FROM EMP e WHERE e.DEPTNO = d.DEPTNO)

-- Inline View (Subquery in FROM)
FROM (SELECT DEPTNO, AVG(SAL) AS avg_sal FROM EMP GROUP BY DEPTNO) a

-- Skalare Subquery in SELECT
SELECT ENAME, (SELECT COUNT(*) FROM EMP i WHERE i.DEPTNO = e.DEPTNO)

Checkliste

Subquery-Konzept und Ausführungsreihenfolge erklären
Einzeilige Subquery mit = und Aggregatfunktionen
Fehler ORA-01427 (mehrere Zeilen statt einer) beheben
Mehrwertige Subquery mit IN / NOT IN
NULL-Falle bei NOT IN kennen und vermeiden
ANY und ALL erklären und anwenden
Subquery in HAVING
Korrelierte Subquery: Unterschied zu unkorrellierter
EXISTS und NOT EXISTS: Existenzprüfung
Inline View: Top-N-Abfrage mit ROWNUM korrekt lösen
Skalare Subquery in SELECT-Liste
Subquery vs. Join: wann was?

Typische Prüfungsaufgaben

-- 1. Alle Mitarbeiter die mehr verdienen als BLAKE
SELECT ENAME, SAL
FROM   EMP
WHERE  SAL > (SELECT SAL FROM EMP WHERE ENAME = 'BLAKE')
ORDER BY SAL DESC;

-- 2. Mitarbeiter mit dem niedrigsten Gehalt pro Abteilung
SELECT ENAME, DEPTNO, SAL
FROM   EMP e
WHERE  SAL = (SELECT MIN(SAL) FROM EMP i WHERE i.DEPTNO = e.DEPTNO)
ORDER BY DEPTNO;

-- 3. Abteilungen ohne Mitarbeiter (zwei Varianten)
-- Variante A: NOT IN
SELECT DEPTNO, DNAME FROM DEPT
WHERE  DEPTNO NOT IN (SELECT DEPTNO FROM EMP WHERE DEPTNO IS NOT NULL);

-- Variante B: NOT EXISTS
SELECT DEPTNO, DNAME FROM DEPT d
WHERE  NOT EXISTS (SELECT 1 FROM EMP e WHERE e.DEPTNO = d.DEPTNO);

-- 4. Die 3 am längsten beschäftigten Mitarbeiter
SELECT ENAME, HIREDATE
FROM   (SELECT ENAME, HIREDATE FROM EMP ORDER BY HIREDATE ASC)
WHERE  ROWNUM <= 3;

-- 5. Mitarbeiter die mehr als der Durchschnitt ihrer Abteilung verdienen
SELECT e.ENAME, e.DEPTNO, e.SAL,
       ROUND(a.avg_sal, 2) AS abt_durchschnitt
FROM   EMP e
       JOIN (SELECT DEPTNO, AVG(SAL) AS avg_sal
             FROM   EMP
             GROUP BY DEPTNO) a ON e.DEPTNO = a.DEPTNO
WHERE  e.SAL > a.avg_sal
ORDER BY e.DEPTNO, e.SAL DESC;

Ausblick: Nächste Themen

• DML: INSERT, UPDATE, DELETE, MERGE
• DDL: CREATE TABLE, ALTER TABLE, Constraints
• Views: Virtuelle Tabellen mit CREATE VIEW
• PL/SQL: Prozedurale Erweiterung, Schleifen, Bedingungen, Cursor

HTL Pinkafeld – IF/IT | Oracle SQL | 11. Schulstufe