SQL Continued - Institute for Web Science and Technologies

Kapitel 8
Dr. Jérôme Kunegis
SQL Continued
WeST –
Institute for Web Science & Technologies
Lernziele
• Syntax von SQL Anfragen
• Erweiterte Konstrukte in SQL Abfragen:
Aggregation, Gruppierung, rekursive Anfragen ..
• Präzise Semantik von SQL Anfragen:
Abbildung auf RA
2
Einfache SQL Anfragen
"Grobsyntax":
select_block { { UNION | INTERSECT | MINUS } [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] ]
3
Oracle: Syntaxdiagramm
4
Mengenoperationen
 UNION
 UNION ALL
 INTERSECT
 MINUS
Vereinigung von Mengen
Vereinigung von Multimengen
Schnittmenge
Set-Minus (a.k.a. EXCEPT)
Anfrage:
Namen von allen Uni-Angehörigen
SELECT Name FROM Assistenten
UNION
SELECT Name FROM Professoren
UNION
SELECT Name FROM Techniker;
5
Korrelationsvariablen (Tupelvariablen)
Anfrage:
alle Paare von Studenten, die in derselben Vorlesung sitzen
SELECT s1.Name, s2.Name
FROM Studenten s1, hören h1,
Studenten s2, hören h2
WHERE
h1.VorlNr = h2.VorlNr AND
h1.MatrNr = s1.MatrNr AND
h2.MatrNr = s2.MatrNr AND
h1.MatrNr < h2.MatrNr;
6
Join
"Grobsyntax" inner joins:
...
FROM <table1>
[INNER | NATURAL | CROSS] JOIN
<table2>
[ON <condition>] | [USING <column_name>, ... ], ...
...
"Grobsyntax" outer joins:
…
FROM <table1>
{LEFT | RIGHT | FULL [OUTER]} | UNION JOIN
<table2>
[ON <condition>] | [USING <column_name>, ... ] , ...
...
7
Inner Join
Anfrage:
welcher Professor bietet welche Vorlesungen an?
SELECT p.Name, v.Name
FROM Professoren p INNER JOIN Vorlesungen v
ON p.PersNr = v.gelesenVon;
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;
8
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;
9
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;
10
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.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
2136
Curie
Ø
Ø
Ø
Ø
Ø
…
…
…
…
…
…
…
11
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.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
…
…
…
…
…
…
…
12
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.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
Ø
Ø
Ø
Ø
Ø
…
…
…
…
…
…
…
13
Bereichsanfragen und Mitgliedschaft in der Menge
Anfrage:
Studenten der ersten vier Semester
SELECT *
FROM Studenten
WHERE Semester >= 1 AND Semester <= 4;
SELECT *
FROM Studenten
WHERE Semester BETWEEN 1 and 4;
SELECT *
FROM Studenten
WHERE Semester IN (1,2,3,4);
14
Subqueries
• unkorellierte Subqueries:
Anfrage: Professoren, die keine Vorlesungen anbieten
SELECT Name
FROM Professoren
WHERE PersNr NOT IN
( SELECT gelesenVon FROM Vorlesungen );
• korrelierte Subqueries:
Anfrage: Professoren, die keine Vorlesungen anbieten
SELECT p.Name
FROM Professoren p
WHERE NOT EXISTS
( SELECT v.gelesenVon
FROM Vorlesungen v
WHERE v.gelesenVon = p.PersNr );
15
String-Pattern-Matching
Anfrage:
Studenten, deren Name 'Schulz' ist:
SELECT * FROM Studenten WHERE Name = 'Schulz';
Anfrage:
Studenten, deren Name mit 'A' beginnt:
SELECT * FROM Studenten WHERE Name LIKE 'A%';
Anfrage:
Studenten mit Namen Mayer, Meyer, Mayor, …:
SELECT * FROM Studenten WHERE Name LIKE 'M_y_r';
16
"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 äquivalent 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
17
"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);
18
Simulation allquantifizierter Suchprädikate
SQL hat keinen Allquantor.
Formulierungen der Anfrage:
Wer hat alle vierstündigen Vorlesungen gehört?
∀ x. P(x)
⇔
¬ ∃ x. ¬P(x)
19
Umsetzung des Allquantors 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));
20
Umsetzung des Allquantors in SQL:
alternative Form
Anfrage:
Wer hat alle vierstündigen Vorlesungen gehört?
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 );
21
Aggregationsfunktionen
"Grobsyntax":
{ MAX | MIN | AVG | SUM | COUNT }
( { ALL | DISTINCT } {column | expression | *} )
"Grobsemantik":
Abbildung einer Menge skalarer Werte auf einen skalaren Wert
22
Aggregationsfunktionen: Beispiele (1)
1) Das höchste Semester unter allen Studenten:
SELECT MAX (Semester) FROM Studenten;
2) Notendurchschnitt aller Prüfungsergebnisse:
SELECT AVG (Note) FROM prüfen;
23
Aggregationsfunktionen: Beispiele (2)
3) Welche Studenten haben bereits einige Prüfungen hinter sich?
SELECT DISTINCT MatrNr FROM prüfen;
4) Wieviele Studenten haben bereits Prüfungen hinter sich?
SELECT COUNT (DISTINCT MatrNr) FROM prüfen ;
5) Wieviele Studenten sind im 15. Semester?
SELECT COUNT (*) FROM Studenten WHERE Semester = 15;
6) Prüfungsleistungen, die über dem globalen Durchschnitt liegen:
SELECT p.Note FROM prüfen p
WHERE p.Note < (SELECT AVG (p1.Note) FROM prüfen p1);
24
Gruppierung
Lehrpensum (Summe der Semesterwochenstunden) pro Professor:
SELECT gelesenVon, SUM (SWS)
FROM Vorlesungen
GROUP BY gelesenVon;
Lehrpensum der C4-Professoren, die überwiegend
umfangreiche Lehrveranstaltungen anbieten
(mit durchschnittlichem Umfang 3 SWS und mehr)
SELECT gelesenVon, Name, SUM (SWS)
FROM Vorlesungen, Professoren
WHERE gelesenVon = PersNr AND Rang = 'C4'
GROUP BY gelesenVon, Name
HAVING AVG (SWS) >= 3;
25
Ausführen einer Anfrage mit GROUP BY
SELECT gelesenVon, Name, SUM (SWS)
FROM Vorlesungen, Professoren
WHERE gelesenVon = PersNr AND Rang = 'C4'
GROUP BY gelesenVon, Name
HAVING AVG (SWS) >= 3;
FROM Vorlesungen, Professoren
VorlNr
Titel
5001 Grundzüge
5041
Ethik
...
...
4630 Die 3 Kritiken
SWS
gelesen
Von
4
4
...
4
2137
2125
...
2137
PersNr
Name
2125 Sokrates
2125 Sokrates
...
...
2137
Kant
Rang Raum
C4
C4
...
C4
226
226
...
7
WHERE gelesenVon = PersNr AND Rang = 'C4'
26
Ausführen einer Anfrage mit GROUP BY (2)
WHERE gelesenVon = PersNr AND Rang = 'C4'
VorlNr
Titel
SWS gelesen PersNr
Von
Name
Rang Raum
5001
5041
5043
5049
4052
5052
Grundzüge
Ethik
Erkenntnistheorie
Mäeutik
Logik
Wissenschaftstheorie
4
4
3
2
4
3
2137
2125
2126
2125
2125
2126
2137
2125
2126
2125
2125
2126
Kant
Sokrates
Russel
Sokrates
Sokrates
Russel
C4
C4
C4
C4
C4
C4
7
226
232
226
226
232
5216
4630
Bioethik
Die 3 Kritiken
2
4
2126
2137
2126
2137
Russel
Kant
C4
C4
232
7
GROUP BY gelesenVon, Name
27
SWS gelesenVon PersNr Name
VorlN
Titel
r
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 Name
4
2125
2125 Sokrates
2
2125
2125 Sokrates
4
2125
2125 Sokrates
4
4
2137
2137
2137
2137
Kant
Kant
Rang Raum
C4
226
C4
226
C4
226
C4
C4
7
7
28
Ausführen einer Anfrage mit GROUP BY (4)
HAVING AVG (SWS) >= 3
VorlN
r
5041
5049
4052
Titel
Ethik
Mäeutik
Logik
5001
4630
Grundzüge
Die 3 Kritiken
SWS gelesenVon PersNr Name
4
2125
2125 Sokrates
2
2125
2125 Sokrates
4
2125
2125 Sokrates
4
4
2137
2137
2137
2137
Kant
Kant
Rang Raum
C4
226
C4
226
C4
226
C4
C4
7
7
SELECT gelesenVon, Name, SUM (SWS)
gelesenVon
Name
sum
(SWS)
2125
Sokrates
10
2137
Kant
8
29
Simulation allquantifizierter Suchprädikate
durch count-Aggregation
Allquantifizierung kann auch durch eine
COUNT-Aggregation ausgedrückt werden
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);
30
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);
31
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!
32
Behandlung von Nullwerten
"Grobsemantik": Unbekannter Wert.
Nullwerte können auch im Zuge der Anfrageauswertung entstehen
(z.B. Outer Joins)
manchmal überraschende Anfrageergebnisse,
wenn Nullwerte vorkommen:
SELECT COUNT(*) FROM prüfen
WHERE Note > 2.0 OR Note <= 2.0 ;
ist NICHT immer identisch mit
SELECT COUNT(*) FROM prüfen ;
Grund: wenn es Prüfungen gibt, deren Note-Attribut (noch)
den Wert NULL hat, werden diese nicht mitgezählt !
33
Auswertung bei Null-Werten
In arithmetischen Ausdrücken werden Nullwerte propagiert:
NULL + 1 = NULL
NULL * 0 = NULL
Die Behandlung von Strings + CLOBs ist systemspezifisch:
Oracle: NULL || 'string' = 'string'
MS SQL Server: NULL + 'string' = null
MySQL: concat(NULL, 'string') = NULL (btw, || equivalent zu
OR)
SQL hat eine dreiwertige Logik mit TRUE, FALSE, UNKNOWN.
Diesen Wert liefern Vergleichsoperationen zurück, wenn
mindestens eines ihrer Argumente NULL ist.
z.B. (Note <= 2.0) liefert UNKNOWN falls Note NULL ist
34
Gruppierung bei Null-Werten
Bei einer Gruppierung wird null als ein eigenständiger Wert
aufgefasst und in eine eigene Gruppe eingeordnet:
SELECT Note, count(*)
FROM prüfen
GROUP BY Note
Note
1.0
count(*)
25
1.3
94
…
NULL
…
248
35
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;
36
Rekursive Anfragen
…für transitive Hüllen u.ä.
Oracle Syntax:
START WITH <condition> CONNECT BY [NOCYCLE] <condition>
Anfrage:
alle Vorlesungen, die man vor der Vorlesung 5041 (Ethik)
besucht haben sollte
SELECT Vorgänger FROM voraussetzen
START WITH Nachfolger = 5041
CONNECT BY NOCYCLE PRIOR Vorgänger = Nachfolger
AND Vorgänger != 5041
37
Built-in-Funktionen auf skalaren Werten
Häufig produktspezifisch, z.B.
• Stringmanipulation in Oracle:
SELECT SUBSTR (Name, INSTR(Name, ' ')+1) FROM Studenten
• Umwandlung eines Datums (Datentyp DATE) in einen String:
SELECT
TO_CHAR(SYSDATE, 'DY DD MONTH YYYY, HH24:MI:SS')
FROM DUAL;
etc.
38
Anhang Kapitel 8
Präzise Semantik von SQL-Anfragen
39
Abbildung SQL auf TRK und RA
"Grobsemantik":
SELECT A, B, ...
FROM R, S, ..., T, ...
WHERE F
(so dass A, B, ... zu R, S, ... gehören, nicht aber zu T, ...,
und F über R, S, ..., T, ... definiert ist)
→ RA:
→ TRK:
40
1. Abbildung SQL auf RA
41
Präzise Semantik einfacher SQL-Anfragen:
Abbildung auf RA
Voraussetzungen:
1) Vernachlässigung von Multimengen, Nullwerten u.ä.
2) Eindeutige Benennung von Tupelvariablen und Zuordnung von Attributen
Vorgehensweise:
Definition einer Abbildungsfunktion
sql2ra: sql query → ra query
von select_block-Konstrukten auf RA-Anfragen
unter Verwendung der Funktion
sql2ra’: sql where clause × ra query→ ra query
von search_condition-Konstrukten auf RA-Ausdrücke
sowie der Hilfsfunktion
sql2ra-: sql where clause × ra query→ ra query
mit sql2ra- [F](E) = E - π[sch(E)] (sql2ra’[F](E) )
.. Erweiterung auf Multirelationen relativ leicht möglich.
42
Abbildung SQL auf RA (1)
sql2ra [ SELECT A1, A2, …
FROM REL1 R1, REL2 R2, …, RELm Rm,
TAB1 T1, TAB2 T2, …, TABk Tk
WHERE F ]
(so dass A1, A2, ..., An zu REL1, REL2, …, RELm gehören,
nicht aber zu TAB1, …, TABk
und F über REL1, ..., RELm, TAB1, …, TABk definiert ist )
43
Abbildung SQL auf RA (2)
sql2ra [ select-block1 UNION select-block2 ]
mit select-block1:
SELECT A1, A2, …
FROM
REL1 R1, …, RELm Rm,
TAB1 T1, …, TABk Tk
WHERE F
und select-block2:
SELECT B1, B2, ...
FROM
SET1 S1, ..., SETm’ Sm’,
PAR1 P1, ..., PARk’ Pk’
WHERE G
.. mit ggf. notwendigen Umbenennungen von Attributen
44
Abbildung SQL auf RA (2)
45
Abbildung SQL auf RA (4)
sql2trc’ [Ri.Aj IN subquery]
(so dass subquery die Form
SELECT Qk.C
FROM QUELL1 Q1, ..., QUELLm’ Qm’
WHERE H
hat)
46
Abbildung SQL auf RA (5)
sql2trc’ [Ri.Aj θ ANY subquery] =
(so dass subquery die Form
SELECT Qk.C
FROM QUELL1 Q1, ..., QUELLm’ Qm’
WHERE H
hat)
47
Abbildung SQL auf RA (6)
sql2trc’ [Ri.Aj θ ALL subquery] =
(so dass subquery die Form
SELECT Qk.C
FROM QUELL1 Q1, ..., QUELLm’ Qm’
WHERE H
hat)
48
Abbildung SQL auf RA (7)
sql2trc’ [EXISTS subquery] =
(so dass subquery die Form
SELECT C1, C2, ...
FROM QUELL1 Q1, ..., QUELLm’ Qm’
WHERE H
hat)
49
Abbildung SQL auf RA: Beispiel
query = SELECT s.MatrNr, s.Name FROM Studenten s
WHERE s.Semester > 5
AND NOT EXISTS
(SELECT * FROM hören h, Vorlesungen v
WHERE v.SWS = 4 AND
v.vorlNr = h.vorlNr AND
s.MatrNr = h.MatrNr)
50
3. Semantik der Gruppierung
51
Abbildung SQL auf RA (1)
sql2ra [ SELECT A', f(B)
FROM …
WHERE …
GROUP BY A ]
(wobei A' ⊆ A gelten muss)
52
Abbildung SQL auf RA (1)
sql2ra [ SELECT A', f(B)
FROM …
WHERE …
GROUP BY A
HAVING cond(A,g(C))]
(wobei A' ⊆ A gelten muss)
MRA-Programm
53