79
Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen

Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Embed Size (px)

Citation preview

Page 1: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Dr. Brigitte Mathiak

Kapitel 2

SQL Anfragen

Page 2: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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 ..

Page 3: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 4: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 5: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 6: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 7: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

select distinct Rang

from Professoren

Rang

C3

C4

Duplikateliminierung

Mit distinct werden Duplikate herausgenommen

Page 8: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 9: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 10: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 11: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 12: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 13: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 14: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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.

Page 15: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 16: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 17: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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?

Page 18: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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

Page 19: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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 );

Page 20: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 21: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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;

Page 22: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 23: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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´

Page 24: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 25: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 26: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

gelesenVon Name sum (SWS)

2125 Sokrates 10

2137 Kant 8

Page 27: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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;

Page 28: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 29: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Unkorrelierte versus korrelierte Unteranfragen

korrelierte Formulierungselect s.*from Studenten swhere exists

(select p.*from Professorenwhere p.GebDatum > s.GebDatum);

Page 30: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Ä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

Page 31: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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;

Page 32: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 33: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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;

Page 34: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

VorlNr AnzProVorl GesamtAnz

Marktanteil

4052 1 8 .125

5001 4 8 .5

5022 2 8 .25

... ... ... ...

Das Ergebnis der Anfrage

Page 35: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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

Page 36: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen 36

Umformung der Logik (1/3)

Schritt 1: Elimination

Page 37: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen 37

Umformung der Logik (2/3)

Schritt 2: Elimination

Page 38: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen 38

Umformung der Logik (3/3)

Schritt 3: Transformation ergibt schließlich:

Page 39: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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?

Page 40: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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

Page 41: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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);

Page 42: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 43: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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?

Page 44: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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..

Page 45: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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

Page 46: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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

Page 47: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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:

Page 48: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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:

Page 49: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 50: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 51: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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

Page 52: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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);

Page 53: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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;

Page 54: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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%´;

Page 55: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 56: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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)

Page 57: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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 ;

Page 58: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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 ;

Page 59: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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 ;

Page 60: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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 Ø Ø Ø Ø Ø

… … … … … … …

Page 61: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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

… … … … … … …

Page 62: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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

Page 63: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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

Page 64: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Ä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 - - - - -

Page 65: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Ä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

Page 66: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Ä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;

Page 67: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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 - - - - -

Page 68: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Rekursion

select Vorgänger

from voraussetzen, Vorlesungen

where Nachfolger= VorlNr and

Titel= `Der Wiener Kreis´

Page 69: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Der Wiener Kreis

Wissenschaftstheorie

Bioethik

Erkenntnistheorie Ethik Mäeutik

Grundzüge

Page 70: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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´

Page 71: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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?

Page 72: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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))}

Page 73: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Der Wiener Kreis

Wissenschaftstheorie

Bioethik

Erkenntnistheorie Ethik Mäeutik

Grundzüge

Page 74: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 75: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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´) )

Page 76: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

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

Page 77: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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

Page 78: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/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] ]

Page 79: Dr. Brigitte Mathiak Kapitel 2 SQL Anfragen. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen2 Lernziele Syntax von SQL Anfragen Präzise

Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen 79

Oracle: Syntaxdiagramme