Kurs:Wirtschaftsinformatik SS11 Datenbankmanagementsysteme/Teil 4/Loesungen SS11

Arithmetische Ausdrücke

Einfach

• 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'.

• • select spielminute, nachspielzeit, (spielminute+ COALESCE(nachspielzeit, 0) ) as effektive_spielminute

from spielminute

• • Musterlösung:

select spielminute, nachspielzeit, spielminute+(case when nachspielzeit is null then 0 else nachspielzeit end) as effektive_spielminute from spielminute

Aggregatfunktionen

Einfach

• Geben Sie (mit Duplikaten) Vorname und Nachname aller Spieler aus, die in einem beliebigen Spiel einer Weltmeisterschaft das erste Tor (zum Spielstand 1:0 oder 0:1) geschossen haben.

select vorname, name from person join tor on person.personid=tor.spielerid where (toret1=1 and toret2=0) or (toret1=0 and toret2=1)

• Geben Sie die höchste Anzahl von Toren aus, die je in einer ersten Halbzeit während der regulären Spielzeit von einer Gastmannschaft (toret2) erzielt worden sind.

select max(toret2) from spielabschnitt natural join spielstandabschnitt natural join spiel where bezeichnung = 'Erste Halbzeit'

Musterloesung: select max(toret2) from spielstandabschnitt natural join spielabschnitt where bezeichnung='Erste Halbzeit'

• Geben Sie die Anzahl der Spiele aus, die durch Elfmeterschießen entschieden werden mussten.

Musterloesung:

select count(*) from spielstandabschnitt natural join spielabschnitt where bezeichnung='Elfmeterschießen'

select count (distinct spielid) from spielabschnitt natural join spielstandabschnitt natural join spiel where bezeichnung='Elfmeterschießen'

• Geben Sie die Anzahl der Gelb-Roten-Karten aus, die bisher in allen Weltmeisterschaften je ausgeteilt worden sind.

Musterloesung:

select count(*) from strafe natural join strafetyp where bezeichnung='Gelb-Rote Karte'

select count(strafetypid) from strafetyp natural join strafe where bezeichnung = 'Gelb-Rote Karte'

• Geben Sie die Anzahl der verschiedenen(!) Rückennummern aus, die bisher bei einer Weltmeisterschaft verwendet wurden.

Musterloesung:

select count(distinct rueckennummer) from personnominierung

select count(distinct rueckennummer) from personnominierung natural join nominierung natural join turnier where 'WM' =substring(turniername,1,2) and rueckennummer is not null

• Geben Sie die Anzahl der Personen an, die bisher an einer Weltmeisterschaft teilgenommen haben.

select count(distinct personid) from personnominierung

Musterloesung: SELECT count(personid) FROM person

• Geben Sie die Anzahl der verschiedenen(!) Spieler an, die bisher bei einer Weltmeisterschaft einen Ausgleichstreffer erzielt haben.

select count(distinct spielerid) from tor where toret1 = toret2

Mittel

• Geben Sie die Anzahl der verschiedenen(!) Spieler an, die bisher bei einer Weltmeisterschaft einen Ausgleichstreffer erzielt haben.

SELECT count(distinct spielerid) FROM tor where toret1=toret2

DDL

Einfach

• Erzeugen Sie eine Tabelle 'foul' mit den Attributen 'aktionid', 'vonspielerid' und 'anspielerid'. Alle Attribute sollen vom Typ Integer und Nicht-Null sein. Attribut 'aktionid' soll darüber hinaus Primärschlüssel sein.

create table foul (aktionid integer primary key, vonspielerid integer not null, anspielerid integer not null);

• Erstellen Sie eine Tabelle 'personattr', die (vorerst nur) die Attribute 'personid' (als Integer-Zahl), 'nachname' und 'vorname' (jeweils als Zeichenkette mit 31 Zeichen) sowie geschlecht (als char) enthält.

• • CREATE TABLE personattr

( personid int, nachname varchar(31), vorname varchar(31), geschlecht char )

• Günther Jauch wird für die nächste Weltmeisterschaft nominiert (er wurde am 13. Juli 1956 in Münster geboren). Tragen Sie ihn in die Tabelle 'person' ein (personid=1000000).

