/* ** 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 10 Boenisch Yvonne GER 4 Buschschulte Antje GER 12 Cavazzuti Cinzia ITA 18 Dede NULL GER 9 Fedotova Irina RUS 5 Filippi Alessia ITA 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 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 Yvonne Boenisch 29. 12. 1980 */ SELECT s.vorname || ' ' || s.name AS name, TO_CHAR(s.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 s.vorname || ' ' || name 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 ; -- Besser: 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. 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? 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 dummy /* enthält stets genau ein Tupel mit einem Attribut */ ; -- PostgreSQL SELECT 17*23 AS ergebnis ;