Übungsaufgaben zu SQL-Abfragen über mehrere Tabellen (JOIN)

Übungsaufgaben zu SQL-Abfragen über mehrere Tabellen (JOIN)

https://bildung.social/@oerinformatik/112633708392604185

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

SQL, JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, Anti-Join, Kartesisches Produkt, MariaDB, Übungsaufgaben, Prüfungsvorbereitung

tl/dr; (Bearbeitungszeit insgesamt 154 min, mehrere Aufgaben): Fünf Übungsaufgaben zu Verbünden über mehrere Tabellen – vom einfachen INNER JOIN über LEFT, RIGHT und nachgebauten FULL JOIN bis zu Tabellen in mehreren Rollen, zusammengesetzten Join-Bedingungen, Anti-Joins, Vervielfachung beim Zählen sowie UPDATE und DELETE mit JOIN, außerdem Zeitspannen-Auswertungen, DISTINCT-Abfragen und Verbünde mit pandas. Alle Beispiellösungen sind ausgeblendet und mit Punkteverteilung versehen [AI-Assisted]. (Zuletzt geändert am 26.10.2026)

Dieser Artikel ist Teil einer Artikelreihe über SQL-Abfragen: Selektion und Projektion / Sortieren und Begrenzen (ORDER BY, LIMIT) / Funktionen im SELECT (Single-Row-Functions) / Fallunterscheidungen und NULL (CASE, COALESCE) / Aggregatfunktionen und Gruppierung / Abfragen über mehrere Tabellen (JOIN) / Übungsaufgaben: einfache Abfragen, Aggregatfunktionen, JOIN, Rechnungsdatenbank (gemischt)

Diese Übungsaufgaben gehören zum Artikel SQL: Abfragen über mehrere Tabellen mit JOIN. Wie die Tabellen über Primär- und Fremdschlüssel zusammenhängen, erklärt der Artikel Der logische Datenbankentwurf mit dem Relationenmodell; Gruppieren und Zählen findest Du im Artikel SQL: Aggregierende Funktionen.

Hinweise zur Bearbeitung: Die Aufgaben sind bewusst umfangreich, damit alle typischen Fragestellungen zum Thema vorkommen; die Angabe hinter dem Aufgabentitel nennt die Kompetenzstufe (Stufe 1 Basiskompetenz bis Stufe 4 Expertise). 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 JOIN statt INNER JOIN oder Tabellennamen statt Aliasen). In den Datenmodellen sind Primärschlüssel unterstrichen und Fremdschlüssel mit # gekennzeichnet. Heute ist in allen Szenarien der 18.10.2026 (CURDATE()). Wo sich die Schreibweise der Datenbanksysteme unterscheidet, zeigen Tabs die Beispiellösung zusätzlich für SQL Server, PostgreSQL, Oracle und SQLite; in Oracle entfällt außerdem das Schlüsselwort AS vor Tabellenaliasen (FROM artikel a). Am Ende findest Du eine SQL-Kurzreferenz.

Aufgabe 1: Bestell- und Fertigungsübersicht im Fahrradshop (Stufe 2 – Kernkompetenz)

(ca. 34 min Bearbeitungszeit)

Du bist Auszubildende*r bei der CSB-Rad GmbH in Bielefeld, die Fahrräder und Komponenten über einen Onlineshop verkauft und Rahmen und Schlösser bei Partnerbetrieben individuell fertigen lässt. Werkstattleiter Bastian Ludwig steuert die Fertigung und braucht für seine Planung einige Listen aus der Shop-Datenbank. Sie enthält unter anderem folgende Tabellen:

• kunde (kunde_id, kundentyp, firma, vorname, nachname, email, …)

• adresse (adresse_id, #kunde_id, typ, strasse, hausnr, plz, ort, land)

• bestellung (bestellung_id, #kunde_id, bestellt_am, status, zahlungsart, #rechnungsadresse_id, #lieferadresse_id, …)

• bestellposition (#bestellung_id, pos_nr, #artikel_id, menge, einzelpreis_netto, rabatt_prozent, #konfiguration_id)

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

• kategorie (kategorie_id, bezeichnung, #ober_kategorie_id)

• fertigungspartner (partner_id, name, ort, leistung, schnittstelle, …)

• fertigungsauftrag (auftrag_id, #bestellung_id, #pos_nr, #partner_id, uebermittelt_am, status, liefertermin, fehlermeldung)

Die Fremdschlüssel rechnungsadresse_id und lieferadresse_id verweisen beide auf adresse. Der Fremdschlüssel (bestellung_id, pos_nr) in fertigungsauftrag verweist auf den zusammengesetzten Primärschlüssel von bestellposition. Derzeit sind 7 Kund*innen und 8 Bestellungen gespeichert.

a) Zeige alle Bestellungen mit Bestellnummer, Bestellzeitpunkt, Status sowie Nachname, Vorname und Firma der Kund*innen an. Die neueste Bestellung soll oben stehen. Erstelle dazu die SQL-Abfrage. (4 Punkte)

SELECT b.bestellung_id, b.bestellt_am, b.status,
       k.nachname, k.vorname, k.firma
FROM bestellung AS b
  INNER JOIN kunde AS k ON k.kunde_id = b.kunde_id
ORDER BY b.bestellt_am DESC;

b) Bastian Ludwig hat sich in einem Forum eine Abfrage für dieselbe Liste abgeschaut und angepasst:

SELECT bestellung_id, kunde_id, nachname
FROM bestellung, kunde;

ba) MariaDB bricht mit der Meldung Column 'kunde_id' in field list is ambiguous ab. Erläutere die Meldung und korrigiere die Abfrage so, dass sie die Schreibweise mit Komma in der FROM-Klausel beibehält und jede Bestellung genau einmal mit dem passenden Nachnamen ausgibt. (3 Punkte)

bb) Ermittle, wie viele Zeilen die Abfrage geliefert hätte, wenn in der SELECT-Liste nur eindeutige Spaltennamen gestanden hätten, und erläutere, wie diese Zeilen zustande kommen. (2 Punkte)

SELECT b.bestellung_id, b.kunde_id, k.nachname
FROM bestellung AS b, kunde AS k
WHERE b.kunde_id = k.kunde_id;

c) Chiamaka Okafor-Schmidt fragt nach dem Stand ihrer Bestellungen. Bastian kennt ihre Kundennummer nicht, nur den Nachnamen. Erstelle eine SQL-Abfrage, die alle von ihr bestellten Positionen mit Bestellnummer, Positionsnummer, Artikelnummer, Artikelbezeichnung, Kategorie und Menge ausgibt, sortiert nach Bestellung und Position. (6 Punkte)

SELECT b.bestellung_id, bp.pos_nr, a.artikelnr,
       a.bezeichnung AS artikel, kat.bezeichnung AS kategorie, bp.menge
