SQL (Teil 2)

Die Folien bauen auf dem Foliensatz zum Buch und den von Prof. Dr.-Ing.
Norbert Fuhr in den vergangenen Semestern verwendeten Folien auf.

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

Wiederholung

2
 Professoren Studenten
PersNr Name Rang Raum MatrNr Name Semester Vorlesungen
2125 Sokrates C4 226 24002 Xenokrates 18 VorlNr Titel SWS gelesenVon
2126 Russel C4 232 25403 Jonas 12 5001 Grundzüge 4 2137
2127 Kopernikus C3 310 26120 Fichte 10 5041 Ethik 4 2125
2133 Popper C3 52 26830 Aristoxenos 8 5043 Erkenntnistheorie 3 2126
2134 Augustinus C3 309 27550 Schopenhauer 6 5049 Mäeutik 2 2125
2136 Curie C4 36 28106 Carnap 3 5052 Logik 4 2125
2137 Kant C4 7 29120 Theophrastos 2 5216 Wissenschaftstheorie 3 2126
voraussetzen 29555 Feuerbach 2
5216 Bioethik 2 2126
Vorgänger Nachfolger hören
5259 Der Wiener Kreis 2 2133
5001 5041 MatrNr VorlNr
5001 5043 5022 Glaube und Wissen 2 2134
26120 5001
5001 5049 4630 Die 3 Kritiken 4 2137
27550 5001
5041 5216 27550 4052 Assistenten
5043 5052 28106 5041 PersNr Name Fachgebiet Boss
5041 5052 28106 5052 3002 Platon Ideenlehre 2125
5052 5259 28106 5216 3003 Aristoteles Syllogistik 2125
prüfen 3004 Wittgenstein Sprachtheorie 2126
28106 5259
MatrNr VorlNr PersNr Note 3005 Rhetikus Planetenbewegung 2127
29120 5001
28106 5001 2126 1 3006 Newton Keplersche Gesetze 2127
29120 5041
25403 5041 2125 2 3007 Spinoza Gott und Natur 2126
29120 5049
27550 4630 2137 2
 Wiederholung
Datenbanken: SQL

Einfache SQL-Anfrage

select PersNr, Name PersNr Name

from Professoren 2125 Sokrates
where Rang= 'C4'; 2126 Russel
2136 Curie
2137 Kant

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

4
 Wiederholung
Datenbanken: SQL

Sortierung

select PersNr, Name, Rang PersNr Name Rang

from Professoren 2136 Curie C4
order by Rang desc, Name asc; 2137 Kant C4
2126 Russel C4
2125 Sokrates C4
2134 Augustinus C3
2127 Kopernikus C3
2133 Popper C3

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

5
 Wiederholung
Datenbanken: SQL

Duplikateliminierung

select Rang select distinct Rang

from Professoren from Professoren

Rang
C4 Rang
C4 C3
C3
C4
C3
C3
C4
C4 6
 Vorlesungen
Wiederholung VorlNr Titel SWS gelesenVon

Datenbanken: SQL 5001 Grundzüge 4 2137

5041 Ethik 4 2125

Anfragen über mehrere Relationen 5043

