Aufgabe 17 - SQL Subqueries (Schul-Datenbank)

Aufgabe 17 - SQL Subqueries

Worum geht es?

In dieser Übung werden Abfragen ineinander geschachtelt: Eine Unterabfrage liefert dynamisch die Werte, mit denen die Hauptabfrage arbeitet (siehe Kapitel 9 - Subqueries). Im Expertenteil kommen korrelierte Subqueries dazu, die pro Zeile neu ausgewertet werden.

Was Sie dafür brauchen

• Kapitel 9 - Subqueries.
• Ein PostgreSQL-Server und ein SQL-Editor.

Welche Kompetenzen Sie erwerben und zeigen

• Sie setzen skalare Subqueries für dynamische Vergleiche ein.
• Sie filtern mit Table-Subqueries und dem IN-Operator über Tabellengrenzen.
• Sie schreiben korrelierte Subqueries mit EXISTS und NOT EXISTS.
• Sie lösen eine gruppenweise Prüfung, ohne Werte von Hand nachzuschlagen.

Pädagogische Einordnung

• Reproduktion: skalare Subqueries anwenden (Teil A).
• Reorganisation und Transfer: Table-Subqueries einsetzen (Teil B).
• Reflexion, Problemlösung und Urteilsbildung: korrelierte Subqueries entwickeln (Teile C und D).

Arbeitsaufträge

Die Übung ist auf etwa zwei Stunden ausgelegt. Teil D ist der Expertenteil.

Schema und Testdaten

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,'Miller','2A');
INSERT INTO classrooms VALUES ('R101','4A',2),('R102','4B',30),('R201','3A',25);

Teil A - Skalare Subqueries

1. Jüngster Schüler: alle Details des Schülers mit dem spätesten Geburtsdatum.
2. Über dem Durchschnitt: alle Schüler, die jünger als der Durchschnitt aller Schüler sind (Vorname, Nachname, Geburtsdatum).
3. Klassen-Vergleich: alle Schüler in derselben Klasse wie der Schüler mit ID 10, ohne dessen Klasse händisch nachzuschlagen.

Teil B - Table-Subqueries

1. Lehrer-Filter: alle Schüler in einer Klasse, die von 'Maurhart' oder 'Gabriel' unterrichtet wird (IN).
2. Jahrgangs-Filter: alle Klassen, in denen mindestens ein Schüler nach 2012 geboren wurde.
3. Verwaiste Klassen: alle Klassennamen aus classrooms, für die es keine Schüler in students gibt.

Teil C - Korrelierte Subqueries

1. Ältester je Klasse: je Klasse den Schüler mit dem frühesten Geburtsdatum, gelöst mit einer korrelierten Subquery (die innere Abfrage bezieht sich auf die Klasse der äußeren Zeile).
2. Existenzprüfung: alle Lehrer, die mindestens einen Schüler in ihrer Klasse haben (EXISTS), und alle Lehrer ohne einen einzigen Schüler (NOT EXISTS).

Teil D - Expertenteil: Kapazitätsprüfung über alle Klassen

1. Überbelegte Räume: Finden Sie alle Klassen, deren Schüleranzahl die max_seats ihres zugeordneten Raums überschreitet. Lösen Sie das mit einer korrelierten Subquery, die pro Raum die Schüleranzahl der zugehörigen Klasse zählt und mit max_seats vergleicht:

   SELECT c.class_name, c.max_seats,
          (SELECT count(*) FROM students s WHERE s.class_name = c.class_name) AS cnt
   FROM classrooms c
   WHERE (SELECT count(*) FROM students s WHERE s.class_name = c.class_name) > c.max_seats;

2. Auslastungsgrad: Erweitern Sie die Abfrage um eine Spalte, die den Auslastungsgrad in Prozent zeigt (cnt * 100.0 / max_seats), und sortieren Sie absteigend.

3. Reflexion: Erklären Sie in drei bis vier Sätzen den Unterschied zwischen einer korrelierten und einer nicht korrelierten Subquery und warum die korrelierte hier notwendig ist. Nennen Sie außerdem, in welcher Reihenfolge (innere oder äußere Abfrage zuerst) eine korrelierte Subquery gedanklich ausgewertet wird.

Wissenscheck

1. Was passiert, wenn eine mit = verbundene Subquery zwei Zeilen zurückgibt?
2. Wann ist eine Subquery in WHERE einfacher zu lesen als ein Join?
3. Welcher Teil einer korrelierten Subquery wird zuerst ausgewertet, der innere oder der äußere?
4. Worin unterscheidet sich eine korrelierte von einer nicht korrelierten Subquery?
5. Warum lässt sich die Kapazitätsprüfung über alle Räume nur mit einer korrelierten Subquery (oder einem Join mit Gruppierung) lösen?

Abgabe

• Die Datei aufgabe17_subqueries.sql mit allen Abfragen, fehlerfrei ausführbar, inklusive der Reflexion aus Teil D als Kommentar.

---

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