Vorbereitung Kurzprüfung

Die Prüfung am 1. Oktober besteht aus zwei Teilen: einem Theorie-Teil und praktischen SQL-Aufgaben hier auf Informatikgarten. Der Umfang sind die Seiten «Was sind Datenbanken?», «Daten abfragen mit SELECT», «Aggregatfunktionen» und «Datenbank-Design». JOIN-Befehle sind nicht Teil der Prüfung.

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: WHERE vs. HAVING

Worin besteht der wesentliche Unterschied zwischen WHERE und HAVING?

Frage 5: Redundanz erkennen

In unserer Spotify-Tabelle tracks taucht «Blinding Lights» mehrfach auf, und bei Kollaborationen stehen mehrere Künstler in einem Feld (z.B. Ingrid Michaelson;ZAYN). Welche Aussagen treffen zu? (Mehrere Antworten möglich)

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: Rechnen in der Abfrage

Welches sind die 5 längsten Songs im Genre 'metal', die nicht als explizit markiert sind (explicit = 'False')?
Tipp: Teilen Sie duration_ms durch 60000.0, um Minuten zu erhalten.
Zielausgabe: Zeigen Sie track_name, artists und die Dauer in Minuten (gerundet auf 2 Nachkommastellen, Alias: dauer_min) an. Der längste Song steht zuoberst.

Aufgabe 6: 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'.
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 7: Einzigartige Werte zählen

Wie viele verschiedene Einträge in der Spalte artists gibt es im Genre 'hip-hop'? Verwenden Sie den Alias anzahl_artists.

Aufgabe 8: 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 9: Genres vergleichen (IN, MIN, MAX)

Vergleichen Sie die Beliebtheit der Genres 'pop', 'rock', 'jazz' und 'classical'.
Zielausgabe: Zeigen Sie vier Spalten an:

1. track_genre
2. Die tiefste Beliebtheit (Alias: min_pop)
3. Die höchste Beliebtheit (Alias: max_pop)
4. Die durchschnittliche Beliebtheit, gerundet auf 1 Nachkommastelle (Alias: avg_pop)

Sortieren Sie nach der durchschnittlichen Beliebtheit absteigend.

Aufgabe 10: 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 11: Die Hit-Maschinen (WHERE, GROUP BY & HAVING)

Welche Künstler haben mindestens 15 Einträge mit einer Beliebtheit von 80 oder mehr?
Zielausgabe: Zeigen Sie artists und die Anzahl (Alias: anzahl_hits) an. Sortieren Sie nach Anzahl absteigend; bei Gleichstand alphabetisch nach artists.

Zum Nachdenken

Bad Bunny hat laut Ihrer Abfrage über 40 Hits. Stimmt das wirklich? Denken Sie an Frage 5 zurück.