• • insert into person (personid, name, vorname, geburtsdatum) values (1000000, 'Jauch', 'Günther', '13-07-1956');

• Alle Spiele, die vor dem 15.06.1970 stattgefunden haben, sollen gelöscht werden.

delete * from spiel where cast(anpfiff as date)-'1970-06-15' < 0

Musterlösung: delete from spiel where anpfiff<'15-06-1970';

• Das in der Datenbank gespeicherte Geburtsdatum von Raul Cordoba ist nicht korrekt. Setzen Sie es auf den 14. April 1924.

UPDATE person SET geburtsdatum = '14 April 1924' WHERE name = Cordoba'

AND vorname = 'Raul'

Musterloesung:

update person set geburtsdatum='14-04-1924' where upper(name)='CORDOBA' and upper(vorname)='RAUL';

• Ergänzen Sie das Eröffnungsspiel der WM 1966 am 11. Juli 1966 um die Bemerkung (im Feld 'bemerkung'): 'Erstes Spiel der WM 1966'. (Hinweis: Am 11. Juli 1966 fand kein weiteres WM-Spiel statt.)

update spiel set bemerkung='Erstes Spiel der WM 1966' where

cast(anpfiff as date) = '1966-07-11'

Musterloesung: update spiel set bemerkung='Erstes Spiel der WM 1966' : where anpfiff='11-07-1966';

DML

Einfach

Ergänzen Sie das Eröffnungsspiel der WM 1966 am 11. Juli 1966 um die Bemerkung (im Feld 'bemerkung'): 'Erstes Spiel der WM 1966'. (Hinweis: Am 11. Juli 1966 fand kein weiteres WM-Spiel statt.)

update spiel set bemerkung='Erstes Spiel der WM 1966' where anpfiff='11-07-1966';

Mittel

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

UPDATE spiel

SET anpfiff = anpfiff + INTERVAL '1 0' DAY TO HOUR

WHERE gruppe = 'Finale';

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.

delete FROM tor
WHERE aktionid IN (select aktionid from tor natural join aktion natural join spiel natural join turnier join person on tor.spielerid=person.personid 
WHERE upper(vorname)='JOSIP' AND upper(name)='SIMUNIC' and jahr=2006);

Gruppierung

Einfach

• Geben Sie für jedes Land, in dem bisher eine Weltmeisterschaft stattgefunden hat, die Anzahl der Weltmeisterschaften an, die das Land bisher insgesamt ausgerichtet hat. Die Ausgabe soll (landname, count) lauten.

• • Musterlösung

select landname, count(landname) as count from turnierausrichtung natural join land group by landname

• • Studentische Lösung

SELECT landname, count(landname)
FROM turnier NATURAL JOIN turnierausrichtung NATURAL JOIN land
GROUP BY landname

• Geben Sie PersonId, Nachname und Vorname einer jeden Person zusammen mit der Häufigkeit aus, mit der sie an einer Weltmeisterschaft (egal in welcher Rolle) teilgenommen hat. Das Ergebnis soll (personid, name, vorname, anzahl) lauten.

• •  SELECT personid,name,  vorname, count(personid) AS anzahl FROM personnominierung natural join person natural join nominierung natural join turnier GROUP BY personid, name,  vorname
    

• • Musterloesung:

     select personid, name, vorname, count(turnierid) as anzahl from person natural join personnominierung natural join nominierung group by personid, name, vorname;
    

• Geben Sie die Häufigkeit aller Rückennummern an, die jemals zu einer Weltmeisterschaft nominiert wurden. Das Ergebnis soll (rueckennummer, anzahl) lauten. Schließen Sie NULL-Werte aus Ihrer Anfrage aus.

• • Studentische Lösung:

    SELECT rueckennummer, count(rueckennummer) AS anzahl
    FROM personnominierung
    GROUP BY rueckennummer
    HAVING rueckennummer IS NOT null
    ORDER BY
    rueckennummer
    

• • Musterlösung

select rueckennummer, count(*) as anzahl from personnominierung where rueckennummer is not null group by rueckennummer

• Geben Sie die SpielerId aller Spieler aus, die je bei einer Weltmeisterschaft ein Tor geschossen haben, zusammen mit der Gesamtanzahl der Tore, die sie bei Weltmeisterschaften insgesamt geschossen haben.

