Dr. Brigitte Mathiak
Kapitel 2
SQL Anfragen
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 2
Lernziele
• Syntax von SQL Anfragen
• Präzise Semantik von SQL Anfragen:Abbildung auf Relationale Algebra
• Erweiterte Konstrukte in SQL Abfragen:Aggregation, Gruppierung, rekursive Anfragen ..
ProfessorenPersN
rName Rang Rau
m
2125 Sokrates C4 226
2126 Russel C4 232
2127 Kopernikus
C3 310
2133 Popper C3 52
2134 Augustinus
C3 309
2136 Curie C4 36
2137 Kant C4 7
Studenten
MatrNr
Name Semester
24002 Xenokrates 18
25403 Jonas 12
26120 Fichte 10
26830 Aristoxenos 8
27550 Schopenhauer
6
28106 Carnap 3
29120 Theophrastos
2
29555 Feuerbach 2
VorlesungenVorlNr Titel SWSgelesen
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
voraussetzenVorgänge
rNachfolger
5001 5041
5001 5043
5001 5049
5041 5216
5043 5052
5041 5052
5052 5259
hörenMatrNr VorlNr
26120 5001
27550 5001
27550 4052
28106 5041
28106 5052
28106 5216
28106 5259
29120 5001
29120 5041
29120 5049
29555 5022
25403 5022
AssistentenPerslN
rName Fachgebiet Boss
3002 Platon Ideenlehre 2125
3003 Aristoteles Syllogistik 2125
3004 Wittgenstein
Sprachtheorie 2126
3005 Rhetikus Planetenbewegung 2127
3006 Newton Keplersche Gesetze 2127
3007 Spinoza Gott und Natur 2126
prüfenMatrN
rVorlNr PersN
rNote
28106 5001 2126 1
25403 5041 2125 2
27550 4630 2137 2
Einfache SQL-Anfragen
select *
from Professoren;
PersNr
Name Rang Raum
2125 Sokrates C4 226
2126 Russel C4 232
2127 Kopernikus
C3 310
2133 Popper C3 52
2134 Augustinus
C3 309
2136 Curie C4 36
2137 Kant C4 7
select *: Wählt alles aus
from wählt die Tabelle
Einfache SQL-Anfragen
PersNr Name2125 Sokrates2126 Russel
2136 Curie2137 Kant
select PersNr, Name
from Professoren
where Rang= ´C4´;
Bei select können auch bestimmte Spalten gewählt werden
Bei where können logische Einschränkungen gemacht werden
Einfache SQL-AnfragenSortierung
select PersNr, Name, Rang
from Professoren
order by Rang desc, Name asc;
PersNr Name Rang2136 Curie C42137 Kant C42126 Russel C42125 Sokrates C42134 Augustinus C32127 Kopernikus C32133 Popper C3
Mit order by kann die Reihenfolge der Anzeige verändert werden
Was zuerst steht, danachwird zuerst sortiert
desc ist für absteigende Ordnung, asc für aufsteigende
select distinct Rang
from Professoren
Rang
C3
C4
Duplikateliminierung
Mit distinct werden Duplikate herausgenommen
ProfessorenPersN
rName Rang Rau
m
2125 Sokrates C4 226
2126 Russel C4 232
2127 Kopernikus
C3 310
2133 Popper C3 52
2134 Augustinus
C3 309
2136 Curie C4 36
2137 Kant C4 7
Studenten
MatrNr
Name Semester
24002 Xenokrates 18
25403 Jonas 12
26120 Fichte 10
26830 Aristoxenos 8
27550 Schopenhauer
6
28106 Carnap 3
29120 Theophrastos
2
29555 Feuerbach 2
VorlesungenVorlNr Titel SWSgelesen
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
voraussetzenVorgänge
rNachfolger
5001 5041
5001 5043
5001 5049
5041 5216
5043 5052
5041 5052
5052 5259
hörenMatrNr VorlNr
26120 5001
27550 5001
27550 4052
28106 5041
28106 5052
28106 5216
28106 5259
29120 5001
29120 5041
29120 5049
29555 5022
25403 5022
AssistentenPerslN
rName Fachgebiet Boss
3002 Platon Ideenlehre 2125
3003 Aristoteles Syllogistik 2125
3004 Wittgenstein
Sprachtheorie 2126
3005 Rhetikus Planetenbewegung 2127
3006 Newton Keplersche Gesetze 2127
3007 Spinoza Gott und Natur 2126
prüfenMatrN
rVorlNr PersN
rNote
28106 5001 2126 1
25403 5041 2125 2
27550 4630 2137 2
Anfragen über mehrere Relationen
Welcher Professor liest "Mäeutik"?
select Name, Titelfrom Professoren, Vorlesungenwhere PersNr = gelesenVon and Titel = `Mäeutik‘ ;
Hinter from werden nun zwei Tabellen genannt
Anfragen über mehrere Relationen
RaumRangNamePersNr
226232
7
C4C4
C4
SokratesRussel
Kant
21252126
2137
Professoren
gelesen VonSWSTitelVorlNr
21374Grundzüge5001
21374Die 3 Kritiken4630
21252Mäeutik5049
21254Ethik5041
Vorlesungen
Verknüpfung
4630
5041
5001
5049
5041
5001
VorlNr
Die 3 Kritiken
Ethik
Grundzüge
Mäeutik
Ethik
Grundzüge
Titel
4
4
4
2
4
4
SWS
2137
2125
2137
2125
2125
2137
gelesen Von
7
232
232
226
226
226
Raum
C4Kant2137
RangNamePersNr C4Sokrates2125
C4Russel2126
C4Russel2126
C4Sokrates2125
C4Sokrates1225
PersNr Name Rang Raum VorlNr Titel SWS gelesen Von
2125 Sokrates C4 226 5049 Mäeutik 2 2125
Name TitelSokrates Mäeutik
where PersNr = gelesenVon and Titel = `Mäeutik‘
select Name, Titel
Jeder mit jedem einmal
Anfragen über mehrere Relationen
Welche Studenten hören welche Vorlesungen?
select Name, Titelfrom Studenten, hören, Vorlesungenwhere Studenten.MatrNr = hören.MatrNr and
hören.VorlNr = Vorlesungen.VorlNr;
Alternativ:select s.Name, v.Titelfrom Studenten s, hören h, Vorlesungen vwhere s. MatrNr = h. MatrNr and
h.VorlNr = v.VorlNrMit Korrelationsvariablen
ProfessorenPersN
rName Rang Rau
m
2125 Sokrates C4 226
2126 Russel C4 232
2127 Kopernikus
C3 310
2133 Popper C3 52
2134 Augustinus
C3 309
2136 Curie C4 36
2137 Kant C4 7
Studenten
MatrNr
Name Semester
24002 Xenokrates 18
25403 Jonas 12
26120 Fichte 10
26830 Aristoxenos 8
27550 Schopenhauer
6
28106 Carnap 3
29120 Theophrastos
2
29555 Feuerbach 2
VorlesungenVorlNr Titel SWSgelesen
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
voraussetzenVorgänge
rNachfolger
5001 5041
5001 5043
5001 5049
5041 5216
5043 5052
5041 5052
5052 5259
hörenMatrNr VorlNr
26120 5001
27550 5001
27550 4052
28106 5041
28106 5052
28106 5216
28106 5259
29120 5001
29120 5041
29120 5049
29555 5022
25403 5022
AssistentenPerslN
rName Fachgebiet Boss
3002 Platon Ideenlehre 2125
3003 Aristoteles Syllogistik 2125
3004 Wittgenstein
Sprachtheorie 2126
3005 Rhetikus Planetenbewegung 2127
3006 Newton Keplersche Gesetze 2127
3007 Spinoza Gott und Natur 2126
prüfenMatrN
rVorlNr PersN
rNote
28106 5001 2126 1
25403 5041 2125 2
27550 4630 2137 2
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; 3 min.
VorlesungenVorlNr Titel SWSgelesen
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
Existenzquantor exists
select p.Namefrom Professoren pwhere not exists ( select *
from Vorlesungen v where v.gelesenVon =
p.PersNr );
Korrelation
ProfessorenPersN
rName Rang Rau
m
2125 Sokrates C4 226
2126 Russel C4 232
2127 Kopernikus
C3 310
2133 Popper C3 52
2134 Augustinus
C3 309
2136 Curie C4 36
2137 Kant C4 7
Existenzquantor exists
select p.Namefrom Professoren pwhere not exists ( select *
from Vorlesungen v where v.gelesenVon =
p.PersNr );Professoren
PersNr
Name Rang Raum
2125 Sokrates C4 226
2126 Russel C4 232
2127 Kopernikus
C3 310
2133 Popper C3 52
2134 Augustinus
C3 309
2136 Curie C4 36
2137 Kant C4 7
ProfessorenPersN
rName Rang Rau
m
2127 Kopernikus
C3 310
2136 Curie C4 36
Mengenvergleich
select Name
from Professoren
where PersNr not in ( select gelesenVon
from Vorlesungen );
Unkorrelierte Unterabfragen sind meist effizienter, da sienicht pro Zeile ausgewertetwerden müssen
Hatten wir das nicht gerade?
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 18
"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, SS 12 Kapitel 2: SQL Anfragen 19
"Quantifizierte" Subqueries
Anfrage:Studenten im höchsten Semester:
SELECT * FROM StudentenWHERE Semester >= ALL (SELECT Semester FROM Studenten) ;
Anfrage:Studenten, für die keine Prüfungen erfasst sind:
SELECT * FROM Studenten sWHERE NOT EXISTS ( SELECT * FROM Prüfen p WHERE p.MatrNr = s.MatrNr );
ProfessorenPersN
rName Rang Rau
m
2125 Sokrates C4 226
2126 Russel C4 232
2127 Kopernikus
C3 310
2133 Popper C3 52
2134 Augustinus
C3 309
2136 Curie C4 36
2137 Kant C4 7
Studenten
MatrNr
Name Semester
24002 Xenokrates 18
25403 Jonas 12
26120 Fichte 10
26830 Aristoxenos 8
27550 Schopenhauer
6
28106 Carnap 3
29120 Theophrastos
2
29555 Feuerbach 2
VorlesungenVorlNr Titel SWSgelesen
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
voraussetzenVorgänge
rNachfolger
5001 5041
5001 5043
5001 5049
5041 5216
5043 5052
5041 5052
5052 5259
hörenMatrNr VorlNr
26120 5001
27550 5001
27550 4052
28106 5041
28106 5052
28106 5216
28106 5259
29120 5001
29120 5041
29120 5049
29555 5022
25403 5022
AssistentenPerslN
rName Fachgebiet Boss
3002 Platon Ideenlehre 2125
3003 Aristoteles Syllogistik 2125
3004 Wittgenstein
Sprachtheorie 2126
3005 Rhetikus Planetenbewegung 2127
3006 Newton Keplersche Gesetze 2127
3007 Spinoza Gott und Natur 2126
prüfenMatrN
rVorlNr PersN
rNote
28106 5001 2126 1
25403 5041 2125 2
27550 4630 2137 2
Aggregatfunktion und GruppierungAggregatfunktionen avg, max, min, count, sum
select avg (Semester)from Studenten;
select gelesenVon, sum (SWS)from Vorlesungengroup by gelesenVon;
select gelesenVon, Name, sum (SWS)from Vorlesungen, Professorenwhere gelesenVon = PersNr and Rang = ´C4´group by gelesenVon, Namehaving 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 ProfessorenVorlNr Titel SWS gelesen
VonPersN
rName Ran
gRaum
5001 Grundzüge
4 2137 2125 Sokrates
C4 226
5041 Ethik 4 2125 2125 Sokrates
C4 226
... ... ... ... ... ... ... ...4630 Die 3
Kritiken4 2137 2137 Kant C4 7
where gelesenVon = PersNr and Rang = ´C4´
VorlNr Titel SWS gelesen Von
PersNr
Name RangRaum
5001 Grundzüge 4 2137 2137 Kant C4 75041 Ethik 4 2125 2125 Sokrates C4 2265043 Erkenntnis-
theorie3 2126 2126 Russel C4 232
5049 Mäeutik 2 2125 2125 Sokrates C4 2264052 Logik 4 2125 2125 Sokrates C4 2265052 Wissenschafts-
theorie3 2126 2126 Russel C4 232
5216 Bioethik 2 2126 2126 Russel C4 2324630 Die 3 Kritiken 4 2137 2137 Kant C4 7
group by gelesenVon, Name
having avg (SWS) >= 3
232232232
226
226226
77
Raum
C4Russel212621263Erkenntnistheorie5043C4Russel212621263Wissenschaftsthe
o.5052
C4Russel212621262Bioethik5216
C4Sokrates212521254Logik4052
C4Sokrates212521254Ethik5041C4Sokrates212521252Mäeutik5049
C4Kant213721374Die 3 Kritiken4630C4
Rang
4
SWS
Grundzüge
Titel NamePersNrgelesenVon
VorlNr
Kant213721375001
RaumRangNamePersNr
gelesenVonSWSTitelVorlNr
77
C4C4
KantKant
21372137
21372137
44
GrundzügeDie 3 Kritiken
50014630
C4C4C4
226226226
424
EthikMäeutik
Logik
SokratesSokratesSokrates
212521252125
212521252125
504150494052
Aggregation (sum(SWS)) pro Gruppe
gelesenVon Name sum (SWS)
2125 Sokrates 10
2137 Kant 8
Geschachtelte Anfrage (Forts.)
Unteranfrage in der select-KlauselFür jedes Ergebnistupel wird die Unteranfrage ausgeführtMan 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;
ProfessorenPersN
rName Rang Rau
m
2125 Sokrates C4 226
2126 Russel C4 232
2127 Kopernikus
C3 310
2133 Popper C3 52
2134 Augustinus
C3 309
2136 Curie C4 36
2137 Kant C4 7
Studenten
MatrNr
Name Semester
24002 Xenokrates 18
25403 Jonas 12
26120 Fichte 10
26830 Aristoxenos 8
27550 Schopenhauer
6
28106 Carnap 3
29120 Theophrastos
2
29555 Feuerbach 2
VorlesungenVorlNr Titel SWSgelesen
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
voraussetzenVorgänge
rNachfolger
5001 5041
5001 5043
5001 5049
5041 5216
5043 5052
5041 5052
5052 5259
hörenMatrNr VorlNr
26120 5001
27550 5001
27550 4052
28106 5041
28106 5052
28106 5216
28106 5259
29120 5001
29120 5041
29120 5049
29555 5022
25403 5022
AssistentenPerslN
rName Fachgebiet Boss
3002 Platon Ideenlehre 2125
3003 Aristoteles Syllogistik 2125
3004 Wittgenstein
Sprachtheorie 2126
3005 Rhetikus Planetenbewegung 2127
3006 Newton Keplersche Gesetze 2127
3007 Spinoza Gott und Natur 2126
prüfenMatrN
rVorlNr PersN
rNote
28106 5001 2126 1
25403 5041 2125 2
27550 4630 2137 2
Unkorrelierte versus korrelierte Unteranfragen
korrelierte Formulierungselect s.*from Studenten swhere exists
(select p.*from Professorenwhere 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 awhere 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 pwhere 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;
VorlNr AnzProVorl GesamtAnz
Marktanteil
4052 1 8 .125
5001 4 8 .5
5022 2 8 .25
... ... ... ...
Das Ergebnis der Anfrage
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 35
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:
Simulation allquantifizierter Suchprädikate
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 36
Umformung der Logik (1/3)
Schritt 1: Elimination
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 37
Umformung der Logik (2/3)
Schritt 2: Elimination
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 38
Umformung der Logik (3/3)
Schritt 3: Transformation ergibt schließlich:
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 39
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 ) );
Umsetzung der Logik in SQL
Anfrage: Wer hat alle vierstündigen Vorlesungen gehört?
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 40
SELECT s.MatrNr FROM Studenten s WHERE NOT EXISTS ( SELECT v.VorlNr FROM Vorlesungen v WHERE v.SWS = 4 MINUS SELECT h.VorlNr FROM hören h WHERE h.MatrNr = s.MatrNr ) ;
Simulation allquantifizierter Suchprädikate:alternative Form mit Mengenoperation
Anfrage: Wer hat alle vierstündigen Vorlesungen gehört?
Zwischenanfrage:Alle Vorlesungen, die 4 SWS habenMINUSAlle Vorlesungen, die der Student s gehört hat
Allquantifizierung durch count-Aggregation
Allquantifizierung kann immer auch durch eine count-Aggregation 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);
HerausforderungWie formuliert man die komplexere Anfrage: „Wer hat alle vierstündigen Vorlesungen gehört“ohne Korrelation nur mit ZählenGrundidee 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
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 43
Simulation allquantifizierter Suchprädikatedurch count-Aggregation
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);
Anfrage: Wer hat alle vierstündigen Vorlesungen gehört?
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 44
Count-Aggregation: Fehlerquellen
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);
Anfrage: Namen der Studenten, die alle vierstündigen Vorlesungen gehört haben
Vorsicht: so geht das nicht..
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 45
Count-Aggregation: Fehlerquellen
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 derSELECT-Klausel aufgeführten Attribute - außer den aggregierten – auch in der GROUP BY-Klausel aufgeführt werden!
Anfrage:Namen der Studenten, die alle vierstündigen Vorlesungen gehört haben
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 46
Allquantifizierung - Fazit
Es geht zwar nicht direkt, aber indirekt
Man kann per deMorgan den Ausdruck auf exists zurückführenMan kann per Mengenoperation minus die Fälle eliminieren, in denen es nicht übereinstimmtMan 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
Nullwerteunbekannter 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 Studentenwhere Semester < 13 or Semester > =13
Wenn es Studenten gibt, deren Semester-Attribut den Wert null hat, werden diese nicht mitgezähltDer 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 falseunknown
unknown
false true
and true unknown falsetrue true unknown falseunknown unknown unknown falsefalse false false false
or true unknown falsetrue true true trueunknown true unknown unknow
nfalse 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 count(*)
1.0 25
1.3 94
… …
null 248
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 51
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
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
Die erste qualifizierende when-Klausel wird ausgeführt
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;
Vergleiche mit likePlatzhalter "%" ; "_" bei Vergleichen mit like"%" steht für beliebig viele (auch gar kein) Zeichen"_" steht für genau ein ZeichenVorsicht: 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, SS 12 Kapitel 2: SQL Anfragen 55
Joins in SQL-92cross join: Kreuzproduktselect *
from R1, R2;
natural join: natürlicher JoinJoin oder inner join: Theta-Joinleft, right oder full outer join: äußerer Joinunion join: Vereinigungs-Join (wird hier nicht vorgestellt)
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 57
Inner Join
Anfrage:welcher Professor bietet welche Vorlesungen an?
SELECT p.Name, v.NameFROM Professoren p JOIN Vorlesungen v ON p.PersNr = v.gelesenVon ;
SELECT p.Name, v.NameFROM Professoren p, Vorlesungen vWHERE p.PersNr = v.gelesenVon ;
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 58
Natural Join
Anfrage: Welcher Student hört welche Vorlesung?
SELECT Studenten.Name, Vorlesungen.NameFROM Studenten NATURAL JOIN hören NATURAL JOIN Vorlesungen ;
SELECT s.Name, v.NameFROM Studenten s, hören h, Vorlesungen vWHERE s.MatrNr = h.MatrNr AND h.VorlNr = v.vorlNr ;
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 59
Cross Join
Anfrage: alle Paare "Professor – Student" :
SELECT Professoren.Name, Studenten.NameFROM Professoren CROSS JOIN Studenten ;
SELECT p.Name, s.NameFROM Professoren p, Studenten s ;
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 60
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.PersNr p.Name f.PersN
rf.Not
ef.MatrN
rs.Matr
Nr s.Name
2126 Russel 2126 1 28106 28106 Carnap
2125 Sokrates 2125 2 25403 25403 Jonas
2137 Kant 2137 2 27550 27550 Schopenhauer
2136 Curie Ø Ø Ø Ø Ø
… … … … … … …
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 61
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.PersNr 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 Schopenhauer
Ø Ø Ø Ø Ø 26120 Fichte
… … … … … … …
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 62
p.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
… … … … … … …
2136 Curie Ø Ø Ø Ø Ø… … … … … … …
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 ;
Outer Joins: Full Outer Join
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 63
Outer Joins: Oracle-Syntax
SELECT * FROM Studenten LEFT OUTER JOIN hörenUSING (MatrNr)
SELECT * FROM Studenten s, hören h WHERE s.MatrNr = h.MatrNr (+)
Anfrage: welche Studenten hören welche Vorlesungen
Äußere Joinsselect p.PersNr, p.Name, f.PersNr, f.Note, f.MatrNr, s.MatrNr, s.Namefrom Professoren p left outer join
(prüfen f left outer join Studenten s on f.MatrNr= s.MatrNr)
on p.PersNr=f.PersNr;
PersNrp.Name f.PersNr f.Note f.MatrNr s.MatrNr
s.Name
2126 Russel 2126 1 28106 28106 Carnap2125 Sokrate
s2125 2 25403 25403 Jonas
2137 Kant 2137 2 27550 27550 Schopen-hauer
2136 Curie - - - - -
Äußere Joins
select p.PersNr, p.Name, f.PersNr, f.Note, f.MatrNr, s.MatrNr, s.Namefrom Professoren p right outer join
(prüfen f right outer join Studenten s on f.MatrNr= s.MatrNr) on p.PersNr=f.PersNr;PersNrp.Name f.PersNr f.Note f.MatrNr s.MatrN
rs.Name
2126 Russel 2126 1 28106 28106 Carnap2125 Sokrate
s2125 2 25403 25403 Jonas
2137 Kant 2137 2 27550 27550 Schopen-hauer
- - - - - 26120 Fichte
Äußere Joins
select p.PersNr, p.Name, f.PersNr, f.Note, f.MatrNr, s.MatrNr, s.Namefrom 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 f.Note f.MatrNr s.MatrNr
s.Name
2126 Russel 2126 1 28106 28106 Carnap2125 Sokrate
s2125 2 25403 25403 Jonas
2137 Kant 2137 2 27550 27550 Schopen-hauer
- - - - - 26120 Fichte
2136 Curie - - - - -
Rekursion
select Vorgänger
from voraussetzen, Vorlesungen
where Nachfolger= VorlNr and
Titel= `Der Wiener Kreis´
Der Wiener Kreis
Wissenschaftstheorie
Bioethik
Erkenntnistheorie Ethik Mäeutik
Grundzüge
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´
select v1.Vorgängerfrom voraussetzen v1
voraussetzen vn_minus_1voraussetzen 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´
Vorgänger des „Wiener Kreises“ der Tiefe n
Wollen wir das wirklich?
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
Bioethik
Erkenntnistheorie Ethik Mäeutik
Grundzüge
Die connect by-Klausel (Oracle)
select Titel
from Vorlesungen
where VorlNr in (select Vorgänger
fromvoraussetzen
connect by Nachfolger= prior Vorgänger
start with Nachfolger= (select VorlNr
from Vorlesungen
where Titel= `Der
Wiener Kreis´));
GrundzügeEthik
ErkenntnistheorieWissenschaftstheorie
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 with-Klausel 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
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 77
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_recVorgänge
rNachfolger
5001 5041
5001 5043
5001 5049
5041 5216
5043 5052
5041 5052
5052 5259
5001 5216
5001 5052
5001 5043
5001 5052
5043 5259
5041 5259
Achtung!Solche Datenstrukturensind oft schwer aktuell zu halten -> Datenintegrität
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 78
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, SS 12 Kapitel 2: SQL Anfragen 79
Oracle: Syntaxdiagramme
Fazit
Sie können nun SQL-Anfragen
Datenbanken, SS 12 Kapitel 2: SQL Anfragen 80