5049
Erkenntnistheorie
Mäeutik
3
2
2126
2125
5052 Logik 4 2125
5216 Wissenschaftstheorie 3 2126
Welcher Professor liest "Mäeutik"? 5216 Bioethik 2 2126
5259 Der Wiener Kreis 2 2133
5022 Glaube und Wissen 2 2134
select Name, Titel
4630 Die 3 Kritiken 4 2137
from Professoren, Vorlesungen
Professoren
where PersNr = gelesenVon and Titel = PersNr Name Rang Raum
`Mäeutik‘ ; 2125 Sokrates C4 226
2126 Russel C4 232
2127 Kopernikus C3 310
ΠName,Titel(σPersNr=gelesenVon∧Titel='Mäeutik'(Prof 2133 Popper C3 52
essoren x Vorlesungen)) 2134 Augustinus C3 309
Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23 2136 Curie C4 36
7
2137 Kant C4 7
 Wiederholung
Datenbanken: SQL

Anfragen über mehrere Relationen

Professoren Professoren
PersNr Name Rang Raum VorlNr Tite SWS gelesenVon
2125 Sokrates C4 226 5001 Grundzüge 4 2137
2126 Russel C4 232 5041 Russel 4 2125
…

…
5049 Mäeutik 2 2125
2137 Kant C4 7

…
4630 Die 3 Kritiken 4 2137

Verknüpfung
Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23 x 8
 Wiederholung
Datenbanken: SQL
PersNr Name Rang Raum VorlNr Titel SWS gelesenVon
2125 Sokrates C4 226 5001 Grundzüge 4 2137
2125 Sokrates C4 226 5041 Ethik 4 2125
…

…
2125 Sokrates C4 226 5049 Mäeutik 2 2125
…

…
2126 Russel C4 232 5001 Grundzüge 4 2137
2126 Russel C4 232 5041 Ethik 4 2125
…

…
2137 Kant C4 7 4630 Die 3 Kritiken 4 2137
Auswahl
PersNr Name Rang Raum VorlNr Titel SWS gelesenVon
2125 Sokrates C4 226 5049 Mäeutik 2 2125
Projektion

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

Name Titel
9
Sokrates Mäeutik
 Wiederholung
Datenbanken: SQL

Explizite Tupelvariablen
werden im from-Teil deklariert:

select [Link], [Link]

from Studenten s, hören h, Vorlesungen V
where [Link] = [Link] and
[Link] = [Link];

Beachte: Die Tupelvariablen müssen in allen Teilen der Anfrage verwendet werden, nicht
nur im where-Teil

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

10
 Wiederholung
Datenbanken: SQL

Mengenoperationen
Mengenoperationen union, intersect, minus
(Anleihen an der Relationenalgebra)

( select [Link]
from Assistenten a)
union
( select [Link]
from Professoren p);

11
Aggregatfunktionen

12
Datenbanken: SQL

Aggregatfunktionen

§ Aggregatfunktionen und Gruppierung

§ Ausführung von Anfragen mit group by

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

13
 Datenbanken: SQL

Aggregatfunktion und Gruppierung

Aggregatfunktionen
avg, max, min, count, sum

1 select avg ([Link]) 3 select [Link], [Link], sum ([Link])

from Studenten s; from Vorlesungen v, Professoren p
where [Link] = [Link] and [Link]
= ´C4´
group by [Link], [Link]
2 select [Link], sum ([Link])
having avg ([Link]) >= 3;
from Vorlesungen v
group by [Link];

14
Datenbanken: SQL

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

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

15
Datenbanken: SQL

Ausführen einer Anfrage mit group by

Vorlesungen x Professoren
VorlNr Titel SWS gelesenVon PersNr Name Rang Raum
5001 Grundzüge 4 2137 2125 Sokrates C4 226
5041 Ethik 4 2125 2125 Sokrates C4 226
… … … … … … … …
4630 Die 3 Kritiken 4 2137 2137 Kant C4 7

where-Bedingung

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

16
Datenbanken: SQL

VorlNr Titel SWS gelesenVon PersNr Name Rang Raum

5001 Grundzüge 4 2137 2137 Kant C4 7
5041 Ethik 4 2125 2125 Sokrates C4 226
5043 Erkenntnistheorie 3 2126 2126 Russel C4 232
5049 Mäeutik 2 2125 2125 Sokrates C4 226
4052 Logik 4 2126 2125 Sokrates C4 226
5052 Wissenschaftsthe 3 2126 2126 Russel C4 232
orie
5216 Bioethik 2 2126 2126 Russel C4 232
4630 Die 3 Kritiken 4 2137 2137 Kant C4 7

Gruppierung
Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23
17
VorlNr Titel SWS gelesenVon PersNr Name Rang Raum
5041 Ethik 4 2125 2125 Sokrates C4 226
5049 Mäeutik 2 2125 2125 Sokrates C4 226
4052 Logik 4 2126 2125 Sokrates C4 226
5043 Erkenntnistheorie 3 2126 2126 Russel C4 232
5052 Wissenschaftsth. 3 2126 2126 Russel C4 232
5216 Bioethik 2 2126 2126 Russel C4 232
5001 Grundzüge 4 2137 2137 Kant C4 7
4630 Die 3 Kritiken 4 2137 2137 Kant C4 7
having-Bedingung
VorlNr Titel SWS gelesenVon PersNr Name Rang Raum
5041 Ethik 4 2125 2125 Sokrates C4 226
5049 Mäeutik 2 2125 2125 Sokrates C4 226
4052 Logik 4 2125 2125 Sokrates C4 226
5001 Grundzüge 4 2137 2137 Kant C4 7
4630 Die 3 Kritiken 4 2137 2137 Kant C4 7

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

Aggregation (sum) und Projektion
18
Datenbanken: SQL

Endergebnis

gelesenVon Name sum (SWS)

2125 Sokrates 10
2137 Kant 8

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

19
Geschachtelte Anfragen

20
Datenbanken: SQL

Geschachtelte Anfragen

§ Unteranfragen
§ Korrelierte vs. unkorrelierte Anfragen
§ Entschachtelung korrelierter Anfragen
§ Verwertung der Ergebnismenge einer Unteranfrage
§ Anfragen mit in und all

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

21
Datenbanken: SQL

Geschachtelte Anfragen

§ Unteranfrage in der where-Klausel

§ Welche Prüfungen sind besser als durchschnittlich verlaufen?

select p.*
from prüfen p
where [Link] < ( select avg ([Link])
from prüfen q );
Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23
22
Datenbanken: SQL

Geschachtelte Anfragen
§ 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 [Link], [Link], (
select sum ([Link]) as Lehrbelastung
from Vorlesungen v
where [Link]=[Link] )
from Professoren p;
as dient zur Umbenennung von Attributen
Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23
23
Datenbanken: SQL

Unkorrelierte versus korrelierte Unteranfragen

Korrelierte Formulierung Äquivalente unkorrelierte Formulierung
select s.* select s.*
from Studenten s
from Studenten s
where exists
where [Link] <
(select p.*
from Professoren p (select max ([Link])
where [Link] > [Link]); from Professoren p);

§ Vorteil: Unteranfrage braucht nur einmal

ausgewertet zu werden
Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23
24
Datenbanken: SQL

Entschachtelung korrelierter Unteranfragen

select a.*
from Assistenten a
where exists
( select p.*
from Professoren p
where [Link] = [Link] and [Link] > [Link]);

§ Entschachtelung durch Join

select a.*
from Assistenten a, Professoren p
where [Link]=[Link] and [Link] > [Link];

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

25
Datenbanken: SQL

Verwertung der Ergebnismenge einer Unteranfrage

select [Link], [Link], [Link]
from (select [Link], [Link], count(*) as VorlAnzahl
from Studenten s, hören h
where [Link]=[Link]
group by [Link], [Link]) tmp
where [Link] > 2;

MatrNr Name VorlAnzahl

28106 Carnap 4
29120 Theophrastos 3
Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23
26
Datenbanken: SQL

Decision-Support-Anfrage mit geschachtelten Unteranfragen

select [Link], [Link], [Link],

[Link]/[Link] as Marktanteil
from ( select [Link], count(h1.*) as AnzProVorl
from hören h1
group by [Link] ) h,
( select count (s.*) as GesamtAnz
from Studenten s) g;

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

27
Datenbanken: SQL

Das Ergebnis der Anfrage

VorlNr AnzProVorl GesamtAnz Marktanteil

4052 1 8 .125
5001 4 8 .5
5022 2 8 .25
… … … …

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

28
Datenbanken: SQL

Anfragen mit in und all

select [Link]
from Professoren p
where [Link] not in ( select [Link]
from Vorlesungen v);

select [Link]
from Studenten s
where [Link] > = all (
select [Link] from Studenten s1);
Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23
29
Quantifizierung in SQL

30
Datenbanken: SQL

Quantifizierung in SQL

§ exists
§ Vergleiche mit all
§ Allquantifizierung: Umformung in exists
§ Allquantifizierung durch count-Aggregation

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

31
Datenbanken: SQL

Quantifizierte Anfragen in SQL

§ Existenzquantor: exists

select [Link]
from Professoren p
where not exists ( select v.*
from Vorlesungen v
where [Link] = [Link] );

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

32
Datenbanken: SQL

Existenzquantor exists

select [Link]
Kor
re latio
from Professoren p n
where not exists ( select v.*
from Vorlesungen v
where [Link] = [Link] );

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

33
Datenbanken: SQL

Mengenvergleich
Unkorrelierte
Unteranfrage: meist
effizienter, wird nur
select [Link] einmal ausgewertet
from Professoren p
where [Link] not in (
select [Link]
from Vorlesungen v);

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

34
Datenbanken: SQL

Der Vergleich mit "all"

Kein vollwertiger Allquantor!

select [Link]
from Studenten s
where [Link] >= all (
select [Link] from Studenten s1);

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

35
Datenbanken: SQL

Allquantifizierung
§ SQL-92 hat keinen Allquantor
§ Allquantifizierung muss also durch eine äquivalente Anfrage mit
Existenzquantifizierung ausgedrückt werden
§ Kalkülformulierung der Anfrage: Wer hat alle vierstündigen Vorlesungen
gehört?

{s| s ∈ Studenten ∧ ∀v ∈ Vorlesungen ([Link]=4 ⇒ ∃h ∈ hören

([Link]=[Link] ∧ [Link]=[Link]))}
§ Elimination von ∀ und ⇒
§ Dazu sind folgende Äquivalenzen anzuwenden
∀t ∈ R (P(t)) = ¬(∃t ∈ R(¬ P(t)))
R ⇒ T = ¬R V T 36
Datenbanken: SQL

Umformung des Kalkül-Ausdrucks ...

§ Wir erhalten

{s | s ∈ Studenten ∧ ¬ (∃v ∈ Vorlesungen ¬(¬([Link]=4) V

∃h ∈ hören ([Link]=[Link] ∧ [Link]=[Link]))}

§ Anwendung von DeMorgan ergibt schließlich:

{s | s ∈ Studenten ∧ ¬ (∃v ∈ Vorlesungen ([Link]=4 ∧

¬(∃h ∈ hören ([Link]=[Link] ∧ [Link]=[Link]))))}

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

37
Datenbanken: SQL

{s | s ∈ Studenten ∧ ¬ (∃v ∈ Vorlesungen ([Link]=4 ∧

¬(∃h ∈ hören ([Link]=[Link] ∧ [Link]=[Link]))))}

select s.*
from Studenten s
where not exists
(select v.*
from Vorlesungen v
where [Link] = 4 and not exists
(select h.*
from hören h
where [Link] = [Link] and [Link]=[Link] ) );
Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23
38
Datenbanken: SQL

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 [Link]
from hören h
group by [Link]
having count (h.*) =
(select count (v.*) from Vorlesungen v);
Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23
39
Datenbanken: SQL

Herausforderung

§ Wie formuliert man die komplexere Anfrage: Wer hat alle vierstündigen
Vorlesungen gehört?
§ Grundidee besteht darin, vorher durch einen Join die
Studenten/Vorlesungs-Paare einzuschränken und danach das Zählen
durchzuführen

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

40
Nullwerte

41
Datenbanken: SQL

Nullwerte

§ Semantik von Nullwerten

§ Auswertung bei Nullwerten
§ Wahrheitstabellen

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

42
Datenbanken: SQL

Semantik von Nullwerten

§ Unbekannter Wert
§ Wird vielleicht später nachgereicht
§ Nullwerte können auch im Zuge der Anfrageauswertung entstehen (Bsp.
äußere Joins)
§ Manchmal sehr überraschende Anfrageergebnisse, wenn Nullwerte
vorkommen
select count (s.*)
from Studenten s
where [Link] < 13 or [Link] > =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:
Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23
43
Datenbanken: SQL

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:
Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23
44
Datenbanken: SQL

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
45
Datenbanken: SQL

§ 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.
§ 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.
§ Bei einer Gruppierung wird null als ein eigenständiger Wert aufgefasst
und in eine eigene Gruppe eingeordnet.

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

46
Spezielle Sprachelemente

47
Datenbanken: SQL

Spezielle Sprachelemente

§ between und in
§ like
§ case

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

48
Datenbanken: SQL

Spezielle Sprachkonstrukte ("syntaktischer Zucker")

select s.* select s.* select s.*

from Studenten s from Studenten s from Studenten s
where [Link] > = where [Link] where [Link] in
1 and [Link] < between 1 and 4; (1,2,3,4);
= 4;

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

49
Datenbanken: SQL

Vergleiche mit like

Platzhalter "%" ; "_"
§ "%" steht für beliebig viele (auch gar kein) Zeichen
§ "_" steht für genau ein Zeichen
select s.*
from Studenten s
where [Link] like ‘T%eophrastos’;

select distinct [Link]

from Vorlesungen v, hören h, Studenten s
where [Link] = [Link] and [Link] = [Link] and [Link] like ‘%thik%’;
Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23
50
Datenbanken: SQL

Das case-Konstrukt

select [Link], ( case

when [Link] < 1.5 then ´sehr gut´
when [Link] < 2.5 then ´gut´
when [Link] < 3.5 then ´befriedigend´
when [Link] < 4.0 then ´ausreichend´
else ´nicht bestanden´ end)
from prüfen p;

§ Die erste qualifizierende when-Klausel wird ausgeführt

Datenbanken | Prof. Dr. Dirk Lewandowski | WS 2022/23

51