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')}
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.
Musterlösungen:
olympia1_aufgabe_4_loesung.sql,
olympia2_aufgabe_4_loesung.sql,
olympia3_aufgabe_4_loesung.sql
Hausaufgabe
Überlegen Sie sich für die Aufgaben des dritten Teils der Olympiaaufgabe olympia_aufgabe_3.sqlweitere 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.
Musterlösungen:
olympia1_aufgabe_3_loesung.sql,
olympia2_aufgabe_3_loesung.sql,
olympia3_aufgabe_3_loesung.sql
Extraaufgabe
Wenn Sie eine Herausforderung suchen, dann lösen Sie zusätzlich die Aufgaben, die Sie in der Datei olympia_aufgabe_extra.sql vorfinden.
Musterlösung: olympia_aufgabe_extra_loesung.sql