Die Olympia-Datenbank: Anfragen (Teil 4)
Lösen Sie die folgende Aufgabe mit PostgreSQL.
Für spezielle Syntaxfragen steht Ihnen die offizielle Online-Dokumentation von PostgreSQL zur Verfügung.
Die Datentypen werden in Kapitel 8 des PostgreSQL-Manuals beschrieben. Die Datumstypen DATE
und TIMESTAMP
finden Sie im PostgreSQL-Manual in den Abschnitten Date/Time Types und Date/Time Functions and Operators.
Das Datenmodell
Gegeben sei wieder folgende Datenbank zur Speicherung von olympischen Wettbewerben:

Datenbankschema
sportler: id, name, vorname*, geburtstag, land, geschlecht {PK: id} {UNIQUE: name, vorname*, geburtstag, land, geschlecht} {geschlecht IN ('w', 'm')} wettkampf: id, sportart, wettkampfart, geschlecht, sportstaette, beginn* {PK: id} {UNIQUE: sportart, wettkampfart, geschlecht} {geschlecht IN ('w', 'm', 'x')} teilnahme: s_id, w_id, platz*, doping*, disqualifiziert {PK: s_id, w_id} {FK: s_id -> sportler: id} {FK: w_id -> wettkampf: id} {platz > 0} {NOT(doping IS TRUE AND disqualifiziert IS FALSE)} {NOT(disqualifiziert IS TRUE AND platz IS NOT NULL)}
Aufgabe
Für Ihre Olympia-Datenbank soll ein – etwas unkonventioneller, aber aussagekräftigerer – Medaillenspiegel erstellt werden:
Für eine Goldmedaille erhält ein Land drei Punkte, für eine Silbermedaille zwei und für eine Bronzemedaille einen Punkt. Die Punkte lassen sich ganz einfach aus der Platzierung berechnen: 4 - platz
.
Erstellen Sie eine geschlossene SQL-Query (d.h. ohne Views), die die vier punktstärksten Länder ermittelt – versehen mit der Rangnummer und der Abweichung der Punktzahl vom Durchschnitt der Punktzahl aller Teilnehmerländer.
Beispiel:
rang | land | punkte | abweichung |
1 |
GER |
123 |
87.5 |
2 |
USA |
121 |
85.5 |
2 |
CHI |
121 |
85.5 |
4 |
RUS |
99 |
63.5 |
Sie sollten bei der Lösung der Aufgabe schrittweise vorgehen. Definieren Sie für jedes Zwischen-Ergebnis eine eigene View und verwenden Sie diese für die nächsten Zwischen-Ergebnisse. Ersetzen Sie ganz zum Schluss jeden View-Aufruf durch die jeweilige View-Definition.
Die einzelnen Teilschritte sind in der Datei olympia_aufgabe_4.sql
enthalten. Kopieren Sie diese Datei auf Ihren Rechner und tragen Sie Ihre Lösungen in die Datei ein.
Achten Sie bitte darauf, dass Ihre SQL-Anfragen für jeden gültigen Datenbestand genau die gewünschten Informationen als Ergebnis ausgeben, nicht mehr und nicht weniger.
Hausaufgabe
Überlegen Sie sich für die Aufgaben des dritten Teils der Olympiaaufgabe olympia_aufgabe_3.sql
weitere Alternativlösungen:
Aufgabe 3a:
3 Varianten: Outer Join; JOIN
+UNION
+NOT EXISTS
; JOIN
+UNION
+IN
+EXCEPT
Aufgabe 3d/3e:
2 Varianten: Aggregation+GROUP BY
; Aggr. ohne GROUP BY
aber mit Subquery
Aufgabe 3f:2 Varianten:
HAVING COUNT(*) = <Subquery>
; NOT EXISTS
+EXCEPT
Versuchen Sie zumindest eine dieser beiden Lösung DRY zu bekommen, indem Sie Teile der Anfrage in eine temporäre View (WITH) auslagern.
Aufgabe 3g:
viele Varianten: >= ALL
; SELECT MAX ...(SELECT COUNT ...)
; MAX(COUNT(*)) OVER ()
; WITH
+LATERAL
Für jede dieser Lösungen gibt es mehrere Subvarianten. Experimentieren Sie!
Denken Sie daran: Maximumsuche mit LIMIT 1
ist unbrauchbar, auch wenn Sie das per Google finden.