Zum Hauptinhalt springen

SQL - Fortgeschritten

Nachdem die SQL Grundlagen (Selektion, Filterung, einfache Verknüpfungen und Sortierungen) bekannt sind, tauchen wir nun tiefer in SQL ein. Für einen Informatik LK reicht es nicht aus, Daten nur auszulesen, wir müssen in der Lage sein, komplexe Analysen durchzuführen und die Datenbasis aktiv zu manipulieren.

Exkurs: Die SQL-Subsprachen​

Weitere SQL Teilsprachen sind DML (Data Manipulation Language) und DDL (Data Definition Language) 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.

Vorhandene Relationen (Tabellen)​

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

  • Schueler (S_ID, Name, Vorname, Geburtsdatum, Bezirk, ↑Tutor_ID)
  • Lehrer (L_ID, Kuerzel, Name, Vorname)
  • Fach (F_ID, Bezeichnung)
  • Kurs (K_ID, ↑F_ID, ↑L_ID, Kursart, Semester)
  • Belegung (↑S_ID, ↑K_ID, Note)
  • Abitur_Pruefung (↑S_ID, Pruefung, ↑F_ID, Note)
    (Pruefung: Gibt die an ob 1. LK, 2. LK, 3. schriftliche Prüfung, 4. mündlich 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​

In der Regel wird GROUP BY im Zusammenhang mit Aggregatfunktionen verwendet. 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 wir 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.

Training

  1. Erstelle eine Übersicht, welche die Durchschnittsnoten der einzelnen Abiturprüfungen angibt.

  2. Finde heraus, wie viele Kurse jeder Lehrer (identifiziert über L_ID, kein JOIN) im aktuellen Semester insgesamt anbietet.

  3. Die Schulleitung sucht Problem-Kurse. Ermittle alle Kurs-IDs, in denen der berechnete Notendurchschnitt schlechter als 10.0 Punkte ist. Geben zu den Kurs_IDs den Notendurchschnitt mit an.

  4. Welche Tutoren (nur Lehrer IDs) haben mehr als 10 Schüler.

Verschachtelte Abfragen (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.

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

Training

  1. Finde den Schüler / die Schülerin (Name, Vorname, Geburtsdatum), der/die an der gesamten Oberstufe am jüngsten ist. (Es soll wirklich nur ein/eine Schüler/Schüler ausgegeben werden.)

  2. 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?

JOINs, Mengen und Logik​

Dieser Abschnitt behandelt das Verbinden verschiedener Tabellen, das Zusammenführen von Ergebnismengen sowie den Einsatz von bedingter Logik, um komplexe Geschäftslogiken abzubilden.

Der einfache JOIN​

Ein Basis-JOIN (implizit immer ein INNER JOIN) verknüpft Datensätze aus zwei Tabellen anhand eines gemeinsamen Schlüssels. Zeilen, die in einer der beiden Tabellen keinen passenden Partner finden, werden im Ergebnis nicht angezeigt.

Training

  1. Zeige von Homer den Vornamen, den Nachnamen sowie alle Noten mit den zu gehörigen Kurs IDs.

  2. Liste das Kursangebot der Oberstufe auf (Bezeichnung des Faches, GK/LK) alphabetisch sortiert nach Fächernamen.

  3. Welche Lehrer, die mit 'H' oder 'N' beginnen unterrichten Leistungskurse. Nur Vor- und Nachname ohne Dopplungen angeben.

  4. Gib eine Liste alle Schüler (Vorname, Nachname) mit dem einfachen Notendurchschnitt aus (LK und GK gleich gewichtet). Sortiere absteigend nach dem Notendurchschnitt.

  5. Erstelle eine absteigend sortierte Übersicht über die durchschnittlichen Prüfungsergebnisse aller Abiturprüfungen (Bezeichnung, Pruefung, Durchschnittsnote).
    Hinweis: GROUP BY kann auch auf zwei Spalten gleichzeitig angewendet werden.

Mehrfache JOINs​

Um sinnvolle Zeugnisse oder Kurslisten zu erstellen, müssen oft mehr als zwei Tabellen verbunden werden. Das Prinzip von JOIN ... ON ... lässt sich beliebig aneinanderreihen.