• • Studentische Lösung

SELECT spielerid, count(toret1+toret2)
FROM tor
GROUP BY spielerid
ORDER BY spielerid

• • Musterloesung:

select spielerid, count(spielerid) from tor group by spielerid

• Geben Sie die SpielerId aller Spieler aus, die je bei einer Weltmeisterschaft eine (Gelbe, Gelb-Rote oder Rote) Karte gesehen haben, zusammen mit der Gesamtanzahl der Karten, die sie bei Weltmeisterschaften insgesamt erhalten haben.

select spielerid, count(spielerid) from strafe group by spielerid

Mittel

• Geben Sie für jeden Spieler die Häufigkeit an, mit der er zur Startelf eines beliebigen Spiels bei einer beliebigen Weltmeisterschaft gehörte (beschränken Sie Ihre Ausgabe *der Einfachheit halber* auf jene Spieler, die bisher -bei einem beliebigen Spiel bei einer beliebigen Weltmeisterschaft- mindestens einmal zur Startelf gehörten). Das Ergebnis soll (spielerid, ist_in_start_elf) lauten.

SELECT spielerid, COUNT(spielerid) AS ist_in_start_elf
FROM spielnominierung
WHERE instartelf = true
GROUP BY spielerid;

• Geben Sie PersonId, Vorname und Nachname aller Spieler aus, die für maximal 15 Spiele nominiert wurden.

select personid, vorname, name 
from person join spielnominierung on person.personid=spielnominierung.spielerid 
group by personid, vorname, name having count(spielid)<=15

• Geben Sie PersonId, Vorname und Nachname aller Personen aus, die an mehr als drei Weltmeisterschaften teilgenommen haben.

select personid, vorname, name
from person natural join personnominierung natural join nominierung
group by personid, vorname, name having count(turnierid)>3;

• Geben Sie PersonId, Vorname und Nachname aller Spieler aus, die für mindestens 10 Spiele nominiert wurden.

select personid, vorname, name 
from person join spielnominierung on person.personid=spielnominierung.spielerid 
group by personid, vorname, name 
having count(spielid)>=10

• Geben Sie die Häufigkeit der Spielstände (0:0, 1:0, 3:1 etc.) an, die am Ende von Spielen mit abschließendem Elfmeterschießen vorlagen und bei denen die Gastmannschaft mit mindestens zwei Toren Vorsprung führte. Die Ausgabe soll (toret1, toret2, count) lauten.

• • Studentische Lösung

SELECT toret1, toret2, count(spielid)
FROM spielabschnitt NATURAL JOIN spielstandabschnitt  
WHERE bezeichnung = 'Elfmeterschießen'
GROUP BY toret1, toret2
HAVING toret2 > toret1+1

• • Musterlösung

select toret1, toret2, count(spielid) 
from spielstandabschnitt natural join spielabschnitt 
where bezeichnung='Elfmeterschießen' and (toret1+1)<toret2
group by toret1, toret2

• Geben Sie die Häufigkeit der Spielstände (z.B. 1:0, 3:1, etc.) an, die am Ende der zweiten Halbzeit eines beliebigen Spiels einer beliebigen Weltmeisterschaft vorlagen und bei denen die Heimmannschaft mit mindestens einem Tor Vorsprung führte. Die Ausgabe soll (toret1, toret2, count) lauten.

• • Studentische Lösung

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

• • Musterlösung

select toret1, toret2, count(spielid)
from spielstandabschnitt natural join spielabschnitt
where bezeichnung='Zweite Halbzeit' and toret1>toret2
group by toret1, toret2

• Erstellen Sie eine Liste aus Alter (in Jahren) und Anzahl der Personen, die dieses Alter haben. Nehmen Sie an, dass alle Personen noch leben. Verwenden Sie die AGE-Funktion. Das Ergebnis soll (alter, anzahl) lauten. Lassen Sie bei Ihren Berechnungen NULL-Werte außer Acht.

• • Studentische Lösung

SELECT alter, count (alter) AS anzahl FROM (
SELECT EXTRACT( YEAR FROM AGE(geburtsdatum) ) AS alter
FROM person
WHERE geburtsdatum IS NOT null
) AS altersliste
GROUP BY alter
ORDER BY alter

