/* ** 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 platz, s_id AS id, name, vorname, geburtstag, land FROM sportler_teilnahme_wettkampf WHERE sportart ='Schwimmen' AND wettkampfart = 'Brust 100m' AND w_geschlecht='w' AND 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 ist wichtig SELECT DISTINCT s_id AS id, name, vorname, geburtstag, land, sportart FROM sportler_alle_teilnahme_wettkampf ORDER BY name, vorname, geburtstag, land ; -- -- Mit Listen-Aggregation -- -- SELECT s_id AS id, name, vorname, geburtstag, land, array_agg(sportart) AS sportarten FROM sportler_alle_teilnahme_wettkampf GROUP BY s_id, name, vorname, geburtstag, land 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 id, name, vorname, geburtstag, land, platz, COUNT(*) AS anzahl FROM sportler_alle_teilnahme GROUP BY id, name, vorname, geburtstag, land, platz ORDER BY name, vorname, geburtstag, land ; /* Antje Buschschulte (genau eine) */ SELECT id, name, vorname, geburtstag, land, platz, COUNT(*) AS anzahl FROM sportler_alle_teilnahme WHERE name='Buschschulte' AND vorname='Antje' AND land='GER' AND geburtstag = DATE '1978-12-27' GROUP BY id, name, vorname, geburtstag, land, platz ; /* Antje Buschschulte (eventuell mehrere) */ SELECT id, name, vorname, geburtstag, land, platz, COUNT(*) AS anzahl FROM sportler_alle_teilnahme WHERE name='Buschschulte' AND vorname='Antje' GROUP BY id, name, vorname, geburtstag, land, platz ORDER BY geburtstag, land ; -- etwas komplizierter Variante, aber dieselbe Idee SELECT id, name, vorname, geburtstag, land, platz, COUNT(*) AS anzahl FROM (SELECT id, name, vorname, geburtstag, land, platz FROM sportler_alle_teilnahme WHERE name='Buschschulte' AND vorname='Antje' ) AS BuschschultePlaetze GROUP BY id, name, vorname, 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 id, name, vorname, geburtstag, land, COUNT(*) AS medaillenanzahl FROM sportler_teilnahme WHERE platz BETWEEN 1 AND 3 GROUP BY id, name, vorname, geburtstag, land HAVING COUNT(*)>1 ORDER BY name, vorname, geburtstag, land ; -- Nur einfache Projektion, Inner Join und einfache Selektion. -- Keine Aggregation oder anderen komplexen Konstrukte. SELECT DISTINCT st.id, st.name, st.vorname, st.geburtstag, st.land FROM sportler_teilnahme st JOIN teilnahme t ON st.id = t.s_id AND st.w_id < t.w_id WHERE st.platz BETWEEN 1 AND 3 AND t.platz BETWEEN 1 AND 3 ORDER BY name, vorname, geburtstag, land ; -- <> an Stelle von < funktioniert auch SELECT DISTINCT st.id, st.name, st.vorname, st.geburtstag, st.land FROM sportler_teilnahme st JOIN teilnahme t ON st.id = t.s_id AND st.w_id <> t.w_id WHERE st.platz BETWEEN 1 AND 3 AND t.platz BETWEEN 1 AND 3 ORDER BY name, vorname, geburtstag, land ; -- Allerdings werden bei st.w_id <> t.w_id doppelt so viele Tupel -- berechnet (Duplikate!) wie bei st.w_id < t.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 id, name, vorname, geburtstag, land, COUNT(*) AS medaillenanzahl FROM sportler_teilnahme WHERE platz=1 GROUP BY id, name, vorname, geburtstag, land HAVING COUNT(*)>=3 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 AS id, name, vorname, geburtstag, land, COUNT(*) AS medaillenanzahl FROM sportler_teilnahme_wettkampf WHERE platz=1 AND sportart='Schwimmen' AND w_geschlecht = 'w' GROUP BY s_id, name, vorname, geburtstag, land HAVING COUNT(*) = (SELECT COUNT(*) FROM wettkampf WHERE sportart='Schwimmen' AND geschlecht = 'w' ) ORDER BY name, vorname, geburtstag, land ; SELECT DISTINCT s_id AS id, name, vorname, geburtstag, land FROM sportler_teilnahme_wettkampf stw WHERE platz=1 AND sportart='Schwimmen' AND w_geschlecht = 'w' AND NOT EXISTS (SELECT id FROM wettkampf WHERE sportart='Schwimmen' AND w_geschlecht = 'w' EXCEPT SELECT t.w_id FROM teilnahme t WHERE stw.s_id = t.s_id AND t.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 AS id, name, vorname, geburtstag, land, COUNT(*) AS medaillenanzahl FROM sportler_teilnahme_wettkampf WHERE platz=1 AND sportart='Schwimmen' AND w_geschlecht = 'w' GROUP BY s_id, name, vorname, geburtstag, land HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM teilnahme WHERE platz=1 GROUP BY s_id ) ORDER BY name, vorname, geburtstag, land ; -- Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt) SELECT s_id AS id, name, vorname, geburtstag, land, COUNT(*) AS medaillenanzahl FROM sportler_teilnahme_wettkampf WHERE platz=1 AND sportart='Schwimmen' AND w_geschlecht = 'w' GROUP BY s_id, name, vorname, geburtstag, land HAVING COUNT(*) = (SELECT MAX(medaillenanzahl) FROM (SELECT COUNT(*) FROM teilnahme WHERE platz=1 GROUP BY s_id ) AS dummy(medaillenanzahl) ) ORDER BY name, vorname, geburtstag, land ; -- Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt) SELECT s_id AS id, name, vorname, geburtstag, land, COUNT(*) AS medaillenanzahl FROM sportler_teilnahme_wettkampf WHERE platz=1 AND sportart='Schwimmen' AND w_geschlecht = 'w' GROUP BY s_id, name, vorname, geburtstag, land HAVING COUNT(*) = (SELECT MAX(medaillenanzahl) FROM (SELECT COUNT(*) OVER (PARTITION BY s_id) FROM 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 id, name, vorname, geburtstag, land, COUNT(*) AS medaillenanzahl, MAX(COUNT(*)) OVER () AS max FROM sportler_teilnahme WHERE platz=1 GROUP BY id, name, vorname, geburtstag, land ) AS dummy WHERE medaillenanzahl = max ORDER BY name, vorname, geburtstag, land ; -- Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt) SELECT id, name, vorname, geburtstag, land, COUNT(*) AS medaillenanzahl FROM sportler_teilnahme WHERE platz=1 GROUP BY id, name, vorname, geburtstag, land HAVING COUNT(*) IN (SELECT MAX(COUNT(*)) OVER () FROM teilnahme WHERE platz=1 GROUP BY s_id ) ORDER BY name, vorname, geburtstag, land ; -- Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt) SELECT id, name, vorname, geburtstag, land, COUNT(*) AS medaillenanzahl FROM sportler_teilnahme WHERE platz=1 GROUP BY id, name, vorname, geburtstag, land HAVING COUNT(*) = (SELECT MAX(COUNT(*)) OVER () FROM 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 ; /* 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 id, name, vorname, geburtstag, land, COUNT(*) AS medaillenanzahl FROM sportler_teilnahme WHERE platz=1 GROUP BY id, name, vorname, geburtstag, land ORDER BY medaillenanzahl DESC, name, vorname, geburtstag, land FETCH FIRST 1 ROW ONLY ;