/*
** Olympiade (Teil 2)
*/

-- ACHTUNG: 
--   Hier finden Sie fuer ausgewaehlte Aufgaben zusaetzliche 
--   Loesungsvorschlaege. 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 Musterloesungen, die Sie in den Dateien
--     olymia1_aufgabe_2_loesungen.sql
--   sowie
--     olymia2_aufgabe_2_loesungen.sql
--   vorfinden, funktionieren auch für das dritte Modell-    

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

land
----
CUB
*/

SELECT DISTINCT land
FROM            sportler_teilnahme
WHERE           doping IS TRUE
;


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

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

SELECT DISTINCT name, vorname, geburtstag, land
FROM            sportler_teilnahme
WHERE           disqualifiziert IS TRUE AND geschlecht = 'w'
;


/*
** Aufgabe c
**
** Welche Sportler ('w' und 'm') wurden noch nicht in allen 
** Disziplinen, 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 id, name, vorname, geburtstag, land
FROM            sportler_teilnahme 
WHERE           doping IS NULL AND w_id IS NOT 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 id, name, vorname, geburtstag, land
FROM            sportler_alle_teilnahme -- definiert mittels LEFT JOIN
WHERE           doping IS NULL
;

-- mit Ausgabe der Anzahl
SELECT   id, name, vorname, geburtstag, land, COUNT(w_id) AS anzahl
FROM     sportler_alle_teilnahme 
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 name, vorname, geburtstag, land
FROM            sportler_teilnahme
WHERE           doping IS NOT TRUE AND disqualifiziert IS TRUE
             -- 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 name, vorname, geburtstag, land
FROM            sportler_teilnahme
WHERE           doping IS TRUE AND disqualifiziert IS FALSE
;

-- besser, wg. dreiwertiger Logik
SELECT DISTINCT name, vorname, geburtstag, land
FROM            sportler_teilnahme
WHERE           doping IS TRUE AND disqualifiziert IS NOT TRUE 
;


-- Einzelabfragen
SELECT DISTINCT name, vorname, geburtstag, land
FROM            sportler_teilnahme
WHERE           disqualifiziert IS TRUE 
;

SELECT DISTINCT name, vorname, geburtstag, land
FROM            sportler_teilnahme
WHERE           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 name, vorname, geburtstag, land
FROM   sportler 
EXCEPT
SELECT name, vorname, geburtstag, land
FROM   sportler_teilnahme
WHERE  doping IS NOT UNKNOWN
ORDER BY name, vorname, geburtstag, land
;

SELECT name, vorname, geburtstag, land
FROM   sportler 
EXCEPT
SELECT name, vorname, geburtstag, land
FROM   sportler_teilnahme
WHERE  doping IS NOT NULL
ORDER BY name, vorname, geburtstag, land
;

SELECT   name, vorname, geburtstag, land
FROM     sportler_alle_teilnahme
GROUP BY name, vorname, geburtstag, land
HAVING   COUNT(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   name, vorname, geburtstag, land, w_id, doping
FROM     sportler_alle_teilnahme
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
*/

SELECT DISTINCT name, vorname, geburtstag, land
FROM            sportler_teilnahme
WHERE           doping IS NOT TRUE AND disqualifiziert IS FALSE AND platz IS NULL
;

-- reicht (zumindest in der robusten Version)
SELECT DISTINCT name, vorname, geburtstag, land
FROM            sportler_teilnahme
WHERE           disqualifiziert IS FALSE AND platz IS NULL
;

-- reicht nicht: Von den Disqualifizierten steht der Platz bereits fest. Dieser
--               ist NULL.
SELECT DISTINCT name, vorname, geburtstag, land
FROM            sportler_teilnahme
WHERE           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   name, vorname, geburtstag, land, s_geschlecht,
         platz, disqualifiziert,
         sportart, wettkampfart, w_geschlecht 
FROM     sportler_teilnahme_wettkampf
WHERE    (   platz <> 2   -- NOT (platz = 2)
          OR disqualifiziert IS TRUE
         )
ORDER BY name, vorname, geburtstag, land, s_geschlecht
;


/*
** 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   name, vorname, geburtstag, land, 
         doping, disqualifiziert,
         sportart, wettkampfart, w_geschlecht as geschlecht
FROM     sportler_teilnahme_wettkampf
WHERE    (disqualifiziert = doping) IS NOT TRUE
ORDER BY name, vorname, geburtstag, land
;

SELECT   name, vorname, geburtstag, land, 
         doping, disqualifiziert,
         sportart, wettkampfart, w_geschlecht as geschlecht 
FROM     sportler_teilnahme_wettkampf
WHERE    disqualifiziert IS DISTINCT FROM doping
ORDER BY name, vorname, geburtstag, land
;

-- Nur Sportler, bei den Disqualifikation und Doping mit Sicherheit
-- nicht übereinstimmen.
SELECT   name, vorname, geburtstag, land, 
         doping, disqualifiziert,
         sportart, wettkampfart, w_geschlecht as geschlecht 
FROM     sportler_teilnahme_wettkampf
WHERE    (disqualifiziert = doping) IS FALSE
ORDER BY name, vorname, geburtstag, land
;

SELECT   name, vorname, geburtstag, land, 
         doping, disqualifiziert,
         sportart, wettkampfart, w_geschlecht as geschlecht 
FROM     sportler_teilnahme_wettkampf
WHERE    disqualifiziert <> doping
ORDER BY name, vorname, geburtstag, land
;


/*
** 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   name, vorname, geburtstag, land, 
         doping, disqualifiziert,
         sportart, wettkampfart, w_geschlecht as geschlecht 
FROM     sportler_teilnahme_wettkampf
WHERE    (disqualifiziert = doping) IS UNKNOWN  
ORDER BY name, vorname, geburtstag, land
;

SELECT   name, vorname, geburtstag, land, 
         doping, disqualifiziert,
         sportart, wettkampfart, w_geschlecht as geschlecht 
FROM     sportler_teilnahme_wettkampf
WHERE    (disqualifiziert = doping) IS NULL  
ORDER BY name, vorname, geburtstag, land
;