• • Musterlösung

select extract(year from age(geburtsdatum)) as alter, count(*) as anzahl 
from person where geburtsdatum is not null group by alter;

• Geben Sie SpielerId, Vornamen und Nachnamen aller Personen aus, die je bei einer Weltmeisterschaft eine Gelbe, Gelb-Rote oder Rote Karte gesehen haben. Geben Sie zudem -getrennt nach Kartenart- die Gesamtanzahl der Gelben, Gelb-Roten bzw. Roten Karten an, die die Personen bei Weltmeisterschaften insgesamt gesehen haben. Die Ausgabe soll (spielerid, vorname, name, bezeichnung, count) lauten.

• • Studentische Lösung

SELECT spielerid, vorname, name, bezeichnung, count(bezeichnung)
FROM ( person INNER JOIN strafe ON personid = spielerid ) NATURAL JOIN strafetyp
GROUP BY spielerid, vorname, name, bezeichnung

• • Musterlösung

select spielerid, vorname, name, bezeichnung, count(spielerid) 
from strafe join person on strafe.spielerid=person.personid natural join strafetyp
group by spielerid, vorname, name, bezeichnung;

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.

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 PersonID, Nachname und Vorname aller Spieler an, die in einem einzigen Weltmeisterschaftsspiel gespielt haben (d.h. zur Startelf gehörten oder während eines Spiels eingewechselt wurden).

select * from (select personid, name, vorname from person join spielnominierung on person.personid=spielnominierung.spielerid
where instartelf is true

UNION ALL

select personid, name, vorname
from person join spielerwechsel on person.personid=spielerwechsel.reinspielerid) as spielteilnahme
group by personid, name, vorname having count(personid)=1

• Geben Sie den Namen des Landes an, dessen Team am häufigsten an einer WM teilgenommen hat. Geben Sie zudem die Anzahl der Turniere an, an denen das Land teilgenommen hat. Die Ausgabe soll (landid, landname, count) lauten.

• • Musterloesung:

select landid, landname, count(distinct turnierid) as count
from land natural join teamland natural join team natural join teamnominierung

view group by

landid, landname having count(distinct turnierid) >=ALL (select count(distinct turnierid) as count
from land natural join teamland natural join team natural join teamnominierung

view group by landname )

• • Studentische Lösung

SELECT landid, landname, count(turnierid)
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)

• Geben Sie den Namen des Landes an, dessen Team am häufigsten an einer WM teilgenommen hat. Geben Sie zudem die Anzahl der Turniere an, an denen das Land teilgenommen hat. Die Ausgabe soll (landid, landname, count) lauten.

SELECT landid, landname, count(nominierungid)
FROM land NATURAL JOIN teamland NATURAL JOIN team NATURAL JOIN teamnominierung
GROUP BY landid, landname

Mengen

Einfach

• Geben Sie (ohne Duplikate) SpielerId, Vorname und Nachname aller Spieler aus, die in einer(!) jener Spielminuten ein Tor erzielten, in denen auch der Allzeit-Torschützenkönig Just Fontaine ein Tor erzielte. Geben Sie zudem die Spielminute aus, in der der Torschuss erfolgt ist.

select distinct spielerid, vorname, name, spielminute
from tor join person on tor.spielerid=person.personid natural join aktion natural join spielminute
where spielminute
in

(select distinct spielminute
from tor natural join aktion natural join spielminute join person on tor.spielerid=person.personid
where upper(name)='FONTAINE' and upper(vorname)='JUST')

• Welche Spieler wurden sowohl für das Spiel mit der SpielId 823 als auch für das Spiel mit der SpielId 935 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 = 823
INTERSECT
SELECT personid, name, vorname
FROM ( person INNER JOIN spielnominierung ON personid = spielerid ) NATURAL JOIN spiel
WHERE spielid = 935

• • Musterlösung

select personid, name, vorname from person join spielnominierung on person.personid=spielnominierung.spielerid where spielid=823 and personid in (select personid from person join spielnominierung on person.personid=spielnominierung.spielerid where spielid=935)

• 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

