Web Science & Technologies

University of Koblenz ▪ Landau, Germany

SQL – Teil 2

Dr. Jérôme Kunegis

Wintersemester 2013/14

Grundlagen der Datenbanken

2

Bereichsanfragen und Mitgliedschaft in der Menge

SELECT *FROM StudentenWHERE Semester >= 1 AND Semester <= 4;

SELECT *FROM StudentenWHERE Semester BETWEEN 1 and 4;

SELECT *FROM StudentenWHERE Semester IN (1,2,3,4);

Anfrage: Studenten der ersten vier Semester

3

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

4 GLDB 2013/14J. KunegisSQL – Teil 2

Subanfragen und IN-Operator

IN-Operator testet auf Mengenmitgliedschaft Erinnerung: Mengenoperatoren

UNION Vereinigung von Mengen (mit Duplikateliminierung)

UNION ALL Vereinigung von Multimengen

INTERSECT Schnittmenge

MINUS Set-Minus (auch EXCEPT)

z.B.

SELECT Name FROM Assistenten

UNION

SELECT Name FROM Professoren;

5 GLDB 2013/14J. KunegisSQL – Teil 2

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.

6 GLDB 2013/14J. KunegisSQL – Teil 2

Quantifizierende Bedingungen – IN und ALL (2)

Anfrage: Studenten im höchsten Semester.

SELECT *

FROM StudentenWHERE 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 ...“

7 GLDB 2013/14J. KunegisSQL – Teil 2

Alternative zu ALL

SELECT *

FROM StudentenWHERE Semester >= ALL (SELECT Semester

FROM Studenten);

⇓SELECT *

FROM StudentenWHERE Semester = (SELECT MAX(Semester)

FROM Studenten);

8 GLDB 2013/14J. KunegisSQL – Teil 2

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

9 GLDB 2013/14J. KunegisSQL – Teil 2

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 Studentenhören und dann überprüft, ob diese Anzahl gleich ist mit der Anzahl der

Tupel in Relation Vorlesungen (mit 4 SWS)

10 GLDB 2013/14J. KunegisSQL – Teil 2

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 oderAttribute nach denen gruppiert wurde enthalten.

11 GLDB 2013/14J. KunegisSQL – Teil 2

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 derSELECT-Klausel aufgeführten Attribute - außer den aggregierten – auch in der GROUP BY-Klausel aufgeführt werden!

12

Subqueries

• unkorellierte Subqueries:

• korrelierte Subqueries:

SELECT NameFROM ProfessorenWHERE PersNr NOT IN ( SELECT gelesenVon FROM Vorlesungen );

SELECT p.NameFROM Professoren pWHERE NOT EXISTS ( SELECT v.gelesenVon FROM Vorlesungen v WHERE v.gelesenVon = p.PersNr );

Anfrage: Professoren, die keine Vorlesungen anbieten

Anfrage: Professoren, die keine Vorlesungen anbieten

13

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

14

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

15

SQL hat keinen Allquantor.

Formulierungen der Anfrage:

Wer hat alle vierstündigen Vorlesungen gehört?

∀ x. P(x) ¬ x. ¬P(x)⇔ ∃

Simulation allquantifizierter Suchprädikate

16

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 des Allquantors in SQL

Anfrage: Wer hat alle vierstündigen Vorlesungen gehört?

17

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

Umsetzung des Allquantors in SQL:alternative Form

Anfrage: Wer hat alle vierstündigen Vorlesungen gehört?

18

Aggregationsfunktionen

"Grobsyntax":

{ MAX | MIN | AVG | SUM | COUNT } ( { ALL | DISTINCT } {column | expression | *} )

"Grobsemantik":

Abbildung einer Menge skalarer Werte auf einen skalaren Wert

19

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;

20

Aggregationsfunktionen: Beispiele (2)

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

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;

21

Gruppierung

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;

Lehrpensum (Summe der Semesterwochenstunden) pro Professor:

Lehrpensum der C4-Professoren, die überwiegend umfangreiche Lehrveranstaltungen anbieten (mit durchschnittlichem Umfang 3 SWS und mehr)

22

Ausführen einer Anfrage mit GROUP BY

FROM Vorlesungen, Professoren

