SELECT - Institute for Web Science and Technologies

Web Science & Technologies
University of Koblenz ▪ Landau, Germany
Grundlagen der Datenbanken
SQL
Dr. Gerd Gröner
Wintersemester 2013/14
Lernziele
 Kenntnisse der Grundkonzepte von SQL
 Fähigkeit zur praktischen Anwendung von einfachen
SQL-Anweisungen
 Schema-Definitionen, referentielle Integrität
 SELECT-Anweisungen
 Änderungsoperationen
SQL
G. Gröner
GLDB 2013/14
2
… zur Wiederholung
Grundlagen Relationales Datenmodell:
 Abbildung ER-Diagramm → Relationales Modell
 Nachbildung der Generalisierung und Aggregation im
Relationalen Modell
 Relationale Invarianten (z.B. Primärschlüssel, …)
➔
Siehe folgende Seiten
 Relationenalgebra (z.B. Selektion, Projektion,
Mengenoperationen)
SQL
G. Gröner
GLDB 2013/14
3
Relationale Invarianten
Integritätsbedingungen des Relationenmodells
 Primärschlüsselbedingung (Entity-Integrität)
 Eindeutigkeit des Primärschlüssels
 Keine Nullwerte
 Referentielle Integrität
 Darstellung von Beziehungen durch Fremdschlüssel
(foreign key): Attribut, das in Bezug auf den
Primärschlüssel einer anderen Relation definiert ist
 Zugehöriger Primärschlüssel muss existieren, d.h. zu
jedem Wert (ungleich Null) des Fremdschlüsselattributs
einer Relation R2 muss ein gleicher Wert des
Primärschlüssels in irgendeinem Tupel von Relation R1
vorhanden sein.
SQL
G. Gröner
GLDB 2013/14
4
Relationale Invarianten (2)
 Fremdschlüssel und zugehöriger Primärschlüssel tragen wichtige
inter-relationale Information
 Sie sind auf dem gleichen Wertebereich definiert
 Sie gestatten die Verknüpfung von Relationen mit Hilfe von
Relationenoperationen
 Fremdschlüssel
 Können Nullwerte aufweisen, wenn sie nicht Teil eines
Primärschlüssels sind
 Ein Fremdschlüssel ist „zusammengesetzt“, wenn der zugehörige
Primärschlüssel „zusammengesetzt“ ist
 Eine Relation kann mehrere Fremdschlüssel besitzen, die die
gleiche oder verschiedene Relationen referenzieren
 Zyklen sind möglich (geschlossener referentieller Pfad)
 Eine Relation kann zugleich referenzierende und referenzierte
Relation sein („self-referencing table“)
SQL
G. Gröner
GLDB 2013/14
5
Komponenten der vollständigen DB-Sprache
Datenmanipulation
 Einfügen, Löschen und ändern von Tupeln
 Zuweisung von ganzen Relationen
Datendefinition
 Definition von Wertebereichen, Attributen und Relationen
 Definition von verschiedenen Sichten auf Relationen
Datenkontrolle
 Spezifikation von Bedingungen zur Zugriffskontrolle
 Spezifikation von Zusicherungen (Assertions) zur semantischen
Integritätskontrolle
Datenabfragen
 Abbildung von Eingaberelationen durch Auswertung von
Bedingungen auf die Ereignisrelation
Kopplung mit einer Hostsprache (z.B. PHP)
 Deskriptive Auswahl von Mengen von Tupeln
 Sukzessive Bereitstellung einzelner Tupel
SQL
G. Gröner
GLDB 2013/14
6
Relationale Anfragesprache
 Begriff „Anfragesprache“ historisch geprägt, beinhaltet
auch Datendefinition und Datenmanipulation
 SQL (früher SEQUEL)
 Deklarative Anfragesprache
 Theoretische Grundlage: Relationale Algebra
SQL
G. Gröner
GLDB 2013/14
7
Datentypen in SQL
3 fundamentale Typen: Zahlen, Zeichenketten und
Datumstyp
CHAR(n) – String mit fester Länge
VARCHAR(n) – String mit max. Länge n
INTEGER – Integer
FLOAT – Float, Fließkommazahl
DATE – Datum
BLOB – Binary Large Object
...
SQL
G. Gröner
für große Binärdateien, nicht
zu interpretierende Daten von
externen Anwendungssystemen
GLDB 2013/14
8
Beispiel: Universität
SQL
G. Gröner
GLDB 2013/14
9
SQL: Create Table
→ zur Schemadefinition
(Erinnerung: Schema wird automatisch in Datenwörterbuch gespeichert)
CREATE TABLE Assistenten
(
PersNr INT PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
Fachgebiet VARCHAR(100),
Boss INT FOREIGN KEY (Boss) REFERENCES Professoren(PersNr)
ON DELETE SET NULL
);
SQL
G. Gröner
GLDB 2013/14
11
SQL: Alter Table
→ zur Schemaveränderung
z.B. nachträgliches Einfügen des Attributs „Raum“
ALTER TABLE Professoren
ADD (Raum INTEGER);
z.B. nachträgliches Ändern der Zeichenlänge des Attributs „Name“
ALTER TABLE Professoren
MODIFY (Name VARCHAR(30));
SQL
G. Gröner
GLDB 2013/14
12
Einfügen von Tupeln
 Werte direkt angeben:
