Übungsaufgaben zu SQL-Unterabfragen und Mengenoperationen

Übungsaufgaben zu SQL-Unterabfragen und Mengenoperationen

https://bildung.social/@oerinformatik/

https://oer-informatik.de/sql-unterabfragen-uebungsaufgaben

SQL, Unterabfragen, Korrelierte Unterabfrage, EXISTS, UNION, INTERSECT, EXCEPT, MariaDB, Übungsaufgaben, Prüfungsvorbereitung

tl/dr; (Bearbeitungszeit insgesamt 149 min, mehrere Aufgaben): Vier Übungsaufgaben zu Unterabfragen und Mengenoperationen, zwei davon für den Einstieg – Vergleich mit berechneten Werten, IN, NOT IN mit NULL-Falle, EXISTS, korrelierte Unterabfragen, Prozentanteile, ALL, abgeleitete Tabellen, UPDATE und DELETE mit Unterabfrage, Archivieren, Merkmalstabellen, typische Fehlerbilder sowie UNION, UNION ALL, INTERSECT und EXCEPT. Alle Lösungen sind ausgeblendet und mit Punkteverteilung versehen [AI-Assisted]. (Zuletzt geändert am 28.10.2026)

Diese Übungsaufgaben gehören zum Artikel SQL: Unterabfragen und Mengenoperationen. Sie setzen die Artikel SQL: Aggregierende Funktionen und SQL: Abfragen über mehrere Tabellen mit JOIN voraus.

Hinweise zur Bearbeitung: Als FIA oder FIDP musst Du zur Teil-2-Prüfung Aufgaben auf Kernkompetenz-Ebene lösen können, das sind Aufgabe 2.1 und Aufgabe 2.2. Von FIA wird zusätzlich erwartet, dass ihnen Aufgaben auf Profilkompetenz-Ebene (Aufgabe 3.1 und Aufgabe 3.2) keine Probleme bereiten. Schreib Deine Lösung zuerst vollständig auf, bevor Du die Beispiellösung aufklappst. Die SQL-Anweisungen sind in MariaDB-Notation formuliert; andere korrekte Lösungen sind ebenfalls richtig (zum Beispiel ein JOIN statt einer Unterabfrage, sofern die Aufgabe nichts anderes verlangt). In den Datenmodellen sind Primärschlüssel unterstrichen und Fremdschlüssel mit # gekennzeichnet. In den Aufgaben 3.1 und 3.2 ist heute Montag, der 19.10.2026 (CURDATE()). Zu jeder Aufgabe gibt es Tabellenauszüge; am Ende findest Du außerdem eine SQL-Kurzreferenz.

Aufgabe 2.1: Räume und Sessions eines Barcamps (Kernkompetenz)

(ca. 29 min Bearbeitungszeit)

Am Ratz und Rübe Berufskolleg (RRB) in Bielefeld findet am 13.03.2027 das Barcamp „Lernen.Digital.Leben 2027“ statt. Die Sessionplanung läuft über eine MariaDB-Datenbank. Dimitrios Papadopoulos, Gesamtleitung der Organisation, bittet Dich um einige Auswertungen. Die Datenbank enthält unter anderem:

• raum (raum_id, bezeichnung, name, gebaeude, etage, plaetze, beamer, stream_faehig, barrierefrei)

• session (session_id, #veranstaltung_id, titel, format, status, eingereicht_am, max_plaetze, #raum_id, #slot_id, livestream)

• session_buchung (#session_id, #anmeldung_id, gebucht_am)

session.status kann eingereicht, angenommen, abgelehnt oder zurueckgezogen sein. raum_id und slot_id einer Session sind NULL, solange ihr noch kein Raum bzw. Zeitfenster zugeordnet ist; max_plaetze ist NULL, wenn die Platzzahl nicht begrenzt ist. barrierefrei ist ein Wahrheitswert (TRUE/FALSE).

a) Für Sessions mit erwartet großem Andrang sucht Dimitrios die großen Räume. Erstelle eine SQL-Abfrage, die Bezeichnung, Name und Platzzahl aller Räume ausgibt, die mehr Plätze haben als der Durchschnitt aller Räume. Die größten Räume sollen oben stehen. Gib das Ergebnis an. (5 Punkte)

SELECT bezeichnung, name, plaetze
FROM raum
WHERE plaetze > (SELECT AVG(plaetze) FROM raum)
ORDER BY plaetze DESC;

b) Hannah Klein, Schülerin im Orga-Team, möchte alle Räume finden, in denen noch keine Session geplant ist. Ihre Abfrage liefert ein leeres Ergebnis:

SELECT bezeichnung, name
FROM raum
WHERE raum_id NOT IN (SELECT raum_id FROM session);

ba) Erläutere, warum das Ergebnis leer ist. (3 Punkte)

bb) Korrigiere die Abfrage so, dass sie alle Räume liefert, denen keine angenommene Session zugeordnet ist, sortiert nach Bezeichnung. Gib das Ergebnis an. (4 Punkte)

SELECT bezeichnung, name, plaetze
FROM raum
WHERE raum_id NOT IN (SELECT raum_id
                      FROM session
                      WHERE status = 'angenommen'
                        AND raum_id IS NOT NULL)
