Aufgabe 6.1 (30 Punkte)

Um die folgenden Abfragen in SQL an Ihre Datenbank user_[loginname]_WM2006 stellen zu können, müssen Sie zunächst einen Teil der Abfrage als Sicht (View) zur Verfügung stellen und mit einer select-Abfrage darauf zugreifen. Formulieren Sie anschließend mit Hilfe der Views die folgenden Abfragen in SQL und überprüfen Sie sie anhand Ihrer Datenbank:

Bestimmen Sie die Spielbegegnung(en), in denen die meisten Verwarnungen ausgesprochen wurden. Geben Sie dabei auch die Schiedsrichter an, die die entsprechenden Verwarnungen ausgesprochen haben.
Erstellen Sie für die die Fußball-WM eine Übersicht der Vorrundenergebnisse. Gruppieren Sie nach den Gruppen der Vorrunde und sortieren Sie die Mannschaften innerhalb der Gruppen absteigend bezüglich der erzielten Punkte.
Definieren Sie zunächst eine View, die die beiden Mannschaften der Spiele sowie die erzielten Tore auflistet. Beachten Sie dabei, auch die Eigentore einer Mannschaft als Gegentore zu zählen.
Welche Mannschaften haben den besten Torhüter? Das sind die Mannschaften, die im Schnitt die wenigsten Gegentore bekommen haben.
Definieren Sie zunächst eine View, die die Anzahl der Spiele pro Mannschaft auflistet, sowie eine View, die die Anzahl der Gegentore pro Mannschaft auflistet. Beachten Sie dabei wieder, die Eigentore als Gegentore zu zählen.

Musterlösung vom 08.06.2008:

Es ergeben sich folgende Abfragen:

-- Eine Sicht, die die Verwarnungen pro Spiel ausgibt

create view VerwJeSpiel as
select SpielID, count(*) as AnzVerw
  from Verwarnungen
 group by SpielID


-- Drei verschiedene Abfragen
-- SELECT mit 'NOT EXISTS'

select sp.Mannschaft1, sp.Mannschaft2, s.Name, v1.AnzVerw
  from Spiele sp, VerwJeSpiel v1, Schiedsrichter s, leitet l
 where sp.SpielID = v1.SpielID
   and l.SpielID  = v1.SpielID
   and l.SID      = s.SID
   and l.Funktion = 'Referee'
   and not exists
    (select *
       from VerwJeSpiel v2
      where v1.AnzVerw < v2.AnzVerw)


-- SELECT mit 'IN'

select sp.Mannschaft1, sp.Mannschaft2, s.Name, v1.AnzVerw
  from Spiele sp, VerwJeSpiel v1, Schiedsrichter s, leitet l
 where sp.SpielID = v1.SpielID
   and l.SpielID  = v1.SpielID
   and l.SID      = s.SID
   and l.Funktion = 'Referee'
   and v1.AnzVerw in
    (select max(AnzVerw)
       from VerwJeSpiel)

-- SELECT mit 'HAVING'

select sp.Mannschaft1, sp.Mannschaft2, s.Name, v1.AnzVerw
  from Spiele sp, VerwJeSpiel v1, Schiedsrichter s, leitet l
 where sp.SpielID = v1.SpielID
   and l.SpielID  = v1.SpielID
   and l.SID      = s.SID
   and l.Funktion = 'Referee'
 group by sp.Mannschaft1, sp.Mannschaft2, s.Name, v1.AnzVerw
having v1.AnzVerw =
    (select max(AnzVerw)
       from VerwJeSpiel)

-- Eine View, die die Spiele der Vorrunde mit den jeweils von beiden
-- Mannschaften erzielten Toren auflistet