FROM kunde AS k
  INNER JOIN bestellung AS b ON b.kunde_id = k.kunde_id
  INNER JOIN bestellposition AS bp ON bp.bestellung_id = b.bestellung_id
  INNER JOIN artikel AS a ON a.artikel_id = bp.artikel_id
  INNER JOIN kategorie AS kat ON kat.kategorie_id = a.kategorie_id
WHERE k.nachname = 'Okafor-Schmidt'
ORDER BY b.bestellung_id, bp.pos_nr;

d) Für die Wochenplanung braucht Bastian alle Fertigungsaufträge, die noch nicht geliefert sind.

da) Erstelle eine SQL-Abfrage, die für diese Aufträge die Auftragsnummer, die Bestellnummer, die Bezeichnung des gefertigten Artikels, den Namen des Fertigungspartners, den Status und den Liefertermin ausgibt, sortiert nach Liefertermin. (6 Punkte)

db) Erläutere, was sich am Ergebnis ändert, wenn fertigungsauftrag und bestellposition nur über die Bestellnummer verknüpft werden. (2 Punkte)

SELECT fa.auftrag_id, fa.bestellung_id, a.bezeichnung AS artikel,
       fp.name AS partner, fa.status, fa.liefertermin
FROM fertigungsauftrag AS fa
  INNER JOIN bestellposition AS bp ON bp.bestellung_id = fa.bestellung_id
                                  AND bp.pos_nr = fa.pos_nr
  INNER JOIN artikel AS a ON a.artikel_id = bp.artikel_id
  INNER JOIN fertigungspartner AS fp ON fp.partner_id = fa.partner_id
WHERE fa.status <> 'geliefert'
ORDER BY fa.liefertermin;

e) Für die Versandplanung sollen alle Bestellungen aufgelistet werden, bei denen Rechnungs- und Lieferadresse verschieden sind. Erstelle eine SQL-Abfrage, die die Bestellnummer, den Nachnamen sowie Straße und Ort der Rechnungsadresse und Straße und Ort der Lieferadresse ausgibt. (5 Punkte)

SELECT b.bestellung_id, k.nachname,
       ra.strasse AS rechnung_strasse, ra.ort AS rechnung_ort,
       la.strasse AS liefer_strasse, la.ort AS liefer_ort
FROM bestellung AS b
  INNER JOIN kunde AS k ON k.kunde_id = b.kunde_id
  INNER JOIN adresse AS ra ON ra.adresse_id = b.rechnungsadresse_id
  INNER JOIN adresse AS la ON la.adresse_id = b.lieferadresse_id
WHERE b.rechnungsadresse_id <> b.lieferadresse_id
ORDER BY b.bestellung_id;

f) Für die Lagerplanung ermittelt Bastian die Artikel, von denen in nicht stornierten Bestellungen insgesamt mehr als 5 Stück verkauft wurden. Erstelle die SQL-Abfrage mit Artikelnummer, Bezeichnung und verkaufter Stückzahl, absteigend nach Stückzahl sortiert. (5 Punkte)

SELECT a.artikelnr, a.bezeichnung, SUM(bp.menge) AS stueck
FROM artikel AS a
  INNER JOIN bestellposition AS bp ON bp.artikel_id = a.artikel_id
  INNER JOIN bestellung AS b ON b.bestellung_id = bp.bestellung_id
WHERE b.status <> 'storniert'
GROUP BY a.artikel_id, a.artikelnr, a.bezeichnung
HAVING SUM(bp.menge) > 5
ORDER BY stueck DESC;

g) Die Lipper SchließTec KG erhöht ihre Preise. Erstelle eine SQL-Anweisung, die den Nettopreis aller Artikel der Kategorie „Schlösser“ um 4 % erhöht und auf zwei Nachkommastellen rundet. Die kategorie_id der Schlösser ist nicht bekannt. (4 Punkte)

UPDATE artikel AS a
  INNER JOIN kategorie AS k ON k.kategorie_id = a.kategorie_id
SET a.netto_preis = ROUND(a.netto_preis * 1.04, 2)
WHERE k.bezeichnung = 'Schlösser';

UPDATE a
SET a.netto_preis = ROUND(a.netto_preis * 1.04, 2)
FROM artikel AS a
  INNER JOIN kategorie AS k ON k.kategorie_id = a.kategorie_id
WHERE k.bezeichnung = 'Schlösser';

UPDATE artikel AS a
SET netto_preis = ROUND(a.netto_preis * 1.04, 2)
FROM kategorie AS k
WHERE k.kategorie_id = a.kategorie_id
  AND k.bezeichnung = 'Schlösser';

UPDATE artikel a
SET a.netto_preis = ROUND(a.netto_preis * 1.04, 2)
FROM kategorie k
WHERE k.kategorie_id = a.kategorie_id
  AND k.bezeichnung = 'Schlösser';

UPDATE artikel AS a
SET netto_preis = ROUND(a.netto_preis * 1.04, 2)
FROM kategorie AS k
WHERE k.kategorie_id = a.kategorie_id
  AND k.bezeichnung = 'Schlösser';

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

Aufgabe 2: Sessionplan und Raumbelegung für ein Barcamp (Stufe 2 – Kernkompetenz)

(ca. 32 min Bearbeitungszeit)

Das Ratz und Rübe Berufskolleg in Bielefeld veranstaltet am 13.03.2027 das Barcamp „Lernen.Digital.Leben 2027“. Du arbeitest als Auszubildende*r im Orga-Team von Informatiklehrer Dimitrios Papadopoulos mit. Emre Aksoy, Leiter des Technikteams, hat schon einige Abfragen geschrieben. Die Orga-Datenbank enthält unter anderem:

• person (person_id, vorname, nachname, email, organisation, geburtsjahr)

• anmeldung (anmeldung_id, #veranstaltung_id, #person_id, ticketart, angemeldet_am, essen, bedarf_barrierefreiheit, foto_einwilligung, …)

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

• slot (slot_id, #veranstaltung_id, beginn, ende)

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

• session_person (#session_id, #person_id, rolle)

• session_buchung (#session_id, #anmeldung_id, gebucht_am)

• geraet (geraet_id, typ, bezeichnung, mac, ip, #vlan_id, #raum_id, standort, leistung_w, poe)

ticketart hat einen der Werte 'Schueler', 'Lehrkraft', 'Extern', 'Referent', 'Orga' oder 'Aussteller'; barrierefrei ist ein Wahrheitswert. Sessions, die noch nicht eingeplant sind, haben in raum_id und slot_id den Wert NULL.

a) Für die Infomonitore braucht Dimitrios den Sessionplan. Erstelle eine SQL-Abfrage, die für alle angenommenen Sessions Beginn und Ende des Slots, die Raumbezeichnung und den Titel ausgibt, sortiert nach Beginn und bei gleichem Beginn nach Raum. (4 Punkte)

SELECT sl.beginn, sl.ende, r.bezeichnung AS raum, s.titel
FROM session AS s
  INNER JOIN slot AS sl ON sl.slot_id = s.slot_id
  INNER JOIN raum AS r ON r.raum_id = s.raum_id
WHERE s.status = 'angenommen'
ORDER BY sl.beginn, r.bezeichnung;

b) Dimitrios möchte für die Planungsrunde alle Sessions sehen, die nicht abgelehnt wurden – auch die noch nicht eingeplanten. Er wünscht sich folgendes Ergebnis:

ba) Begründe, warum die Abfrage aus a) die Session 5 auch ohne den Statusfilter nicht liefern würde. (2 Punkte)

bb) Erstelle die SQL-Abfrage für das gewünschte Ergebnis. (4 Punkte)