ORDER BY bezeichnung;

SELECT r.bezeichnung, r.name, r.plaetze
FROM raum AS r
WHERE NOT EXISTS (SELECT *
                  FROM session AS s
                  WHERE s.raum_id = r.raum_id
                    AND s.status = 'angenommen')
ORDER BY r.bezeichnung;

c) Hannah hat es inzwischen mit einem Verbund versucht – wieder ohne Ergebnis:

SELECT r.bezeichnung
FROM raum AS r
INNER JOIN session AS s ON s.raum_id = r.raum_id
WHERE s.session_id IS NULL;

Erläutere den Fehler und gib an, wie die Abfrage mit einem Verbund richtig lautet. (4 Punkte)

SELECT r.bezeichnung
FROM raum AS r
LEFT JOIN session AS s ON s.raum_id = r.raum_id
WHERE s.session_id IS NULL
ORDER BY r.bezeichnung;

d) Erstelle eine SQL-Abfrage, die Titel und Platzbegrenzung des Workshops (bzw. der Workshops) mit der größten Platzbegrenzung ausgibt. Begründe, warum die Bedingung auf das Format sowohl in der äußeren Abfrage als auch in der Unterabfrage stehen muss. (5 Punkte)

SELECT titel, max_plaetze
FROM session
WHERE format = 'Workshop'
  AND max_plaetze = (SELECT MAX(max_plaetze)
                     FROM session
                     WHERE format = 'Workshop');

e) Gerhard Vogt, Referent des Reparatur-Workshops, ist auf einen ebenerdigen bzw. per Aufzug erreichbaren Raum angewiesen. Alle angenommenen Sessions, die bisher in einem nicht barrierefreien Raum geplant sind, sollen deshalb in den Raum A.104 verlegt werden. Erstelle eine SQL-Anweisung, die das erledigt, ohne Schlüsselwerte als Zahl zu verwenden. (5 Punkte)

UPDATE session
SET raum_id = (SELECT raum_id FROM raum WHERE bezeichnung = 'A.104')
WHERE status = 'angenommen'
  AND raum_id IN (SELECT raum_id FROM raum WHERE barrierefrei = FALSE);

f) Für einen Zwischenbericht sollen alle Buchungen vom 12.10. bis einschließlich 16.10.2026 mit Datum, Sessiontitel und Anmeldenummer aufgelistet werden, sortiert zuerst nach Datum und dann nach Sessiontitel. Erstelle die SQL-Abfrage und erkläre, worauf Du bei der Bedingung achten musst, weil gebucht_am auch eine Uhrzeit enthält. (6 Punkte)

SELECT DATE(b.gebucht_am) AS tag, s.titel, b.anmeldung_id
FROM session_buchung AS b
JOIN session AS s ON s.session_id = b.session_id
WHERE b.gebucht_am >= '2026-10-12'
  AND b.gebucht_am <  '2026-10-17'
ORDER BY tag, s.titel;

Punkte gesamt: 32 (a: 5, b: 7, c: 4, d: 5, e: 5, f: 6)

Aufgabe 2.2: Technische Merkmale im Fahrrad-Onlineshop (Kernkompetenz)

(ca. 25 min Bearbeitungszeit)

Die CSB-Rad GmbH in Bielefeld verkauft Fahrräder und Komponenten in einem Onlineshop. Für einen Produktvergleich speichert Geschäftsführerin Sophie Bertram technische Merkmale (Akkukapazität, Reichweite, Zuladung …) nicht als eigene Spalten, sondern in einer Merkmalstabelle. Du erstellst die Abfragen für die Vergleichsseite.

• kategorie (kategorie_id, bezeichnung, #ober_kategorie_id)

• artikel (artikel_id, artikelnr, bezeichnung, #kategorie_id, netto_preis, lagerbestand, aktiv, …)

• merkmal (merkmal_id, bezeichnung, einheit)

• artikel_merkmal (#artikel_id, #merkmal_id, wert)

a) Erstelle eine Liste aller Artikel mit einer Akkukapazität von mindestens 500 Wh mit Artikelnummer, Bezeichnung, Akkukapazität und Nettopreis. Sortiere so, dass die Artikel mit der größten Akkukapazität oben stehen und bei gleicher Kapazität der günstigere Artikel zuerst kommt. Wähle das Merkmal über seine Bezeichnung aus, nicht über die merkmal_id. Gib das Ergebnis an. (6 Punkte)

SELECT a.artikelnr, a.bezeichnung, am.wert AS akku_wh, a.netto_preis
FROM artikel AS a
JOIN artikel_merkmal AS am ON am.artikel_id = a.artikel_id
WHERE am.merkmal_id = (SELECT merkmal_id
                       FROM merkmal
                       WHERE bezeichnung = 'Akkukapazität')
  AND am.wert >= 500
ORDER BY am.wert DESC, a.netto_preis ASC;

b) Erstelle eine Liste aller Artikel mit der geringsten Zuladung (merkmal_id 3) mit Bezeichnung und Zuladung. Gib das Ergebnis an und erläutere, warum eine Lösung mit ORDER BY wert LIMIT 1 hier nicht ausreicht. (5 Punkte)

SELECT a.bezeichnung, am.wert AS zuladung_kg
FROM artikel AS a
JOIN artikel_merkmal AS am ON am.artikel_id = a.artikel_id
WHERE am.merkmal_id = 3
  AND am.wert = (SELECT MIN(wert)
                 FROM artikel_merkmal
                 WHERE merkmal_id = 3);

c) Für die Pflege der Vergleichsseite braucht Sophie eine Liste aller Artikel mit Kategorie, zu denen noch keine Merkmale erfasst sind, sortiert nach Kategorie und Artikelbezeichnung. Erstelle die SQL-Abfrage und gib das Ergebnis an. (5 Punkte)

