Kapitel 2

Kapitel 2
Dr. Brigitte Mathiak
SQL Anfragen
Lernziele

• Syntax von SQL Anfragen
• Präzise Semantik von SQL Anfragen:
Abbildung auf Relationale Algebra
• Erweiterte Konstrukte in SQL Abfragen:
Aggregation, Gruppierung, rekursive Anfragen ..
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
2
Professoren
Vorlesungen
Studenten
Rang Raum MatrNr
Name
Semester VorlNr
PersNr
Name
2125
Sokrates
C4
226
24002
Xenokrates
18
2126
Russel
C4
232
25403
Jonas
12
2127 Kopernikus
C3
310
26120
Fichte
10
2133
C3
52
26830
Aristoxenos
8
2134 Augustinus
C3
309
27550 Schopenhauer
6
2136
Curie
C4
36
28106
3
2137
Kant
C4
7
29120 Theophrastos
2
29555
2
Popper
voraussetzen
5041
5001
5043
5001
5049
5041
5216
5043
5052
5041
5052
5052
5259
Feuerbach
hören
Vorgänger Nachfolger
5001
Carnap
prüfen
MatrNr VorlNr PersNr Note
Titel
SWS gelesen
Von
5001
Grundzüge
4
2137
5041
Ethik
4
2125
5043
Erkenntnistheorie
3
2126
5049
Mäeutik
2
2125
4052
Logik
4
2125
5052 Wissenschaftstheorie
3
2126
5216
Bioethik
2
2126
5259
Der Wiener Kreis
2
2133
5022
Glaube und Wissen
2
2134
4630
Die 3 Kritiken
4
2137
MatrNr
VorlNr
26120
5001
27550
5001
27550
4052
28106
5041
28106
5052
PerslNr
Name
Fachgebiet
Boss
28106
5216
3002
Platon
Ideenlehre
2125
28106
5259
3003
Aristoteles
Syllogistik
2125
29120
5001
3004
Wittgenstein
Sprachtheorie
2126
29120
5041
3005
Rhetikus
Planetenbewegung
2127
Assistenten
28106
5001
2126
1
29120
5049
3006
Newton
Keplersche Gesetze
2127
25403
5041
2125
2
29555
5022
3007
Spinoza
Gott und Natur
2126
27550
4630
2137
2
25403
5022
Einfache SQL-Anfragen
select
*
from
Professoren;
from wählt die Tabelle
select *: Wählt alles aus
PersNr
Name
2125
Sokrates
C4
226
2126
Russel
C4
232
2127 Kopernikus
C3
310
2133
C3
52
2134 Augustinus
C3
309
2136
Curie
C4
36
2137
Kant
C4
7
Popper
Rang Raum
Einfache SQL-Anfragen
select
Bei select können
auch bestimmte Spalten
PersNr, Name gewählt werden
from
Professoren
where
Rang= ´C4´;
Bei where können logische
Einschränkungen gemacht
werden
PersNr
2125
2126
Name
Sokrates
Russel
2136
2137
Curie
Kant
Einfache SQL-Anfragen
Sortierung
select PersNr, Name, Rang
Mit order by kann die
Reihenfolge der Anzeige
verändert werden
from Professoren
order by Rang desc, Name asc;
Was zuerst steht, danach
wird zuerst sortiert
desc ist für absteigende
Ordnung, asc für
aufsteigende
PersNr
2136
2137
2126
2125
2134
2127
2133
Name
Curie
Kant
Russel
Sokrates
Augustinus
Kopernikus
Popper
Rang
C4
C4
C4
C4
C3
C3
C3
Duplikateliminierung
select distinct Rang
Mit distinct werden Duplikate
herausgenommen
from Professoren
Rang
C3
C4
Professoren
Vorlesungen
Studenten
Rang Raum MatrNr
Name
Semester VorlNr
PersNr
Name
2125
Sokrates
C4
226
24002
Xenokrates
18
2126
Russel
C4
232
25403
Jonas
12
2127 Kopernikus
C3
310
26120
Fichte
10
2133
C3
52
26830
Aristoxenos
8
2134 Augustinus
C3
309
27550 Schopenhauer
6
2136
Curie
C4
36
28106
3
2137
Kant
C4
7
29120 Theophrastos
2
29555
2
Popper
voraussetzen
5041
5001
5043
5001
5049
5041
5216
5043
5052
5041
5052
5052
5259
Feuerbach
hören
Vorgänger Nachfolger
5001
Carnap
prüfen
MatrNr VorlNr PersNr Note
Titel
SWS gelesen
Von
5001
Grundzüge
4
2137
5041
Ethik
4
2125
5043
Erkenntnistheorie
3
2126
5049
Mäeutik
2
2125
4052
Logik
4
2125
5052 Wissenschaftstheorie
3
2126
5216
Bioethik
2
2126
5259
Der Wiener Kreis
2
2133
5022
Glaube und Wissen
2
2134
4630
Die 3 Kritiken
4
2137
MatrNr
VorlNr
26120
5001
27550
5001
27550
4052
28106
5041
28106
5052
PerslNr
Name
Fachgebiet
Boss
28106
5216
3002
Platon
Ideenlehre
2125
28106
5259
3003
Aristoteles
Syllogistik
2125
29120
5001
3004
Wittgenstein
Sprachtheorie
2126
29120
5041
3005
Rhetikus
Planetenbewegung
2127
Assistenten
28106
5001
2126
1
29120
5049
3006
Newton
Keplersche Gesetze
2127
25403
5041
2125
2
29555
5022
3007
Spinoza
Gott und Natur
2126
27550
4630
2137
2
25403
5022
Anfragen über mehrere Relationen
Welcher Professor liest "Mäeutik"?
select Name, Titel
from Professoren, Vorlesungen
where PersNr = gelesenVon and Titel = `Mäeutik‘ ;
Hinter from werden nun
zwei Tabellen genannt
Anfragen über mehrere Relationen
Professoren
PersNr Name
Rang Raum VorlNr
2125 Sokrates C4
226
5001
2126
Russel
C4
232
5041
2137
Kant
C4
7
5049
Vorlesungen
Titel
SWS gelesen Von
Grundzüge
4
2137
Ethik
4
2125
Mäeutik
4630 Die 3 Kritiken
Verknüpfung 
2
2125
4
2137
PersN
r
2125
Name
Sokrates
Rang
C4
Raum
226
VorlNr
5001
Titel
Grundzüge
SWS gelesen Von
4
2137
1225
Sokrates
C4
226
5041
Ethik
4
2125
2125
Sokrates
C4
226
5049
Mäeutik
2
2125
Jeder mit jedem einmal
2126
2126
Russel
Russel
C4
C4
232
232
5001
5041
Grundzüge
Ethik
4
4
2137
2125
2137
Kant
C4
7
4630
Die 3 Kritiken
4
2137
where PersNr = gelesenVon and Titel = `Mäeutik‘
PersNr
Name
Rang
Raum
VorlNr
Titel
SWS
2125
Sokrates
C4
226
5049
Mäeutik
2
select Name, Titel
Name
Sokrates
Titel
Mäeutik
gelesen
Von
2125
Anfragen über mehrere Relationen
Welche Studenten hören welche Vorlesungen?
select Name, Titel
from Studenten, hören, Vorlesungen
where Studenten.MatrNr = hören.MatrNr and
hören.VorlNr = Vorlesungen.VorlNr;
Alternativ:
select s.Name, v.Titel
from Studenten s, hören h, Vorlesungen v
where s. MatrNr = h. MatrNr and
h.VorlNr = v.VorlNr
Mit Korrelationsvariablen
Professoren
Vorlesungen
Studenten
Rang Raum MatrNr
Name
Semester VorlNr
PersNr
Name
2125
Sokrates
C4
226
24002
Xenokrates
18
2126
Russel
C4
232
25403
Jonas
12
2127 Kopernikus
C3
310
26120
Fichte
10
2133
C3
52
26830
Aristoxenos
8
2134 Augustinus
C3
309
27550 Schopenhauer
6
2136
Curie
C4
36
28106
3
2137
Kant
C4
7
29120 Theophrastos
2
29555
2
Popper
voraussetzen
5041
5001
5043
5001
5049
5041
5216
5043
5052
5041
5052
5052
5259
Feuerbach
hören
Vorgänger Nachfolger
5001
Carnap
prüfen
MatrNr VorlNr PersNr Note
Titel
SWS gelesen
Von
5001
Grundzüge
4
2137
5041
Ethik
4
2125
5043
Erkenntnistheorie
3
2126
5049
Mäeutik
2
2125
4052
Logik
4
2125
5052 Wissenschaftstheorie
3
2126
5216
Bioethik
2
2126
5259
Der Wiener Kreis
2
2133
5022
Glaube und Wissen
2
2134
4630
Die 3 Kritiken
4
2137
MatrNr
VorlNr
26120
5001
27550
5001
27550
4052
28106
5041
28106
5052
PerslNr
Name
Fachgebiet
Boss
28106
5216
3002
Platon
Ideenlehre
2125
28106
5259
3003
Aristoteles
Syllogistik
2125
29120
5001
3004
Wittgenstein
Sprachtheorie
2126
29120
5041
3005
Rhetikus
Planetenbewegung
2127
Assistenten
28106
5001
2126
1
29120
5049
3006
Newton
Keplersche Gesetze
2127
25403
5041
2125
2
29555
5022
3007
Spinoza
Gott und Natur
2126
27550
4630
2137
2
25403
5022
Mengenoperationen und geschachtelte Anfragen
Mengenoperationen union, intersect, minus
(Vereinigung, Schnittmenge, Differenz)
( select Name
from Assistenten )
union
( select Name
from Professoren);
Was passiert hier?
Was würde bei intersect
Passieren oder bei minus?
Jeder für sich mit
Zettel und Stift; 5 min.
Existenzquantor exists
select p.Name
from Professoren p
where not exists ( select *
from Vorlesungen v
where v.gelesenVon = p.PersNr );
Vorlesungen
VorlNr
Titel
SWS gelesen
Von
5001
Grundzüge
4
2137
5041
Ethik
4
2125
5043
Erkenntnistheorie
3
2126
5049
Mäeutik
2
2125
4052
Logik
4
2125
5052 Wissenschaftstheorie
3
2126
5216
Bioethik
2
2126
5259
Der Wiener Kreis
2
2133
5022
Glaube und Wissen
2
2134
4630
Die 3 Kritiken
4
2137
Professoren
PersNr
Name
2125
Sokrates
C4
226
2126
Russel
C4
232
2127 Kopernikus
C3
310
2133
C3
52
2134 Augustinus
C3
309
2136
Curie
C4
36
2137
Kant
C4
7
Popper
Rang Raum
Existenzquantor exists
select p.Name
from Professoren p
where not exists ( select *
from Vorlesungen v
where v.gelesenVon = p.PersNr );
Professoren
PersNr
Name
2125
Sokrates
C4
226
2126
Russel
C4
232
2127 Kopernikus
C3
310
2127 Kopernikus
C3
310
2133
C3
52
2136
C4
36
2134 Augustinus
C3
309
2136
Curie
C4
36
2137
Kant
C4
7
Popper
Rang Raum
Professoren
PersNr
Name
Curie
Rang Raum
Mengenvergleich
Hatten wir das nicht gerade?
select Name
from Professoren
where PersNr not in ( select gelesenVon
from Vorlesungen );
Unkorrelierte Unterabfragen
sind meist effizienter, da sie
nicht pro Zeile ausgewertet
werden müssen
"Quantifizierte" Subqueries
Die Bedingung Wert  ANY Menge mit   {{=, , , , , } ist erfüllt,
wenn es in der Menge ein Element gibt, für das Wert  Element gilt.
(=ANY ist äquivalent zu IN) .. SOME ist equivalent to ANY
Die Bedingung Wert  ALL Menge mit   {=, , , , , } ist erfüllt,
wenn für alle Elemente der Menge gilt: Wert  Element.
(<>ALL ist äquivalent zu NOT IN.)
Die Bedingung EXISTS Menge ist erfüllt,
wenn die Menge nicht leer ist
(dies ist äquivalent zur Bedingung 0 < SELECT COUNT(*) FROM ...)
Achtung: Wert  ALL (query_returns_no_rows) = TRUE
Wert  ANY (query_returns_no_rows) = FALSE
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
18
"Quantifizierte" Subqueries
Anfrage:
Studenten im höchsten Semester:
SELECT *
FROM Studenten
WHERE Semester >= ALL (SELECT Semester
FROM Studenten) ;
Anfrage:
Studenten, für die keine Prüfungen erfasst sind:
SELECT * FROM Studenten s
WHERE NOT EXISTS ( SELECT *
FROM Prüfen p
WHERE p.MatrNr = s.MatrNr );
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
19
Professoren
Vorlesungen
Studenten
Rang Raum MatrNr
Name
Semester VorlNr
PersNr
Name
2125
Sokrates
C4
226
24002
Xenokrates
18
2126
Russel
C4
232
25403
Jonas
12
2127 Kopernikus
C3
310
26120
Fichte
10
2133
C3
52
26830
Aristoxenos
8
2134 Augustinus
C3
309
27550 Schopenhauer
6
2136
Curie
C4
36
28106
3
2137
Kant
C4
7
29120 Theophrastos
2
29555
2
Popper
voraussetzen
5041
5001
5043
5001
5049
5041
5216
5043
5052
5041
5052
5052
5259
Feuerbach
hören
Vorgänger Nachfolger
5001
Carnap
prüfen
MatrNr VorlNr PersNr Note
Titel
SWS gelesen
Von
5001
Grundzüge
4
2137
5041
Ethik
4
2125
5043
Erkenntnistheorie
3
2126
5049
Mäeutik
2
2125
4052
Logik
4
2125
5052 Wissenschaftstheorie
3
2126
5216
Bioethik
2
2126
5259
Der Wiener Kreis
2
2133
5022
Glaube und Wissen
2
2134
4630
Die 3 Kritiken
4
2137
MatrNr
VorlNr
26120
5001
27550
5001
27550
4052
28106
5041
28106
5052
PerslNr
Name
Fachgebiet
Boss
28106
5216
3002
Platon
Ideenlehre
2125
28106
5259
3003
Aristoteles
Syllogistik
2125
29120
5001
3004
Wittgenstein
Sprachtheorie
2126
29120
5041
3005
Rhetikus
Planetenbewegung
2127
Assistenten
28106
5001
2126
1
29120
5049
3006
Newton
Keplersche Gesetze
2127
25403
5041
2125
2
29555
5022
3007
Spinoza
Gott und Natur
2126
27550
4630
2137
2
25403
5022
Aggregatfunktion und Gruppierung
Aggregatfunktionen avg, max, min, count, sum
select avg (Semester)
from Studenten;
select gelesenVon, sum (SWS)
from Vorlesungen
group by gelesenVon;
select gelesenVon, Name, sum (SWS)
from Vorlesungen, Professoren
where gelesenVon = PersNr and Rang = ´C4´
group by gelesenVon, Name
having avg (SWS) >= 3;
Besonderheiten bei 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 by-Klausel aufgeführt werden
Nur so kann SQL sicherstellen, dass sich das Attribut
nicht innerhalb der Gruppe ändert
Ausführen der Anfrage mit group by
Vorlesung x Professoren
VorlNr
Titel
SWS gelesen PersNr Name Rang Raum
Von
5001 Grundzüge 4
2137
2125 Sokrates C4 226
5041
Ethik
4
2125
2125 Sokrates C4
...
4630
...
Die 3
Kritiken
...
4
...
2137
...
2137
...
Kant
...
C4
where gelesenVon = PersNr and Rang = ´C4´
226
...
7
VorlNr
Titel
5001
5041
5043
Grundzüge
Ethik
Erkenntnistheorie
Mäeutik
Logik
Wissenschaftstheorie
Bioethik
Die 3 Kritiken
5049
4052
5052
5216
4630
SWS gelesen
Von
4
2137
4
2125
3
2126
PersNr
Name
Rang Raum
2137
Kant
2125 Sokrates
2126 Russel
C4
C4
C4
7
226
232
2
4
3
2125
2125
2126
2125 Sokrates
2125 Sokrates
2126 Russel
C4
C4
C4
226
226
232
2
4
2126
2137
2126
2137
C4
C4
232
7
group by gelesenVon, Name
Russel
Kant
VorlNr
Titel
SWS gelesenVon PersNr Name
5041
Ethik
4
2125
2125 Sokrates
5049
Mäeutik
2
2125
2125 Sokrates
4052
Logik
4
2125
2125 Sokrates
5043 Erkenntnistheorie
3
2126
2126 Russel
5052 Wissenschaftstheo. 3
2126
2126 Russel
5216
Bioethik
2
2126
2126 Russel
5001
Grundzüge
4
2137
2137
Kant
4630
Die 3 Kritiken
4
2137
2137
Kant
Rang Raum
C4
226
C4
226
C4
226
C4
232
C4
232
C4
232
C4
7
C4
7
having avg (SWS) >= 3
VorlN
r
5041
5049
4052
Titel
Ethik
Mäeutik
Logik
5001
4630
Grundzüge
Die 3 Kritiken
SWS gelesenVon PersNr
4
2125
2125
2
2125
2125
4
2125
2125
4
4
2137
2137
2137
2137
Name
Sokrates
Sokrates
Sokrates
Kant
Kant
Rang Raum
C4
226
C4
226
C4
226
C4
C4
7
7
Aggregation (sum(SWS)) pro Gruppe
gelesenVon
Name
sum (SWS)
2125
Sokrates
10
2137
Kant
8
Geschachtelte Anfrage (Forts.)
Unteranfrage in der select-Klausel
Für jedes Ergebnistupel wird die Unteranfrage ausgeführt
Man beachte, dass die Unteranfrage korreliert ist (greift auf
Attribute der umschließenden Anfrage zu)
select PersNr, Name, ( select sum (SWS) as Lehrbelastung
from Vorlesungen
where gelesenVon=PersNr )
from Professoren;
Professoren
Vorlesungen
Studenten
Rang Raum MatrNr
Name
Semester VorlNr
PersNr
Name
2125
Sokrates
C4
226
24002
Xenokrates
18
2126
Russel
C4
232
25403
Jonas
12
2127 Kopernikus
C3
310
26120
Fichte
10
2133
C3
52
26830
Aristoxenos
8
2134 Augustinus
C3
309
27550 Schopenhauer
6
2136
Curie
C4
36
28106
3
2137
Kant
C4
7
29120 Theophrastos
2
29555
2
Popper
voraussetzen
5041
5001
5043
5001
5049
5041
5216
5043
5052
5041
5052
5052
5259
Feuerbach
hören
Vorgänger Nachfolger
5001
Carnap
prüfen
MatrNr VorlNr PersNr Note
Titel
SWS gelesen
Von
5001
Grundzüge
4
2137
5041
Ethik
4
2125
5043
Erkenntnistheorie
3
2126
5049
Mäeutik
2
2125
4052
Logik
4
2125
5052 Wissenschaftstheorie
3
2126
5216
Bioethik
2
2126
5259
Der Wiener Kreis
2
2133
5022
Glaube und Wissen
2
2134
4630
Die 3 Kritiken
4
2137
MatrNr
VorlNr
26120
5001
27550
5001
27550
4052
28106
5041
28106
5052
PerslNr
Name
Fachgebiet
Boss
28106
5216
3002
Platon
Ideenlehre
2125
28106
5259
3003
Aristoteles
Syllogistik
2125
29120
5001
3004
Wittgenstein
Sprachtheorie
2126
29120
5041
3005
Rhetikus
Planetenbewegung
2127
Assistenten
28106
5001
2126
1
29120
5049
3006
Newton
Keplersche Gesetze
2127
25403
5041
2125
2
29555
5022
3007
Spinoza
Gott und Natur
2126
27550
4630
2137
2
25403
5022
Unkorrelierte versus korrelierte Unteranfragen
korrelierte Formulierung
select s.*
from Studenten s
where exists
(select p.*
from Professoren
where p.GebDatum > s.GebDatum);
Ä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
Entschachtelung korrelierter Unteranfragen -- Forts.
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;
Verwertung der Ergebnismenge einer Unteranfrage
select tmp.MatrNr, tmp.Name, tmp.VorlAnzahl
from (select s.MatrNr, s.Name, count(*) as VorlAnzahl
from Studenten s, hören h
where s.MatrNr=h.MatrNr
group by s.MatrNr, s.Name) tmp
where tmp.VorlAnzahl > 2;
MatrNr
Name
VorlAnzahl
28106
Carnap
4
29120
Theophrastos
3
Decision-Support-Anfragen mit geschachtelten Unteranfragen
select h.VorlNr, h.AnzProVorl, g.GesamtAnz,
h.AnzProVorl/g.GesamtAnz as Marktanteil
from
( select VorlNr, count(*) as AnzProVorl
from hören
group by VorlNr ) h,
( select count (*) as GesamtAnz
from Studenten) g;
Das Ergebnis der Anfrage
VorlNr
AnzProVorl
GesamtAnz Marktanteil
4052
1
8
.125
5001
4
8
.5
5022
2
8
.25
...
...
...
...
Simulation allquantifizierter Suchprädikate

Anfrage:
Wer hat alle vierstündigen Vorlesungen gehört?
Problem:  ist in SQL nicht vorgesehen, nur exists
Idee: Elimination von  und 
Dazu sind folgende Äquivalenzen anzuwenden:
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
35
Umformung der Logik (1/3)

Schritt 1: Elimination 
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
36
Umformung der Logik (2/3)

Schritt 2: Elimination 
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
37
Umformung der Logik (3/3)

Schritt 3: Transformation
ergibt schließlich:
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
38
Umsetzung der Logik in SQL
Anfrage:
Wer hat alle vierstündigen Vorlesungen gehört?
SELECT s.MatrNr
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 ) );
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
39
Simulation allquantifizierter Suchprädikate:
alternative Form mit Mengenoperation
Anfrage:
Wer hat alle vierstündigen Vorlesungen gehört?
SELECT s.MatrNr
Zwischenanfrage:
FROM Studenten s
Alle Vorlesungen, die 4 SWS
WHERE NOT EXISTS
haben
( SELECT v.VorlNr
MINUS
Alle Vorlesungen, die der Student s
FROM Vorlesungen v
gehört hat
WHERE v.SWS = 4
MINUS
SELECT h.VorlNr
FROM hören h
WHERE h.MatrNr = s.MatrNr ) ;
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
40
Allquantifizierung durch count-Aggregation
Allquantifizierung kann immer auch durch eine countAggregation 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);
Herausforderung
Wie formuliert man die komplexere Anfrage:
„Wer hat alle vierstündigen Vorlesungen gehört“
ohne Korrelation nur mit Zählen
Grundidee besteht darin, vorher durch einen Join die
Studenten/Vorlesungs-Paare einzuschränken und danach das
Zählen durchzuführen
Jeder für sich mit Zettel und Stift; 5 min
Simulation allquantifizierter Suchprädikate
durch count-Aggregation
Anfrage:
Wer hat alle vierstündigen Vorlesungen gehört?
SELECT h.MatrNr
FROM hören h, Vorlesungen v
WHERE v.SWS = 4 AND h.VorlNr = v.VorlNr
GROUP BY h.MatrNr
HAVING count (*) = (SELECT count (*)
FROM Vorlesungen v1
WHERE v1.SWS = 4);
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
43
Count-Aggregation: Fehlerquellen
Anfrage:
Namen der Studenten, die alle vierstündigen Vorlesungen gehört haben
Vorsicht: so geht das nicht..
SELECT h.MatrNr, s.Name
FROM hören h, Vorlesungen v, Studenten s
WHERE
v.SWS = 4 AND h.VorlNr = v.VorlNr AND h.MatrNr = s.MatrNr
GROUP BY h.MatrNr
HAVING count (*) = (SELECT count (*)
FROM Vorlesungen v1
WHERE v1.SWS = 4);
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
44
Count-Aggregation: Fehlerquellen
Anfrage:
Namen der Studenten, die alle vierstündigen Vorlesungen gehört haben
SELECT h.MatrNr, s.Name
FROM hören h, Vorlesungen v, Studenten s
WHERE
v.SWS = 4 AND h.VorlNr = v.VorlNr AND h.MatrNr = s.MatrNr
GROUP BY h.MatrNr, s.Name
HAVING count (*) = ( SELECT count (*)
FROM Vorlesungen v1
WHERE v1.SWS = 4);
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
BY-Klausel aufgeführt werden!
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
45
Allquantifizierung - Fazit
Es geht zwar nicht direkt, aber indirekt
Man kann per deMorgan den Ausdruck auf exists
zurückführen
Man kann per Mengenoperation minus die Fälle
eliminieren, in denen es nicht übereinstimmt
Man kann zählen, ob alle Fälle betrachtet sind und dies
dann mit der Gesamtmenge der Fälle vergleichen
Für die Prüfung reicht eine Methode
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
46

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:
select count (*)
from Studenten
where Semester < 13 or Semester > =13
Wenn es Studenten gibt, deren Semester-Attribut den Wert
null hat, werden diese nicht mitgezählt
Der Grund liegt in folgenden Regeln für den Umgang mit
null-Werten begründet:

Auswertung bei Null-Werten
1. In arithmetischen Ausdrücken werden Nullwerte propagiert,
d.h. sobald ein Operand null ist, wird auch das Ergebnis null.
Dementsprechend wird z.B. null + 1 zu null ausgewertet-aber
auch null * 0 wird zu null ausgewertet.
2. SQL hat eine dreiwertige Logik, die nicht nur true und false
kennt, sondern auch einen dritten Wert unknown. Diesen
Wert liefern Vergleichsoperationen zurück, wenn mindestens
eines ihrer Argumente null ist. Beispielsweise wertet SQL das
Prädikat (PersNr=...) immer zu unknown aus, wenn die
PersNr des betreffenden Tupels den Wert null hat.
3. Logische Ausdrücke werden nach den folgenden Tabellen
berechnet:

not
true
false
unknown unknown
false
true
and
true
unknown
false
true
true
unknown
false
unknown
unknown
unknown
false
false
false
false
false
or
true
unknown
false
true
true
true
true
unknown
true
unknown
unknown
false
true
unknown
false
Diese Berechnungsvorschriften sind recht intuitiv.
Unknown or true wird z.B. zu true - die Disjunktion ist
mit dem true-Wert des rechten Arguments immer
erfüllt, unabhängig von der Belegung des linken
Arguments. Analog ist unknown and false
automatisch false - keine Belegung des linken
Arguments könnte die Konjunktion mehr erfüllen.
4. In einer where-Bedingung werden nur Tupel
weitergereicht, für die die Bedingung true ist.
Insbesondere werden Tupel, für die die Bedingung zu
unknown auswertet, nicht ins Ergebnis aufgenommen.
5. Bei einer Gruppierung wird null als
ein eigenständiger Wert aufgefasst
und in eine eigene Gruppe
eingeordnet.
Note
1.0
count(*)
25
1.3
94
…
null
…
248

