Aufgabe 5.3 (45 Punkte)

Wechseln Sie in die Datenbank user_[loginname]_WM2006. Finden Sie eine Möglichkeit, die Tabellen und Inhalte aus der Datenbank WM2006 in Ihre Datenbank zu importieren. Erläutern Sie Ihre Vorgehensweise.

Formulieren Sie anschließend die folgenden Abfragen in SQL und überprüfen Sie sie anhand Ihrer Datenbank:

1. Bestimmen Sie die Mannschaften der Spiele, in denen 'Lukas Podolski' Tore geschossen hat, inklusive der Anzahl der von ihm erzielten Tore.

2. In welcher Minute schoss welches Land das schnellste Tor der WM?

3. In welchem Stadion wurden die meisten Tore geschossen?

4. In welcher Mannschaft haben die Stürmer zusammen weniger Tore geschossen als alle übrigen Spieler.

5. Gibt es Mannschaften, deren Spiele mehrmals vom selben Schiedsrichter geleitet wurden? Erstellen Sie eine Anfrage, die die Anzahl der Spiele pro Mannschaft und Schiedsrichter auflistet.

6. Listen Sie alle Mannschaften mit der durchschnittlichen Anzahl der Verwarnungen pro Spiel, absteigend sortiert.

7. In welchen Spielen wurde das erste Tor erzielt, bevor die erste Verwarnung ausgesprochen wurde.

8. Bei welchen Schiedsrichtern wurden in allen Spielen Platzverweise ausgesprochen, in denen sie als 4. Offizieller teilgenommen haben?

Musterlösung vom 02.06.2009:

Eine Möglichkeit, die Datenbank zu kopieren, ist die Verwendung der MySQL-Kommandozeilentools mysqldump und mysql. Mit dem Befehl mysqldump -u [loginname] -p WM2006 > wm.sql exportiert man die Datenbank in die Datei wm.sql. Diese enthält anschließend entsprechende create table- und insert-Befehle in SQL-Syntax, um die Tabellen zu erzeugen und zu füllen. Analog zu Aufgabe 5.2 kann man mit dem Aufruf mysql -u [loginname] -p [datenbank] < wm.sql die Tabellen dann in einer beliebigen Datenbank wieder anlegen.

Alternativ lassen sich die beiden Schritte in phpMyAdmin ausführen. In der Datenbank 'WM2006' lässt sich unter 'Exportieren' analog zu mysqldump eine SQL-Datei erstellen. Anschließend lässt sich diese wie in Aufgabe 5.2 beschrieben in der eigenen Datenbank wieder importieren.

Es ergeben sich folgende Abfragen:
1.

  select s.Stadion, s.Mannschaft1, s.Mannschaft2, count(*)
    from Spieler p, Tore t, Spiele s
   where p.SpielerNr = t.SpielerNr
     and s.SpielID = t.SpielID
     and p.Name = 'PODOLSKI Lukas'
group by s.SpielID

2.

select s.Land, t.minute
  from Spieler s, Tore t
 where s.SpielerNr = t.SpielerNr
   and minute <= all (select minute from Tore)

3.

select s.Stadion, count(*) as Tore
      from Spiele s, Tore t
     where s.SpielID = t.SpielID
  group by s.Stadion
    having Tore >= all (select count(*) as Tore
                          from Spiele s, Tore t
                         where s.SpielID = t.SpielID
                      group by s.Stadion) 

4.

select m.Land,
  (select count(*) as To1
   from Spieler s1, Tore t1
   where s1.SpielerNr = t1.SpielerNr
         and s1.Position = 'Forward'
         and s1.Land = m.Land) as Tore1,
  (select count(*) as To2
   from Spieler s2, Tore t2
   where s2.SpielerNr = t2.SpielerNr
         and s2.Position != 'Forward'
         and s2.Land = m.Land) as Tore2
from Mannschaften m
group by m.Land
having Tore1 < Tore2
order by m.Land

5.

select s.Mannschaft, s.Name, count(*) as Anzahl
from 
((select sp.Mannschaft1 as Mannschaft, sr.Name  
    from Spiele sp, leitet l, Schiedsrichter sr
   where sp.SpielID = l.SpielID
     and l.SID = sr.SID
     and l.Funktion = 'Referee')
   union all
 (select sp.Mannschaft2 as Mannschaft, sr.Name 
    from Spiele sp, leitet l, Schiedsrichter sr
   where sp.SpielID = l.SpielID
     and l.SID = sr.SID
     and l.Funktion = 'Referee')) s
group by s.Mannschaft, s.Name

6.

select s.Land, count(*)/(select count(*) 
                           from Spiele sp
                          where s.Land = sp.Mannschaft1 
                             or s.Land = sp.Mannschaft2) as Schnitt
  from Spieler s, Verwarnungen v
 where s.SpielerNr = v.SpielerNr
 group by s.Land
 order by Schnitt desc

7.

select distinct s.Mannschaft1, s.Mannschaft2
  from Tore t, Spiele s
 where t.SpielID = s.SpielID
   and t.Minute < (select min(v.Minute)
                     from Verwarnungen v
                    where s.SpielID = v.SpielID)

8.

select s1.name
  from Schiedsrichter s1, leitet l1
 where s1.sid = l1.sid
   and l1.funktion = 'Fourth Official'
   and s1.name NOT IN (select s.name
                         from Schiedsrichter s, leitet l
                        where s.sid = l.sid
                          and l.funktion = 'Fourth Official'
                       having (select count(*)
                                 from Verwarnungen
                                where spielid = l.spielid
                                  and karte != 'yellow') = 0)