Post on 06-Apr-2015
transcript
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Vorlesung #5
SQL (Teil 2)
© Bojan Milijaš, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 2
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 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) 3
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 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) 4
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Geschachtelte 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š, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 5
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Geschachtelte 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š, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 6
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Geschachtelte Anfragen (3)
... 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 vWHERE p.PersNr = v.gelesenVonGROUP BY PersNr, Name;
© Bojan Milijaš, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 7
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Geschachtelte 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š, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 8
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Geschachtelte 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š, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 9
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Korreliert 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š, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 10
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Korreliert 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š, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 11
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
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š, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 12
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Operationen 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š, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 13
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Operationen der Mengenlehre (2)
• 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) 14
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Operationen der Mengenlehre (3)
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) 15
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Operationen 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š, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 16
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Operationen der Mengenlehre (5)
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) 17
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Operationen 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š, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 18
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 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) 19
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Spezielle 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š, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 20
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Spezielle 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š, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 21
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Spezielle 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š, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 22
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Quantifizierte 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š, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 23
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 24
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
© Bojan Milijaš, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 25
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Quantifizierte Anfragen in SQL (4)• 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) 26
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Quantifizierte Anfragen in SQL (5)
• 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) 27
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 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) 28
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
CROSS JOIN (Kartesisches Produkt)
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) 29
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
NATURAL JOIN(natürlicher Verbund)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) 30
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
JOIN(Theta Verbund)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) 31
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
JOIN(Theta Verbund) (2)
Allgemein – setzt die Gleichheit der Join-Spalten nicht voraus:
SELECT *
FROM Professoren p JOIN
Assistenten a
ON p.persnr = a.boss;
© Bojan Milijaš, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 32
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
* OUTER JOINs(äußerer Vebund)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) 33
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
* OUTER JOINs(äußerer Vebund) (2)
SELECT *
FROM Vorlesungen v FULL OUTER JOIN
Assistenten a
ON v.gelesenvon = a.Boss
© Bojan Milijaš, 30.10.2009 Vorlesung #5 - SQL (Teil 2) 34
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Ausblick Vorlesung #6 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)
WS 2009/10Datenbanksysteme
Fr 15:15 – 16:45R 0.006
Vorlesung #5
Ende