select

Rückblick
§ SQL bietet viele Möglichkeiten zur Anfrageformulierung
§ mathematische Funktionen (z.B. ABS(A) und SIGN(A))
§ Aggregatfunktionen (z.B. MIN(A) und SUM(A))
§ Boole‘sche Operatoren (AND, OR, EXCEPT)
§ Verknüpfungen von mehreren Tabellen (JOINs)
§ Data Manipulation Language (DML) zum Einfügen,
Ändern und Löschen von Tupeln in Tabellen
§ SQLite als RDBMS zum Üben zu Hause
Datenbanken / Kapitel 4: Structured Query Language (SQL)
66
Unteranfragen
§ SQL unterstützt Unteranfragen, d.h. ein SELECT
Kommando kann häufig anstelle einer Tabelle oder
einer Wertemenge verwendet werden
§ Beispiel: Studenten mit einem Vornamen, den es auch
unter den Professoren gibt
1
2
3
SELECT *
FROM Studenten
WHERE Vorname IN ( SELECT Vorname FROM Professoren )
Datenbanken / Kapitel 4: Structured Query Language (SQL)
67
Umbenennung
§ SQL erlaubt die Umbenennung von Tabellen und
Attributen; dies ist insbesondere dann nützlich, wenn die
gleiche Tabelle mehrfach verwendet werden soll
§ Beispiel: Studenten, die eine gemeinsame Vorlesung hören
1
2
3
4
5
SELECT
FROM
WHERE
AND
AND
DISTINCT s1 . Name AS StudentEins , s2 . Name AS StudentZwei
Studenten s1 , h ö ren h1 , h ö ren h2 , Studenten s2
s1 . MatrNr = h1 . MatrNr
h1 . VorlNr = h2 . VorlNr
h2 . MatrNr = s2 . MatrNr
Datenbanken / Kapitel 4: Structured Query Language (SQL)
68
Korrelierte Unteranfragen
§ Unteranfragen heißen korreliert, wenn sie Bezug auf die
übergeordnete Anfrage nehmen, ansonsten unkorreliert
§ Beispiel: Unkorrelierte Unteranfrage
1
2
3
SELECT *
FROM Studenten
WHERE Vorname IN ( SELECT Vorname FROM Professoren )
§ Beispiel: Korrelierte Unteranfrage
1
2
3
4
5
SELECT *
FROM Studenten s
WHERE Vorname IN ( SELECT Vorname
FROM Professoren p
WHERE p . Name = s . Name )
Datenbanken / Kapitel 4: Structured Query Language (SQL)
69
4.4 Äußere Joins
§ SQL unterstützt äußere Joins mittels der
(LEFT|FULL|RIGHT) OUTER JOIN Kommandos
§ Beispiel: Professoren und Studenten mit gleichem Namen
(bei Erhaltung aller Professoren)
1
2
3
SELECT *
FROM Professoren LEFT OUTER JOIN Studenten
ON Professoren . Name = Studenten . Name
P.PersNr
P.Vorname
P.Name
P.Fach
S.MatrNr
S.Vorname
S.Name
S.Semester
101101
231011
300128
478122
600321
Donald
Albert
Alfred
Donald
Carl
Knuth
Einstein
Nobel
Kossmann
Gauss
Informatik
Informatik
Chemie
Informatik
Mathematik
19882
Nora
Nobel
3
18979
Gundula
Gauss
1
Datenbanken / Kapitel 4: Structured Query Language (SQL)
70
Äußere Joins
§ Beispiel: Professoren und Studenten mit gleichem Namen
(bei Erhaltung aller Studenten)
1
2
3
SELECT *
FROM Professoren RIGHT OUTER JOIN Studenten
ON Professoren . Name = Studenten . Name
P.PersNr
P.Vorname
P.Name
P.Fach
S.MatrNr
S.Vorname
S.Name
S.Semester
600321
Carl
Gauss
Mathematik
300128
Alfred
Nobel
Chemie
18979
19880
19882
20020
Gundula
Petra
Nora
Emma
Gauss
Parker
Nobel
Ernst
1
1
3
3
Datenbanken / Kapitel 4: Structured Query Language (SQL)
71
Äußere Joins
§ Beispiel: Professoren und Studenten mit gleichem Namen
(bei Erhaltung aller Professoren und Studenten)
1
2
3
SELECT *
FROM Professoren FULL OUTER JOIN Studenten
ON Professoren . Name = Studenten . Name
P.PersNr
101101
231011
300128
478122
600321
P.Vorname
Donald
Albert
Alfred
Donald
Carl
P.Name
Knuth
Einstein
Nobel
Kossmann
Gauss
P.Fach
Informatik
Informatik
Chemie
Informatik
Mathematik
Datenbanken / Kapitel 4: Structured Query Language (SQL)
S.MatrNr
S.Vorname
S.Name
S.Semester
19880
Petra
Parker
1
19882
Nora
Nobel
3
18979
20020
Gundula
Emma
Gauss
Ernst
1
3
72
Äußere Joins
§ SQLite unterstützt (leider) nur LEFT OUTER JOIN
§ Oracle kennt (aus historischen Gründen) zusätzlich eine
alternative Syntax, um äußere Joins zu formulieren
1
2
3
SELECT *
FROM Professoren , Studenten
ON Professoren . Name = Studenten . Name (+)
Hier markiert (+) optionale Tabellen, d.h. obige Anfrage
drückt einen linken äußeren Join aus
Datenbanken / Kapitel 4: Structured Query Language (SQL)
73
4.5 Gruppierung & Aggregation
§ Aggregatfunktionen wurde bisher nur auf gesamte
Tabellen angewendet; oft möchte man diese
jedoch auf Gruppen von Tupeln anwenden
§ Beispiele:
§ Durchschnittliche Note je Vorlesung
§ Semesterwochenstunden je Professor
§ Anzahl der Studenten pro Fach
Datenbanken / Kapitel 4: Structured Query Language (SQL)
74
Gruppierung
§ Aggregatfunktionen lassen sich mittels des GROUP BY
Kommandos auf Gruppen von Tupeln anwenden; dieses
hat folgende allgemeine Form
1
2
3
4
5
6
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
< Projektionsattribute > , < Aggregate >
< Tabellen >
< Bedingungen auf Attributen >
< Gruppierattribute >
< Bedingungen auf Aggregaten >
< Sortierattribute > , < Aggregate >
Datenbanken / Kapitel 4: Structured Query Language (SQL)
75
Gruppierung
§ Tupel mit den gleichen Werten für die angegebenen
Gruppierattribute bilden eine Gruppe, für die ein
Wert der Aggregate berechnet wird
§ Beispiel: Fächer absteigend sortiert nach Anzahl Studenten
1
2
3
4
SELECT
FROM
GROUP BY
ORDER BY
Fach , Count (*) AS Anzahl
Studenten
Fach
Anzahl DESC
Datenbanken / Kapitel 4: Structured Query Language (SQL)
76
Gruppierung
§ Beispiel: Anzahl Professoren eines Namens je Fach
1
2
3
SELECT Fach , Name , Count (*)
FROM Professoren
GROUP BY Fach , Name
§ Projektionsattribute sind in der Regel identisch mit den
Gruppierattributen; sie müssen eine Teilmenge sein
§ Sortierattribute müssen eine Teilmenge der
Gruppierattribute sein
Datenbanken / Kapitel 4: Structured Query Language (SQL)
77
Gruppierung mit Selektion auf Attributen
§ Soll die Gruppierung nur solche Tupel berücksichtigen,
deren Attribute eine bestimmte Bedingung erfüllen,
so kann dies mittels einer WHERE-Klausel
erreicht werden
§ Beispiel: Anzahl Professoren namens Meier je Fach
1
2
3
4
SELECT
FROM
WHERE
GROUP BY
Fach , Count (*) AS Anzahl
Professoren
Name = ’ Meier ’
Fach
Datenbanken / Kapitel 4: Structured Query Language (SQL)
78
Gruppierung mit Selektion auf Aggregaten
§ Soll die Gruppierung nur solche Tupel zurückliefern,
deren Aggregate eine bestimmte Bedingung erfüllen,
so kann dies mittels einer HAVING-Klausel
erreicht werden
§ Beispiel: Fächer mit mehr als 10 Professoren
1
2
3
4
SELECT
FROM
GROUP BY
HAVING
Fach , Count (*) AS Anzahl
Professoren
Fach
Anzahl > 10
Datenbanken / Kapitel 4: Structured Query Language (SQL)
79
Gruppierung mit Selektion
§ SQL-Anfrage kann sowohl eine WHERE-Klausel als auch
eine HAVING-Klausel besitzen und es kann
mehrere Aggregate geben
§ Beispiel: Anzahl Professoren in Fächern, die auf
„ik“ enden und mehr als 10 Professoren haben,
alphabetisch sortiert
1
2
3
4
5
6
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
Datenbanken / Kapitel 4: Structured Query Language (SQL)
Fach , COUNT (*)
Professoren
Fach LIKE ’% ik ’
Fach
COUNT (*) > 5
Fach
80
Verarbeitung der Gruppierung
§ WHERE
(d.h. betrachte nur Professoren, deren Fach auf „ik“ endet)
§ GROUP BY
(d.h. gruppiere verbleibende Professoren nach Fach)
§ HAVING
(d.h. eliminiere Fächer mit weniger als 5 Professoren)
1
§ ORDER BY
(d.h. sortiere verbleibende Fächer)
2
3
4
5
6
Datenbanken / Kapitel 4: Structured Query Language (SQL)
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
Fach , COUNT (*)
Professoren
Fach LIKE ’% ik ’
Fach
COUNT (*) > 5
Fach
81
Top-k Anfragen
§ Bei Bildung von Aggregaten sind wir häufig nur an einer
bestimmten Anzahl von Tupeln mit höchstem oder
niedrigstem Aggregatwert interessiert; dies lässt sich
durch Verwendung des TOP (MS SQL Server) oder
LIMIT Kommandos (SQLite) erzielen
§ Beispiel: 5 Fächer mit den meisten Professoren
MS SQL Server:
1
2
3
4
SELECT
FROM
GROUP BY
ORDER BY
TOP 5 Fach , COUNT (*) AS Anzahl
Professoren
Fach
Anzahl DESC
Datenbanken / Kapitel 4: Structured Query Language (SQL)
82
Top-k Anfragen
§ Beispiel: 5 Fächer mit den meisten Professoren
SQLite:
1
2
3
4
5
SELECT
FROM
GROUP BY
ORDER BY
LIMIT
Fach , COUNT (*) AS Anzahl
Professoren
Fach
Anzahl DESC
5
Datenbanken / Kapitel 4: Structured Query Language (SQL)
83
4.6 Quantifizierung
§ SQL unterstützt mittels der Kommandos EXISTS und
NOT EXISTS den Existenzquantor
und
den Nichtexistenzquantor
§ Beispiel: Professoren zu denen es Studenten
mit gleichem Vornamen gibt
1
2
3
4
SELECT *
FROM Professoren p
WHERE EXISTS
( SELECT * FROM Studenten s WHERE p . Vorname = s . Vorname )
Datenbanken / Kapitel 4: Structured Query Language (SQL)
84
Quantifizierung
§ Allquantor wird nicht explizit unterstützt; kann aber
mittels des Nichtexistenzquantors ausgedrückt werden
§ Beispiel: Studenten, die alle Vorlesungen hören
(d.h. es gibt kein Vorlesung, die der Student nicht hört)
1
2
3
4
5
6
7
8
9
10
SELECT *
FROM Studenten s
WHERE NOT EXISTS
( SELECT *
FROM Vorlesungen v
WHERE NOT EXISTS
( SELECT *
FROM h ö ren
WHERE s . MatrNr = h . MatrNr
AND h . VorlNr = v . VorlNr ))
Datenbanken / Kapitel 4: Structured Query Language (SQL)
85
Anfrageübung Versandhandel
§ Anzahl der Kunden pro Wohnort
§ Minimaler und maximaler Preis je Artikelkategorie
§ Top-3 Kunden aus PLZ-Gebiet 661XX nach Umsatz
§ Bausätze mit mehr als 100€ Umsatz
§ Artikel, die noch nie bestellt wurden
Datenbanken / Kapitel 4: Structured Query Language (SQL)
86
Anfrageübung Versandhandel
§ Anzahl der Kunden pro Wohnort
1
2
3
SELECT Wohnort , COUNT (*) AS Anzahl
FROM Kunden
GROUP BY Wohnort
§ Minimaler und maximaler Preis je Artikelkategorie
1
2
3
SELECT Kategorie , MIN ( Preis ) , MAX ( Preis )
FROM Artikel
GROUP BY Kategorie
Datenbanken / Kapitel 4: Structured Query Language (SQL)
87
Anfrageübung Versandhandel
§ Top-3 Kunden aus PLZ-Gebiet 661XX nach Umsatz
1
2
3
4
5
6
SELECT
FROM
WHERE
AND
GROUP BY
ORDER BY
Kunden . Kunden_Nr , SUM ( Bestellungen . Betrag ) AS Umsatz
Kunden , Bestellungen
Kunden . Kunden_Nr = Bestellungen . Kunden_Nr
Kunden . PLZ > 66099 AND Kunden . PLZ < 66200
Kunden . Kunden_Nr
Umsatz DESC LIMIT 10
§ Bausätze mit mehr als 100€ Umsatz
1
2
3
4
5
6
SELECT
FROM
WHERE
AND
GROUP BY
HAVING
a . Artikel_Nr , SUM ( b . Anzahl * a . Preis ) AS Umsatz
Artikel a , Bestellpositionen b
a . Artikel_Nr = b . Artikel_Nr
a . Kategorie = ’B ’
a . Artikel_Nr
Umsatz > 100
Datenbanken / Kapitel 4: Structured Query Language (SQL)
88
Anfrageübung Versandhandel
§ Artikel, die noch nie bestellt wurden
1
2
3
4
5
6
SELECT *
FROM Artikel a
WHERE NOT EXISTS
( SELECT *
FROM Bestellpositionen b
WHERE a . Artikel_Nr = b . Artikel_Nr )
oder mit Wertemengen (IN)
1
2
3
4
5
SELECT *
FROM Artikel
WHERE Artikel_Nr NOT IN
( SELECT Artikel_Nr
FROM Bestellpo sition en )
Datenbanken / Kapitel 4: Structured Query Language (SQL)
89
Zusammenfassung
§ Äußere Joins
§ (LEFT|FULL|RIGHT) OUTER JOIN
§ Gruppierung und Aggregation
§ Aggregatfunktionen (SUM, COUNT, MAX, MIN, etc.)
§ WHERE zur Selektion auf Attributen
§ GROUP BY zur Angabe der Gruppierattribute
§ HAVING zur Selektion auf Aggregaten
§ Quantifizierung
§ EXISTS und NOT EXISTS
Datenbanken / Kapitel 4: Structured Query Language (SQL)
90
Literatur
[1]
A. Kemper und A. Eickler: Datenbanksysteme – Eine
Einführung, De Gruyter Oldenbourg, 2013 (Kapitel 4)
[2]
G. Saake, K.-U. Sattler und A. Heuer:
Datenbanken - Konzepte und Sprachen,
mitp Professional, 2013 (Kapitel 7 & 10)
Datenbanken / Kapitel 4: Structured Query Language (SQL)
91