VorlNr Titel SWS gelesen Von 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 gelesenVon = PersNr AND Rang = 'C4'

SELECT gelesenVon, Name, SUM (SWS)FROM Vorlesungen, ProfessorenWHERE gelesenVon = PersNr AND Rang = 'C4'GROUP BY gelesenVon, NameHAVING AVG (SWS) >= 3;

23

VorlNr Titel SWS gelesen Von

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 2125 2125 Sokrates C4 226

5052 Wissenschafts-theorie

3 2126 2126 Russel C4 232

5216 Bioethik 2 2126 2126 Russel C4 232

4630 Die 3 Kritiken 4 2137 2137 Kant C4 7

WHERE gelesenVon = PersNr AND Rang = 'C4'

GROUP BY gelesenVon, Name

Ausführen einer Anfrage mit GROUP BY (2)

24

HAVING AVG (SWS) >= 3

232

232

232

226

226

226

7

7

Raum

C4Russel212621263Erkenntnistheorie5043

C4Russel212621263Wissenschaftstheo.5052

C4Russel212621262Bioethik5216

C4Sokrates212521254Logik4052

C4Sokrates212521254Ethik5041

C4Sokrates212521252Mäeutik5049

C4Kant213721374Die 3 Kritiken4630

C4

Rang

4

SWS

Grundzüge

Titel NamePersNrgelesenVonVorlNr

Kant213721375001

RaumRangNamePersNrgelesenVonSWSTitelVorlNr

77

C4C4

KantKant

21372137

21372137

44

GrundzügeDie 3 Kritiken

50014630

C4C4C4

226226226

424

EthikMäeutikLogik

SokratesSokratesSokrates

212521252125

212521252125

504150494052

25

gelesenVon Name sum (SWS)

2125 Sokrates 10

2137 Kant 8

RaumRangNamePersNrgelesenVonSWSTitelVorlNr

77

C4C4

KantKant

21372137

21372137

44

GrundzügeDie 3 Kritiken

50014630

C4C4C4

226226226

424

EthikMäeutikLogik

SokratesSokratesSokrates

212521252125

212521252125

504150494052

HAVING AVG (SWS) >= 3

SELECT gelesenVon, Name, SUM (SWS)

Ausführen einer Anfrage mit GROUP BY (4)

26

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üfenWHERE 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 !

27

Auswertung bei Null-Werten

In arithmetischen Ausdrücken werden Nullwerte propagiert:

NULL + 1 = NULLNULL * 0 = NULL

Die Behandlung von Strings + CLOBs ist systemspezifisch:

Oracle: NULL || 'string' = 'string' MS SQL Server: NULL + 'string' = nullMySQL: 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

28

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 count(*)

1.0 25

1.3 94

… …

NULL 248

Gruppierung bei Null-Werten

29

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;

30

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.

31

Anhang Kapitel 8

Präzise Semantik von SQL-Anfragen

32

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:

33

1. Abbildung SQL auf RA

34

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 queryvon select_block-Konstrukten auf RA-Anfragenunter Verwendung der Funktion sql2ra’: sql where clause × ra query→ ra queryvon search_condition-Konstrukten auf RA-Ausdrückesowie der Hilfsfunktion sql2ra-: sql where clause × ra query→ ra querymit sql2ra- [F](E) = E - π[sch(E)] (sql2ra’[F](E) )

.. Erweiterung auf Multirelationen relativ leicht möglich.

35

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 )

36

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

37

Abbildung SQL auf RA (2)

38

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)

39

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)

40

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)

41

Abbildung SQL auf RA (7)

sql2trc’ [EXISTS subquery] = (so dass subquery die Form

SELECT C1, C2, ...

FROM QUELL1 Q1, ..., QUELLm’ Qm’

WHERE H hat)

42

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)

43

3. Semantik der Gruppierung

44

Abbildung SQL auf RA (1)

sql2ra [ SELECT A', f(B) FROM … WHERE … GROUP BY A ]

(wobei A' ⊆ A gelten muss)

45

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

46 GLDB 2013/14J. KunegisSQL – Teil 2

Fragen?

kunegis@uni-koblenz.de

http://west.uni-koblenz.de/teaching/ws1314/GdDB