SQL Modul 05 – LEFT JOIN · GROUP BY

MODUL 05 — ERWEITERTE ABFRAGEN

SCHEMA — ERWEITERUNG AUS DER NORMALISIERUNG

In Modul 04 haben wir die Schulbibliothek normalisiert. Dabei entstand eine neue Tabelle: Ausleihposition. Ein Schüler kann jetzt mehrere Bücher auf einmal ausleihen — jedes Buch bekommt eine eigene Zeile in Ausleihposition.

Dieses Modul arbeitet mit diesem vollständigen Schema:

SCHUELER

SchuelerID	INTEGER · PK
Name	TEXT
Klasse	TEXT
Geburtsdatum	DATE

BUCH

BuchID	INTEGER · PK
Titel	TEXT
Autor	TEXT
Genre	TEXT
Erscheinungsjahr	INTEGER
Preis	REAL · NULL möglich

AUSLEIHE

AusleiheID	INTEGER · PK
SchuelerID	INTEGER · FK
DatumAusleihe	DATE
DatumRueckgabe	DATE · NULL möglich

AUSLEIHPOSITION

PositionID	INTEGER · PK
AusleiheID	INTEGER · FK
BuchID	INTEGER · FK

DATEN ERKUNDEN

ALLE TABELLEN ANSCHAUEN

Führe die Abfragen aus, um die Daten kennenzulernen:

SELECT * FROM Schueler;

SELECT * FROM Buch;

SELECT * FROM Ausleihe;

SELECT * FROM Ausleihposition;

O · LEFT JOIN100 XP

LEFT JOIN — ALLE ZEILEN DER LINKEN TABELLE

INNER JOIN zeigt nur Zeilen, bei denen auf beiden Seiten ein Treffer existiert. LEFT JOIN zeigt alle Zeilen der linken Tabelle — auch wenn es rechts keinen Treffer gibt. Fehlende Werte erscheinen als NULL.

SELECT spalten FROM linke_tabelle l LEFT JOIN rechte_tabelle r ON l.schluessel = r.schluessel;

Typischer Einsatz: „Zeige alle X — auch die ohne Y."

INNER JOIN vs. LEFT JOIN

Führe beide Abfragen aus und vergleiche die Anzahl der Ergebniszeilen:

VERGLEICH — INNER vs. LEFT 0 XP

-- INNER JOIN: nur Bücher die ausgeliehen wurden
SELECT b.Titel, ap.AusleiheID
FROM Buch b
INNER JOIN Ausleihposition ap ON b.BuchID = ap.BuchID;

-- LEFT JOIN: alle Bücher, auch nie ausgeliehene (NULL)
SELECT b.Titel, ap.AusleiheID
FROM Buch b
LEFT JOIN Ausleihposition ap ON b.BuchID = ap.BuchID;

IS NULL NACH LEFT JOIN

NULL ALS FILTER — NIE AUSGELIEHEN

Nach einem LEFT JOIN kann man mit WHERE … IS NULL gezielt die Zeilen herausfiltern, für die es keinen Treffer rechts gab:

SELECT b.Titel FROM Buch b LEFT JOIN Ausleihposition ap ON b.BuchID = ap.BuchID WHERE ap.AusleiheID IS NULL;

BEISPIEL — NIE AUSGELIEHENE BÜCHER 0 XP

Welche Bücher wurden noch nie ausgeliehen?

SELECT b.Titel, b.Genre
FROM Buch b
LEFT JOIN Ausleihposition ap ON b.BuchID = ap.BuchID
WHERE ap.AusleiheID IS NULL;

AUFGABE O1 — +10 XP +10 XP

Welche Schüler haben noch nie etwas ausgeliehen? Zeige Name und Klasse.

Tipp: FROM Schueler s LEFT JOIN Ausleihe a ON ... WHERE a.AusleiheID IS NULL

AUFGABE O2 — +15 XP +15 XP

Zeige alle Bücher mit ihrer AusleiheID — auch Bücher die nie ausgeliehen wurden. Sortiere so dass die nie ausgeliehenen zuerst erscheinen.

Tipp: LEFT JOIN + ORDER BY ap.AusleiheID — NULL sortiert nach oben.

VIER TABELLEN

BEISPIEL — VOLLSTÄNDIGE AUSLEIHLISTE 0 XP

Wer hat welches Buch ausgeliehen — alle vier Tabellen mit Aliasen:

SELECT s.Name, s.Klasse, b.Titel, a.DatumAusleihe
FROM Schueler s
INNER JOIN Ausleihe a ON s.SchuelerID = a.SchuelerID
INNER JOIN Ausleihposition ap ON a.AusleiheID = ap.AusleiheID
INNER JOIN Buch b ON ap.BuchID = b.BuchID
ORDER BY s.Name;

AUFGABE O3 — +20 XP +20 XP

