Pr asenz ubungsblatt Nr. 6 - Lehrstuhl 6, Fakultät für Informatik

Technische Universit
at Dortmund
Lehrstuhl Informatik VI
Prof. Dr. Joachim Biskup
Grundvorlesung Informationssysteme (SS 2008)
Prof. Dr. Joachim Biskup

Leitung der Ubungen:
Jan-Hendrik Lochner und Torben Weibert
Pr
asenzu
 bungsblatt Nr. 6
Pr
asenzaufgabe 1 (SQL und Relationenalgebra)
Gebt zu den folgenden SQL-Anfragen aquivalente Ausdrucke der Relationenalgebra an:
1. SELECT Pres_Name FROM President WHERE State_Born='Ohio';
2. SELECT * FROM Election
WHERE Election_Year=1968 AND Winner_Loser_Indic='L';
3. SELECT Pres_Name Name FROM President;
4. SELECT Pres_Name FROM Pres_Hobby
WHERE Hobby='Shooting' OR Hobby='Fishing';
5. SELECT * FROM President P, Pres_Hobby H
WHERE P.Pres_Name=H.Pres_Name;
6. SELECT * FROM President P, Pres_Hobby H;
Pr
asenzaufgabe 2 (SQL, hierarchische Anfragen)
In der Datenbank ist in der Tabelle umfasst eine Lebensmittelhierarchie abgespeichert. Die
Tabelle hat die Spalten Gruppe und Nahrungsmittel. Auerdem gibt es die Tabelle Konkretes,
die die Spalten Name und Brennwert besitzt. Auszuge aus umfasst und Konkretes sind auf
Seite 3 dieses Prasenzubungsblatts zu nden.
Gebt zu den folgenden Fragen SQL-Anfragen an.
1. Welche Obstsorten sind in der Datenbank abgespeichert?
2. Welche Sorten Beerenobst haben einen Brennwert von mehr als 50 (kcal/100 g)?
Weiterfuhrende Informationen zu hierarchischen Anfragen nden sich auf Seite 3
dieses Prasenzubungsblatts und ab Seite 8-3 von Oracle9i SQL Reference\ ( Hierarchical Que"
"
ries\).
Hinweis:
Grundvorlesung Informationssysteme
Pr
asenz
ubungsblatt Nr. 6
Pr
asenzaufgabe 3 (SQL-Anfragen)
Stellt Vermutungen an, was die folgenden Oracle-SQL-Anfragen berechnen.
1. SELECT 2+3 FROM DUAL;
2. SELECT USER FROM DUAL;
3. SELECT election_year, candidate,
DECODE(winner_loser_indic, 'W', 'Sieger', '')
FROM election
WHERE election_year >= 1980;
4. SELECT pres_name,
COALESCE(TO_CHAR(death_age), 'Lebt noch') todesalter
FROM president
WHERE birth_year >= 1900
ORDER BY todesalter;
5. WITH pres_jh AS (SELECT p.*, TRUNC((birth_year+99)/100) Jh FROM president p)
SELECT Jh, MIN(death_age), AVG(death_age), MAX(death_age)
FROM pres_jh
GROUP BY Jh
ORDER BY 1;
6. WITH pres_nn AS (
SELECT pres_name, birth_year,
SUBSTR(pres_name, 1, INSTR(pres_name, ' ') - 1) nachname
FROM president)
SELECT p1.pres_name, p1.birth_year, p2.pres_name, p2.birth_year
FROM pres_nn p1, pres_nn p2
WHERE p1.nachname = p2.nachname
AND p1.pres_name != p2.pres_name
AND p1.birth_year < p2.birth_year;
Weiterfuhrende Informationen nden sich in Oracle9i SQL Reference\ unter den
"
Stichworten DUAL\, USER\, DECODE\, COALESCE\, Subquery Factoring Clause\ (WITH
"
"
"
"
"
. . . AS . . . ), SUBSTR\ and INSTR\.
"
"
Hinweis:
2
Grundvorlesung Informationssysteme
Pr
asenz
ubungsblatt Nr. 6
Informationen zu hierarchischen Anfragen
Syntax:
SELECT . . . FROM . . . [WHERE . . . ]
[START WITH condition ]
/* Auswahl der Wurzeln */
CONNECT BY connect by condition . . . /* Verbindung zwischen Eltern und Kindern */
[ORDER SIBLINGS BY . . . ]
/* Ordnung innerhalb der Kinder */
connect by condition ::= PRIOR expr comparison condition expr
expr comparison condition PRIOR expr
Pseudoattribut: LEVEL
j
(gibt die Hierarchie-Ebene jeder ausgegebenen Zeile aus)
Funktion: SYS_CONNECT_BY_PATH(column , char )
(gibt fur column den Pfad von der Wurzel bis zum Attributwert aus und verwendet dabei
char als Trennzeichen)
Auszu
 ge aus den Tabellen umfasst und Konkretes
Konkretes:
umfasst:
GRUPPE
-----------------Pflanzliches
...
Pflanzliches
Pflanzliches
...
Obst
Obst
Obst
Obst
Steinobst
Kernobst
Kernobst
Beerenobst
Beerenobst
Beerenobst
Beerenobst
Johannisbeere
Johannisbeere
Johannisbeere
Beerenobst
Nussobst
Pflanzliches
...
Tierisches
Tierisches
NAHRUNGSMITTEL
-----------------Getreide
NAME
BRENNWERT
------------------------------ ---------Wasser
0
...
Birne
54
Apfel
48
Pflaume
57
Erdbeere
32
Himbeere
35
Stachelbeere
50
Rote Johannisbeere
45
Schwarze Johannisbeere
58
Weie Johannisbeere
37
Brombeere
54
...
Obst
Gemuse
Kernobst
Steinobst
Beerenobst
Nussobst
Pflaume
Apfel
Birne
Erdbeere
Himbeere
Stachelbeere
Johannisbeere
Rote Johannisbeere
Schwarze Johannisbeere
Weie Johannisbeere
Brombeere
Haselnuss
Steinfrucht
Fisch
Geflugel
3