Kurs Diskussion:Wirtschaftsinformatik SS11 Datenbankmanagementsysteme/Teil 4/Loesungen SS11

Arithmetische Ausdrücke

Einfach

Aufgabenstellung: Geben Sie die Spalten Spielminute und Nachspielzeit der Tabelle Spielminute aus zusammen mit einer Spalte, die die Summe beider Werte angibt (beachten Sie, dass die Nachspielzeit NULL sein kann). Bennen Sie die berechnete Spalte 'effektive_spielminute'.

Lösungsansatz:

Das Problem bei dieser Aufgabe ist ja, dass einige Tupel in der Spalte Nachspielzeit den Wert Null haben. Addiert man eine Spielminute(z.B. =90) + Nachspielzeit(=NULL) so erhält man als Lösung NULL (und nicht 90). Man muss also eine Fallunterscheidung machen:

wenn Nachspielzeit einen Zahlenwert hat dann addiert man Spielminute+Nachspielzeit=Effektive_Spielminute. Ansonsten (Nachspielzeit=NULL) gilt Spielminute=Effektive_Spielminute.

Danach werden beider Ergebnisrelationen vereinigt, müsste doch eigentlich gehen?

• • • SQL-STATEMENT***

UNION

• • • SQL-STATEMENT***

Error: FEHLER: ResultException: The number of result tupel differs from the number of expected tupels

Ich habe die Anzahl der Tupel in der Relation spielminute gezählt, es sind 4012. Der erste Block unserer Anweisung gibt 3917 Tupel aus. Der zweite Block 95. Vereinigt also 3917+95=4012

Stehn wir aufm Schlauch, oder ist die hinterlegte Lösung falsch?

Danke schonmal für eure Hilfe!

Hallo!

UNION ist eine Mengenoperation. Als solche löscht es standardmäßig alle Duplikate. Der gepostete UNION liefert folglich nur noch 133 Datensätze...

Mit UNION ALL kann man die Löschung der Duplikate verhindern. Dann liefert der UNION alle 4012 Datensätze (und würde somit eine gültige Lösung darstellen!)...

DML

Mittel

• Verschieben Sie das Datum aller Finalspiele um einen Tag nach hinten.

UPDATE spiel
SET anpfiff = anpfiff + INTERVAL '1 DAY'
WHERE spieltag = ( SELECT MAX(spieltag) FROM spiel )

Schwer

• In einem Vorrunden-Spiel der WM 2006 gegen Australien bekam der Kroate Josip Simunic in der 90. Spielminute irrtümlich eine zweite Gelbe Karte (ehe er bei einem dritten Vergehen mit Gelb-Rot vom Platz gestellt wurde). Ändern Sie die zweite Gelbe Karte in eine Gelb-Rote Karte. Verwenden Sie SELECT-Anweisung um die entsprechenden IDs (strafetypid und aktionid) festzustellen.

• • WHERE-Klausel

SELECT distinct strafetypid, aktionid
FROM ( turnier NATURAL JOIN spiel NATURAL JOIN spielnominierung NATURAL JOIN person NATURAL JOIN strafe NATURAL JOIN strafetyp ) NATURAL JOIN aktion NATURAL JOIN spielminute
WHERE name = 'Simunic' AND vorname = 'Josip' AND bezeichnung = 'Gelbe Karte' AND turniername LIKE 'WM%2006' AND spielminute = 90 AND phase = 'Gruppenspiele' AND spieltag = 25

Gruppierung

Einfach

• Geben Sie die Häufigkeit der Spielstände (0:0, 0:1, 1:0, 3:1 etc.) an, die am Ende der ersten Halbzeit eines beliebigen Spiels einer beliebigen Weltmeisterschaft vorlagen. Die Ausgabe soll (toret1, toret2, count) lauten.

SELECT toret1, toret2, count(spielid)
FROM spielstandabschnitt NATURAL JOIN spielabschnitt
WHERE bezeichnung = 'Erste Halbzeit' 
GROUP BY toret1, toret2