INSERT INTO Professoren
VALUES (2136, 'Curie', 'C4', 36);
Attributwerte in
Reihenfolge
der Definition
 Werte durch Anfrage generiert:
INSERT INTO hören
SELECT MatrNr, VorlNr
FROM Studenten, Vorlesungen
WHERE Titel = 'Logik';
SQL
G. Gröner
Bedeutung:
„... jeder Student hört
Vorlesung Logik“
GLDB 2013/14
13
Einfügen von Tupeln (2)
Nur einen Teil der Attribute einfügen (z.B. wenn einige Werte
unbekannt sind)
INSERT INTO Studenten (MatrNr, Name)
VALUES (28121,
Angabe der
gewünschten
Attribute
'Archimedes');
NULL-Wert
SQL
G. Gröner
GLDB 2013/14
14
Auswertung bei NULL-Werten
Arithmetische Ausdrücke:
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.
Logische Ausdrücke:
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.
→ siehe folgende Tabelle
SQL
G. Gröner
GLDB 2013/14
15
Auswertung bei NULL-Werten (2)
SQL
G. Gröner
GLDB 2013/14
16
Löschen und Ändern
 Löschen von Tupeln
DELETE FROM Studenten
WHERE Semester > 13;
 Ändern von Tupeln
UPDATE Studenten
SET Semester = Semester + 1;
SQL
G. Gröner
GLDB 2013/14
17
SQL-Anfragen – Syntax
"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] ]
SQL
G. Gröner
GLDB 2013/14
18
SQL-Anfrage
SELECT PersNr, Name
FROM Professoren
WHERE Rang= 'C4';
SQL
G. Gröner
GLDB 2013/14
19
Sortierung
SELECT PersNr, Name, Rang
FROM
Professoren
ORDER BY Rang DESC, Name ASC;
- ohne Angabe: aufsteigend (ASC)
SQL
G. Gröner
GLDB 2013/14
20
Duplikateliminierung
SELECT DISTINCT Rang
FROM Professoren;
SQL
G. Gröner
GLDB 2013/14
21
Anfragen über mehrere Relationen
 Welcher Professor liest „Mäeutik“?
SELECT Name, Titel
FROM Professoren, Vorlesungen
WHERE PersNr = gelesenVon
AND Titel = 'Mäeutik'
verbinden von
Professoren und
Vorlesungen
Bedeutung: „wähle Name und Titel aus der Kombination von Professoren und
Vorlesungen, wo gelesenVon und PersNr übereinstimmt und Vorlesung
„Mäeutik“ ist.
SQL
G. Gröner
GLDB 2013/14
23
Anfragen über mehrere Relationen (2)
 Abarbeitung der vorherigen Anfrage
1. Kreuzprodukt der Tabelle bilden
2. Jede Zeile des Kreuzprodukts auf Erfüllung der Bedingung
aus dem WHERE-Teil überprüfen
3. Projektion anhand des SELECT-Teils
In Relationenalgebra:
SQL
G. Gröner
GLDB 2013/14
24
SQL
G. Gröner
GLDB 2013/14
25
Anfragen über mehrere Relationen (4)
 Welche Studenten hören welche Vorlesungen?
SELECT Name, Titel
FROM Studenten, hören, Vorlesungen
WHERE Studenten.MatrNr = hören.MatrNr AND
hören.VorlNr = Vorlesungen.VorlNr;
Alternativ: mit Tupelvariablen
sinnvoll wenn die selbe
Relation mehrfach
vorkommt!
SELECT s.Name, v.Titel
FROM Studenten s, hören h, Vorlesungen v
WHERE s.MatrNr = h.MatrNr AND
h.VorlNr = v.VorlNr;
SQL
G. Gröner
GLDB 2013/14
26
Anfragen über mehrere Relationen (5)
SELECT
s1.Name, s2.Name
FROM
Studenten s1, hören h1, hören h2, Studenten s2
WHERE
h1.VorlNr = h2.VorlNr AND
h1.MatrNr = s1.MatrNr AND
h2.MatrNr = s2.MatrNr;
SQL
G. Gröner
GLDB 2013/14
27
Mengenoperationen
 Mengenoperationen UNION, INTERSECT, MINUS
