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

/*
** Aufgabe a
**
** Wo finden die Schwimmwettbewerbe statt?

sportstaette
--------------
Schwimmhalle 1
Schwimmhalle 2
*/

-- "DISTINCT" ist wichtig.
SELECT DISTINCT w.sportstaette
FROM            wettkampf w
WHERE           w.sportart = 'Schwimmen'
ORDER BY        sportstaette  -- nicht w.sportstaette!
;

-- Kurzform
SELECT DISTINCT sportstaette
FROM            wettkampf
WHERE           sportart = 'Schwimmen'
ORDER BY        sportstaette
;



/*
** Aufgabe b 
**
** Geben Sie die Namen, Vornamen und Länder aller Sportler aus.
** Wie sorgen Sie dafür, dass 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
*/

-- Josef Maier können nicht unterschieden werden.
SELECT   name, vorname, land
FROM     sportler
ORDER BY name, vorname, land
;

-- Primärschlüssel mit ausgeben
SELECT   id, name, vorname, land
FROM     sportler
ORDER BY name, vorname, land, id
;

-- Vollständigen Schlüsselkandidaten ausgeben
SELECT   name, vorname, land, geburtstag
FROM     sportler
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(vorname || ' ' || name, name) AS name,
         TO_CHAR(geburtstag, 'DD. MM. YYYY') AS geburtstag
FROM     sportler
WHERE    land = 'GER'
ORDER BY name, geburtstag
;

SELECT   COALESCE(vorname || ' ' || name, name) AS name,
         TO_CHAR(geburtstag, 'DD. TMMonth YYYY') AS geburtstag
FROM     sportler
WHERE    land = 'GER'
ORDER BY name, geburtstag
;

-- Martin Kohnle
SELECT   CONCAT(s_name, ' ', s_vorname) AS name, 
         TO_CHAR(s_geburtstag, 'dd.mm.yyyy') as Geburtsdatum 
FROM     sportler
WHERE    s_land = '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   wettkampfart, geschlecht
FROM     wettkampf
WHERE    sportart = 'Schwimmen'
ORDER BY wettkampfart, geschlecht
;

/*
** Aufgabe e
**
** Wo finden die Brustschwimm-Wettbewerbe statt?

sportstaette
--------------
Schwimmhalle 1
Schwimmhalle 2
*/

-- DISTINCT ist wichtig
SELECT DISTINCT  sportstaette
FROM             wettkampf
WHERE            sportart = 'Schwimmen' AND 
                 wettkampfart LIKE 'Brust %'
ORDER BY         sportstaette
;

SELECT DISTINCT  sportstaette
FROM             wettkampf
WHERE            sportart = 'Schwimmen' AND 
                 wettkampfart IN ('Brust 100m', 'Brust 200m')
ORDER BY         sportstaette
;

SELECT DISTINCT  sportstaette
FROM             wettkampf
WHERE            sportart = 'Schwimmen' AND 
                 (wettkampfart = 'Brust 100m' OR wettkampfart = 'Brust 200m')
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, s.land, s.geburtstag, w.wettkampfart, w.geschlecht
FROM          sportler  s
         JOIN teilnahme t ON s.id = t.s_id
         JOIN wettkampf w ON t.w_id = w.id
WHERE    w.sportart = 'Schwimmen' 
ORDER BY name, vorname, land, geburtstag, wettkampfart, geschlecht
;

SELECT   s.id, s.name, s.vorname, s.land, s.geburtstag, w.wettkampfart, w.geschlecht
FROM     sportler s, teilnahme t, wettkampf w
WHERE    s.id = t.s_id AND t.w_id = w.id AND
         w.sportart = 'Schwimmen' 
ORDER BY name, vorname, land, geburtstag, wettkampfart, geschlecht
;

-- Es ist nicht genau spzifiziert, ob Sportler, die disqaulifiziert 
-- wurden, als Teilnehmer gelten. Sie sind zwar gemeldet, wurden aber
-- vor oder während des Rennens von der Teilnahme ausgeschlossen.
-- Daher ist auch folgende Lösung korrekt: 

SELECT   s.id, s.name, s.vorname, s.land, s.geburtstag, w.wettkampfart, w.geschlecht
FROM          sportler  s
         JOIN teilnahme t ON s.id = t.s_id
         JOIN wettkampf w ON t.w_id = w.id
WHERE    w.sportart = 'Schwimmen'  AND disqualifiziert IS FALSE  -- nicht ... = FALSE!!!
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   s.land, s.geburtstag, w.wettkampfart, w.sportart, w.geschlecht
FROM          sportler  s
         JOIN teilnahme t ON s.id = t.s_id
         JOIN wettkampf w ON t.w_id = w.id
WHERE    s.vorname = 'Antje' AND s.name='Buschschulte'
ORDER BY land, geburtstag, sportart, wettkampfart, geschlecht
;

-- Zum Vergleich: Es gibt zwei Josef Maier.
SELECT   s.land, s.geburtstag, w.wettkampfart, w.sportart, w.geschlecht
FROM          sportler  s
         JOIN teilnahme t ON s.id = t.s_id
         JOIN wettkampf w ON t.w_id = w.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   w.wettkampfart, w.sportart, w.geschlecht
FROM          sportler  s
         JOIN teilnahme t ON s.id = t.s_id
         JOIN wettkampf w ON t.w_id = w.id
WHERE        s.vorname = 'Antje' 
         AND s.name='Buschschulte'
         AND s.land='GER'        
         AND s.geburtstag = DATE '1978-12-27'
ORDER BY sportart, wettkampfart, geschlecht
;

-- Beziehungsweise nach einem bestimmten Josef Maier:
SELECT   w.wettkampfart, w.sportart, w.geschlecht
FROM          sportler  s
         JOIN teilnahme t ON s.id = t.s_id
         JOIN wettkampf w ON t.w_id = w.id
WHERE        s.vorname = 'Josef' 
         AND s.name='Maier'
         AND s.land='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, w.sportstaette
FROM          sportler  s
         JOIN teilnahme t ON s.id = t.s_id
         JOIN wettkampf w ON t.w_id = w.id
WHERE        s.vorname = 'Antje' AND s.name='Buschschulte'
         AND s.land='GER' AND s.geburtstag = DATE '1978-12-27'
ORDER BY ankunft
;

/*
** Aufgabe i
**
** Wie viel ist 17*23?

ergebnis
--------
391
*/

-- Standard-SQL
SELECT 17*23 AS ergebnis
FROM   (VALUES (1)) as one /* enthält stets genau ein Tupel mit einem Attribut */
;

-- PostgreSQL
SELECT 17*23 AS ergebnis
;