Vorlesung 5 - SQL (Teil 2)

WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
Vorlesung #5
SQL (Teil 2)
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
„Fahrplan“






NULL Werte
Geschachtelte Anfragen in SQL
Korrelierte vs. Unkorrelierte Anfragen
Entschachtelung der Anfragen
Operationen der Mengenlehre
Spezielle Sprachkonstrukte (BETWEEN,
CASE, LIKE)
© Bojan Milijaš, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
2
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
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 Studenten
where Semester < 13 or Semester >= 13
 Tupel (Zeilen) mit Null-Werten werden einfach
nicht mitgezählt !!!
© Bojan Milijaš, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
3
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
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
© Bojan Milijaš, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
4
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
3-wertige Logik
(NULL Logik)
not
TRUE FALSE
NULL NULL
FALSE TRUE
and
TRUE
NULL
FALSE
TRUE NULL
TRUE NULL
NULL
NULL
FALSE FALSE
or
TRUE
TRUE
TRUE
NULL
TRUE
FALSE
TRUE
NULL
FALSE
TRUE
TRUE
NULL
NULL
NULL
FALSE
© Bojan Milijaš, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
FALSE
FALSE
FALSE
FALSE
5
Regeln für NULL Werte
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 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
© Bojan Milijaš, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
6
Behandlung von NULL Werten
(Oracle und SQL-92)
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 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)
© Bojan Milijaš, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
7
Geschachtelte Anfragen
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 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š, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
8
Geschachtelte Anfragen (2)
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 ... 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š, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
9
Geschachtelte Anfragen (3)
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 ... 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š, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
10
Geschachtelte Anfragen (4)
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 ... 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š, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
11
Geschachtelte Anfragen (5)
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 ... 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š, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
12
Korreliert vs. unkorreliert
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 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š, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
13
Korreliert vs. Unkorreliert (2)
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 Ä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š, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
14
Entschachtelung korrelierter
Unteranfragen
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
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š, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
15
Operationen der Mengenlehre
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
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š, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
16
Operationen der Mengenlehre (2)
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
• UNION – mit Duplikatelimierung,
• UNION ALL – ohne Duplikateliminierung
(
select Name
from Assistenten )
union
(
select Name
from Professoren );
© Bojan Milijaš, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
17
Operationen der Mengenlehre (3)
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 Mengendurchschnitt – INTERSECT
 Alle Professoren, die eine Vorlesung halten
SELECT PersNr
FROM Professoren
INTERSECT
SELECT gelesenVon
FROM Vorlesungen
© Bojan Milijaš, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
18
Operationen der Mengenlehre (4)
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 Gleiche Abfrage, „alle Professoren, die eine
Vorlesung halten“ mit IN Operator
SELECT PersNr
FROM Professoren
WHERE PersNr IN (SELECT gelesenVon
FROM Vorlesungen);
© Bojan Milijaš, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
19
Operationen der Mengenlehre (5)
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 Mengendifferenz – MINUS
 Alle Professoren, die keine Vorlesung halten
SELECT PersNr
FROM Professoren
MINUS
SELECT gelesenVon
FROM Vorlesungen;
© Bojan Milijaš, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
20
Operationen der Mengenlehre (6)
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 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š, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
21
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
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š, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
22
Spezielle Sprachkonstrukte
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 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š, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
23
Spezielle Sprachkonstrukte (2)
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 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š, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
24
Spezielle Sprachkonstrukte (3)
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 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š, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
25
Ausblick Vorlesung #6
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 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)
© Bojan Milijaš, 31.10.2013
Vorlesung #5 - SQL (Teil 2)
26
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
Vorlesung #5
Ende