/*
** Olympiade (Teil 2)
*/

/*
** Aufgabe a
**
** Welche Teilnehmerländer haben sich eines Dopingvergehens schuldig gemacht? 

land
----
CUB
*/


SELECT DISTINCT s.land
FROM            sportler s JOIN teilnahme t ON s.id = t.s_id
WHERE           t.doping IS TRUE
;

SELECT DISTINCT s.land
FROM            sportler s, teilnahme t
WHERE           s.id = t.s_id AND t.doping IS TRUE
;


/*
** Aufgabe b
**
**  Welche Sportlerinnen ('w') sind disqualifiziert worden?

name     vorname     geburtstag  land
-------------------------------------
Xeteylu  Leidisyuri  1970-01-01  CUB
*/

SELECT DISTINCT s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s JOIN teilnahme t ON s.id = t.s_id
WHERE           t.disqualifiziert IS TRUE AND s.geschlecht = 'w'
;

SELECT DISTINCT s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s, teilnahme t
WHERE               s.id = t.s_id
                AND t.disqualifiziert IS TRUE 
                AND s.geschlecht = 'w'
;

/*
** Aufgabe c 
**
** Welche Sportler ('w' und 'm') wurden noch nicht in allen 
** Wettkämpfen, in denen sie gemeldet sind, auf Doping untersucht?

id  name         vorname    geburtstag  land
----------------------------------------------
3   Harstick     Sara       1981-01-01  GER
2   van Almsick  Franziska  1978-04-05  GER 
*/

SELECT DISTINCT s.id, s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s JOIN teilnahme t ON s.id = t.s_id
WHERE           doping IS NULL
;

SELECT DISTINCT s.id, s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s, teilnahme t
WHERE           s.id = t.s_id AND t.doping IS NULL
;

-- Alle Sportler, für die Wettkämpfe existieren, an denen sie teilnehmen,
-- aber in denen sie noch nicht getestet wurden.
SELECT id, name, vorname, geburtstag, land
FROM   sportler s
WHERE  EXISTS (SELECT *
               FROM   teilnahme t
               WHERE  s.id=t.s_id AND t.doping IS NULL
              )
;

/*
** Aufgabe c Reloaded
**
** Wie sieht das Ergebnis von Aufgabe c aus, wenn auch Sportler 
** berücksichtigt werden sollen, die noch für keinen Wettkampf 
** gemeldet worden sind?
** 
** Hier gilt: Sie sind nirgendwo angetreten, also wurden
**            sie in jeder Sportart, in der sie angetreten sind,
**            auch getestet. 
**
** Es gilt allerdings auch:
**            Sie sind nirgendwo angetreten, also wurden
**            sie in jeder Sportart, in der sie angetreten sind,
**            auch NICHT getestet.
 
id  name                   vorname    geburtstag  land
------------------------------------------------------
3   Harstick               Sara       1981-01-01  GER
2   van Almsick            Franziska  1978-04-05  GER
7   Rutschow-Stomporowski  Katrin     1975-04-02  GER
9   Fedotova               Irina      1970-01-01  RUS
10  Boenisch               Yvonne     1980-12-29  GER
12  Cavazzuti              Cinzia     1970-01-01  ITA
18  Dede                   NULL       1970-01-01  GER

Profis geben auch noch die Anzahl der Wettkämpfe aus, für
die jeder dieser Sportler gemeldet ist. 
*/

SELECT DISTINCT s.id, s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s LEFT JOIN teilnahme t ON s.id = t.s_id
WHERE           doping IS NULL
;

-- mit Ausgabe der Anzahl
SELECT   s.id, s.name, s.vorname, s.geburtstag, s.land, COUNT(w_id) AS anzahl_wettkaempfe
FROM     sportler s LEFT JOIN teilnahme t ON s.id = t.s_id
WHERE    doping IS NULL
GROUP BY id, name, vorname, geburtstag, land
;


/*
** Aufgabe d1
**
** Welche Sportler ('w' und 'm') sind disqualifiziert,
** aber nicht gedopt worden?

name     vorname     geburtstag  land
-------------------------------------
Maier    Josef       1982-01-01  GER
*/

SELECT DISTINCT s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s JOIN teilnahme t ON s.id = t.s_id
WHERE           t.doping IS NOT TRUE AND t.disqualifiziert IS TRUE
             -- t.doping IS FALSE  reicht nicht!!!
;

/*
** Aufgabe d2
**
** Welche Sportler ('w' und 'm') sind gedopt,
** aber nicht disqualifiziert worden?
**
** Das Ergebnis muss leer sein! Warum?
*/

-- Grund: Integritätsbedingung gedopt => disqualifiziert
 