SELECT s.session_id, s.titel, s.status,
       COALESCE(r.bezeichnung, 'noch offen') AS raum, sl.beginn
FROM session AS s
  LEFT JOIN raum AS r ON r.raum_id = s.raum_id
  LEFT JOIN slot AS sl ON sl.slot_id = s.slot_id
WHERE s.status <> 'abgelehnt'
ORDER BY s.session_id;

c) Emre plant die E-Ink-Türschilder und möchte wissen, wie viele Slots in jedem Raum belegt sind, auch wenn es keine sind. Er hat folgenden Entwurf begonnen:

SELECT r.bezeichnung, r.name, COUNT(______①______) AS belegte_slots
FROM session AS s
  ______②______ JOIN raum AS r ON ______③______
GROUP BY r.raum_id, r.bezeichnung, r.name
ORDER BY r.bezeichnung;

ca) Ergänze die Lücken ① bis ③ so, dass alle acht Räume mit der richtigen Anzahl erscheinen. (4 Punkte)

cb) Formuliere die Abfrage so um, dass sie mit einem LEFT JOIN dasselbe Ergebnis liefert. (2 Punkte)

SELECT r.bezeichnung, r.name, COUNT(s.session_id) AS belegte_slots
FROM session AS s
  RIGHT JOIN raum AS r ON r.raum_id = s.raum_id
GROUP BY r.raum_id, r.bezeichnung, r.name
ORDER BY r.bezeichnung;

SELECT r.bezeichnung, r.name, COUNT(s.session_id) AS belegte_slots
FROM raum AS r
  LEFT JOIN session AS s ON s.raum_id = r.raum_id
GROUP BY r.raum_id, r.bezeichnung, r.name
ORDER BY r.bezeichnung;

d) Für die Auswertung der Schüler*innen-Beteiligung soll für jede angenommene Session angezeigt werden, wie viele Buchungen von Schüler*innen (Ticketart 'Schueler') es gibt – auch wenn es keine gibt. Emre hat dazu geschrieben:

SELECT s.titel, COUNT(sb.anmeldung_id) AS schueler_buchungen
FROM session AS s
  LEFT JOIN session_buchung AS sb ON sb.session_id = s.session_id
  LEFT JOIN anmeldung AS a ON a.anmeldung_id = sb.anmeldung_id
WHERE s.status = 'angenommen'
  AND a.ticketart = 'Schueler'
GROUP BY s.session_id, s.titel;

Seine Abfrage liefert nur drei der fünf angenommenen Sessions.

da) Erläutere, warum die Sessions „KI-Assistenten im Unterricht“ und „Barrierefreie Videos“ fehlen. (2 Punkte)

db) Korrigiere die Abfrage. Achte darauf, dass wirklich nur Buchungen von Schüler*innen gezählt werden. (4 Punkte)

SELECT s.titel, COUNT(a.anmeldung_id) AS schueler_buchungen
FROM session AS s
  LEFT JOIN session_buchung AS sb ON sb.session_id = s.session_id
  LEFT JOIN anmeldung AS a ON a.anmeldung_id = sb.anmeldung_id
                          AND a.ticketart = 'Schueler'
WHERE s.status = 'angenommen'
GROUP BY s.session_id, s.titel
ORDER BY s.session_id;

e) Gerhard Vogt, der den Repair-Workshop leitet, ist mit dem Rollator unterwegs und braucht einen Aufzug.

ea) Erstelle eine SQL-Abfrage, die alle Referierenden (alle Rollen in session_person) mit Vor- und Nachname, Sessiontitel, Raumbezeichnung und angegebenem Barrierefreiheitsbedarf auflistet, die einen Bedarf angegeben haben und deren Session in einem nicht barrierefreien Raum stattfindet. Den Bedarf findest Du in der Anmeldung der Person zu derselben Veranstaltung. (5 Punkte)

eb) Dimitrios will die Abfrage künftig vor jeder Änderung am Sessionplan laufen lassen. Nimm Stellung, ob sie damit alle Barrierefreiheitsprobleme der Referierenden zuverlässig findet. (3 Punkte)

SELECT p.vorname, p.nachname, s.titel, r.bezeichnung AS raum,
       an.bedarf_barrierefreiheit
FROM session AS s
  INNER JOIN raum AS r ON r.raum_id = s.raum_id
  INNER JOIN session_person AS sp ON sp.session_id = s.session_id
  INNER JOIN person AS p ON p.person_id = sp.person_id
  INNER JOIN anmeldung AS an ON an.person_id = p.person_id
                            AND an.veranstaltung_id = s.veranstaltung_id
WHERE r.barrierefrei = FALSE
  AND an.bedarf_barrierefreiheit IS NOT NULL;

f) Jedes E-Ink-Türschild ist in geraet mit dem Typ 'E-Ink-Tuerschild' erfasst. Seit gestern liegt ein Ersatz-Türschild im Orga-Büro, das noch keinem Raum zugeordnet ist:

INSERT INTO geraet (bezeichnung, typ, mac, ip, vlan_id, raum_id, standort, leistung_w, poe)
VALUES ('TS-RES-1', 'E-Ink-Tuerschild', '24:6F:28:40:09:01', '10.120.0.250', 120,
        NULL, 'Lager Orga-Büro A.010', 1, FALSE);

Liste alle Räume und alle E-Ink-Türschilder in einer Ergebnistabelle auf, sodass sowohl Räume ohne Türschild als auch Türschilder ohne Raum zu sehen sind. Beachte die SQL-Kurzreferenz in der Anlage. (5 Punkte)

SELECT r.bezeichnung AS raum, g.bezeichnung AS tuerschild
FROM raum AS r
  LEFT JOIN geraet AS g ON g.raum_id = r.raum_id
                       AND g.typ = 'E-Ink-Tuerschild'
UNION
SELECT r.bezeichnung, g.bezeichnung
FROM raum AS r
  RIGHT JOIN geraet AS g ON g.raum_id = r.raum_id
WHERE g.typ = 'E-Ink-Tuerschild'
ORDER BY raum;

