Prüfungsvorbereitung

Die Prüfung besteht aus zwei Teilen: einem Theorie-Teil und praktischen SQL-Aufgaben. Der Umfang sind die Lektionen 3 (Was sind Datenbanken?) bis 7 (JOIN-Befehle).

Teil 1: Theorie

In diesem Teil überprüfen wir Ihr Verständnis zu grundlegenden Datenbankkonzepten wie Tabellen-Design, Datenintegrität und Schlüsseln.

Frage 1: Vorteile relationaler Datenbanken

Welche der folgenden Aussagen beschreiben wesentliche Vorteile von relationalen Datenbanken gegenüber einfachen Tabellenkalkulationen (wie Excel)? (Mehrere Antworten möglich)

Frage 2: Schlüsselkonzepte

Was ist der zentrale Unterschied zwischen einem Primärschlüssel (Primary Key) und einem Fremdschlüssel (Foreign Key)?

Frage 3: Beziehungstypen im echten Leben

Schülerinnen und Schüler besuchen in der Regel verschiedene Kurse, und in einem Kurs sitzen meistens viele Schülerinnen und Schüler. Wie wird diese Art der Beziehung in einer relationalen Datenbank korrekt modelliert?

Frage 4: JOIN vs. LEFT JOIN

Worin besteht der wesentliche Unterschied zwischen einem JOIN und einem LEFT JOIN?

Teil 2: Praktische Aufgaben mit SQL

Wenden Sie das Gelernte nun an! Ihnen steht eine vorbereitete Spotify-Datenbank zur Verfügung. Lesen Sie die Aufgabenstellung jeweils genau durch, achten Sie auf Rundungen und geforderte Filter.

Das Datenbankschema für die Aufgaben

Die Tabelle für alle folgenden Aufgaben heisst tracks.Nutzen Sie für Ihre Abfragen ausschliesslich folgende Spaltennamen:

• track_name (Name des Songs)
• artists (Name der Künstlerin / des Künstlers)
• track_genre (Musikrichtung, z.B. 'rock', 'pop')
• popularity (Beliebtheitsscore von 0 bis 100)
• duration_ms (Länge des Songs in Millisekunden)
• danceability (Wert dafür, wie gut man dazu tanzen kann, von 0.0 bis 1.0)
• explicit (Markierung als explizit, Wert 'True' oder 'False')

Aufgabe 1: Erste Daten anzeigen

Zeigen Sie die Spalten track_name, artists und track_genre der Tabelle tracks an. Begrenzen Sie die Ausgabe auf 5 Ergebnisse.

Aufgabe 2: Filtern mit WHERE

Zeigen Sie alle Songs an, die eine Beliebtheit (popularity) von mehr als 90 haben. Geben Sie track_name, artists und popularity aus, sortiert nach Beliebtheit absteigend.

Aufgabe 3: Zählen mit COUNT

Wie viele Songs in der Datenbank gehören zum Genre 'pop'? Verwenden Sie den Alias anzahl_songs für das Ergebnis.

Aufgabe 4: SELECT mit mehreren Bedingungen

Finden Sie die 5 populärsten Songs im Genre 'rock'.
Zielausgabe: Zeigen Sie Titel (track_name), Künstler (artists) und Beliebtheit (popularity) an. Sortieren Sie das Ergebnis so, dass der populärste Song ganz oben steht.

Aufgabe 5: Aggregation & Mathematik

Die Länge der Songs ist in der Datenbank sehr unintuitiv in Millisekunden abgespeichert. Berechnen Sie die durchschnittliche Dauer in Minuten für alle Songs des Künstlers 'Ed Sheeran'.
Tipp: Sie müssen dazu die Spalte duration_ms durch 60000.0 teilen.
Zielausgabe: Zeigen Sie nur eine einzige Spalte an, berechnen Sie darin diesen Durchschnitt und runden Sie ihn auf 2 Nachkommastellen (nutzen Sie die Funktion ROUND()). Verwenden Sie den Alias avg_duration_min für das Ergebnis.

Aufgabe 6: Gruppieren (GROUP BY)

Finden Sie heraus, welche 10 Genres am meisten Songs haben, die als explizit (explicit = 'True') markiert sind.
Zielausgabe: Zeigen Sie die Spalte track_genre und die berechnete Spalte mit der Anzahl an (verwenden Sie für die Anzahl den Alias anzahl_explicit). Sortieren Sie nach Anzahl absteigend.

Aufgabe 7: Filtern von Gruppen (GROUP BY & HAVING)

Die Plattenfirma möchte wissen, welche Genres bei populären Songs besonders tanzbar sind. Berücksichtigen Sie nur Songs mit einer Beliebtheit (popularity) grösser als 50. Finden Sie dann alle Genres, die mehr als 20 solcher Songs haben UND deren durchschnittliche Tanzbarkeit (danceability) grösser als 0.7 ist.
Zielausgabe: Zeigen Sie drei Spalten an:

1. track_genre
2. Die durchschnittliche Tanzbarkeit (gerundet auf 2 Nachkommastellen, Alias: avg_danceability)
3. Wie viele Songs das Genre hat (Alias: song_count)

Sortieren Sie das Endergebnis nach der durchschnittlichen Tanzbarkeit absteigend.

Aufgabe 8: Einfache Verknüpfung über eine Zwischentabelle

Die eindimensionale Tabelle bisher reicht für einfache Abfragen, führt aber in der Realität schnell zu grosser Datenredundanz. Ab jetzt arbeiten wir deshalb mit einer normalisierten Datenbank.

Das normalisierte Schema für JOIN-Aufgaben (spotify.db)

Für die folgenden Aufgaben nutzen Sie die Datenbank spotify.db. Die unterschiedlichen Informationen sind nun sauber per n:m-Beziehung aufgeteilt.

• tracks: Enthält Infos zu den Songs (track_id, track_name, popularity, duration_ms, danceability).
• artists: Enthält Infos zu den Künstlerinnen und Künstlern (artist_id, name).
• track_artists: Die reine Zwischentabelle zur Verknüpfung der Tracks und Artists (track_id, artist_id).

Zeigen Sie den Songtitel (track_name) und den Namen der Künstlerin / des Künstlers (name) an. Verknüpfen Sie dazu die Tabellen tracks, track_artists und artists mithilfe von JOIN. Begrenzen Sie die Ausgabe auf 5 Ergebnisse.

Aufgabe 9: JOIN kombiniert mit GROUP BY

Welche 5 Künstlerinnen oder Künstler haben die meisten Lieder in dieser Datenbank verzeichnet? Zielausgabe: Zeigen Sie den Spaltennamen name und die berechnete Anzahl als anzahl_tracks an. Sortieren Sie die Liste absteigend nach der Anzahl.

Tipp: Verknüpfen Sie die Tabelle artists mit track_artists, gruppieren Sie nach Künstler und zählen Sie anschliessend die Einträge.

Aufgabe 10: Komplexe Abfrage mit JOIN und Filtern

Finden Sie heraus, von welchen Künstlern die echten Hit-Lieder stammen. Wir bestimmen diese über eine Beliebtheit (popularity) von über 95. Zielausgabe: Geben Sie in Ihrer Abfrage name, track_name und popularity zurück. Sortieren Sie das Resultat nach der Beliebtheit absteigend.