SELECT Name
FROM Assistenten
MINUS
SELECT Name
FROM Professoren;
SELECT Name
FROM Assistenten
UNION
SELECT Name
FROM Professoren;
SELECT Name
FROM Assistenten
INTERSECT
SELECT Name
FROM Professoren;
SQL
G. Gröner
GLDB 2013/14
28
Join (Verbund)
 Direkte Angabe eines Join-Operators im from-Teil:
 CROSS JOIN: Kreuzprodukt
 NATURAL JOIN: nätürlicher Join
 JOIN oder INNER JOIN: Theta-Join
 LEFT, RIGHT oder FULL OUTER JOIN: äußerer Join
 aus:
SELECT *
FROM R1, R2
WHERE R1.A = R2.B ;
 wird:
SELECT *
FROM R1 JOIN R2 ON R1.A = R2.B
SQL
G. Gröner
GLDB 2013/14
29
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;
SQL
G. Gröner
GLDB 2013/14
30
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;
SQL
G. Gröner
GLDB 2013/14
31
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;
SQL
G. Gröner
GLDB 2013/14
32
Left Outer Join
SELECT
FROM
SQL
p.PersNr, p.Name, f.PersNr, f.Note,
f.MatrNr, s.MatrNr, s.Name
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
-
-
-
-
-
…
…
…
…
…
…
…
G. Gröner
GLDB 2013/14
33
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
…
…
…
…
…
…
…
SQL
G. Gröner
GLDB 2013/14
34
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
-
-
-
-
-
…
…
…
…
…
…
…
SQL
G. Gröner
GLDB 2013/14
35
Aggregatfunktionen und Gruppierung
 Aggregatfunktionen:
 Operationen auf Tupelmengen
 komprimieren eine Menge von Werten zu einem Wert
• AVG, MAX, MIN, SUM und COUNT
 Bsp.: durchschnittliche Semesterzahl aller Studenten
SELECT AVG(Semester)
FROM Studenten;
 Bsp.: höchste Semesterzahl aller Studenten
SELECT MAX(Semester)
FROM Studenten;
SQL
G. Gröner
GLDB 2013/14
36
Aggregatfunktionen und Gruppierung (2)
 Aggregatfunktionen sind nützlich zusammen mit
Gruppierungen
• GROUP BY
 Bsp.: Lehrpensum (Summe Wochenstunden) pro Professor
SELECT gelesenVon, SUM(SWS)
FROM Vorlesungen
hier werden alle Zeilen der Tabelle
GROUP BY gelesenVon;
Vorlesungen, die den selben Wert
Im Attribut gelesenVon haben
Zusammengefasst;
für jede der so entstandenen
Gruppen wird die Summe der
SWS berechnet.
SQL
G. Gröner
GLDB 2013/14
37
Aggregatfunktionen und Gruppierung (3)
 Lehrpensum der C4-Professoren, die überwiegend
umfangreiche Lehrveranstaltungen anbieten (mit
durchschnittlichem Umfang von 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;
Hinweis: Name muss in GROUP BY, da
- jedes Ausgabetupel eine Gruppe ist
→ SELECT-Klausel darf nur aus
(1) Aggregatfunktionen und
(2) Attributen nach denen gruppiert
wird bestehen
SQL
G. Gröner
GLDB 2013/14
38
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
Grundzüge
Ethik
...
Die 3 Kritiken
4
4
...
4
2137
2125
...
2137
2125
2125
...
2137
Sokrates
Sokrates
...
Kant
C4
C4
...
C4
226
226
...
7
WHERE gelesenVon = PersNr AND Rang = 'C4'
SQL
G. Gröner
GLDB 2013/14
39
Ausführen einer Anfrage mit GROUP BY (2)
WHERE gelesenVon = PersNr AND Rang = 'C4'
VorlNr
Titel
SWS gelesen
Von
PersNr Name
Rang Raum
C4
C4
C4
C4
C4
C4
7
226
232
226
226
232
C4
C4
232
7
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
5216
4630
Bioethik
Die 3 Kritiken
2
4
2126
2137
2126 Russel
2137 Kant
GROUP BY gelesenVon, Name
SQL
G. Gröner
GLDB 2013/14
40
SQL
G. Gröner
GLDB 2013/14
41
Ausführen einer Anfrage mit GROUP BY (4)
SQL
G. Gröner
GLDB 2013/14
42
Geschachtelte Anfragen
 Prinzip: „Ergebnis einer inneren Anfrage (Subanfrage,
Subquery, Unteranfrage) wird in der äußeren Anfrage
verwendet“
 Unterscheidung bzgl. Ergebnis der Subanfrage:
 Ein Tupel mit nur einem Attribut
• Behandlung wie skalarer Wert möglich (in SELECT und
WHERE-Klausel)!
 Mehrere Tupel
SELECT *
FROM prüfen
WHERE Note = ( SELECT AVG(Note) FROM prüfen);
wie skalarer Wert
SQL
G. Gröner
GLDB 2013/14
43
Geschachtelte Anfragen (2)
 Nochmal: „Lehrpensum der Professoren“
SELECT PersNr,Name, (SELECT SUM(SWS) AS Lehrbelastung
FROM Vorlesungen
WHERE gelesenVon = PersNr)
FROM Professoren);
Beobachtung (im Vgl. vorheriger Anfrage):
Subanfrage (innere Anfrage) verwendet Attribute (PersNr) von Tupel der
äußeren Anfrage.
➔
Subanfrage ist korreliert mit äußeren Anfrage.
SQL
G. Gröner
GLDB 2013/14
44
Subanfragen (unkorreliert und korreliert)
 Beispielanfrage: „Professoren, die keine Vorlesung anbieten.“
 Unkorrelierte Subanfrage:
SELECT Name
FROM Professoren
WHERE PersNr NOT IN
( SELECT gelesenVon FROM Vorlesungen );
 Korrelierte Subanfrage
SELECT p.Name
FROM Professoren p
WHERE NOT EXISTS
( SELECT v.gelesenVon
FROM Vorlesungen v
WHERE v.gelesenVon = p.PersNr );
SQL
G. Gröner
GLDB 2013/14
45
Subanfragen (unkorreliert und korreliert) (2)
 Unkorrelierte Anfrage ist nicht immer möglich, z.B.
SELECT a.*
FROM Assistenten a
WHERE EXISTS
(SELECT p.*
FROM Professoren p
WHERE a.Boss = p.PersNr AND p.GebDatum > a.GebDatum );
.. wegen diesem Prädikat
Aber: Anfrage kann ohne Subanfrage gestellt werden:
SELECT a.*
FROM Assistenten a, Professoren p
WHERE a.Boss = p.PersNr AND p.GebDatum > a.GebDatum ;
SQL
G. Gröner
GLDB 2013/14
46
Weitere Subanfragen
 Beobachtung in vorheriger Subanfrage: EXISTS-Operator
bildet Subanfrage auf atomaren Wert (true, false) ab.
 Alternativ: Tupel der Subanfrage als Kollektion weiter zu
verwenden, z.B.
 Als Argument für Mengenoperationen
 Wie eine Relation in der FROM-Klausel, z.B.
SELECT tmp.MatrNr, tmp.Name, tmp:VorlAnzahl
FROM (SELECT s.MatrNr, s:name, COUNT(*) AS VorlAnzahl
FROM Studenten s, hören h
WHERE a.MatrNr = h.MatrNr
GROUP By s.MatrNr, s.Name) tmp
WHERE tmp.VorlAnzahl > 2;
Alternative zur Schachtelung: mit HAVING-Klausel
SQL
G. Gröner
GLDB 2013/14
47
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
G. Gröner
GLDB 2013/14
48
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
G. Gröner
GLDB 2013/14
49
Quantifizierende Bedingungen – IN und ALL (2)
Anfrage: Studenten im höchsten Semester.
SELECT *
FROM Studenten
WHERE Semester >= ALL (SELECT Semester
FROM Studenten);
Alternativ: 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
G. Gröner
GLDB 2013/14
50
Quantifizierte Anfragen
 Existenzquantor wird durch EXISTS umgesetzt.
 Teste ob Menge von Tupeln leer ist oder nicht (Abb. auf
true, false)
 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
G. Gröner
GLDB 2013/14
51
Quantifizierte Anfragen (2)
 Ziel: Allquantor umsetzen!
 Idee: logische Umformung (konkret: Allquantor wird
eliminiert)
 Beispiel: „Studenten, die alle vierstündigen Vorlesungen
hören“
Wir können folgende Äquivalenzen verwenden:
t … Tupel
R … Relation
P … Prädikat
SQL
G. Gröner
GLDB 2013/14
52
Quantifizierte Anfragen (3)
… dies ergibt dann:
… jetzt noch Negation „nach innen“ (DeMorgan-Gesetz):
… → dies kann mit NOT EXISTS in SQL dargestellt werden
SQL
G. Gröner
GLDB 2013/14
53
Quantifizierte Anfragen (4)
… als geschachtelte SQL-Anfrage
SELECT s.*
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));
SQL
G. Gröner
GLDB 2013/14
54
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
G. Gröner
GLDB 2013/14
55
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
G. Gröner
GLDB 2013/14
56
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
BY-Klausel aufgeführt werden!
SQL
G. Gröner
GLDB 2013/14
57
Quantifizierte Anfragen (4)
 Alternative Umsetzung der vorherigen Anfrage:
SELECT s.*
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 );
SQL
G. Gröner
GLDB 2013/14
58
Fragen?
[email protected]
http://west.uni-koblenz.de/teaching/ws1314/GLDB1314
SQL
G. Gröner
GLDB 2013/14
59