/*
** Olympiade (Teil 1)
**
** Beachten Sie, dass Sie stets richtige Duplikate, aber keine
** ueberfluessigen Duplikate ausgeben.
*/

/*
** Aufgabe a 
**
** Wo finden die Schwimm-Wettbewerbe statt?
*/ 

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

/*
** Aufgabe b 
**
** Geben Sie die Namen, Vornamen und Laender aller Sportler aus.
** Wie sorgen Sie dafuer, dass Sie zwei Sportler, die gleich heissen und
** aus demselben Land kommen, unterschieden werden koennen?
*/

-- Primaerschluessel mit ausgeben
SELECT   s_id, name, vorname, land
FROM     sportler
ORDER BY name, vorname, land, s_id
;

-- Vollstaendigen Schluesselkandidaten 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.
*/

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

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

-- Besser:

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
;


/*
** Aufgabe d
**
** Welche Schwimm-Wettbewerbe gibt es?
*/

SELECT   wettkampfart, geschlecht
FROM     wettkampf
WHERE    sportart = 'Schwimmen'
ORDER BY wettkampfart, geschlecht
;

/*
** Aufgabe e
**
** Wo finden die Brust-Schwimm-Wettbewerbe statt?
*/

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

/*
** Aufgabe e
**
** Welche Sportler nehmen an welchen Schwimm-Wettbewerben teil?
*/

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

/*
** Aufgabe f
**
** An welchen Wettbewerben nimmt Antje Buschschulte teil?
*/

-- Achtung: Das Land und das Geburtsdatum muessen mit ausgegeben werden.
-- Es koennte mehrere Antje Buschschultes geben.
SELECT   land, geburtstag, wettkampfart, sportart, w.geschlecht
FROM     sportler s, teilnahme t, wettkampf w
WHERE        s.s_id = t.s_id AND t.w_id = w.w_id
         AND vorname = 'Antje' AND name='Buschschulte'
ORDER BY land, geburtstag, sportart, wettkampfart, geschlecht
;

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

-- Oder man sucht gezielt nach einer speziellen Antje Buschschulte:
SELECT   wettkampfart, sportart, w.geschlecht
FROM     sportler s, teilnahme t, wettkampf w
WHERE        s.s_id = t.s_id AND t.w_id = w.w_id
         AND vorname = 'Antje' AND name='Buschschulte'
         AND land='GER' AND geburtstag = DATE '1978-12-27'
ORDER BY sportart, wettkampfart, geschlecht
;

SELECT   wettkampfart, sportart, w.geschlecht
FROM     sportler s, teilnahme t, wettkampf w
WHERE        s.s_id = t.s_id AND t.w_id = w.w_id
         AND vorname = 'Josef' AND name='Maier'
         AND land='GER' AND geburtstag = DATE '1980-01-01'
ORDER BY sportart, wettkampfart, geschlecht
;

/*
** Aufgabe g
**
** 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?
*/

SELECT   beginn - INTERVAL '1 hour' AS ankunft, sportstaette
FROM     sportler s, teilnahme t, wettkampf w
WHERE        s.s_id = t.s_id AND t.w_id = w.w_id
         AND vorname = 'Antje' AND name='Buschschulte'
         AND land='GER' AND geburtstag = DATE '1978-12-27'
ORDER BY beginn
;

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

DROP TABLE IF EXISTS dummy;
CREATE TABLE dummy
(x INTEGER PRIMARY KEY);

INSERT INTO dummy VALUES (1);

-- Standard-SQL
SELECT 17*23 AS ergebnis
FROM   dummy
;

-- PostgreSQL
SELECT 17*23 AS ergebnis
;
