Vorlesung 6 - SQL (Teil 3)

WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
Vorlesung #6
SQL (Teil 3)
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
„Fahrplan“





Wiederholung
Korrelierte vs. Unkorrelierte Anfragen
Entschachtelung der Anfragen
Operationen der Mengenlehre
Spezielle Sprachkonstrukte (BETWEEN,
CASE, LIKE)
© Bojan Milijaš, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
2
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
Nullwerte

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š, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
3
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
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š, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
4
Behandlung von NULL Werten
(Oracle und SQL-92)
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
 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š, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
5
Geschachtelte Anfragen (2)
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
 ... 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š, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
6
Geschachtelte Anfragen (3)
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
 ... 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š, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
7
Geschachtelte Anfragen (4)
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
 ... 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š, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
8
Geschachtelte Anfragen (5)
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
 ... 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š, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
9
Korreliert vs. unkorreliert
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
 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š, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
10
Korreliert vs. Unkorreliert (2)
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
 Ä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š, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
11
Entschachtelung korrelierter
Unteranfragen
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
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š, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
12
Operationen der Mengenlehre
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
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š, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
13
Operationen der Mengenlehre (2)
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
• UNION – mit Duplikatelimierung,
• UNION ALL – ohne Duplikateliminierung
(
select Name
from Assistenten )
union
(
select Name
from Professoren );
© Bojan Milijaš, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
14
Operationen der Mengenlehre (3)
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
 Mengendurchschnitt – INTERSECT
 Alle Professoren, die eine Vorlesung halten
SELECT PersNr
FROM Professoren
INTERSECT
SELECT gelesenVon
FROM Vorlesungen
© Bojan Milijaš, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
15
Operationen der Mengenlehre (4)
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
 Gleiche Abfrage, „alle Professoren, die eine
Vorlesung halten“ mit IN Operator
SELECT PersNr
FROM Professoren
WHERE PersNr IN (SELECT gelesenVon
FROM Vorlesungen);
© Bojan Milijaš, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
16
Operationen der Mengenlehre (5)
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
 Mengendifferenz – MINUS
 Alle Professoren, die keine Vorlesung halten
SELECT PersNr
FROM Professoren
MINUS
SELECT gelesenVon
FROM Vorlesungen;
© Bojan Milijaš, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
17
Operationen der Mengenlehre (6)
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
 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š, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
18
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
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š, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
19
Spezielle Sprachkonstrukte
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
 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š, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
20
Spezielle Sprachkonstrukte (2)
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
 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š, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
21
Spezielle Sprachkonstrukte (3)
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
 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š, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
22
Ausblick Vorlesung #6
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
 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š, 06.11.2014
Vorlesung #6 - SQL (Teil 3)
23
WS 2014/15
Datenbanksysteme
D0 15:15 – 16:45
R 4.080
Vorlesung #6
Ende