SELECT DISTINCT s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s JOIN teilnahme t ON s.id = t.s_id
WHERE           t.doping IS TRUE AND t.disqualifiziert IS FALSE
;

SELECT DISTINCT s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s JOIN teilnahme t ON s.id = t.s_id
WHERE           t.doping IS TRUE AND t.disqualifiziert IS NOT TRUE
;

-- Es gilt: t.disqualifiziert IS FALSE  == t.disqualifiziert IS NOT TRUE
--          (t.disqualifiziert nicht NULL sein darf)   
SELECT DISTINCT s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s JOIN teilnahme t ON s.id = t.s_id
WHERE           t.doping IS TRUE AND t.disqualifiziert IS NOT TRUE 
;


-- Einzelabfragen
SELECT DISTINCT s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s JOIN teilnahme t ON s.id = t.s_id
WHERE           t.disqualifiziert IS TRUE 
;

SELECT DISTINCT s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s JOIN teilnahme t ON s.id = t.s_id
WHERE           t.doping IS TRUE 
;


/*
** Aufgabe e
**
** Welche Sportler ('w' und 'm') wurden noch 
** überhaupt keinem Doping-Test unterzogen?

name                   vorname  geburtstag  land
------------------------------------------------
Boenisch               Yvonne   1980-12-29  GER
Cavazzuti              Cinzia   1970-01-01  ITA
Dede                   NULL     1970-01-01  GER
Fedotova               Irina    1970-01-01  RUS
Harstick               Sara     1981-01-01  GER
Rutschow-Stomporowski  Katrin   1975-04-02  GER
*/
               
SELECT   s.name, s.vorname, s.geburtstag, s.land
FROM     sportler s
EXCEPT
SELECT   s.name, s.vorname, s.geburtstag, s.land
FROM     sportler s JOIN teilnahme t ON s.id = t.s_id
WHERE    t.doping IS NOT UNKNOWN
ORDER BY name, vorname, geburtstag, land
;

SELECT   s.name, s.vorname, s.geburtstag, s.land
FROM     sportler s
EXCEPT
SELECT   s.name, s.vorname, s.geburtstag, s.land
FROM     sportler s JOIN teilnahme t ON s.id = t.s_id
WHERE    t.doping IS NOT NULL
ORDER BY name, vorname, geburtstag, land
;

-- Selektiere alle Sportler, für die kein Dopingtest existiert
-- (für die NICHT ein Dopingtest EXISTIERT).
SELECT name, vorname, geburtstag, land
FROM   sportler s 
WHERE  NOT EXISTS
       (SELECT *
        FROM   teilnahme t
        WHERE  t.s_id = s.id AND t.doping IS NOT NULL
       )
ORDER BY name, vorname, geburtstag, land
;

SELECT name, vorname, geburtstag, land  -- Alle Sportler
FROM   sportler s
WHERE  NOT EXISTS 
       (SELECT *              -- für die nicht existiert
        FROM   teilnahme t    -- eine Teilnahme mit doping IS TRUE OR doping IS FALSE
        WHERE  t.s_id = s.id AND doping IS NOT UNKNOWN  -- s.id verweist auf 
       );                                               -- aktuellen Sportler
 
-- Selektiere alle Sportler, die NICHT IN der Menge der Getesteten enthalten sind.
SELECT name, vorname, geburtstag, land
FROM   sportler
WHERE  id NOT IN
       (SELECT s_id 
        FROM   teilnahme 
        WHERE  doping IS NOT NULL
       )
ORDER BY name, vorname, geburtstag, land
;

SELECT name, vorname, geburtstag, land
FROM   sportler
WHERE  id <>ALL
       (SELECT s_id
        FROM   teilnahme
        WHERE  doping IS NOT NULL
       ) 
ORDER BY name, vorname, geburtstag, land
;

SELECT   s.name, s.vorname, s.geburtstag, s.land
FROM     sportler s LEFT JOIN teilnahme t ON s.id = t.s_id
GROUP BY s.name, s.vorname, s.geburtstag, s.land
HAVING   COUNT(t.doping) = 0
ORDER BY name, vorname, geburtstag, land
;

/* Wie funktioniert die letzte Lösung?
   Auf Doping wurde nicht getestet, wer bei jeder Teilnahme 
   den Doing-Wert NULL aufweist, d.h., bei dem die
   Anzahl der Doping-Tests gleich 0 ist.
   Der Left-Join fügt alle Sportler ein, die noch
   nicht gemeldet sind. 

   So sieht die Tabelle vor der Gruppierung aus:
*/

SELECT   s.name, s.vorname, s.geburtstag, s.land, t.w_id, t.doping
FROM     sportler s LEFT JOIN teilnahme t ON s.id = t.s_id
ORDER BY name, vorname, geburtstag, land
;