SELECT r.bezeichnung AS raum, g.bezeichnung AS tuerschild
FROM raum AS r
  FULL JOIN (SELECT bezeichnung, raum_id
             FROM geraet
             WHERE typ = 'E-Ink-Tuerschild') AS g ON g.raum_id = r.raum_id
ORDER BY raum;

SELECT r.bezeichnung AS raum, g.bezeichnung AS tuerschild
FROM raum AS r
  FULL JOIN (SELECT bezeichnung, raum_id
             FROM geraet
             WHERE typ = 'E-Ink-Tuerschild') AS g ON g.raum_id = r.raum_id
ORDER BY raum NULLS FIRST;

SELECT r.bezeichnung AS raum, g.bezeichnung AS tuerschild
FROM raum r
  FULL JOIN (SELECT bezeichnung, raum_id
             FROM geraet
             WHERE typ = 'E-Ink-Tuerschild') g ON g.raum_id = r.raum_id
ORDER BY raum NULLS FIRST;

SELECT r.bezeichnung AS raum, g.bezeichnung AS tuerschild
FROM raum AS r
  FULL JOIN (SELECT bezeichnung, raum_id
             FROM geraet
             WHERE typ = 'E-Ink-Tuerschild') AS g ON g.raum_id = r.raum_id
ORDER BY raum;

Aufgabe 3: Buchungen, Matching und Statistik einer Mitfahr-App (Stufe 2 – Kernkompetenz)

(ca. 34 min Bearbeitungszeit)

Die Auszubildenden des Ratz und Rübe Berufskollegs organisieren ihre Fahrgemeinschaften zur Berufsschule mit der App „RatzFahrt“. Du arbeitest im FIA-Projektteam mit Oksana Melnyk und Jonas Wolff. Andrea Wolf ist die Datenschutzbeauftragte der Schule und administriert die App. Ausschnitt aus dem Datenmodell:

• 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, erstellt_am)

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

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

fahrer_id und mitfahrer_id verweisen beide auf nutzer. abfahrt, zeit_von und zeit_bis sind Uhrzeiten (TIME), angefragt_am und beantwortet_am Zeitpunkte (DATETIME).

a) In der App sehen Fahrende und Mitfahrende nur Pseudonyme. Erstelle eine SQL-Abfrage für eine Buchungsübersicht mit Buchungsnummer, Datum und Abfahrtszeit des Angebots, Ort des Haltepunkts, Pseudonym der Fahrerin oder des Fahrers, Pseudonym der Mitfahrerin oder des Mitfahrers und Buchungsstatus, sortiert nach Datum und Abfahrtszeit. (6 Punkte)

SELECT b.buchung_id, an.datum, an.abfahrt, h.ort,
       f.pseudonym AS fahrer, m.pseudonym AS mitfahrer, b.status
FROM buchung AS b
  INNER JOIN angebot AS an ON an.angebot_id = b.angebot_id
  INNER JOIN haltepunkt AS h ON h.haltepunkt_id = an.haltepunkt_id
  INNER JOIN nutzer AS f ON f.nutzer_id = an.fahrer_id
  INNER JOIN nutzer AS m ON m.nutzer_id = b.mitfahrer_id
ORDER BY an.datum, an.abfahrt;

b) Oksana entwickelt die Vermittlung. Ein Angebot passt zu einem Gesuch, wenn Haltepunkt, Richtung und Datum übereinstimmen und die Abfahrt im Zeitfenster des Gesuchs liegt (Grenzen eingeschlossen).

ba) Erstelle eine SQL-Abfrage, die zu jedem Gesuch die Gesuchsnummer, den Ort des Haltepunkts, Datum und Zeitfenster sowie Nummer, Abfahrt und Status der passenden Angebote ausgibt. Gesuche ohne passendes Angebot sollen ebenfalls erscheinen. (7 Punkte)

bb) Aylin Kaya (Gesuch 1) wundert sich, dass ihr kein Angebot vorgeschlagen wird, obwohl Ben Richter (Angebot 1) ebenfalls ab Gütersloh zur Schule fährt. Begründe das Ergebnis und gib an, welche Bedingung ergänzt werden muss, damit nur noch tatsächlich buchbare Angebote vorgeschlagen werden. (2 Punkte)

SELECT g.gesuch_id, h.ort, g.datum, g.zeit_von, g.zeit_bis,
       an.angebot_id, an.abfahrt, an.status AS angebot_status
FROM gesuch AS g
  INNER JOIN haltepunkt AS h ON h.haltepunkt_id = g.haltepunkt_id
  LEFT JOIN angebot AS an ON an.haltepunkt_id = g.haltepunkt_id
                         AND an.richtung = g.richtung
                         AND an.datum = g.datum
                         AND an.abfahrt BETWEEN g.zeit_von AND g.zeit_bis
ORDER BY g.gesuch_id;

c) Die Schülervertretung möchte wissen, wie schnell Fahrende auf Anfragen reagieren. Erstelle eine SQL-Abfrage, die für jede beantwortete Buchung die Buchungsnummer, das Pseudonym der Fahrerin oder des Fahrers, den Status und die Antwortzeit in Minuten ausgibt, die längste Antwortzeit zuerst. (5 Punkte)

SELECT b.buchung_id, f.pseudonym AS fahrer, b.status,
       TIMESTAMPDIFF(MINUTE, b.angefragt_am, b.beantwortet_am) AS antwortzeit_min
FROM buchung AS b
  INNER JOIN angebot AS an ON an.angebot_id = b.angebot_id
  INNER JOIN nutzer AS f ON f.nutzer_id = an.fahrer_id
WHERE b.beantwortet_am IS NOT NULL
ORDER BY antwortzeit_min DESC;

SELECT b.buchung_id, f.pseudonym AS fahrer, b.status,
       DATEDIFF(minute, b.angefragt_am, b.beantwortet_am) AS antwortzeit_min
FROM buchung AS b
  INNER JOIN angebot AS an ON an.angebot_id = b.angebot_id
  INNER JOIN nutzer AS f ON f.nutzer_id = an.fahrer_id
WHERE b.beantwortet_am IS NOT NULL
ORDER BY antwortzeit_min DESC;

SELECT b.buchung_id, f.pseudonym AS fahrer, b.status,
       FLOOR(EXTRACT(EPOCH FROM b.beantwortet_am - b.angefragt_am) / 60) AS antwortzeit_min
FROM buchung AS b
  INNER JOIN angebot AS an ON an.angebot_id = b.angebot_id
  INNER JOIN nutzer AS f ON f.nutzer_id = an.fahrer_id
WHERE b.beantwortet_am IS NOT NULL
ORDER BY antwortzeit_min DESC;

SELECT b.buchung_id, f.pseudonym AS fahrer, b.status,
       FLOOR((b.beantwortet_am - b.angefragt_am) * 24 * 60) AS antwortzeit_min
