Vorlesung7

SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Vorlesung #7
SQL (Teil 2)
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
„Fahrplan“






Besprechung der Übungsaufgaben
Geschachtelte Anfragen in SQL
Korrelierte vs. Unkorrelierte Anfragen
Entschachtelung der Anfragen
Operationen der Mengenlehre
Spezielle Sprachkonstrukte (BETWEEN,
CASE, LIKE)
 Joins in SQL-92
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
2
Geschachtelte Anfragen
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 In SQL ist es möglich, SELECT Anweisungen
auf viele Weisen zu verknüpfen und zu
verschachteln
 Man unterscheidet zwischen Anfragen, die
ein Tupel (eine Zeile) zurückliefern, von
denen, die mehrere Tupeln (Zeilen) ergeben
 Die Anfragen können dann als Unterfragen in
SELECT, FROM oder WHERE Teil eingesetzt
werden
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
3
Geschachtelte Anfragen (2)
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 ... in WHERE-Klausel
 Welche Prüfungen sind besser als
durchschnittlich verlaufen?
SELECT *
FROM prüfen
WHERE Note < ( select avg (Note)
from prüfen );
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
4
Geschachtelte Anfragen (3)
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 ... in SELECT-Klausel
 Professoren mit deren Lehrbelastung und
durchschnittlicher Lehrbelsatung
SELECT PersNr, Name, sum(SWS) AS Lehrbelastung,
( select avg (sum(SWS))
from Vorlesungen
group by gelesenVon) AS Durchschnitt
FROM Professoren p, Vorlesungen v
WHERE p.PersNr = v.gelesenVon
GROUP BY PersNr, Name;
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
5
Geschachtelte Anfragen (4)
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 ... in SELECT-Klausel
 Professoren mit deren Lehrbelastung und
durchschnittlicher Lehrbelsatung – korreliert ohne
GROUP BY:
SELECT PersNr, Name,
(select sum (SWS)
from Vorlesungen
where gelesenVon = p.PersNr) AS Lehrbelastung,
(select sum(SWS) / count(DISTINCT gelesenVon)
from Vorlesungen) AS Durchschnitt
FROM Professoren p
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
6
Geschachtelte Anfragen (5)
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 ... in FROM-Klausel
 „fleißige“ Studenten – die mehr als 2 Vorlesungen
hören:
SELECT tmp.MatrNr, tmp.Name, tmp.VorlAnzahl
FROM (select s.MatrNr, s.Name,
count(*) as VorlAnzahl
from Studenten s, hoeren h
where s.MatrNr=h.MatrNr
group by s.MatrNr, s.Name) tmp
WHERE tmp.VorlAnzahl > 2;
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
7
Korreliert vs. unkorreliert
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 Achtung: funktioniert in der Original-BeispielDatenbank UNI nicht, da GebDatum fehlt!
 Alle Studenten, die älter als der jüngste
Professor sind - korrelierte Formulierung
select s.*
from Studenten s
where exists
(select p.*
from Professoren p
where p.GebDatum > s.GebDatum);
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
8
Korreliert vs. Unkorreliert (2)
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 Äquivalente unkorrelierte Formulierung
select s.*
from Studenten s
where s.GebDatum <
(select max (p.GebDatum)
from Professoren p);
 Vorteil: Unteranfrageergebnis kann materialisiert werden
 Unteranfrage braucht nur einmal ausgewertet zu werden
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
9
Entschachtelung korrelierter
Unteranfragen
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Assistenten, die für eine(n) jüngere(n) Professor(in) arbeiten
select a.*
from Assistenten a
where exists
( select p.*
from Professoren p
where a.Boss = p.PersNr and p.GebDatum > a.GebDatum);
Entschachtelung durch Join
select a.*
from Assistenten a, Professoren p
where a.Boss = p.PersNr
and p.GebDatum > a.GebDatum;
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
10
Operationen der Mengenlehre
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Vereinigung – UNION bzw. UNION ALL
Durchschnitt – INTERSECT
Differenz – MINUS (auch EXCEPT)
UNION, INTERSECT und MINUS setzen
Schemagleichheit voraus
 Der Operator IN bzw. NOT IN testet auf
Mengenmitgliedschaft
 Der Operator ALL testet, ob alle Ergebnisse
der Unteranfrage den Vergleich erfüllen




© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
11
Operationen der Mengenlehre (2)
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
• UNION – mit Duplikatelimierung,
• UNION ALL – ohne Duplikateliminierung
(
select Name
from Assistenten )
union
(
select Name
from Professoren );
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
12
Operationen der Mengenlehre (3)
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 Mengendurchschnitt – INTERSECT
 Alle Professoren, die eine Vorlesung halten
SELECT PersNr
FROM Professoren
INTERSECT
SELECT gelesenVon
FROM Vorlesungen
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
13
Operationen der Mengenlehre (4)
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 Gleiche Abfrage, „alle Professoren, die eine
Vorlesung halten“ mit IN Operator
SELECT PersNr
FROM Professoren
WHERE PersNr IN (SELECT gelesenVon
FROM Vorlesungen);
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
14
Operationen der Mengenlehre (5)
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 Mengendifferenz – MINUS
 Alle Professoren, die keine Vorlesung halten
SELECT PersNr
FROM Professoren
MINUS
SELECT gelesenVon
FROM Vorlesungen;
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
15
Operationen der Mengenlehre (6)
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 Gleiche Abfrage, „alle Professoren, die keine
Vorlesung halten“ mit NOT IN
SELECT PersNr
FROM Professoren
WHERE PersNr NOT IN
( SELECT gelesenVon
FROM Vorlesungen);
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
16
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
ALL Operator
 Studenten mit der größten Semesterzahl
SELECT Name, Semester
FROM Studenten
WHERE Semester >= ALL (select Semester
from Studenten);
äquivalent
SELECT Name, Semester
FROM Studenten
WHERE Semester >= (select max(Semester)
from Studenten);
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
17
Spezielle Sprachkonstrukte
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 BETWEEN
select * from Studenten
where Semester > = 1 and Semester < = 4;
select * from Studenten
where Semester between 1 and 4;
select * from Studenten where Semester in (1,2,3,4);
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
18
Spezielle Sprachkonstrukte (2)
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 CASE WHEN ... THEN ... ELSE ... END – die erste
qualifizierende WHEN Klausel wird ausgeführt
select MatrNr,
( case when Note < 1.5 then ´sehr gut´
when Note < 2.5 then ´gut´
when Note < 3.5 then ´befriedigend´
when Note < 4.0 then ´ausreichend´
else ´nicht bestanden´end)
from pruefen;
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
19
Spezielle Sprachkonstrukte (3)
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
 LIKE Operator – Vergleich von Zeichenketten
 "%" steht für beliebig viele (auch gar kein) Zeichen
 "_" steht für genau ein Zeichen
select * from Studenten
where Name like ´T%eophrastos´;
select distinct Name
from Vorlesungen v, hören h, Studenten s
where s.MatrNr = h.MatrNr and h.VorlNr = v.VorlNr and
v.Titel LIKE ´%thik%´;
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
20
Quantifizierte Anfragen in SQL
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
•
Es gibt keinen expliziten Allquantor
•
Es gibt aber den Existenzquantor: exists, not exist
•
wieder: „Professoren, die keine Vorlesung halten“
select Name, PersNr
from Professoren p
where not exists
( select *
from Vorlesungen
where gelesenVon = p.PersNr );
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
21
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
22
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
23
Quantifizierte Anfragen
in SQL (5)
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
• 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š, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
24
Quantifizierte Anfragen
in SQL (4)
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
• 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š, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
25
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
JOINs in SQL-92




cross join: Kreuzprodukt
natural join: natürlicher Join
join oder inner join: Theta-Join
left, right oder full outer join: äußerer Join
select *
from R1, R2
where = R1.A = R2.B;
select *
from R1 join R2 on R1.A = R2.B;
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
26
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
27
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
28
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
JOINs in SQL-92

FULL OUTER JOIN
select p.PersNr, p.Name, f.PersNr, f.Note,
f.MatrNr, s.MatrNr, s.Name
from Professoren p full outer join
(pruefen f full outer join Studenten s
on f.MatrNr= s.MatrNr)
on p.PersNr=f.PersNr;
© Bojan Milijaš, 28.04.2004
Vorlesung #7 - SQL (Teil 2)
29
SS 2004
Datenbanken 4W
Mi 13:30 – 15:00
G 2.30
Vorlesung #7
Ende