SQL (Teil 2)

WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
Vorlesung #5
SQL (Teil 2)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
„Fahrplan“






Einschub: NULL Werte, median
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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
2
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
median Funktion
 Median (Zentralwert) – unterhalb und oberhalb des
Medians liegt jeweils die Hälfte der Messwerte
 Bei der ungeraden Anzahl der Messwerte ist Median
ein tatsächlich auftretender Wert
(1,1,3,4,5,16,29)  median = 4 (avg=8.42)
 Bei geraden Anzahl der Messwerte ist Median
Mittelwert der beiden mittleren Werte
(1,3,4,5,16,29)  median = 4.5
 Median ist unempfindlich gegenüber Ausreißern
© Bojan Milijaš, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
3
Geschachtelte Anfragen
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
4
Geschachtelte Anfragen (2)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 ... 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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
5
Geschachtelte Anfragen (3)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 ... in SELECT-Klausel
 Professoren mit deren Lehrbelastung und
durchschnittlicher Lehrbelastung
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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
6
Geschachtelte Anfragen (4)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 ... 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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
7
Geschachtelte Anfragen (5)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 ... 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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
8
Korreliert vs. unkorreliert
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
9
Korreliert vs. Unkorreliert (2)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 Ä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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
10
Entschachtelung korrelierter
Unteranfragen
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
11
Operationen der Mengenlehre
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
12
Operationen der Mengenlehre (2)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
• UNION – mit Duplikatelimierung,
• UNION ALL – ohne Duplikateliminierung
(
select Name
from Assistenten )
union
(
select Name
from Professoren );
© Bojan Milijaš, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
13
Operationen der Mengenlehre (3)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 Mengendurchschnitt – INTERSECT
 Alle Professoren, die eine Vorlesung halten
SELECT PersNr
FROM Professoren
INTERSECT
SELECT gelesenVon
FROM Vorlesungen
© Bojan Milijaš, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
14
Operationen der Mengenlehre (4)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 Gleiche Abfrage, „alle Professoren, die eine
Vorlesung halten“ mit IN Operator
SELECT PersNr
FROM Professoren
WHERE PersNr IN (SELECT gelesenVon
FROM Vorlesungen);
© Bojan Milijaš, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
15
Operationen der Mengenlehre (5)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 Mengendifferenz – MINUS
 Alle Professoren, die keine Vorlesung halten
SELECT PersNr
FROM Professoren
MINUS
SELECT gelesenVon
FROM Vorlesungen;
© Bojan Milijaš, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
16
Operationen der Mengenlehre (6)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
17
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
18
Spezielle Sprachkonstrukte
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
19
Spezielle Sprachkonstrukte (2)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
20
Spezielle Sprachkonstrukte (3)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
21
Quantifizierte Anfragen in SQL
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
•
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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
22
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
© Bojan Milijaš, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
23
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
© Bojan Milijaš, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
24
Quantifizierte Anfragen
in SQL (4)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
• 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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
25
Quantifizierte Anfragen
in SQL (5)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
• 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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
26
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
27
CROSS JOIN
(Kartesisches Produkt)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
SELECT *
FROM Studenten CROSS JOIN prüfen;
ist äquivalent zu
SELECT *
FROM Studenten, prüfen;
© Bojan Milijaš, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
28
NATURAL JOIN
(natürlicher Verbund)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
29
JOIN
(Theta Verbund)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
30
JOIN
(Theta Verbund) (2)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 Allgemein – setzt die Gleichheit der JoinSpalten nicht voraus:
SELECT *
FROM Professoren p JOIN
Assistenten a
ON p.persnr = a.boss;
© Bojan Milijaš, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
31
* OUTER JOINs
(äußerer Vebund)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
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š, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
32
* OUTER JOINs
(äußerer Vebund) (2)
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
SELECT *
FROM Vorlesungen v FULL OUTER JOIN
Assistenten a
ON v.gelesenvon = a.Boss
© Bojan Milijaš, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
33
Ausblick Vorlesung #6
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 Rekursion
 Rekursion in SQL-92
 Rekursion in DBMS-“Dialekten“ (Oracle und DB2)
 Views (Sichten) - gespeicherte Abfragen
 Gewährleistung der logischen Datenunabhängigkeit
 Modellierung von Generalisierung
 UPDATE-fähige Sichten
 Datenintegrität
 Statische und dynamische Bedingungen
 Referentielle Integrität (primary key, foreign key)
 Propagieren der Primärschlüsselveränderungen (cascade)
© Bojan Milijaš, 30.10.2009
Vorlesung #5 - SQL (Teil 2)
34
WS 2009/10
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
Vorlesung #5
Ende