FROM buchung b
  INNER JOIN angebot an ON an.angebot_id = b.angebot_id
  INNER JOIN nutzer f ON f.nutzer_id = an.fahrer_id
WHERE b.beantwortet_am IS NOT NULL
ORDER BY antwortzeit_min DESC;

SELECT b.buchung_id, f.pseudonym AS fahrer, b.status,
       (strftime('%s', b.beantwortet_am) - strftime('%s', b.angefragt_am)) / 60 AS antwortzeit_min
FROM buchung AS b
  INNER JOIN angebot AS an ON an.angebot_id = b.angebot_id
  INNER JOIN nutzer AS f ON f.nutzer_id = an.fahrer_id
WHERE b.beantwortet_am IS NOT NULL
ORDER BY antwortzeit_min DESC;

d) Ermittle mit einer SQL-Abfrage alle Haltepunkte (Nummer, Name, Ort), die bisher weder in einem Angebot noch in einem Gesuch verwendet wurden. Verwende dazu keine Unterabfrage. (4 Punkte)

SELECT h.haltepunkt_id, h.name, h.ort
FROM haltepunkt AS h
  LEFT JOIN angebot AS an ON an.haltepunkt_id = h.haltepunkt_id
  LEFT JOIN gesuch AS g ON g.haltepunkt_id = h.haltepunkt_id
WHERE an.angebot_id IS NULL
  AND g.gesuch_id IS NULL;

e) Jonas möchte für die Monatsstatistik je Fahrer*in die Anzahl der Angebote und die Anzahl der bestätigten Mitfahrten ausgeben. Seine Abfrage:

SELECT n.pseudonym,
       COUNT(an.angebot_id) AS angebote,
       COUNT(b.buchung_id)  AS mitfahrten
FROM nutzer AS n
  INNER JOIN angebot AS an ON an.fahrer_id = n.nutzer_id
  LEFT JOIN buchung AS b ON b.angebot_id = an.angebot_id
GROUP BY n.nutzer_id, n.pseudonym
ORDER BY n.pseudonym;

liefert:

ea) Funkenflug (Ben Richter) hat aber nur zwei Angebote eingestellt. Erläutere anhand der Zwischenergebnisse des Verbunds, wie die falschen Werte zustande kommen. (3 Punkte)

eb) Korrigiere die Abfrage. (4 Punkte)

ec) Oksana schlägt vor, die beiden Werte stattdessen mit zwei Unterabfragen im SELECT zu ermitteln, zum Beispiel (SELECT COUNT(*) FROM angebot WHERE fahrer_id = n.nutzer_id) AS angebote. Beurteile den Vorschlag. (2 Punkte)

SELECT n.pseudonym,
       COUNT(DISTINCT an.angebot_id) AS angebote,
       COUNT(b.buchung_id)           AS mitfahrten
FROM nutzer AS n
  INNER JOIN angebot AS an ON an.fahrer_id = n.nutzer_id
  LEFT JOIN buchung AS b ON b.angebot_id = an.angebot_id
                        AND b.status = 'bestaetigt'
GROUP BY n.nutzer_id, n.pseudonym
ORDER BY n.pseudonym;

f) Lina Hartwig (17 Jahre) hat sich ohne Einwilligung ihrer Eltern registriert und eine Mitfahrt angefragt. Andrea Wolf legt fest: Buchungsanfragen von minderjährigen Nutzenden ohne eingetragene Einwilligung werden gelöscht. Erstelle eine SQL-Anweisung, die alle betroffenen Buchungen löscht, ohne die Nutzer-IDs zu kennen. Das Alter am heutigen Tag erhältst Du mit TIMESTAMPDIFF(YEAR, geburtsdatum, CURDATE()). (4 Punkte)

DELETE b
FROM buchung AS b
  INNER JOIN nutzer AS n ON n.nutzer_id = b.mitfahrer_id
WHERE TIMESTAMPDIFF(YEAR, n.geburtsdatum, CURDATE()) < 18
  AND n.einwilligung_eltern IS NULL;

DELETE b
FROM buchung AS b
  INNER JOIN nutzer AS n ON n.nutzer_id = b.mitfahrer_id
WHERE DATEADD(year, 18, n.geburtsdatum) > CAST(GETDATE() AS date)
  AND n.einwilligung_eltern IS NULL;

DELETE FROM buchung AS b
USING nutzer AS n
WHERE n.nutzer_id = b.mitfahrer_id
  AND date_part('year', age(CURRENT_DATE, n.geburtsdatum)) < 18
  AND n.einwilligung_eltern IS NULL;

DELETE FROM buchung
WHERE mitfahrer_id IN (SELECT nutzer_id
                       FROM nutzer
                       WHERE MONTHS_BETWEEN(TRUNC(SYSDATE), geburtsdatum) < 18 * 12
                         AND einwilligung_eltern IS NULL);

DELETE FROM buchung
WHERE mitfahrer_id IN (SELECT nutzer_id
                       FROM nutzer
                       WHERE date(geburtsdatum, '+18 years') > date('now')
                         AND einwilligung_eltern IS NULL);

DELETE FROM buchung
WHERE mitfahrer_id IN (SELECT nutzer_id
                       FROM nutzer
                       WHERE TIMESTAMPDIFF(YEAR, geburtsdatum, CURDATE()) < 18
                         AND einwilligung_eltern IS NULL);

Aufgabe 4: Aktivitäten einer Freizeit-App auswerten (Stufe 3 – Profilkompetenz)

(ca. 29 min Bearbeitungszeit)

Du bist Auszubildende*r bei der Let’s Meet GmbH in Bielefeld. Die App bringt Menschen für gemeinsame Freizeitaktivitäten zusammen. Für das Quartalsgespräch möchte Geschäftsführerin Clara Neumann wissen, wie die Aktivitäten angenommen werden. UX-Designerin Anh Nguyen arbeitet zusätzlich mit exportierten CSV-Dateien in Python. Ausschnitt aus dem Datenmodell:

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

• hobby (hobby_id, bezeichnung, kategorie)

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

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

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

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

aktivitaet.status ist geplant, abgesagt oder stattgefunden; teilnahme.status ist angemeldet, warteliste, abgemeldet, teilgenommen oder nicht_erschienen. bewerter_id verweist auf benutzer; bewerten kann nur, wer teilgenommen hat. beginn und angemeldet_am sind Zeitpunkte (DATETIME).

a) Clara Neumann ist keine Informatikerin. Erkläre ihr das Konzept hinter einem JOIN und den Unterschied zwischen einem inneren und einem linken äußeren Verbund am Beispiel der Tabellen aktivitaet und bewertung. (4 Punkte)