SELECT k.bezeichnung AS kategorie, a.bezeichnung AS artikel
FROM artikel AS a
JOIN kategorie AS k ON k.kategorie_id = a.kategorie_id
WHERE a.artikel_id NOT IN (SELECT artikel_id FROM artikel_merkmal)
ORDER BY k.bezeichnung, a.bezeichnung;

d) Erstelle eine Liste der aktiven Artikel, deren Nettopreis über dem Durchschnittspreis aller aktiven Artikel liegt, mit Artikelnummer, Bezeichnung und Nettopreis, den teuersten zuerst. (4 Punkte)

SELECT artikelnr, bezeichnung, netto_preis
FROM artikel
WHERE aktiv = TRUE
  AND netto_preis > (SELECT AVG(netto_preis)
                     FROM artikel
                     WHERE aktiv = TRUE)
ORDER BY netto_preis DESC;

e) Wegen gestiegener Einkaufspreise sollen alle Artikel der Kategorie „Schlösser“ um 4 % teurer werden (auf zwei Nachkommastellen gerundet). Erstelle die SQL-Anweisung, ohne die kategorie_id als Zahl zu verwenden, und gib die neuen Preise an. (4 Punkte)

UPDATE artikel
SET netto_preis = ROUND(netto_preis * 1.04, 2)
WHERE kategorie_id IN (SELECT kategorie_id
                       FROM kategorie
                       WHERE bezeichnung = 'Schlösser');

f) Natalia Wróbel aus dem Controlling wollte die Reichweiten aller Artikel sehen. Ihre Abfrage liefert dreimal das E-Bike „Kanal“ mit den Werten 500, 120 und 130:

SELECT a.bezeichnung, m.bezeichnung, am.wert
FROM artikel AS a
JOIN artikel_merkmal AS am ON am.artikel_id = a.artikel_id
JOIN merkmal AS m ON m.merkmal_id = am.artikel_id
WHERE m.bezeichnung = 'Reichweite';

Erläutere den Fehler und korrigiere die Abfrage. (3 Punkte)

Punkte gesamt: 27 (a: 6, b: 5, c: 5, d: 4, e: 4, f: 3)

Die Tabellen merkmal und artikel_merkmal mit diesen Daten stehen in src/aufgabe4_merkmale.sql; sie ergänzen den Datenbestand der CSB-Rad GmbH.

Aufgabe 3.1: Auswertungen und Datenpflege in einer Freizeit-App (Profilkompetenz)

(ca. 51 min Bearbeitungszeit)

Du bist Auszubildende*r bei der Let’s Meet GmbH in Bielefeld. Die App „Let’s Meet“ bringt Menschen ab 16 Jahren für gemeinsame Freizeitaktivitäten an öffentlichen Treffpunkten zusammen. Dein Ausbilder Yusuf Demir hat Dir mehrere Wünsche aus dem Team auf den Tisch gelegt: UX-Designerin Anh Nguyen arbeitet am Filter „barrierefreier Treffpunkt“, Geschäftsführerin Clara Neumann braucht Kennzahlen, und die Datenschutzbeauftragte Dr. Marta Kowalczyk möchte, dass nicht mehr benötigte Daten gelöscht werden. Dein Mit-Azubi Jonas Weber hat bereits einige Abfragen vorbereitet.

Die Datenbank enthält unter anderem folgende Tabellen:

• benutzer (benutzer_id, vorname, nachname, email, geburtsdatum, plz, ort, status, …)

• hobby (hobby_id, bezeichnung, kategorie)

• treffpunkt (treffpunkt_id, name, strasse, plz, ort, barrierefrei, oeffentlich)

