Blatt 5 - Lehrstuhl für Praktische Informatik III

Lehrstuhl für Praktische Informatik III
Prof. Dr. Carl-Christian Kanne
Email: [email protected]
Norman May
B6, 29, Raum C0.05
68131 Mannheim
Telefon: (0621) 181–2517
Email: [email protected]
Datenbanksysteme 1
Sommersemester 06
5. Übungsblatt
31. Mai 2006
Aufgabe 1
21 Punkte
Formulieren Sie folgende Anfragen in SQL. Sie können die Anfragen über die Webschnittstelle testen. Wählen sie die Datenbank terra2 aus.
Aufgabe 1 a)
3 Punkte
Finden Sie die Namen aller Inseln, die (politisch) zu Japan gehören.
Lösung
select
from
where
and
and
and
i.name
land l, landesteil lt, geo insel g, insel i
l.l id = lt.l id
lt.lt id = g.lt id
g.i id = i.i id
l.name = ’Japan’;
Aufgabe 1 b)
4 Punkte
Geben Sie die Namen aller Städte an, die am Rhein liegen.
Lösung
select
from
where
and
and
and
s.name
stadt s,
g.g id =
l.g id =
l.s id =
g.name =
liegt an l, gewaesser g, fluss f
f.g id
g.g id
s.s id
’Rhein’;
Aufgabe 1 c)
4 Punkte
1
Geben Sie die Namen der Staaten an, zu denen eine Insel der Antillen gehört.
Lösung
select
from
where
and
and
and
distinct l.name
land l, landesteil lt, geo insel g, insel i
i.inselgruppe like ’% ntillen%’
i.i id = g.i id
g.lt id = lt.lt id
lt.l id = l.l id;
Aufgabe 1 d)
5 Punkte
Geben Sie die Namen aller Inselstaaten an und zählen Sie aus wieviel Inseln diese
Staaten jeweils bestehen. (Hinweis: Inselstaaten sind diejenigen Staaten, die an keinen
anderen Staat grenzen.)
Lösung
select l.name, count(distinct g.i id)
from
land l, landesteil lt, geo insel g
where l.l id = lt.l id
and
lt.lt id = g.lt id
and
l.l id not in
((select l id1
from benachbart)
union
(select l id2
from benachbart))
group by l.l id, l.name
order by l.name;
Aufgabe 1 e)
7 Punkte
Geben Sie eine Rangliste (d.h. durchnumeriert mit dem jeweiligen Rang) der Länder
in Europa sortiert nach der Fläche an.
Lösung
with Europa(l id, name, flaeche) as
(select l.l id, l.name, l.flaeche
from
land l, umfasst u, kontinent k
2
where l.l id = u.l id
and
u.k id = k.k id
and
k.name = ’Europa’)
select count(*) as Rang, A.Name, A.Flaeche
from
Europa A, Europa B
where (A.Flaeche < B.Flaeche
or
A.L ID = B.L ID)
group by A.L ID, A.Name, A.Flaeche
order by Rang;
Aufgabe 2
10 Punkte
In den folgenden abstrakten SQL-Ausdrücken bezeichnet die Notation {. . . } eine
Menge skalarer Werte, wie sie als Ergebnis einer Unteranfrage berechnet wurde. Sie
stellt eine gültige Eingabe für SOME/ALL dar.
Welche Lösung haben die folgenden abstrakten SQL-Ausdrücke?
(0 = some {})
(0 = some {null})
(2 <> some {null, 1, 2, 3})
(4 <> some {null, 1, 2, 3})
(null = some {null, 1, 2, 3})
(0
= all
{})
(0
= all
{null})
(2 <> all {null, 1, 2, 3})
(4 <> all {null, 1, 2, 3})
(null
= all
{null, 1, 2, 3})
=
=
=
=
=
=
=
=
=
=
Lösung
(0 = some {})
(0 = some {null})
(2 <> some {null, 1, 2, 3})
(4 <> some {null, 1, 2, 3})
(null = some {null, 1, 2, 3})
(0
= all
{})
(0
= all
{null})
(2 <> all {null, 1, 2, 3})
(4 <> all {null, 1, 2, 3})
(null
= all
{null, 1, 2, 3})
Aufgabe 3
=
=
=
=
=
=
=
=
=
=
False
Unknown
True
True
Unknown
True
Unknown
False
Unknown
Unknown
12 Punkte
3
In den folgenden abstrakten SQL-Ausdrücken bezeichnet die Notation {. . . } eine
Menge skalarer Werte, wie sie als Ergebnis einer Unteranfrage berechnet wurde. Sie
stellt eine gültige Eingabe für eine Aggregatfunktion dar.
Welche Lösung haben die folgenden (abstrakten) SQL-Ausdrücke?
count(all{})
max(all{})
count(all{null})
max(all{null, 1})
sum(all{null, 1, 2, 3})
avg(all{1, 1, 2, 2, 3, 3})
=
=
=
=
=
=
count(distinct{})
max(distinct{})
count(distinct{null})
max(distinct{null, 1})
sum(distinct{null, 1, 2, 3})
avg(distinct{1, 1, 2, 2, 3, 3})
=
=
=
=
=
=
Lösung
count(all{})
max(all{})
count(all{null})
max(all{null, 1})
sum(all{null, 1, 2, 3})
avg(all{1, 1, 2, 2, 3, 3})
=
=
=
=
=
=
count(distinct{})
max(distinct{})
count(distinct{null})
max(distinct{null, 1})
sum(distinct{null, 1, 2, 3})
avg(distinct{1, 1, 2, 2, 3, 3})
4
0
Null
0
1
6
2
=
=
=
=
=
=
0
Null
0
1
6
2