b) Erstelle eine SQL-Abfrage, die für alle nicht abgesagten Aktivitäten den Titel, die Bezeichnung des Hobbys, die Anzahl der Bewertungen und den Durchschnitt der Sterne (eine Nachkommastelle) ausgibt. Aktivitäten ohne Bewertung sollen mit der Anzahl 0 erscheinen. Sortiere nach dem Durchschnitt absteigend und bei Gleichstand nach dem Titel. (6 Punkte)

SELECT a.titel, h.bezeichnung AS hobby,
       COUNT(b.bewertung_id) AS bewertungen,
       ROUND(AVG(b.sterne), 1) AS durchschnitt
FROM aktivitaet AS a
  INNER JOIN hobby AS h ON h.hobby_id = a.hobby_id
  LEFT JOIN bewertung AS b ON b.aktivitaet_id = a.aktivitaet_id
WHERE a.status <> 'abgesagt'
GROUP BY a.aktivitaet_id, a.titel, h.bezeichnung
ORDER BY durchschnitt DESC, a.titel;

c) Für das Jugendschutzkonzept will Clara wissen, an welchen Treffpunkten schon Aktivitäten mit minderjährigen Teilnehmenden stattfanden oder geplant sind. Erstelle eine SQL-Abfrage, die Name und Ort dieser Treffpunkte jeweils nur einmal ausgibt. Berücksichtige Teilnahmen mit dem Status angemeldet oder teilgenommen; minderjährig ist, wer zu Beginn der Aktivität noch keine 18 Jahre alt ist. (5 Punkte)

SELECT DISTINCT tp.name, tp.ort
FROM treffpunkt AS tp
  INNER JOIN aktivitaet AS a ON a.treffpunkt_id = tp.treffpunkt_id
  INNER JOIN teilnahme AS t ON t.aktivitaet_id = a.aktivitaet_id
  INNER JOIN benutzer AS u ON u.benutzer_id = t.benutzer_id
WHERE t.status IN ('angemeldet', 'teilgenommen')
  AND TIMESTAMPDIFF(YEAR, u.geburtsdatum, a.beginn) < 18;

d) Clara vermutet, dass sich Menschen für Sportangebote kurzfristiger anmelden als für andere Aktivitäten. Erstelle eine SQL-Abfrage, die je Hobby-Kategorie die Anzahl der Anmeldungen (Status angemeldet oder teilgenommen) und den durchschnittlichen Vorlauf in Stunden zwischen Anmeldung und Beginn der Aktivität (eine Nachkommastelle) ausgibt, sortiert nach dem Vorlauf absteigend. (6 Punkte)

SELECT h.kategorie, COUNT(*) AS anmeldungen,
       ROUND(AVG(TIMESTAMPDIFF(MINUTE, t.angemeldet_am, a.beginn)) / 60, 1) AS vorlauf_h
FROM teilnahme AS t
  INNER JOIN aktivitaet AS a ON a.aktivitaet_id = t.aktivitaet_id
  INNER JOIN hobby AS h ON h.hobby_id = a.hobby_id
WHERE t.status IN ('angemeldet', 'teilgenommen')
GROUP BY h.kategorie
ORDER BY vorlauf_h DESC;

SELECT h.kategorie, COUNT(*) AS anmeldungen,
       ROUND(AVG(CAST(DATEDIFF(MINUTE, t.angemeldet_am, a.beginn) AS DECIMAL(10,2))) / 60, 1) AS vorlauf_h
FROM teilnahme AS t
  INNER JOIN aktivitaet AS a ON a.aktivitaet_id = t.aktivitaet_id
  INNER JOIN hobby AS h ON h.hobby_id = a.hobby_id
WHERE t.status IN ('angemeldet', 'teilgenommen')
GROUP BY h.kategorie
ORDER BY vorlauf_h DESC;

SELECT h.kategorie, COUNT(*) AS anmeldungen,
       ROUND(CAST(AVG(EXTRACT(EPOCH FROM (a.beginn - t.angemeldet_am))) / 3600 AS NUMERIC), 1) AS vorlauf_h
FROM teilnahme AS t
  INNER JOIN aktivitaet AS a ON a.aktivitaet_id = t.aktivitaet_id
  INNER JOIN hobby AS h ON h.hobby_id = a.hobby_id
WHERE t.status IN ('angemeldet', 'teilgenommen')
GROUP BY h.kategorie
ORDER BY vorlauf_h DESC;

SELECT h.kategorie, COUNT(*) AS anmeldungen,
       ROUND(AVG((CAST(a.beginn AS DATE) - CAST(t.angemeldet_am AS DATE)) * 24), 1) AS vorlauf_h
FROM teilnahme t
  INNER JOIN aktivitaet a ON a.aktivitaet_id = t.aktivitaet_id
  INNER JOIN hobby h ON h.hobby_id = a.hobby_id
WHERE t.status IN ('angemeldet', 'teilgenommen')
GROUP BY h.kategorie
ORDER BY vorlauf_h DESC;

SELECT h.kategorie, COUNT(*) AS anmeldungen,
       ROUND(AVG((julianday(a.beginn) - julianday(t.angemeldet_am)) * 24), 1) AS vorlauf_h
FROM teilnahme AS t
  INNER JOIN aktivitaet AS a ON a.aktivitaet_id = t.aktivitaet_id
  INNER JOIN hobby AS h ON h.hobby_id = a.hobby_id
WHERE t.status IN ('angemeldet', 'teilgenommen')
GROUP BY h.kategorie
ORDER BY vorlauf_h DESC;

e) Anh Nguyen hat die Tabellen teilnahme und bewertung als CSV-Dateien exportiert. Sie möchte alle Teilnahmen mit der Bewertung derselben Person für dieselbe Aktivität zusammenführen und anschließend die gut bewerteten Teilnahmen herausfiltern. Ihr Skript:

1  import pandas as pd
2  teilnahme = pd.read_csv("teilnahme.csv")   # aktivitaet_id, benutzer_id, angemeldet_am, status
3  bewertung = pd.read_csv("bewertung.csv")   # bewertung_id, aktivitaet_id, bewerter_id, sterne, kommentar
4  daten = pd.merge(teilnahme, bewertung, on="aktivitaet_id", how="inner")
5  gut = daten[daten.sterne >= 4 and daten.status == "teilgenommen"]

Das Ergebnis von Zeile 4 hat 24 Zeilen, obwohl es nur 23 Teilnahmen gibt; Zeile 5 bricht mit einem ValueError ab.

ea) Erläutere zwei Fehler in Zeile 4. (4 Punkte)

eb) Korrigiere die Zeilen 4 und 5. (4 Punkte)

ec) Gib die SQL-Abfrage an, die der korrigierten Zeile 4 entspricht. (3 Punkte)

4  daten = pd.merge(teilnahme, bewertung,
                    left_on=["aktivitaet_id", "benutzer_id"],
                    right_on=["aktivitaet_id", "bewerter_id"], how="left")
5  gut = daten[(daten.sterne >= 4) & (daten.status == "teilgenommen")]