• aktivitaet (aktivitaet_id, titel, #hobby_id, #ersteller_id, #treffpunkt_id, beginn, dauer_min, max_teilnehmende, kosten_eur, mindestalter, status)

• teilnahme (#aktivitaet_id, #benutzer_id, angemeldet_am, status)

• bewertung (bewertung_id, #aktivitaet_id, #bewerter_id, sterne, kommentar)

• meldung (meldung_id, #melder_id, #gemeldet_id, #aktivitaet_id, grund, ergebnis)

teilnahme.status kann die Werte angemeldet, warteliste, abgemeldet, teilgenommen und nicht_erschienen haben, aktivitaet.status die Werte geplant, abgesagt und stattgefunden. In meldung ist aktivitaet_id optional: Meldungen, die sich nicht auf eine bestimmte Aktivität beziehen (zum Beispiel Spam im Chat), haben dort NULL. barrierefrei ist ein Wahrheitswert (TRUE/FALSE).

Bewertungen gibt es bisher zu den Aktivitäten 1 (5 und 4 Sterne), 2 (5 und 4 Sterne) und 8 (5 und 4 Sterne). In der Tabelle hobby gehören die Hobbys 6 (Brettspiele) und 19 (Schach) zur Kategorie Spiele; zur Kategorie Kultur (Kino, Konzerte, Chor, Lesen) gibt es noch keine Aktivität.

a) Clara Neumann möchte wissen, welche Aktivitäten mehr kosten als der Brettspielabend (aktivitaet_id 1). Gib Titel, Beginn und Kostenbeitrag dieser Aktivitäten an, die teuerste zuerst. Der Kostenbeitrag des Brettspielabends darf nicht als Zahl in Deiner SQL-Anweisung stehen, weil er sich ändern kann. (4 Punkte)

SELECT titel, beginn, kosten_eur
FROM aktivitaet
WHERE kosten_eur > (SELECT kosten_eur
                    FROM aktivitaet
                    WHERE aktivitaet_id = 1)
ORDER BY kosten_eur DESC;

b) Anh Nguyen möchte die Personen befragen, die schon einmal für eine Aktivität an einem barrierefreien Treffpunkt eingetragen waren – egal mit welchem Teilnahmestatus.

ba) Erstelle eine SQL-Abfrage, die Vorname und Nachname dieser Personen ausgibt, sortiert nach Nachname und Vorname. Verwende ausschließlich Unterabfragen, keinen JOIN. (5 Punkte)

bb) Jonas meint, mit einem JOIN über benutzer, teilnahme, aktivitaet und treffpunkt gehe es genauso. Begründe, warum seine Liste ohne DISTINCT Personen mehrfach enthielte, und nenne am Beispiel einer Person aus den Tabellen, wie oft sie erschiene. (2 Punkte)

SELECT vorname, nachname
FROM benutzer
WHERE benutzer_id IN (
        SELECT benutzer_id
        FROM teilnahme
        WHERE aktivitaet_id IN (
                SELECT aktivitaet_id
                FROM aktivitaet
                WHERE treffpunkt_id IN (
                        SELECT treffpunkt_id
                        FROM treffpunkt
                        WHERE barrierefrei = TRUE)))
ORDER BY nachname, vorname;

c) Für eine Infobox in der App hat Jonas folgende Abfrage geschrieben:

SELECT titel
FROM aktivitaet
WHERE treffpunkt_id = (SELECT treffpunkt_id
                       FROM treffpunkt
                       WHERE barrierefrei = TRUE);

MariaDB bricht mit der Meldung Subquery returns more than 1 row ab. Erläutere die Meldung und korrigiere die Abfrage. (3 Punkte)

SELECT titel
FROM aktivitaet
WHERE treffpunkt_id IN (SELECT treffpunkt_id
                        FROM treffpunkt
                        WHERE barrierefrei = TRUE);

d) Dr. Kowalczyk möchte wissen, zu welchen Aktivitäten noch nie eine Meldung eingegangen ist. Jonas hat dazu geschrieben:

SELECT aktivitaet_id, titel
FROM aktivitaet
WHERE aktivitaet_id NOT IN (SELECT aktivitaet_id FROM meldung);

Die Abfrage läuft fehlerfrei, liefert aber keine einzige Zeile.

da) Erläutere, warum das Ergebnis leer ist. (3 Punkte)

db) Erstelle eine korrigierte Abfrage mit NOT EXISTS und gib das Ergebnis an. (3 Punkte)

SELECT a.aktivitaet_id, a.titel
FROM aktivitaet AS a
WHERE NOT EXISTS (SELECT *
                  FROM meldung AS m
                  WHERE m.aktivitaet_id = a.aktivitaet_id);

e) Für das Team-Dashboard soll eine Übersicht aller nicht abgesagten Aktivitäten entstehen:

angemeldet zählt die Teilnahmen mit Status angemeldet oder teilgenommen, warteliste die mit Status warteliste, sterne_schnitt ist der auf eine Nachkommastelle gerundete Durchschnitt der Bewertungen.

ea) Erstelle die SQL-Abfrage mit Unterabfragen in der SELECT-Liste, sortiert nach aktivitaet_id. (7 Punkte)

eb) Jonas hat dieselbe Übersicht mit zwei LEFT JOINs versucht:

SELECT a.aktivitaet_id, COUNT(t.benutzer_id) AS angemeldet,
       ROUND(AVG(b.sterne), 1) AS sterne_schnitt
FROM aktivitaet AS a
  LEFT JOIN teilnahme AS t ON t.aktivitaet_id = a.aktivitaet_id
                          AND t.status IN ('angemeldet', 'teilgenommen')
  LEFT JOIN bewertung AS b ON b.aktivitaet_id = a.aktivitaet_id
WHERE a.status <> 'abgesagt'
GROUP BY a.aktivitaet_id;

Beim Brettspielabend erscheinen 8 statt 4 Angemeldete. Erläutere, wie diese Zahl zustande kommt. (2 Punkte)

ec) In Deinem Ergebnis aus ea) steht für den Feierabend-Kick bei warteliste eine 0, bei sterne_schnitt aber NULL. Erläutere den Unterschied. (2 Punkte)

