/* ** Olympiade (Teil 3) */ /* ** Aufgabe a ** ** Welche Sportlerinnen erhielten im Schwimmwettbewerb ** 'Brust 100m' der Frauen eine Medaille? platz id name vorname geburtstag land ---------------------------------------------------- 1 6 Angelopoulou Vasiliki 1987-05-30 GRE 1 8 Karsten Ekaterina 1972-06-02 BLR 1 1 Völker Sandra 1974-04-01 GER 1 11 Yukhareva Natalya 1970-01-01 RUS 2 4 Buschschulte Antje 1978-12-27 GER 2 5 Filippi Alessia 1987-06-23 ITA 2 13 Harel Barbara 1970-01-01 FRA */ SELECT t.platz, s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id JOIN e_wettkampf w ON t.w_id = w.id JOIN enum_wettkampfart wa ON w.wa_id = wa.id JOIN enum_sportart sa ON wa.sa_id = sa.id WHERE sa.name ='Schwimmen' AND wa.name = 'Brust 100m' AND s.geschlecht='w' AND t.platz BETWEEN 1 AND 3 ORDER BY platz, name, vorname, geburtstag, land ; /* ** Aufgabe b ** ** Erstellen Sie ein Teilnehmer-Verzeichnis. Neben den persönlichen Daten ** der Teilnehmer sollen auch die Sportarten aufgeführt werden, ** in denen diese antreten. ** ** Geben Sie in diesem Verzeichnis auch diejenigen Sportler aus, ** die noch in keinem Wettbewerb gemeldet sind. id name vorname geburtstag land sportart ------------------------------------------------------------------ 6 Angelopoulou Vasiliki 1987-05-30 GRE Schwimmen 20 Becker Boris 1967-11-22 GER Tennis 10 Boenisch Yvonne 1980-12-29 GER NULL 4 Buschschulte Antje 1978-12-27 GER Schwimmen 12 Cavazzuti Cinzia 1970-01-01 ITA NULL 18 Dede NULL 1970-01-01 GER NULL 9 Fedotova Irina 1970-01-01 RUS NULL 5 Filippi Alessia 1987-06-23 ITA Schwimmen 19 Graf Steffi 1969-06-14 GER Tennis 13 Harel Barbara 1970-01-01 FRA Schwimmen 3 Harstick Sara 1981-01-01 GER Schwimmen 8 Karsten Ekaterina 1972-06-02 BLR Schwimmen 16 Maier Josef 1980-01-01 GER Rudern 17 Maier Josef 1982-01-01 GER Schwimmen 7 Rutschow-Stomporowski Katrin 1975-04-02 GER NULL 15 Tuuxaru Iliisquix 1970-01-01 CUB Schwimmen 2 van Almsick Franziska 1978-04-05 GER Rudern 2 van Almsick Franziska 1978-04-05 GER Schwimmen 1 Völker Sandra 1974-04-01 GER Schwimmen 14 Xeteylu Leidisyuri 1970-01-01 CUB Schwimmen 11 Yukhareva Natalya 1970-01-01 RUS Schwimmen */ -- LEFT JOIN (oder vergleichbarer UNION) ist wichtig SELECT DISTINCT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, sa.name AS sportart FROM e_sportler s LEFT JOIN enum_land l ON s.l_id = l.id LEFT JOIN r_teilnahme t ON t.s_id = s.id LEFT JOIN e_wettkampf w ON t.w_id = w.id LEFT JOIN enum_wettkampfart wa ON w.wa_id = wa.id LEFT JOIN enum_sportart sa ON wa.sa_id = sa.id ORDER BY name, vorname, geburtstag, land ; -- -- Mit Listen-Aggregation -- -- SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, array_agg(sa.name) AS sportarten FROM e_sportler s LEFT JOIN enum_land l ON s.l_id = l.id LEFT JOIN r_teilnahme t ON t.s_id = s.id LEFT JOIN e_wettkampf w ON t.w_id = w.id LEFT JOIN enum_wettkampfart wa ON w.wa_id = wa.id LEFT JOIN enum_sportart sa ON wa.sa_id = sa.id GROUP BY s.id, s.name, s.vorname, s.geburtstag, l.kuerzel ORDER BY name, vorname, geburtstag, land ; /* ** Aufgabe c ** ** Welche Plätze belegt Antje Buschschulte bislang wie oft? id name vorname geburtstag land platz anzahl ---------------------------------------------------------- 4 Buschschulte Antje 1978-12-27 GER 1 2 4 Buschschulte Antje 1978-12-27 GER 2 1 */ /* alle Sportler, auch nicht-gemeldete */ SELECT t.platz, s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, COUNT(*) AS anzahl FROM e_sportler s JOIN enum_land l ON s.l_id = l.id LEFT JOIN r_teilnahme t ON t.s_id = s.id GROUP BY t.platz, s.id, s.name, s.vorname, s.geburtstag, l.kuerzel ORDER BY name, vorname, geburtstag, land ; /* Antje Buschschulte (genau eine) */ SELECT t.platz, s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, COUNT(*) AS anzahl FROM e_sportler s JOIN enum_land l ON s.l_id = l.id LEFT JOIN r_teilnahme t ON t.s_id = s.id WHERE s.name='Buschschulte' AND s.vorname='Antje' AND l.kuerzel='GER' AND s.geburtstag = DATE '1978-12-27' GROUP BY t.platz, s.id, s.name, s.vorname, s.geburtstag, l.kuerzel ORDER BY name, vorname, geburtstag, land ; /* Antje Buschschulte (eventuell mehrere) */ SELECT t.platz, s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, COUNT(*) AS anzahl FROM e_sportler s JOIN enum_land l ON s.l_id = l.id LEFT JOIN r_teilnahme t ON t.s_id = s.id WHERE s.name='Buschschulte' AND s.vorname='Antje' GROUP BY t.platz, s.id, s.name, s.vorname, s.geburtstag, l.kuerzel ORDER BY name, vorname, geburtstag, land ; -- etwas komplizierter Variante, aber dieselbe Idee SELECT id, vorname, name, geburtstag, land, platz, COUNT(*) AS anzahl FROM (SELECT t.platz, s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land FROM e_sportler s JOIN enum_land l ON s.l_id = l.id LEFT JOIN r_teilnahme t ON t.s_id = s.id WHERE s.name='Buschschulte' AND s.vorname='Antje' ) AS BuschschultePlaetze GROUP BY id, vorname, name, geburtstag, land, platz ; /* ** Aufgabe d ** ** Welche Sportler und Sportlerinnen haben mehrere Medaillen gewonnen? ** Die Spalte 'medaillenzahl' ist eigentlich unnötig, da nicht gefragt! ** ** Lösen Sie die Aufgabe zweimal: ** - Einmal mit Aggregation ** - Einmal nur mit Selektion, Projektion und Inner Join, ** d.h, ohne Aggregation, Subqueries oder irgendwelche ** andere komplexeren SQL-Konstrukte. ** (diesmal OHNE Ausgabe der Medaillenzahl!) id name vorname geburtstag land medaillenanzahl -------------------------------------------------------------- 20 Becker Boris 1967-11-22 GER 2 4 Buschschulte Antje 1978-12-27 GER 3 5 Filippi Alessia 1987-06-23 ITA 2 19 Graf Steffi 1969-06-14 GER 2 2 van Almsick Franziska 1978-04-05 GER 2 1 Völker Sandra 1974-04-01 GER 4 */ -- Die Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt). SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, COUNT(*) AS medaillenanzahl FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id WHERE t.platz BETWEEN 1 AND 3 GROUP BY s.id, s.name, s.vorname, s.geburtstag, l.kuerzel HAVING COUNT(*)>1 ORDER BY name, vorname, geburtstag, land ; -- ohne GROUP BY SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land FROM e_sportler s JOIN enum_land l ON s.l_id = l.id WHERE (SELECT COUNT(*) FROM r_teilnahme t WHERE t.s_id=s.id AND t.platz BETWEEN 1 AND 3 ) > 1 ORDER BY name, vorname, geburtstag, land ; -- Nur einfache Projektion, Inner Join und einfache Selektion. -- Keine Aggregation oder anderen komplexen Konstrukte. SELECT DISTINCT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t1 ON s.id = t1.s_id JOIN r_teilnahme t2 ON s.id = t2.s_id AND t1.w_id < t2.w_id WHERE t1.platz BETWEEN 1 AND 3 AND t2.platz BETWEEN 1 AND 3 ORDER BY name, vorname, geburtstag, land ; -- <> an Stelle von < funktioniert auch SELECT DISTINCT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t1 ON s.id = t1.s_id JOIN r_teilnahme t2 ON s.id = t2.s_id AND t1.w_id <> t2.w_id WHERE t1.platz BETWEEN 1 AND 3 AND t2.platz BETWEEN 1 AND 3 ORDER BY name, vorname, geburtstag, land ; -- Allerdings werden bei t1.w_id <> t2.w_id doppelt so viele Tupel -- berechnet (Duplikate!) wie bei t1.w_id < t2.w_id, da für jede Person, -- die beispielsweise die beiden Wettbewerbe 3 und 4 gewonnen hat, -- festgestellt wird, dass sie auch die beiden Wettbewerbe 4 und 3 -- gewonnen hat. Dies trägt aber zu der gesuchten Information -- "Hat die Person mindestens zwei Wettbewerbe gewonnen?" nichts bei. /* ** Aufgabe e ** ** Welche Sportler/-innen haben drei oder mehr Gold-Medaillien gewonnen? ** Die Spalte 'medaillenzahl' ist eigentlich unnötig, da nicht gefragt! ** ** Lösen Sie die Aufgabe zweimal: ** - Einmal mit Aggregation ** - Einmal nur mit Selektion, Projektion und Inner Join ** (diesmal OHNE Ausgabe der Medaillenzahl!) id name vorname geburtstag land medaillenanzahl -------------------------------------------------------- 1 Völker Sandra 1974-04-01 GER 4 */ -- Die Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt). SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, COUNT(*) AS medaillenanzahl FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id WHERE t.platz=1 GROUP BY s.id, s.name, s.vorname, s.geburtstag, l.kuerzel HAVING COUNT(*)>=3 ORDER BY name, vorname, geburtstag, land ; SELECT DISTINCT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t1 ON s.id = t1.s_id JOIN r_teilnahme t2 ON s.id = t2.s_id AND t1.w_id < t2.w_id JOIN r_teilnahme t3 ON s.id = t3.s_id AND t2.w_id < t3.w_id WHERE t1.platz = 1 AND t2.platz = 1 AND t3.platz = 1 ORDER BY name, vorname, geburtstag, land ; /* ** Aufgabe f ** ** Welche Sportlerin (d.h. welche Schwimmerin) hat ALLE ** Schwimmwettbewerbe der Frauen gewonnen? ** Die Spalte 'medaillenzahl' ist eigentlich unnötig, da nicht gefragt! id name vorname geburtstag land medaillenanzahl -------------------------------------------------------- 1 Völker Sandra 1974-04-01 GER 4 */ -- Die Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt). SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, COUNT(*) AS medaillenanzahl FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id JOIN e_wettkampf w ON t.w_id = w.id JOIN enum_wettkampfart wa ON w.wa_id = wa.id JOIN enum_sportart sa ON wa.sa_id = sa.id WHERE t.platz=1 AND sa.name='Schwimmen' AND w.geschlecht = 'w' GROUP BY s.id, s.name, s.vorname, s.geburtstag, l.kuerzel HAVING COUNT(*) = (SELECT COUNT(*) FROM e_wettkampf w JOIN enum_wettkampfart wa ON w.wa_id = wa.id JOIN enum_sportart sa ON wa.sa_id = sa.id WHERE sa.name='Schwimmen' AND geschlecht = 'w' ) ORDER BY name, vorname, geburtstag, land ; -- Ohne Code-Duplikation mit Hilfe einer temporären View WITH schwimmwettbewerbe_weiblich(id) AS (SELECT w.id FROM e_wettkampf w JOIN enum_wettkampfart wa ON w.wa_id = wa.id JOIN enum_sportart sa ON wa.sa_id = sa.id WHERE sa.name='Schwimmen' AND geschlecht = 'w' ) SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, COUNT(*) AS medaillenanzahl FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id JOIN schwimmwettbewerbe_weiblich w ON w.id = t.w_id WHERE t.platz=1 GROUP BY s.id, s.name, s.vorname, s.geburtstag, l.kuerzel HAVING COUNT(*) = (SELECT COUNT(*) FROM schwimmwettbewerbe_weiblich ) ORDER BY name, vorname, geburtstag, land ; SELECT DISTINCT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id JOIN e_wettkampf w ON t.w_id = w.id JOIN enum_wettkampfart wa ON w.wa_id = wa.id JOIN enum_sportart sa ON wa.sa_id = sa.id WHERE t.platz=1 AND sa.name='Schwimmen' AND w.geschlecht = 'w' AND NOT EXISTS (SELECT w.id FROM e_wettkampf w JOIN enum_wettkampfart wa ON w.wa_id = wa.id JOIN enum_sportart sa ON wa.sa_id = sa.id WHERE t.platz=1 AND sa.name='Schwimmen' AND w.geschlecht = 'w' EXCEPT SELECT t2.w_id FROM r_teilnahme t2 WHERE t2.s_id = s.id AND t2.platz=1 ) ORDER BY name, vorname, geburtstag, land ; /* ** Aufgabe g ** ** Welche Sportler/-innen haben die meisten Gold-Medaillen gewonnen? ** Die Spalte 'medaillenzahl' ist eigentlich unnötig, da nicht gefragt! id name vorname geburtstag land medaillenanzahl -------------------------------------------------------- 1 Völker Sandra 1974-04-01 GER 4 */ -- Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt) SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, COUNT(*) AS medaillenanzahl FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id WHERE t.platz=1 GROUP BY s.id, s.name, s.vorname, s.geburtstag, l.kuerzel HAVING COUNT(*) >=ALL (SELECT COUNT(*) FROM r_teilnahme t WHERE t.platz=1 GROUP BY t.s_id ) ORDER BY name, vorname, geburtstag, land ; -- Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt) SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, COUNT(*) AS medaillenanzahl FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id WHERE t.platz=1 GROUP BY s.id, s.name, s.vorname, s.geburtstag, l.kuerzel HAVING COUNT(*) = (SELECT MAX(medaillenanzahl) FROM (SELECT COUNT(*) FROM r_teilnahme t WHERE t.platz=1 GROUP BY t.s_id ) AS dummy(medaillenanzahl) ) ORDER BY name, vorname, geburtstag, land ; -- Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt) SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, COUNT(*) AS medaillenanzahl FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id WHERE t.platz=1 GROUP BY s.id, s.name, s.vorname, s.geburtstag, l.kuerzel HAVING COUNT(*) = (SELECT MAX(medaillenanzahl) FROM (SELECT COUNT(*) OVER (PARTITION BY s_id) FROM r_teilnahme WHERE platz = 1 ) as dummy(medaillenanzahl) ) ORDER BY name, vorname, geburtstag, land ; -- Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt) SELECT id, name, vorname, geburtstag, land, medaillenanzahl FROM (SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, COUNT(*) AS medaillenanzahl, MAX(COUNT(*)) OVER () AS max FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id WHERE t.platz=1 GROUP BY s.id, s.name, s.vorname, s.geburtstag, l.kuerzel ) AS dummy WHERE medaillenanzahl = max ORDER BY name, vorname, geburtstag, land ; -- Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt) SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, COUNT(*) AS medaillenanzahl FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id WHERE t.platz=1 GROUP BY s.id, s.name, s.vorname, s.geburtstag, l.kuerzel HAVING COUNT(*) IN (SELECT MAX(COUNT(*)) OVER () FROM r_teilnahme WHERE platz=1 GROUP BY s_id ) ORDER BY name, vorname, geburtstag, land ; -- Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt) SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, COUNT(*) AS medaillenanzahl FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id WHERE t.platz=1 GROUP BY s.id, s.name, s.vorname, s.geburtstag, l.kuerzel HAVING COUNT(*) = (SELECT MAX(COUNT(*)) OVER () FROM r_teilnahme WHERE platz=1 GROUP BY s_id FETCH FIRST 1 ROW ONLY ) -- LIMIT 1 ist OK, da alle Elemente der Liste denselben Wert enthalten. -- Evtl. besser =SOME, für den Fall, dass die Menge leer ist. -- Postgres akzeptiert hier aber auch leere Mengen. ORDER BY name, vorname, geburtstag, land ; WITH medaillen(s_id, medaillenanzahl) AS (SELECT s_id, COUNT(*) FROM r_teilnahme WHERE platz=1 GROUP BY s_id ) SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land FROM e_sportler s JOIN enum_land l ON s.l_id = l.id WHERE (SELECT medaillenanzahl FROM medaillen m WHERE m.s_id = s.id) = (SELECT MAX(medaillenanzahl) FROM medaillen) ; -- Medaillenanzahl ist eigentlich unwichtig -- (war nicht gefragt, kann aber wegen LATERAL-JOIN problemlos -- DRY! -- ausgegeben werden) WITH medaillen(s_id, medaillenanzahl) AS (SELECT s_id, COUNT(*) FROM r_teilnahme WHERE platz=1 GROUP BY s_id ) SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, medaillenanzahl FROM e_sportler s JOIN enum_land l ON s.l_id = l.id, LATERAL (SELECT medaillenanzahl FROM medaillen m WHERE m.s_id = s.id) sm WHERE sm.medaillenanzahl = (SELECT MAX(medaillenanzahl) FROM medaillen) ; /* Schmutziger Trick! Funktioniert nur, wenn es genau einen Teilnehmer gibt, der die meisten Medaillen gewonnen hat. FETCH ... ONLY ist ein Hilfsmittel, um die Anzahl der Ausgaben (z.B. für Web-Seiten) zu begrenzen. Für die Minimums- und Maximumssuche ist diese Klausel denkbar ungeeignet: - Es wird nur ein Minimum/Maximum gefunden, auch wenn es mehrere gibt. - Die Sortieroperation ist sehr teuer (O(n*log(n))). Das Minimum/Maximum kann viel schneller bestimmt werden (O(n)). */ -- Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt) SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, COUNT(*) AS medaillenanzahl FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id WHERE t.platz=1 GROUP BY s.id, s.name, s.vorname, s.geburtstag, l.kuerzel ORDER BY medaillenanzahl DESC, name, vorname, geburtstag, land FETCH FIRST 1 ROW ONLY ;