SELECT t.*, b.bewertung_id, b.sterne, b.kommentar
FROM teilnahme AS t
  LEFT JOIN bewertung AS b ON b.aktivitaet_id = t.aktivitaet_id
                          AND b.bewerter_id = t.benutzer_id;

Aufgabe 5: Lieferzeiten eines Fahrrad-Onlineshops analysieren (Stufe 3 – Profilkompetenz)

(ca. 25 min Bearbeitungszeit)

Du bist Auszubildende*r bei der CSB-Rad GmbH in Bielefeld. Kund*innen haben sich über lange Lieferzeiten beschwert. Geschäftsführer Cem Sarıkaya bittet Dich, die Bestell- und Versanddaten auszuwerten, bevor er mit den Dienstleistern spricht. Ausschnitt aus dem Datenmodell:

• kunde (kunde_id, kundentyp, firma, vorname, nachname, email, …)

• bestellung (bestellung_id, #kunde_id, bestellt_am, status, zahlungsart, …)

• bestellposition (#bestellung_id, pos_nr, #artikel_id, menge, einzelpreis_netto, …)

• artikel (artikel_id, artikelnr, bezeichnung, …)

• rechnung (rechnung_id, rechnungsnr, #bestellung_id, rechnungsdatum, …)

• versand (versand_id, #bestellung_id, dienstleister, sendungsnr, versandt_am, zugestellt_am)

kundentyp ist privat, haendler oder oeffentlich; firma ist nur bei Händlern und öffentlichen Auftraggebern gefüllt. bestellt_am, versandt_am und zugestellt_am sind Zeitpunkte (DATETIME); zugestellt_am bleibt leer, solange die Sendung unterwegs ist.

a) Erstelle eine SQL-Abfrage, die für alle zugestellten Sendungen die Bestellnummer, den Dienstleister und die Lieferdauer in Tagen vom Bestellzeitpunkt bis zur Zustellung (eine Nachkommastelle) ausgibt, die längste Lieferdauer zuerst. (5 Punkte)

SELECT b.bestellung_id, v.dienstleister,
       ROUND(TIMESTAMPDIFF(MINUTE, b.bestellt_am, v.zugestellt_am) / 1440, 1) AS lieferdauer_tage
FROM bestellung AS b
  INNER JOIN versand AS v ON v.bestellung_id = b.bestellung_id
WHERE v.zugestellt_am IS NOT NULL
ORDER BY lieferdauer_tage DESC;

SELECT b.bestellung_id, v.dienstleister,
       ROUND(DATEDIFF(MINUTE, b.bestellt_am, v.zugestellt_am) / 1440.0, 1) AS lieferdauer_tage
FROM bestellung AS b
  INNER JOIN versand AS v ON v.bestellung_id = b.bestellung_id
WHERE v.zugestellt_am IS NOT NULL
ORDER BY lieferdauer_tage DESC;

SELECT b.bestellung_id, v.dienstleister,
       ROUND(CAST(EXTRACT(EPOCH FROM (v.zugestellt_am - b.bestellt_am)) / 86400 AS NUMERIC), 1) AS lieferdauer_tage
FROM bestellung AS b
  INNER JOIN versand AS v ON v.bestellung_id = b.bestellung_id
WHERE v.zugestellt_am IS NOT NULL
ORDER BY lieferdauer_tage DESC;

SELECT b.bestellung_id, v.dienstleister,
       ROUND((CAST(v.zugestellt_am AS DATE) - CAST(b.bestellt_am AS DATE)), 1) AS lieferdauer_tage
FROM bestellung b
  INNER JOIN versand v ON v.bestellung_id = b.bestellung_id
WHERE v.zugestellt_am IS NOT NULL
ORDER BY lieferdauer_tage DESC;

SELECT b.bestellung_id, v.dienstleister,
       ROUND((julianday(v.zugestellt_am) - julianday(b.bestellt_am)), 1) AS lieferdauer_tage
FROM bestellung AS b
  INNER JOIN versand AS v ON v.bestellung_id = b.bestellung_id
WHERE v.zugestellt_am IS NOT NULL
ORDER BY lieferdauer_tage DESC;

b) Erstelle eine SQL-Abfrage, die je Kundentyp die Anzahl der zugestellten Lieferungen und die durchschnittliche Lieferdauer in Tagen (eine Nachkommastelle) ausgibt, die kürzeste zuerst. (5 Punkte)

SELECT k.kundentyp, COUNT(*) AS lieferungen,
       ROUND(AVG(TIMESTAMPDIFF(MINUTE, b.bestellt_am, v.zugestellt_am)) / 1440, 1) AS durchschnitt_tage
FROM kunde AS k
  INNER JOIN bestellung AS b ON b.kunde_id = k.kunde_id
  INNER JOIN versand AS v ON v.bestellung_id = b.bestellung_id
WHERE v.zugestellt_am IS NOT NULL
GROUP BY k.kundentyp
ORDER BY durchschnitt_tage;

SELECT k.kundentyp, COUNT(*) AS lieferungen,
       ROUND(AVG(CAST(DATEDIFF(MINUTE, b.bestellt_am, v.zugestellt_am) AS DECIMAL(12,2))) / 1440, 1) AS durchschnitt_tage
FROM kunde AS k
  INNER JOIN bestellung AS b ON b.kunde_id = k.kunde_id
  INNER JOIN versand AS v ON v.bestellung_id = b.bestellung_id
WHERE v.zugestellt_am IS NOT NULL
GROUP BY k.kundentyp
ORDER BY durchschnitt_tage;

SELECT k.kundentyp, COUNT(*) AS lieferungen,
       ROUND(CAST(AVG(EXTRACT(EPOCH FROM (v.zugestellt_am - b.bestellt_am))) / 86400 AS NUMERIC), 1) AS durchschnitt_tage
FROM kunde AS k
  INNER JOIN bestellung AS b ON b.kunde_id = k.kunde_id
  INNER JOIN versand AS v ON v.bestellung_id = b.bestellung_id
WHERE v.zugestellt_am IS NOT NULL
GROUP BY k.kundentyp
ORDER BY durchschnitt_tage;

SELECT k.kundentyp, COUNT(*) AS lieferungen,
       ROUND(AVG(CAST(v.zugestellt_am AS DATE) - CAST(b.bestellt_am AS DATE)), 1) AS durchschnitt_tage
FROM kunde k
  INNER JOIN bestellung b ON b.kunde_id = k.kunde_id
  INNER JOIN versand v ON v.bestellung_id = b.bestellung_id
WHERE v.zugestellt_am IS NOT NULL
GROUP BY k.kundentyp
ORDER BY durchschnitt_tage;

SELECT k.kundentyp, COUNT(*) AS lieferungen,
       ROUND(AVG(julianday(v.zugestellt_am) - julianday(b.bestellt_am)), 1) AS durchschnitt_tage
FROM kunde AS k
  INNER JOIN bestellung AS b ON b.kunde_id = k.kunde_id
  INNER JOIN versand AS v ON v.bestellung_id = b.bestellung_id
WHERE v.zugestellt_am IS NOT NULL
GROUP BY k.kundentyp
ORDER BY durchschnitt_tage;

c) Für das Gespräch braucht Cem eine Übersicht aller nicht stornierten Bestellungen mit Bestellnummer, Kundenname (bei Firmen der Firmenname, sonst der Nachname), Status, Rechnungsnummer, Dienstleister und Zustellzeitpunkt. Bestellungen ohne Rechnung oder ohne Versand sollen ebenfalls erscheinen. Erstelle die SQL-Abfrage, sortiert nach Bestellnummer. (6 Punkte)

SELECT b.bestellung_id, COALESCE(k.firma, k.nachname) AS kunde, b.status,
       r.rechnungsnr, v.dienstleister, v.zugestellt_am
FROM bestellung AS b
  INNER JOIN kunde AS k ON k.kunde_id = b.kunde_id
  LEFT JOIN rechnung AS r ON r.bestellung_id = b.bestellung_id
  LEFT JOIN versand AS v ON v.bestellung_id = b.bestellung_id
WHERE b.status <> 'storniert'
ORDER BY b.bestellung_id;

d) Erstelle eine SQL-Abfrage, die die Artikelnummer und Bezeichnung aller Artikel ausgibt, die schon in nicht stornierten Bestellungen öffentlicher Auftraggeber enthalten waren – jeden Artikel nur einmal, sortiert nach Artikelnummer. (4 Punkte)