• 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 upper(name), upper(vorname), turniername
from person natural join personnominierung natural join personrolle natural join nominierung natural join turnier
where bezeichnung in ('TW','VT','MI','ST') and (upper(name), upper(vorname)) 
in (select upper(name), upper(vorname)
from person natural join personnominierung natural join personrolle
where bezeichnung='Trainer')

• Geben Sie (ohne Duplikate) PersonId, Nachname und Vorname aller Spieler an, die zwar in einem der Spiele der zweiten Runde einer beliebigen Weltmeisterschaft gespielt haben (d.h. in der Anfangsaufstellung dabei waren oder im Verlauf des Spiels eingewechselt wurden), aber in keinem dieser Spiele ein Tor erzielt haben. (Hinweis! Bitte verwenden Sie die Tabellen 'spielnominierung' und 'spielerwechsel' nicht innerhalb desselben Verbunds, um Problemen mit -leider vorhandenen- Dateninkonsistenzen in diesen Tabellen aus dem Wege zu gehen!)

select distinct personid, name, vorname
from person natural join (select spielerid as personid from spielnominierung natural join spiel
where phase='Zweite Runde' and instartelf

union

select reinspielerid as personid
from spielerwechsel natural join aktion natural join spiel
where phase='Zweite Runde') as t1
where personid not in

(select spielerid from tor natural join aktion natural join spiel where phase='Zweite Runde');

SQL-Abfragen

Einfach

• Geben Sie Vorname, Nachname und Geburtsdatum aller Personen aus, deren Vorname weder 'Paul', noch 'Paulo', noch 'Paolo' lautet.

• • Musterloesung:

select vorname, name, geburtsdatum from person where vorname!='Paul' and vorname!='Paolo' and vorname!='Paulo'

• • Studentische Lösung mit Mengensyntax:

select vorname, name, geburtsdatum from person where vorname not in ('Paul', 'Paulo', 'Paolo' )

Mittel

• Geben Sie (ohne Duplikate) Vornamen und Nachnamen aller Personen aus, deren Nachname aus mindestens 3 Buchstaben besteht sowie ein 'p' beinhaltet und deren Vornamen mit 'M' beginnt.

select distinct vorname, name from person where name like '___%' and name LIKE '%p%' and vorname like 'M%'

• Geben Sie (ohne Duplikate) Vornamen und Nachnamen aller Personen aus, deren Nachname aus mindestens 4 Buchstaben besteht und mit 't' endet.

select distinct vorname, name from person where name like '%___t'

• Geben Sie Name, Vorname und Geburtsdatum aller Personen aus, deren Nachname mit den Buchstaben 'Chi' beginnt

select name, vorname, geburtsdatum from person where name like 'Chi%'

• Geben Sie alle Attribute aller Spiele aus, die in den letzten 5 Jahren stattgefunden haben.

select * from spiel where extract(year from age(anpfiff))<=5

• Geben Sie Vornamen und Nachnamen aller Personen aus, deren Nachname aus mindestens 4 Buchstaben besteht.

select vorname, name from person where name like '____%'

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

select * from spiel where extract(year from age(anpfiff))<30

• Geben Sie alle Attribute aller Personen aus, deren Nachnamen nur aus Großbuchstaben besteht.

select * from person where upper(name)=name

• Geben Sie (ohne Duplikate) Vornamen und Nachnamen aller Personen aus, deren Nachname aus mindestens 4 Buchstaben besteht und mit 't' endet.

select distinct vorname, name from person where name like '___%t'

• Geben Sie Vorname und Nachname aller Personen aus, die im 1. Halbjahr 1969 geboren wurden.

SELECT vorname, name from person WHERE geburtsdatum >= '1969-01-01' and geburtsdatum <= '1969-06-30';

• Geben Sie Vorname, Nachname und Geburtsdatum aller Personen aus, die in den Jahren 1960 bis 1982 geboren wurden.

select vorname, name, geburtsdatum from person where geburtsdatum >= '1960-01-01' and geburtsdatum < '1983-01-01'

• Geben Sie Vornamen und Nachnamen aller Personen aus, deren Nachname aus höchstens 4 Buchstaben besteht und deren Vorname die Buchstabenkombination 'ai' beinhaltet.

