/*
** Olympiade (Teil 1)
**
** Beachten Sie, dass Sie stets richtige Duplikate, aber keine
** überflüssigen Duplikate ausgeben (DISTINCT).
*/

-- ACHTUNG: 
--   Hier finden Sie für ausgewählte Aufgaben zusätzliche 
--   Lösungsvorschlaege. Diese basieren auf den Views, die
--   im Modell olympia3_uml definiert wurden, um JOINS 
--   zwischen den Tabellen "sportler", "teilnahme" und 
--   "wettkampf" nicht mehr explizit formulieren zu müssen.
--
--   Alle Musterlösungen, die Sie in den Dateien
--     olymia1_aufgabe_1_loesungen.sql
--   sowie
--     olymia2_aufgabe_1_loesungen.sql
--   vorfinden, funktionieren auch für das dritte Modell.   


/*
** Aufgabe f
**
** Welche Sportler nehmen/nahmen an welchen Schwimm-Wettbewerben 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 AS id, name, vorname, land, geburtstag, wettkampfart, s_geschlecht AS geschlecht
FROM     sportler_teilnahme_wettkampf
WHERE    sportart = 'Schwimmen' 
ORDER BY name, vorname, land, geburtstag, wettkampfart, geschlecht
;

/*
** Aufgabe g
**
** An welchen Wettbewerben nimmt Antje Buschschulte teil?
** Achtung: Das Land und das Geburtsdatum muessen 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   land, geburtstag, sportart, wettkampfart, s_geschlecht AS geschlecht
FROM     sportler_teilnahme_wettkampf
WHERE    vorname = 'Antje' AND name='Buschschulte'
ORDER BY land, geburtstag, sportart, wettkampfart, geschlecht
;

SELECT   land, geburtstag, sportart, wettkampfart, s_geschlecht AS geschlecht
FROM     sportler_teilnahme_wettkampf
WHERE    vorname = 'Josef' AND name='Maier'
ORDER BY land, geburtstag, sportart, wettkampfart, geschlecht
;


-- Oder man sucht gezielt nach einer speziellen Antje Buschschulte:
SELECT   land, geburtstag, sportart, wettkampfart, s_geschlecht AS geschlecht
FROM     sportler_teilnahme_wettkampf
WHERE    vorname = 'Antje' AND name='Buschschulte' AND
         land='GER'        AND geburtstag = DATE '1978-12-27'
ORDER BY land, geburtstag, sportart, wettkampfart, geschlecht
;

SELECT   land, geburtstag, sportart, wettkampfart, s_geschlecht AS geschlecht
FROM     sportler_teilnahme_wettkampf
WHERE    vorname = 'Josef' AND name='Maier' AND
         land='GER'        AND geburtstag = DATE '1980-01-01'
ORDER BY land, geburtstag, 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   beginn - INTERVAL '1 hour' AS ankunft, sportstaette
FROM     sportler_teilnahme_wettkampf
WHERE    vorname = 'Antje' AND name='Buschschulte' AND
         land='GER'        AND geburtstag = DATE '1978-12-27'
ORDER BY beginn
;