/* ** Olympiade (Extra) * * Fügen Sie zunächst folgende Daten in Ihre Datenbank ein. * (Beachten Sie: Die Ergebnisse der anderen Olympia-Aufgaben * verändern sich dadurch und stimmen oftmals micht mehr * mit der zugehörigen Musterlösung überein. Daher ist * es evtl. sinnvoll, wenn Sie für diese Aufgabe eine eigene * Olympiadatenbank anlegen.) */ INSERT INTO sportler(id, name, vorname, geburtstag, geschlecht, land) VALUES (101, 'Sportlerin01', 'Prima', DATE '1971-01-01', 'w', 'GER'), (102, 'Sportlerin02', 'Secunda', DATE '1971-02-02', 'w', 'GER'), (103, 'Sportlerin03', 'Tertia', DATE '1971-03-03', 'w', 'GER'), (104, 'Sportlerin04', 'Quarta', DATE '1971-04-04', 'w', 'GER'), (105, 'Sportlerin05', 'Quinta', DATE '1971-05-05', 'w', 'GER'), (106, 'Sportlerin06', 'Sexta', DATE '1971-06-06', 'w', 'GER'), (107, 'Sportlerin07', 'Septa', DATE '1971-07-07', 'w', 'GER'), (108, 'Sportlerin08', 'Octavia', DATE '1971-07-07', 'w', 'GER'), (201, 'Sportler01', 'Primus', DATE '1970-01-01', 'm', 'GER'), (202, 'Sportler02', 'Sexundus', DATE '1970-02-02', 'm', 'GER'), (203, 'Sportler03', 'Tertius', DATE '1970-03-03', 'm', 'FRA'), (204, 'Sportler04', 'Quartus', DATE '1970-04-04', 'm', 'GER'), (205, 'Sportler05', 'Quintus', DATE '1970-05-05', 'm', 'GER'), (206, 'Sportler06', 'Sextus', DATE '1970-06-06', 'm', 'GER'), (207, 'Sportler07', 'Septus', DATE '1970-07-07', 'm', 'GER'), (208, 'Sportler08', 'Oktavius', DATE '1970-08-08', 'm', 'GER'), (300, 'Allos', 'Protos', DATE '1972-01-01', 'm', 'FRA') ; INSERT INTO teilnahme (s_id, w_id, platz) VALUES (101, 1, 1), (102, 1, 2), (103, 1, 3), (104, 1, 4), (105, 1, 5), (106, 1, 6), (107, 1, 7), (108, 1, 8), (300, 2, 1), (201, 2, 2), (202, 2, 3), (203, 2, 4), (204, 2, 5), (205, 2, 6), (206, 2, 7), (207, 2, 8), (208, 2, 9) ; /* ** Extraaufgabe ** ** Geben Sie die Länder aus, die fünf Plätze hintereinander ** in einem Wettkampf belegt haben. Geben Sie zusätzlich ** jeweils die Sportart, die Wettkampfart, das Geschlecht der Sportart ** sowie die kleinste und die größte Platznummer der Fünferketter ** und die Anzahl der aufeinanderfolgenden Plätze aus: ** ** land sportart wettkampfart geschlecht platz_min platz_max anzahl ** ---------------------------------------------------------------------- ** GER Judo bis 57kg w 1 5 5 ** GER Judo bis 57kg w 2 6 5 ** GER Judo bis 57kg w 3 7 5 ** GER Judo bis 57kg w 4 8 5 ** GER Judo bis 80kg m 5 9 5 */ -------------------------------------------------------------------------- -- Schritt 1 -- -- Definieren zunächst eine View "st", die die Tabellen -- "sportler" und "teilnahme" joint (da Sie diese öfters -- benötigen werden): -- st (s_id, land, w_id, platz) -------------------------------------------------------------------------- -------------------------------------------------------------------------- -- Schritt 2 -- -- Finden Sie nun mit Hilfe der zuvor definierten View zwei Plätze, -- die von Sportlern desselben Landes belegt werden und deren Abstand -- 4 beträgt (genauer: 5 minus 1). -- Tipp (https://rot13.de/): Wbvara Fvr fg zvg fvpu fryofg. -- -- land sportart wettkampfart geschlecht platz_min platz_max -- -------------------------------------------------------------- -- GER Judo bis 57kg w 1 5 -- GER Judo bis 57kg w 2 6 -- GER Judo bis 57kg w 3 7 -- GER Judo bis 57kg w 4 8 -- GER Judo bis 80kg m 2 6 -- GER Judo bis 80kg m 3 7 -- GER Judo bis 80kg m 5 9 -------------------------------------------------------------------------- -------------------------------------------------------------------------- -- Schritt 3 -- -- Erweitern Sie die vorherige Anfrage. Zählen Sie, wie viele -- Plätze zwischen den beiden zuvor ermittelten Plätzen -- (einschließlich dieser beiden Plätze) vom selben Land -- belegt werden. -- -- land sportart wettkampfart geschlecht platz_min platz_max anzahl -- ---------------------------------------------------------------------- -- GER Judo bis 57kg w 1 5 5 -- GER Judo bis 57kg w 2 6 5 -- GER Judo bis 57kg w 3 7 5 -- GER Judo bis 57kg w 4 8 5 -- GER Judo bis 80kg m 2 6 4 -- GER Judo bis 80kg m 3 7 4 -- GER Judo bis 80kg m 5 9 5 -- -- Diese Aufgabe kann auf unterschiedliche Weise gelöst werden: -- - Mit einer komplexen Subquery in der Projektionsliste -- - Mit GROUP BY (Tipp: Wbvara Fvr fg rva qevggrf Zny zvg fvpu fryofg.) -- - Mit einem LATERAL JOIN (vermutlich die eleganteste Lösung) -- - Auf eine ganz andere Weise -- Versuchen Sie (mindestens) die ersten beiden Varianten -- zu implementieren. -------------------------------------------------------------------------- -- -- Variante 1 (Subquery, die die Anzahl der Plätze vom selben Land zählt) -- -- -- Variante 2 (GROUB BY, COUNT und HAVING) -- -- -- Variante 3 (LATERAL JOIN) -- -------------------------------------------------------------------------- -- Schritt 4 -- -- Selektieren Sie mittels einer WHERE- oder einer HAVING-Klausel, -- diejenigen Wettkämpfe, für die die Anzahl gleicher Länder -- gleich 5 ist. -- -- land sportart wettkampfart geschlecht platz_min platz_max anzahl -- ---------------------------------------------------------------------- -- GER Judo bis 57kg w 1 5 5 -- GER Judo bis 57kg w 2 6 5 -- GER Judo bis 57kg w 3 7 5 -- GER Judo bis 57kg w 4 8 5 -- GER Judo bis 80kg m 5 9 5 -------------------------------------------------------------------------- -- -- Variante 1 (Subquery, die die Anzahl der Plätze vom selben Land zählt) -- -- -- Variante 2 (GROUB BY, COUNT und HAVING) -- -- -- Variante 3 (LATERAL JOIN) -- -------------------------------------------------------------------------- -- Schritt 5 -- -- -- Erweitern Sie diese Anfrage so, dass sie für alle Folgen mit -- n Plätzen, die in einem Wettkampf von Sportlern eines Landes -- belegt werden, Das Land, der Wettkampf, der kleinste und der -- größte Platz sowie die Anzahl ausgegeben werden. Im Prinzip -- müssen Sie "nur" dafür sorgen, dass die Zahl der benachbarten -- Plätze (5 oder 4 für 5-1) nicht mehr explizit in der Anfrage -- auftaucht. -- -- Tipp: Qvr Namnuy vfg fgrgf fg2.cyngm - fg1.cyngm + 1, -- jraa nyyr Fcbegyre iba fg1.cyngm ovf fg2.cyngm -- qrzfryora Ynaq natruöera. -- -- land sportart wettkampfart geschlecht platz_min platz_max anzahl -- ---------------------------------------------------------------------- -- GER Judo bis 57kg w 1 2 2 -- GER Judo bis 57kg w 2 3 2 -- ... -- GER Schwimmen Brust 100m w 1 2 2 -- ... -- GER Judo bis 57kg w 1 5 5 -- GER Judo bis 57kg w 2 6 5 -- GER Judo bis 57kg w 3 7 5 -- GER Judo bis 57kg w 4 8 5 -- GER Judo bis 80kg m 5 9 5 -- ... -- GER Judo bis 57kg w 1 8 8 -- -- Insgesamt 40 Ergebnistupel -------------------------------------------------------------------------- -- -- Variante 1 (Subquery, die die Anzahl der Plätze vom selben Land zählt) -- -- -- Variante 2 (GROUB BY, COUNT und HAVING) -- -- -- Variante 3 (LATERAL JOIN) -- -------------------------------------------------------------------------- -- Schritt 6 -- -- Schreiben Sie eine View, mit der man allgemein Folgen -- mit n (n = 2, 3, ... ) aufeinanderfolgenden Plätzen -- ermitteln kann, die von Sportlern eines Landes belegt werden. -- Geben Sie testhalber die Folgen für n = 5 und n = 8 aus. -- -- land sportart wettkampfart geschlecht platz_min platz_max anzahl -- ---------------------------------------------------------------------- -- GER Judo bis 57kg w 1 5 5 -- GER Judo bis 57kg w 2 6 5 -- GER Judo bis 57kg w 3 7 5 -- GER Judo bis 57kg w 4 8 5 -- GER Judo bis 80kg m 5 9 5 -- -- land sportart wettkampfart geschlecht platz_min platz_max anzahl -- ---------------------------------------------------------------------- -- GER Judo bis 57kg w 1 8 8 -------------------------------------------------------------------------- -- -- Variante 1 (Subquery, die die Anzahl der Plätze vom selben Land zählt) -- -- -- Variante 2 (GROUB BY, COUNT und HAVING) -- -- -- Variante 3 (LATERAL JOIN) --