SELECT - Institute for Web Science and Technologies

Web Science & Technologies
University of Koblenz ▪ Landau, Germany
Grundlagen der Datenbanken
SQL – Teil 2
Dr. Jérôme Kunegis
Wintersemester 2013/14
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);
2
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';
3
Subanfragen und IN-Operator
 IN-Operator testet auf Mengenmitgliedschaft
 Erinnerung: Mengenoperatoren




UNION
UNION ALL
INTERSECT
MINUS
Vereinigung von Mengen (mit Duplikateliminierung)
Vereinigung von Multimengen
Schnittmenge
Set-Minus (auch EXCEPT)
 z.B.
SELECT Name FROM Assistenten
UNION
SELECT Name FROM Professoren;
SQL – Teil 2
J. Kunegis
GLDB 2013/14
4
Quantifizierende Bedingungen – IN und ALL
SELECT Name
FROM
Professoren
WHERE PersNr NOT IN ( SELECT gelesenVon
FROM Vorlesungen );
IN ist äquivalent zur quantifizierenden Bedingung = ANY.
• Quantifizierende Bedingungen haben einen Vergleichsoperator
(=, <, >, …) und den Operator IN oder ANY (alternativ auch
SOME).
IN testet ob es mindestens ein Element im Ergebnis einer Subanfrage
gibt
➔
ALL überprüft, ob alle Ergebnisse der Subanfrage einen Vergleich
erfüllen.
SQL – Teil 2
J. Kunegis
GLDB 2013/14
5
Quantifizierende Bedingungen – IN und ALL (2)
Anfrage: Studenten im höchsten Semester.
SELECT *
FROM Studenten
WHERE Semester >= ALL (SELECT Semester
FROM Studenten);
Alternativ: Verwendung von „=“ und Subanfrage mit MAX-Aggregator
Hinweis: ALL vergleicht nur einen Wert mit einer Menge
→ Kein Allquantor: Nicht möglich sind Anfragen wie „finde alle Studenten mit ...“
SQL – Teil 2
J. Kunegis
GLDB 2013/14
6
Alternative zu ALL
SELECT *
FROM Studenten
WHERE Semester >= ALL (SELECT Semester
FROM Studenten);
⇓
SELECT *
FROM Studenten
WHERE Semester = (SELECT MAX(Semester)
FROM Studenten);
SQL – Teil 2
J. Kunegis
GLDB 2013/14
7
Quantifizierte Anfragen
 Existenzquantor wird durch EXISTS umgesetzt.
 Teste ob Menge von Tupeln leer ist oder nicht (gibt TRUE oder
FALSE zurück)
 NOT EXISTS ist umgekehrt, z.B. „Professoren, die keine
Vorlesung halten“
SELECT Name
FROM
Professoren
WHERE NOT EXISTS ( SELECT *
FROM Vorlesungen
WHERE gelesenVon = PersNr );
SQL – Teil 2
J. Kunegis
GLDB 2013/14
8
Allquantifizierte Prädikate durch COUNT
 Allquantifizierung kann auch durch eine
COUNT-Aggregation ausgedrückt werden
 Nochmal eine etwas einfachere 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);