• Geben Sie die Häufigkeit der Nachnamen aller Personen aus, die jemals an einer Weltmeisterschaft teilgenommen haben. Das Ergebnis soll aus den Spalten (nachname, anzahl) bestehen.

SELECT name AS nachname, count(name) AS anzahl
FROM person NATURAL JOIN personnominierung
WHERE nominierungid is not null
GROUP BY name

SELECT name AS nachname, count(name) AS anzahl
FROM person INNER JOIN spielnominierung ON spielerid = personid
GROUP BY name
ORDER BY name

• Geben Sie die Häufigkeit der Geburtsdaten der Personen aus, die jemals an einer Weltmeisterschaft teilgenommen haben. Das Ergebnis soll aus (geburtsdatum, anzahl) bestehen. Schließen Sie NULL-Werte aus Ihrer Anfrage aus.

SELECT distinct geburtsdatum, count(geburtsdatum) AS anzahl
FROM person NATURAL JOIN personnominierung
GROUP BY geburtsdatum
HAVING geburtsdatum IS NOT null
ORDER BY geburtsdatum

Mittel

• Geben Sie für jeden Spieler die Häufigkeit an, mit der er während eines beliebigen Spiels bei einer beliebigen Weltmeisterschaft eingewechselt wurde (beschränken Sie Ihre Ausgabe *der Einfachheit halber* auf jene Spieler, die bisher -bei einer beliebigen Weltmeisterschaft- mindestens einmal eingewechselt wurden). Das Ergebnis soll (spielerid, anzahl_einwechslungen) lauten.

SELECT spielerid, count(reinspielerid) AS anzahl_einwechslungen
FROM (person INNER JOIN spielerwechsel ON personid = reinspielerid) INNER JOIN spielnominierung ON personid = spielerid
WHERE instartelf is false
GROUP BY spielerid
HAVING count(reinspielerid) >= 1

• Geben Sie alle Tore aus, die im selben Spiel in der selben Spielminute erzielt worden sind. Lassen Sie dabei Nachspielminuten sowie Tore, deren Spielminute nicht erfasst wurde (d.h. spielminute=0), außer Acht. Die Ausgabe soll (spielid, spielminute) lauten.

SELECT spielid, spielminute
FROM spielminute NATURAL JOIN aktion NATURAL JOIN tor
WHERE nachspielzeit is null
GROUP BY spielid, spielminute
HAVING spielminute <> 0

• Geben Sie für jeden *Spieler* (!) an, wie oft er an einer Weltmeisterschaft teilgenommen hat. Das Ergebnis soll (personid, vorname, nachname, anzahl_nominierung) lauten.

SELECT personid, vorname, name AS nachname, count(nominierungid) AS anzahl_nominierung
FROM (spielnominierung INNER JOIN person ON spielerid = personid) NATURaL JOIN personnominierung NATURAL JOIN nominierung NATURAL JOIN turnier
WHERE turniername like 'WM%'
GROUP BY personid, vorname, name

Es liegt auchnicht an den Nullmarken

SELECT personid, vorname, name AS nachname, count(nominierungid) AS anzahl_nominierung
FROM (spielnominierung INNER JOIN person ON spielerid = personid) NATURaL JOIN personnominierung NATURAL JOIN nominierung NATURAL JOIN turnier
WHERE turniername like 'WM%' AND nominierungid IS NOT null
GROUP BY personid, vorname, name

Schwer

• Geben Sie PersonID, Vorname und Nachname aller Personen an, die mehr Tore geschossen haben als Sandor Kocsis - durchschnittlich gesehen über alle seine WM-Spiele, bei denen er nominiert wurde.

1. Lösung ohne upper

select personid, vorname , name from (person inner join tor on person.personid=tor.spielerid)
group by personid, vorname , name
having ( count(aktionid) >
(select (1.0*count(tor.aktionid))/(1.0*count(spielnominierung.spielid)) from (person inner join tor on person.personid=tor.spielerid) right join spielnominierung on person.personid=spielnominierung.spielerid where name = 'Kocsis' and vorname = 'Sandor'))

