WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
Vorlesung #5
SQL (Teil 2)
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 2
„Fahrplan“
NULL Werte Geschachtelte Anfragen in SQL Korrelierte vs. Unkorrelierte Anfragen Entschachtelung der Anfragen Operationen der Mengenlehre Spezielle Sprachkonstrukte (BETWEEN,
CASE, LIKE)
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 3
Nullwerte unbekannter Wert „wird vielleicht später nachgereicht“ Nullwerte können auch im Zuge der
Anfrageauswertung entstehen (Bsp. äußere Joins) manchmal sehr überraschende Anfrageergebnisse,
wenn Nullwerte vorkommen UPDATE studenten SET semester = '' WHERE matrnr = 24002;
select count (*) from Studentenwhere Semester < 13 or Semester >= 13
Tupel (Zeilen) mit Null-Werten werden einfach nicht mitgezählt !!!
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 4
Nullwerte (2)
NULL + 1 = NULL, NULL * 1 = NULL Beispiele (Oracle)
SELECT NULL + 2
FROM Dual;
SELECT NULL * 2
FROM Dual;
Dual ist so etwas wie „Dummy“-Tabelle in Oracle Es gilt die sogenannte 3-wertige Logik in SQL
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 5
3-wertige Logik (NULL Logik)
not
TRUE FALSE
NULL NULL
FALSE TRUE
and TRUE NULL FALSE
TRUE TRUE NULL FALSE
NULL NULL NULL FALSE
FALSE FALSE FALSE FALSE
or TRUE NULL FALSE
TRUE TRUE TRUE TRUE
NULL TRUE NULL NULL
FALSE TRUE NULL FALSE
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 6
Regeln für NULL Werte
In arithmetischen Ausdrücken werden NULL Werte propagiert (NULL + 3 = NULL)
Es gilt die 3-wertige Logik. Vergleichsoperatoren (=, >,<=) liefern immer NULL zurück, wenn mindestens ein Argument NULL ist
Logische Ausdrücke werden entsprechend den vorgestellten 3-wertige-Logik Tabellen ausgewertet
Tupeln mit NULL als Ergebnis in einer WHERE Klausel werden nicht weitergereicht, d.h NULL Werte werden in WHERE Klauseln ausgelassen
Bei Gruppierung ist NULL ein eigenständiger Wert
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 7
Behandlung von NULL Werten (Oracle und SQL-92) In Oracle gibt es eine „null value“ Funktion nvl (if
NULL then):
SELECT nvl(NULL,5) + 2 AS Result
FROM Dual;
Result
------
7 Man kann NULL Werte mit IS NULL oder IS NOT
NULL testen (SQL-92)
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 8
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
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 9
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 );
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 10
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;
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 11
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
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 12
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;
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 13
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);
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 14
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
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 15
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;
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 16
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
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 17
Operationen der Mengenlehre (2)
• UNION – mit Duplikatelimierung, • UNION ALL – ohne Duplikateliminierung
( select Name
from Assistenten )
union
( select Name
from Professoren );
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 18
Operationen der Mengenlehre (3)
Mengendurchschnitt – INTERSECT Alle Professoren, die eine Vorlesung halten
SELECT PersNr
FROM Professoren
INTERSECT
SELECT gelesenVon
FROM Vorlesungen
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 19
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);
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 20
Operationen der Mengenlehre (5)
Mengendifferenz – MINUS Alle Professoren, die keine Vorlesung halten
SELECT PersNr
FROM Professoren
MINUS
SELECT gelesenVon
FROM Vorlesungen;
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 21
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);
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 22
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);
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 23
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);
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 24
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;
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 25
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%´;
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
© Bojan Milijaš, 31.10.2013 Vorlesung #5 - SQL (Teil 2) 26
Ausblick Vorlesung #6 Quantifizierte Anfragen Joins in SQL 92 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)
WS 2013/14Datenbanksysteme
D0 15:15 – 16:45R 1.007
Vorlesung #5
Ende