Vorlesung 6 - SQL (Teil 3)

WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
Vorlesung #6
SQL (Teil 3)
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
„Fahrplan“
 Besprechung eines Beispiels, SQL-Übungen
 Quantifizierte Anfragen
 Doppelter NOT EXISTS
 HAVING count =
 JOINs in SQL-92
 CROSS, NATURAL, INNER, LEFT/RIGHT/FULL OUTER
 Rekursion
 Standard SQL (geschachtelt, entschachtelt)
 in ORACLE (CONNECT BY PRIOR)
 in DB2 (WITH View)
© Bojan Milijaš, 07.11.2013
Vorlesung #6 - SQL (Teil 3)
Quantifizierte Anfragen in SQL
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
•
Es gibt keinen expliziten Allquantor
•
Es gibt aber den Existenzquantor: exists, not exists
•
wieder: „Professoren, die keine Vorlesung halten“
select Name, PersNr
from Professoren p
where not exists
( select *
from Vorlesungen
where gelesenVon = p.PersNr );
© Bojan Milijaš, 07.11.2013
Vorlesung #6 - SQL (Teil 3)
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
© Bojan Milijaš, 07.11.2013
Vorlesung #6 - SQL (Teil 3)
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
© Bojan Milijaš, 07.11.2013
Vorlesung #6 - SQL (Teil 3)
Quantifizierte Anfragen
in SQL (4)
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
• Wer hat alle vierstündigen Vorlesungen gehört?
select s.*
from Studenten s
where not exists
(select *
from Vorlesungen v
where v.SWS = 4 and not exists
(select *
from hören h
where h.VorlNr = v.VorlNr and h.MatrNr=s.MatrNr ) );
© Bojan Milijaš, 07.11.2013
Vorlesung #6 - SQL (Teil 3)
Quantifizierte Anfragen
in SQL (5)
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
• Allquantifizierung kann immer auch durch eine count
Aggregation ausgedrückt werden
 Wir betrachten dazu eine etwas einfachere Anfrage,
in der wir die (MatrNr der) Studenten ermitteln wollen,
die alle Vorlesungen hören:
select h.MatrNr
from hören h
group by h.MatrNr
having count (*) = (select count (*) from Vorlesungen);
© Bojan Milijaš, 07.11.2013
Vorlesung #6 - SQL (Teil 3)
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
JOINs in SQL-92






cross join: Kreuzprodukt
natural join: natürlicher Join
join oder inner join: Theta-Join
left outer join: linker äußerer Join
right outer join: rechter äußerer Join
full outer join: äußerer Join
© Bojan Milijaš, 07.11.2013
Vorlesung #6 - SQL (Teil 3)
CROSS JOIN
(Kartesisches Produkt)
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
SELECT *
FROM Studenten CROSS JOIN prüfen;
ist äquivalent zu
SELECT *
FROM Studenten, prüfen;
© Bojan Milijaš, 07.11.2013
Vorlesung #6 - SQL (Teil 3)
NATURAL JOIN
(natürlicher Verbund)
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
SELECT *
FROM Studenten NATURAL JOIN prüfen;
ist äquivalent zu
SELECT s.MatrNr, s.Name, s.Semester,
/* p.MatrNr ausgelassen */
p.VorlNr, p.PersNr, p.Note
FROM Studenten s, prüfen p
WHERE s.MatrNr = p.MatrNr;
© Bojan Milijaš, 07.11.2013
Vorlesung #6 - SQL (Teil 3)
JOIN
(Theta Verbund)
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
SELECT *
FROM Studenten s JOIN prüfen p
ON s.MatrNr = p.MatrNr;
ist äquivalent zu
SELECT *
FROM Studenten s, prüfen p
WHERE s.MatrNr = p.MatrNr;
© Bojan Milijaš, 07.11.2013
Vorlesung #6 - SQL (Teil 3)
JOIN
(Theta Verbund) (2)
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
 Allgemein – setzt die Gleichheit der JoinSpalten nicht voraus:
SELECT *
FROM Professoren p JOIN
Assistenten a
ON p.persnr = a.boss;
© Bojan Milijaš, 07.11.2013
Vorlesung #6 - SQL (Teil 3)
* OUTER JOINs
(äußerer Vebund)
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
SELECT *
FROM Studenten s LEFT OUTER JOIN
pruefen p
ON s.matrnr = p.matrnr;
SELECT *
FROM uni.hoeren h RIGHT OUTER JOIN
uni.Vorlesungen v
ON h.vorlnr = v.vorlnr;
© Bojan Milijaš, 07.11.2013
Vorlesung #6 - SQL (Teil 3)
* OUTER JOINs
(äußerer Vebund) (2)
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
SELECT *
FROM Vorlesungen v FULL OUTER JOIN
Assistenten a
ON v.gelesenvon = a.Boss
© Bojan Milijaš, 07.11.2013
Vorlesung #6 - SQL (Teil 3)
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
Rekursion
 Rekursive Relation voraussetzen in UNI Schema
