Übungen Datenbanken - Daten abfragen (digital)

Aufgabe 1: Strickotopia 🧶

Hier verwenden wir die Strickotopia-Datenbank mit Kunden, Produkten und Bestellungen.

📊 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

Versuche folgende Abfragen selbst zu schreiben:

1. Zeige alle Produkte mit ihrem Namen und Preis.

2. Zeige alle Kunden aus der Schweiz.

3. Zeige alle Bestellungen vom Januar 2024 (Datum beginnt mit '2024-01').

4. Zeige die Anzahl der Bestellungen pro Kunde.

5. Zeige alle Bestellungen mit dem Namen des Kunden (JOIN).

6. Zeige für jedes Land die Anzahl seiner Kunden.

Lösungen

1. SELECT name, preis FROM produkte;

2. SELECT * FROM kunden WHERE land = 'Schweiz';

3. SELECT * FROM bestellungen WHERE datum LIKE '2024-01%';

4. SELECT kunde, COUNT(*) AS anzahl_bestellungen FROM bestellungen GROUP BY kunde;

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

6. SELECT land, COUNT(*) AS anzahl_kunden
   FROM kunden
   GROUP BY land;

Bonus

7. Zeige für jedes Produkt die Gesamtzahl verkaufter Stück.

8. Zeige die Kunden, die mehr als ein Produkt bestellt haben.

Lösungen (Bonus)

7. SELECT p.name, SUM(bp.menge) AS verkauft
   FROM produkte p
   JOIN bestellpositionen bp ON p.id = bp.produkt
   GROUP BY p.id, p.name;

8. SELECT k.id, k.name, COUNT(bp.bestellung) AS anzahl
   FROM kunden k
   JOIN bestellungen b ON k.id = b.kunde
   JOIN bestellpositionen bp ON b.id = bp.bestellung
   GROUP BY k.id, k.name
   HAVING COUNT(DISTINCT bp.bestellung) > 1;

---

Aufgabe 2: Länder und Städte 🌍

Hier verwenden wir die World-Datenbank mit Ländern und Hauptstädten.

🌍 SQLzoo World Datenbank

Tabellen anzeigen

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

cityid (PK), name, country, population

Übungsaufgaben

Versuche folgende Abfragen selbst zu schreiben:

1. Zeige alle europäischen Länder, sortiert nach Bevölkerung (größte zuerst).

2. Wie viele Länder sind in der Datenbank?

3. Zeige den Kontinent und die Gesamtbevölkerung jedes Kontinents.

4. Zeige alle Städte in Asien mit dem Namen des Landes.

5. Zeige für jedes Land die Anzahl seiner Städte.

6. Zeige die größte Stadt jedes Kontinents.

Lösungen

1. SELECT name, population
   FROM world
   WHERE continent = 'Europe'
   ORDER BY population DESC;

2. SELECT COUNT(*) AS anzahl_laender FROM world;

3. SELECT continent, SUM(population) AS gesamt_bevoelkerung
   FROM world
   GROUP BY continent;

4. SELECT city.name AS stadt, world.name AS land
   FROM city
   JOIN world ON city.country = world.name
   WHERE world.continent = 'Asia';

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

6. SELECT w.continent, c.name AS stadt, c.population
   FROM city c
   JOIN world w ON c.country = w.name
   WHERE (w.continent, c.population) IN (
     SELECT w2.continent, MAX(c2.population)
     FROM city c2
     JOIN world w2 ON c2.country = w2.name
     GROUP BY w2.continent
   );

Bonus

7. Zeige für jedes Land die Hauptstadt und die Bevölkerung der Hauptstadt im Verhältnis zur Landesbevölkerung (in Prozent).

8. Welcher Kontinent hat die höchste durchschnittliche GDP pro Kopf?

Lösungen (Bonus)

7. SELECT
     w.name AS land,
     c.name AS hauptstadt,
     ROUND(c.population * 100.0 / w.population, 2) AS anteil_prozent
   FROM world w
   JOIN city c ON w.name = c.country
   ORDER BY anteil_prozent DESC;

8. SELECT continent, AVG(gdp / population) AS durchschnitt_gdp_pro_kopf
   FROM world
   WHERE population > 0
   GROUP BY continent
   ORDER BY durchschnitt_gdp_pro_kopf DESC
   LIMIT 1;