Blatt 5 - Universität Mannheim

Universität Mannheim
Lehrstuhl für Praktische Informatik III
Norman May
D7 27, Raum 410
68131 Mannheim
Telefon: (0621) 181-2586
Email: [email protected]
Datenbanksysteme I, SS 2004
Lösungen zum 5. Übungsblatt
1. SQL-Anfragen: Buchladen
Formulieren Sie die folgenden Anfragen in SQL. Alle Anfragen ausser der letzten
entsprechen den Anfragen von Aufgabe 1 des letzten Übungsblatts.
(a) Zählen Sie die Namen aller Verlage auf.
(b) Finden Sie alle Buchtitel aus dem Jahr 2003.
(c) Geben Sie alle Buchtitel an, die mindestens ein Rating haben, das grösser als
3 ist.
(d) Geben Sie Nachname und Vorname aller Autoren des Buches mit dem Titel
“Es” an.
(e) Geben Sie Nachname und Vorname aller Autoren an, die mindestens ein Buch
geschrieben haben.
(f) Geben Sie für jeden Verlag dessen Name und eine Liste von Buchtiteln aus,
die im Jahr 2003 erschienen sind. Verlage ohne Publikationen in diesem Jahr
sollen auch ausgegeben werden.
(g) Geben Sie Nachname und Vorname aller Autoren an, die bei allen Verlagen
mindestens ein Buch geschrieben haben.
Lösungsvorschlag
(a) SELECT Name FROM Verlag;
(b) SELECT DISTINCT Titel FROM Buch WHERE Jahr = 2003;
(c) SELECT
FROM
WHERE
AND
b Titel
Review r, Buch b
r.ISBN = b.ISBN
r.Rating > 3;
1
(d) SELECT
FROM
WHERE
AND
AND
DISINCT a.Nachname, a.Vorname
Buch b, hatGeschrieben h, Autor a
b.ISBN = h.ISBN
h.AutorNr = a.AutorNr
b.Titel = "Es";
(e) SELECT DISTINCT a.Nachname, a.Vorname
FROM
hatGeschrieben h, Autor a
WHERE h.AutorNr = a.AutorNr;
(f) SELECT v.Name, b.Titel
FROM
Verlag v LEFT OUTER JOIN Buch b ON v.Name = b.verlegtBei
WHERE b.Jahr = 2003;
(g)
• Allquantor in Existenzquantor umwandeln:
SELECT a.Nachname, a.Vorname
FROM
Autor a
WHERE NOT EXISTS
(SELECT *
FROM Verlag v
WHERE NOT EXISTS
(SELECT *
FROM Buch b, hatGeschrieben h
WHERE a.AutorNr = h.AutorNr
AND b.ISBN = h.ISBN
AND b.verlegtBei = v.Name));
• GROUP BY und COUNT bei referentieller Integrität:
SELECT a.Nachname, a.Vorname
FROM
Autor a, Buch b, hatGeschrieben h
WHERE a.AutorNr = h.AutorNr
AND b.ISBN = h.ISBN
GROUP BY a.Nachname, a.Vorname
HAVING COUNT(b.verlegtBei) = (SELECT COUNT(v.Name)
FROM Verlag v);
• Mengenoperationen: ziehe vom Kreuzprodukt aus Autoren und Verlagen
die Kobinationen ab, die tatsächlich auftreten.
2. SQL-Anfragen: Terra2-DB (elektronische Abgabe, je 2 Punkte)
Formulieren Sie die folgenden Anfragen in SQL.
(a) Geben Sie eine Liste aller Berge im ’Pamir’-Gebirge aus.
2
(b) Geben Sie den Namen aller Landesteile von China zusammen mit dem Namen
der Hauptstadt des Landesteils aus. Sortieren Sie die Namen der Landesteile
aufsteigend.
(c) Geben Sie die Namen und die Höhe aller Berge in Japan aus. Sortieren Sie die
Ausgabe absteigend nach der Höhe der Berge.
(d) Finden Sie alle Länder, in denen die Hauptstadt mindestens 5-mal weniger Einwohner hat als eine andere Stadt im selben Land. Geben Sie den Landesnamen,
den Namen der Hauptstadt und alle Städte an, die mindestens 5-mal mehr Einwohner haben als die Hauptstadt. Sortieren Sie die Ausgabe aufsteigend nach
Land, Hauptstadt und Stadtname.
Lösungsvorschlag
(a) SELECT Name
FROM
Berg
WHERE Gebirge = ’Pamir’;
(b) SELECT
FROM
WHERE
AND
AND
ORDER BY
(c) SELECT
FROM
WHERE
AND
AND
AND
ORDER
(d) SELECT
FROM
WHERE
AND
AND
AND
AND
ORDER
lt.name, s.name
landesteil lt, land l, stadt s
lt.l_id = l.l_id
lt.hauptstadt = s.s_id
l.name like ’\%China\%’
lt.name;
b.name, b.hoehe
berg b, geo_Berg gb, landesteil lt,
land l
b.b_id = gb.b_id
gb.lt_id = lt.lt_id
lt.l_id = l.l_id
l.name = ’Japan’
BY b.hoehe DESC;
l.name as Land,
s1.Name as Hauptstadt,
s2.Name as Groesser
land l, landesteil lt, Stadt s1,
gehoert_lt glt, Stadt s2
l.hauptstadt = s1.s_id
l.l_id = lt.l_id
glt.lt_id = lt.lt_id
s2.s_id = glt.s_id
s2.Einwohner > 5 * s1.Einwohner
BY l.name, s1.name, s2.name;
3
3. SQL-Anfragen: Terra2-DB (elektronische Abgabe, je 3 Punkte)
Formulieren Sie die folgenden Anfragen in SQL.
(a) Geben Sie den Namen und die Höhe des höchsten Berges von Japan an.
(b) Geben Sie für jedes Gebirge den Namen und die Höhe des höchsten Berges an.
Sortieren Sie die Gebirgsnamen alphabetisch (aufsteigend).
(c) Berechnen Sie für jede Staatsform das durchschnittliche Bruttosozialprodukt
(BSP) und sortieren Sie Staatsformen absteigend nach dem durchschnittlichen
BSP. Geben Sie nur die Staatsformen aus, deren durchschnittliches BSP über
dem durchschnittlichen BSP aller Länder liegt.
Lösungsvorschlag
(a) WITH berg_japan AS
(SELECT b.b_id
FROM
berg b, geo_Berg gb, landesteil lt, land l
WHERE b.b_id = gb.b_id
AND
lt.lt_id = gb.lt_id
AND
lt.l_id = l.l_id
AND
l.Name = ’Japan’)
SELECT
FROM
WHERE
AND
b.name, b.hoehe
berg b, berg_japan bj
b.b_id = bj.b_id
b.hoehe = (SELECT MAX(b2.hoehe)
FROM
berg b2, berg_japan bj2
WHERE bj2.b_id = b2.b_id);
(b) SELECT
FROM
b1.gebirge, b1.name, b1.hoehe
berg b1,
(SELECT
b2.gebirge, MAX(b2.hoehe) as hoechsterberg
FROM
berg b2
GROUP BY b2.gebirge) b3
WHERE
b1.gebirge = b3.gebirge
AND
b1.hoehe = b3.hoechsterberg
ORDER BY b1.gebirge;
(c) SELECT
FROM
GROUP BY
HAVING
ORDER BY
staatsform, avg(BSP) as AVG_BSP
land
staatsform
AVG(BSP) > (SELECT AVG(BSP) FROM land)
AVG_BSP desc;
4