View
230
Download
0
Category
Preview:
Citation preview
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.
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:
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
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)
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
Recommended