select vorname, name from person where name not like '_____%' and vorname like '%ai%'

Schwer

• Geben Sie (ohne Duplikate) Vornamen und Nachnamen aller Personen aus, deren Nachname aus höchstens 5 Buchstaben besteht und deren Vornamen ein 'r' beinhaltet und mit einem 'P' beginnt.

select distinct vorname, name from person where name not like '______%' and vorname LIKE '%r%' and vorname like 'P%'

Verbund

Einfach

• Geben Sie PersonId, Vorname und Nachname aller *Spieler* (!) aus.

select personid, vorname, name from person natural join personnominierung natural join personrolle where upper(bezeichnung) in ('TW','VT','MI','ST');

• Geben Sie Vorname und Nachname aller Personen zusammen mit den Weltmeisterschaften (Spalte 'turniername') aus, an denen sie teilgenommen haben.

select vorname, name, turniername  from person natural join personnominierung natural join nominierung natural join turnier

• Geben Sie (mit Duplikaten) Vorname und Nachname aller Spieler aus, die in einem beliebigen Spiel einer Weltmeisterschaft das erste Tor (zum Spielstand 1:0 oder 0:1) geschossen haben.

select vorname, name from person join tor on person.personid=tor.spielerid where (toret1=1 and toret2=0) or (toret1=0 and toret2=1)

• Geben Sie PersonId, Vorname und Nachname aller *Spieler* (!) aus.

select personid, vorname, name from person natural join personnominierung natural join personrolle where upper(bezeichnung) in ('TW','VT','MI','ST');

• Geben Sie (ohne Duplikate) Vorname und Nachname aller *Spieler* (!) aus, die jemals für Deutschland an einer Weltmeisterschaft teilgenommen haben. (Berücksichtigen Sie dabei auch die Zeit der Teilung Deutschlands sowie die Zeit davor.)

select distinct vorname, name from person natural join personnominierung natural join personrolle natural join teamnominierung_view natural join teamland natural join land where upper(landname) like '%DEUTSCH%' and upper(bezeichnung) in ('TW','VT','MI','ST');

Mittel

• Geben Sie (ohne Duplikate) für alle Spiele aller Weltmeisterschaften die Spielminute (ggf. zzgl. der Minuten der Nachspielzeit) an, in der die Heimmannschaft mit 1:0 in Führung gegangen ist. Geben Sie zudem den Nachnamen und den Ländernamen des Torschützen aus. Bennen Sie die berechnete Spalte 'spielminute'.

select distinct spielminute+case when nachspielzeit is null then 0 else nachspielzeit end as spielminute, name, landname from spielminute natural join aktion natural join tor  join person on tor.spielerid=person.personid natural join personnominierung natural join teamnominierung_view natural join teamland natural join land where toret1=1 and toret2=0;

• Geben Sie (mit Duplikaten) das (heutige) Alter (in ganzen Jahren!) aller *Spieler* (!) an, die mindestens 30 Jahre alt sind (nehmen Sie an, dass alle Spieler noch leben und verwenden Sie die AGE Funktion). Das Ergebnis soll (vorname, nachname, alter) lauten.

select vorname, name as nachname, extract(year from age(geburtsdatum)) as alter from person natural join personnominierung natural join personrolle where bezeichnung in ('TW','VT','MI','ST') AND extract(year from age(geburtsdatum)) >= 30;

• Geben Sie die PersonID aller *Spieler* (!) aus, die im Mai des Jahres 1977 bzw. 1982 geboren worden sind.

select personid from person natural join personnominierung natural join personrolle where bezeichnung in ('TW','VT','MI','ST') and extract(month from geburtsdatum) = 5 and (extract(year from geburtsdatum) in (1977, 1982))

• 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, t1.name heimmannschaft, t2.name AS gastmannschaft, turniername
FROM spielabschnitt NATURAL JOIN spielstandabschnitt NATURAL JOIN spiel
join team as t1 on t1.teamid=spiel.t1id join team as t2 on
t2.teamid=spiel.t2id NATURAL JOIN turnier
WHERE bezeichnung = 'Erste Halbzeit' AND toret1 = 3 AND toret2 = 1

• • Musterlösung

