Die Olympia-Datenbank: Anfragen (Teil 4)

Lösen Sie die fol­gen­de Auf­ga­be mit Post­greS­QL.

Für spe­zi­el­le Syn­tax­fra­gen steht Ihnen die offi­zi­el­le Online-Doku­men­ta­ti­on von Post­greS­QL zur Verfügung.

Die Daten­ty­pen wer­den in Kapi­tel 8 des Post­greS­QL-Manu­als beschrie­ben. Die Datums­ty­pen DATE und TIMESTAMP fin­den Sie im Post­greS­QL-Manu­al in den Abschnit­ten Date/​Time Types und Date/​Time Func­tions and Ope­ra­tors.

Das Datenmodell

Gege­ben sei wie­der fol­gen­de Daten­bank zur Spei­che­rung von olym­pi­schen 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 Olym­pia-Daten­bank soll ein – etwas unkon­ven­tio­nel­ler, aber aus­sa­ge­kräf­ti­ge­rer – Medail­len­spie­gel erstellt werden:

Für eine Gold­me­dail­le erhält ein Land drei Punk­te, für eine Sil­ber­me­dail­le zwei und für eine Bron­ze­me­dail­le einen Punkt. Die Punk­te las­sen sich ganz ein­fach aus der Plat­zie­rung berech­nen: 4 - platz.

Erstel­len Sie eine geschlos­se­ne SQL-Que­ry (d.h. ohne Views), die die vier punkt­stärks­ten Län­der ermit­telt – ver­se­hen mit der Rang­num­mer und der Abwei­chung der Punkt­zahl vom Durch­schnitt der Punkt­zahl aller Teilnehmerländer.

Bei­spiel:

rang land punk­te abwei­chung
1 GER 123 87.5
2 USA 121 85.5
2 CHI 121 85.5
4 RUS 99 63.5

Sie soll­ten bei der Lösung der Auf­ga­be schritt­wei­se vor­ge­hen. Defi­nie­ren Sie für jedes Zwi­schen-Ergeb­nis eine eige­ne View und ver­wen­den Sie die­se für die nächs­ten Zwi­schen-Ergeb­nis­se. Erset­zen Sie ganz zum Schluss jeden View-Auf­ruf durch die jewei­li­ge View-Definition.

Die ein­zel­nen Teil­schrit­te sind in der Datei olympia_aufgabe_4.sql ent­hal­ten. Kopie­ren Sie die­se Datei auf Ihren Rech­ner und tra­gen Sie Ihre Lösun­gen in die Datei ein.

Ach­ten Sie bit­te dar­auf, dass Ihre SQL-Anfra­gen für jeden gül­ti­gen Daten­be­stand genau die gewünsch­ten Infor­ma­tio­nen als Ergeb­nis aus­ge­ben, nicht mehr und nicht weniger.

Haus­auf­ga­be

Über­le­gen Sie sich für die Auf­ga­ben des drit­ten Teils der Olym­pia­auf­ga­be olympia_aufgabe_3.sqlwei­te­re Alternativlösungen:

Auf­ga­be 3a:
3 Vari­an­ten: Outer Join; JOIN+UNION+NOT EXISTS; JOIN+UNION+IN+EXCEPT

Auf­ga­be 3d/​3e:
2 Vari­an­ten: Aggre­ga­ti­on+GROUP BY; Aggr. ohne GROUP BY aber mit Subquery

Auf­ga­be 3f:
2 Vari­an­ten: HAVING COUNT(*) = <Subquery>; NOT EXISTS+EXCEPT

Ver­su­chen Sie zumin­dest eine die­ser bei­den Lösung DRY zu bekom­men, indem Sie Tei­le der Anfra­ge in eine tem­po­rä­re View (WITH) auslagern.

Auf­ga­be 3g:
vie­le Vari­an­ten: >= ALL; SELECT MAX ...(SELECT COUNT ...);  MAX(COUNT(*)) OVER (); WITH+LATERAL

Für jede die­ser Lösun­gen gibt es meh­re­re Sub­va­ri­an­ten. Expe­ri­men­tie­ren Sie!

Den­ken Sie dar­an: Maxi­mum­su­che mit LIMIT 1 ist unbrauch­bar, auch wenn Sie das per Goog­le finden.

Extraaufgabe

Wenn Sie eine Her­aus­for­de­rung suchen, dann lösen Sie zusätz­lich die Auf­ga­ben, die Sie in der Datei olympia_aufgabe_extra.sql vorfinden.