/*
** Aufgabe f
**
** Von welchen Sportlern  ('w' und 'm') steht bei 
** mindestens einem Wettkampf noch nicht fest, 
** welchen Platz sie belegen?

name   vorname  geburtstag  land
--------------------------------
Harel  Barbara  1970-01-01  FRA
Maier  Josef    1980-01-01  GER
*/

-- zu viel des Guten
SELECT DISTINCT s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s JOIN teilnahme t ON s.id = t.s_id
WHERE           t.doping IS NOT TRUE AND t.disqualifiziert IS FALSE AND platz IS NULL
;

-- reicht (da t.doping IS TRUE => t.disqualifiziert is TRUE)
SELECT DISTINCT s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s JOIN teilnahme t ON s.id = t.s_id
WHERE           t.disqualifiziert IS FALSE AND t.platz IS NULL
;

-- reicht nicht: Von den Disqualifizierten steht der Platz bereits fest. Dieser
--               ist NULL.
SELECT DISTINCT s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s JOIN teilnahme t ON s.id = t.s_id
WHERE           t.platz IS NULL
;


/*
** Aufgabe g
**
** Wer kann in welcher Wettkampfart, in der sie/er als Teilnehmerin/Teilnehmer gemeldet ist,
** keinen zweiten Platz mehr erreichen (entweder weil er/sie einen bereits einen anderen Platz 
** belegt hat oder weil er/sie disqualifiziert wurde)?
**

name          vorname     geburtstag  land  w_geschlecht  platz  disqualifiziert  sportart   wettkampfart        s_geschlecht
-----------------------------------------------------------------------------------------------------------------------------
Angelopoulou  Vasiliki    1987-05-30  GRE   w             1      FALSCH           Schwimmen  Brust 100m          w
Becker        Boris       1967-11-22  GER   m             1      FALSCH           Tennis     Mixed  Finale       x
Becker        Boris       1967-11-22  GER   m             1      FALSCH           Tennis     Einzel Finale       m
Buschschulte  Antje       1978-12-27  GER   w             1      FALSCH           Schwimmen  Lagen 400m          w
Buschschulte  Antje       1978-12-27  GER   w             1      FALSCH           Schwimmen  Schmetterling 100m  w
Filippi       Alessia     1987-06-23  ITA   w             1      FALSCH           Schwimmen  Lagen 400m          w
Graf          Steffi      1969-06-14  GER   w             1      FALSCH           Tennis     Einzel Finale       w
Graf          Steffi      1969-06-14  GER   x             1      FALSCH           Tennis     Mixed  Finale       x
Harstick      Sara        1981-01-01  GER   w             1      FALSCH           Schwimmen  Lagen 400m          w
Karsten       Ekaterina   1972-06-02  BLR   w             1      FALSCH           Schwimmen  Brust 100m          w
Maier         Josef       1982-01-01  GER   m             NULL   WAHR             Schwimmen  Brust 100m          m
Tuuxaru       Iliisquix   1970-01-01  CUB   m             NULL   WAHR             Schwimmen  Brust 100m          m
van Almsick   Franziska   1978-04-05  GER   w             1      FALSCH           Rudern     Kajak-Einer         w
van Almsick   Franziska   1978-04-05  GER   w             8      FALSCH           Schwimmen  Brust 100m          w
Völker        Sandra      1974-04-01  GER   w             1      FALSCH           Schwimmen  Brust 100m          w
Völker        Sandra      1974-04-01  GER   w             1      FALSCH           Schwimmen  Brust 200m          w
Völker        Sandra      1974-04-01  GER   w             1      FALSCH           Schwimmen  Lagen 400m          w
Völker        Sandra      1974-04-01  GER   w             1      FALSCH           Schwimmen  Schmetterling 100m  w
Xeteylu       Leidisyuri  1970-01-01  CUB   w             NULL   WAHR             Schwimmen  Brust 100m          w
Yukhareva     Natalya     1970-01-01  RUS   w             1      FALSCH           Schwimmen  Brust 100m          w
*/

SELECT   s.name, s.vorname, s.geburtstag, s.land, s.geschlecht AS s_geschlecht,
         t.platz, t.disqualifiziert,
         w.sportart, w.wettkampfart, w.geschlecht AS w_geschlecht
FROM     sportler s JOIN teilnahme t ON s.id = t.s_id
                    JOIN wettkampf w ON t.w_id = w.id
WHERE    (   t.platz <> 2   -- NOT (t.platz = 2)
          OR t.disqualifiziert IS TRUE
         )
ORDER BY name, vorname, geburtstag, land
;