SELECT a.aktivitaet_id,
       a.titel,
       a.max_teilnehmende,
       (SELECT COUNT(*)
          FROM teilnahme AS t
         WHERE t.aktivitaet_id = a.aktivitaet_id
           AND t.status IN ('angemeldet', 'teilgenommen')) AS angemeldet,
       (SELECT COUNT(*)
          FROM teilnahme AS t
         WHERE t.aktivitaet_id = a.aktivitaet_id
           AND t.status = 'warteliste') AS warteliste,
       (SELECT ROUND(AVG(b.sterne), 1)
          FROM bewertung AS b
         WHERE b.aktivitaet_id = a.aktivitaet_id) AS sterne_schnitt
FROM aktivitaet AS a
WHERE a.status <> 'abgesagt'
ORDER BY a.aktivitaet_id;

f) Auf der Startseite sollen geplante Aktivitäten mit freien Plätzen beworben werden. Erstelle eine SQL-Abfrage, die alle geplanten Aktivitäten mit einer Auslastung unter 50 % ausgibt (aktivitaet_id, titel, auslastung_prozent), aufsteigend nach Auslastung. Die Auslastung ist der Anteil der Teilnahmen mit Status angemeldet an max_teilnehmende in Prozent, gerundet auf eine Nachkommastelle. Beachte, dass ein Spaltenalias in der WHERE-Klausel derselben Abfrage nicht verwendet werden kann. (6 Punkte)

SELECT aktivitaet_id, titel, auslastung_prozent
FROM (SELECT a.aktivitaet_id,
             a.titel,
             ROUND((SELECT COUNT(*)
                      FROM teilnahme AS t
                     WHERE t.aktivitaet_id = a.aktivitaet_id
                       AND t.status = 'angemeldet')
                   * 100 / a.max_teilnehmende, 1) AS auslastung_prozent
        FROM aktivitaet AS a
       WHERE a.status = 'geplant') AS uebersicht
WHERE auslastung_prozent < 50
ORDER BY auslastung_prozent;

g) Clara Neumann möchte für eine Präsentation wissen, wie sich alle Teilnahmen auf die verschiedenen Status verteilen. Ermittle mit einer SQL-Abfrage je Status die Anzahl und den Anteil an allen Teilnahmen in Prozent (eine Nachkommastelle), den größten Anteil zuerst, und gib das Ergebnis an. (5 Punkte)

SELECT status,
       COUNT(*) AS anzahl,
       ROUND(COUNT(*) * 100 / (SELECT COUNT(*) FROM teilnahme), 1) AS anteil_prozent
FROM teilnahme
GROUP BY status
ORDER BY anteil_prozent DESC;

h) Clara möchte außerdem wissen, welche Aktivitäten länger dauern als jede Aktivität einer bestimmten Hobby-Kategorie. Jonas hat für die Kategorie Spiele geschrieben:

SELECT titel, dauer_min
FROM aktivitaet
WHERE dauer_min > ALL (SELECT dauer_min
                       FROM aktivitaet
                       WHERE hobby_id IN (SELECT hobby_id
                                          FROM hobby
                                          WHERE kategorie = 'Spiele'));

ha) Ermittle das Ergebnis der Abfrage. (2 Punkte)

hb) Yusuf schlägt vor, statt > ALL (…) den Vergleich > (SELECT MAX(dauer_min) …) zu schreiben. Erläutere, für welche Kategorie aus den Tabellen sich die beiden Varianten unterschiedlich verhalten, und gib jeweils das Ergebnis an. (2 Punkte)

i) Durch einen Fehler in der alten App-Version stehen Personen auf der Warteliste, obwohl noch Plätze frei sind.

ia) Erstelle eine SQL-Anweisung, die bei allen geplanten Aktivitäten die Teilnahmen mit Status warteliste auf angemeldet setzt, sofern weniger Teilnahmen mit Status angemeldet vorliegen als max_teilnehmende. Gib an, welche Teilnahme betroffen ist. (5 Punkte)

ib) Nimm Stellung, ob Deine Anweisung auch dann richtig arbeitet, wenn bei einer Aktivität mit nur einem freien Platz zwei Personen auf der Warteliste stehen. (2 Punkte)

UPDATE teilnahme AS t
SET status = 'angemeldet'
WHERE t.status = 'warteliste'
  AND t.aktivitaet_id IN (SELECT aktivitaet_id
                          FROM aktivitaet
                          WHERE status = 'geplant')
  AND (SELECT COUNT(*)
         FROM teilnahme AS x
        WHERE x.aktivitaet_id = t.aktivitaet_id
          AND x.status = 'angemeldet')
      < (SELECT a.max_teilnehmende
           FROM aktivitaet AS a
          WHERE a.aktivitaet_id = t.aktivitaet_id);

j) Dr. Kowalczyk hat festgelegt: Teilnahmen mit Status abgemeldet werden nicht mehr gebraucht, sobald die Aktivität stattgefunden hat oder abgesagt wurde. Lösche diese Teilnahmen mit einer SQL-Anweisung und gib an, wie viele Zeilen gelöscht werden. (3 Punkte)