Test auf Nullwert
Anfrage:
bisher unbenotete Prüfungsleistungen
SELECT * FROM prüfen WHERE Note IS NULL
Anfrage:
Professoren mit eigenem Büro
SELECT * FROM Professoren WHERE Raum IS NOT NULL
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
51
Spezielle Sprachkonstrukte ("syntactic sugar")
select *
from Studenten
where Semester > = 1 and Semester < = 6;
select *
from Studenten
where Semester between 1 and 6;
select *
from Studenten
where Semester in (2,4,6);
Das case-Konstrukt
select MatrNr, ( case when Note >= 5.5 then ´sehr gut´
when Note >= 5.0 then ´gut´
when Note >= 4.5 then ´befriedigend´
when Note >= 4.0 then ´ausreichend´
else ´nicht bestanden´end)
from prüfen;
Die erste qualifizierende when-Klausel wird ausgeführt
Vergleiche mit like
Platzhalter "%" ; "_" bei Vergleichen mit like
"%" steht für beliebig viele (auch gar kein) Zeichen
"_" steht für genau ein Zeichen
Vorsicht: Bei Vergleichen mit = werden Platzhalter als
normale Zeichen interpretiert!
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%´;
Behandlung spezieller Datentypen
• Zu Strings gibt es meist eine Reihe Zusatzfunktionen in den
Datenbanken
• Zu erwarten ist einfache Stringmanipulation: Konkatenation,
Suchen, Ersetzen; oft reguläre Ausdrücke und zunehmend
Anwendungen aus NLP und Suchmaschinentechnologie, wie
Synonyme, Ranking, Topic Analysis, …
• Die sind aber bislang noch nicht vereinheitlicht und werden
daher hier nicht behandelt
• Weiterhin gibt es oft Methoden um komplexe Datentypen, wie
Zeit, Ort, Bilder, Musik, etc. zu verarbeiten
• Das ist aber auch nicht standardisiert, wird hier also nicht
behandelt
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
55
Joins in SQL-92
cross join: Kreuzprodukt
select *
from R1, R2;
natural join: natürlicher Join
Join oder inner join: Theta-Join
left, right oder full outer join: äußerer Join
union join: Vereinigungs-Join (wird hier nicht vorgestellt)
Inner Join
Anfrage:
welcher Professor bietet welche Vorlesungen an?
SELECT p.Name, v.Name
FROM Professoren p
JOIN Vorlesungen v ON p.PersNr = v.gelesenVon ;
SELECT p.Name, v.Name
FROM Professoren p, Vorlesungen v
WHERE p.PersNr = v.gelesenVon ;
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
57
Natural Join
Anfrage: Welcher Student hört welche Vorlesung?
SELECT Studenten.Name, Vorlesungen.Name
FROM Studenten
NATURAL JOIN hören
NATURAL JOIN Vorlesungen ;
SELECT s.Name, v.Name
FROM Studenten s, hören h, Vorlesungen v
WHERE
s.MatrNr = h.MatrNr AND
h.VorlNr = v.vorlNr ;
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
58
Cross Join
Anfrage:
alle Paare "Professor – Student" :
SELECT Professoren.Name, Studenten.Name
FROM Professoren CROSS JOIN Studenten ;
SELECT p.Name, s.Name
FROM Professoren p, Studenten s ;
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
59
Outer Joins: Left Outer Join
SELECT p.PersNr, p.Name, f.PersNr, f.Note,
f.MatrNr, s.MatrNr, s.Name
FROM Professoren p LEFT OUTER JOIN
( prüfen f LEFT OUTER JOIN Studenten s
ON f.MatrNr = s.MatrNr )
ON p.PersNr = f.PersNr ;
p.PersN
f.PersN f.Not f.MatrN
p.Name
r
r
e
r
2126
Russel
2126
1
28106
Sokrate
2125
2125
2
25403
s
s.Matr
Nr
28106
25403
s.Name
Carnap
Jonas
2137
Kant
2137
2
27550
2136
Curie
Ø
Ø
Ø
Ø
Ø
…
…
…
…
…
…
…
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
27550 Schopenhauer
60
Outer Joins: Right Outer Join
SELECT p.PersNr, p.Name, f.PersNr, f.Note,
f.MatrNr, s.MatrNr, s.Name
FROM Professoren p RIGHT OUTER JOIN
(prüfen f RIGHT OUTER JOIN
Studenten s ON f.MatrNr = p.MatrNr)
ON p.PersNr = f.PersNr ;
p.PersN
r
p.Name
f.PersNr
f.Note
f.MatrN
r
s.MatrNr
s.Name
2126
Russel
2126
1
28106
28106
Carnap
2125
Sokrates
2125
2
25403
25403
Jonas
2137
Kant
2137
2
27550
27550
Schopenhau
er
Ø
Ø
Ø
Ø
Ø
26120
Fichte
…
…
…
…
…
…
…
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
61
Outer Joins: Full Outer Join
SELECT p.PersNr, p.Name, f.PersNr, f.Note,
f.MatrNr, s.MatrNr, s.Name
FROM Professoren p FULL OUTER JOIN
(prüfen f FULL OUTER JOIN Studenten s
ON f.MatrNr = s.MatrNr)
ON p.PersNr = f.PersNr ;
p.PersN
r
2126
p.Name
f.PersNr
f.Note
f.MatrNr
s.MatrNr
s.Name
Russel
Sokrate
s
2126
1
28106
28106
Carnap
2125
2
25403
25403
Jonas
2137
Kant
2137
2
27550
27550
Schopenhauer
Ø
Ø
Ø
Ø
Ø
26120
Fichte
…
…
…
…
…
…
…
2136
Curie
Ø
Ø
Ø
Ø
Ø
…
…
…
…
…
…
…
2125
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
62
Outer Joins: Oracle-Syntax
Anfrage:
welche Studenten hören welche Vorlesungen
SELECT *
FROM Studenten
LEFT OUTER JOIN hören
USING (MatrNr)
SELECT *
FROM Studenten s, hören h
WHERE
s.MatrNr = h.MatrNr (+)
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
63
Äußere Joins
select p.PersNr, p.Name, f.PersNr, f.Note, f.MatrNr,
s.MatrNr, s.Name
from Professoren p left outer join
(prüfen f left outer join Studenten s
on f.MatrNr= s.MatrNr)
on p.PersNr=f.PersNr;
PersNr p.Name f.PersNr
2126 Russel
2126
2125 Sokrates 2125
2137
Kant
2137
2136
Curie
-
f.Note
1
2
2
-
f.MatrNr s.MatrNr s.Name
28106
28106
Carnap
25403
25403
Jonas
27550
27550 Schopenhauer
-
Äußere Joins
select p.PersNr, p.Name, f.PersNr, f.Note, f.MatrNr,
s.MatrNr, s.Name
from Professoren p right outer join
(prüfen f right outer join Studenten s on
f.MatrNr= s.MatrNr)
on p.PersNr=f.PersNr;
PersNr p.Name f.PersNr f.Note f.MatrNr s.MatrNr s.Name
2126 Russel
2126
1
28106
28106
Carnap
2125 Sokrates 2125
2
25403
25403
Jonas
2137
Kant
2137
2
27550
27550 Schopenhauer
26120
Fichte
Äußere Joins
select p.PersNr, p.Name, f.PersNr, f.Note, f.MatrNr,
s.MatrNr, s.Name
from Professoren p full outer join
(prüfen f full outer join Studenten s on
f.MatrNr= s.MatrNr)
on p.PersNr=f.PersNr;
p.PersNr p.Name f.PersNr
2126 Russel
2126
2125 Sokrates 2125
2137
Kant
2137
f.Note
1
2
2
-
-
-
-
2136
Curie
-
-
f.MatrNr s.MatrNr s.Name
28106
28106
Carnap
25403
25403
Jonas
27550
27550 Schopenhauer
26120
Fichte
-
-
-

