/* ** Olympiade (Teil 1) ** ** Beachten Sie, dass Sie stets richtige Duplikate, aber keine ** ueberflüssigen Duplikate ausgeben (DISTINCT). */ /* ** Aufgabe a ** ** Wo finden die Schwimmwettbewerbe statt? sportstaette -------------- Schwimmhalle 1 Schwimmhalle 2 */ -- "DISTINCT" ist wichtig. SELECT DISTINCT spst.name AS sportstaette FROM e_wettkampf w JOIN enum_sportstaette spst ON w.spst_id = spst.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' ORDER BY sportstaette ; /* ** Aufgabe b ** ** Geben Sie die Namen, Vornamen und Länder aller Sportler aus. ** Wie sorgen Sie dafür, dass Sie zwei Sportler, die gleich ** heißen und aus demselben Land kommen, unterschieden werden ** können? (Es gibt zwei Möglichkeiten, dies zu erreichen.) id name vorname land ------------------------------------------- 6 Angelopoulou Vasiliki GRE 20 Becker Boris GER 10 Boenisch Yvonne GER 4 Buschschulte Antje GER 12 Cavazzuti Cinzia ITA 18 Dede NULL GER 9 Fedotova Irina RUS 5 Filippi Alessia ITA 19 Graf Steffi GER 13 Harel Barbara FRA 3 Harstick Sara GER 8 Karsten Ekaterina BLR 16 Maier Josef GER 17 Maier Josef GER 7 Rutschow-Stomporowski Katrin GER 15 Tuuxaru Iliisquix CUB 2 van Almsick Franziska GER 1 Völker Sandra GER 14 Xeteylu Leidisyuri CUB 11 Yukhareva Natalya RUS */ -- Primärschlüssel mit ausgeben SELECT s.id, s.name, s.vorname, l.kuerzel AS land FROM e_sportler s JOIN enum_land l ON s.l_id = l.id ORDER BY name, vorname, land, id ; -- Vollständigen Schlüsselkandidaten ausgeben SELECT s.name, s.vorname, l.kuerzel AS land, s.geburtstag FROM e_sportler s JOIN enum_land l ON s.l_id = l.id ORDER BY name, vorname, land, geburtstag ; /* ** Aufgabe c ** ** Geben Sie die Namen (Vorname + Nachname als ein String!) ** und Geburtsdaten (formatiert!) aller deutschen Sportler aus. name geburtstag ------------------------------------------ Antje Buschschulte 27. 12. 1978 Boris Becker 22. 11. 1967 Dede 01. 01. 1970 Franziska van Almsick 05. 04. 1978 Josef Maier 01. 01. 1980 Josef Maier 01. 01. 1982 Katrin Rutschow-Stomporowski 02. 04. 1975 Sandra Völker 01. 04. 1974 Sara Harstick 01. 01. 1981 Steffi Graf 14. 06. 1969 Yvonne Boenisch 29. 12. 1980 */ SELECT COALESCE(s.vorname || ' ' || s.name, s.name/*, s.vorname*/) AS name, TO_CHAR(geburtstag, 'DD. MM. YYYY') AS geburtstag FROM e_sportler s JOIN enum_land l ON s.l_id = l.id WHERE l.kuerzel = 'GER' ORDER BY name, geburtstag ; SELECT COALESCE(s.vorname || ' ' || s.name, s.name/*, s.vorname*/) AS name, TO_CHAR(s.geburtstag, 'DD. TMMonth YYYY') AS geburtstag FROM e_sportler s JOIN enum_land l ON s.l_id = l.id WHERE l.kuerzel = 'GER' ORDER BY name, geburtstag ; /* ** Aufgabe d ** ** Welche Schwimmwettbewerbe gibt es? wettkampfart geschlecht ------------------------------ Brust 100m m Brust 100m w Brust 200m m Brust 200m w Lagen 400m w Schmetterling 100m w */ SELECT wa.name AS wettkampfart, w.geschlecht 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' ORDER BY wettkampfart, geschlecht ; /* ** Aufgabe e ** ** Wo finden die Brustschwimm-Wettbewerbe statt? sportstaette -------------- Schwimmhalle 1 Schwimmhalle 2 */ -- DISTINCT ist wichtig SELECT DISTINCT spst.name AS sportstaette FROM e_wettkampf w JOIN enum_sportstaette spst ON w.spst_id = spst.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 LIKE 'Brust %' ORDER BY sportstaette ; /* ** Aufgabe f ** ** Welche Sportler nehmen/nahmen an welchen Schwimmwettbewerben teil? id name vorname land geburtstag wettkampfart geschlecht ------------------------------------------------------------------------------ 6 Angelopoulou Vasiliki GRE 1987-05-30 Brust 100m w 4 Buschschulte Antje GER 1978-12-27 Brust 100m w 4 Buschschulte Antje GER 1978-12-27 Lagen 400m w 4 Buschschulte Antje GER 1978-12-27 Schmetterling 100m w 5 Filippi Alessia ITA 1987-06-23 Brust 100m w 5 Filippi Alessia ITA 1987-06-23 Lagen 400m w 13 Harel Barbara FRA 1970-01-01 Brust 100m w 13 Harel Barbara FRA 1970-01-01 Schmetterling 100m w 3 Harstick Sara GER 1981-01-01 Lagen 400m w 8 Karsten Ekaterina BLR 1972-06-02 Brust 100m w 17 Maier Josef GER 1982-01-01 Brust 100m m 15 Tuuxaru Iliisquix CUB 1970-01-01 Brust 100m m 2 van Almsick Franziska GER 1978-04-05 Brust 100m w 2 van Almsick Franziska GER 1978-04-05 Lagen 400m w 1 Völker Sandra GER 1974-04-01 Brust 100m w 1 Völker Sandra GER 1974-04-01 Brust 200m w 1 Völker Sandra GER 1974-04-01 Lagen 400m w 1 Völker Sandra GER 1974-04-01 Schmetterling 100m w 14 Xeteylu Leidisyuri CUB 1970-01-01 Brust 100m w 11 Yukhareva Natalya RUS 1970-01-01 Brust 100m w */ SELECT s_id, s.name, s.vorname, l.kuerzel AS land, s.geburtstag, wa.name AS wettkampfart, w.geschlecht 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_sportstaette spst ON w.spst_id = spst.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' ORDER BY name, vorname, land, geburtstag, wettkampfart, geschlecht ; /* ** Aufgabe g ** ** An welchen Wettbewerben nimmt Antje Buschschulte teil? ** Achtung: Das Land und das Geburtsdatum müssen mit ausgegeben ** werden. Es könnte mehrere Antje Buschschultes geben. ** Überprüfen Sie, was passiert, wenn Sie nach Josef Maier ** an Stelle von Antje Buschschulte suchen. land geburtstag wettkampfart sportart geschlecht ----------------------------------------------------------- GER 1978-12-27 Brust 100m Schwimmen w GER 1978-12-27 Lagen 400m Schwimmen w GER 1978-12-27 Schmetterling 100m Schwimmen w */ SELECT l.kuerzel AS land, s.geburtstag, wa.name AS wettkampfart, sa.name AS sportart, w.geschlecht 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_sportstaette spst ON w.spst_id = spst.id JOIN enum_wettkampfart wa ON w.wa_id = wa.id JOIN enum_sportart sa ON wa.sa_id = sa.id WHERE s.vorname = 'Antje' AND s.name='Buschschulte' ORDER BY land, geburtstag, sportart, wettkampfart, geschlecht ; SELECT l.kuerzel AS land, s.geburtstag, wa.name AS wettkampfart, sa.name AS sportart, w.geschlecht 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_sportstaette spst ON w.spst_id = spst.id JOIN enum_wettkampfart wa ON w.wa_id = wa.id JOIN enum_sportart sa ON wa.sa_id = sa.id WHERE s.vorname = 'Josef' AND s.name='Maier' ORDER BY land, geburtstag, sportart, wettkampfart, geschlecht ; -- Oder man sucht gezielt nach einer speziellen Antje Buschschulte: SELECT wa.name AS wettkampfart, sa.name AS sportart, w.geschlecht 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_sportstaette spst ON w.spst_id = spst.id JOIN enum_wettkampfart wa ON w.wa_id = wa.id JOIN enum_sportart sa ON wa.sa_id = sa.id WHERE s.vorname = 'Antje' AND s.name='Buschschulte' AND l.kuerzel = 'GER' AND s.geburtstag = DATE '1978-12-27' ORDER BY sportart, wettkampfart, geschlecht ; SELECT wa.name AS wettkampfart, sa.name AS sportart, w.geschlecht 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_sportstaette spst ON w.spst_id = spst.id JOIN enum_wettkampfart wa ON w.wa_id = wa.id JOIN enum_sportart sa ON wa.sa_id = sa.id WHERE s.vorname = 'Josef' AND s.name='Maier' AND l.kuerzel = 'GER' AND s.geburtstag = DATE '1980-01-01' ORDER BY sportart, wettkampfart, geschlecht ; /* ** Aufgabe h ** ** Wo muss Antje Buschschulte (aus Deutschland, geb. am 27. 12. 1978) ** um wie viel Uhr sein, wenn Sie jeweils rechtzeitig, d.h. eine ** Stunde vor Wettkampf-Beginn, bei ihren Wettbewerben sein will? ** Überprüfen Sie, was passiert, wenn Sie nach Josef Maier ** an Stelle von Antje Buschschulte suchen. ankunft sportstaette ----------------------------------- 2004-08-14 16:30:00 Schwimmhalle 1 2004-08-15 14:30:00 Schwimmhalle 1 2004-08-16 11:15:00 Schwimmhalle 2 */ SELECT w.beginn - INTERVAL '1 hour' AS ankunft, spst.name AS sportstaette 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_sportstaette spst ON w.spst_id = spst.id WHERE s.vorname = 'Antje' AND s.name='Buschschulte' AND l.kuerzel = 'GER' AND s.geburtstag = DATE '1978-12-27' ORDER BY beginn ; /* ** Aufgabe i ** ** Wie viel ist 17*23? ergebnis -------- 391 */ -- Standard-SQL SELECT 17*23 AS ergebnis FROM one /* enthält stets genau ein Tupel mit einem Attribut */ ; -- PostgreSQL SELECT 17*23 AS ergebnis ;