SELECT DISTINCT a.artikelnr, a.bezeichnung
FROM kunde AS k
  INNER JOIN bestellung AS b ON b.kunde_id = k.kunde_id
  INNER JOIN bestellposition AS p ON p.bestellung_id = b.bestellung_id
  INNER JOIN artikel AS a ON a.artikel_id = p.artikel_id
WHERE k.kundentyp = 'oeffentlich' AND b.status <> 'storniert'
ORDER BY a.artikelnr;

e) Erstelle eine SQL-Abfrage, die alle Sendungen ermittelt, die heute (siehe Hinweise zur Bearbeitung) seit mehr als fünf Tagen unterwegs und noch nicht zugestellt sind. Auszugeben sind Bestellnummer, Vor- und Nachname der Kundin bzw. des Kunden, Sendungsnummer und die Anzahl der vollen Tage seit dem Versand. (4 Punkte)

SELECT b.bestellung_id, k.vorname, k.nachname, v.sendungsnr,
       TIMESTAMPDIFF(DAY, v.versandt_am, CURDATE()) AS tage_unterwegs
FROM versand AS v
  INNER JOIN bestellung AS b ON b.bestellung_id = v.bestellung_id
  INNER JOIN kunde AS k ON k.kunde_id = b.kunde_id
WHERE v.zugestellt_am IS NULL
  AND v.versandt_am < CURDATE() - INTERVAL 5 DAY;

SELECT b.bestellung_id, k.vorname, k.nachname, v.sendungsnr,
       DATEDIFF(DAY, v.versandt_am, CAST(GETDATE() AS DATE)) AS tage_unterwegs
FROM versand AS v
  INNER JOIN bestellung AS b ON b.bestellung_id = v.bestellung_id
  INNER JOIN kunde AS k ON k.kunde_id = b.kunde_id
WHERE v.zugestellt_am IS NULL
  AND v.versandt_am < DATEADD(DAY, -5, CAST(GETDATE() AS DATE));

SELECT b.bestellung_id, k.vorname, k.nachname, v.sendungsnr,
       EXTRACT(DAY FROM CURRENT_DATE - v.versandt_am) AS tage_unterwegs
FROM versand AS v
  INNER JOIN bestellung AS b ON b.bestellung_id = v.bestellung_id
  INNER JOIN kunde AS k ON k.kunde_id = b.kunde_id
WHERE v.zugestellt_am IS NULL
  AND v.versandt_am < CURRENT_DATE - INTERVAL '5 days';

SELECT b.bestellung_id, k.vorname, k.nachname, v.sendungsnr,
       TRUNC(TRUNC(SYSDATE) - CAST(v.versandt_am AS DATE)) AS tage_unterwegs
FROM versand v
  INNER JOIN bestellung b ON b.bestellung_id = v.bestellung_id
  INNER JOIN kunde k ON k.kunde_id = b.kunde_id
WHERE v.zugestellt_am IS NULL
  AND v.versandt_am < TRUNC(SYSDATE) - 5;

SELECT b.bestellung_id, k.vorname, k.nachname, v.sendungsnr,
       CAST(julianday(date('now')) - julianday(v.versandt_am) AS INTEGER) AS tage_unterwegs
FROM versand AS v
  INNER JOIN bestellung AS b ON b.bestellung_id = v.bestellung_id
  INNER JOIN kunde AS k ON k.kunde_id = b.kunde_id
WHERE v.zugestellt_am IS NULL
  AND v.versandt_am < date('now', '-5 days');

f) Natalia Wróbel aus der Buchhaltung schlägt vor, die Tabellen künftig als CSV-Dateien zu exportieren und in der Tabellenkalkulation mit einer Verweisfunktion (z. B. XVERWEIS) zusammenzuführen, „dann brauchen wir kein SQL“. Nimm Stellung zu diesem Vorschlag. (3 Punkte)

Anlage: SQL-Kurzreferenz (MariaDB)

Auszug der in diesen Aufgaben nützlichen Sprachelemente. Eckige Klammern kennzeichnen optionale Teile.

Links und weitere Informationen

• Artikel „SQL: Abfragen über mehrere Tabellen mit JOIN“ auf oer-informatik.de

• Der logische Datenbankentwurf mit dem Relationenmodell

• SQL: Aggregierende Funktionen

• SQL-Übungsaufgaben auf oer-informatik.de

• als Nachschlagewerk: MariaDB Knowledge Base: JOIN Syntax

• als Nachschlagewerk: MariaDB Knowledge Base: UPDATE (mehrere Tabellen)

• als Nachschlagewerk: MariaDB Knowledge Base: DELETE (mehrere Tabellen)

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-Abfragen über mehrere Tabellen (JOIN)” von oer-informatik.de (H. Stein), Lizenz: CC BY 4.0. Der Artikel wurde unter https://oer-informatik.de/sql-join-uebungsaufgaben veröffentlicht, die Quelltexte sind in weiterverarbeitbarer Form verfügbar im Repository unter https://gitlab.com/oer-informatik/db-sql/dml-select-queries. Erstellt mit KI-Assistenz; von mir umfassend redaktionell überarbeitet, geprüft und freigegeben. Stand: 26.10.2026.

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