Upload
amaliric-laubert
View
104
Download
0
Embed Size (px)
Citation preview
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30
Vorlesung #7
SQL (Teil 2)
© Bojan Milijaš, 28.04.2004 Vorlesung #7 - SQL (Teil 2) 2
SS 2004Datenbanken 4WMi 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) 3
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30Geschachtelte Anfragen
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) 4
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30Geschachtelte Anfragen (2)
... 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) 5
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30Geschachtelte Anfragen (3)
... 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 vWHERE p.PersNr = v.gelesenVonGROUP BY PersNr, Name;
© Bojan Milijaš, 28.04.2004 Vorlesung #7 - SQL (Teil 2) 6
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30Geschachtelte Anfragen (4)
... 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) 7
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30Geschachtelte Anfragen (5)
... 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) 8
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30Korreliert vs. unkorreliert
Achtung: funktioniert in der Original-Beispiel-Datenbank UNI nicht, da GebDatum fehlt!
Alle Studenten, die älter als der jüngste Professor sind - korrelierte Formulierung
select s.*from Studenten swhere exists
(select p.*from Professoren pwhere p.GebDatum >
s.GebDatum);
© Bojan Milijaš, 28.04.2004 Vorlesung #7 - SQL (Teil 2) 9
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30Korreliert vs. Unkorreliert (2)
Ä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) 10
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30
Entschachtelung korrelierter Unteranfragen
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) 11
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30Operationen der Mengenlehre
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) 12
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30Operationen der Mengenlehre (2)
• 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) 13
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30Operationen der Mengenlehre (3)
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) 14
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30Operationen der Mengenlehre (4)
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) 15
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30Operationen der Mengenlehre (5)
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) 16
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30Operationen der Mengenlehre (6)
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) 17
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30ALL 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) 18
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30Spezielle Sprachkonstrukte
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) 19
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30Spezielle Sprachkonstrukte (2)
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) 20
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30Spezielle Sprachkonstrukte (3)
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) 21
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30Quantifizierte Anfragen in SQL
• 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) 22
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30
© Bojan Milijaš, 28.04.2004 Vorlesung #7 - SQL (Teil 2) 23
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30
© Bojan Milijaš, 28.04.2004 Vorlesung #7 - SQL (Teil 2) 24
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30
Quantifizierte Anfragen in SQL (5)• 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) 25
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30
Quantifizierte Anfragen in SQL (4)
• 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) 26
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30JOINs 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) 27
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30
© Bojan Milijaš, 28.04.2004 Vorlesung #7 - SQL (Teil 2) 28
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30
© Bojan Milijaš, 28.04.2004 Vorlesung #7 - SQL (Teil 2) 29
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30JOINs 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;
SS 2004Datenbanken 4WMi 13:30 – 15:00
G 2.30
Vorlesung #7
Ende