create view Vorrunde2 as 
select sp.*,
       (select count(*) 
          from Tore t, Spieler s 
         where t.SpielID = sp.SpielID 
           and t.SpielerNr = s.SpielerNr 
           and ((s.Land = sp.Mannschaft1
           and t.Spielsituation != 'own goal') 
            or (s.Land = sp.Mannschaft2 
           and t.Spielsituation = 'own goal'))) as Tore1, 
       (select count(*) 
          from Tore t, Spieler s 
         where t.SpielID = sp.SpielID 
           and t.SpielerNr = s.SpielerNr 
           and ((s.Land = sp.Mannschaft2 
           and t.Spielsituation != 'own goal') 
            or (s.Land = sp.Mannschaft1
           and t.Spielsituation = 'own goal'))) as Tore2 
  from Spiele sp 
 where Runde like 'Group%'; 

-- Abfrage auf der View
-- Die inneren selects berechnen mit Hilfe des case-Operators die Punkte pro 
-- Spiel, jewels fuer beide Mannschaften
-- Das aeussere select summiert die Punkte pro Mannschaft auf, sortiert
-- entsprechend

select t.Runde, t.Land, sum(t.Punkte) as Gesamtpunkte
from 
  (select Runde, Mannschaft1 as Land, 
    (case when Tore1 < Tore2 then 0 
          when Tore1 = Tore2 then 1 
          when Tore1 > Tore2 then 3 
     end) as Punkte 
     from Vorrunde 
   union all
   select Runde, Mannschaft2 as Land, 
    (case when Tore2 < Tore1 then 0 
     when Tore2 = Tore1 then 1 
     when Tore2 > Tore1 then 3 
     end) as Punkte 
     from Vorrunde) t 
group by t.Runde, t.Land 
order by t.Runde asc, Gesamtpunkte desc;

-- Eine View, die die Spiele pro Mannschaft zaehlt

  create View SpieleJeMannschaft as
  select m.Land, count(*) as Anzahl
    from Spiele s, Mannschaften m
   where s.Mannschaft1 = m.Land
      or s.Mannschaft2 = m.Land
group by m.Land 

-- Das MySQL keine Subselect in der 'from'-clause in Views zulaesst, wird eine
-- Hilfsview definiert, die fuer jedes Gegentor einmal die Mannschaft
-- auflistet, die das Tor kassiert hat.

create view Subselect as
select s.Mannschaft1 as Land
  from Spiele s, Tore t, Spieler sp
 where s.SpielID = t.SpielID
   and s.Mannschaft2 = sp.Land
   and sp.SpielerNr = t.SpielerNr
   and t.Spielsituation != 'own goal'
 union all
select s.Mannschaft2 as Land
  from Spiele s, Tore t, Spieler sp
 where s.SpielID = t.SpielID
   and s.Mannschaft1 = sp.Land
   and sp.SpielerNr = t.SpielerNr
   and t.Spielsituation != 'own goal'
 union all
select sp.Land as Land
  from Spieler sp, Tore t
 where sp.SpielerNr = t.SpielerNr
   and t.Spielsituation = 'own goal'

-- Select, welches ueber die Hilfsview laeuft und die Eintraege pro Mannschaft,
-- also die Anzahl der Gegentore zaehlt

create view GegentoreJeLand as
  (select Land, count(*) as Gegentore
     from Subselect
 group by Land) 

-- Eigentliche Abfrage
-- Das erste innere Select teilt die Anzahl der Gegentore durch die Anzahl der
-- Spiele fuer jede Mannschaft. Das zweite innere select ergaenzt Mannschaften,
-- die keine Gegentore (ausser im Elfmeterschiessen) bekommen haben.
-- Aeusseres select wird benoetigt, um auf der Vereinigungsmenge (union) der
-- beiden inneren Selects sortieren zu koennen

select *
  from (select s.Land, g.Gegentore / s.Anzahl as Rang
          from SpieleJeMannschaft s, GegentoreJeLand g
         where g.Land = s.Land
         union
        select m.Land, 0 as Rang
          from Mannschaften m
         where m.Land not in (select s.Land from GegentoreJeLand s)) tmp
order by Rang