Zum Hauptinhalt springen

SQL - Gruppierung & Subqueries

Nachdem die SQL Grundlagen (Selektion, Filterung, einfache Verknüpfungen und Sortierungen) bekannt sind, tauchen wir nun tiefer in SQL ein.

SQL ist ein mächtiges Werkzeugset, das klassischerweise in verschiedene Teilsprachen (Subsprachen) unterteilt wird. Auf dieser Seite fokussieren wir uns auf die DQL (Data Query Language), die ausschließlich dem Lesezugriff auf Daten dient (SELECT). Die zugrundeliegenden Tabellen werden dabei nicht verändert.

Miniwelt Oberstufe​

Wir arbeiten mit einer Datenbank, welche alle Schüler einer fiktiven Berliner Oberstufe abbildet. Es sind die unterrichtenden Lehrer, Fächer, Kurse, Belegungen und Abiturprüfungen hinterlegt:

  • Schueler (S_ID, Name, Vorname, Geburtsdatum, Bezirk, 🠙Tutor_ID)
    • 🠙Tutor_ID: Referenziert als Fremdschlüssel Lehrer.L_ID
  • Lehrer (L_ID, Kuerzel, Name, Vorname)
  • Fach (F_ID, Bezeichnung)
  • Kurs (K_ID, 🠙F_ID, 🠙L_ID, Kursart, Semester)
    • Kursart: LK oder GK; Semester: Q1 bis Q4
  • Belegung (🠙S_ID, 🠙K_ID, Note, Einbringung)
    • Note: 0–15 Punkte; Einbringung: Kurs wird für die Abiturnote berücksichtigt wenn 1, ansonsten nicht.
  • Abitur_Pruefung (🠙S_ID, Pruefung, 🠙F_ID, Note)
    • Pruefung: Gibt an, ob 1. LK, 2. LK, 3. schriftliche Prüfung, 4. mündliche Prüfung oder 5. PK

\

Relationen

Gruppierung und Subqueries​

In diesem Abschnitt fokussieren wir uns auf fortgeschrittene Aggregation und Verschachtelung von Abfragen.

GROUP BY und HAVING — Daten gruppieren ●○​

Bisher haben Aggregatfunktionen wie COUNT(), AVG() oder SUM() immer die gesamte Tabelle zusammengefasst. Oft wollen wir aber Teilgruppen analysieren, zum Beispiel die Anzahl der Schüler pro Bezirk. Hierfür wird GROUP BY genutzt.

Wenn wir diese gruppierten Daten filtern wollen, reicht WHERE nicht aus, da WHERE Zeilen filtert, bevor sie gruppiert werden. Um nach der Gruppierung zu filtern, nutzen wir HAVING. HAVING ist für Bedingungen auf Aggregaten wie MIN() oder AVG() gedacht.

Training
  1. Erstelle eine Übersicht, welche die Durchschnittsnoten der einzelnen Abiturprüfungen (1. LK, 2. LK, …) angibt.
  2. Finde heraus, wie viele Kurse jeder Lehrer (identifiziert über L_ID, kein JOIN) insgesamt anbietet.
  3. Die Schulleitung sucht Problem-Kurse. Ermittle alle Kurs-IDs, in denen der berechnete Notendurchschnitt schlechter als 10.0 Punkte ist. Gib zu den Kurs-IDs den Notendurchschnitt mit an.
  4. Welche Tutoren (nur Lehrer-IDs) haben mehr als 10 Schüler?
  5. Wie viele Schüler kommen pro Bezirk aus der Oberstufe, aber nur Bezirke mit mindestens 3 verschiedenen Schülern auflisten — absteigend nach Anzahl sortieren.

Skalare Subqueries ●○​

Eine Unterabfrage (Subquery) ist eine Abfrage innerhalb einer anderen Abfrage. Sie wird in der Regel in runden Klammern geschrieben und immer zuerst ausgeführt. Das Ergebnis der inneren Abfrage wird dann von der äußeren genutzt. Eine skalare Subquery liefert genau einen Wert zurück (eine Zelle).

Beispiel: Welche Schüler sind jünger als der älteste Schüler aus Neukölln?

Training
  1. Finde alle Schüler:innen (Name, Vorname), die mindestens eine Kursnote erhalten haben, die über dem Notendurchschnitt aller Kursnoten der gesamten Oberstufe liegt. (Hinweis: JOIN wird benötigt.)
  2. Welche Schüler sind älter als Lukas Müller (S_ID = 1)? Gib Vorname, Name und Geburtsdatum aus, sortiert nach Geburtsdatum.
  3. Thaddäus Tentakel (S_ID = 30) gilt nicht gerade als Überflieger. Welche Schüler (S_ID) haben einen schlechteren persönlichen Notendurchschnitt als Thaddäus?

IN und NOT IN ●○​

IN prüft, ob ein Wert in einer Werte-Liste enthalten ist. NOT IN kehrt die Bedingung um. Die Liste kann entweder direkt geschrieben sein oder von einer Subquery geliefert werden.

Variante 1 — Werte-Liste direkt: Kürzer als viele OR-Verknüpfungen.

Variante 2 — Liste aus Subquery: Sinnvoll, wenn die Liste sich aus den Daten ergibt. Beispiel: Welche Schüler haben in mindestens einem Leistungskurs die volle Punktzahl von 15 erreicht?

Training

Mit Werte-Liste:

  1. Welche Schüler kommen aus den Bezirken Neukölln, Friedrichshain-Kreuzberg oder Charlottenburg-Wilmersdorf? Gib Vorname, Name und Bezirk aus. Nutze IN mit einer Werte-Liste.
  2. Liste alle Schüler (Name,Vorname) mit Ihren Tutoren (Name) auf, die Tutanden von Frau Sommer, Dr. No oder Heinrich Hertz sind. Nutze IN mit einer Werte-Liste.

Mit Subquery:

  1. Welche Schüler (Vorname, Name) haben mindestens eine Abiturprüfung in Biologie (F_ID = 5) abgelegt? Nutze IN mit Subquery — ohne JOIN.
  2. Welche Lehrer (Vorname, Name) unterrichten keinen Leistungskurs ('LK')? Nutze NOT IN mit Subquery — ohne JOIN.
  3. Welche Lehrkräfte (Vorname, Name) unterrichten mindestens einen Kurs den Peter Scholze (S_ID = 50) einbringen wird? Ohne JOIN. Tipp: zwei geschachtelte INs.
  4. Welche Schüler haben in keinem ihrer Kurse jemals 15 Punkte erhalten? Nutze NOT IN mit Subquery.

EXISTS und NOT EXISTS ●●​

EXISTS prüft, ob eine Subquery mindestens eine Zeile zurückliefert, der konkrete Wert spielt keine Rolle. Häufig wird die innere Abfrage dabei mit der äußeren über eine Bedingung korreliert (verknüpft).

Beispiel: Welche Lehrer betreuen mindestens einen Leistungskurs?

Training
  1. Welche Lehrer sind keine Tutoren? Nutze NOT EXISTS.
  2. Welche Schüler haben in mindestens einem Kurs einen Ausfall (4 oder weniger Notenpunkte)? Nutze EXISTS.
  3. Welche Schüler haben keine Abiturprüfung im Fach Englisch (F_ID = 3) abgelegt? Nutze NOT EXISTS.
  4. Welche Lehrer unterrichten keinen Leistungskurs ('LK')? Verwende NOT EXISTS.

Oberstufen-Datenbank​

Die verwendete SQLite-Datenbank kann auch heruntergeladen werden und beispielsweise mit SQLite-Browser bearbeitet werden.