DELETE FROM teilnahme
WHERE status = 'abgemeldet'
  AND aktivitaet_id IN (SELECT aktivitaet_id
                        FROM aktivitaet
                        WHERE status IN ('stattgefunden', 'abgesagt'));

Aufgabe 3.2: Monitorlisten, Monatsstatistik und Archiv einer Mitfahr-App (Profilkompetenz)

(ca. 44 min Bearbeitungszeit)

Am Ratz und Rübe Berufskolleg (RRB) in Bielefeld organisieren Auszubildende über die Web-App „RatzFahrt“ Fahrgemeinschaften zu den Berufsschultagen. Treffpunkte sind öffentliche Haltepunkte im Umland, Flurmonitore in der Schule zeigen offene Angebote und Gesuche ohne Namen an. Du arbeitest im FIA-Projektteam mit Oksana Melnyk und Jonas Wolff. Die Datenschutzbeauftragte der Schule, Andrea Wolf, betreut die App als Administratorin. Sie hat festgelegt, dass vergangene Fahrten nur noch ohne Personenbezug aufbewahrt werden.

Die Datenbank enthält unter anderem folgende Tabellen:

• nutzer (nutzer_id, pseudonym, vorname, nachname, email_schule, geburtsdatum, #klasse_id, rolle, einwilligung_eltern, fuehrerschein, …)

• haltepunkt (haltepunkt_id, name, ort, plz, entfernung_km, …)

• angebot (angebot_id, #fahrer_id, #fahrzeug_id, #haltepunkt_id, richtung, datum, abfahrt, freie_plaetze, kostenbeitrag_eur, status)

• gesuch (gesuch_id, #nutzer_id, #haltepunkt_id, richtung, datum, zeit_von, zeit_bis, status)

• buchung (buchung_id, #angebot_id, #mitfahrer_id, #gesuch_id, status, angefragt_am, beantwortet_am)

• bewertung (bewertung_id, #buchung_id, #von_nutzer_id, sterne, puenktlich, erstellt_am)

• fahrt_statistik (jahr, monat, #haltepunkt_id, richtung, angebote, mitfahrten, personen_km)

fahrer_id und mitfahrer_id verweisen auf nutzer. richtung hat die Werte zur Schule und nach Hause. angebot.status kann offen, voll, abgesagt oder durchgefuehrt sein, gesuch.status offen, vermittelt, zurueckgezogen oder abgelaufen, buchung.status angefragt, bestaetigt, abgelehnt oder storniert. Die Fremdschlüssel von buchung auf angebot und von bewertung auf buchung sind mit ON DELETE CASCADE angelegt.

a) Die App soll am Morgen alle Personen erinnern, die heute unterwegs sind. Andrea Wolf möchte dafür intern eine Liste sehen:

aa) Erstelle eine SQL-Abfrage, die alle Fahrer*innen heutiger Angebote mit Status offen oder voll und alle Mitfahrer*innen mit bestätigter Buchung für diese Angebote in einer Liste ausgibt, sortiert nach Abfahrt und Nachname. Die Spalte rolle_heute gibt an, aus welcher Teilabfrage eine Zeile stammt. (6 Punkte)

ab) Jonas schlägt vor, statt UNION ALL einfach UNION zu schreiben und die Spalte abfahrt wegzulassen, weil die Erinnerung ohnehin nur den Namen braucht. Beurteile den Vorschlag am Beispiel von Ben Richter. (2 Punkte)

SELECT 'Fahrer*in' AS rolle_heute, n.vorname, n.nachname, a.abfahrt
FROM angebot AS a
  JOIN nutzer AS n ON n.nutzer_id = a.fahrer_id
WHERE a.datum = CURDATE()
  AND a.status IN ('offen', 'voll')
UNION ALL
SELECT 'Mitfahrer*in', n.vorname, n.nachname, a.abfahrt
FROM buchung AS b
  JOIN angebot AS a ON a.angebot_id = b.angebot_id
  JOIN nutzer AS n ON n.nutzer_id = b.mitfahrer_id
WHERE a.datum = CURDATE()
  AND a.status IN ('offen', 'voll')
  AND b.status = 'bestaetigt'
ORDER BY abfahrt, nachname;

b) Für die Flurmonitore möchte Oksana wissen, an welchen Haltepunkten es Angebot und Nachfrage gibt.

ba) Erstelle eine SQL-Abfrage mit INTERSECT, die Name und Ort aller Haltepunkte ausgibt, an denen es ab heute sowohl ein offenes Angebot als auch ein offenes Gesuch gibt. (3 Punkte)

bb) Formuliere eine SQL-Abfrage mit EXCEPT, die alle Haltepunkte (ID, Name, Ort) ausgibt, für die noch nie ein Angebot eingestellt wurde, und gib das Ergebnis an. (2 Punkte)

SELECT h.name, h.ort
FROM haltepunkt AS h
WHERE h.haltepunkt_id IN (SELECT haltepunkt_id FROM angebot
                          WHERE status = 'offen' AND datum >= CURDATE())
INTERSECT
SELECT h.name, h.ort
FROM haltepunkt AS h
WHERE h.haltepunkt_id IN (SELECT haltepunkt_id FROM gesuch
                          WHERE status = 'offen' AND datum >= CURDATE());