voraussetzen : {[Vorgänger, Nachfolger]}
 „Welche Vorlesungen muss man hören, um die
Vorlesung „Der Wiener Kreis“ zu verstehen?
SELECT Vorgaenger
FROM voraussetzen vs, Vorlesungen vo
WHERE vs.Nachfolger = vo.VorlNr
AND vo.Titel = 'Der Wiener Kreis' ;
© Bojan Milijaš, 16.11.2012
Vorlesung #7 - SQL (Teil 4)
15
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
Rekursion (2)
 Das sind aber nur die direkten Vorgänger, bzw.
Vorgänger erster Stufe!
 Wie bekommt man alle?
 Zunächst Vorgänger zweiter Stufe:
SELECT Vorgaenger
FROM voraussetzen
WHERE Nachfolger IN (SELECT Vorgaenger
FROM voraussetzen, Vorlesungen
WHERE Nachfolger = VorlNr
AND Titel = 'Der Wiener Kreis');
© Bojan Milijaš, 16.11.2012
Vorlesung #7 - SQL (Teil 4)
16
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
Rekursion (3)
SELECT Vogänger
FROM voraussetzen
WHERE Nachfolger IN (SELECT Vorgänger
FROM voraussetzen, Vorlesungen
WHERE Nachfolger = VorlNr
AND Titel = `Der Wiener Kreis´);
Entschachtelung
SELECT v1.Vogänger
FROM voraussetzen v1, voraussetzen v2, Vorlesungen v
WHERE v1.Nachfolger = v2.Vorgänger
AND v2.Nachfolger = v.VorlNr
AND v.Titel = `Der Wiener Kreis´;
© Bojan Milijaš, 16.11.2012
Vorlesung #7 - SQL (Teil 4)
17
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
Rekursion (4)
 Man kann die gewonenne entschachtelte
Abfrage verallgemeinern für die Vorgänger
n-ten Stufe
SELECT v1.Vorgänger
FROM voraussetzen v1, ..., voraussetzen v_n-1, voraussetzen vn,
Vorlesungen v
WHERE v1.Nachfolger = v2.Vorgänger
AND ...
AND v_n-1.Nachfolger = vn.Vorgänger
AND vn.Nachfolger = VorlNr
AND v.Titel = `Der Wiener Kreis´;
© Bojan Milijaš, 16.11.2012
Vorlesung #7 - SQL (Teil 4)
18
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
Rekursion (5)
 (-) sehr umständlich zu formulieren
 (-) ineffizient bei der Durchführung
 (-) leider in SQL-Standard nicht anders
möglich  SQL ist nicht Turing-vollständig,
SQL ist deklarativ, keine Schleifen, keine
Kontrollverzweigungen, keine GO TO, JUMP
Befehle oder ähnliches
 Das Finden aller Vorgänger nennt man
allgemein „Berechnen der transitiver
Hülle“
© Bojan Milijaš, 16.11.2012
Vorlesung #7 - SQL (Teil 4)
19
Rekursion (6) –
Transitive Hülle voraussetzen
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
Der Wiener Kreis
Wissenschaftstheorie
Erkenntnistheorie
Bioethik
Ethik
Mäeutik
Grundzüge
© Bojan Milijaš, 16.11.2012
Vorlesung #7 - SQL (Teil 4)
20
Rekursion (7) – Transitive Hülle
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
transA,B(R)= {(a,b)  k  IN (1, ..., k  R (
1.A= 2.B 

k-1.A= k.B 
1.A= a 
k.B= b))}
© Bojan Milijaš, 16.11.2012
Vorlesung #7 - SQL (Teil 4)
21
Rekursion (8) –
Oracle CONNECT BY Konstrukt
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
select Titel
from Vorlesungen
where VorlNr in
(select Vorgaenger
from voraussetzen
CONNECT BY Nachfolger = PRIOR Vorgaenger
START WITH Nachfolger =
(select VorlNr
from Vorlesungen
where Titel= 'Der Wiener Kreis'));
© Bojan Milijaš, 16.11.2012
Vorlesung #7 - SQL (Teil 4)
22
Rekursion (9) in IBM DB2
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
with TransVorl (Vorg, Nachf)
as (select Vorgaenger, Nachfolger from voraussetzen
union all
select t.Vorg, v.Nachfolger
from TransVorl t, voraussetzen v
where t.Nachf= v.Vorgaenger)
select Titel from Vorlesungen where VorlNr in
(select Vorg from TransVorl where Nachf in
(select VorlNr from Vorlesungen
where Titel= 'Der Wiener Kreis') );
© Bojan Milijaš, 16.11.2012
Vorlesung #7 - SQL (Teil 4)
23
WS 2013/14
Datenbanksysteme
Do 17:00 – 18:30
R 1.007
Vorlesung #6
Ende