Rekursion
select Vorgänger
from voraussetzen, Vorlesungen
where Nachfolger= VorlNr and
Titel= `Der Wiener Kreis´

Der Wiener Kreis
Wissenschaftstheorie
Erkenntnistheorie
Bioethik
Ethik
Grundzüge
Mäeutik
Rekursion
select v1.Vorgänger
from voraussetzen v1, voraussetzen v2, Vorlesungen v
where v1.Nachfolger= v2.Vorgänger and
v2.Nachfolger= v.VorlNr and
v.Titel=`Der Wiener Kreis´

Vorgänger des „Wiener Kreises“
der Tiefe n
select v1.Vorgänger
Wollen wir das wirklich?
from voraussetzen v1

voraussetzen vn_minus_1
voraussetzen vn,
Vorlesungen v
where v1.Nachfolger= v2.Vorgänger and

vn_minus_1.Nachfolger= vn.Vorgänger and
vn.Nachfolger = v.VorlNr and
v.Titel= `Der Wiener Kreis´


Grundproblem: Transitive Hülle
transA,B(R)= {(a,b)  k  IN (1, ..., k  R (
1.A= 2.B 

k-1.A= k.B 
1.A= a 
k.B= b))}

Der Wiener Kreis
Wissenschaftstheorie
Erkenntnistheorie
Bioethik
Ethik
Grundzüge
Mäeutik

Die connect by-Klausel (Oracle)
select Titel
from Vorlesungen
where VorlNr in (select Vorgänger
Grundzüge
Ethik
Erkenntnistheorie
Wissenschaftstheorie
from voraussetzen
connect by Nachfolger=prior Vorgänger
start with Nachfolger= (select VorlNr
from Vorlesungen
where Titel= `Der
Wiener Kreis´));
Rekursion in DB2/SQL99:
gleiche Anfrage
with TransVorl (Vorg, Nachf)
as (select Vorgänger, Nachfolger from voraussetzen
union all
select t.Vorg, v.Nachfolger
from TransVorl t, voraussetzen v
where t.Nachf= v.Vorgänger)
select Titel from Vorlesungen where VorlNr in
(select Vorg from TransVorl where Nachf in
(select VorlNr from Vorlesungen
where Titel= `Der Wiener Kreis´) )


zuerst wird eine temporäre Sicht TransVorl mit der withKlausel angelegt
Diese Sicht TransVorl ist rekursiv definiert, da sie selbst in
der Definition vorkommt
Aus dieser Sicht werden dann die gewünschten Tupel
extrahiert
Ergebnis ist natürlich wie gehabt
Fazit Rekursion/transitive Hülle
In SQL nur mühsam lösbar
Vorhandene Lösungen sind technologieabhängig
Die praktisch beste Lösung ist fast immer das Ändern der
Datenstruktur oder das Anlegen von Hilfstabellen
voraussetzen_rec
5001
5216
Vorgänger Nachfolger
5001
5052
5001
5041
5001
5043
5001
5043
5001
5052
5001
5049
5043
5041
5216
5041
5043
5052
5041
5052
5052
5259
Achtung!
5259
Solche Datenstrukturen
5259
sind oft schwer aktuell zu
halten -> Datenintegrität
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
77
Syntaktische Beschreibung einer Select Anweisung
"Grobsyntax":
select_block { { UNION | INTERSECT | EXCEPT } [ALL]
select_block ...}
[ORDER BY result_column [ASC | DESC]
{, result_column [ASC | DESC] …}
mit select_block ::=
SELECT [ALL | DISTINCT]
{column | {expression [AS result_column]}}
{, {column | {expression [AS result_column]}} …}
FROM table [correlation_var] {, table [correlation_var] …}
[WHERE search_condition]
[GROUP BY column {, column …} [HAVING search_condition] ]
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
78
Oracle: Syntaxdiagramme
Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen
79