Übungen – Daten verknüpfen (JOIN)

Aufgabe 1: JOIN lesen und verstehen (analog)

Gegeben sind die folgenden zwei Tabellen aus der Strickotopia-Datenbank:

Tabelle: kunden

id	vorname	name	land
1	Max	Mustermann	Schweiz
2	Anna	Schmidt	Schweiz
3	Lukas	Weber	Deutschland
4	Sophie	Müller	Österreich

Tabelle: bestellungen

id	datum	kunde
1	2024-01-10	2
2	2024-01-18	1
3	2024-02-05	2
4	2024-02-20	3
5	2024-03-01	1

Teil A: Was ist das Ergebnis?

Gib jeweils die Tabelle an, die durch die folgende SQL-Abfrage zurückgegeben wird. Löse die Aufgabe zunächst ohne Computer.

SELECT b.id, b.datum, k.vorname, k.name
FROM bestellungen b
JOIN kunden k ON b.kunde = k.id;

SELECT b.id, b.datum, k.vorname
FROM bestellungen b
JOIN kunden k ON b.kunde = k.id
WHERE k.id = 1;

SELECT k.vorname, k.name, COUNT(*) AS anzahl_bestellungen
FROM bestellungen b
JOIN kunden k ON b.kunde = k.id
GROUP BY k.id, k.vorname, k.name;

SELECT b.id, b.datum, k.vorname, k.name
FROM bestellungen b
JOIN kunden k ON b.kunde = k.id
WHERE k.land = 'Schweiz'
ORDER BY b.datum;

Teil B: Schreibe die SQL-Abfrage

Schreibe die SQL-Abfrage, die das Folgende liefert. Verwende die zwei Tabellen oben.

• 1a) Eine Liste aller Bestellungen mit Bestelldatum und dem vollständigen Namen des Kunden (Vorname und Name),

• 1b) Das selbe, jetzt aber noch sortiert nach Datum (neueste zuerst).

• 2. Zeige nur die Bestellungen, die nach dem 31. Januar 2024 aufgegeben wurden (datum > '2024-01-31'), zusammen mit dem Land des Kunden.
• 3. Zeige für jedes Land, wie viele Bestellungen aus diesem Land kamen.

Aufgabe 2: Strickotopia – drei Tabellen (digital)

Hier verwenden wir die vollständige Strickotopia-Datenbank mit den Tabellen kunden, bestellungen, bestellpositionen und produkte.

📊 Strickotopia Datenbank

Tabellen anzeigen

kundenid (PK), vorname, name, email, strasse, plz, land

produkteid (PK), name, preis, stoff, stoffmenge

bestellungenid (PK), kunde (FK→kunden.id), datum

bestellpositionenbestellung (FK→bestellungen.id), produkt (FK→produkte.id), menge

Übungsaufgaben

1. Zeige alle Bestellungen mit dem Vor- und Nachnamen des Kunden sowie dem Bestelldatum.

2. Zeige alle Bestellpositionen mit dem Namen des Produkts und der bestellten Menge.

3. Zeige alle Bestellungen mit dem Kundennamen und dem Namen des bestellten Produkts (verbinde kunden, bestellungen, bestellpositionen und produkte).

4. Zeige für jeden Kunden, wie viele Produkte er insgesamt bestellt hat (Summe der Mengen aus bestellpositionen).

5. Welche Produkte wurden von Schweizer Kunden bestellt? Zeige den Produktnamen und den Namen des Kunden.

Lösungen

SELECT k.vorname, k.name, b.datum
FROM bestellungen b
JOIN kunden k ON b.kunde = k.id;

SELECT p.name AS produkt, bp.menge
FROM bestellpositionen bp
JOIN produkte p ON bp.produkt = p.id;

SELECT k.vorname, k.name, p.name AS produkt, bp.menge
FROM bestellungen b
JOIN kunden k ON b.kunde = k.id
JOIN bestellpositionen bp ON bp.bestellung = b.id
JOIN produkte p ON bp.produkt = p.id;