• Geben Sie PersonID, Vorname und Nachname aller Personen an, die mehr Tore geschossen haben als Sandor Kocsis - durchschnittlich gesehen über alle seine WM-Spiele, bei denen er nominiert wurde.

2. Lösung mit upper

select spielerid as personid, vorname, name from tor 
join person on person.personid=tor.spielerid 
group by spielerid, vorname, name 
having count(aktionid) > (select (select 1.0*count(spielerid) from tor 
join person on person.personid=tor.spielerid 
where upper(name)='KOCSIS' and upper(vorname)='SANDOR') / (select 1.0*count(spielid) from spielnominierung 
join person on person.personid=spielnominierung.spielerid 
where upper(name)='KOCSIS' and upper(vorname)='SANDOR'))

Komplexe Anfragen

Einfach

• Geben Sie die Namen jener Länder aus, die am wenigsten oft an einer Weltmeisterschaft teilgenommen haben.

SELECT landname
FROM land NATURAL JOIN teamland NATURAL JOIN teamnominierung NATURAL JOIN nominierung 
GROUP BY landid, landname
HAVING count(turnierid) <= ALL ( SELECT count(turnierid) AS anzahl
FROM land NATURAL JOIN teamland NATURAL JOIN teamnominierung NATURAL JOIN nominierung 
GROUP BY landid, landname)

Mittel

• Geben Sie PersonID, Nachname und Vorname aller Spieler an, die bei der Weltmeisterschaft 1998 mehr Tore geschossen haben als die durchschnittliche Anzahl von Toren (pro Spiel und mit Nachkommastellen!), die in allen Spielen der zweiten Runde aller bisheriger Weltmeisterschaften geschossen wurden.

• • Studentische Lösung

Oberanfrage

SELECT personid, name AS nachname, vorname
FROM turnier NATURAL JOIN spiel NATURAL JOIN aktion NATURAL JOIN ( tor INNER JOIN person ON spielerid = personid)
WHERE turniername LIKE 'WM%1998' AND (toret1+toret2) > ALL ( SELECT AVG(durchschnittstore) FROM
( SELECT spielid, AVG( toret1 + toret2 ) 
FROM tor NATURAL JOIN aktion NATURAL JOIN spiel
WHERE phase = 'Zweite Runde'
GROUP BY spielid ) AS durchschnittstore )

Oberanfrage:

SELECT personid, name AS nachname, vorname
FROM turnier NATURAL JOIN spiel NATURAL JOIN aktion NATURAL JOIN ( tor INNER JOIN person ON spielerid = personid)
WHERE turniername LIKE 'WM%1998'

Unteranfrage:

SELECT AVG(durchschnittstore) FROM
( SELECT spielid, AVG( toret1 + toret2 )
FROM tor NATURAL JOIN aktion NATURAL JOIN spiel
WHERE phase = 'Zweite Runde'
GROUP BY spielid ) AS durchschnittstoreProSpiel

• • Gesamtanfrage

SELECT distinct personid, vorname, name
FROM ( person INNER JOIN tor ON personid = spielerid ) NATURAL JOIN spielnominierung NATURAL JOIN spiel NATURAL JOIN turnier
WHERE turniername LIKE '%1998' AND (toret1+toret2) > ALL ( SELECT AVG(durchschnittstore) FROM
( SELECT spielid, AVG( toret1 + toret2 )
FROM tor NATURAL JOIN aktion NATURAL JOIN spiel
WHERE phase = 'Zweite Runde'
GROUP BY spielid ) AS durchschnittstore )

Mengen

Einfach

• Geben Sie die PersonId aller Spieler aus, deren *letzte*(!) Nominierung zu einem Weltmeisterschafts*spiel* mehr als 10 Jahre zurückliegt. (Musterlösung aktualisiert am 06.06.2011, 17h04!)

• • Studentische Lösung

