/*
** Olympiade (Teil 2)
*/

/*
** Aufgabe a 
**
** Welche Sportler wurden noch nicht in allen Disziplinen, in denen
** sie angetreten sind, auf Doping untersucht?
*/

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

SELECT DISTINCT s.s_id, s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s JOIN teilnahme t USING (s_id)
WHERE           t.doping IS NULL
;

SELECT s.s_id, s.name, s.vorname, s.geburtstag, s.land
FROM   sportler s
WHERE  EXISTS (SELECT *
               FROM   teilnahme t
               WHERE  s.s_id=t.s_id AND t.doping IS NULL
              )
;

-- 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.


SELECT DISTINCT s.s_id, s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s LEFT JOIN teilnahme t  USING (s_id)
WHERE           t.doping IS NULL
;



/*
** Aufgabe b 
**
** Welche Sportler wurden noch ueberhaupt keinem Doping-Test unterzogen?
*/

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 USING (s_id)
WHERE  t.doping IS NOT NULL
;

SELECT s.name, s.vorname, s.geburtstag, s.land
FROM   sportler s 
WHERE  NOT EXISTS
       (SELECT *
        FROM teilnahme t
        WHERE t.s_id = s.s_id AND t.doping IS NOT NULL
       )
;

SELECT s.name, s.vorname, s.geburtstag, s.land
FROM   sportler s
WHERE  s.s_id NOT IN
       (SELECT t.s_id
        FROM teilnahme t
        WHERE t.doping IS NOT NULL
       ) 
;

SELECT s.name, s.vorname, s.geburtstag, s.land
FROM   sportler s
WHERE  s.s_id <>ALL
       (SELECT t.s_id
        FROM teilnahme t
        WHERE t.doping IS NOT NULL
       ) 
;

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

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

   So sieht die Tabelle vor der Gruppierung aus:
*/

SELECT DISTINCT s.name, s.vorname, s.geburtstag, s.land, t.doping
FROM            sportler s LEFT JOIN teilnahme t USING (s_id)
ORDER BY        s.name, s.vorname, s.geburtstag, s.land, t.doping
;


/*
** Aufgabe c
**
** Welche Teilnehmerlaender haben sich eines Dopingvergehens schuldig gemacht? 
*/

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

SELECT DISTINCT s.land
FROM            sportler s JOIN teilnahme t USING (s_id)
WHERE           t.doping IS TRUE
;


/*
** Aufgabe d
**
** Welche Sportlerinnen sind disqualifiziert worden?
*/

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

/*
** Aufgabe e
**
** Welche Sportler sind gedopt, aber nicht disqualifiziert worden?
**
** Das Ergebnis sollte leer sein!
*/

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

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


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


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

-- Zum Vergleich:
-- Welche Sportler sind disqualifiziert, aber nicht gedpot worden?

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





/*
** Aufgabe f
**
** Von welchen Sportlern steht noch nicht fest, welchen Platz sie belegen?
*/

SELECT DISTINCT s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s, teilnahme t
WHERE           s.s_id=t.s_id AND 
                t.doping IS NOT TRUE AND t.disqualifiziert IS FALSE AND t.platz IS NULL
;

-- reicht (zumindest in der robusten Version)
SELECT DISTINCT s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s, teilnahme t
WHERE           s.s_id=t.s_id AND 
                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, teilnahme t
WHERE           s.s_id=t.s_id AND
                t.platz IS NULL
;


/*
** Aufgabe g
**
** Wer kann in welcher Sportart, in der sie/er als Teilnehmerin/Teilnehmer gemeldet ist,
** keinen zweiten Platz mehr erreichen?
**
** Schreiben Sie zwei SQL-Varianten. 
** Einmal soll in der Where-Klausel der Test (platz=2) 
** und einmal der Test (platz<>2) verwendet werden.
*/

SELECT DISTINCT s.name, s.vorname, s.geburtstag, s.land,
                t.platz, t.disqualifiziert,
                w.sportart, w.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
                (NOT (t.platz = 2)
                 OR  t.disqualifiziert IS TRUE
                )
;

SELECT DISTINCT s.name, s.vorname, s.geburtstag, s.land, 
                t.platz, t.disqualifiziert,
                w.sportart, w.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
                (t.platz <> 2 
                 OR t.disqualifiziert IS TRUE
                )
;

/*
** Aufgabe h
**
** Bei welchen Sportlern stimmen in welchen Sportarten die Werte
** für Disqualifikation und Doping nicht überein?
*/

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

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

-- vgl,: 

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


/*
** Aufgabe i
**
** Bei welchen Sportlern kann man in welchen Sportarten derzeit
** nicht sagen, ob die Werte für Disqualifikation und Doping 
** übereinstimmen?
*/

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

;