SELECT k.vorname, k.name, SUM(bp.menge) AS gesamt_menge
FROM kunden k
JOIN bestellungen b ON b.kunde = k.id
JOIN bestellpositionen bp ON bp.bestellung = b.id
GROUP BY k.id, k.vorname, k.name;

SELECT p.name AS produkt, k.vorname, k.name
FROM bestellungen b
JOIN kunden k ON b.kunde = k.id
JOIN bestellpositionen bp ON bp.bestellung = b.id
JOIN produkte p ON bp.produkt = p.id
WHERE k.land = 'Schweiz';

Bonus

6. Zeige für jedes Produkt den Gesamtumsatz (Preis × Menge), sortiert vom höchsten zum tiefsten.

7. Welcher Kunde hat am meisten ausgegeben? Zeige den Namen und den Gesamtbetrag.

Lösungen (Bonus)

SELECT p.name AS produkt, SUM(p.preis * bp.menge) AS umsatz
FROM produkte p
JOIN bestellpositionen bp ON bp.produkt = p.id
GROUP BY p.id, p.name
ORDER BY umsatz DESC;

SELECT k.vorname, k.name, SUM(p.preis * bp.menge) AS gesamtbetrag
FROM kunden k
JOIN bestellungen b ON b.kunde = k.id
JOIN bestellpositionen bp ON bp.bestellung = b.id
JOIN produkte p ON bp.produkt = p.id
GROUP BY k.id, k.vorname, k.name
ORDER BY gesamtbetrag DESC
LIMIT 1;

Aufgabe 3: Länder und Städte (digital)

Hier verwenden wir die World-Datenbank mit den Tabellen world (Länder) und city (Städte).

🌍 SQLzoo World Datenbank

Tabellen anzeigen

worldname (PK), continent, area, population, gdp

cityid (PK), name, country, population

Übungsaufgaben

1. Zeige alle Städte mit dem Namen des Landes, in dem sie liegen.

2. Zeige alle Städte in Europa mit dem Namen des Landes, sortiert nach Stadtname.

3. Zeige für jedes Land die Anzahl seiner Städte in der Datenbank.

4. Welches Land hat die bevölkerungsreichste Stadt? Zeige den Ländernamen und den Stadtnamen.

5. Zeige für jeden Kontinent die Gesamtbevölkerung aller Städte in der Datenbank.

Lösungen

SELECT city.name AS stadt, world.name AS land
FROM city
JOIN world ON city.country = world.name;

SELECT city.name AS stadt, world.name AS land
FROM city
JOIN world ON city.country = world.name
WHERE world.continent = 'Europe'
ORDER BY city.name;

SELECT w.name AS land, COUNT(c.id) AS anzahl_staedte
FROM world w
JOIN city c ON c.country = w.name
GROUP BY w.name
ORDER BY anzahl_staedte DESC;

SELECT w.name AS land, c.name AS stadt, c.population
FROM city c
JOIN world w ON c.country = w.name
ORDER BY c.population DESC
LIMIT 1;

SELECT w.continent, SUM(c.population) AS stadtbevoelkerung_gesamt
FROM city c
JOIN world w ON c.country = w.name
GROUP BY w.continent
ORDER BY stadtbevoelkerung_gesamt DESC;

Bonus

6. Zeige für jedes Land den Anteil der Stadtbevölkerung an der Gesamtbevölkerung des Landes (in Prozent), sortiert vom höchsten Anteil.

Lösung (Bonus)

SELECT
  w.name AS land,
  SUM(c.population) AS stadtbevoelkerung,
  w.population AS landbevoelkerung,
  ROUND(SUM(c.population) * 100.0 / w.population, 1) AS anteil_prozent
FROM world w
JOIN city c ON c.country = w.name
WHERE w.population > 0
GROUP BY w.name, w.population
ORDER BY anteil_prozent DESC;