SELECT personid
FROM ( person INNER JOIN spielnominierung ON personid = spielerid ) NATURAL JOIN spiel
WHERE EXTRACT( YEAR FROM AGE(anpfiff) ) > 10
EXCEPT
SELECT personid
FROM ( person INNER JOIN spielnominierung ON personid = spielerid ) NATURAL JOIN spiel
WHERE EXTRACT( YEAR FROM AGE(anpfiff) ) <= 10

select distinct personid from person join spielnominierung on spielerid=personid where personid not in (select personid from person join spielnominierung on person.personid=spielnominierung.spielerid natural join spiel where extract(year from age(anpfiff))<=10)

• Geben Sie (ohne Duplikate) PersonId, Vorname und Nachname aller Spieler an, die nur an Spielen der zweiten Runde teilgenommen und dort mindestens ein Tor geschossen haben.

• • Studentische Lösung

SELECT personid, vorname, name
FROM ( tor INNER JOIN person ON spielerid = personid ) NATURAL JOIN spielnominierung NATURAL JOIN spiel
WHERE (toret1 > 0 OR toret2 > 0) 
EXCEPT
SELECT personid, vorname, name
FROM ( tor INNER JOIN person ON spielerid = personid ) NATURAL JOIN spielnominierung NATURAL JOIN spiel
WHERE phase <> 'Zweite Runde'

• • Musterlösung

select distinct personid, vorname, name from person join tor on person.personid=tor.spielerid natural join aktion natural join spiel where phase='Zweite Runde' and personid not in (select personid from person join spielnominierung on person.personid=spielnominierung.spielerid natural join spiel where phase<>'Zweite Runde')

• Geben Sie die PersonId aller Spieler aus, deren *letzte*(!) Nominierung zu einem Weltmeisterschafts*spiel* mehr als 10 Jahre zurückliegt. (Musterlösung aktualisiert am 06.06.2011, 17h04!)

SELECT distinct personid
FROM person NATURAL JOIN personnominierung NATURAL JOIN nominierung NATURAL JOIN turnier
WHERE EXTRACT( YEAR from current_date ) - jahr > 10

• Welche Spieler wurden für das Spiel mit der SpielId 1034, aber nicht für das Spiel mit der SpielId 1129 nominiert? Das Ergebnis soll (personid, name, vorname) lauten.

• • Studentische Lösung

SELECT personid, name, vorname
FROM person INNER JOIN spielnominierung ON personid = spielerid NATURAL JOIN spiel
WHERE spielid = 1034
EXCEPT
SELECT personid, name, vorname
FROM person INNER JOIN spielnominierung ON personid = spielerid NATURAL JOIN spiel
WHERE spielid = 1129

• • Musterlösung

select personid, name, vorname from person join spielnominierung on person.personid=spielnominierung.spielerid where spielid=1034 and personid not in (select personid from person join spielnominierung on person.personid=spielnominierung.spielerid where spielid=1129)

Schwer

• Welche Spieler haben sowohl in dem Spiel mit der SpielId 787 als auch in dem Spiel mit der SpielId 837 *gespielt* (!) (d.h. waren gleich in der Anfangsaufstellung dabei oder wurden im Verlauf des Spiels eingewechselt)? Das Ergebnis soll (personid, name, vorname) lauten.

SELECT personid, name, vorname
FROM
(spielnominierung INNER JOIN person ON spielerid = personid)
WHERE spielid = 787

INTERSECT

SELECT personid, name, vorname
FROM
(spielnominierung INNER JOIN person ON spielerid = personid)
WHERE spielid = 837

SELECT personid, name, vorname
FROM
(spielnominierung INNER JOIN person ON spielerid = personid)
WHERE spielid = 787 

INTERSECT

SELECT personid, name, vorname
FROM spielnominierung INNER JOIN person ON spielerid = personid
WHERE spielid = 837


INTERSECT