Zeige alle Schüler mit Name, Klasse und Buchtitel — auch Schüler die nichts ausgeliehen haben sollen erscheinen (dann steht NULL beim Titel).

P · GROUP BY125 XP

GROUP BY — GRUPPEN BILDEN

In Modul 02 haben wir Aggregatfunktionen ohne GROUP BY genutzt — das Ergebnis war immer eine Zahl für die ganze Tabelle. Mit GROUP BY berechnet man die Aggregatfunktion pro Gruppe.

SELECT spalte, COUNT(*) AS Anzahl FROM tabelle GROUP BY spalte;

Wichtig: Im SELECT dürfen nur Spalten stehen die in GROUP BY genannt sind — oder Aggregatfunktionen.

BEISPIEL — GROUP BY OHNE JOIN 0 XP

Wie viele Bücher gibt es pro Genre?

SELECT b.Genre, COUNT(*) AS Anzahl
FROM Buch b
GROUP BY b.Genre;

AUFGABE P1 — +10 XP +10 XP

Welcher Durchschnittspreis gilt pro Genre? Runde nicht — zeige den rohen AVG-Wert.

Tipp: AVG(b.Preis) statt COUNT(*)

AUFGABE P2 — +15 XP +15 XP

Wie oft wurde jedes Buch ausgeliehen? Zeige Titel und Anzahl — häufigstes zuerst.

Tipp: FROM Buch b INNER JOIN Ausleihposition ap ON ... GROUP BY b.BuchID, b.Titel ORDER BY Anzahl DESC

KOMPLEXERE GRUPPEN

BEISPIEL — GROUP BY MIT ZWEI JOINS 0 XP

Wie viele Bücher hat jeder Schüler insgesamt ausgeliehen?

SELECT s.Name, COUNT(ap.BuchID) AS AnzahlBuecher
FROM Schueler s
INNER JOIN Ausleihe a ON s.SchuelerID = a.SchuelerID
INNER JOIN Ausleihposition ap ON a.AusleiheID = ap.AusleiheID
GROUP BY s.SchuelerID, s.Name
ORDER BY AnzahlBuecher DESC;

AUFGABE P3 — +20 XP +20 XP

Wie viele Ausleihen hat jede Klasse insgesamt?

Tipp: GROUP BY s.Klasse nach dem JOIN mit Ausleihe.

AUFGABE P4 — +25 XP +25 XP

Wie viele Bücher hat jeder Schüler insgesamt ausgeliehen — auch Schüler mit 0 Ausleihen sollen erscheinen. Meiste zuerst.

Tipp: Zwei LEFT JOINs — erst Ausleihe, dann Ausleihposition. Ohne LEFT fallen Schüler mit 0 raus.

Q · HAVING125 XP

HAVING — GRUPPEN FILTERN

WHERE filtert Zeilen — bevor gruppiert wird.
HAVING filtert Gruppen — nachdem gruppiert wurde.

SELECT spalte, COUNT(*) AS Anzahl FROM tabelle GROUP BY spalte HAVING COUNT(*) > 3;

Faustregel: Wenn die Bedingung eine Aggregatfunktion enthält (COUNT, AVG, …) → HAVING. Sonst → WHERE.

BEISPIEL — HAVING OHNE JOIN 0 XP

Welche Genres haben mehr als 3 Bücher?

SELECT b.Genre, COUNT(*) AS Anzahl
FROM Buch b
GROUP BY b.Genre
HAVING COUNT(*) > 3;

AUFGABE Q1 — +10 XP +10 XP

Welche Genres haben einen Durchschnittspreis über 12€? Zeige Genre und Durchschnitt.

Tipp: HAVING AVG(b.Preis) > 12

AUFGABE Q2 — +15 XP +15 XP

Welche Autoren haben mehr als ein Buch in der Bibliothek?

Tipp: GROUP BY b.Autor HAVING COUNT(*) > 1

HAVING MIT JOIN

BEISPIEL — HAVING MIT JOIN 0 XP

Welche Bücher wurden mehr als 2 Mal ausgeliehen?

SELECT b.Titel, COUNT(ap.PositionID) AS Anzahl
FROM Buch b
INNER JOIN Ausleihposition ap ON b.BuchID = ap.BuchID
GROUP BY b.BuchID, b.Titel
HAVING COUNT(ap.PositionID) > 2
ORDER BY Anzahl DESC;

AUFGABE Q3 — +20 XP +20 XP

Welche Klassen haben mehr als 5 Ausleihen insgesamt?

Tipp: JOIN mit Ausleihe, GROUP BY s.Klasse HAVING COUNT(a.AusleiheID) > 5

AUFGABE Q4 — +25 XP +25 XP

Welche Schüler haben mehr als 3 Bücher insgesamt ausgeliehen? Zeige Name und Anzahl, meiste zuerst.

Tipp: Zwei JOINs (Ausleihe + Ausleihposition), GROUP BY s.SchuelerID, s.Name HAVING COUNT(ap.BuchID) > 3