select spielid, t1.name as heimmanschaft, t2.name as gastmannschaft, turniername from spiel join team as t1 on spiel.t1id=t1.teamid join team as t2 on spiel.t2id=t2.teamid natural join turnier natural join spielstandabschnitt join spielabschnitt using (abschnittid) where spielabschnitt.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).

• • Studentische Lösung

SELECT  spielid, phase, gruppe, spieltag, anpfiff, turnierid, t1id, t2id, bemerkung, zuschauer
FROM spiel
join team as t1 on t1.teamid=spiel.t1id join team as t2 on
t2.teamid=spiel.t2id
WHERE ( t1.name = 'Frankreich' OR t2.name = 'Frankreich') AND AGE(anpfiff) <= INTERVAL '30' YEAR

SELECT  spiel.*
FROM spiel
join team as t1 on t1.teamid=spiel.t1id join team as t2 on
t2.teamid=spiel.t2id
WHERE ( t1.name = 'Frankreich' OR t2.name = 'Frankreich') AND AGE(anpfiff) <= INTERVAL '30' YEAR

• • Musterlösung

select spiel.* from spiel join team on (spiel.t1id=team.teamid or spiel.t2id=team.teamid) natural join teamland natural join land where landname='Frankreich' and extract(year from age(anpfiff))<=30;

select spiel.* from spiel join team on (spiel.t1id=team.teamid or spiel.t2id=team.teamid) natural join teamland natural join land where landname='Frankreich' and extract(year from age(anpfiff))<=30;

• Geben Sie (ohne Duplikate) PersonId, Vorname und Nachname aller Spieler aus, die an einem WM-Spiel teilgenommen haben, das mehr als 45 Jahre zurückliegt (verwenden Sie die AGE-Funktion).

SELECT distinct personid, vorname, name AS nachname
FROM ( person INNER JOIN spielnominierung ON personid = spielerid ) NATURAL JOIN spiel
WHERE AGE(anpfiff) > INTERVAL '45' YEAR

select distinct personid, vorname, name from spielnominierung join person on spielnominierung.spielerid=person.personid natural join spiel where extract(year from age(anpfiff)) > 45;

• Geben Sie (ohne Duplikate) PersonId, Vorname und Nachname aller Spieler aus, die an einem WM-Spiel teilgenommen haben, das mehr als 45 Jahre zurückliegt (verwenden Sie die AGE-Funktion).

select distinct personid, vorname, name from spielnominierung join person on spielnominierung.spielerid=person.personid natural join spiel where extract(year from age(anpfiff)) > 45;

• Geben Sie die PersonID aller Torwärte aus, die im Juli zwischen 1965 und 1985 geboren worden sind.

• • Studentische Lösung

SELECT personid
FROM person NATURAL JOIN personnominierung NATURAL JOIN personrolle
WHERE bezeichnung = 'TW' AND EXTRACT( MONTH FROM geburtsdatum) = 7 AND EXTRACT(YEAR FROM geburtsdatum) BETWEEN 1965 AND 1985

• • Musterlösung

select personid from person natural join personnominierung natural join personrolle where bezeichnung='TW' and extract(month from geburtsdatum) = 7 and (extract(year from geburtsdatum) between 1965 And 1985)

Schwer

• Geben Sie das Jahr und den Turniernamen aller Weltmeisterschaften aus. Geben Sie zudem (wenn vorhanden) das Datum und die Teilnehmer der Finalspiele der jeweiligen Weltmeisterschaft aus. Das Ergebnis soll (jahr, turniername, datum, heimmannschaft_name, gastmannschaft_name) lauten. (Beachten Sie, dass es im Jahr 1950 kein "Finale" gab; dennoch soll auch diese Weltmeisterschaft - gemäß dem ersten Satz dieser Aufgabe - Bestandteil der Ausgabe sein.)

select jahr, turniername, datum, heimmannschaft_name, gastmannschaft_name
from turnier natural left join
  (select spielid, turnierid, anpfiff as datum, t1.name as heimmannschaft_name, t2.name as      gastmannschaft_name 
   from spiel join team as t1 on spiel.t1id=t1.teamid join team as t2 on spiel.t2id=t2.teamid
   where gruppe='Finale') as spiel;