… hier wird zunächst gezählt, wie viele Vorlesungen die einzelnen Studenten
hören und dann überprüft, ob diese Anzahl gleich ist mit der Anzahl der
Tupel in Relation Vorlesungen (mit 4 SWS)
SQL – Teil 2
J. Kunegis
GLDB 2013/14
9
Hinweis: Fehlerquellen bei COUNT-Aggregation
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);
Erinnerung: SELECT-Klausel darf nur aggregierte Werte oder
Attribute nach denen gruppiert wurde enthalten.
SQL – Teil 2
J. Kunegis
GLDB 2013/14
10
Hinweis: Fehlerquellen bei COUNT-Aggregation (2)
… deshalb so:
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 BYKlausel aufgeführt werden!
SQL – Teil 2
J. Kunegis
GLDB 2013/14
11
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 );
12
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 zu 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
13
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);
14
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)
15
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));
16
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 );
17
Aggregationsfunktionen
"Grobsyntax":
{ MAX | MIN | AVG | SUM | COUNT }
( { ALL | DISTINCT } {column | expression | *} )
"Grobsemantik":
Abbildung einer Menge skalarer Werte auf einen skalaren Wert
18
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;
19
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);
20
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;
21
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
SWS gelesen Von PersNr Name
Rang Raum
5001
5041
...
4630
4
4
...
4
C4
C4
...
C4
Grundzüge
Ethik
...
Die 3 Kritiken
2137
2125
...
2137
2125
2125
...
2137
Sokrates
Sokrates
...
Kant
226
226
...
7
WHERE gelesenVon = PersNr AND Rang = 'C4'
22
Ausführen einer Anfrage mit GROUP BY (2)
WHERE gelesenVon = PersNr AND Rang =
'C4'
VorlNr Titel
SWS gelesen
Von
PersNr 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
23
VorlNr
5041
5049
4052
5043
5052
5216
5001
4630
SWS
Titel
Ethik
Mäeutik
Logik
Erkenntnistheorie
Wissenschaftstheo.
Bioethik
Grundzüge
Die 3 Kritiken
4
2
4
3
3
2
4
4
gelesenVon PersNr Name
2125
2125 Sokrates
2125
2125 Sokrates
2125
2125 Sokrates
2126
2126 Russel
2126
2126 Russel
2126
2126 Russel
2137
2137 Kant
2137
2137 Kant
Rang
C4
C4
C4
C4
C4
C4
C4
C4
Raum
226
226
226
232
232
232
7
7
HAVING AVG (SWS) >= 3
VorlNr
Titel
5041 Ethik
5049 Mäeutik
4052 Logik
SWS
4
2
4
gelesenVon
2125
2125
2125
PersNr
2125
2125
2125
Name
Sokrates
Sokrates
Sokrates
Rang
C4
C4
C4
Raum
226
226
226
5001 Grundzüge
4630 Die 3 Kritiken
4
4
2137
2137
2137
2137
Kant
Kant
C4
C4
7
7
24
Ausführen einer Anfrage mit GROUP BY (4)
HAVING AVG (SWS) >= 3
VorlNr
Titel
5041 Ethik
5049 Mäeutik
4052 Logik
SWS
4
2
4
gelesenVon
2125
2125
2125
PersNr
2125
2125
2125
Name
Sokrates
Sokrates
Sokrates
Rang
C4
C4
C4
Raum
226
226
226
5001 Grundzüge
4630 Die 3 Kritiken
4
4
2137
2137
2137
2137
Kant
Kant
C4
C4
7
7
SELECT gelesenVon, Name, SUM (SWS)
gelesenVon
Name
sum (SWS)
2125
Sokrates
10
2137
Kant
8
25
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 !
26
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
27
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
28
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;
29
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.
30
Anhang Kapitel 8
Präzise Semantik von SQL-Anfragen
31
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:
32
1. Abbildung SQL auf RA
33
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.
34
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 )
35
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
36
Abbildung SQL auf RA (2)
37
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)
38
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)
39
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)
40
Abbildung SQL auf RA (7)
sql2trc’ [EXISTS subquery] =
(so dass subquery die Form
SELECT C1, C2, ...
FROM QUELL1 Q1, ..., QUELLm’ Qm’
WHERE H
hat)
41
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)
42
3. Semantik der Gruppierung
43
Abbildung SQL auf RA (1)
sql2ra [ SELECT A', f(B)
FROM …
WHERE …
GROUP BY A ]
(wobei A' ⊆ A gelten muss)
44
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
45
Fragen?
[email protected]
http://west.uni-koblenz.de/teaching/ws1314/GdDB
SQL – Teil 2
J. Kunegis
GLDB 2013/14
46