Aufgabe 17 - SQL Subqueries (Schul-Datenbank)

Aufgabe 17 - SQL Subqueries

Worum geht es?

In dieser Übung schachteln Sie Abfragen ineinander: Eine Unterabfrage (Subquery) liefert dynamisch die Werte, mit denen die Hauptabfrage arbeitet (siehe Kapitel 9 - Subqueries). So bleiben Abfragen flexibel, auch wenn sich die Daten ständig ändern.

In dieser Übung üben Sie:

• Verstehen: den Unterschied zwischen Hauptabfrage und Unterabfrage erfassen.
• Anwenden: skalare Subqueries für dynamische Vergleiche einsetzen.
• Nutzen: Table-Subqueries mit dem IN-Operator zur Filterung über Tabellengrenzen hinweg verwenden.
• Verknüpfen: Daten logisch ohne expliziten JOIN zusammenführen.

Szenario: Analyse der Schulverwaltung

Die Direktion benötigt spezifische Auswertungen über die Schüler- und Klassenstruktur. Da sich die Daten (wie Geburtsdaten oder Klassenzugehörigkeiten) ständig ändern, sollen die Abfragen dynamisch gestaltet werden. Das bedeutet: Anstatt feste Werte (wie ein fixes Datum) in die WHERE-Klausel zu schreiben, soll die Datenbank diese Werte mittels Subqueries selbst ermitteln.

CREATE TABLE students (
    student_id   INT PRIMARY KEY,
    first_name   VARCHAR(50),
    last_name    VARCHAR(50),
    birth_date   DATE,
    class_name   VARCHAR(10)
);

CREATE TABLE teachers (
    teacher_id   INT PRIMARY KEY,
    teacher_name VARCHAR(50),
    class_name   VARCHAR(10)
);

CREATE TABLE classrooms (
    room_id      VARCHAR(10) PRIMARY KEY,
    class_name   VARCHAR(10),
    max_seats    INT
);

INSERT INTO students VALUES
(1, 'Alice', 'Adelson', '2010-05-15', '4A'),
(2, 'Bob', 'Burger', '2011-02-20', '4B'),
(3, 'Charlie', 'Check', '2012-11-30', '4A'),
(4, 'Daisy', 'Duck', '2013-01-10', '3A'),
(10, 'Felix', 'Fix', '2010-12-17', '4A');

INSERT INTO teachers VALUES
(101, 'Maurhart', '4A'),
(102, 'Gabriel', '4B'),
(103, 'Steindl', '3A'),
(104, 'Müller', '2A');

INSERT INTO classrooms VALUES
('R101', '4A', 2),
('R102', '4B', 30),
('R201', '3A', 25);

Arbeitsaufträge (SQL-Abfragen)

Teil A: Skalare Subqueries (Einzelergebnisse)

1. Der jüngste Schüler: Lassen Sie sich alle Details (*) des Schülers anzeigen, der als Letztes geboren wurde (das maximale Geburtsdatum hat).
2. Über dem Durchschnitt: Ermitteln Sie alle Schüler (Vorname, Nachname, Geburtsdatum), die jünger als der Durchschnitt aller Schüler sind.
3. Klassen-Vergleich: Zeigen Sie alle Schüler an, die in derselben Klasse sind wie der Schüler mit der ID 10 (dessen Klasse Sie jedoch nicht händisch nachschlagen dürfen).

Teil B: Table Subqueries (Listen & Mengen)

4. Lehrer-Filter: Finden Sie alle Schüler (Vorname, Nachname), die in einer Klasse sind, welche von einem Lehrer mit dem Nachnamen ‘Maurhart’ oder ‘Gabriel’ unterrichtet wird.
5. Exklusive Gruppen: Listen Sie alle Klassen auf, in denen mindestens ein Schüler registriert ist, der nach dem Jahr 2012 geboren wurde.
6. Verwaiste Klassen: Identifizieren Sie alle Klassennamen aus einer (hypothetischen) Liste von Räumen, für die es aktuell keine Einträge in der students-Tabelle gibt.

Teil C: Experten-Herausforderung

7. Die Kapazitäts-Prüfung: Angenommen, Sie haben eine Tabelle classrooms mit einer Spalte max_seats. Finden Sie alle Klassen, deren Schüleranzahl (Count) die max_seats des Raums ‘R101’ überschreitet.

Abgabe & Kriterien

• Abgabe der Datei aufgabe17_subqueries.sql.
• Alle Abfragen müssen auf einer PostgreSQL-Datenbank fehlerfrei ausführbar sein.
• Konsistente Nutzung von Großbuchstaben für SQL-Keywords und saubere Formatierung.

---

Wissens-Check

• Frage 1: Was passiert, wenn eine Subquery, die mit einem = Operator verbunden ist, plötzlich zwei Zeilen statt einer zurückgibt?
• Frage 2: Warum ist eine Subquery in der WHERE-Klausel oft einfacher zu lesen als ein JOIN, wenn man nur prüfen möchte, ob ein Datensatz existiert, aber keine Spalten aus der zweiten Tabelle im Ergebnis benötigt?
• Frage 3: Erklären Sie den Begriff “Execution Order”: Welcher Teil der Abfrage wird technisch gesehen zuerst ausgeführt - die innere oder die äußere?

---

HTL Villach, Schuljahr 2025-2026,
https://www.htl-villach.at