SQL Modul 06 – Subqueries & Shop

R · SUBQUERIES200 XP

SUBQUERY — ABFRAGE IN DER ABFRAGE

Eine Subquery ist eine SELECT-Anweisung innerhalb einer anderen. Sie steht in Klammern und liefert entweder eine Liste von Werten (für IN/NOT IN) oder einen einzelnen Wert (skalare Subquery).

SELECT spalten FROM tabelle WHERE spalte IN ( SELECT spalte FROM andere_tabelle WHERE bedingung );

Vorteil gegenüber JOIN: manchmal lesbarer, besonders bei NOT IN und EXISTS.

BEISPIEL — IN 0 XP

Welche Schüler haben mindestens eine Ausleihe?

SELECT s.Name, s.Klasse
FROM Schueler s
WHERE s.SchuelerID IN (
    SELECT a.SchuelerID FROM Ausleihe a
);

AUFGABE R1 — +10 XP +10 XP

Welche Bücher wurden schon mindestens einmal ausgeliehen? Zeige Titel und Genre.

Tipp: WHERE b.BuchID IN (SELECT ap.BuchID FROM Ausleihposition ap)

AUFGABE R2 — +15 XP +15 XP

Welche Schüler haben das Buch „Tschick" ausgeliehen? Zeige Name und Klasse.

NOT IN

NOT IN — AUSSCHLIESSEN

NOT IN kehrt das Ergebnis um — es werden nur Zeilen angezeigt, die nicht in der Subquery-Liste vorkommen. Entspricht dem LEFT JOIN + IS NULL Muster aus Block O.

SELECT b.Titel FROM Buch b WHERE b.BuchID NOT IN ( SELECT ap.BuchID FROM Ausleihposition ap );

⚠ Achtung: Wenn die Subquery NULL-Werte liefert, gibt NOT IN keine Ergebnisse zurück. Bei möglichen NULLs besser NOT EXISTS verwenden.

BEISPIEL — NOT IN 0 XP

Welche Bücher wurden nie ausgeliehen?

SELECT b.Titel, b.Genre
FROM Buch b
WHERE b.BuchID NOT IN (
    SELECT ap.BuchID FROM Ausleihposition ap
);

AUFGABE R3 — +10 XP +10 XP

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

Tipp: WHERE s.SchuelerID NOT IN (SELECT a.SchuelerID FROM Ausleihe a)

AUFGABE R4 — +15 XP +15 XP

Welche Bücher aus dem Genre „Fantasy" wurden noch nie ausgeliehen?

Tipp: WHERE b.Genre = 'Fantasy' AND b.BuchID NOT IN (...) — WHERE und NOT IN kombinieren.

EXISTS / NOT EXISTS

EXISTS — KORRELIERTE SUBQUERY

Bei EXISTS wird die Subquery für jede Zeile der äußeren Abfrage neu ausgeführt — sie kann auf die äußere Tabelle zugreifen. Das nennt man korrelierte Subquery.

SELECT s.Name FROM Schueler s WHERE EXISTS ( SELECT 1 FROM Ausleihe a WHERE a.SchuelerID = s.SchuelerID -- Bezug zur äußeren Tabelle AND a.DatumRueckgabe IS NULL );

SELECT 1 — der Rückgabewert ist egal, nur ob Zeilen existieren zählt. NOT EXISTS kehrt das Ergebnis um.

BEISPIEL — EXISTS 0 XP

Welche Schüler haben aktuell ein Buch ausgeliehen (noch nicht zurück)?

SELECT s.Name, s.Klasse
FROM Schueler s
WHERE EXISTS (
    SELECT 1 FROM Ausleihe a
    WHERE a.SchuelerID = s.SchuelerID
    AND a.DatumRueckgabe IS NULL
);

AUFGABE R5 — +15 XP +15 XP

Welche Bücher sind aktuell ausgeliehen (in einer offenen Ausleihe, DatumRueckgabe IS NULL)? Zeige Titel und Genre.

Tipp: Subquery verknüpft Ausleihposition und Ausleihe, Bezug zur äußeren Tabelle: WHERE ap.BuchID = b.BuchID

AUFGABE R6 — +20 XP +20 XP

Welche Schüler haben kein Buch aktuell ausgeliehen? Zeige Name und Klasse.

Tipp: NOT EXISTS statt EXISTS — sonst identisch zu Beispiel.

SKALARE SUBQUERY

SKALARE SUBQUERY — EIN WERT ALS VERGLEICH

Eine skalare Subquery liefert genau einen Wert — z.B. einen Durchschnitt — der dann als Vergleichswert in WHERE verwendet wird.

SELECT b.Titel, b.Preis FROM Buch b WHERE b.Preis > (SELECT AVG(Preis) FROM Buch);

AUFGABE R7 — +20 XP +20 XP

Welche Bücher kosten mehr als der Durchschnittspreis aller Bücher? Zeige Titel und Preis, teuerste zuerst.

Tipp: WHERE b.Preis > (SELECT AVG(Preis) FROM Buch)

S · SHOP-ÜBUNGEN395 XP

NEUE DATENBANK — ONLINESHOP

