SQL

Die vorliegenden Materialien wurden von Daniel Hoherz und André Tempel erstellt. Sollten andere Editoren die Materialien erstellt haben, werden diese explizit genannt.
SELECT [DISTINCT] … FROM tabelle1 [t1], tabelle2 [t2], … [WHERE …] [GROUP BY … [HAVING …]] [ORDER BY … [ASC|DESC]] [LIMIT anzahl], die Operatoren + - * /, = != > < >= <=, NOT, IS NULL, IN, BETWEEN, LIKE und die Aggregatfunktionen AVG, COUNT, MAX, MIN, SUM. Tabellen werden über eine Bedingung in der WHERE-Klausel verbunden (Schreibweise ohne JOIN). Abschnitte mit Vertiefung gehen darüber hinaus: JOIN … ON, LEFT/RIGHT JOIN, geschachtelte Abfragen und Funktionen wie LENGTH oder ROUND.Wiederholung zu Datenbanken
In Jahrgang 10 wurden erste Grundlagen zu:
- Datenbankentwurf
- Informationen mit SQL aus einer Datenbank gewinnen
eingeführt. Unsere Datenbank für die Videoplattform sah final so aus:
| AName | Adresse | Lebensalter | |
|---|---|---|---|
| FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 |
| AmirA | amir.ali | Allee 10, 50667 Köln | 24 |
| AishaB | aisha.ben | Straße 5, 10115 Berlin | 20 |
| NoahS | noah.schmidt | Schulstraße 12, 80331 München | 25 |
| EmmaT | emma.taylor | Gartenstraße 8, 50667 Köln | 21 |
| LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 |
| LaraM2 | lara.meier | Lindenweg 5, 20257 Hamburg | 19 |
| LeoK | leo.klein | Hauptstraße 1, 10115 Berlin | 22 |
| … | … | … | … |
| KID | KText | KDatum | Rating | ↑AName | ↑VID |
|---|---|---|---|---|---|
| 1 | Toller Vlog, Leo! | 20251002 | 5 | LaraM | 6 |
| 2 | Ich liebe vegane Pasta! | 20251006 | 4 | FinnW | 2 |
| 3 | Das Rezept klingt lecker, Noah! | 20251011 | 4 | AishaB | 2 |
| 4 | Wunderschöne Kunstwerke, Emma! | 20251013 | 5 | LaraM | 4 |
| 5 | Das Workout war super, Finn! | 20251016 | 3 | EmmaT | 5 |
| 6 | Barcelona sieht toll aus! | 20251018 | 2 | LeoK | 6 |
| 7 | Toller Einblick in die Kultur! | 20251021 | 4 | LeoK | 1 |
| 8 | Sehr informativ, danke Aisha! | 20251023 | 4 | LaraM2 | 3 |
| … | … | … | … | … | … |
| CID | CName | CBeschreibung | ↑AName |
|---|---|---|---|
| 1 | Aisha’s Tech Hub | Neueste Technik und Gadgets | AishaB |
| 2 | Aisha’s Musik | What’s hot? | AishaB |
| 3 | Gaming mit Aisha | Endgegner! | AishaB |
| 4 | Emma’s Creations | Kreative Kunst und DIY-Projekte | EmmaT |
| 5 | Amir’s World | Kulturelle Einblicke und Reisen | AmirA |
| 6 | Lara’s Lifestyle | Lifestyle und persönliche Einblicke | LaraM2 |
| 7 | Finn’s Reisen | Super Kulissen für Bilder | FinnW |
| 8 | Lara’s Kitchen | Leckere Rezepte und Tipps | LaraM |
| 9 | Noah’s Vlogs | Einblicke in die Kultur und Lifestyle | NoahS |
| 10 | Finn’s Fitness | Fitness und Gesundheitstipps | FinnW |
| 11 | Cats&Emma | How cute | EmmaT |
| 12 | Leo’s Travels | Abenteuer und Reisen | LeoK |
| … | … | … | … |
| VID | VTitel | VBeschreibung | VDatum |
|---|---|---|---|
| 1 | Abenteuer in den Alpen | Ein aufregender Trip in die Berge | 20251001 |
| 2 | Vegane Pasta selbst gemacht | Schritt-für-Schritt-Anleitung für Pasta | 20251005 |
| 3 | Aisha’s Smartphone Reviews | Vergleich der neuesten Modelle | 20251010 |
| 4 | Malen für Anfänger | Tipps und Tricks für Einsteiger | 20251012 |
| 5 | 30-Minuten HIIT Workout | Effektives Training für zu Hause | 20251015 |
| 6 | Ein Tag in Barcelona | Vlog über meine Reise nach Barcelona | 20251017 |
| 7 | Amir in Istanbul | Kulturelle Erlebnisse in der Türkei | 20251020 |
| 8 | Noah’s Kochkünste | Traditionelle Gerichte aus dem Allgäu | 20251022 |
| … | … | … | … |
| ↑CID | ↑VID |
|---|---|
| 1 | 3 |
| 8 | 2 |
| 4 | 4 |
| … | … |
Tabellendarstellung
Videos(VID, VTitel, VBeschreibung, VDatum)
Kommentare(KID, KText, KDatum, Rating, ↑AName, ↑VID)
Channels(CID, CName, CBeschreibung, ↑AName)
ChannelContent(↑CID, ↑VID)
Aufgaben: Die Datenbank von „Bella Data“
Das junge Unternehmen „Bella Data“ möchte die Daten für verschiedene Filialen italienischer Restaurants verwalten.
Ein erster Datenbankentwurf ist in der unteren Abbildung zu sehen.
| BestellNr | Bestell | Filial | Filiale | Kunden | Pizza | Kategorie | Preis | Bewertungs | Bewertungs | Rating | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 10041 | 20251005 | Piazza Mitte | Marktstraße 12, 30159 Hannover | LenaM | lena.meyer | Margherita | Klassiker | 7.50 | „Immer leckere, knusprige Margherita!“ | 20251006 | 5 |
| 10042 | 20251010 | Piazza Mitte | Marktstraße 12, 30159 Hannover | AliA | ali.ahmed | Diavola | Scharf | 9.50 | „Schön scharf, genau richtig.“ | 20251011 | 4 |
| 10043 | 20251012 | Piazza Mitte | Marktstraße 12, 30159 Hannover | LenaM | lena.meyer | Vegan Garden | Vegan | 10.50 | „Gemüse frisch, Teig top.“ | 20251013 | 3 |
| 10044 | 20251017 | Piazza Nord | Lindenweg 7, 30165 Hannover | NoahS | noah.schmidt | Vegan Garden | Vegan | 10.50 | „Gute Auswahl für Veganer.“ | 20251018 | 4 |
| 10045 | 20251020 | Piazza Mitte | Marktstraße 12, 30159 Hannover | LenaM | lena.meyer | Margherita | Klassiker | 7.50 | „Heute etwas zu salzig.“ | 20251021 | 2 |
| … | … | … | … | … | … | … | … | … | … | … | … |
Untersuchen Sie die vorliegende Datenbank und erläutern Sie die folgenden Fachbegriffe:
- Datensatz
- Attribut
- Attributwert
Nennen Sie eine geeignete Kombination aus Attributen für einen Primärschlüssel und erläutern Sie die Bedeutung eines Primärschlüssels.
Entwickeln Sie je ein Beispiel für eine Einfüge-, Änderungs- und Löschanomalie und die daraus folgende Inkonsistenz.
Leiten Sie ein sinnvolles Mehrtabellen-Design ab: Listen Sie Tabellen mit Attributen auf, benennen Sie Primärschlüssel und benötigte Fremdschlüssel-Beziehungen. Begründen Sie kurz Ihre Schlüsselwahl.
Diskutieren Sie in 4–5 Sätzen Vorteile Ihres Mehrtabellen-Designs gegenüber der „All-in-One“-Tabelle.
Erstellen Sie ein relationales Schema und ein ER-Diagramm Ihrer neuen Datenbank.
Stellen Sie sich gegenseitig Ihre Ergebnisse vor und fügen Sie diese zu einer gemeinsamen Lösung zusammen.
Jetzt sollen aus einer verbesserten Variante der „Bella Data“-Datenbank Informationen mit SQL-Abfragen gewonnen werden.
Die zugrundeliegende Datenbank hat das folgende relationale Schema.
Filialen(FID, FName, Adresse, Stadt)
Pizzen(PID, PName, Beschreibung, Kategorie, Preis)
Bestellungen(BID, BDatum, Betrag, ↑KID, ↑FID)
Bestellpositionen(BPID, Menge, Preis, ↑PID, ↑BID)
Hinweis: Ein Datum ist in der Form YYYYMMDD gespeichert.
Entwickeln Sie für die folgenden gewünschten Informationen je eine geeignete SQL-Abfrage.
- Datum und Betrag aller Bestellungen, absteigend sortiert.
- Datum und Betrag der drei letzten Bestellungen.
- PID und Name aller Gerichte, die zur Kategorie „Pasta“ gehören.
- PID und Name aller Gerichte, die in ihrer Beschreibung „vegan“ enthalten.
- Alle verschiedenen Kategorien ohne Mehrfachnennung.
- BestellID und Datum aller Bestellungen vom Oktober 2025.
- Anzahl aller Kundinnen und Kunden, die jünger als 21 Jahre sind.
- FilialID und Name aller Filialen, deren Name mit P beginnt, alphabetisch sortiert.
ORDER BY … DESC, LIMIT, LIKE '%…%', DISTINCT, BETWEEN … AND …, COUNT(*).Tauschen Sie sich zu den Lösungen der beiden Teilgruppen aus.
🎉 Ausgezeichnet – Alle Aufgaben abgeschlossen!
Hier finden Sie eine mögliche Lösung zur Bella-Data-Aufgabe.
a) 1. Erläuterung der Fachbegriffe
- Datensatz: Ein Datensatz (oder Tupel) entspricht einer kompletten Zeile in der Tabelle. Er enthält alle zusammengehörigen Informationen zu einem spezifischen Vorgang. Beispiel: Die erste Zeile mit der BestellNr 10041 ist ein Datensatz, der aussagt, dass LenaM am 05.10.2025 eine Pizza Margherita in der Filiale Piazza Mitte für 7.50 gekauft hat.
- Attribut: Ein Attribut ist eine Eigenschaft oder ein Merkmal, das in einer Spalte der Tabelle steht. Es definiert, was gespeichert wird. Beispiel: PizzaName, Preis oder Email sind Attribute.
- Attributwert: Dies ist die konkrete Ausprägung eines Attributs in einem bestimmten Datensatz. Beispiel: „7.50“ ist der Attributwert des Attributs Preis im ersten Datensatz.
2. Primärschlüssel
- Geeignete Kombination: Da in der „All-in-One“-Tabelle eine Bestellnummer (BestellNr) theoretisch mehrere Pizzen enthalten könnte (auch wenn im Beispiel pro Zeile nur eine Pizza steht), wäre eine Kombination aus BestellNr und PizzaName am sichersten, um jede Zeile eindeutig zu identifizieren. Betrachtet man die Daten jedoch so, dass jede Zeile eine einzigartige Transaktion ist, könnte auch die BestellNr allein als Schlüssel dienen (sofern jede Position eine eigene Nr. bekommt).
- Bedeutung: Ein Primärschlüssel identifiziert jeden Datensatz in einer Tabelle eindeutig. Er stellt sicher, dass keine Zeile doppelt vorkommt und ermöglicht die eindeutige Referenzierung von Daten.
3. Anomalien (Beispiele anhand der Tabelle)
- Einfügeanomalie (Insert-Anomaly): Wir möchten eine neue Filiale „Piazza Süd“ anlegen. Das geht in dieser Tabelle nicht, solange dort noch kein Kunde eine Pizza bestellt hat, da wir sonst NULL-Werte bei z. B. BestellNr (Primärschlüsselattribut) eingeben müssten.
- Änderungsanomalie (Update-Anomaly): Die Filiale „Piazza Mitte“ zieht um. Wir müssen die Adresse („Marktstraße 12…“) nun in allen Zeilen ändern, in denen „Piazza Mitte“ vorkommt. Wird eine Zeile vergessen, sind die Daten inkonsistent (Widerspruch: gleiche Filiale, unterschiedliche Adressen).
- Löschanomalie (Delete-Anomaly): Kunde „AliA“ hat die einzige Bestellung der Pizza „Diavola“ getätigt. Wenn wir diesen Datensatz (Bestellung 10042) löschen, verlieren wir komplett die Information, dass es eine Pizza namens „Diavola“ gibt und was sie kostet.
4. Mehrtabellen-Design
- Tabelle Filialen – PS: FilialID; Attribute: FName, Adresse
- Tabelle Kunden – PS: KundenID; Attribute: Name, Email
- Tabelle Pizzen – PS: PizzaID; Attribute: PName, Kategorie, Preis
- Tabelle Bestellungen – PS: BestellNr; FS: KundenID, FilialID; Attribute: Datum, Betrag
- Tabelle Bestellpositionen (verbindet Bestellung und Pizza, inkl. Bewertung) – PS: BestellNr, PizzaID (zusammengesetzt); Attribute: BText, Rating, BDatum
5. Vorteile des Designs
Das Mehrtabellen-Design reduziert Redundanz, da Informationen wie die Adresse einer Filiale nur einmal gespeichert werden müssen und nicht bei jeder Bestellung wiederholt werden. Dies verhindert Inkonsistenzen bei Änderungen (z. B. Umzug der Filiale). Zudem wird Speicherplatz gespart. Schließlich lassen sich Daten flexibler hinzufügen (z. B. eine neue Pizza anlegen, ohne dass sie bestellt werden muss).
6. Relationales Schema und ER-Diagramm
Filialen(FilialID, FName, Adresse)
Pizzen(PizzaID, PName, Kategorie, Preis)
Bestellungen(BestellNr, Datum, Betrag, ↑KundenID, ↑FilialID)
Bestellpositionen(↑PizzaID, ↑BestellNr, BText, Rating, BDatum)
b) Individuelle Zusammenführung der Gruppenergebnisse.
c) SQL-Abfragen
1. Datum und Betrag aller Bestellungen, absteigend sortiert
SELECT BDatum, Betrag
FROM Bestellungen
ORDER BY BDatum DESC;2. Datum und Betrag der drei letzten Bestellungen
SELECT BDatum, Betrag
FROM Bestellungen
ORDER BY BDatum DESC
LIMIT 3;3. PID und Name aller Gerichte, die zur Kategorie „Pasta“ gehören
SELECT PID, PName
FROM Pizzen
WHERE Kategorie = 'Pasta';4. PID und Name aller Gerichte, die in ihrer Beschreibung „vegan“ enthalten
SELECT PID, PName
FROM Pizzen
WHERE Beschreibung LIKE '%vegan%';5. Alle verschiedenen Kategorien ohne Mehrfachnennung
SELECT DISTINCT Kategorie
FROM Pizzen;6. BestellID und Datum aller Bestellungen vom Oktober 2025
SELECT BID, BDatum
FROM Bestellungen
WHERE BDatum BETWEEN 20251001 AND 20251031;
-- Alternativ bei String-Speicherung: WHERE BDatum LIKE '202510%'7. Anzahl aller Kundinnen und Kunden, die jünger als 21 Jahre sind
SELECT COUNT(*)
FROM Kunden
WHERE Lebensalter < 21;8. FilialID und Name aller Filialen, deren Name mit P beginnt, alphabetisch sortiert
SELECT FID, FName
FROM Filialen
WHERE FName LIKE 'P%'
ORDER BY FName ASC;d) Individueller Austausch der Teilgruppen.
SQL (Structured Query Language): Verbund (Inner Join)
In den meisten Datenbanksystemen sind die Daten auf mehrere Tabellen verteilt, welche durch entsprechende Attribute, die Fremdschlüssel, miteinander verknüpft sind.
Möchte eine Nutzerin/ein Nutzer Informationen aus einer Datenbank generieren, die Datensätze aus mehreren Tabellen involvieren, kann dies über genau diese gemeinsamen Attribute passieren.
In SQL geht dies mit einem „Inner Join“ (Verbund). Es werden hier zwei gleichwertige Schreibweisen für einen Join vorgestellt: mit INNER JOIN … ON und mit mehreren Tabellen im FROM-Teil und der Verknüpfungsbedingung in der WHERE-Klausel. In den Abituraufgaben wird die zweite Schreibweise verwendet.
Wenn es zu einem Datensatz keinen zu dem gemeinsamen Attribut passenden in einer anderen Tabelle gibt, werden diese Ergebnisse nicht aufgelistet.
Zunächst ein konkretes Beispiel mit zwei Tabellen aus unserer Datenbank zu „WeTube“:
| AccountID | Username | Adresse | Lebensalter | |
|---|---|---|---|---|
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 |
| ChannelID | Channel | Channel | ↑AccountID |
|---|---|---|---|
| 7 | Finn’s Reisen | Super Kulissen für Bilder | 1 |
| 8 | Lara’s Kitchen | Leckere Rezepte und Tipps | 6 |
| 10 | Finn’s Fitness | Fitness und Gesundheitstipps | 1 |
Das Datenbankmanagementsystem geht bei einer Verbundabfrage über mehrere Tabellen wie folgt vor. Ohne Verknüpfungsbedingung liefern die beiden folgenden Schreibweisen dasselbe Ergebnis; in den Abituraufgaben wird die linke verwendet:
SELECT *
FROM Accounts, Channels;SELECT *
FROM Accounts CROSS JOIN Channels;Jetzt wird vom DBMS zunächst ein sogenanntes Kreuzprodukt aus den beteiligten Tabellen gebildet. Das bedeutet, dass jeder Datensatz aus der einen Tabelle mit allen aus der anderen zusammengefügt wird.
| Accounts. | Username | Adresse | Lebensalter | ChannelID | Channel | Channel | Channels. | |
|---|---|---|---|---|---|---|---|---|
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 7 | Finn’s Reisen | Super Kulissen für Bilder | 1 |
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 8 | Lara’s Kitchen | Leckere Rezepte und Tipps | 6 |
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 10 | Finn’s Fitness | Fitness und Gesundheitstipps | 1 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 | 7 | Finn’s Reisen | Super Kulissen für Bilder | 1 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 | 8 | Lara’s Kitchen | Leckere Rezepte und Tipps | 6 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 | 10 | Finn’s Fitness | Fitness und Gesundheitstipps | 1 |
Gibt es zusätzlich noch Filterbedingungen, also Selektionen, werden die Datensätze des Kreuzproduktes noch weiter gefiltert. Betrachten wir zwei gleichwertige Schreibweisen einer Selektion:
SELECT *
FROM Accounts INNER JOIN Channels
ON Accounts.AccountID = Channels.AccountID;SELECT *
FROM Accounts, Channels
WHERE Accounts.AccountID = Channels.AccountID;Um die verschiedenen AccountIDs unterscheiden zu können, schreibt man den Namen der Tabelle davor, gefolgt von einem Punkt: Accounts.AccountID bzw. Channels.AccountID.
Nun prüft das DBMS, welche Datensätze die Bedingung ON Accounts.AccountID = Channels.AccountID bzw. WHERE Accounts.AccountID = Channels.AccountID erfüllen:
| Accounts. | Username | Adresse | Lebensalter | ChannelID | Channel | Channel | Channels. | |
|---|---|---|---|---|---|---|---|---|
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 7 | Finn’s Reisen | Super Kulissen für Bilder | 1 |
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 8 | Lara’s Kitchen | Leckere Rezepte und Tipps | 6 |
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 10 | Finn’s Fitness | Fitness und Gesundheitstipps | 1 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 | 7 | Finn’s Reisen | Super Kulissen für Bilder | 1 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 | 8 | Lara’s Kitchen | Leckere Rezepte und Tipps | 6 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 | 10 | Finn’s Fitness | Fitness und Gesundheitstipps | 1 |
grün: Bedingung erfüllt – rot: Bedingung nicht erfüllt
Daraus ergibt sich nun die Ausgabe:
| Accounts. | Username | Adresse | Lebensalter | ChannelID | Channel | Channel | Channels. | |
|---|---|---|---|---|---|---|---|---|
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 7 | Finn’s Reisen | Super Kulissen für Bilder | 1 |
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 10 | Finn’s Fitness | Fitness und Gesundheitstipps | 1 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 | 8 | Lara’s Kitchen | Leckere Rezepte und Tipps | 6 |
Zusammenfassung: Inner Join
Insgesamt liefert unser „Inner Join“ von Accounts und Channels:
| AccountID | Username | Adresse | Lebensalter | |
|---|---|---|---|---|
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 |
| ChannelID | Channel | Channel | ↑AccountID |
|---|---|---|---|
| 7 | Finn’s Reisen | Super Kulissen für Bilder | 1 |
| 8 | Lara’s Kitchen | Leckere Rezepte und Tipps | 6 |
| 10 | Finn’s Fitness | Fitness und Gesundheitstipps | 1 |
SELECT *
FROM Accounts INNER JOIN Channels
ON Accounts.AccountID = Channels.AccountID;SELECT *
FROM Accounts, Channels
WHERE Accounts.AccountID = Channels.AccountID;| Accounts. | Username | Adresse | Lebensalter | ChannelID | Channel | Channel | Channels. | |
|---|---|---|---|---|---|---|---|---|
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 7 | Finn’s Reisen | Super Kulissen für Bilder | 1 |
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 10 | Finn’s Fitness | Fitness und Gesundheitstipps | 1 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 | 8 | Lara’s Kitchen | Leckere Rezepte und Tipps | 6 |
Verbund (Left und Right Join)
Zusätzlich zum „Inner Join“ gibt es noch einseitige Joins, die gelegentlich nützlich sein können:
| AccountID | Username | Adresse | Lebensalter | |
|---|---|---|---|---|
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 |
| 3 | AishaB | aisha.ben | Straße 5, 10115 Berlin | 20 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 |
| ChannelID | Channel | Channel | ↑AccountID |
|---|---|---|---|
| 7 | Finn’s Reisen | Super Kulissen für Bilder | 1 |
| 8 | Lara’s Kitchen | Leckere Rezepte und Tipps | 6 |
| 10 | Finn’s Fitness | Fitness und Gesundheitstipps | 1 |
| 13 | Caitlyn’s Sports | Calisthenics | 4 |
Left Join
SELECT *
FROM Accounts LEFT JOIN Channels
ON Accounts.AccountID = Channels.AccountID;| Accounts. | Username | Adresse | Lebensalter | ChannelID | Channel | Channel | Channels. | |
|---|---|---|---|---|---|---|---|---|
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 7 | Finn’s Reisen | Super Kulissen für Bilder | 1 |
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 8 | Lara’s Kitchen | Leckere Rezepte und Tipps | 6 |
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 10 | Finn’s Fitness | Fitness und Gesundheitstipps | 1 |
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 13 | Caitlyn’s Sports | Calisthenics | 4 |
| 3 | AishaB | aisha.ben | Straße 5, 10115 Berlin | 20 | 7 | Finn’s Reisen | Super Kulissen für Bilder | 1 |
| 3 | AishaB | aisha.ben | Straße 5, 10115 Berlin | 20 | 8 | Lara’s Kitchen | Leckere Rezepte und Tipps | 6 |
| 3 | AishaB | aisha.ben | Straße 5, 10115 Berlin | 20 | 10 | Finn’s Fitness | Fitness und Gesundheitstipps | 1 |
| 3 | AishaB | aisha.ben | Straße 5, 10115 Berlin | 20 | 13 | Caitlyn’s Sports | Calisthenics | 4 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 | 7 | Finn’s Reisen | Super Kulissen für Bilder | 1 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 | 8 | Lara’s Kitchen | Leckere Rezepte und Tipps | 6 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 | 10 | Finn’s Fitness | Fitness und Gesundheitstipps | 1 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 | 13 | Caitlyn’s Sports | Calisthenics | 4 |
Beim „Left Join“ werden alle Datensätze der linken Tabelle ausgegeben, auch wenn es zu diesen keine passenden Datensätze der rechten Tabelle gibt:
| Accounts. | Username | Adresse | Lebensalter | ChannelID | Channel | Channel | Channels. | |
|---|---|---|---|---|---|---|---|---|
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 7 | Finn’s Reisen | Super Kulissen für Bilder | 1 |
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 10 | Finn’s Fitness | Fitness und Gesundheitstipps | 1 |
| 3 | AishaB | aisha.ben | Straße 5, 10115 Berlin | 20 | ||||
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 | 8 | Lara’s Kitchen | Leckere Rezepte und Tipps | 6 |
Right Join
Es gibt auch einen „Right Join„:
SELECT *
FROM Accounts RIGHT JOIN Channels
ON Accounts.AccountID = Channels.AccountID;| Accounts. | Username | Adresse | Lebensalter | ChannelID | Channel | Channel | Channels. | |
|---|---|---|---|---|---|---|---|---|
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 7 | Finn’s Reisen | Super Kulissen für Bilder | 1 |
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 8 | Lara’s Kitchen | Leckere Rezepte und Tipps | 6 |
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 10 | Finn’s Fitness | Fitness und Gesundheitstipps | 1 |
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 13 | Caitlyn’s Sports | Calisthenics | 4 |
| 3 | AishaB | aisha.ben | Straße 5, 10115 Berlin | 20 | 7 | Finn’s Reisen | Super Kulissen für Bilder | 1 |
| 3 | AishaB | aisha.ben | Straße 5, 10115 Berlin | 20 | 8 | Lara’s Kitchen | Leckere Rezepte und Tipps | 6 |
| 3 | AishaB | aisha.ben | Straße 5, 10115 Berlin | 20 | 10 | Finn’s Fitness | Fitness und Gesundheitstipps | 1 |
| 3 | AishaB | aisha.ben | Straße 5, 10115 Berlin | 20 | 13 | Caitlyn’s Sports | Calisthenics | 4 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 | 7 | Finn’s Reisen | Super Kulissen für Bilder | 1 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 | 8 | Lara’s Kitchen | Leckere Rezepte und Tipps | 6 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 | 10 | Finn’s Fitness | Fitness und Gesundheitstipps | 1 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 | 13 | Caitlyn’s Sports | Calisthenics | 4 |
Beim „Right Join“ werden alle Datensätze der rechten Tabelle ausgegeben, auch wenn es zu diesen keine passenden Datensätze der linken Tabelle gibt:
| Accounts. | Username | Adresse | Lebensalter | ChannelID | Channel | Channel | Channels. | |
|---|---|---|---|---|---|---|---|---|
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 7 | Finn’s Reisen | Super Kulissen für Bilder | 1 |
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 | 10 | Finn’s Fitness | Fitness und Gesundheitstipps | 1 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 | 8 | Lara’s Kitchen | Leckere Rezepte und Tipps | 6 |
| 13 | Caitlyn’s Sports | Calisthenics | 4 |
Leere Felder enthalten den Wert NULL.
Aufgaben zu SQL mit Joins
Gegeben sind folgende Ausschnitte aus der WeTube-Datenbank.
| AccountID | Username | Adresse | Lebensalter | |
|---|---|---|---|---|
| 1 | CarlS | carlS | Parkallee 2, 80331 München | 22 |
| 2 | FloraE | floraE | Kleiner Platz 5, 10115 Berlin | 28 |
| 3 | KimQ | kimQ | Schulstraße 1, 80331 München | 19 |
| 4 | FloW | floW | Lindenweg 8, 20257 Hamburg | 31 |
| ChannelID | Channel | Channel | ↑AccountID |
|---|---|---|---|
| 10 | FloraTech | Tech und Gadgets | 2 |
| 11 | CarlTravel | Reise und Vlog von Carl | 1 |
| 12 | KimLife | DIY | 3 |
| KommentarID | Kommentar | Erstellungs | Rating | ↑AccountID | ↑VideoID |
|---|---|---|---|---|---|
| 1001 | Super erklärt! Ich habe nichts vermisst. | 2025-10-06 | 5 | 1 | 101 |
| 1002 | Hilfreich, danke dir! | 2025-10-07 | 3 | 3 | 101 |
| 1003 | Toller Vlog! | 2025-15-22 | 4 | 4 | 102 |
| 1004 | Schön! | 2026-01-05 | 3 | 3 | 103 |
| VideoID | Titel | Beschreibung | Upload |
|---|---|---|---|
| 101 | Kamera-Review | Test der XCam | 2025-10-05 |
| 102 | Italien-Vlog | Rom und Toskana | 2025-12-20 |
| 103 | DIY-Deko | Papierblumen basteln | 2026-01-03 |
| ↑ChannelID | ↑VideoID |
|---|---|
| 10 | 101 |
| 11 | 102 |
| 12 | 103 |
Ermitteln Sie die Ergebnisrelation (Spalten und Zeilen), die bei den folgenden SQL-Abfragen ausgegeben wird.
SELECT Titel, UploadDatum
FROM Videos
WHERE UploadDatum BETWEEN '2025-10-01' AND '2025-12-31';SELECT a.Username, c.ChannelName
FROM Accounts a
INNER JOIN Channels c ON a.AccountID = c.AccountID;SELECT Titel, Beschreibung FROM Videos
UNION
SELECT ChannelName, ChannelBeschreibung FROM Channels;SELECT ch.ChannelName, v.Titel
FROM Channels ch
RIGHT JOIN ChannelContent cc ON ch.ChannelID = cc.ChannelID
RIGHT JOIN Videos v ON cc.VideoID = v.VideoID
WHERE v.UploadDatum < '2025-11-01';Entwerfen Sie jeweils zwei verschiedene SQL-Abfragen, die zu den unten gewünschten Ergebnissen führen.
- Titel und UploadDatum aller Videos aus Q4/2025 (01.10.2025 bis 31.12.2025), aufsteigend nach Datum sortiert.
- Username und ChannelName aller Kanäle, die von Accounts aus „München“ erstellt wurden.
- KommentarText zu VideoID = 101, absteigend sortiert nach ErstellungsDatum.
BETWEEN bzw. >= und <= oder mit INNER JOIN … ON bzw. mehreren Tabellen im FROM und einer WHERE-Bedingung.Entwerfen Sie jeweils eine SQL-Abfrage, die zu den unten gewünschten Ergebnissen führt.
- Geben Sie alle Videotitel zusammen mit dem Namen des Kanals aus, in dem sie veröffentlicht wurden.
- Geben Sie die Titel und das UploadDatum aller Videos aus, die nach dem 01.01.2026 hochgeladen wurden, zusammen mit dem Username des Kanalinhabers.
- Geben Sie die Titel und das UploadDatum der Videos aus, deren Beschreibung länger als 10 Zeichen ist, zusammen mit ChannelName und der Email des Kanalinhabers. Sortieren Sie das Ergebnis nach UploadDatum aufsteigend.
- Geben Sie Username und ChannelName aller Kanäle aus, deren Inhaber 18, 19 oder 20 Jahre alt sind. Verwenden Sie dafür den Operator
IN.
LENGTH(…).a.Lebensalter IN (18, 19, 20) prüft, ob der Wert in der Liste vorkommt – kürzer als drei mit OR verknüpfte Vergleiche.Gegeben ist die folgende SQL-Anweisung:
SELECT k.KommentarText, v.Titel, k.ErstellungsDatum
FROM Kommentare AS k
INNER JOIN Videos AS v
ON k.VideoID = v.VideoID;Erläutern Sie, welche Ausgabe mit dieser Anweisung erzielt werden soll und weshalb mehrere Tabellen notwendig sind.
Erstellen Sie die Tabelle des Kreuzproduktes nach dem Join INNER JOIN Videos AS v ON k.VideoID = v.VideoID und vor der eigentlichen Ausgabe.
Jetzt ist die folgende Anweisung gegeben:
SELECT ch.ChannelName, v.Titel, v.UploadDatum
FROM Channels AS ch
INNER JOIN ChannelContent AS cc
ON ch.ChannelID = cc.ChannelID
INNER JOIN Videos AS v
ON cc.VideoID = v.VideoID;Analysieren Sie die Anweisung und begründen Sie, dass zu jedem Kanal die Channelnamen, die Titel und das Uploaddatum aller Videos ausgegeben werden.
Erstellen Sie die Tabelle des Kreuzproduktes nach dem Join INNER JOIN ChannelContent AS cc ON ch.ChannelID = cc.ChannelID und vor dem zweiten Join.
Erstellen Sie die Tabelle des Kreuzproduktes nach dem zweiten Join INNER JOIN Videos AS v ON cc.VideoID = v.VideoID und vor der Ausgabe. Markieren Sie jetzt diejenigen Spalten der Datensätze, die ausgegeben werden.
Erläutern Sie, welche Änderungen passieren, wenn die Abfrage wie folgt verändert wird:
SELECT ch.ChannelName, v.Titel, v.UploadDatum
FROM Channels AS ch
INNER JOIN ChannelContent AS cc
ON ch.ChannelID = cc.ChannelID
INNER JOIN Videos AS v
ON cc.VideoID = v.VideoID
WHERE v.UploadDatum >= '2025-01-01';Erläutern Sie die folgenden SQL-Anweisungen und die Ausgabe-Relationen.
-- (1)
SELECT a.Username, ch.ChannelName
FROM Accounts AS a
LEFT JOIN Channels AS ch
ON a.AccountID = ch.AccountID
ORDER BY a.Username ASC;-- (2)
SELECT v.Titel, k.KommentarText
FROM Videos AS v
INNER JOIN Kommentare AS k
ON v.VideoID = k.VideoID
WHERE v.UploadDatum BETWEEN '2025-10-01'
AND '2025-12-31'
ORDER BY v.Titel ASC, k.ErstellungsDatum ASC;-- (3)
SELECT DISTINCT ch.ChannelName, a.Adresse
FROM Channels AS ch
INNER JOIN Accounts AS a
ON ch.AccountID = a.AccountID
INNER JOIN ChannelContent AS cc
ON ch.ChannelID = cc.ChannelID
INNER JOIN Videos AS v
ON cc.VideoID = v.VideoID
WHERE a.Lebensalter < 25
AND v.UploadDatum >= '2026-01-01';-- (4)
SELECT a.Username, v.Titel, k.ErstellungsDatum
FROM Accounts AS a
RIGHT JOIN Kommentare AS k
ON a.AccountID = k.AccountID
INNER JOIN Videos AS v
ON k.VideoID = v.VideoID
WHERE k.ErstellungsDatum = '2026-01-01'
ORDER BY k.KommentarID ASC;Entwerfen Sie je eine SQL-Anweisung, die die folgenden Ausgaben erzeugt.
- Alle KommentarTexte zusammen mit dem Videotitel und dem Username des Kommentierenden sollen ausgegeben werden, für Kommentare ab dem 01.12.2025. Sortieren Sie nach ErstellungsDatum aufsteigend.
- Alle Videotitel mit Kanalnamen und Username des Kanalinhabers sollen ausgegeben werden, für Videos mit UploadDatum im Jahr 2026. Sortieren Sie nach UploadDatum absteigend.
- Die Namen der Kanäle (ChannelName) und die Email des Kanalinhabers sollen ausgegeben werden, wenn diese Kanäle mindestens ein Video haben, das im Jahr 2025 hochgeladen wurde. Duplikate sollen vermieden werden.
- Username und Videotitel sollen ausgegeben werden, für alle Fälle, in denen ein Account ein Video kommentiert hat, das nicht von ihm selbst hochgeladen wurde. Sortieren Sie nach Username, dann nach Titel.
- Die Titel aller Videos sollen ausgegeben werden, zu denen es noch keinen Kommentar gibt.
!= bedeutet „ungleich“).IS NULL – ein Vergleich mit = NULL liefert nie ein Ergebnis.🎉 Ausgezeichnet – Alle Aufgaben abgeschlossen!
Hier finden Sie die Musterlösungen zu den Aufgaben mit Joins.
Aufgabe 1 (Partnerarbeit)
1.1 SQL-Abfrage 1
SELECT Titel, UploadDatum
FROM Videos
WHERE UploadDatum BETWEEN '2025-10-01' AND '2025-12-31';- Aus der Tabelle Videos werden nur die Spalten „Titel“ und „UploadDatum“ selektiert.
- Es werden nur Videos berücksichtigt, deren UploadDatum zwischen dem 01.10.2025 und dem 31.12.2025 liegt.
| Titel | Upload |
|---|---|
| Kamera-Review | 2025-10-05 |
| Italien-Vlog | 2025-12-20 |
(Hinweis: Das Video „DIY-Deko“ mit UploadDatum 2026-01-03 wird ausgeschlossen.)
1.2 SQL-Abfrage 2
SELECT a.Username, c.ChannelName
FROM Accounts a
INNER JOIN Channels c ON a.AccountID = c.AccountID;- Es erfolgt ein INNER JOIN zwischen den Tabellen Accounts und Channels anhand der Spalte AccountID.
- Daraus ergibt sich, dass nur die Accounts angezeigt werden, die auch einen zugehörigen Kanal haben.
| Username | Channel |
|---|---|
| FloraE | FloraTech |
| CarlS | CarlTravel |
| KimQ | KimLife |
(Hinweis: In der Aufgabenbeschreibung besitzt jeder Kanal am Ende eine Angabe, die angibt, welchem Account er gehört – so gehört „FloraTech“ beispielsweise zu der AccountID 2 (FloraE).)
1.3 SQL-Abfrage 3 (UNION)
SELECT Titel, Beschreibung
FROM Videos
UNION
SELECT ChannelName, ChannelBeschreibung
FROM Channels;- Zunächst werden aus der Tabelle Videos in den Spalten Titel und Beschreibung alle Datensätze ausgewählt.
- Anschließend wird per UNION (welches Duplikate eliminiert) die Auswahl aus den Spalten ChannelName und ChannelBeschreibung der Tabelle Channels angehängt.
- Das Ergebnis enthält also sowohl Videotitel und -beschreibungen als auch Kanalnamen und -beschreibungen.
- Die Spaltenüberschriften entsprechen dabei den ersten Spaltennamen („Titel“ und „Beschreibung“).
| Titel | Beschreibung |
|---|---|
| Kamera-Review | Test der XCam |
| Italien-Vlog | Rom und Toskana |
| DIY-Deko | Papierblumen basteln |
| FloraTech | Tech und Gadgets |
| CarlTravel | Reise und Vlog von Carl |
| KimLife | DIY |
1.4 SQL-Abfrage 4 (RIGHT JOIN) (Lösung ergänzt)
SELECT ch.ChannelName, v.Titel
FROM Channels ch
RIGHT JOIN ChannelContent cc ON ch.ChannelID = cc.ChannelID
RIGHT JOIN Videos v ON cc.VideoID = v.VideoID
WHERE v.UploadDatum < '2025-11-01';- Durch die beiden RIGHT JOINs bleiben alle Videos erhalten, auch wenn ihnen kein Kanal zugeordnet wäre (dann stünde bei ChannelName NULL).
- Die WHERE-Klausel lässt nur Videos übrig, die vor dem 01.11.2025 hochgeladen wurden – das ist nur „Kamera-Review“ (Kanal 10).
| Channel | Titel |
|---|---|
| FloraTech | Kamera-Review |
Aufgabe 2 (Partnerarbeit)
a) Zwei alternative SQL-Abfragen pro gewünschtem Ergebnis
(1) Titel und UploadDatum aller Videos aus Q4/2025, sortiert (aufsteigend nach Datum)
-- Alternative 1
SELECT Titel, UploadDatum
FROM Videos
WHERE UploadDatum BETWEEN '2025-10-01' AND '2025-12-31'
ORDER BY UploadDatum ASC;-- Alternative 2
SELECT Titel, UploadDatum
FROM Videos
WHERE UploadDatum >= '2025-10-01' AND UploadDatum <= '2025-12-31'
ORDER BY UploadDatum;(2) Username und ChannelName aller Kanäle, die von Accounts aus „München“ erstellt wurden (Es wird davon ausgegangen, dass in der Spalte Adresse das Schlüsselwort „München“ vorkommt.)
-- Alternative 1
SELECT a.Username, c.ChannelName
FROM Accounts a
INNER JOIN Channels c ON a.AccountID = c.AccountID
WHERE a.Adresse LIKE '%München%';-- Alternative 2 (Verknüpfung in WHERE)
SELECT a.Username, c.ChannelName
FROM Accounts a, Channels c
WHERE a.AccountID = c.AccountID
AND a.Adresse LIKE '%München%';(3) KommentarText zu VideoID = 101, sortiert nach ErstellungsDatum (absteigend)
-- Alternative 1
SELECT KommentarText
FROM Kommentare
WHERE VideoID = 101
ORDER BY ErstellungsDatum DESC;-- Alternative 2
SELECT k.KommentarText
FROM Kommentare k
WHERE k.VideoID = 101
ORDER BY k.ErstellungsDatum DESC;b) Eine SQL-Abfrage pro gewünschtem Ergebnis
(1) Alle Videotitel zusammen mit dem Namen des Kanals, in dem sie veröffentlicht wurden
SELECT v.Titel, ch.ChannelName
FROM Videos v
JOIN ChannelContent cc ON v.VideoID = cc.VideoID
JOIN Channels ch ON cc.ChannelID = ch.ChannelID;(2) Titel und UploadDatum der Videos, die nach dem 01.01.2026 hochgeladen wurden, zusammen mit dem Username des Kanalinhabers
SELECT v.Titel, v.UploadDatum, a.Username
FROM Videos v
JOIN ChannelContent cc ON v.VideoID = cc.VideoID
JOIN Channels ch ON cc.ChannelID = ch.ChannelID
JOIN Accounts a ON ch.AccountID = a.AccountID
WHERE v.UploadDatum > '2026-01-01';(3) Titel und UploadDatum der Videos, deren Beschreibung länger als 10 Zeichen ist, zusammen mit ChannelName und der Email des Kanalinhabers – sortiert nach UploadDatum (aufsteigend)
SELECT v.Titel, v.UploadDatum, ch.ChannelName, a.Email
FROM Videos v
JOIN ChannelContent cc ON v.VideoID = cc.VideoID
JOIN Channels ch ON cc.ChannelID = ch.ChannelID
JOIN Accounts a ON ch.AccountID = a.AccountID
WHERE LENGTH(v.Beschreibung) > 10
ORDER BY v.UploadDatum ASC;Hinweis: Je nach SQL-Dialekt kann statt LENGTH() auch CHAR_LENGTH() verwendet werden.
(4) Username und ChannelName aller Kanäle, deren Inhaber 18, 19 oder 20 Jahre alt sind
SELECT a.Username, ch.ChannelName
FROM Accounts a, Channels ch
WHERE a.AccountID = ch.AccountID
AND a.Lebensalter IN (18, 19, 20);Alle Abfragen aus b) lassen sich auch ohne JOIN schreiben, wie es in den Abituraufgaben üblich ist. Für (1) zum Beispiel:
SELECT v.Titel, ch.ChannelName
FROM Videos v, ChannelContent cc, Channels ch
WHERE v.VideoID = cc.VideoID
AND cc.ChannelID = ch.ChannelID;Aufgabe 3 (Gruppenarbeit)
a) Erläuterung der SQL-Anweisung
SELECT k.KommentarText, v.Titel, k.ErstellungsDatum
FROM Kommentare AS k
INNER JOIN Videos AS v
ON k.VideoID = v.VideoID;- Ausgabe: Für jeden Datensatz in der Tabelle Kommentare, der mit einem Video in der Tabelle Videos übereinstimmt (über die Spalte VideoID), wird der Kommentartext, der Videotitel und das Erstellungsdatum (des Kommentars) ausgegeben.
- Notwendigkeit mehrerer Tabellen:
- Kommentare: Liefert den Kommentartext und das Erstellungsdatum sowie die Zuordnung zum Video (über VideoID).
- Videos: Stellt den Titel des Videos bereit, der im Kommentarkontext nicht direkt vorhanden wäre.
- In der Gesamtdatenbank wird häufig auch die Beziehung zum Kanal (z. B. über ChannelContent) oder zum Account (Kommentarersteller) benötigt – daher sind in der Gesamtstruktur oft drei (oder mehr) Tabellen involviert, auch wenn diese spezielle Abfrage nur zwei direkt verwendet.
b) Kreuzprodukt nach dem INNER JOIN (Kommentare ⨝ Videos)
Ziel: Darstellung des Zwischenergebnisses, bevor die Projektion (also die Auswahl der auszugebenden Spalten) erfolgt. Ergebnis des Joins (alle Spalten beider Tabellen, nur wenn k.VideoID = v.VideoID):
| KommentarID | Kommentar | Erstellungs | Rating | AccountID | k. | v. | Titel | Beschreibung | Upload |
|---|---|---|---|---|---|---|---|---|---|
| 1001 | Super erklärt! Ich habe nichts vermisst. | 2025-10-06 | 5 | 1 | 101 | 101 | Kamera-Review | Test der XCam | 2025-10-05 |
| 1002 | Hilfreich, danke dir! | 2025-10-07 | 3 | 3 | 101 | 101 | Kamera-Review | Test der XCam | 2025-10-05 |
| 1003 | Toller Vlog! | 2025-15-22* | 4 | 4 | 102 | 102 | Italien-Vlog | Rom und Toskana | 2025-12-20 |
| 1004 | Schön! | 2026-01-05 | 3 | 3 | 103 | 103 | DIY-Deko | Papierblumen basteln | 2026-01-03 |
*Das Datum beim dritten Eintrag ist im Aufgabenbeispiel fehlerhaft („2025-15-22“); man geht von einem gültigen Datum aus.
Bevor im SELECT lediglich die Spalten KommentarText, Titel und ErstellungsDatum (aus den jeweiligen Tabellen) ausgewählt werden, liegt dieses Zwischenergebnis vor.
c) Analyse der zweiten SQL-Anweisung
SELECT ch.ChannelName, v.Titel, v.UploadDatum
FROM Channels AS ch
INNER JOIN ChannelContent AS cc
ON ch.ChannelID = cc.ChannelID
INNER JOIN Videos AS v
ON cc.VideoID = v.VideoID;- Join zwischen Channels und ChannelContent: Hier werden die Kanäle mit ihren Einträgen in der ChannelContent-Tabelle verknüpft. Dadurch hat man für jeden Kanal seine zugeordneten VideoIDs.
- Zweiter Join mit Videos: Anhand der VideoID werden die Details (z. B. Titel, UploadDatum) aus der Tabelle Videos ergänzt.
- Ausgabe: Für jeden Kanal, der in der ChannelContent-Tabelle einen Eintrag hat, werden der Kanalname, der Titel des jeweiligen Videos sowie das UploadDatum des Videos ausgegeben.
- Begründung: Da ein Kanal durchaus mehrere Videos veröffentlichen kann, werden alle Kombinationen (sprich: alle Videos eines Kanals) ausgegeben.
(Zweite Teilaufgabe) – Ergebnis des ersten Joins (Channels ⨝ ChannelContent):
| ChannelID | Channel | Channel | AccountID | cc. | cc. |
|---|---|---|---|---|---|
| 10 | FloraTech | Tech und Gadgets | 2 | 10 | 101 |
| 11 | CarlTravel | Reise und Vlog von Carl | 1 | 11 | 102 |
| 12 | KimLife | DIY | 3 | 12 | 103 |
d) Ergebnis des zweiten Joins (Ergebnis aus c) wird mit Videos verknüpft: ON cc.VideoID = v.VideoID). Die ausgegebenen Spalten sind markiert:
| ch. | Channel | Channel | AccountID | cc. | cc. | v. | Titel | Beschreibung | Upload |
|---|---|---|---|---|---|---|---|---|---|
| 10 | FloraTech | Tech und Gadgets | 2 | 10 | 101 | 101 | Kamera-Review | Test der XCam | 2025-10-05 |
| 11 | CarlTravel | Reise und Vlog von Carl | 1 | 11 | 102 | 102 | Italien-Vlog | Rom und Toskana | 2025-12-20 |
| 12 | KimLife | DIY | 3 | 12 | 103 | 103 | DIY-Deko | Papierblumen basteln | 2026-01-03 |
Die finale SELECT-Anweisung gibt die Spalten ChannelName (z. B. FloraTech), Titel (z. B. Kamera-Review) und UploadDatum (z. B. 2025-10-05) aus.
e) Auswirkungen der Erweiterung um die WHERE-Klausel
SELECT ch.ChannelName, v.Titel, v.UploadDatum
FROM Channels AS ch
INNER JOIN ChannelContent AS cc
ON ch.ChannelID = cc.ChannelID
INNER JOIN Videos AS v
ON cc.VideoID = v.VideoID
WHERE v.UploadDatum >= '2025-01-01';- Filter: Es wird zusätzlich eine Bedingung in der WHERE-Klausel gesetzt, die nur Videos berücksichtigt, deren UploadDatum größer oder gleich dem 01.01.2025 ist.
- Auswirkung: Alle Join-Ergebnisse, bei denen das UploadDatum eines Videos vor dem 01.01.2025 liegt, werden herausgefiltert. Dadurch wird die Ausgabe nur für Videos erfolgen, die ab dem angegebenen Datum hochgeladen wurden. Die Struktur des Joins und die Zugehörigkeit von Kanal zu Video bleiben unverändert. (In den Beispieldaten liegen alle Videos nach diesem Datum, die Ausgabe ändert sich hier also nicht.)
Aufgabe 4 (Partnerarbeit)
a) Erläuterung der gegebenen SQL-Anweisungen
(1)
SELECT a.Username, ch.ChannelName
FROM Accounts AS a
LEFT JOIN Channels AS ch
ON a.AccountID = ch.AccountID
ORDER BY a.Username ASC;- Mit einem LEFT JOIN werden alle Datensätze aus der Tabelle Accounts ausgegeben.
- Falls für einen Account kein zugehöriger Kanal in Channels existiert, wird für ChannelName ein NULL-Wert angezeigt.
- Die Ergebnisse werden alphabetisch (ASC) nach dem Username sortiert.
(2)
SELECT v.Titel, k.KommentarText
FROM Videos AS v
INNER JOIN Kommentare AS k
ON v.VideoID = k.VideoID
WHERE v.UploadDatum BETWEEN '2025-10-01'
AND '2025-12-31'
ORDER BY v.Titel ASC, k.ErstellungsDatum ASC;- Zunächst findet ein INNER JOIN zwischen Videos und Kommentare statt, sodass nur Videos angezeigt werden, zu denen es mindestens einen Kommentar gibt.
- Anschließend wird mit der WHERE-Klausel nur der Zeitraum vom 01.10.2025 bis zum 31.12.2025 berücksichtigt.
- Sortiert wird primär nach dem Videotitel (alphabetisch) und sekundär nach dem Erstellungsdatum der Kommentare (aufsteigend).
(3)
SELECT DISTINCT ch.ChannelName, a.Adresse
FROM Channels AS ch
INNER JOIN Accounts AS a
ON ch.AccountID = a.AccountID
INNER JOIN ChannelContent AS cc
ON ch.ChannelID = cc.ChannelID
INNER JOIN Videos AS v
ON cc.VideoID = v.VideoID
WHERE a.Lebensalter < 25
AND v.UploadDatum >= '2026-01-01';- Die Abfrage verknüpft zuerst Channels mit Accounts (um den Besitzer und dessen Adresse zu ermitteln).
- Anschließend wird über ChannelContent die Beziehung zu den Videos hergestellt.
- Mit der WHERE-Klausel werden nur Datensätze ausgewählt, bei denen der Accountinhaber jünger als 25 Jahre ist und bei denen mindestens ein Video hochgeladen wurde, das ab dem 01.01.2026 liegt.
- Das DISTINCT sorgt dafür, dass Duplikate in der Ausgabe vermieden werden.
(4)
SELECT a.Username, v.Titel, k.ErstellungsDatum
FROM Accounts AS a
RIGHT JOIN Kommentare AS k
ON a.AccountID = k.AccountID
INNER JOIN Videos AS v
ON k.VideoID = v.VideoID
WHERE k.ErstellungsDatum = '2026-01-01'
ORDER BY k.KommentarID ASC;- Durch den RIGHT JOIN werden alle Kommentare (und deren zugehörige Accounts, sofern vorhanden) berücksichtigt.
- Anschließend wird mittels INNER JOIN der Videotitel zu jedem Kommentar hinzugefügt.
- Mit der WHERE-Klausel werden nur die Kommentare angezeigt, deren ErstellungsDatum exakt dem 01.01.2026 entspricht.
- Die Ergebnisse werden nach der KommentarID (aufsteigend) sortiert.
b)
(1) Alle KommentarTexte zusammen mit dem Videotitel und dem Username des Kommentierenden (Kommentare ab dem 01.12.2025, sortiert nach ErstellungsDatum aufsteigend)
SELECT k.KommentarText, v.Titel, a.Username
FROM Kommentare k
JOIN Videos v ON k.VideoID = v.VideoID
JOIN Accounts a ON k.AccountID = a.AccountID
WHERE k.ErstellungsDatum >= '2025-12-01'
ORDER BY k.ErstellungsDatum ASC;(2) Alle Videotitel mit Kanalnamen und Username des Kanalinhabers (für Videos mit UploadDatum im Jahr 2026, sortiert nach UploadDatum absteigend)
SELECT v.Titel, ch.ChannelName, a.Username
FROM Videos v
JOIN ChannelContent cc ON v.VideoID = cc.VideoID
JOIN Channels ch ON cc.ChannelID = ch.ChannelID
JOIN Accounts a ON ch.AccountID = a.AccountID
WHERE v.UploadDatum BETWEEN '2026-01-01' AND '2026-12-31'
ORDER BY v.UploadDatum DESC;(3) Die Namen der Kanäle und die Email des Kanalinhabers (nur wenn der Kanal mindestens ein Video hat, das im Jahr 2025 hochgeladen wurde; Duplikate vermeiden)
SELECT DISTINCT ch.ChannelName, a.Email
FROM Channels ch
JOIN ChannelContent cc ON ch.ChannelID = cc.ChannelID
JOIN Videos v ON cc.VideoID = v.VideoID
JOIN Accounts a ON ch.AccountID = a.AccountID
WHERE v.UploadDatum BETWEEN '2025-01-01' AND '2025-12-31';(4) Username und Videotitel (für alle Fälle, in denen ein Account ein Video kommentiert hat, das nicht von ihm selbst hochgeladen wurde; sortiert nach Username und dann nach Titel)
SELECT a.Username, v.Titel
FROM Kommentare k
JOIN Accounts a ON k.AccountID = a.AccountID
JOIN Videos v ON k.VideoID = v.VideoID
JOIN ChannelContent cc ON v.VideoID = cc.VideoID
JOIN Channels ch ON cc.ChannelID = ch.ChannelID
WHERE ch.AccountID != a.AccountID
ORDER BY a.Username, v.Titel;Die Bedingung ch.AccountID != a.AccountID stellt sicher, dass der kommentierende Account nicht gleichzeitig der Ersteller (Kanalinhaber) des Videos ist.
(5) Titel aller Videos, zu denen es noch keinen Kommentar gibt
SELECT v.Titel
FROM Videos v
LEFT JOIN Kommentare k ON v.VideoID = k.VideoID
WHERE k.KommentarID IS NULL;Der LEFT JOIN behält alle Videos. Hat ein Video keinen Kommentar, stehen in den Spalten von Kommentare NULL-Werte. Die Bedingung k.KommentarID IS NULL wählt genau diese Videos aus.
Zusammenfassung
- Aufgabe 1 befasst sich mit der Bestimmung der Ergebnisrelationen einzelner, relativ einfacher SQL-Abfragen.
- Aufgabe 2 verlangt die Formulierung alternativer Abfragen zu konkreten Anforderungen sowie komplexere Joins, um zusätzliche Informationen aus mehreren Tabellen zu verknüpfen.
- Aufgabe 3 beschäftigt sich intensiv mit JOIN-Operationen: zunächst die Verknüpfung von Kommentaren und Videos sowie die Darstellung des Zwischenergebnisses (Kreuzprodukt), danach die Aufbereitung eines kombinierten Ergebnisses aus mehreren Tabellen mit Analyse der Join-Logik.
- Aufgabe 4 beinhaltet das Erklären gegebener SQL-Abfragen mit LEFT/RIGHT JOINs sowie die Konstruktion von Abfragen, die spezifische Filter- und Sortieranforderungen erfüllen.
Gruppieren
Betrachten wir ein anderes Szenario. Sie spielen mit Ihren kleinen Geschwistern und Ihren Eltern ein Reiseziele-Quartett. Hier ist ein Auszug der einzelnen Karten.
Auszug aus dem Quartett: Reiseziele
Aufgaben: Gruppieren
Erläutern Sie, inwiefern das Reiseziel-Quartett als Datenbank betrachtet werden kann. Geben Sie auch geeignete Attribute für einen Primärschlüssel an.
Häufig möchte man nicht alle Datensätze miteinander, sondern Gruppen von Datensätzen vergleichen. Zum Beispiel könnte man sich in unserem Quartett für den Durchschnittspreis aller Reiseziele der einzelnen Kontinente interessieren.
Dies kann mit „GROUP BY“ durchgeführt werden. „GROUP BY“ erstellt aus den Datensätzen zunächst Gruppen nach einem vorgegebenen Kriterium. „GROUP BY Kontinent“ teilt unsere Quartettkarten in die folgenden Gruppen ein.
| Gruppe „Europa“ | Gruppe „Südamerika“ | Gruppe „Nordamerika“ | Gruppe „Afrika“ | Gruppe „Asien“ | Gruppe „Australien“ |
|---|---|---|---|---|---|
| Edinburgh | Machu Picchu | Vancouver | Kapstadt | Tokio | Queenstown |
| Schwarzwald | Buenos Aires | Honolulu | Marrakesch | Bali | Auckland |
| Santorini | Rio de Janeiro | New York City | Kairo | Seoul | Sydney |
| … | … | … | … | … | … |
Es werden also für jeden Kontinent zunächst Stapel gebildet. Jetzt können nur noch die Datensätze innerhalb einer Gruppe betrachtet werden.
Wir betrachten nun die folgenden beiden SQL-Abfragen.
-- Person 1
SELECT Kontinent, AVG(MittlereHotelkostenProNacht)
FROM Reiseziele
GROUP BY Kontinent-- Person 2
SELECT Kontinent, MAX(DurchschnittstemperaturImSommer)
FROM Reiseziele
GROUP BY KontinentTeilen Sie sich in Ihrer Zweiergruppe die beiden SQL-Abfragen untereinander auf. Nehmen Sie sich je einen Stapel des Reiseziele-Quartetts und versuchen Sie, durch das Bilden einzelner Stapel die Rückgabe der jeweiligen SQL-Abfrage zu ermitteln.
Erläutern Sie sich gegenseitig Ihre jeweilige SQL-Abfrage, Ihren Lösungsweg und das Ergebnis.
🎉 Ausgezeichnet – Alle Aufgaben abgeschlossen!
Hier finden Sie die Lösungen zu den Aufgaben 1 und 2.
Aufgabe 1
Die Karten können als Datensätze interpretiert werden. Die einzelnen Informationen der Karten sind die Attribute. Das gesamte Quartett ist dann die Datenbank. Ein geeigneter Primärschlüssel ist zum Beispiel Ort, da dieser im Quartett eindeutig ist.
Aufgabe 2 – Ergebnisse der SQL-Abfragen
SELECT Kontinent, MAX(DurchschnittstemperaturImSommer)
FROM Reiseziele
GROUP BY Kontinent| Kontinent | MAX(DurchschnittstemperaturImSommer) |
|---|---|
| Europa | 30 |
| Asien | 40 |
| Afrika | 38 |
| Nordamerika | 27 |
| Südamerika | 30 |
| Australien (inkl. Neuseeland) | 23 |
SELECT Kontinent, AVG(MittlereHotelkostenProNacht)
FROM Reiseziele
GROUP BY Kontinent| Kontinent | AVG(MittlereHotelkostenProNacht) |
|---|---|
| Europa | 134,38 |
| Asien | 131,43 |
| Afrika | 80,00 |
| Nordamerika | 171,43 |
| Südamerika | 90,00 |
| Australien (inkl. Neuseeland) | 150,00 |
Nun können Aggregatfunktionen nur noch auf die Gruppen angewendet werden. Es ist mit GROUP BY nicht mehr möglich, alle Datensätze miteinander zu vergleichen. Nur innerhalb der einzelnen Gruppen können Datensätze untereinander verglichen werden.
Group By und Having
Wir betrachten die folgende Situation. Die Datensätze mit den Karten wurden wieder mit „GROUP BY Kontinent“ in die folgenden Gruppen eingeteilt.
| Gruppe „Europa“ | Gruppe „Südamerika“ | Gruppe „Nordamerika“ | Gruppe „Afrika“ | Gruppe „Asien“ | Gruppe „Australien“ |
|---|---|---|---|---|---|
| Edinburgh | Machu Picchu | Vancouver | Kapstadt | Tokio | Queenstown |
| Schwarzwald | Buenos Aires | Honolulu | Marrakesch | Bali | Auckland |
| Santorini | Rio de Janeiro | New York City | Kairo | Seoul | Sydney |
| … | … | … | … | … | … |
Aufgaben: Gruppen filtern
Erstellen Sie eine SQL-Anweisung, mit welcher die Namen der Kontinente mit ihrer jeweiligen maximalen Durchschnittstemperatur ausgegeben werden.
GROUP BY Kontinent.MAX(DurchschnittstemperaturImSommer).Untersuchen Sie, welche Ausgabe nach der folgenden SQL-Anweisung getätigt wird.
SELECT Kontinent, MAX(DurchschnittstemperaturImSommer) AS MaxTemperatur
FROM Reiseziele
GROUP BY Kontinent
HAVING MAX(DurchschnittstemperaturImSommer) > 25
ORDER BY MaxTemperatur ASCZusammenfassung zu Gruppierungen
Die folgende Abfrage berechnet für die Reiseziele den Durchschnitt der Sommertemperaturen je Kontinent:
SELECT Kontinent, AVG(DurchschnittstemperaturImSommer) AS AvgTemp
FROM Reiseziele
GROUP BY Kontinent;- Gruppen bilden: GROUP BY fasst alle Zeilen mit demselben Wert in der Spalte Kontinent zu einer Gruppe zusammen, z. B. alle europäischen Reiseziele.
- Je Gruppe einen Wert berechnen: Die Aggregatfunktion AVG fasst die Temperaturen einer Gruppe zu einem einzigen Wert zusammen („aggregieren“). Im Ergebnis steht für jeden Kontinent genau eine Zeile.
Sollen nur Kontinente mit einer Durchschnittstemperatur über 25 °C erscheinen, hilft WHERE nicht: WHERE prüft einzelne Zeilen vor dem Gruppieren, der Durchschnitt entsteht aber erst danach. Bedingungen an Gruppen stehen deshalb in der HAVING-Klausel:
SELECT Kontinent, AVG(DurchschnittstemperaturImSommer) AS AvgTemp
FROM Reiseziele
GROUP BY Kontinent
HAVING AVG(DurchschnittstemperaturImSommer) > 25;Merkkasten: In welcher Reihenfolge wird eine Abfrage ausgewertet?
- FROM – Tabellen bereitstellen (bei mehreren Tabellen: Kreuzprodukt)
- WHERE – einzelne Zeilen filtern (auch die Verknüpfungsbedingung)
- GROUP BY – Gruppen bilden
- HAVING – Gruppen filtern (Bedingungen mit Aggregatfunktionen)
- SELECT – Spalten auswählen und Werte berechnen
- ORDER BY – sortieren
- LIMIT – Anzahl der Ergebniszeilen begrenzen
Deshalb kann WHERE keine Aggregatfunktionen enthalten. Und weil SELECT erst nach dem Gruppieren kommt, steht im SELECT-Teil jede Spalte entweder in einer Aggregatfunktion oder im GROUP BY.
Aufgaben: HAVING anwenden
Geben Sie an, was mit den folgenden SQL-Anweisungen ausgegeben wird.
-- (1)
SELECT Land, AVG(MittlereHotelkostenProNacht) AS DurchschnittlicheHotelkosten,
COUNT(*) AS AnzahlReiseziele
FROM Reiseziele
GROUP BY Land
HAVING COUNT(*) > 2-- (2)
SELECT Kontinent, AVG(EntfernungVonDeutschland) AS DurchschnittlicheEntfernung,
MAX(EntfernungVonDeutschland) AS MaximaleEntfernung
FROM Reiseziele
GROUP BY Kontinent
HAVING AVG(MittlereHotelkostenProNacht) < 130-- (3)
SELECT Kontinent, AVG(EntfernungVonDeutschland) AS DurchschnittlicheEntfernung
FROM Reiseziele
GROUP BY Kontinent
HAVING COUNT(*) >= 3;-- (4)
SELECT Kontinent, AVG(MittlereHotelkostenProNacht) AS DurchschnittlicheHotelkosten,
COUNT(*) AS AnzahlReiseziele
FROM Reiseziele
GROUP BY Kontinent
HAVING AVG(EntfernungVonDeutschland) > 5000;-- (5)
SELECT Kontinent, AVG(DurchschnittstemperaturImSommer) AS DurchschnittlicheTemperatur,
MAX(DurchschnittstemperaturImSommer) AS MaximaleTemperatur
FROM Reiseziele
GROUP BY Kontinent
HAVING MAX(DurchschnittstemperaturImSommer) > 35;-- (6)
SELECT Kontinent, AVG(MittlereHotelkostenProNacht) AS DurchschnittlicheHotelkosten
FROM Reiseziele
GROUP BY Kontinent
HAVING MAX(DurchschnittstemperaturImSommer) > 20;Erstellen Sie jeweils eine SQL-Anweisung, welche die folgenden Informationen liefert.
- Durchschnittliche Hotelkosten pro Kontinent, nur für Kontinente mit mindestens 4 Reisezielen.
- Kontinente mit einer durchschnittlichen Entfernung von Deutschland über 5.000 km.
- Durchschnittliche Hotelkosten pro Kontinent, nur für Kontinente mit maximaler Durchschnittstemperatur über 30 °C.
- Durchschnittliche Entfernung und Anzahl der Reiseziele pro Kontinent, nur für Kontinente mit durchschnittlichen Hotelkosten unter 150 €.
- Kontinente mit mindestens einem Reiseziel, dessen Hotelkosten über 200 € liegen, und einer durchschnittlichen Entfernung unter 8.000 km.
- Kontinente mit durchschnittlichen Hotelkosten unter 150 € und einer maximalen Durchschnittstemperatur über 25 °C.
HAVING MAX(MittlereHotelkostenProNacht) > 200.🎉 Ausgezeichnet – Alle Aufgaben abgeschlossen!
Hier finden Sie die Lösungen zu Group By und Having.
Aufgabe 1
a)
SELECT Kontinent, MAX(DurchschnittstemperaturImSommer) AS MaxTemperatur
FROM Reiseziele
GROUP BY Kontinentb) Ausgegeben werden die Kontinente mit einer maximalen Durchschnittstemperatur im Sommer über 25 °C, aufsteigend nach der MaxTemperatur sortiert.
Aufgabe 2 a)
- Durchschnittliche Hotelkosten und Anzahl der Reiseziele pro Land, nur für Länder mit mehr als 2 Reisezielen.
- Durchschnittliche und maximale Entfernung pro Kontinent, nur für Kontinente mit durchschnittlichen Hotelkosten unter 130 €.
- Durchschnittliche Entfernung von Deutschland pro Kontinent, nur für Kontinente mit mindestens drei Reisezielen.
- Durchschnittliche Hotelkosten und Anzahl der Reiseziele pro Kontinent, nur für Kontinente mit einer durchschnittlichen Entfernung über 5.000 km.
- Durchschnittliche und maximale Temperaturen im Sommer pro Kontinent, gefiltert nach Kontinenten mit mindestens einem Reiseziel mit Temperaturen über 35 °C.
- Durchschnittliche Hotelkosten pro Kontinent, nur für Kontinente mit einer maximalen Durchschnittstemperatur im Sommer über 20 °C.
Aufgabe 2 b)
(1) Durchschnittliche Hotelkosten pro Kontinent, nur für Kontinente mit mindestens 4 Reisezielen.
SELECT Kontinent, AVG(MittlereHotelkostenProNacht) AS DurchschnittlicheHotelkosten
FROM Reiseziele
GROUP BY Kontinent
HAVING COUNT(*) >= 4;(2) Kontinente mit einer durchschnittlichen Entfernung von Deutschland über 5.000 km.
SELECT Kontinent, AVG(EntfernungVonDeutschland) AS DurchschnittlicheEntfernung
FROM Reiseziele
GROUP BY Kontinent
HAVING AVG(EntfernungVonDeutschland) > 5000;(3) Durchschnittliche Hotelkosten pro Kontinent, nur für Kontinente mit maximaler Durchschnittstemperatur über 30 °C.
SELECT Kontinent, AVG(MittlereHotelkostenProNacht) AS DurchschnittlicheHotelkosten
FROM Reiseziele
GROUP BY Kontinent
HAVING MAX(DurchschnittstemperaturImSommer) > 30;(4) Durchschnittliche Entfernung und Anzahl der Reiseziele pro Kontinent, nur für Kontinente mit durchschnittlichen Hotelkosten unter 150 €.
SELECT Kontinent, AVG(EntfernungVonDeutschland) AS DurchschnittlicheEntfernung,
COUNT(*) AS AnzahlReiseziele
FROM Reiseziele
GROUP BY Kontinent
HAVING AVG(MittlereHotelkostenProNacht) < 150;(5) Kontinente mit mindestens einem Reiseziel, dessen Hotelkosten über 200 € liegen, und einer durchschnittlichen Entfernung unter 8.000 km.
SELECT Kontinent, AVG(EntfernungVonDeutschland) AS DurchschnittlicheEntfernung,
MAX(MittlereHotelkostenProNacht) AS MaxHotelkosten
FROM Reiseziele
GROUP BY Kontinent
HAVING MAX(MittlereHotelkostenProNacht) > 200
AND AVG(EntfernungVonDeutschland) < 8000;(6) Kontinente mit durchschnittlichen Hotelkosten unter 150 € und einer maximalen Durchschnittstemperatur über 25 °C.
SELECT Kontinent, AVG(MittlereHotelkostenProNacht) AS DurchschnittlicheHotelkosten,
MAX(DurchschnittstemperaturImSommer) AS MaximaleTemperatur
FROM Reiseziele
GROUP BY Kontinent
HAVING AVG(MittlereHotelkostenProNacht) < 150
AND MAX(DurchschnittstemperaturImSommer) > 25;Gruppieren über mehrere Spalten
Mit GROUP BY kann man Zeilen mit gleichen Werten zu Gruppen zusammenfassen und darüber Aggregatfunktionen anwenden (z. B. COUNT, AVG, MIN, MAX, SUM). Gruppiert man über mehrere Spalten, bilden exakt die gemeinsamen Werte aller aufgeführten Spalten die Gruppen. Das ist nützlich, um Kennzahlen je Kombination aus Merkmalen zu berechnen, z. B. „durchschnittliche Hotelkosten je Kontinent und bestes Reisezeitfenster“.
Wichtig (GROUP-BY-Regel): In der SELECT-Liste dürfen nur
- Spalten stehen, die in der GROUP BY-Klausel aufgeführt sind, oder
- Ausdrücke, die Aggregatfunktionen verwenden.
Alles andere führt zu einem Fehler, weil die nicht gruppierten Spalten keinen eindeutigen Wert pro Gruppe haben.
Eine Beispieldatenbank „Reiseziele“ könnte so aussehen:
| RID | Ort | Land | Kontinent | Entfernung | Durchschnittstemperatur | Beliebteste | Mittlere | Bestes | Besondere |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Rom | Italien | Europa | 1200 km | 25 °C | Kolosseum | 130 € | Mai bis Oktober | Besuch des Vatikans |
| 2 | Dubai | Vereinigte Arabische Emirate | Asien | 4800 km | 40 °C | Burj Khalifa | 250 € | November bis März | Wüstensafari |
| 3 | Sydney | Australien | Australien | 16500 km | 22 °C | Sydney Opera House | 150 € | September bis November | Surfen am Bondi Beach |
| 4 | Mexiko-Stadt | Mexiko | Nordamerika | 9900 km | 20 °C | Zócalo | 80 € | März bis Mai | Besuch der Pyramiden von Teotihuacan |
| … | … | … | … | … | … | … | … | … | … |
Aufgaben: Gruppieren über mehrere Spalten
Analysieren Sie die folgenden SQL-Anweisungen und erläutern Sie deren jeweilige Funktionsweise und Ausgabe.
Beispiel 1
SELECT RID, Ort, COUNT(*) AS anzahl_eintraege
FROM Reiseziele
GROUP BY RID, Ort
ORDER BY RID;Beispiel 2
SELECT RID, Ort, MAX(MittlereHotelkostenProNacht) AS max_hotelpreis
FROM Reiseziele
GROUP BY RID, Ort
ORDER BY RID;Beispiel 3
SELECT Land, BestesReisezeitfenster, COUNT(*) AS anzahl_ziele
FROM Reiseziele
GROUP BY Land, BestesReisezeitfenster
ORDER BY Land, anzahl_ziele DESC;Beispiel 4
SELECT Kontinent, BestesReisezeitfenster, AVG(MittlereHotelkostenProNacht) AS avg_hotelpreis
FROM Reiseziele
GROUP BY Kontinent, BestesReisezeitfenster
ORDER BY Kontinent, avg_hotelpreis DESC;Zusammenfassung zu Aufgabe 3 anzeigen
Beispiel 1
SELECT RID, Ort, COUNT(*) AS anzahl_eintraege
FROM Reiseziele
GROUP BY RID, Ort
ORDER BY RID;Zählt die Anzahl der Zeilen pro Gruppe, wobei die Gruppen nach den Spalten RID und Ort gebildet werden. Das Ergebnis enthält für jede Gruppe die Spalten RID, Ort und die Anzahl (alias anzahl_eintraege). ORDER BY RID sortiert das Ergebnis nach RID aufsteigend.
Hinweise / Besonderheiten:
- Wenn RID ein Primärschlüssel ist (also jede RID nur einmal vorkommt), ist jede Gruppe genau 1 Zeile groß, daher ist COUNT(*) = 1 für jede Zeile.
- Ort steht im GROUP BY, weil Ort im SELECT-Teil ausgegeben wird. Da RID eindeutig ist, ändert das die Gruppen nicht.
Erwartete Beispielausgabe (Auszug): Da die RID als Primärschlüssel eindeutig ist, ergibt sich für jede Zeile COUNT = 1.
| RID | Ort | anzahl_eintraege |
|---|---|---|
| 1 | Rom | 1 |
| 2 | Dubai | 1 |
| 3 | Sydney | 1 |
| 4 | Mexiko-Stadt | 1 |
| … | … | 1 |
Fazit: Die Abfrage liefert pro Eintrag die Zeilenanzahl (meist 1), ist also meist nicht aussagekräftig, wenn RID eindeutig ist.
Beispiel 2
SELECT RID, Ort, MAX(MittlereHotelkostenProNacht) AS max_hotelpreis
FROM Reiseziele
GROUP BY RID, Ort
ORDER BY RID;Berechnet innerhalb jeder Gruppe (hier gruppiert nach RID und Ort) den maximalen Wert der Spalte MittlereHotelkostenProNacht. Gibt RID, Ort und den maximalen Hotelpreis (alias max_hotelpreis) aus.
Hinweise / Besonderheiten:
- Wenn jede Gruppe nur eine Zeile enthält (RID eindeutig), ist MAX(…) identisch mit dem Einzelwert MittlereHotelkostenProNacht dieser Zeile.
- MAX in Kombination mit GROUP BY ist sinnvoll, wenn mehrere Zeilen pro Gruppe existieren (z. B. wenn man nur nach Ort gruppiert, aber mehrere Preise pro Ort hat).
Erwartete Beispielausgabe (Auszug): Wieder ist der MAX bei eindeutigen RID einfach der Wert der jeweiligen Zeile:
| RID | Ort | max_hotelpreis |
|---|---|---|
| 1 | Rom | 130 |
| 2 | Dubai | 250 |
| 3 | Sydney | 150 |
| 4 | Mexiko-Stadt | 80 |
| … | … | … |
Fazit: Funktional korrekt, aber nur dann interessant, wenn mehrere Zeilen pro Gruppe existieren.
Beispiel 3
SELECT Land, BestesReisezeitfenster, COUNT(*) AS anzahl_ziele
FROM Reiseziele
GROUP BY Land, BestesReisezeitfenster
ORDER BY Land, anzahl_ziele DESC;Bildet Gruppen je Kombination aus Land und BestesReisezeitfenster und zählt für jede Kombination die Anzahl der Datensätze (Ziele). Sortierung: zuerst alphabetisch nach Land, innerhalb desselben Landes nach anzahl_ziele absteigend.
Aussage & Beispielausgabe (ausgewählte, relevante Gruppen): Die Abfrage zeigt, wie viele Ziele es pro Kombination Land/BestesReisezeitfenster gibt. Bei vielen Ländern und genau spezifizierten Zeitfenstern ist der Wert häufig 1. Nur wenn ein Land mehrere Orte mit exakt identischem BestesReisezeitfenster hat, entsteht ein Wert > 1. Aus der vorliegenden Daten-Analyse sind auffällige Gruppierungen:
| Land | Bestes | anzahl_ziele |
|---|---|---|
| Kanada | Mai bis September | 2 |
| USA | Mai bis Oktober | 2 |
| Italien | (verschiedene Fenster) | 1 / 1 / … |
| … | … | … |
Erläuterung zu den gefundenen Mehrfachgruppen:
- Kanada: Toronto (Mai–Sep) und Vancouver (Mai–Sep) → anzahl_ziele = 2
- USA: Los Angeles (Mai–Okt) und New York City (Mai–Okt) → anzahl_ziele = 2
Alle anderen Kombinationen in der Liste sind in der Regel Einzelvorkommen (anzahl_ziele = 1), weil das BestesReisezeitfenster meist als exakt gleiche Zeichenkette angegeben wurde.
Fazit: Die Abfrage ist nützlich, um zu sehen, in welchen Ländern mehrere Reiseziele dasselbe empfohlene Reisezeitfenster haben.
Beispiel 4
SELECT Kontinent, BestesReisezeitfenster, AVG(MittlereHotelkostenProNacht) AS avg_hotelpreis
FROM Reiseziele
GROUP BY Kontinent, BestesReisezeitfenster
ORDER BY Kontinent, avg_hotelpreis DESC;Bildet Gruppen je Kontinent und BestesReisezeitfenster und berechnet den Durchschnitt der mittleren Hotelkosten pro Nacht für jede Gruppe. Sortiert das Ergebnis zuerst nach Kontinent, innerhalb desselben Kontinents nach avg_hotelpreis absteigend.
Hinweise / Besonderheiten: Wieder gilt: Gruppieren nach zwei Feldern. Nur wenn innerhalb einer Kontinent/Zeitraum-Kombination mehrere Zeilen existieren, ist der AVG relevant; sonst ist AVG identisch mit dem einzelnen Preis.
Sinnvolle Gruppen mit mehr als 1 Eintrag sowie einige exemplarische Einzelgruppen:
| Kontinent | Bestes | avg_hotelpreis | Berechnung (Kurzerklärung) |
|---|---|---|---|
| Europa | Mai bis September | 153.33 | (London 180 + Prag 100 + Santorini 180) / 3 = 153.333… |
| Europa | Juni bis August | 136.67 | (Stockholm 140 + Edinburgh 120 + Moskau 150 + Zürich 180 + Kopenhagen 150 + Schwarzwald 80) / 6 = 136.666… |
| Europa | April bis Juni | 133.33 | (Paris 150 + Venedig 170 + Istanbul 80) / 3 = 133.333… |
| Europa | Mai bis Oktober | 120.00 | (Rom 130 + Athen 110) / 2 = 120.00 |
| Kontinent | Bestes | avg_hotelpreis |
|---|---|---|
| Nordamerika | Mai bis Oktober | 190.00 |
| Nordamerika | Mai bis September | 170.00 |
| Kontinent | Bestes | avg_hotelpreis |
|---|---|---|
| Afrika | November bis März | 85.00 |
Einzelne (AVG = Einzelwert):
- Asien / September bis Oktober: Peking → avg_hotelpreis = 100 €
- Asien / November bis Februar: Bangkok → 60 €
- Südamerika / Dezember bis März: Rio de Janeiro → 90 €
- Australien / September bis November: Sydney → 150 €
- (und viele weitere Einzelgruppen in der Tabelle)
Fazit: Diese Abfrage ist nützlich, um durchschnittliche Hotelkosten für Kombinationen Kontinent × Reisezeitfenster zu sehen.
Ergänzende Hinweise und typische Stolpersteine
- COUNT(*) vs. COUNT(Spalte): COUNT(*) zählt alle Zeilen in der Gruppe, auch wenn einzelne Spalten NULL sind. COUNT(Spalte) zählt nur Zeilen, in denen Spalte nicht NULL ist.
- Datentyp der Preise: Für MAX/AVG müssen die Hotelkosten numerisch gespeichert sein (z. B. DECIMAL(10,2)). Sind sie als Text mit „€“ gespeichert, ist vor Aggregation ein CAST/REPLACE oder eine saubere Datentypdefinition nötig.
- Schlüssel im GROUP BY: Gruppiert man nach RID und Ort, ändert Ort die Gruppen nicht, solange RID eindeutig ist. Ort muss trotzdem im GROUP BY stehen, weil es im SELECT-Teil ausgegeben wird.
- GROUP-BY-Regel: In allen vier Beispielen sind alle ausgewählten Spalten entweder im GROUP BY enthalten oder sie werden aggregiert. Genau das verlangen auch die Abituraufgaben.
Aufgaben: Abfragen entwickeln und analysieren
Entwickeln Sie geeignete SQL-Anweisungen, die die jeweils gewünschte Ausgabe erzeugen.
- Geben Sie die fünf Reiseziele mit den höchsten mittleren Hotelkosten pro Nacht aus. Zeigen Sie Ort, Land, Kontinent und den Preis an.
- Berechnen Sie den durchschnittlichen mittleren Hotelpreis pro Kontinent und sortieren Sie absteigend nach dem Durchschnittspreis.
- Geben Sie alle Reiseziele aus, die höchstens 2000 km von Deutschland entfernt sind und mittlere Hotelkosten unter 120 € haben. Zeigen Sie Ort, Land, Entfernung und Preis; sortieren Sie nach Entfernung aufsteigend.
- Zeigen Sie nur die Reisezeitfenster, die in der Datenbank für mindestens zwei Ziele angegeben sind, und die jeweilige Anzahl der Ziele. Sortieren Sie nach Anzahl absteigend.
ORDER BY … DESC LIMIT 5.Analysieren Sie die folgenden SQL-Abfragen und gehen Sie auch darauf ein, welche Informationen die jeweilige Abfrage im Sachzusammenhang liefert.
-- (1)
SELECT Kontinent,
MIN(EntfernungVonDeutschland) AS naechste_entfernung,
MAX(EntfernungVonDeutschland) AS entfernteste_entfernung
FROM Reiseziele
GROUP BY Kontinent;-- (2)
SELECT Kontinent,
COUNT(*) AS anzahl_teure_ziele
FROM Reiseziele
WHERE MittlereHotelkostenProNacht > 150
GROUP BY Kontinent
ORDER BY anzahl_teure_ziele DESC;-- (3)
SELECT Land,
COUNT(*) AS anzahl_ziele,
MIN(MittlereHotelkostenProNacht) AS min_preis,
MAX(MittlereHotelkostenProNacht) AS max_preis
FROM Reiseziele
GROUP BY Land
HAVING COUNT(*) > 1
ORDER BY max_preis DESC, min_preis ASC;-- (4)
SELECT BestesReisezeitfenster,
COUNT(*) AS anzahl_ziele
FROM Reiseziele
GROUP BY BestesReisezeitfenster
ORDER BY anzahl_ziele DESC, BestesReisezeitfenster
LIMIT 3;🎉 Ausgezeichnet – Alle Aufgaben abgeschlossen!
Hier finden Sie die Lösungen zu Aufgabe 4.
a)
(1) Geben Sie die fünf Reiseziele mit den höchsten mittleren Hotelkosten pro Nacht aus. Zeigen Sie Ort, Land, Kontinent und den Preis an.
SELECT Ort, Land, Kontinent, MittlereHotelkostenProNacht
FROM Reiseziele
ORDER BY MittlereHotelkostenProNacht DESC
LIMIT 5;(2) Berechnen Sie den durchschnittlichen mittleren Hotelpreis pro Kontinent und sortieren Sie absteigend nach dem Durchschnittspreis.
SELECT Kontinent, ROUND(AVG(MittlereHotelkostenProNacht), 2) AS avg_hotelpreis
FROM Reiseziele
GROUP BY Kontinent
ORDER BY avg_hotelpreis DESC;(3) Geben Sie alle Reiseziele aus, die höchstens 2000 km von Deutschland entfernt sind und mittlere Hotelkosten unter 120 € haben. Zeigen Sie Ort, Land, Entfernung und Preis; sortieren Sie nach Entfernung aufsteigend.
SELECT Ort, Land, EntfernungVonDeutschland, MittlereHotelkostenProNacht
FROM Reiseziele
WHERE EntfernungVonDeutschland <= 2000
AND MittlereHotelkostenProNacht < 120
ORDER BY EntfernungVonDeutschland ASC;(4) Zeigen Sie nur die Reisezeitfenster, die in der Datenbank für mindestens zwei Ziele angegeben sind, und die jeweilige Anzahl der Ziele. Sortieren Sie nach Anzahl absteigend.
SELECT BestesReisezeitfenster, COUNT(*) AS anzahl_ziele
FROM Reiseziele
GROUP BY BestesReisezeitfenster
HAVING COUNT(*) >= 2
ORDER BY anzahl_ziele DESC;b)
(1)
SELECT Kontinent,
MIN(EntfernungVonDeutschland) AS naechste_entfernung,
MAX(EntfernungVonDeutschland) AS entfernteste_entfernung
FROM Reiseziele
GROUP BY Kontinent;Was die Abfrage macht: Für jeden Kontinent wird die minimale und maximale Entfernung von Deutschland aus den Datensätzen ermittelt.
Welche Informationen das im Sachzusammenhang liefert: Zeigt die geographische Spannweite der Reiseziele pro Kontinent (welche Ziele relativ nahe an Deutschland liegen und welche sehr weit entfernt sind).
(2)
SELECT Kontinent,
COUNT(*) AS anzahl_teure_ziele
FROM Reiseziele
WHERE MittlereHotelkostenProNacht > 150
GROUP BY Kontinent
ORDER BY anzahl_teure_ziele DESC;Was die Abfrage macht: Filtert zunächst alle Reiseziele mit mittleren Hotelkosten pro Nacht über 150 € (WHERE). Dann gruppiert sie diese gefilterten Zeilen nach Kontinent und zählt die Anzahl der teuren Ziele pro Kontinent. Abschließend sortiert sie absteigend nach der Anzahl.
Welche Informationen das im Sachzusammenhang liefert: Zeigt an, auf welchen Kontinenten relativ viele teure Reiseziele konzentriert sind (z. B. Kontinente mit hohem Anteil von Zielen mit >150 €/Nacht). Nützlich für Marktsegmentierung und Preisstrategie.
(3)
SELECT Land,
COUNT(*) AS anzahl_ziele,
MIN(MittlereHotelkostenProNacht) AS min_preis,
MAX(MittlereHotelkostenProNacht) AS max_preis
FROM Reiseziele
GROUP BY Land
HAVING COUNT(*) > 1
ORDER BY max_preis DESC, min_preis ASC;Was die Abfrage macht: Gruppiert Einträge pro Land, zählt die Anzahl der Ziele, bestimmt den minimalen und maximalen mittleren Hotelpreis im jeweiligen Land. HAVING filtert Länder mit mehr als einem Eintrag.
Welche Informationen das im Sachzusammenhang liefert: Zeigt die Preisspanne innerhalb von Ländern, aber nur dort, wo das Land mehrere Reiseziele in der Datenbank hat. Damit lassen sich Länder mit großen Preisunterschieden (z. B. Budget vs. Luxus innerhalb desselben Landes) identifizieren.
(4)
SELECT BestesReisezeitfenster,
COUNT(*) AS anzahl_ziele
FROM Reiseziele
GROUP BY BestesReisezeitfenster
ORDER BY anzahl_ziele DESC, BestesReisezeitfenster
LIMIT 3;Was die Abfrage macht: Gruppiert die Reiseziele nach dem Textfeld BestesReisezeitfenster, zählt die Anzahl Ziele je Zeitfenster, sortiert absteigend nach der Häufigkeit und gibt die drei häufigsten Zeitfenster aus (LIMIT 3).
Welche Informationen das im Sachzusammenhang liefert: Identifiziert die drei beliebtesten Saisons/Zeiträume in der Datenbank (d. h. die Zeitfenster, die für die meisten Reiseziele empfohlen werden). Hilfreich für saisonale Planung, Werbung und Angebotsfokussierung.
Gruppieren mit mehreren Tabellen
Im Kontext der Reisedaten könnten wir zwei Tabellen haben – beispielsweise die Tabelle Reiseziele (mit den in den Datensätzen aufgeführten Informationen) und eine zweite Tabelle, die Buchungsinformationen für diese Reiseziele enthält. Dabei kann die zweite Tabelle so definiert werden, dass sie genau auf die Inhalte der ersten Tabelle Bezug nimmt.
Eine mögliche Tabelle und Beispieldaten wären:
| RID | Ort | Land | Kontinent | Entfernung | Durchschnittstemperatur | Beliebteste | Mittlere | Bestes | Besondere |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Rom | Italien | Europa | 1200 km | 25 °C | Kolosseum | 130 € | Mai bis Oktober | Besuch des Vatikans |
| 2 | Dubai | Vereinigte Arabische Emirate | Asien | 4800 km | 40 °C | Burj Khalifa | 250 € | November bis März | Wüstensafari |
| 3 | Sydney | Australien | Australien | 16500 km | 22 °C | Sydney Opera House | 150 € | September bis November | Surfen am Bondi Beach |
| 4 | Mexiko-Stadt | Mexiko | Nordamerika | 9900 km | 20 °C | Zócalo | 80 € | März bis Mai | Besuch der Pyramiden von Teotihuacan |
| … | … | … | … | … | … | … | … | … | … |
Diese zweite Tabelle enthält Informationen zu Buchungen, die für die in der Tabelle Reiseziele gespeicherten Reiseziele vorgenommen wurden. Sie ist eng mit der Tabelle Reiseziele verknüpft (über den Fremdschlüssel RID).
| BuchungsID | ↑RID | Kunde | Buchungsdatum | Anzahl | Gesamtpreis |
|---|---|---|---|---|---|
| 101 | 1 | Anna Müller | 2023-05-01 | 7 | 910 € |
| 102 | 1 | Max Mustermann | 2023-05-03 | 5 | 650 € |
| 103 | 2 | Jane Doe | 2023-06-10 | 4 | 1000 € |
| 104 | 3 | John Smith | 2023-07-15 | 10 | 1500 € |
| 105 | 4 | Maria González | 2023-08-20 | 6 | 480 € |
Die ergänzte Tabelle Buchungen liefert Buchungsinformationen, die über den Fremdschlüssel (RID) mit den Reisezielen verbunden sind.
Mit GROUP BY können Abfragen erstellt werden, die z. B. aggregierte Informationen (wie die Summe aller Buchungspreise) entweder auf der Ebene einzelner Reiseziele (Primärschlüssel) oder auf aggregierter Ebene (z. B. pro Kontinent) anzeigen.
Aufgaben: Buchungen summieren
Entwerfen Sie eine SQL-Abfrage, welche die RID, den Ort, den Kontinent und den Gesamtpreis aller Buchungen aller RID ausgibt.
SUM(b.Gesamtpreis).Entwerfen Sie eine SQL-Abfrage, welche den Kontinent und den Gesamtpreis aller Buchungen der Kontinente ausgibt.
r.Kontinent.Zusammenfassung und Erläuterungen
Beim Gruppieren muss man zwei Fälle unterscheiden.
Gruppierung je Reiseziel (über den Primärschlüssel RID)
SELECT r.RID, r.Ort, r.Land, r.Kontinent,
SUM(b.Gesamtpreis) AS SummeBuchungen
FROM Reiseziele r, Buchungen b
WHERE r.RID = b.RID
GROUP BY r.RID, r.Ort, r.Land, r.Kontinent;Diese SQL-Abfrage gibt für jedes Reiseziel die RID, den Ort, das Land, den Kontinent und die Summe der Gesamtpreise aller zugehörigen Buchungen aus.
Jede Spalte im SELECT-Teil, die nicht in einer Aggregatfunktion steht, muss auch im GROUP BY stehen. Weil die RID jedes Reiseziel eindeutig festlegt, ändern die zusätzlich aufgeführten Spalten Ort, Land und Kontinent die Gruppen nicht: Es entsteht weiterhin genau eine Gruppe je Reiseziel.
Erläuterung:
- Wir verbinden die Tabellen über die Bedingung r.RID = b.RID in der WHERE-Klausel.
- Gruppiert wird je Reiseziel. Ort, Land und Kontinent werden im GROUP BY mit aufgeführt, damit sie im SELECT-Teil stehen dürfen.
- Die Aggregatfunktion SUM(b.Gesamtpreis) berechnet die Summe aller Buchungspreise, die zu diesem Reiseziel gehören.
Hinweis: Manche Datenbanksysteme (z. B. MySQL) akzeptieren auch GROUP BY r.RID allein, weil Ort, Land und Kontinent von der RID abhängen. In den Abituraufgaben gilt aber die Regel oben: Alle nicht aggregierten Spalten des SELECT-Teils stehen im GROUP BY.
Gruppierung anhand eines Nicht-Schlüssel-Attributs (Kontinent)
SELECT r.Kontinent, SUM(b.Gesamtpreis) AS SummeBuchungen
FROM Reiseziele r, Buchungen b
WHERE r.RID = b.RID
GROUP BY r.Kontinent;Diese SQL-Abfrage gibt den Kontinent und den Gesamtpreis aller Buchungen der Kontinente aus.
Wenn wir hingegen ausschließlich nach einem (oder mehreren) Nicht-Schlüssel-Attribut(en) wie dem Kontinent gruppieren, dürfen im SELECT-Teil nur diese Gruppierkriterien (hier r.Kontinent) und Aggregatfunktionen stehen. Eine Gruppe wie „Europa“ enthält viele verschiedene Orte – welcher davon sollte ausgegeben werden?
Erläuterung:
- Im GROUP BY wird ausschließlich nach r.Kontinent gruppiert.
- Dementsprechend erscheint im SELECT-Teil nur r.Kontinent (das Gruppierungskriterium) und die aggregierte Gesamtsumme der Buchungen.
- Würden weitere nicht gruppierte, nicht aggregierte Spalten (z. B. r.Ort oder r.Land) im SELECT stehen, wäre nicht festgelegt, welcher Wert der Gruppe ausgegeben wird. Die Anweisung wäre fehlerhaft.
Zusammenfassung
- Regel: Jede Spalte im SELECT-Teil steht entweder in einer Aggregatfunktion oder im GROUP BY.
- Im ersten Beispiel entsteht über die RID je Reiseziel eine Gruppe. Weitere Spalten derselben Tabelle (Ort, Land, Kontinent) werden zusätzlich ins GROUP BY aufgenommen, ohne dass sich die Gruppen ändern.
- Im zweiten Beispiel fasst eine Gruppe viele Reiseziele eines Kontinents zusammen. Einzelwerte wie der Ort lassen sich dann nur noch über Aggregatfunktionen (z. B. COUNT(r.Ort)) ausgeben.
Aufgaben: Universitätsverwaltung
Im Folgenden wird ein neuer Datenbankkontext vorgestellt, der drei Tabellen umfasst. Anhand dieses Beispiels müssen mehrere SQL-Anweisungen zur Anwendung von GROUP BY entwickelt werden. Wir betrachten folgende drei Tabellen für eine Universitätsverwaltung:
| SID | Name | Hauptfach | Einschreibungsjahr |
|---|---|---|---|
| 1 | Anna Schmidt | Informatik | 2020 |
| 2 | Max Müller | Betriebswirtschaft | 2019 |
| 3 | Julia Fischer | Maschinenbau | 2021 |
| … | … | … | … |
| CID | Titel | Fachbereich |
|---|---|---|
| 101 | Datenbanken | Informatik |
| 102 | Marketing Grundlagen | Betriebswirtschaft |
| 103 | Thermodynamik | Maschinenbau |
| … | … | … |
| BID | ↑SID | ↑CID | Note |
|---|---|---|---|
| 1001 | 1 | 101 | 1.0 |
| 1002 | 1 | 102 | 5.0 |
| 1003 | 2 | 102 | 3.7 |
| 1004 | 3 | 103 | 2.3 |
| … | … | … | … |
Die folgenden zu entwickelnden SQL-Anweisungen sind unterschiedlich schwierig. Bearbeitet mindestens drei mit Standardschwierigkeit, zwei mit schwierigerem und eine mit schwerem Niveau.
| Standard | Schwieriger | Schwer |
|---|---|---|
| Für jeden Studierenden (aus der Tabelle Studierende) soll ermittelt werden, in wie vielen Kursen er eingeschrieben ist. | Für jeden Studiengang sollen die Gesamtzahl der Einschreibungen sowie der Durchschnitt der Noten berechnet werden. | Für jeden Kurs (CID, Titel und Fachbereich) sollen die Gesamtzahl der Einschreibungen und die durchschnittliche Note angezeigt werden. |
| Für jeden Studierenden sollen SID, Name, Hauptfach und der Durchschnitt der vergebenen Noten angezeigt werden. | Berechne, wie viele Einschreibungen in jedem Einschreibungsjahr vorgenommen wurden, und zeigen Sie nur die Jahre an, in denen mehr als 3 Einschreibungen verzeichnet wurden. | Für jeden Fachbereich sollen die niedrigste Note (MIN), die höchste Note (MAX) und der Durchschnitt der Noten (AVG) berechnet werden – jedoch nur für Fachbereiche mit mindestens 3 Einschreibungen in den zugehörigen Kursen. |
| Für jeden Fachbereich soll die durchschnittliche Note aller in dessen Kursen vergebenen Noten ausgegeben werden. | Für jede Kombination aus Studiengang (Hauptfach) und Einschreibungsjahr soll der Durchschnitt der vergebenen Noten ausgegeben werden. | Für jeden Fachbereich aus der Tabelle Kurse soll ermittelt werden: 1. Die Anzahl der angebotenen Kurse 2. Die Gesamtzahl der Einschreibungen in diesen Kursen (aus der Tabelle Belegung) Es sollen nur Fachbereiche angezeigt werden, in denen mindestens 2 Kurse angeboten werden. Die Ergebnisse werden abschließend absteigend nach der Gesamtzahl der Einschreibungen sortiert. |
| Für jeden Kurs (aus der Tabelle Kurse) wird der durchschnittliche Notendurchschnitt der Einschreibungen ermittelt. | Für jeden Kurs (Titel) innerhalb eines Fachbereichs sollen der Durchschnitt der Noten und die Anzahl der Einschreibungen ermittelt werden – jedoch nur für Kurse mit mindestens 2 Einschreibungen. | Für jeden Kurs (aus der Tabelle Kurse) soll ermittelt werden: 1. Der Kurstitel 2. Die Anzahl der Einschreibungen 3. Der Durchschnitt der Noten in diesem Kurs. Es sollen nur jene Kurse angezeigt werden, in denen mehr als 5 Einschreibungen verzeichnet sind. Die Ergebnisse werden abschließend aufsteigend nach dem Kurstitel sortiert. |
COUNT(DISTINCT k.CID) zählt jeden Kurs nur einmal.Stellen Sie sich mit einer anderen Partnergruppe Ihre Lösungen aus Aufgabenteil a) vor und diskutieren Sie, ob die jeweiligen Lösungen korrekt sind. Gehen Sie auch auf Gemeinsamkeiten und Unterschiede ein.
🎉 Ausgezeichnet – Alle Aufgaben abgeschlossen!
Hier finden Sie die Lösungen zu den Aufgaben 5 und 6.
Aufgabe 5 a)
Anforderung: Ausgabe der RID, des Ortes, des Kontinents und des Gesamtpreises aller Buchungen pro RID:
SELECT r.RID, r.Ort, r.Kontinent, SUM(b.Gesamtpreis) AS SummeBuchungen
FROM Reiseziele r
JOIN Buchungen b ON r.RID = b.RID
GROUP BY r.RID, r.Ort, r.Kontinent;- Wir verknüpfen Tabellen via JOIN ON r.RID = b.RID.
- Gruppiert wird pro Reiseziel. Weil r.Ort und r.Kontinent im SELECT-Teil stehen, gehören sie nach der GROUP-BY-Regel auch ins GROUP BY. Da r.RID der Primärschlüssel ist, ändern die zusätzlichen Spalten an den Gruppen nichts.
- SUM(b.Gesamtpreis) summiert die Buchungspreise für das jeweilige Reiseziel.
Beispiel mit den angegebenen Buchungen:
- RID 1: 910 €, 650 € → Summe 1 560 €
- RID 2: 1 000 € → Summe 1 000 €
- RID 3: 1 500 € → Summe 1 500 €
- RID 4: 480 € → Summe 480 €
Aufgabe 5 b)
Anforderung: Ausgabe je Kontinent die Summe aller Buchungen:
SELECT r.Kontinent, SUM(b.Gesamtpreis) AS SummeBuchungen
FROM Reiseziele r
JOIN Buchungen b ON r.RID = b.RID
GROUP BY r.Kontinent;- Gruppierung nach Kontinent; alle Buchungen der Reiseziele dieses Kontinents werden zusammengefasst.
- Ausgabe enthält nur die Gruppierungsspalte (r.Kontinent) und Aggregat (SUM). Würde man hier nicht gruppierte Spalten aufnehmen (z. B. r.Ort), würde dies in manchen SQL-Konfigurationen einen Fehler erzeugen.
Aufgabe 6
Standard
(1) Für jeden Studierenden (aus der Tabelle Studierende) soll ermittelt werden, in wie vielen Kursen er eingeschrieben ist.
SELECT s.SID, s.Name, COUNT(b.CID) AS AnzahlKurse
FROM Studierende s
JOIN Belegung b ON s.SID = b.SID
GROUP BY s.SID, s.Name;(2) Für jeden Studierenden sollen SID, Name, Hauptfach und der Durchschnitt der vergebenen Noten angezeigt werden.
SELECT s.SID, s.Name, s.Hauptfach, AVG(b.Note) AS Durchschnittsnote
FROM Studierende s
JOIN Belegung b ON s.SID = b.SID
GROUP BY s.SID, s.Name, s.Hauptfach;(3) Für jeden Fachbereich soll die durchschnittliche Note aller in dessen Kursen vergebenen Noten ausgegeben werden.
SELECT k.Fachbereich, AVG(b.Note) AS Durchschnittsnote
FROM Kurse k
JOIN Belegung b ON k.CID = b.CID
GROUP BY k.Fachbereich;(4) Für jeden Kurs (aus der Tabelle Kurse) wird der durchschnittliche Notendurchschnitt der Einschreibungen ermittelt.
SELECT k.CID, k.Titel, AVG(b.Note) AS Durchschnittsnote
FROM Kurse k
JOIN Belegung b ON k.CID = b.CID
GROUP BY k.CID, k.Titel;Schwieriger
(1) Für jeden Studiengang sollen die Gesamtzahl der Einschreibungen sowie der Durchschnitt der Noten berechnet werden.
SELECT s.Hauptfach, COUNT(b.BID) AS GesamtEinschreibungen, AVG(b.Note) AS Durchschnittsnote
FROM Studierende s
JOIN Belegung b ON s.SID = b.SID
GROUP BY s.Hauptfach;(2) Berechne, wie viele Einschreibungen in jedem Einschreibungsjahr vorgenommen wurden, und zeigen Sie nur die Jahre an, in denen mehr als 3 Einschreibungen verzeichnet wurden.
SELECT s.Einschreibungsjahr, COUNT(b.BID) AS AnzahlEinschreibungen
FROM Studierende s
JOIN Belegung b ON s.SID = b.SID
GROUP BY s.Einschreibungsjahr
HAVING COUNT(b.BID) > 3;(3) Für jede Kombination aus Studiengang (Hauptfach) und Einschreibungsjahr soll der Durchschnitt der vergebenen Noten ausgegeben werden.
SELECT s.Hauptfach, s.Einschreibungsjahr, AVG(b.Note) AS Durchschnittsnote
FROM Studierende s
JOIN Belegung b ON s.SID = b.SID
GROUP BY s.Hauptfach, s.Einschreibungsjahr;(4) Für jeden Kurs (Titel) innerhalb eines Fachbereichs sollen der Durchschnitt der Noten und die Anzahl der Einschreibungen ermittelt werden – jedoch nur für Kurse mit mindestens 2 Einschreibungen.
SELECT k.Fachbereich, k.Titel, COUNT(b.BID) AS AnzahlEinschreibungen, AVG(b.Note) AS Durchschnittsnote
FROM Kurse k
JOIN Belegung b ON k.CID = b.CID
GROUP BY k.CID, k.Fachbereich, k.Titel
HAVING COUNT(b.BID) >= 2;Schwer
(1) Für jeden Kurs (CID, Titel und Fachbereich) sollen die Gesamtzahl der Einschreibungen und die durchschnittliche Note angezeigt werden.
SELECT k.CID, k.Titel, k.Fachbereich, COUNT(b.BID) AS AnzahlEinschreibungen, AVG(b.Note) AS Durchschnittsnote
FROM Kurse k
JOIN Belegung b ON k.CID = b.CID
GROUP BY k.CID, k.Titel, k.Fachbereich;(2) Für jeden Fachbereich sollen die niedrigste Note (MIN), die höchste Note (MAX) und der Durchschnitt der Noten (AVG) berechnet werden – jedoch nur für Fachbereiche mit mindestens 3 Einschreibungen in den zugehörigen Kursen.
SELECT k.Fachbereich, MIN(b.Note) AS MinNote, MAX(b.Note) AS MaxNote, AVG(b.Note) AS AvgNote, COUNT(b.BID) AS AnzahlEinschreibungen
FROM Kurse k
JOIN Belegung b ON k.CID = b.CID
GROUP BY k.Fachbereich
HAVING COUNT(b.BID) >= 3;(3) Für jeden Fachbereich aus der Tabelle Kurse soll ermittelt werden:
1. Die Anzahl der angebotenen Kurse
2. Die Gesamtzahl der Einschreibungen in diesen Kursen (aus der Tabelle Belegung)
Es sollen nur Fachbereiche angezeigt werden, in denen mindestens 2 Kurse angeboten werden. Die Ergebnisse werden abschließend absteigend nach der Gesamtzahl der Einschreibungen sortiert.
SELECT k.Fachbereich, COUNT(DISTINCT k.CID) AS AnzahlKurse, COUNT(b.BID) AS GesamtEinschreibungen
FROM Kurse k
JOIN Belegung b ON k.CID = b.CID
GROUP BY k.Fachbereich
HAVING COUNT(DISTINCT k.CID) >= 2
ORDER BY GesamtEinschreibungen DESC;(4) Für jeden Kurs (aus der Tabelle Kurse) soll ermittelt werden:
1. Der Kurstitel
2. Die Anzahl der Einschreibungen
3. Der Durchschnitt der Noten in diesem Kurs.
Es sollen nur jene Kurse angezeigt werden, in denen mehr als 5 Einschreibungen verzeichnet sind. Die Ergebnisse werden abschließend aufsteigend nach dem Kurstitel sortiert.
SELECT k.Titel, COUNT(b.BID) AS AnzahlEinschreibungen, AVG(b.Note) AS Durchschnittsnote
FROM Kurse k
JOIN Belegung b ON k.CID = b.CID
GROUP BY k.CID, k.Titel
HAVING COUNT(b.BID) > 5
ORDER BY k.Titel ASC;Hinweise zur Praxis, Performance und Sonderfälle
- NULL-Handling: AVG, MIN, MAX und SUM ignorieren NULL-Werte. Falls Noten NULL sein können und man z. B. 0 als Note interpretieren will, verwendet man COALESCE(b.Note, 0) — aber in der Regel ist NULL besser (keine Note vorhanden).
- LEFT JOIN vs. JOIN: LEFT JOIN verwendet man, wenn auch Gruppen ohne zugehörige Zeilen gezeigt werden sollen (z. B. Kurse ohne Einschreibungen oder Studierende ohne Kurse). INNER JOIN verwendet man, wenn nur existierende Kombinationen gewünscht sind (z. B. Fachbereiche, die tatsächlich Noten haben).
- HAVING vs. WHERE: WHERE filtert Zeilen vor der Aggregation; HAVING filtert nach der Aggregation (z. B. COUNT(…) > N).
- Performance: Indizes auf Join- und Gruppierungsschlüsseln helfen: z. B. Belegung(SID), Belegung(CID), Kurse(CID), Studierende(SID), Buchungen(RID).
- GROUP-BY-Regel: Im GROUP BY stehen immer alle nicht aggregierten Spalten des SELECT-Teils – auch dann, wenn bereits nach dem Primärschlüssel gruppiert wird.
Geschachtelte Select-Ausdrücke
Das Ergebnis einer SQL-Abfrage wird oft in einer weiteren, äußeren SQL-Abfrage genutzt. Man erhält so geschachtelte Abfragen. Die Videoplattform-Datenbank zu WeTube hat die folgende Form.
| AccountID | Username | Adresse | Lebensalter | |
|---|---|---|---|---|
| 1 | FinnW | finn.wagner | Bergstraße 3, 90402 Nürnberg | 27 |
| 2 | AmirA | amir.ali | Allee 10, 50667 Köln | 24 |
| 3 | AishaB | aisha.ben | Straße 5, 10115 Berlin | 20 |
| 4 | NoahS | noah.schmidt | Schulstraße 12, 80331 München | 25 |
| 5 | EmmaT | emma.taylor | Gartenstraße 8, 50667 Köln | 21 |
| 6 | LaraM | lara.meyer | Seestraße 4, 20359 Hamburg | 18 |
| 7 | LaraM2 | lara.meier | Lindenweg 5, 20257 Hamburg | 19 |
| 8 | LeoK | leo.klein | Hauptstraße 1, 10115 Berlin | 22 |
| … | … | … | … | … |
| ChannelID | Channel | Channel | ↑AccountID |
|---|---|---|---|
| 1 | Aisha’s Tech Hub | Neueste Technik und Gadgets | 3 |
| 2 | Aisha’s Musik | What’s hot? | 3 |
| 3 | Gaming mit Aisha | Endgegner! | 3 |
| 4 | Emma’s Creations | Kreative Kunst und DIY-Projekte | 5 |
| 5 | Amir’s World | Kulturelle Einblicke und Reisen | 2 |
| 6 | Lara’s Lifestyle | Lifestyle und persönliche Einblicke | 7 |
| 7 | Finn’s Reisen | Super Kulissen für Bilder | 1 |
| 8 | Lara’s Kitchen | Leckere Rezepte und Tipps | 6 |
| 9 | Noah’s Vlogs | Einblicke in die Kultur und Lifestyle | 4 |
| 10 | Finn’s Fitness | Fitness und Gesundheitstipps | 1 |
| 11 | Cats&Emma | How cute | 5 |
| 12 | Leo’s Travels | Abenteuer und Reisen | 8 |
| … | … | … | … |
| VideoID | Titel | Beschreibung | Upload |
|---|---|---|---|
| 1 | Abenteuer in den Alpen | Ein aufregender Trip in die Berge | 20251001 |
| 2 | Vegane Pasta selbst gemacht | Schritt-für-Schritt-Anleitung für Pasta | 20251005 |
| 3 | Aisha’s Smartphone Reviews | Vergleich der neuesten Modelle | 20251010 |
| 4 | Malen für Anfänger | Tipps und Tricks für Einsteiger | 20251012 |
| 5 | 30-Minuten HIIT Workout | Effektives Training für zu Hause | 20251015 |
| 6 | Ein Tag in Barcelona | Vlog über meine Reise nach Barcelona | 20251017 |
| 7 | Amir in Istanbul | Kulturelle Erlebnisse in der Türkei | 20251020 |
| 8 | Noah’s Kochkünste | Traditionelle Gerichte aus dem Allgäu | 20251022 |
| … | … | … | … |
| KommentarID | Kommentar | Erstellungsdatum | Rating | ↑AccountID | ↑VideoID |
|---|---|---|---|---|---|
| 1 | Toller Vlog, Leo! | 20251002 | 5 | 6 | 6 |
| 2 | Ich liebe vegane Pasta! | 20251006 | 4 | 1 | 2 |
| 3 | Das Rezept klingt lecker, Noah! | 20251011 | 4 | 3 | 2 |
| 4 | Wunderschöne Kunstwerke, Emma! | 20251013 | 5 | 6 | 4 |
| 5 | Das Workout war super, Finn! | 20251016 | 3 | 5 | 5 |
| 6 | Barcelona sieht toll aus! | 20251018 | 2 | 8 | 6 |
| 7 | Toller Einblick in die Kultur! | 20251021 | 4 | 8 | 1 |
| 8 | Sehr informativ, danke Aisha! | 20251023 | 4 | 7 | 3 |
| … | … | … | … | … | … |
| ↑ChannelID | ↑VideoID |
|---|---|
| 1 | 3 |
| 8 | 2 |
| 4 | 4 |
| … | … |
Tabellendarstellung
Channels(ChannelID, ↑AccountID, ChannelName, ChannelBeschreibung)
Videos(VideoID, Titel, Beschreibung, UploadDatum)
Kommentare(KommentarID, ↑VideoID, ↑AccountID, KommentarText, Erstellungsdatum, Rating)
ChannelContent(↑ChannelID, ↑VideoID)
Aufgaben: Geschachtelte Select-Ausdrücke
Analysieren Sie die folgende SQL-Abfrage und erläutern Sie ihre Funktionsweise.
SELECT ChannelName
FROM Channels
WHERE AccountID = (
SELECT AccountID
FROM Channels
WHERE ChannelName = 'Cats&Emma'
);Erläutern Sie die folgende SQL-Abfrage und geben Sie an, was ausgegeben wird.
SELECT v.Titel
FROM Videos AS v
INNER JOIN ChannelContent AS cc
ON v.VideoID = cc.VideoID
INNER JOIN Channels AS ch
ON cc.ChannelID = ch.ChannelID
WHERE ch.ChannelID = (
SELECT ChannelID
FROM Channels
WHERE ChannelName = 'Lara''s Kitchen'
)
ORDER BY v.Titel ASC;'Lara''s Kitchen'.Ersetzen Sie die Verbindung mit Inner Join durch geeignete WHERE-Klauseln.
Entwickeln Sie für die folgenden Ausgaben geeignete SQL-Anweisungen.
UploadDatum LIKE '2025%'; doppelte Zeilen vermeidet DISTINCT.Die folgenden Aufgaben werden mit einer Datenbank zur Videoplattform bearbeitet. Diese findet man hier: https://www.sql-ide.de/. Die Daten zur Anmeldung bekommen Sie von Ihrer Lehrkraft.
Die Datenbank zu unserer Videoplattform „WeTube“ hat nun das folgende relationale Schema.
Channels(ChannelID, ↑AccountID, ChannelName, ChannelBeschreibung)
Videos(VideoID, Titel, Beschreibung, UploadDatum)
Kommentare(KommentarID, ↑VideoID, ↑AccountID, KommentarText, Erstellungsdatum, Rating)
ChannelContent(↑ChannelID, ↑VideoID)
Entwickeln Sie für die folgenden Ausgaben geeignete SQL-Anweisungen und testen Sie diese in der IDE. Die Aufgaben finden Sie im nächsten Abschnitt „SQL-Aufgaben zur Videoplattform“.
🎉 Ausgezeichnet – Alle Aufgaben abgeschlossen!
Hier finden Sie die Lösungen zu den geschachtelten Select-Ausdrücken.
Aufgabe 1
Möchte man zum Beispiel alle Kanäle des Accounts, der hinter Cats&Emma steht, finden, kann dies zweistufig geschehen.
SELECT AccountID
FROM Channels
WHERE ChannelName = 'Cats&Emma';| AccountID |
|---|
| 5 |
Dieses Ergebnis kann nun genutzt werden.
SELECT ChannelName
FROM Channels
WHERE AccountID = (
SELECT AccountID
FROM Channels
WHERE ChannelName = 'Cats&Emma'
);| Channel |
|---|
| Emma’s Creations |
| Cats&Emma |
Es gibt also zunächst eine innere Abfrage, welche eine Ergebnisrelation liefert. Diese wird dann als Input-Tabelle für die äußere Anweisung genutzt. Das Ergebnis einer SQL-Anweisung ist wieder eine Tabelle und kann als solche auch als Input für eine weitere SQL-Anweisung genutzt werden. Man kann SQL-Anweisungen also auch ineinander schachteln.
Aufgabe 2 a) Erklärung der gegebenen Abfrage
- Von der Tabelle Videos werden die Titelfelder ausgewählt.
- Videos werden über ChannelContent mit Channels verknüpft (cc verbindet ChannelID mit VideoID).
- Die WHERE-Klausel schränkt die Ergebnisse auf genau den Kanal ein, dessen ChannelID gleich dem Ergebnis der Subquery ist.
- Die Subquery sucht die ChannelID des Kanals mit dem Namen „Lara’s Kitchen“.
- Ergebnis: alle Videotitel, die im Kanal „Lara’s Kitchen“ veröffentlicht wurden, alphabetisch sortiert.
Hinweis: Wenn es mehrere Channels mit exakt gleichem Namen gäbe, würde die Subquery unter Umständen mehrere Zeilen liefern und die äußere Abfrage mit = fehlschlagen. In dem Fall IN statt = verwenden.
Beispielausgabe (auf Basis der bereitgestellten Daten): Channel „Lara’s Kitchen“ hat VideoID 2 zugeordnet; VideoID 2 → Titel: „Vegane Pasta selbst gemacht“. Ergebnis (sortiert): Vegane Pasta selbst gemacht.
Hinweis zur Schreibweise: Ein Apostroph innerhalb einer Zeichenkette wird in SQL verdoppelt: 'Lara''s Kitchen'.
Aufgabe 2 b) Ersetzen der INNER JOINs durch WHERE-Klauseln
-- Alternative Form (implizite Joins)
SELECT v.Titel
FROM Videos v, ChannelContent cc, Channels ch
WHERE v.VideoID = cc.VideoID
AND cc.ChannelID = ch.ChannelID
AND ch.ChannelID = (
SELECT ChannelID FROM Channels
WHERE ChannelName = 'Lara''s Kitchen'
)
ORDER BY v.Titel ASC;-- Alternative (vereinfachte WHERE ohne Subquery)
SELECT v.Titel
FROM Videos v, ChannelContent cc, Channels ch
WHERE v.VideoID = cc.VideoID
AND cc.ChannelID = ch.ChannelID
AND ch.ChannelName = 'Lara''s Kitchen'
ORDER BY v.Titel ASC;- Die relationsverknüpfenden Bedingungen sind nun in der WHERE-Klausel statt in JOINs definiert.
- Die Funktionalität entspricht der ursprünglichen Abfrage (sofern ChannelName eindeutig ist).
Aufgabe 3
(1) Geben Sie alle ChannelName der Kanäle aus, die vom Account mit dem Username „AishaB“ betrieben werden.
-- mit JOIN
SELECT ch.ChannelName
FROM Channels ch
JOIN Accounts a ON ch.AccountID = a.AccountID
WHERE a.Username = 'AishaB';-- mit Subquery
SELECT ChannelName
FROM Channels
WHERE AccountID = (
SELECT AccountID FROM Accounts WHERE Username = 'AishaB'
);Gesucht werden alle Channels, deren AccountID der AccountID des Benutzers AishaB entspricht. Beispielausgabe: Aisha’s Tech Hub, Aisha’s Musik, Gaming mit Aisha.
(2) Geben Sie alle Videotitel aus, die im Kanal „Emma’s Creations“ veröffentlicht wurden. Sortieren Sie alphabetisch nach Titel.
SELECT v.Titel
FROM Videos v
JOIN ChannelContent cc ON v.VideoID = cc.VideoID
JOIN Channels ch ON cc.ChannelID = ch.ChannelID
WHERE ch.ChannelName = 'Emma''s Creations'
ORDER BY v.Titel ASC;Videos werden über ChannelContent mit Channels verknüpft und nach ChannelName gefiltert. Beispielausgabe: Für Channel „Emma’s Creations“ (ChannelID 4) ist VideoID 4 zugeordnet: Malen für Anfänger.
(3) Geben Sie die Email-Adresse des Accounts aus, der den Kanal „Cats&Emma“ betreibt.
SELECT a.Email
FROM Accounts a
JOIN Channels ch ON a.AccountID = ch.AccountID
WHERE ch.ChannelName = 'Cats&Emma';Über einen JOIN von Channels zu Accounts wird ermittelt, welcher Account hinter dem Kanal steht, und dessen Email ausgegeben. Beispielausgabe: Kanal „Cats&Emma“ gehört zu AccountID 5 (EmmaT): emma.taylor@example.com
(4) Geben Sie alle Videotitel aus, die im Kanal „Lara’s Kitchen“ veröffentlicht wurden, alphabetisch sortiert. (Variante mit Username des Kanalbetreibers)
SELECT a.Username, v.Titel
FROM Accounts a
JOIN Channels ch ON a.AccountID = ch.AccountID
JOIN ChannelContent cc ON ch.ChannelID = cc.ChannelID
JOIN Videos v ON cc.VideoID = v.VideoID
WHERE ch.ChannelName = 'Lara''s Kitchen'
ORDER BY v.Titel ASC;- Benötigt werden der Username des Kanal-Betreibers und die Videotitel; daher die Verknüpfung Accounts → Channels → ChannelContent → Videos. Werden nur die Titel verlangt, lässt man a.Username im SELECT weg.
- Sortierung nach Videotitel (alphabetisch).
- Beispielausgabe: Kanal „Lara’s Kitchen“ (ChannelID 8) hat VideoID 2: Username LaraM, Titel „Vegane Pasta selbst gemacht“.
(5) Geben Sie Username und ChannelName aller Kanäle aus, die mindestens ein Video im Jahr 2025 veröffentlicht haben. Sortieren Sie nach Username und ChannelName aufsteigend. Vermeiden Sie Duplikate.
SELECT DISTINCT a.Username, ch.ChannelName
FROM Accounts a
JOIN Channels ch ON a.AccountID = ch.AccountID
JOIN ChannelContent cc ON ch.ChannelID = cc.ChannelID
JOIN Videos v ON cc.VideoID = v.VideoID
WHERE v.UploadDatum LIKE '2025%'
ORDER BY a.Username ASC, ch.ChannelName ASC;- v.UploadDatum LIKE ‚2025%‘ filtert alle Videos, deren UploadDatum mit 2025 beginnt (z. B. ‚20251017‘).
- DISTINCT verhindert doppelte Zeilen, falls ein Kanal mehrere 2025-Videos hat.
- Sortierung nach Username und ChannelName aufsteigend.
- Beispielausgabe (teilweise, basierend auf den gegebenen Zuordnungen): AishaB — Aisha’s Tech Hub; EmmaT — Emma’s Creations; LaraM — Lara’s Kitchen (die vollständige Liste hängt von den vollständigen ChannelContent-Zuordnungen ab).
(6) Geben Sie Username und Videotitel für alle Videos aus, die in Kanälen von Accounts mit Adresse „Berlin“ im Jahr 2025 hochgeladen wurden. Sortieren Sie nach Username (ASC) und UploadDatum (ASC).
SELECT a.Username, v.Titel, v.UploadDatum
FROM Accounts a
JOIN Channels ch ON a.AccountID = ch.AccountID
JOIN ChannelContent cc ON ch.ChannelID = cc.ChannelID
JOIN Videos v ON cc.VideoID = v.VideoID
WHERE a.Adresse LIKE '%Berlin%'
AND v.UploadDatum LIKE '2025%'
ORDER BY a.Username ASC, v.UploadDatum ASC;- a.Adresse LIKE ‚%Berlin%‘ filtert Accounts mit Adresse, die „Berlin“ enthält.
- v.UploadDatum LIKE ‚2025%‘ schränkt auf Uploads im Jahr 2025 ein.
- Sortierung zuerst nach Username, dann nach UploadDatum.
- Wenn nur die Titel ausgegeben werden sollen, kann man nur v.Titel auswählen, die ORDER BY-Klauseln bleiben gleich; sinnvoll ist jedoch, Username im SELECT zu haben, wenn danach sortiert wird.
- Beispielausgabe: Accounts mit Adresse Berlin sind z. B. AishaB (Straße 5, 10115 Berlin) und LeoK (Hauptstraße 1, 10115 Berlin). Bekanntes 2025-Video von AishaB (über ChannelContent → Channel 1 → VideoID 3): AishaB — Aisha’s Smartphone Reviews — 20251010. Weitere Treffer hängen von der vollständigen ChannelContent-Tabelle ab.
Aufgabe 4
Dies ist eine Aufgabe zur Online-Datenbank der Videoplattform „WeTube“ zu allen SQL-Themen. Die Lösungen zu den SQL-Aufgaben sind unter folgendem Link einsehbar: Lösungen zu den SQL-Aufgaben.
SQL-Aufgaben zur Videoplattform
Die folgenden Aufgaben werden in der SQL-IDE mit der WeTube-Datenbank bearbeitet (siehe Aufgabe 4 oben).
Normalized = Rating / 5.0
Boosted = POWER(Normalized, 2)
Zeigen Sie KommentarID, Rating, Normalized und Boosted.
ORDER BY … ASC/DESC, LIKE '%…%', LENGTH(), Vergleichsoperatoren wie >=.Rating / 5.0 AS NormalizedRating.date('now', '-30 day'), in MySQL DATE_SUB(CURDATE(), INTERVAL 30 DAY).ROUND(AVG(Rating), 2), gefiltert nach der Gruppierung mit HAVING.🎉 Ausgezeichnet – Alle Aufgaben abgeschlossen!
Die Musterlösungen zu diesen SQL-Aufgaben stehen online bereit.
Datenbankentwurf – Entity-Relationship-Modelle
Bevor eine komplexe Datenbank erstellt wird, entwirft man zunächst ein Modell der gewünschten Datenbank, um allen Anforderungen im Sachzusammenhang zu genügen.
Es wird eine graphische Darstellung aufgestellt, ein Entity-Relationship-Modell (ER-Modell). Dies haben Sie bereits in Jahrgang 10 und zu Beginn des Themas Datenbanken vor einiger Zeit gemacht. Zur Erinnerung das ER-Modell zur Partyplanung aus Jahrgang 10:
Schauen wir uns ein weiteres konkretes Beispiel an.
In einer Firma gibt es Projekte, die einen Namen, ein Anfangsdatum und ein Budget haben. Weiterhin muss jedes Projekt von genau einem Mitarbeiter geleitet werden. Ein Mitarbeiter kann prinzipiell mehrere Projekte leiten. Jeder Mitarbeiter hat eine ID sowie einen Vor- und Nachnamen.
Das ER-Modell zum oben beschriebenen Szenario sieht wie folgt aus:
Die Kardinalitäten machen deutlich, in welcher Beziehung die einzelnen Entitäten zueinander stehen. Die Relation leitet ist hier eine 1:n-Relation, die sich aus dem Sachzusammenhang ergibt: Jedes Projekt wird von genau einem Mitarbeiter geleitet, und jeder Mitarbeiter kann bis zu n Projekte leiten. Da jeder Mitarbeiter eine unbestimmte Anzahl von Projekten leiten kann, wird die Variable n genutzt. Der Name einer Relation kann beliebig gestaltet werden. Hier liest man die Relation von links nach rechts. Man hätte sie auch wird geleitet von nennen können.
Es gibt noch zwei weitere Kardinalitäten beziehungsweise Relationstypen, die wir hier betrachten. (Es gibt noch weitere Möglichkeiten für Kardinalitäten, jedoch sollen uns an der Schule die drei hier betrachteten genügen.) Hierzu jeweils ein weiteres Beispiel.
Studenten besuchen Vorlesungen
Da eine Vorlesung von mehreren Studenten besucht werden und ein Student mehrere Vorlesungen besuchen kann, sind zwei Variablen notwendig. Dies nennt man n:m-Relation.
Personen haben DNA-Profile
Da jede Person genau ein DNA-Profil hat und jedes DNA-Profil zu genau einer Person passt, steht hier zweimal eine 1. Dies nennt man 1:1-Relation.
Aufgaben: Onlinebücherei
Eine Onlinebücherei hat Bücher aus verschiedenen Kategorien im Angebot. Ein Buch hat einen Titel, einen Autor, einen Preis und eine ISBN-Nummer. Der Onlinehändler verwaltet außerdem die Verlage der Bücher digital, mit Namen, Firmensitz und Ansprechpartner. Weiterhin muss jeder Kunde einen Nutzernamen, Vor- / Nachnamen, Adresse, Emailadresse und ein Passwort haben. Bei einer Bestellung wird eine Anzahl für jeden Artikel gefordert. Die Webseite bietet für jedes Buch eine Darstellung an, in welcher eine durchschnittliche Bewertung und ob es lieferbar oder nicht ist zu finden ist.
Erstellen Sie ein geeignetes ER-Modell zum oben beschriebenen Szenario.
Treffen Sie sich mit einer zweiten Partnergruppe, tauschen Sie sich zu Ihren Modellen aus und entwickeln Sie ein gemeinsames Modell.
🎉 Ausgezeichnet – Alle Aufgaben abgeschlossen!
Hier finden Sie einen Lösungsvorschlag zur Onlinebücherei.
Nach der Bearbeitung der Aufgabe stellt eine Person des Kurses die Lösung ihrer Gruppe vor und moderiert die folgende Phase. Die anderen Gruppen sollen eigene Anmerkungen und Ergänzungen vorschlagen, woraufhin sich die gesamte Lerngruppe auf eine gemeinsame Kurslösung einigt.
Datenbankentwurf mit agilen Methoden
Agile Methoden sind in Wirtschaft und Wissenschaft moderne Arbeitsformen, welche unter anderem die folgenden besonderen Eigenschaften haben:
- Es geht um das Lösen kleinerer Aufgaben, welche aus der Aufteilung eines großen Problems entstanden sind,
- Es werden Selbstorganisation und Eigenverantwortlichkeit gefördert und das „Management“ ist im eigentlichen Entwicklungsprozess nicht involviert,
- Sie sind besonders für Teams geeignet, die häufig Schritte überdenken und anpassen müssen (hier Schülerinnen und Schüler),
- Es gibt mehr Transparenz und Verantwortlichkeit der Teams,
- Probleme können durch die regelmäßigen Wiederholungen schneller erkannt und behoben werden.
Dezidierte Rollen
| Product Owner | Master | Entwickler*innen |
|---|---|---|
| Ziele (Anforderungen an das Produkt und spezifische Anforderungen) festlegen → kann als Kunde / Auftraggeber betrachtet werden | Beseitigen von Hindernissen im Arbeitsablauf, Einhaltung der Rahmenstruktur, Moderator, nicht weisungsbefugt | Diejenigen, welche selbstorganisiert das Produkt entwickeln. Die Teams werden, je nach Modell, aus interdisziplinären Experten oder aus Experten aus einem Gebiet besetzt. |
| Nicht im Team → Lehrkraft | Teammitglied | Team aus Schüler*innen |
Die Arbeit wird von den Schüler*innenteams selbst organisiert, nachdem die Person „Product Owner“, hier die Lehrkraft, die konkreten Anforderungen an das Produkt festgelegt hat. Anschließend läuft die Arbeit in mehreren festgelegten Schritten ab:
| Aktion | Aufgaben |
|---|---|
| Planung des Schritts | Überblick verschaffen, was bereits erledigt ist |
| Planung der Ziele des aktuellen Schritts | |
| Aufteilen von Aufgaben | |
| Sicherstellen, dass alle Teammitglieder wissen, was zu tun ist | |
| Täglicher Schritt | Fertigstellung der aktuellen Ziele |
| Reflexion des Schritts | Mit allen zusammen, auch der Lehrkraft |
| Wurde das aktuelle Ziel erreicht? | |
| Was sollten wir nicht mehr tun? | |
| Was sollten wir beibehalten? | |
| Ergebnisse der Reflexion dokumentieren |
Hilfestellungen zum Entwurf von Datenbanken
Diese Hilfestellungen sind nach einer Idee von Marlies Händchen (Oldenburg, 2023) erstellt worden. Die folgenden vier Abschnitte helfen Ihnen, eine Datenbank zu einem gegebenen Szenario effizient zu entwerfen. Teilen Sie die Abschnitte z. B. in Ihrer Gruppe auf, lesen Sie sie durch und stellen Sie sie sich gegenseitig vor.
ER-Modelle in Tabellen umsetzen – Rekursive Beziehungen
Es kommt häufig vor, dass eine Entität zu sich selbst in Beziehung steht.
1:n
| ID_Mitarbeiter | Vorname | Nachname | ID_Mitarbeiter_Vorgesetzter |
|---|---|---|---|
| 1 | Willi | Klein | 2 |
| 2 | Eva | Neumann |
Das Feld bleibt leer, wenn der Mitarbeiter keine Vorgesetzten mehr hat.
1:1
| ID_Kanzler | Vorname | Nachname | ID_Kanzler_Nachfolger |
|---|---|---|---|
| 1 | Konrad | Adenauer | 2 |
| 2 | Ludwig | Erhard | 3 |
| … | … | … | … |
| 7 | Gerhard | Schröder | 8 |
| 8 | Angela | Merkel | 9 |
| 9 | Olaf | Scholz | 10 |
| 10 | Friedrich | Merz |
Das Feld bleibt leer.
m:n → eigene Tabelle
| ID_Substanz | Bezeichnung |
|---|---|
| 1 | Wasser |
| 2 | Wasserstoff |
| 3 | Sauerstoff |
| ID_Substanz_Ganzes | ID_Substanz_Teil | Anteile |
|---|---|---|
| 1 | 2 | 2 |
| 1 | 3 | 1 |
Regel: Bei rekursiven Beziehungen, die auf einer Entität definiert sind, können die bereits bekannten Überführungsregeln für 1:1-, 1:n- und n:m-Beziehungen analog angewendet werden. Dabei ist jedoch darauf zu achten, dass keine Spalten mit gleichem Namen entstehen. Die Fremdschlüssel müssen durch sinnvolle Zusätze umbenannt werden.
ER-Modelle in Tabellen umsetzen – Mehrfachbeziehungen
Es kann sinnvoll sein, eine Beziehung zwischen mehr als zwei Entitäten zu bilden.
Beispiel A – m:n:k
Beispiel B – 1:m:n
Beispiel C – 1:1:n
Rahmenbedingung:
- Unter Produkt sollen hier individuelle, unterscheidbare Einzelexemplare, z. B. mit Seriennummern oder kostbare Antiquitäten gemeint sein.
- Ein Kunde kann somit ein bestimmtes Produkt nicht in unterschiedlichen Fachgeschäften kaufen.
Beispiel D – 1:1:n
Rahmenbedingung:
- An einem Fall sind immer 2 Mandanten beteiligt (Kläger und Beklagter).
- Ein Anwalt kann in einem Fall nur einen Mandanten vertreten (entweder den Kläger oder den Beklagten, aber nicht beide).
- Ein Mandant hat immer nur einen Anwalt je Fall.
Aufgaben: Mehrfachbeziehungen umsetzen
Finden Sie für die oben abgebildeten Beispiele sinnvolle Umsetzungen in Tabellenschemata. Testen Sie jeweils mittels Beispieldatensätzen, ob redundante oder nicht eindeutig identifizierbare Zeilen entstehen.
Leiten Sie aus Ihren Erkenntnissen Regeln für die Umsetzung von Mehrfachbeziehungen ab.
Regel zu Mehrfachbeziehungen anzeigen
Mehrfachbeziehungen werden in der Regel durch eine eigenständige Tabelle realisiert. In die Beziehungstabelle müssen die Schlüssel der zugehörigen Entitäten als Fremdschlüssel aufgenommen werden. Der Schlüssel der Beziehungstabelle wird aus einer Kombination der Fremdschlüssel gebildet. Fremdschlüssel, die aus Entitäten mit der Anzahlangabe „1“ stammen, sind nicht zwingend für die Schlüsselbildung notwendig. Alle weiteren Merkmale der Beziehung werden ebenfalls in die Beziehungstabelle übernommen.
Modellierungsentscheidungen bei ER-Modellen
Die größte Schwierigkeit beim Entwerfen von ER-Modellen liegt oft darin, bei Grenzfällen zu entscheiden, ob eine Information als Attribut, als Entität oder als Beziehung modelliert werden sollte. Oft lassen sich diese Entscheidungen nur individuell für den jeweiligen Sachzusammenhang treffen. Es ist allerdings möglich, gewisse Entscheidungshilfen zu abstrahieren.
| Entscheidungsfeld 1: Attribut oder Entität? | |
|---|---|
| Attribut | Entität |
| kann als Eigenschaft einer Entität / Beziehung betrachtet werden | real in der Lebenswelt existierende Gegenstände Information, der mehrere Eigenschaften zugeordnet werden können eigenständige, datenbankrelevante Objekte (auch ohne Bezug zu anderen Entitäten von Interesse) ein Attribut müsste ansonsten mit zwei Werten belegt werden |
| Entscheidungsfeld 2: Beziehung oder Entität? | |
|---|---|
| Beziehung mit Attributen | Entität |
| Information ist nur in Abhängigkeit von den beiden Entitäten interessant Attribute sind in Abhängigkeit von beiden Entitäten gleichzeitig von Interesse | Information ist unabhängig von den äußeren Entitäten von Interesse trotz zusätzlicher Entität treten keine Redundanzen auf wenn ein Attribut der Beziehung Schlüssel sein müsste, bei der Umsetzung in Tabellen treten ansonsten nicht eindeutig identifizierbare Zeilen auf |
Kriterien zur Bewertung von ER-Modellen
Die folgenden Kriterien können Sie in Ihrem entworfenen ER-Modell zur Überprüfung nutzen.
- Entitäten passend zum Sachzusammenhang gewählt
- Untergliederung in Entitäten nicht zu grob und nicht zu fein
- Attribute der Entitäten vorhanden, sinnvoll und vollständig
- Sinnvolle Schlüsselattribute für alle Entitäten
- Beziehungen zwischen Entitäten vorhanden, sinnvoll und klar benannt
- Kardinalitäten der Beziehungen vorhanden und sinnvoll
Übungsaufgaben zum Datenbankentwurf
Die Aufgaben 1 und 2 werden mit agilen Methoden in Gruppen aus drei bis vier Personen bearbeitet. Die Gruppenzusammenstellung wechselt nach jeder Aufgabe.
Die Lehrkraft (Product Owner) wird ab und zu die Anforderungen während Ihrer Arbeitsphasen erweitern oder ändern!
Ein Restaurant möchte die Verwaltung seiner Daten digital mit einer Datenbank durchführen. Es gibt verschiedene Kategorien von Produkten (Vorspeisen, Hauptgerichte, …). Jedes Produkt auf der Speise- und auf der Getränkekarte hat eine Nummer, einen Preis und einen Namen. Weiterhin gibt es für jedes Produkt eine eindeutige Beschreibung, in welcher die einzelnen Komponenten des Produktes aufgeführt sind. Die Komponenten und die Getränke haben verschiedene Kategorien (Fleisch, Fisch, Gemüse, Gewürze, …), Einkaufspreise und es gibt verschiedene Lieferfirmen. Bestellungen werden tischweise aufgegeben. Ein Tisch hat eine Nummer und eine Personenanzahl. Bei einer Bestellung wird die Anzahl und Nummer der bestellten Produkte angegeben. Das Bedienungspersonal hat eine Personalnummer und einen Vor- / Nachnamen. Jeder Tisch wird von genau einer Person bedient. Die Mitarbeiter in der Küche sollen ebenfalls erfasst werden.
Erstellen Sie ein geeignetes ER-Modell zum obigen Szenario.
Erweitern Sie Ihr Modell um weitere sinnvolle Informationen, welche in der Beschreibung nicht vorhanden sind.
Diese Aufgabe ist nach einer Idee von Marlies Händchen (Oldenburg, 2023) entworfen worden. Gegeben sind drei User-Stories (aus wenigen, alltagssprachlichen Sätzen bestehende Anforderungsbeschreibung aus der Sicht des Kunden) zu verschiedenen Datenbankanforderungen.
A: Belegsystem einer Hochschule
Eine Hochschule bietet Veranstaltungen an, die an einem bestimmten Wochentag und einem bestimmten Block in einem bestimmten Raum stattfinden. Jede dieser Veranstaltungen wird von genau einem Dozenten angeboten, von dem Name und Vorname bekannt sind.
Ein Modul beschreibt die Veranstaltungen, die angeboten werden. Für jedes Modul gibt es eine eindeutige Modulnummer, eine Bezeichnung und die Zahl der „credit points“, die nach erfolgreichem Absolvieren den Studierenden zugerechnet werden.
Studierende besuchen Veranstaltungen. Studierende haben einen Namen und Vornamen sowie eine eindeutige Matrikelnummer.
B: Fitnessstudio
Ein Fitnessstudio möchte die folgenden Informationen verwalten: Von den Kunden des Studios werden neben Namen und Adresse auch Informationen wie Gewicht, Körpergröße, Geschlecht und ggf. körperliche Beschwerden (z. B. Knieprobleme) gespeichert.
Kunden können Kurse besuchen, von denen Wochentag und Start- und Endzeit, an denen sie stattfinden, sowie der Raum, die Kursbezeichnung und die maximale Teilnehmerzahl gespeichert werden sollen. Es wird zudem gespeichert, ab wann der Kunde den Kurs belegt.
Kurse werden von Trainern durchgeführt, von denen Name, Vorname und Telefonnummer gespeichert werden muss.
C: KFZ-Zulassung
Eine KFZ-Zulassungsstelle in einem Landkreis möchte Informationen über die bei ihr zugelassenen KFZ und ihre Eigentümer speichern. Dabei interessieren folgende Informationen: Von den Eigentümern der KFZ werden Name, Vorname, Geburtsdatum und die Postanschrift mit Straße, Ort und PLZ benötigt. Von den zugelassenen Fahrzeugen werden die Fahrgestellnummer und das Baujahr verzeichnet. Außerdem werden die Fahrzeuge in Typklassen eingeordnet. Jeder Fahrzeugtyp hat einen Typcode, die Angabe des Herstellers, des Modells und der Schadstoffklasse.
Bei der Zulassung eines Fahrzeugs wird das Kennzeichen vergeben. Die Zulassung findet zu einem bestimmten Datum statt. Ein Fahrzeug kann auch abgemeldet werden, dann wird das Abmeldedatum verzeichnet. Ein Fahrzeug kann erst einen Tag nach der Zulassung wieder abgemeldet werden.
Teilen Sie die drei Szenarien untereinander auf.
Entwickeln Sie zu dem Ihnen zugewiesenen Beispiel ein ER-Modell.
Stellen Sie sich gegenseitig Ihre Ergebnisse vor. Die anderen Teilgruppen nehmen hier eine beurteilende Rolle ein und sollen mögliche Fehler und Verbesserungen nennen.
Integrieren Sie mögliche Verbesserungen, die von den anderen Teilgruppen genannt wurden. Bereiten Sie sich darauf vor, Ihr ER-Modell zu präsentieren. Nehmen Sie hierbei die Rolle der Datenbankentwickler*innen ein, welche die Kunden (die anderen Kursteilnehmenden) überzeugen möchten, ihr Produkt zu kaufen. Begründen Sie hierzu bei Ihrer Präsentation explizit, warum Ihre Modellierung alle gewünschten Anforderungen erfüllt und gegebenenfalls auch zusätzliche Aspekte berücksichtigt.
Entwickeln Sie auch zu den anderen beiden User-Stories ER-Modelle.
Erläutern Sie das abgebildete ER-Modell.
Das Datum der Vermietung und der Vor- und Nachname des Vermieters sollen gespeichert werden. Erweitern Sie das Modell entsprechend.
Ordnen Sie den Beziehungen in den folgenden ER-Modellen passende Kardinalitäten zu.
Attribute und Schlüssel sind hier bewusst weggelassen, weil es nur um die Kardinalitäten geht. In vollständigen ER-Modellen gehören die Schlüsselattribute immer dazu.
🎉 Ausgezeichnet – Alle Aufgaben abgeschlossen!
Hier finden Sie Lösungshinweise zu den Übungsaufgaben.
Aufgabe 1 (Restaurant)
Generell sind hier verschiedene Lösungsmöglichkeiten vorstellbar. Mögliche Erweiterungen (Änderungen des Product Owners) für einzelne Gruppen wären unter anderem:
- Die Bezahlungen, welche bar, mit Kreditkarte, EC-Karte oder PayPal getätigt werden können, sollen ebenfalls integriert werden.
- Das gesamte Modell soll auch für mehrere Restaurants der Besitzer anwendbar sein.
- Die Speisen und Getränke sollen auch die Informationen vegetarisch, vegan, glutenfrei oder bio beinhalten.
Aufgabe 2 (User-Stories)
Die einzelnen Anforderungsbeschreibungen können nach Interesse gewählt werden. Es wäre gut, wenn jedes Szenario mindestens einmal auftaucht. Generell sind auch hier verschiedene Lösungsmöglichkeiten vorstellbar.
Aufgabe 3
a) Es gibt drei Entitäten: Person, Wohnung und Haus.
- Die Entität Person hat die Attribute Vorname und Nachname, welche den Primärschlüssel bilden.
- Die Entität Wohnung hat die Attribute Nr, Fläche und Etage, wobei Nr den Primärschlüssel bildet.
- Die Entität Haus hat das Attribut Adresse, welches den Primärschlüssel bildet.
Weiterhin gibt es die drei Beziehungen „wohnt in“, „wird gemietet von“ und „in“.
- Die Beziehung „wohnt in“ ist zwischen Person und Wohnung. Es ist eine n:m-Beziehung. Also kann nach dem vorliegenden Modell eine Person in mehreren Wohnungen wohnen und in einer Wohnung können mehrere Personen wohnen.
- Die Beziehung „wird gemietet von“ ist zwischen Person und Wohnung. Es ist eine n:m-Beziehung. Also kann nach dem vorliegenden Modell eine Person mehrere Wohnungen mieten und eine Wohnung kann von mehreren Personen gemietet werden.
- Diese beiden Beziehungen sollen es ermöglichen, zwischen „Bewohnern“ und „Mietern“ zu unterscheiden.
- Die Beziehung „in“ ist zwischen den Entitäten Haus und Wohnung und es ist eine 1:n-Beziehung. Somit kann ein Haus mehrere Wohnungen haben, aber eine Wohnung kann nur in genau einem Haus sein.
b) Das Datum sollte als Attribut an die Beziehung „wird gemietet von“ gesetzt werden. Für den Vor- und Nachnamen des Vermieters bietet sich eine eigene Entität (z. B. Vermieter) an, die mit der Wohnung bzw. der Vermietung in Beziehung steht.
Aufgabe 4: Mögliche Lösungen (je nach Interpretation)
| Beziehung | Kardinalität |
|---|---|
| Mutter – hat – Kind | biologische Mutter → 1:n; Elternteil Mutter → n:m |
| Lieferant – liefert – Artikel | n:m oder 1:n |
| Schüler – ist Kurssprecher – Kurs | 1:n |
| Schüler – nimmt teil – Kurs | n:m |
| Tanzkursteilnehmer – tanzt mit | n:m |
| Person – hat – Ausweis | 1:1 |
| Lehrer – unterrichtet – Klasse – Fach | 1 (Lehrer) : n (Fach) : m (Klassen) |
Umsetzung des ER-Modells ins relationale Schema
Hat man erst einmal ein ER-Modell für eine Datenbank erstellt, muss es noch in konkrete Tabellen umgesetzt werden. Hierfür gibt es Regeln, sodass Redundanzen in den entstandenen Tabellen minimiert werden. Wir setzen beispielhaft das Modell der Onlinebücherei um.
Aufgaben: Vom ER-Modell zum Schema
Setzen Sie das ER-Modell in das entsprechende relationale Datenbankschema um.
🎉 Ausgezeichnet – Alle Aufgaben abgeschlossen!
Hier finden Sie das relationale Schema zur Onlinebücherei.
Buch(ISBN, Kategorie, Titel, Autor, Preis, ↑VID, ↑DID)
Kunde(Nutzername, Vorname, Nachname, Adresse, Email, Passwort)
Darstellung(DID, Durchschnittsbewertung, lieferbar)
kauft(↑Nutzername, ↑ISBN, Anzahl)
Schritt 1 – Entitäten
Für jede Entitätsmenge wird eine Tabelle im relationalen Schema erstellt und mit den zugehörigen Attributen aufgefüllt.
Buch(ISBN, Kategorie, Titel, Autor, Preis)
Kunde(Nutzername, Vorname, Nachname, Adresse, Email, Passwort)
Darstellung(DID, Durchschnittsbewertung, lieferbar)
Schritt 2 – Beziehungen
Die Umsetzung der Beziehungen stellt sicher, dass die einzelnen Tabellen in der Datenbank vernünftig miteinander verbunden sind. Für jeden Beziehungstyp gibt es eine Regel, wie selbige in einem relationalen Schema umzusetzen ist.
Betrachten wir die 1:1-Beziehung besitzt. Jedes Buch hat genau eine Darstellung und jede Darstellung gehört zu genau einem Buch. Damit die beiden Tabellen Buch und Darstellung verbunden werden, kann man den Primärschlüssel der einen gegenseitig als Fremdschlüssel in die jeweils andere Tabelle einfügen. Also hätte man dann in der Tabelle Buch als zusätzliches Attribut DID und in Darstellung das Attribut ISBN. Man kann so zu jedem Buch die entsprechende Darstellung in der zugehörigen Tabelle finden und auch zu jeder Darstellung das passende Buch. Jedoch genügt es, nur einen der beiden Schlüssel in die andere Tabelle aufzunehmen. Erweitert man zum Beispiel Buch um DID, kann man die Darstellung zu einem Buch finden, indem die DID in Buch gesucht und dann diese in Darstellung gesucht wird. Andersherum funktioniert es genauso. Wir entscheiden uns für die erste Variante, also DID in Buch.
Nun zur 1:n-Beziehung vertreibt. Jedes Buch wird von genau einem Verlag vertrieben, aber jeder Verlag vertreibt n Bücher. Man könnte ISBN als neues Attribut in Verlag aufnehmen. Dies hat jedoch ungewünschte Konsequenzen. Bisher gibt es für jeden Verlag genau einen Datensatz in Verlag. Wenn ISBN in Verlag aufgenommen wird, sodass eine Verbindung von Verlag zu Buch entsteht, muss für jedes vertriebene Buch ein Datensatz in Verlag entstehen. Vertreibt ein Verlag zum Beispiel 300 Bücher, entstehen in Verlag insgesamt 300 Datensätze, einer für jedes Buch. Dies führt zu unnötigen Redundanzen, da alle Informationen zu den Büchern bereits in Buch vorhanden sind. Die zweite Variante, den Schlüssel VID in Buch aufzunehmen, ist wesentlich besser. Kein neuer Datensatz entsteht. Es wird nur zu jedem Buch ein neues Attribut angefügt und die Verbindung von Buch zu Verlag ist fertig.
Zum Schluss noch zur n:m-Beziehung kauft. Jeder Kunde kann mehrere Bücher kaufen und jedes Buch kann von mehreren Kunden gekauft werden. Das hat zur Folge, dass ein Einfügen des jeweils anderen Schlüssels in die Tabellen für jedes gekaufte Buch einen eigenen Datensatz des Kunden in Kunde und für jeden Kunden, der ein Buch kauft, einen eigenen Datensatz in Buch entstehen lässt. Es ist sofort deutlich, dass dadurch sehr viele Redundanzen erzeugt werden würden. Also ist hier das Einfügen von Fremdschlüsseln wenig sinnvoll. Anstelle dessen erzeugt man eine eigene Tabelle kauft, in welcher die beiden Schlüssel aus Buch und Kunde als Fremdschlüssel eingefügt werden. Diese beiden Schlüssel zusammen bilden den Primärschlüssel von kauft. Möchte man nun wissen, welcher Kunde ein bestimmtes Buch gekauft hat, sucht man die ISBN in Buch und dann die zugehörigen Nutzernamen in kauft heraus. Damit können dann in Kunde die gesuchten Kunden gefunden werden. Genauso kann man alle Bücher, die ein Kunde bestellt hat, über seinen Nutzernamen aus Kunde, dann mit den entsprechenden ISBN aus kauft und die passenden Bücher in Buch finden.
Insgesamt erhalten wir das folgende relationale Schema:
Buch(ISBN, Kategorie, Titel, Autor, Preis, ↑VID, ↑DID)
Kunde(Nutzername, Vorname, Nachname, Adresse, Email, Passwort)
Darstellung(DID, Durchschnittsbewertung, lieferbar)
kauft(↑Nutzername, ↑ISBN, Anzahl)
Zusammenfassung der Regeln:
- Für jede Entität wird eine Tabelle erstellt. Die Attribute der Entitäten kommen in die Tabelle.
- 1:1-Beziehung: Ein beliebiger Primärschlüssel aus einer der Tabellen wird in der anderen als Attribut hinzugefügt. Sollte die Beziehung selbst Attribute besitzen, werden diese ebenfalls in diese Tabelle aufgenommen.
- 1:n-Beziehung: Der Primärschlüssel der Entität, an der die 1 steht, wird in die Tabelle, an der das n steht, als Attribut hinzugefügt. Sollte die Beziehung selbst Attribute besitzen, werden diese ebenfalls in die Tabelle mit dem n aufgenommen.
- n:m-Beziehung: Eine neue Tabelle wird erstellt. Die Primärschlüssel der beiden Entitäten, welche durch die Beziehung verbunden sind, werden als Primärschlüssel der neuen Tabelle als Attribute aufgenommen. Sollte die Beziehung selbst Attribute besitzen, werden diese auch in die neue Tabelle aufgenommen.
- Schreibweise (wie in den Abituraufgaben): Primärschlüssel werden unterstrichen, Fremdschlüssel bekommen einen vorangestellten Pfeil, z. B. Buch(ISBN, …, ↑VID, ↑DID). Hätte ein Fremdschlüssel denselben Namen wie ein anderes Attribut der Tabelle, wird er umbenannt, z. B. Model(MID, ↑AgenturName, Name, …).
Integritätsbedingungen
Nachdem man das relationale Schema einer Datenbank erstellt hat, können die dazugehörigen Tabellen in einem Datenbankmanagementsystem (DBMS) erzeugt werden.
Beim Verändern von Daten in den Tabellen mit Hilfe verschiedener Operationen muss das DBMS jederzeit sicherstellen, dass die Integrität der Datenbank erhalten bleibt. Wenn immer wieder Daten eingefügt, gelöscht oder geändert werden, muss das DBMS immer wieder einen konsistenten Zustand der Datenbank in einen neuen konsistenten überführen. Wir betrachten hier nur drei Ebenen (in der Realität gibt es mehr).
| SNr | Kennzeichen | Bestanden | Fahrstunden |
|---|---|---|---|
| 001 | OS-ME-2000 | 0 | achtzehn |
| 2 | OS-HK-1234 | 1 | 12 |
| 003 | OSFG4567 | Nein | 9 |
Betrachten Sie die Tabelle. Weshalb sind einige Attributwerte rot markiert? Welche Probleme könnte diese Datenbank z. B. bei einer Kennzeichensuche oder beim Berechnen der Gesamtfahrstunden einer Fahrschülergruppe verursachen?
1. Datenfeldebene
Jedem Attribut muss ein bestimmter Datentyp und gegebenenfalls sogar ein bestimmter Wertebereich zugewiesen sein. Bei neu eingegebenen oder geänderten Daten müssen diese Bedingungen immer eingehalten werden. Die Tabelle „Fahrschule“ oben zeigt Beispiele für Verletzungen der Integrität auf der Datenfeldebene.
2. Datensatzebene (Entitätsintegrität)
Jeder Datensatz einer Tabelle muss mit Hilfe des Primärschlüssels eindeutig identifiziert werden. Daher darf derselbe Primärschlüsselwert nicht zweimal vorkommen, und die Primärschlüsselattribute dürfen nicht leer bleiben, also keinen Null-Wert haben.
3. Beziehungsebene (Referenzielle Integritätsbedingung)
Beziehungen werden im relationalen Schema durch Fremdschlüssel umgesetzt. Wenn nun ein Datensatz gelöscht wird, auf dessen Primärschlüssel in anderen Tabellen ein Fremdschlüssel verweist, muss das DBMS sicherstellen, dass in diesen Tabellen die referenzielle Integrität erhalten bleibt und konsistent weitergearbeitet werden kann.
Ein Beispiel:
Aufgrund der n:m-Beziehung wird es im relationalen Schema drei Tabellen geben.
| LID | Name | Vorname |
|---|---|---|
| hei | Heine | Heinrich |
| man | Mann | Thomas |
| bre | Brecht | Bertolt |
| FNr | FName |
|---|---|
| 1 | Mathe |
| 2 | Deutsch |
| 3 | Englisch |
| ↑LID | ↑FNr |
|---|---|
| bre | 1 |
| bre | 2 |
| hei | 3 |
Aufgaben: Der Lehrer Heine verlässt die Schule
Setzen Sie dies in der Datenbank um. Welche Schritte sind nötig?
Die referenzielle Integrität soll hier sicherstellen, dass in der Tabelle unterrichtet die Fremdschlüssel nur Werte erhalten, die es in den anderen beiden Tabellen gibt. Es darf nicht möglich sein, in unterrichtet Werte für LID und FNr einzutragen, die nicht existent sind.
Wenn zum Beispiel der Lehrer Heine die Schule verlässt und in Lehrer gelöscht wird, müssen auch die entsprechenden Datensätze in unterrichtet entfernt werden. Ist für den Fremdschlüssel die Löschweitergabe festgelegt (in SQL: ON DELETE CASCADE), übernimmt das DBMS diesen Schritt automatisch.
Noch ein Beispiel für eine 1:n-Beziehung:
| MID | Name | Vorname | ↑AID |
|---|---|---|---|
| m1 | Anfang | Anna | 1 |
| m2 | Buch | Bea | 1 |
| m3 | Clemens | Clara | 3 |
| AID | Name |
|---|---|
| 1 | Fertigung |
| 2 | Vertrieb |
| 3 | Lager |
Aufgaben: Die Abteilung Lager wird gelöscht
Es soll die Abteilung Lager gelöscht werden, weil die Firma ein externes Unternehmen mit der Lagerung der Produkte beauftragt hat. Erläutern Sie, welche Schritte geschehen müssen und was das DBMS tut.
Mit Löschweitergabe würde das DBMS anschließend auch die Mitarbeiterin Clara löschen, da ihr Fremdschlüssel 3 sonst ins Leere zeigt. Damit verschwände aber eine Person aus der Datenbank, die weiterhin in der Firma arbeitet (Löschanomalie). Für diesen Fremdschlüssel ist deshalb eine andere Regel sinnvoll: Mit ON DELETE RESTRICT lehnt das DBMS das Löschen der Abteilung ab, solange noch Mitarbeiter auf sie verweisen. Erst wenn alle Mitarbeiter anderen Abteilungen zugeordnet sind, lässt sich die Abteilung löschen. Alternativ setzt ON DELETE SET NULL die Abteilung der betroffenen Mitarbeiter auf NULL. Welche Regel gilt, wird beim Anlegen der Tabelle am Fremdschlüssel festgelegt.
🎉 Ausgezeichnet – Alle Aufgaben abgeschlossen!
Hier finden Sie die erwarteten Ergebnisse der Austauschphasen.
Einstieg Fahrschule
Jedem Attribut muss ein bestimmter Datentyp und gegebenenfalls sogar ein bestimmter Wertebereich zugewiesen sein: „achtzehn“ ist keine Zahl, „2“ hat ein anderes Format als „001“, „OSFG4567“ hat kein einheitliches Kennzeichenformat und „Nein“ passt nicht zu den Werten 0/1. Eine Kennzeichensuche oder das Summieren der Fahrstunden würde so fehlschlagen.
Lehrer Heine
- Löschen des Datensatzes zum Primärschlüssel hei aus der Tabelle „Lehrer“ (manuell).
- Löschen der Datensätze zum Fremdschlüssel hei aus der Tabelle „unterrichtet“ – diesen Schritt übernimmt das DBMS, wenn für den Fremdschlüssel die Löschweitergabe (ON DELETE CASCADE) festgelegt ist. So bleibt die referenzielle Integrität gewahrt.
Fazit: Alle Tabellen mit Fremdschlüsseln dürfen nur Fremdschlüssel enthalten, die es auch in den „Haupttabellen“ zu diesen Schlüsseln gibt. Ist die Löschweitergabe festgelegt, entfernt das DBMS die abhängigen Datensätze selbst.
Abteilung Lager
- Löschen des Datensatzes zum Primärschlüssel 3 aus der Tabelle „Abteilung“.
- Löschen der Datensätze zum Fremdschlüssel 3 aus der Tabelle „Mitarbeiter“ → DBMS (Löschweitergabe).
Dabei würde ungewollt die Mitarbeiterin Clara Clemens aus der Datenbank verschwinden – eine Inkonsistenz aufgrund einer Löschanomalie. Für diesen Fremdschlüssel passt deshalb keine Löschweitergabe, sondern die Regel RESTRICT: Das DBMS lehnt das Löschen der Abteilung ab, solange noch Mitarbeiter auf sie verweisen. Erst wenn alle Personen der Abteilung 3 anderen Abteilungen zugeordnet sind, kann die Abteilung gelöscht werden. Alternativ setzt die Regel SET NULL die Abteilung der betroffenen Mitarbeiter auf NULL.
Weitere Aufgaben zum Datenbankentwurf
Ergänzen Sie in den folgenden ER-Modellen die Entitäten und Beziehungen mit geeigneten Attributen und Primärschlüsseln. Überführen Sie anschließend die Modelle in relationale Schemata.
Überführen Sie das unten abgebildete ER-Modell ins relationale Schema.
Fotografen sollen für Produkte verschiedener Firmen Werbebilder anfertigen. Die Agentur möchte die notwendigen Daten in einer Datenbank verwalten. Es wurden zunächst Tabellen angefertigt, ohne die Beziehungen unter diesen zu realisieren. Das Modell soll verbessert werden.
| PID | Name | Kategorie | Preis |
|---|---|---|---|
| 654 | Pure Hair | Kosmetik | 3,99 |
| 987 | High Energy | Getränke | 0,65 |
| 321 | Soytella | Brotaufstrich | 2,43 |
| Name | Ansprechpartner |
|---|---|
| Drink AG | Herr Lorenz |
| Hairstylez | Frau Korb |
| FID | Name | Vorname | Kategorie | Gehalt |
|---|---|---|---|---|
| 1234 | White | Jane | Brotaufstrich | 4000 |
| 4321 | Black | Joe | Getränke | 4200 |
Jeder Fotograf ist für das Shooting in einer bestimmten Kategorie zuständig. Eine Firma, welche für eines ihrer Produkte Aufnahmen möchte, kann einen Fotografen zu einem bestimmten Gehalt buchen.
Modellieren Sie die zu erstellende Datenbank in einem ER-Modell.
Überführen Sie Ihr ER-Modell in ein relationales Schema.
Erläutern Sie Integritätsbedingungen für Datenbanken anhand eines konkreten Beispiels für Inkonsistenzen durch Verletzung der referenziellen Integrität. Gehen Sie auch darauf ein, für welche Beziehung Sie eine Kontrolle der referenziellen Integrität mit bzw. ohne Löschweitergabe für sinnvoll erachtet.
Der Onlinehandel für Bekleidung Zolonda hat einen ersten groben Entwurf für eine Datenbank erstellt.
Untersuchen Sie, inwiefern dieser Entwurf brauchbar ist, anhand der folgenden Kriterien und verändern Sie das Modell schrittweise.
- Die Entitäten sind sinnvoll gewählt.
- Die Attribute sind sinnvoll gewählt.
- Es gibt geeignete Beziehungen.
- Es gibt geeignete Kardinalitäten.
Erstellen Sie aus Ihrem ER-Modell ein relationales Schema und Tabellen mit einigen Beispieldatensätzen.
Diese Aufgabe ist nach einer Idee von Marlies Händchen (Oldenburg, 2023) entworfen worden.
Einige Schüler*innen des 12. Jahrgangs sind auf die Idee gekommen, dass man die in der Schule ohnehin gespeicherten Schülerdaten verwenden könnte, um ein Dating-Portal einzurichten und die Daten zu nutzen, um für jede*n Schüler*in den*die passende*n Partner*in unter den in der Datenbank vorhandenen Schüler*innen zu finden. Als Informatik-Kurs sind Sie natürlich prädestiniert, ein solches Vorhaben umzusetzen.
Die Datenbank des GSG enthält zusätzlich zu den schulisch relevanten Daten bereits einige weitere Informationen über die eingetragenen Schülerinnen und Schüler.
Entscheiden Sie, welche Informationen für das Betreiben einer Dating-Seite relevant sind.
Entwerfen Sie ein geeignetes ER-Modell für die Datenbank der Datingseite.
🎉 Ausgezeichnet – Alle Aufgaben abgeschlossen!
Hier finden Sie Lösungsvorschläge zu den weiteren Aufgaben.
Aufgabe 1: Mögliche Lösung
Trainingsgerät(GNr, Bezeichnung, Typ, Platz, Zubehör)
nutzt(↑MID, ↑GNr)
Klasse(Klasse, Jahrgang, AnzahlS, Klassenraum)
unterrichtet(↑Kuerzel, ↑Klasse, Wochenstunden)
Bereich(BID, ↑AID, Name, Umsatz)
Aufgabe 2: Mögliche Lösung
Model(MID, ↑AgenturName, Name, Vorname, Geschlecht, Größe)
Shooting(Ort, Termin, ↑ProduktName, ↑FID)
arbeitet bei(↑MID, ↑Ort, ↑Termin, Honorar)
Produkt(Name, Firma)
Fotograf(FID)
Aufgabe 3
a) Mögliche Lösung:
b)
Produkt(PID, Name, Preis, ↑KName, ↑FirmaName)
Kategorie(KName)
Fotograf(FID, Name, Vorname, ↑KName)
Shooting(↑FID, ↑PID, ↑FirmaName, Gehalt)
c) Eine Firma wird insolvent und soll aus dem System entfernt werden. Mit Löschweitergabe (CASCADE) würden auch alle Datensätze in Shooting gelöscht, die auf diese Firma verweisen. Damit gingen alle Informationen zu den stattgefundenen Shootings verloren, was die Fotografen sicher nicht gut fänden. Sinnvoller ist hier die Regel RESTRICT: Das DBMS lehnt das Löschen der Firma ab, solange noch Shootings oder Produkte auf sie verweisen. Diese Datensätze müssen vorher einer anderen Firma zugeordnet oder bewusst gelöscht werden.
Wenn eine Kategorie entfernt werden soll, ist die Löschweitergabe bzgl. der Beziehung „arbeitet für“ in Ordnung, da dies kein Schlüsselattribut berührt und diejenigen Fotografen sich eine andere Kategorie suchen müssen oder auch aus der Verwaltung entfernt werden würden.
Aufgabe 4
Hier gibt es verschiedenste Lösungsmöglichkeiten, die sinnvoll sind. Ein Beispiel:
a) Die Attribute Adresse und Name bei Kunden sind nicht atomar. Jetzt werden erst die Entitäten genauer analysiert. Die Entität Warenkorb ist so nicht sinnvoll, da recht viele Redundanzen auftauchen werden. Deshalb wurde eine zweite Entität Bestellung eingeführt. Der Warenkorb besteht dann aus allen Einzelbestellungen jedes Produkts, welches der Kunde in den Warenkorb legt. Die Fremdattribute wurden ebenfalls entfernt, da diese erst bei der Umsetzung ins relationale Schema entsprechend angepasst werden und von den Beziehungen abhängen.
Im nächsten Schritt werden geeignete Beziehungen ergänzt.
- Ein Kunde gibt für ein Produkt eine Bestellung mit der jeweiligen Anzahl auf. Ein Kunde kann n Bestellungen aufgeben, aber jede Bestellung gehört zu genau einer Person.
- Ein Artikel kann in mehreren Bestellungen enthalten sein, aber jede Bestellung darf nur einen Artikel beinhalten.
- Ein Warenkorb besteht aus mehreren Bestellungen, aber jede Bestellung ist in genau einem Warenkorb.
b)
Artikel(ArtNr, Name, Preis)
Bestellungen(BestellNr, Anzahl, ↑WKID, ↑ArtNr, ↑Account)
Warenkorb(WKID, Datum)
| Account | Vorname | Nachname | Straße | PLZ | Ort |
|---|---|---|---|---|---|
| oolsen | Olga | Olsen | Am See 1 | 12345 | Test |
| aarm | Arne | Arm | Am Wald 2 | 56789 | Kaufstadt |
| llustig | Laura | Lustig | Am Berg 3 | 04321 | Neudorf |
| BestellNr | Anzahl | ↑WKID | ↑ArtNr | ↑Account |
|---|---|---|---|---|
| 1 | 3 | WK001589 | 3 | aarm |
| 35 | 1 | WK001589 | 2 | aarm |
| 2 | 1 | WK005321 | 4 | llustig |
| 10 | 1 | WK850000 | 1 | oolsen |
| ArtNr | Name | Preis |
|---|---|---|
| 1 | Gelber Pulli | 39,99 |
| 2 | Rote Hose | 85,49 |
| 3 | Blaue Socken | 9,99 |
| 4 | Grüner Mantel | 189,95 |
| WKID | Datum |
|---|---|
| WK001589 | 22.06.2024 |
| WK005321 | 01.03.2024 |
| WK850000 | 13.10.2024 |
Aufgabe 5
Das ist eine kreative Aufgabe, welche verschiedenste Lösungsmöglichkeiten hat. Im Anschluss bietet es sich an, das Thema Datenschutz anzusprechen und zu den Prinzipien des Datenschutzes überzuleiten.
Prinzipien des Datenschutzes
Diese Seite ist nach einer Idee von Marlies Händchen (Oldenburg, 2023) erstellt worden.
Um welche Daten geht es?
- personenbezogene Daten
- auch Daten, die nur indirekt einer Person zugeordnet werden können, z. B. Fotos von einem Grundstück, das einer Anschrift zugeordnet werden kann
- Daten sind dann nicht personenbezogen, wenn sie anonym sind, d. h. nur noch mit unverhältnismäßig hohem Aufwand einer Person zugeordnet werden können.
Wie wird das Datenschutzgesetz begründet?
„ … Wer nicht mit hinreichender Sicherheit überschauen kann, welche ihn betreffenden Informationen in bestimmten Bereichen seiner sozialen Umwelt bekannt sind, und wer das Wissen möglicher Kommunikationspartner nicht einigermaßen abzuschätzen vermag, kann in seiner Freiheit wesentlich gehemmt werden, aus eigener Selbstbestimmung zu planen oder zu entscheiden. Mit dem Recht auf informationelle Selbstbestimmung wären eine Gesellschaftsordnung und eine diese ermöglichende Rechtsordnung nicht vereinbar, in der Bürger nicht mehr wissen können, wer was wann und bei welcher Gelegenheit über sie weiß. Wer unsicher ist, ob abweichende Verhaltensweisen jederzeit notiert und als Information dauerhaft gespeichert, verwendet oder weitergegeben werden, wird versuchen, nicht durch solche Verhaltensweisen aufzufallen.“
Aufgaben: Datenschutz
Szenario: Die Schulleitung plant nach der erfolgreichen Einführung einer allgemeinen Schul-App nun ein Pilotprojekt für den Sportunterricht. Mit dem System „SmartSports“ sollen Schülerinnen und Schüler im Ausdauerbereich durch Fitness-Armbänder und eine dazugehörige App unterstützt werden.
Die App erfasst während des Unterrichts detaillierte Positionen von Laufrouten per GPS sowie die jeweilige Pulsmessung. Diese Daten werden mit einer anonymen Benutzerkennung an einen Cloud-Server gesendet. Personenbezogene Daten wie der Klarname oder das Geburtsdatum werden in der App nicht direkt mit den Laufdaten verknüpft.
Das Projekt wird von einem lokalen Start-up (Anbieter von Sportartikeln) finanziert. Das Start-up stellt die App und die Armbänder kostenlos zur Verfügung. Im Gegenzug erhält das Unternehmen die von der App gesammelten, anonymisierten Lauf- und Pulsdaten, um darauf basierend zielgerichtete Werbemaßnahmen (z. B. personalisierte Rabattcodes für Laufschuhe auf Basis der gelaufenen Strecken) zu entwickeln.
Zusätzlich hat die örtliche Universität, die im Bereich Sportwissenschaften forscht, bei der Schule angefragt. Sie bittet zunächst um eine tabellarische Übersicht der Sport-Durchschnittsnoten des gesamten Jahrgangs (aufgeteilt nach Geschlecht), um das allgemeine Fitnesslevel zu bewerten. In einer zweiten Anfrage bittet die Universität um die Kontaktdaten der Schülerinnen und Schüler, die im Sportunterricht durchweg 13 Punkte oder besser erreicht haben, um diese zu einer speziellen Leistungsstudie einzuladen.
Erläutern Sie das Konzept des „Rechts auf informationelle Selbstbestimmung“ (gemäß dem Volkszählungsurteil des BVerfG von 1983).
Begründen Sie, inwiefern dieses Recht bei der verpflichtenden Nutzung der „SmartSports“-App im Sportunterricht berührt wird.
Um die App rechtmäßig betreiben zu können, müssen grundlegende Datenschutzprinzipien eingehalten werden. Wenden Sie die DSGVO-Grundsätze der Zweckbindung, der Datenminimierung und der Transparenz auf das beschriebene „SmartSports“-Szenario an. Analysieren Sie dabei mögliche Konflikte oder Risiken, die durch die Weitergabe der Daten an das Start-up entstehen.
Das Start-up argumentiert, dass die Nutzung der Daten für Werbemaßnahmen unbedenklich sei, da nur eine „anonyme Benutzerkennung“ übertragen werde und keine echten Namen weitergegeben werden. Erörtern Sie zwei grundlegende Möglichkeiten des Datenmissbrauchs oder der De-Anonymisierung, die sich aus der Weitergabe der kombinierten GPS- und Pulsdaten an das Start-up ergeben können.
Die Schulleitung muss nun auf die beiden Anfragen der Universität (Tabelle mit Durchschnittsnoten sowie die Kontaktdaten der leistungsstarken Schülerinnen und Schüler) reagieren. Beurteilen Sie eine mögliche Herausgabe der Notentabelle (aufgeschlüsselt nach Geschlecht und Anzahl) durch die Schule im Kontext des Datenschutzes. Gehen Sie dabei auch auf die Gefahr der Identifizierbarkeit von Einzelpersonen (z. B. bei der Angabe „divers: 1“) ein. Beschreiben Sie eine rechtlich zulässige und datenschutzkonforme Reaktion der Schule auf die zweite Anfrage (Herausgabe von Kontaktdaten), sodass die Universität die leistungsstarken Jugendlichen dennoch für ihre Studie gewinnen kann.
Die Schule speichert die Sportnoten des Jahrgangs in der Tabelle Sportnoten(SNr, Geschlecht, Punkte). Für die Universität wird die folgende Abfrage ausgeführt:
SELECT Geschlecht, AVG(Punkte) AS Durchschnitt, COUNT(*) AS Anzahl
FROM Sportnoten
GROUP BY Geschlecht;- Im Jahrgang sind 48 Schülerinnen, 51 Schüler und eine Person mit dem Geschlechtseintrag „divers“. Geben Sie an, wie die Ausgabe aufgebaut ist, und erläutern Sie, welche Information die Zeile „divers“ preisgibt.
- Ergänzen Sie die Abfrage so, dass nur Gruppen mit mindestens fünf Personen ausgegeben werden. Beurteilen Sie, ob damit niemand mehr identifizierbar ist, wenn die Universität zusätzlich den Durchschnitt des gesamten Jahrgangs erhält.
- Die App wertet die Lauf- und Pulsdaten automatisch aus, schlägt jeder Person einen Trainingsplan vor und erstellt für die Lehrkraft eine Rangliste. Diskutieren Sie Chancen und Risiken dieser automatisierten Datenanalyse.
GROUP BY Geschlecht entsteht für jeden Wert eine Zeile – auch für eine Gruppe mit nur einer Person. Was ist der Durchschnitt einer einzigen Note?HAVING COUNT(*) >= 5. Rechnen Sie dann: 100 · Gesamtdurchschnitt − 48 · Durchschnitt der Schülerinnen − 51 · Durchschnitt der Schüler = ?Anforderungsbeschreibung
Als junges Team eines Software-Start-ups entwickeln Sie Softwarelösungen für Unternehmen.
Ein neuer Kunde betreibt mehrere Supermärkte in einer größeren Region. Die Märkte haben je einen Verkaufsraum, der unterschiedlich groß sein kann. Zu jedem Markt gehört eine Person, welche den Markt leitet. Es gibt verschiedene Mitarbeiter eines jeden Marktes. In den Verkaufsräumen gibt es einzelne Bereiche für verschiedene Produktkategorien, wie zum Beispiel Molkereiprodukte, Getränke usw. Die Kassen und die sogenannten Ordner, die sich unter anderem um die Einkaufswagen und das Nachfüllen der Einkaufstüten kümmern, gehören ebenfalls zu den Bereichen. Jeder dieser Bereiche hat eine leitende Person und weitere Personen, die nur in diesem Bereich arbeiten. Für alle Personen soll die Hierarchiekette erkennbar sein, also zum Beispiel, wer einer Person direkt vorgesetzt ist.
Jeder Bereich hat eine bestimmte Menge an Regalen. Unter einem Regal versteht man sowohl ein Hochregal als auch ein schräges Regal, zum Beispiel für Obst und Gemüse. Alle Regale haben eine bestimmte Höhe, Breite und eine Etagenanzahl. Alle Etagen eines Regals haben die gleiche Höhe. Weiterhin muss erkennbar sein, ob es sich um ein normales Regal, ein Kühlregal oder ein Tiefkühlregal handelt.
Die einzelnen Produkte sind in verschiedene Kategorien, wie zum Beispiel Brot/Brötchen, Gemüse, Erfrischungsgetränke usw., eingeteilt und in einer für sie passenden Abteilung ausgestellt. Die Produkte in den Abteilungen sind in bestimmten Regalen angeordnet. Jedes Produkt wird von einem bestimmten Zulieferer für einen bestimmten Einkaufspreis gekauft. Ein Markt kann neue Produkte bei den entsprechenden Firmen bestellen.
Die Kunden der Märkte haben alle eine App, mit der sie sich an den Einkaufswagen anmelden. Es wird gespeichert, welche Produkte ein einzelner Kunde jemals gekauft hat und deren Gesamtanzahl. Die Kunden sind einzelnen Märkten zugeordnet. Die Kunden melden sich bei der App erstmals mit einer Emailadresse an und müssen dort auch eine Art digitaler Zahlungsinformationen hinterlegen.
Das Unternehmen möchte ebenfalls informiert werden, welche datenschutzrechtlichen Belange zu beachten sind.
Für die Bearbeitung dieser Aufgabe haben Sie etwa zwei Doppelstunden Zeit. Gehen Sie folgendermaßen vor:
- Lesen Sie zunächst die Anforderungen und die Aufgabenteile durch.
- Besprechen Sie anschließend in Ihrem Team die Vorgehensweise für die noch zur Verfügung stehende Arbeitszeit und für die erste Arbeitsphase.
- Planen Sie eine Phase am Ende ein, in der Sie Ihre bisherigen Ergebnisse evaluieren und Aufgaben für die Folgestunde verteilen.
- Wiederholen Sie die Schritte 2 und 3, bis Sie fertig sind.
Entwickeln Sie ein geeignetes ER-Modell, welches den gegebenen Anforderungen entspricht.
Vergleichen Sie Ihr Modell mit dem einer anderen Gruppe und einigen Sie sich auf ein gemeinsames, gegebenenfalls aus den beiden Versionen zusammengesetztes Modell. Bereiten Sie sich auf die Vorstellung Ihres Modells vor.
Das Unternehmen möchte wissen, welche datenschutzrechtlichen Belange zu beachten sind. Erstellen Sie dafür eine kurze Übersicht:
- Kennzeichnen Sie in Ihrem ER-Modell alle personenbezogenen Daten.
- Begründen Sie für jedes dieser Attribute, ob der Markt es für seinen Betrieb wirklich braucht (Datenminimierung, Zweckbindung).
- Die Kette möchte aus der gespeicherten Kaufhistorie automatisch persönliche Angebote erstellen. Beurteilen Sie dieses Vorhaben.
🎉 Ausgezeichnet – Alle Aufgaben abgeschlossen!
Hier finden Sie mögliche Lösungen zur Aufgabe 1 sowie Hinweise zu Aufgabe 2.
Aufgabe 1 a) Erläuterung des „Rechts auf informationelle Selbstbestimmung“
Das „Recht auf informationelle Selbstbestimmung“ wurde 1983 durch das Bundesverfassungsgericht im Rahmen des sogenannten Volkszählungsurteils formuliert. Es ist ein Grundrecht, das aus dem allgemeinen Persönlichkeitsrecht (Art. 2 Abs. 1 in Verbindung mit Art. 1 Abs. 1 GG) abgeleitet wird. Es besagt, dass jeder Einzelne das Recht hat, grundsätzlich selbst darüber zu entscheiden, wann, wem und innerhalb welcher Grenzen persönliche Daten über die eigene Person offengelegt und verwendet werden. Es schützt die Bürgerinnen und Bürger somit vor einer unbegrenzten Erhebung, Speicherung, Verwendung und Weitergabe ihrer Daten.
b) Begründung der Berührung dieses Rechts bei verpflichtender Nutzung
Bei einer verpflichtenden Nutzung der „SmartSports“-App im Unterricht können die Schülerinnen und Schüler nicht frei entscheiden, ob ihre Daten erhoben werden. Da sensible Gesundheitsdaten (Pulsmessung) und Bewegungsprofile (GPS-Routen) erfasst werden, greift ein Zwang massiv in das Recht ein, selbst über die Preisgabe dieser Informationen zu bestimmen. In einem Schulverhältnis besteht zudem ein Abhängigkeitsverhältnis (Notengebung); eine echte, freiwillige Einwilligung (wie sie die DSGVO fordert) ist daher bei einer verpflichtenden Einführung praktisch unmöglich.
c) Anwendung der DSGVO-Grundsätze und Risikoanalyse
- Zweckbindung: Dieser Grundsatz besagt, dass Daten nur für den Zweck verarbeitet werden dürfen, für den sie erhoben wurden. Im Szenario werden die Daten für den Sportunterricht erhoben. Die Weitergabe an das Start-up für „zielgerichtete Werbemaßnahmen“ ist eine klare Zweckentfremdung.
- Datenminimierung: Es dürfen nur die Daten erhoben werden, die für den jeweiligen Zweck absolut notwendig sind. Für die Notengebung im Ausdauerbereich reicht in der Regel die gelaufene Strecke oder die Zeit. Eine kontinuierliche, detaillierte GPS-Routenverfolgung und eine permanente Überwachung des Pulses sind übermäßig und widersprechen der Datenminimierung.
- Transparenz: Die Schülerinnen, Schüler und deren Eltern müssten klar, verständlich und im Vorfeld darüber informiert werden, welche Daten genau erhoben werden, wo sie gespeichert werden (Cloud-Server) und dass sie an Dritte (Start-up) abfließen.
- Konflikte/Risiken: Es entsteht ein immenser Interessenskonflikt zwischen dem Bildungsauftrag der Schule und den kommerziellen Interessen des Start-ups. Die sensiblen Daten Minderjähriger werden für Werbezwecke (z. B. personalisierte Rabattcodes) kommerzialisiert.
d) Möglichkeiten des Datenmissbrauchs und der De-Anonymisierung
Das Start-up behauptet, die Daten seien aufgrund der „anonymen Benutzerkennung“ sicher. Das ist ein Trugschluss, da die Daten lediglich pseudonymisiert sind.
- De-Anonymisierung durch Standortdaten (Kreuzkorrelation): GPS-Daten sind hochgradig individuell. Wenn eine Route regelmäßig an einer bestimmten Haustür beginnt und endet, lässt sich über öffentliche Melderegister, Telefonbücher oder soziale Medien sehr leicht herausfinden, welche Person dort wohnt. Die Anonymität ist somit hinfällig.
- Profiling und Gesundheitsrisiken: Selbst ohne direkten Namen lassen sich aus GPS- und Pulsdaten tiefgreifende Persönlichkeits- und Gesundheitsprofile erstellen. Beispielsweise können ungewöhnliche Pulswerte auf gesundheitliche Probleme hinweisen. Werden diese Daten von dem Start-up an Dritte (z. B. Krankenversicherungen) weiterverkauft oder mit anderen Datenbanken verknüpft, kann dies zu massiven Nachteilen für die Betroffenen führen (z. B. höhere Versicherungsbeiträge).
e) Reaktion auf die Anfragen der Universität
- Beurteilung der Notentabelle: Die Herausgabe einer rein statistischen Übersicht der Durchschnittsnoten ohne Namen ist prinzipiell unbedenklich, sofern sie vollständig anonymisiert ist. Das Problem entsteht bei sehr kleinen Fallzahlen. Gibt es, wie im Beispiel genannt, genau eine Person mit dem Geschlecht „divers“ („divers: 1“), ist diese Person für jeden, der die Zusammensetzung der Stufe kennt, sofort identifizierbar. Die Universität wüsste damit die exakte Note dieser einen Person. Das ist ein Datenschutzverstoß. Die Schule müsste solche kleinen Gruppen zusammenfassen (z. B. nur „alle: 100“), um die Anonymität zu wahren.
- Datenschutzkonforme Reaktion auf die zweite Anfrage: Die Schule darf die Kontaktdaten der Schülerinnen und Schüler mit 13 oder mehr Punkten keinesfalls ohne deren ausdrückliche Einwilligung an die Universität herausgeben. Die rechtlich zulässige Lösung ist das „Opt-In“-Verfahren mit der Schule als Mittelsmann: Die Universität gibt der Schule Informationsmaterialien oder Einladungen für die Studie. Die Lehrkräfte verteilen diese Einladungen gezielt an die entsprechenden leistungsstarken Schülerinnen und Schüler. Die Jugendlichen können dann selbst entscheiden, ob sie sich bei der Universität melden möchten oder nicht. So verlassen keine personenbezogenen Daten die Schule.
f) Automatisierte Auswertung
- Die Ausgabe hat drei Zeilen (weiblich, männlich, divers) mit Durchschnitt und Anzahl. In der Zeile „divers“ steht Anzahl 1 – der Durchschnitt ist damit genau die Note dieser einen Person. Wer den Jahrgang kennt, weiß, um wen es sich handelt.
Die Zeile „divers“ fällt weg. Kennt die Universität aber auch den Durchschnitt des ganzen Jahrgangs, lässt sich die fehlende Note zurückrechnen: 100 · Gesamtdurchschnitt − 48 · Durchschnitt der Schülerinnen − 51 · Durchschnitt der Schüler. Eine Mindestgröße der Gruppen genügt also nicht; man muss auch prüfen, welche Auswertungen zusammen herausgegeben werden.SELECT Geschlecht, AVG(Punkte) AS Durchschnitt, COUNT(*) AS Anzahl FROM Sportnoten GROUP BY Geschlecht HAVING COUNT(*) >= 5;- Chancen: individuell passende Trainingspläne, frühes Erkennen von Überlastung, schnelle Rückmeldung. Risiken: Aus Puls- und Bewegungsdaten entstehen Gesundheits- und Bewegungsprofile; ein Algorithmus bewertet Menschen, ohne dass sie nachvollziehen können, wie; Ranglisten setzen unter Druck und können zur Notengebung missbraucht werden; einmal gesammelte Daten wecken Begehrlichkeiten (z. B. beim Start-up). Eine automatische Auswertung ist daher nur mit klar begrenztem Zweck, Transparenz und echter Freiwilligkeit vertretbar.
Aufgabe 2
Die Lernenden setzen hier in der Gruppe systematisch mit einer agilen Methode eine umfangreiche Anforderungsbeschreibung zur Modellierung einer Datenbank, inklusive datenschutzrechtlicher Aspekte, um. Die Arbeitsphase ist für ca. zwei Doppelstunden angesetzt; es sind verschiedene Modellierungen möglich. Prüfen Sie die Modelle mit den Kriterien zur Bewertung von ER-Modellen.
Zu d): Personenbezogen sind vor allem die Kundendaten (Emailadresse, Zahlungsinformationen, App-Kennung, Zuordnung zum Markt) und die Kaufhistorie, außerdem die Daten der Mitarbeiterinnen und Mitarbeiter. Für den Betrieb nötig sind z. B. die Zahlungsdaten für den Bezahlvorgang; eine dauerhaft gespeicherte, vollständige Kaufhistorie ist dafür nicht erforderlich. Persönliche Angebote aus der Kaufhistorie sind eine Profilbildung: Sie verlangen eine informierte, freiwillige Einwilligung, die jederzeit widerrufen werden kann. Aus den Einkäufen lassen sich sensible Informationen ableiten (z. B. Schwangerschaft, Krankheiten, Religion). Gute Lösungen trennen Zahlungs- und Kaufdaten, speichern nur zeitlich begrenzt und werten nur mit Einwilligung aus.