SELECT haltepunkt_id, name, ort
FROM haltepunkt
EXCEPT
SELECT h.haltepunkt_id, h.name, h.ort
FROM haltepunkt AS h
  JOIN angebot AS a ON a.haltepunkt_id = h.haltepunkt_id;

c) Oksana entwickelt die Vermittlung: Für jedes offene Gesuch ab heute sucht die App ein passendes offenes Angebot mit gleichem Haltepunkt, gleicher Richtung und gleichem Datum, dessen Abfahrt im Zeitfenster des Gesuchs liegt. Gesuche ohne passendes Angebot sollen auf den Flurmonitoren hervorgehoben werden. Oksana hat die Abfrage begonnen:

SELECT g.gesuch_id, g.datum, g.zeit_von, g.zeit_bis
FROM gesuch AS g
WHERE g.status = 'offen'
  AND g.datum >= CURDATE()
  AND ____(1)____ (SELECT *
                   FROM ____(2)____
                   WHERE a.status = 'offen'
                     AND a.haltepunkt_id = ____(3)____
                     AND a.richtung = g.richtung
                     AND ____(4)____
                     AND a.abfahrt ____(5)____ g.zeit_von AND g.zeit_bis);

ca) Vervollständige die Abfrage, indem Du die Lücken (1) bis (5) ergänzt, und gib das Ergebnis an. (5 Punkte)

cb) Jonas fragt, ob SELECT * in der Unterabfrage nicht unnötig viele Daten lädt und was passieren würde, wenn man Lücke (3) mit a.haltepunkt_id statt mit einer Spalte von g füllt. Erläutere beides. (2 Punkte)

SELECT g.gesuch_id, g.datum, g.zeit_von, g.zeit_bis
FROM gesuch AS g
WHERE g.status = 'offen'
  AND g.datum >= CURDATE()
  AND NOT EXISTS (SELECT *
                  FROM angebot AS a
                  WHERE a.status = 'offen'
                    AND a.haltepunkt_id = g.haltepunkt_id
                    AND a.richtung = g.richtung
                    AND a.datum = g.datum
                    AND a.abfahrt BETWEEN g.zeit_von AND g.zeit_bis);

d) Für eine Übersichtsseite auf dem Monitor im Foyer möchte Thomas Becker je Haltepunkt und je Richtung sehen, wie viele offene Angebote und offene Gesuche es ab heute gibt – ausdrücklich auch dann, wenn es keine gibt:

Erstelle die SQL-Abfrage, sortiert nach Ort und innerhalb des Orts mit zur Schule zuerst. Hinweis: Die beiden Richtungen kannst Du mit einer abgeleiteten Tabelle erzeugen, die aus zwei SELECT-Anweisungen mit einer Konstanten besteht. (7 Punkte)

SELECT h.ort,
       r.richtung,
       (SELECT COUNT(*)
          FROM angebot AS a
         WHERE a.haltepunkt_id = h.haltepunkt_id
           AND a.richtung = r.richtung
           AND a.status = 'offen'
           AND a.datum >= CURDATE()) AS angebote,
       (SELECT COUNT(*)
          FROM gesuch AS g
         WHERE g.haltepunkt_id = h.haltepunkt_id
           AND g.richtung = r.richtung
           AND g.status = 'offen'
           AND g.datum >= CURDATE()) AS gesuche
FROM haltepunkt AS h,
     (SELECT 'zur Schule' AS richtung
      UNION
      SELECT 'nach Hause') AS r
ORDER BY h.ort, r.richtung DESC;

e) Andrea Wolf möchte im Schulausschuss zeigen, von welchen Orten aus die Fahrgemeinschaften im September 2026 genutzt wurden.

ea) Erstelle eine SQL-Abfrage, die aus fahrt_statistik für September 2026 je Ort die Summe der Mitfahrten (beide Richtungen zusammen) und ihren Anteil an allen Mitfahrten dieses Monats in Prozent (eine Nachkommastelle) ausgibt, den größten Anteil zuerst. Gib das Ergebnis an. (4 Punkte)

eb) Erläutere, warum die Einschränkung auf September 2026 auch in der Unterabfrage stehen muss, obwohl fahrt_statistik derzeit nur Daten für September enthält. (2 Punkte)

SELECT h.ort,
       SUM(s.mitfahrten) AS mitfahrten,
       ROUND(SUM(s.mitfahrten) * 100 / (SELECT SUM(x.mitfahrten)
                                        FROM fahrt_statistik AS x
                                        WHERE x.jahr = 2026 AND x.monat = 9), 1) AS anteil_prozent
FROM fahrt_statistik AS s
  JOIN haltepunkt AS h ON h.haltepunkt_id = s.haltepunkt_id
WHERE s.jahr = 2026 AND s.monat = 9
GROUP BY h.haltepunkt_id, h.ort
ORDER BY anteil_prozent DESC;

f) Andrea Wolf hat festgelegt: Angebote vor dem heutigen Tag, die durchgeführt oder abgesagt wurden, werden ohne Personenbezug in eine Archivtabelle übertragen und anschließend gelöscht. Im Archiv sollen ID, Haltepunkt, Richtung, Datum, Abfahrt, Status und die Zahl der bestätigten Mitfahrten stehen.