Keine Erklärungen, keine Beispiele — nur Aufgaben. Nutze das Schema und dein gesamtes SQL-Wissen. Hinweise kosten −5 XP.

KUNDE

KundeID	INTEGER · PK
Name	TEXT
Strasse	TEXT
PLZ	TEXT
Stadt	TEXT

KATEGORIE

KategorieID	INTEGER · PK
Bezeichnung	TEXT

ARTIKEL

ArtikelID	INTEGER · PK
Bezeichnung	TEXT
Preis	REAL
KategorieID	INTEGER · FK

LIEFERANT

LieferantID	INTEGER · PK
Name	TEXT
Strasse	TEXT
PLZ	TEXT
Stadt	TEXT

ARTIKELLIEFERANT

ArtikelID	INTEGER · FK
LieferantID	INTEGER · FK

BESTELLUNG

BestellungID	INTEGER · PK
KundeID	INTEGER · FK
Datum	DATE

BESTELLPOSITION

PositionID	INTEGER · PK
BestellungID	INTEGER · FK
ArtikelID	INTEGER · FK
Anzahl	INTEGER

DATEN ERKUNDEN

TABELLEN ANSCHAUEN

SELECT * FROM Artikel;

SELECT * FROM Bestellung;

SELECT * FROM Bestellposition;

MITTELSCHWER

S1 — +15 XP +15 XP

Welche Artikel gehören zur Kategorie „Elektronik"? Zeige Bezeichnung und Preis.

S2 — +15 XP +15 XP

Welche Kunden haben noch keine Bestellung aufgegeben? Zeige Name und Stadt.

S3 — +20 XP +20 XP

Wie viele Bestellungen hat jeder Kunde aufgegeben? Auch Kunden mit 0 Bestellungen anzeigen. Meiste zuerst.

S4 — +20 XP +20 XP

Welche Artikel wurden noch nie bestellt?

S5 — +20 XP +20 XP

Wie viel hat jede Bestellung insgesamt gekostet? Berechne die Summe aus Anzahl × Preis pro Position. Zeige BestellungID und Gesamtbetrag, teuerste zuerst.

Tipp: SUM(bp.Anzahl * a.Preis) — JOIN Bestellposition mit Artikel, GROUP BY BestellungID.

SCHWER

S6 — +25 XP +25 XP

Welche Kategorien haben einen Durchschnittspreis über 50 €? Zeige Bezeichnung und Durchschnitt.

S7 — +25 XP +25 XP

Welche Kunden haben mehr als 2 verschiedene Artikel bestellt (gezählt über alle Bestellungen)?

Tipp: COUNT(DISTINCT bp.ArtikelID) — DISTINCT damit jeder Artikel nur einmal zählt, auch wenn mehrfach bestellt.

S8 — +25 XP +25 XP

Welche Artikel kosten mehr als der Durchschnittspreis ihrer eigenen Kategorie? Zeige Bezeichnung, Preis und Kategorie.

Tipp: Korrelierte Subquery — WHERE a2.KategorieID = a.KategorieID bezieht sich auf die äußere Tabelle.

S9 — +30 XP +30 XP

Welche Lieferanten liefern ausschließlich Artikel aus einer einzigen Kategorie? Zeige Lieferantenname und die Kategorie.

Tipp: HAVING COUNT(DISTINCT a.KategorieID) = 1 — genau eine Kategorie über alle gelieferten Artikel.

S10 — +30 XP +30 XP

Welche Kunden haben im Jahr 2025 für mehr als 200 € bestellt (Summe über alle ihre 2025-Bestellungen)?

Tipp: WHERE für Jahr 2025 (LIKE '2025%'), HAVING für Umsatzschwelle — beides zusammen.

KOMPLEX

S11 — +30 XP +30 XP

Umsatz pro Kunde über alle Bestellungen. Nur Kunden mit Gesamtumsatz über 100 € anzeigen. Höchster Umsatz zuerst.

S12 — +30 XP +30 XP

Umsatz pro Kategorie über alle Bestellungen. Welche Kategorie hat den höchsten Gesamtumsatz? Alle Kategorien anzeigen, höchste zuerst.

S13 — +35 XP +35 XP

Welche Artikel aus der Kategorie „Elektronik" wurden insgesamt mehr als 3 Mal bestellt (Anzahl summiert über alle Bestellungen)?

S14 — +35 XP +35 XP

Welche Kunden haben im Jahr 2025 mindestens eine Bestellung aufgegeben, aber keinen Artikel aus der Kategorie „Lebensmittel" bestellt?

S15 — +40 XP +40 XP

Welche Lieferanten liefern Artikel, die zusammen einen Gesamtumsatz über 500 € erzielt haben? Zeige Lieferantenname und Umsatz.

DIESES MODUL

SCHEMA BLOCK R — SCHULBIBLIOTHEK (bekannt)

SCHEMA BLOCK S — ONLINESHOP (neu)

SUBQUERY — ABFRAGE IN DER ABFRAGE

NOT IN — AUSSCHLIESSEN

EXISTS — KORRELIERTE SUBQUERY

SKALARE SUBQUERY — EIN WERT ALS VERGLEICH

NEUE DATENBANK — ONLINESHOP

ABSCHLUSS