/* ** Olympiade (Teil 2) */ /* ** Aufgabe a ** ** Welche Sportler wurden noch nicht in allen Disziplinen, in denen ** sie angetreten 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, l.kuerzel AS land FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON s.id = t.s_id WHERE t.doping IS NULL ; SELECT DISTINCT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land FROM e_sportler s JOIN enum_land l ON s.l_id = l.id WHERE EXISTS (SELECT * FROM r_teilnahme t WHERE s.id=t.s_id AND t.doping IS NULL ) ; /* ** Aufgabe a (Verallgemeinerung) ** ** Wie sieht das Ergebnis 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, l.kuerzel AS land FROM e_sportler s LEFT JOIN enum_land l ON s.l_id = l.id LEFT JOIN r_teilnahme t ON s.id = t.s_id WHERE doping IS NULL ; -- mit Ausgabe der Anzahl SELECT DISTINCT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land FROM e_sportler s LEFT JOIN enum_land l ON s.l_id = l.id LEFT JOIN r_teilnahme t ON s.id = t.s_id WHERE doping IS NULL ; SELECT DISTINCT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, COUNT(t.w_id) AS anzahl FROM e_sportler s LEFT JOIN enum_land l ON s.l_id = l.id LEFT JOIN r_teilnahme t ON s.id = t.s_id WHERE doping IS NULL GROUP BY s.id, s.name, s.vorname, s.geburtstag, l.kuerzel ; /* ** Aufgabe b ** ** Welche Sportler 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.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land FROM e_sportler s JOIN enum_land l ON s.l_id = l.id EXCEPT SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON s.id = t.s_id WHERE t.doping IS NOT UNKNOWN ORDER BY name, vorname, geburtstag, land ; SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land FROM e_sportler s JOIN enum_land l ON s.l_id = l.id EXCEPT SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON s.id = t.s_id WHERE t.doping IS NOT NULL ORDER BY name, vorname, geburtstag, land ; SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land FROM e_sportler s JOIN enum_land l ON s.l_id = l.id WHERE NOT EXISTS (SELECT * FROM r_teilnahme t WHERE t.s_id = s.id AND t.doping IS NOT NULL ) ORDER BY name, vorname, geburtstag, land ; SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land FROM e_sportler s JOIN enum_land l ON s.l_id = l.id WHERE s.id NOT IN (SELECT s_id FROM r_teilnahme WHERE doping IS NOT NULL ) ORDER BY name, vorname, geburtstag, land ; SELECT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land FROM e_sportler s JOIN enum_land l ON s.l_id = l.id WHERE s.id <>ALL (SELECT s_id FROM r_teilnahme WHERE doping IS NOT NULL ) ORDER BY name, vorname, geburtstag, land ; SELECT DISTINCT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land FROM e_sportler s LEFT JOIN enum_land l ON s.l_id = l.id LEFT JOIN r_teilnahme t ON s.id = t.s_id GROUP BY s.id, s.name, s.vorname, s.geburtstag, l.kuerzel 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 DISTINCT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, t.w_id, t.doping FROM e_sportler s LEFT JOIN enum_land l ON s.l_id = l.id LEFT JOIN r_teilnahme t ON s.id = t.s_id ORDER BY id ; /* ** Aufgabe c ** ** Welche Teilnehmerländer haben sich eines Dopingvergehens schuldig gemacht? land ---- CUB */ SELECT DISTINCT l.kuerzel AS land FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON s.id = t.s_id WHERE t.doping IS TRUE ; /* ** Aufgabe d ** ** Welche Sportlerinnen sind disqualifiziert worden? name vorname geburtstag land ------------------------------------- Xeteylu Leidisyuri 1970-01-01 CUB */ SELECT DISTINCT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, t.w_id, t.doping FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON s.id = t.s_id WHERE t.disqualifiziert IS TRUE AND s.geschlecht = 'w' ; /* ** Aufgabe e ** ** Welche Sportler sind gedopt, aber nicht disqualifiziert worden? ** ** Das Ergebnis muss leer sein! */ SELECT DISTINCT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, t.w_id, t.doping FROM e_sportler s LEFT JOIN enum_land l ON s.l_id = l.id LEFT JOIN r_teilnahme t ON s.id = t.s_id WHERE t.doping IS TRUE AND t.disqualifiziert IS FALSE ; -- besser, wg. dreiwertiger Logik SELECT DISTINCT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, t.w_id, t.doping FROM e_sportler s LEFT JOIN enum_land l ON s.l_id = l.id LEFT JOIN r_teilnahme t ON s.id = t.s_id WHERE t.doping IS TRUE AND t.disqualifiziert IS NOT TRUE ; -- Einzelabfragen SELECT DISTINCT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, t.w_id, t.doping FROM e_sportler s LEFT JOIN enum_land l ON s.l_id = l.id LEFT JOIN r_teilnahme t ON s.id = t.s_id WHERE t.disqualifiziert IS TRUE ; SELECT DISTINCT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, t.w_id, t.doping FROM e_sportler s LEFT JOIN enum_land l ON s.l_id = l.id LEFT JOIN r_teilnahme t ON s.id = t.s_id WHERE t.doping IS TRUE ; -- Zum Vergleich: -- Welche Sportler sind disqualifiziert, aber nicht gedopt worden? SELECT DISTINCT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, t.w_id, t.doping FROM e_sportler s LEFT JOIN enum_land l ON s.l_id = l.id LEFT JOIN r_teilnahme t ON s.id = t.s_id WHERE doping IS NOT TRUE AND disqualifiziert IS TRUE -- doping IS FALSE reicht nicht!!! ; /* ** Aufgabe f ** ** Von welchen Sportlern 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 s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, t.w_id, t.doping FROM e_sportler s LEFT JOIN enum_land l ON s.l_id = l.id LEFT JOIN r_teilnahme t ON s.id = t.s_id WHERE t.doping IS NOT TRUE AND t.disqualifiziert IS FALSE AND platz IS NULL ; -- reicht (zumindest in der robusten Version) SELECT DISTINCT s.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, t.w_id, t.doping FROM e_sportler s LEFT JOIN enum_land l ON s.l_id = l.id LEFT JOIN r_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.id, s.name, s.vorname, s.geburtstag, l.kuerzel AS land, t.w_id, t.doping FROM e_sportler s LEFT JOIN enum_land l ON s.l_id = l.id LEFT JOIN r_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 einen bereits einen anderen Platz ** belegt hat oder weil er 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, l.kuerzel AS land, s.geschlecht AS s_geschlecht, t.platz, t.disqualifiziert, sa.name AS sportart, wa.name AS wettkampfart, w.geschlecht AS w_geschlecht FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id JOIN e_wettkampf w ON t.w_id = w.id JOIN enum_sportstaette spst ON w.spst_id = spst.id JOIN enum_wettkampfart wa ON w.wa_id = wa.id JOIN enum_sportart sa ON wa.sa_id = sa.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 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, l.kuerzel AS land, t.doping, t.disqualifiziert, sa.name AS sportart, wa.name AS wettkampfart, w.geschlecht FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id JOIN e_wettkampf w ON t.w_id = w.id JOIN enum_sportstaette spst ON w.spst_id = spst.id JOIN enum_wettkampfart wa ON w.wa_id = wa.id JOIN enum_sportart sa ON wa.sa_id = sa.id WHERE (t.disqualifiziert = t.doping) IS NOT TRUE ORDER BY name, vorname, geburtstag, land ; SELECT s.name, s.vorname, s.geburtstag, l.kuerzel AS land, t.doping, t.disqualifiziert, sa.name AS sportart, wa.name AS wettkampfart, w.geschlecht FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id JOIN e_wettkampf w ON t.w_id = w.id JOIN enum_sportstaette spst ON w.spst_id = spst.id JOIN enum_wettkampfart wa ON w.wa_id = wa.id JOIN enum_sportart sa ON wa.sa_id = sa.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, l.kuerzel AS land, t.doping, t.disqualifiziert, sa.name AS sportart, wa.name AS wettkampfart, w.geschlecht FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id JOIN e_wettkampf w ON t.w_id = w.id JOIN enum_sportstaette spst ON w.spst_id = spst.id JOIN enum_wettkampfart wa ON w.wa_id = wa.id JOIN enum_sportart sa ON wa.sa_id = sa.id WHERE (t.disqualifiziert = t.doping) IS FALSE ORDER BY name, vorname, geburtstag, land ; SELECT s.name, s.vorname, s.geburtstag, l.kuerzel AS land, t.doping, t.disqualifiziert, sa.name AS sportart, wa.name AS wettkampfart, w.geschlecht FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id JOIN e_wettkampf w ON t.w_id = w.id JOIN enum_sportstaette spst ON w.spst_id = spst.id JOIN enum_wettkampfart wa ON w.wa_id = wa.id JOIN enum_sportart sa ON wa.sa_id = sa.id WHERE t.disqualifiziert <> t.doping ORDER BY name, vorname, geburtstag, land ; /* ** Aufgabe i ** ** Bei welchen Sportlern 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 Einer Kajak w */ SELECT s.name, s.vorname, s.geburtstag, l.kuerzel AS land, t.doping, t.disqualifiziert, sa.name AS sportart, wa.name AS wettkampfart, w.geschlecht FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id JOIN e_wettkampf w ON t.w_id = w.id JOIN enum_sportstaette spst ON w.spst_id = spst.id JOIN enum_wettkampfart wa ON w.wa_id = wa.id JOIN enum_sportart sa ON wa.sa_id = sa.id WHERE (t.disqualifiziert = t.doping) IS UNKNOWN ORDER BY name, vorname, geburtstag, land ; SELECT s.name, s.vorname, s.geburtstag, l.kuerzel AS land, t.doping, t.disqualifiziert, sa.name AS sportart, wa.name AS wettkampfart, w.geschlecht FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON t.s_id = s.id JOIN e_wettkampf w ON t.w_id = w.id JOIN enum_sportstaette spst ON w.spst_id = spst.id JOIN enum_wettkampfart wa ON w.wa_id = wa.id JOIN enum_sportart sa ON wa.sa_id = sa.id WHERE (t.disqualifiziert = t.doping) IS NULL ORDER BY name, vorname, geburtstag, land ;