fa) Erstelle die SQL-Anweisung zum Anlegen der Tabelle angebot_archiv mit geeigneten Datentypen, Primärschlüssel und Fremdschlüssel auf haltepunkt. (2 Punkte)

fb) Erstelle die SQL-Anweisung, die die betroffenen Angebote mit der Zahl ihrer bestätigten Buchungen in das Archiv überträgt, und gib den Inhalt des Archivs an. (3 Punkte)

fc) Erstelle die SQL-Anweisung, die alle archivierten Angebote aus angebot löscht, und verwende dabei die Archivtabelle. (2 Punkte)

fd) Begründe, warum die Anweisungen aus fb) und fc) in einer Transaktion ausgeführt werden sollten, und gib an, welche Datensätze anderer Tabellen beim Löschen ebenfalls entfernt werden. (2 Punkte)

CREATE TABLE angebot_archiv (
  angebot_id     INT UNSIGNED NOT NULL PRIMARY KEY,
  haltepunkt_id  INT UNSIGNED NOT NULL,
  richtung       ENUM('zur Schule', 'nach Hause') NOT NULL,
  datum          DATE NOT NULL,
  abfahrt        TIME NOT NULL,
  status         ENUM('durchgefuehrt', 'abgesagt') NOT NULL,
  mitfahrten     TINYINT UNSIGNED NOT NULL,
  CONSTRAINT fk_archiv_haltepunkt FOREIGN KEY (haltepunkt_id)
    REFERENCES haltepunkt (haltepunkt_id)
);

INSERT INTO angebot_archiv
       (angebot_id, haltepunkt_id, richtung, datum, abfahrt, status, mitfahrten)
SELECT a.angebot_id, a.haltepunkt_id, a.richtung, a.datum, a.abfahrt, a.status,
       (SELECT COUNT(*)
          FROM buchung AS b
         WHERE b.angebot_id = a.angebot_id
           AND b.status = 'bestaetigt')
FROM angebot AS a
WHERE a.datum < CURDATE()
  AND a.status IN ('durchgefuehrt', 'abgesagt');

DELETE FROM angebot
WHERE angebot_id IN (SELECT angebot_id FROM angebot_archiv);

g) Nach dem Archivieren möchte Jonas die Zahl der Angebote je Haltepunkt über aktuelle und archivierte Angebote auswerten. Seine Abfrage:

SELECT haltepunkt_id, COUNT(*) AS angebote
FROM angebot
GROUP BY haltepunkt_id
UNION ALL
SELECT haltepunkt_id, COUNT(*)
FROM angebot_archiv
GROUP BY haltepunkt_id
ORDER BY haltepunkt_id;

ga) Erläutere, warum die Haltepunkte 1 und 2 zweimal erscheinen. (2 Punkte)

gb) Korrigiere die Abfrage so, dass jeder Haltepunkt mit seinem Ort genau einmal mit der Gesamtzahl seiner Angebote erscheint, die meisten zuerst, und gib das Ergebnis an. Begründe, warum Du dabei UNION ALL und nicht UNION verwendest. (4 Punkte)

SELECT h.ort, COUNT(*) AS angebote
FROM (SELECT haltepunkt_id FROM angebot
      UNION ALL
      SELECT haltepunkt_id FROM angebot_archiv) AS alle
  JOIN haltepunkt AS h ON h.haltepunkt_id = alle.haltepunkt_id
GROUP BY h.haltepunkt_id, h.ort
ORDER BY angebote DESC;

Anlage: SQL-Kurzreferenz (MariaDB)

Links und weitere Informationen

• Artikel „SQL: Unterabfragen und Mengenoperationen“ auf oer-informatik.de

• SQL: Abfragen über mehrere Tabellen mit JOIN und die Übungsaufgaben zu JOIN

• SQL: Aggregierende Funktionen

• SQL-Übungsaufgaben auf oer-informatik.de

• als Nachschlagewerk: MariaDB Knowledge Base: Subqueries

• als Nachschlagewerk: MariaDB Knowledge Base: UNION, INTERSECT und EXCEPT

Hinweis zur Nachnutzung als Open Educational Resource (OER)

Dieser Artikel und seine Texte, Bilder, Grafiken, Code und sonstiger Inhalt sind - sofern nicht anders angegeben - lizenziert unter CC BY 4.0. Nennung gemäß TULLU-Regel bitte wie folgt: “Übungsaufgaben zu SQL-Unterabfragen und Mengenoperationen” von oer-informatik.de (H. Stein), Lizenz: CC BY 4.0. Der Artikel wurde unter https://oer-informatik.de/sql-unterabfragen-uebungsaufgaben veröffentlicht, die Quelltexte sind in weiterverarbeitbarer Form verfügbar im Repository unter https://gitlab.com/oer-informatik/db-sql/sql. Erstellt mit KI-Assistenz; von mir umfassend redaktionell überarbeitet, geprüft und freigegeben. Stand: 28.10.2026.

[Kommentare zum Artikel lesen, schreiben] / [Artikel teilen] / [gitlab-Issue zum Artikel schreiben]