/*
** Aufgabe h
**
** Bei welchen Sportlern ('w' und 'm') stimmen in welchen 
** Sportarten die Werte für Disqualifikation und Doping 
** nicht überein?
**
** Verwenden Sie in der Where-Klausel einen Test der Art
** WHERE (t.disqualifiziert = t.doping) IS ...
**

name         vorname    geburtstag  land  doping  disqualifiziert  sportart   wettkampfart  geschlecht
------------------------------------------------------------------------------------------------------
Harstick     Sara       1981-01-01  GER   NULL    FALSCH           Schwimmen  Lagen 400m    w
Maier        Josef      1982-01-01  GER   FALSCH  WAHR             Schwimmen  Brust 100m    m
van Almsick  Franziska  1978-04-05  GER   NULL    FALSCH           Schwimmen  Lagen 400m    w
van Almsick  Franziska  1978-04-05  GER   NULL    FALSCH           Rudern     Kajak-Einer   w


** Ueberlegen Sie sich, welche Auswirkungen es hat,
** welchen Unterschied es macht, wenn Sie 
** IS FALSE an Stelle von IS NOT TRUE verwenden.

name         vorname    geburtstag  land  doping  disqualifiziert  sportart   wettkampfart  geschlecht
------------------------------------------------------------------------------------------------------
Maier        Josef      1982-01-01  GER   FALSCH  WAHR             Schwimmen  Brust 100m    m

** Machen Sie sich zu guter Letzt an diesem Beispiel den Unterschied zwischen
**   a <> b
** und
**   a IS DISTINCT FROM b
** klar.
*/

-- Auch Sportler, bei den Disqualifikation und Doping EVENTUELL
-- nicht übereinstimmen.
SELECT   s.name, s.vorname, s.geburtstag, s.land, 
         t.doping, t.disqualifiziert,
         w.sportart, 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    (t.disqualifiziert = t.doping) IS NOT TRUE
ORDER BY name, vorname, geburtstag, land
;

SELECT   s.name, s.vorname, s.geburtstag, s.land, 
         t.doping, t.disqualifiziert,
         w.sportart, 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    t.disqualifiziert IS DISTINCT FROM t.doping
ORDER BY name, vorname, geburtstag, land
;

-- Nur Sportler, bei den Disqualifikation und Doping mit Sicherheit
-- nicht übereinstimmen.
SELECT   s.name, s.vorname, s.geburtstag, s.land, 
         t.doping, t.disqualifiziert,
         w.sportart, 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    (t.disqualifiziert = t.doping) IS FALSE
ORDER BY name, vorname, geburtstag, land
;

SELECT   s.name, s.vorname, s.geburtstag, s.land, 
         t.doping, t.disqualifiziert,
         w.sportart, 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    t.disqualifiziert <> t.doping
ORDER BY name, vorname, geburtstag, land
;


SELECT   s.name, s.vorname, s.geburtstag, s.land, 
         t.doping, t.disqualifiziert,
         w.sportart, 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  (t.disqualifiziert = t.doping) IS FALSE
--WHERE  (t.disqualifiziert = t.doping) IS NOT TRUE
--WHERE  t.disqualifiziert <> t.doping                -- entspricht IS FALSE
--WHERE  t.disqualifiziert IS DISTINCT FROM t.doping  -- entspricht IS NOT TRUE

--WHERE  (t.disqualifiziert = t.doping) IS UNKNOWN    -- Aufgabe i
  WHERE  (t.disqualifiziert = t.doping) IS NULL       -- Aufgabe i
;

/*
** Aufgabe i
**
** Bei welchen Sportlern ('w' und 'm') kann man in welchen Sportarten derzeit
** nicht mit Sicherheit entscheiden, ob die Werte für Disqualifikation und Doping 
** übereinstimmen?
**
** Verwenden Sie in der Where-Klausel wieder einen Test der Art
** WHERE (t.disqualifiziert = t.doping) IS ...

name         vorname    geburtstag  land  doping  disqualifiziert  sportart   wettkampfart  geschlecht
------------------------------------------------------------------------------------------------------
Harstick     Sara       1981-01-01  GER   NULL    FALSCH           Schwimmen  Lagen 400m    w
van Almsick  Franziska  1978-04-05  GER   NULL    FALSCH           Schwimmen  Lagen 400m    w
van Almsick  Franziska  1978-04-05  GER   NULL    FALSCH           Rudern     Kajak-Einer   w
*/

SELECT   s.name, s.vorname, s.geburtstag, s.land, 
         t.doping, t.disqualifiziert,
         w.sportart, 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    (t.disqualifiziert = t.doping) IS UNKNOWN  
ORDER BY name, vorname, geburtstag, land
;

SELECT   s.name, s.vorname, s.geburtstag, s.land, 
         t.doping, t.disqualifiziert,
         w.sportart, 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    (t.disqualifiziert = t.doping) IS NULL  
ORDER BY name, vorname, geburtstag, land
;