(
SELECT personid, name, vorname
FROM spielnominierung NATURAL JOIN (person INNER JOIN spielerwechsel ON personid = reinspielerid )
WHERE spielid = 837 

UNION

SELECT personid, name, vorname
FROM spielnominierung NATURAL JOIN (person INNER JOIN spielerwechsel ON personid = reinspielerid )
WHERE spielid = 787
)

***


select * from ( select personid, name, vorname from person join spielnominierung on person.personid=spielnominierung.spielerid 
where instartelf and spielid=837 
union 
select personid, name, vorname from person join spielerwechsel on person.personid=spielerwechsel.reinspielerid natural join aktion where spielid=837) as t1 
intersect 
select * from ( select personid, name, vorname from person join spielnominierung on person.personid=spielnominierung.spielerid 
where instartelf and spielid=787 
union 
select personid, name, vorname from person join spielerwechsel on person.personid=spielerwechsel.reinspielerid natural join aktion where spielid=787) as t2

Verbund

Mittel

• Geben Sie die SpielId aller Spiele aus, in denen es nach Abpfiff der ersten Halbzeit 3:1 für die Heimmannschaft stand. Geben Sie zudem die Namen der beiden Teams (bennen Sie die Spalten 'heimmannschaft' bzw. 'gastmannschaft') sowie den Namen des Turniers aus.

SELECT spielid, t1id heimmannschaft, t2id AS gastmannschaft, turniername
FROM spielabschnitt NATURAL JOIN spielstandabschnitt NATURAL JOIN spiel NATURAL JOIN turnier
WHERE bezeichnung = 'Erste Halbzeit' AND toret1 = 3 AND toret2 = 1

• Geben Sie alle Attribute der Weltmeisterschaftsspiele Frankreichs aus, die nicht länger als 30 Jahre zurückliegen (benutzen Sie die AGE Funktion).

SELECT distinct *
FROM spiel INNER JOIN team ON ( teamid = t1id OR teamid = t2id)
WHERE name = 'Frankreich' AND EXTRACT( YEAR FROM AGE(anpfiff) ) <= 30

SELECT distinct *
FROM spiel INNER JOIN team ON ( teamid = t1id OR teamid = t2id)
WHERE name = 'Frankreich' AND AGE(anpfiff) <= INTERVAL '30' YEAR

• Geben Sie den Turniernamen aller Weltmeisterschaften aus, an denen Ernst Happel teilgenommen hat. Geben Sie zudem die Rollenbezeichnung aus sowie den Namen des Landes, für das Ernst Happel an der Weltmeisterschaft teilgenommen hat. (Tip: Verwenden Sie die UPPER-Funktion, um Vor- und Nachnamen in Großbuchstaben umzuwandeln.)

SELECT distinct turniername, bezeichnung, landname
FROM personrolle NATURAL JOIN personnominierung  NATURAL JOIN person INNER JOIN spielnominierung ON personid = spielerid NATURAL JOIN spiel NATURAL JOIN turnier NATURAL JOIN turnierausrichtung NATURAL JOIN land
WHERE UPPER(vorname) = 'ERNST' AND UPPER(name) = 'HAPPEL'

• Geben Sie Namen und Vornamen aller Spieler aus, die auch als Trainer an einer Weltmeisterschaft teilgenommen haben. Geben Sie zudem die Namen der Turniere aus, an denen die betreffenden Personen als Spieler teilgenommen haben. Das Ergebnis soll (name, vorname, turniername) sein. (TIP: Joinen Sie über Vor- und Nachnamen in Großbuchstaben und geben Sie Vor- und Nachnamen in Großbuchstaben aus!)

SELECT distinct name, vorname, turniername

FROM

personrolle NATURAL JOIN personnominierung NATURAL JOIN person NATURAL JOIN spielnominierung NATURAL JOIN spiel NATURAL JOIN turnier

WHERE bezeichnung IN ('VT','ST','TW','MI') AND

upper(vorname || name) IN (

Select upper(vorname||name) from person NATURAL JOIN personnominierung NATURAL JOIN personrolle
WHERE bezeichnung = 'Trainer'
)