Vorlesung 5

WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
Vorlesung #5
SQL (Teil 2)
„Fahrplan“
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 Aggregation und Gruppierung
 NULL -Werte, 3-wertige Logik
 Kanonische Übersetzung (SQL  relationale
Algebra)
 Geschachtelte Anfragen in SQL
© Bojan Milijaš, 29.10.2014
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
© Bojan Milijaš, 29.10.2014
Aggregatfunktionen
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 min, max, avg, sum, count
SELECT
FROM
SELECT
FROM
SELECT
FROM
SELECT
FROM
SELECT
min(Semester)
Studenten;
max(Semester)
Studenten;
avg(Semester)
Studenten;
sum(Semester)
Studenten;
count(MatrNr), -- count(*)
count(DISTINCT Semester)
FROM Studenten;
© Bojan Milijaš, 29.10.2014
... mit Gruppierung
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 min, max, avg, sum, count mit GROUP BY
select gelesenVon, sum (SWS)
from Vorlesungen
group by gelesenVon;
 mit HAVING
select gelesenVon, Name, sum (SWS)
from Vorlesungen, Professoren
where gelesenVon = PersNr and Rang = ´C4´
group by gelesenVon, Name
having avg (SWS) >= 3;
© Bojan Milijaš, 29.10.2014
WS 2013/14
Besonderheiten bei
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
Aggregatoperationen
• SQL erzeugt pro Gruppe ein Ergebnistupel
• Deshalb müssen alle in der select-Klausel
aufgeführten Attribute - außer den
aggregierten – auch in der group byKlausel aufgeführt werden
• Nur so kann SQL sicherstellen, dass sich
das Attribut nicht innerhalb der Gruppe
ändert
SELECT A1, A2, An, count(*)
FROM ...
WHERE ..
GROUP BY A1, A2, An
© Bojan Milijaš, 29.10.2014
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
© Bojan Milijaš, 29.10.2014
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
© Bojan Milijaš, 29.10.2014
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
© Bojan Milijaš, 29.10.2014
Ergebnis der Abfrage
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
gelesenVon
2125
Name
Sokrates
sum (SWS)
10
2137
Kant
8
© Bojan Milijaš, 29.10.2014
Ausblick Vorlesung #5









SQL, Fortsetzung
Geschachtelte Anfragen
Korrelierte Anfragen
Mengenoperationen
Quantifizierte Anfragen (, )
Spezielle Sprachkonstrukte
Joins in SQL-92, SQL-99
Rekursion
Sichten (Views)
© Bojan Milijaš, 29.10.2014
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
Nullwerte
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 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š, 29.10.2014
Nullwerte (2)
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
 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š, 29.10.2014
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š, 29.10.2014
FALSE
FALSE
FALSE
FALSE
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š, 29.10.2014
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š, 29.10.2014
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š, 29.10.2014
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š, 29.10.2014
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š, 29.10.2014
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š, 29.10.2014
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š, 29.10.2014
WS 2013/14
Datenbanksysteme
D0 15:15 – 16:45
R 1.007
Vorlesung #5
Ende