SQL-Abfragen

Relationale Algebra

Die Relationenalgebra oder relationale Algebra stellt Methoden zur Verfügung, mit denen sich Relationen manipulieren lassen. Sie ermöglicht es, Relationen zu filtern, zu verknüpfen, zu aggregieren oder anderweitig zu modifizieren, um Anfragen an eine Datenbank zu formulieren. Um Mengenoperationen auf den Relationen R und S durchführen zu können, müssen beide miteinander kompatibel sein. Diese Kompatibilität zweier Relationen ist gegeben, wenn  A und B die gleiche Anzahl von Attributen haben und der Wertebereich der Attribute von A und B identisch ist.

Mit Hilfe der beiden folgenden Tabellen sollen die Operationen Durchschnitt, Vereinigung und Differenz erklärt werden:

Die Vereinigung A ∪ B zweier Relationen A und B ist die Menge aller Tupel, die in A oder B oder in beiden Relationen enthalten sind.

Definition: A ∪ B := {t|t∈A ∨ t∈B}

Die erzeugte Relation ist

Der Durchschnitt A ∩ B zweier Relationen A und B ist die Menge aller Tupel, die sowohl in A als auch in B enthalten sind.

Definition: A ∩ B := {t|t∈A ∧ t∈B}

Die erzeugte Relation ist

Die Differenz A \ B zweier Relationen A und B ist die Menge aller Tupel, die in A aber nicht gleichzeitig in B enthalten sind.

Definition: A \ B := {t|t∈A ∧ t∉B}

Die erzeugte Relation ist

Zwei weitere relationale Operationen sind die Projektion und die Selektion. Die Projektion entspricht der Projektionsabbildung aus der Mengenlehre und kann auch Attributbeschränkung genannt werden. Sie extrahiert einzelne Attribute aus der ursprünglichen Attributmenge und ist somit als eine Art Selektion auf Spaltenebene zu verstehen, das heißt, die Projektion blendet Spalten aus. Wenn β die Attributliste ist, schreibt man πβ(R). β heißt auch Projektionsliste. Duplikate in der Ergebnisrelation werden eliminiert.

Definition:  A sei eine Relation über {A1, …, Ak} und β ⊆ {A1, …, Ak}.
π _β ( A ) := { t _β | t ∈ A }

Die Selektion liefert die Teilmenge aller Tupel einer Relation A, die eine Formel F erfüllen.

Definition: σ_Ausdruck(A) := {t|t ∈ A ∧ t erfüllt Ausdruck)

Kartesisches Produkt

Das kartesische Produkt A x B ist die Menge aller (n1 * n2)-Tupel, die durch Kombination der Tupel aus A mit den Tupeln aus B entstehen.

Natural-Join

Als erstes wird das kartesische Produkt gebildet. Dann erfolgt die Selektion mit der Bedingung, dass der Inhalt bestimmter Spalten identisch sein muss. Anschließend wird die duplizierte Spalte gelöscht.

Übungsbeispiele:

1. Die folgenden relationalen Operationen sollen durchgeführt und die Ergebnistabellen erstellt werden. Ausgangsrelationen sind die folgenden drei Tabellen:

a) Selektion von Tabelle 1 mit der Bedingung B=2

b) Projektion von Tabelle 3 auf E.

c) Join Tabelle 1 und Tabelle 2 nach dem gemeinsamen Attribut C

d) Selektion von Tabelle 1 mit B>C vereinigt mit Selektion von Tabelle 1 mit A>5

Lösung:

a) σ_B=2(Tabelle1)    b) π_E(Tabelle3)    c) Tabelle1 ⋈_C=C Tabelle2    d) σ_B>C(Tabelle1) ∪ σ_A<5(Tabelle1)

2. Gegeben sind folgende Relationen (# ist das Zeichen für Nummer):
LIEFERANTEN (L#, LName, Status, Stadt1)
TEILE (T#, TName, Farbe, Gewicht, Stadt2)
PROJEKTE (P#, PName, Stadt3)
LIEFERUNGEN (L#, T#, P#, Anzahl)
a) Gesucht werden alle Lieferungen mit Anzahlen zwischen 300 und 750.
b) Gesucht werden alle Städte, in denen Lieferanten sitzen.
c) Gesucht werden alle vorkommenden Paarungen TName und  Stadt2 aus.
d) Gesucht werden alle schwarzen Teile. Die Nummern und Namen sollen ausgegeben werden.
e) Gesucht werden alle Lieferanten, die in einer Einzellieferung mehr als 150 Teile geliefert haben.

Lösung:

a) σ_{Anzahl>=300 und Anzahl<=750}(Lieferungen)

b) π_Stadt1(Lieferanten)

c) π_TName,Stadt2(Teile)

d) π_T#,Stadt2(σ _{Farbe=schwarz}(Teile))

e) π_Lname(π _L#(σ_Anzahl>150(Lieferungen))⋈π_L#,Lname(Lieferanten))

3. Die folgenden drei Relationen sind gegeben:

a) Das ER-Diagramm soll erstellt werden.

b) Welche Tabelle liefert π_KursID(σ_{Fach=Informatik}(Kurs))? Diese neue Tabelle heißt Informatikkurse.

c) Die Tabelle Informatikschüler wird durch den Join Informatikkurse ⋈ Besucht erzeugt. Wie sieht diese aus?

d) Es sollen die notwendigen Relationen-Operatoren bestimmt und begründet werden, um von der Tabelle Informatikschüler auf die Tabelle InformatikschülerPunkteNamen zu gelangen.

e) Es soll der Relationen-Operator angegeben werden, um von der Tabelle InformatikschülerPunkteNamen auf eine Tabelle zu gelangen, welche nur die Namen und Punkte der männlichen Informatikschüler enthält, die mindestens eine "zwei" (10-15Punkte) in Informatik haben.

Lösungen:

π_{SchülerId,Punkte}(Informatikschüler ⋈ Schüler) 

π_{Punkte,Name,Vorname}(σ_{Punkte>=12 ∧ Geschlecht="m"}(InformatikschülerPunkteNamen))

SQL-Befehle

Die Syntax eines Standard-Select-Befehls in BNF (Backus-Naur-Form) sieht wie folgt aus:

SELECT [ALL|DISTINCT]{spalten|*}
FROM Tabelle [alias] [Tabelle[alias]]...
[WHERE {bedingung|unterabfrage}]
[GROUP BY spalten[HAVING {bedingung|unterabfrage}]]
[ORDER BY spalten[ASC|DESC]...];

Für die folgenden Beispiele werden die drei Relationen Student (MatrNr, Name), Vorlesung (VorlNr, Titel, PersNr), Professor (PersNr, Name) und die beiden Beziehungsrelationen hört (MatrNr, VorlNr) und liest (PersNr, VorlNr) verwendet.

SELECT *
from Student;

listet alle Spaten und Zeilen aus der Tabelle Student auf.

SELECT *
FROM Student
WHERE (MatrNr LIKE "156-*") OR (Name LIKE "Ab*");

listet alle Studenten der Tabelle Student auf, deren MatrNr mit "156-" beginnt oder deren Namen mit "Ab" beginnt.

SELECT MatrNr
FROM Student;

listet die Spalte MatrNr der Tabelle Student auf.

SELECT DISTINCT MatrNr
FROM hört;

listet nur unterschiedliche Einträge der Spalte MatrNr aus der Tabelle hört auf. Dies zeigt die Matrikelnummern aller Studenten, die mindestens eine Vorlesung hören, wobei mehrfach auftretende Matrikelnummern nur einmal ausgegeben werden.

SELECT Name, MatrNr AS Matrikelnummer
FROM Student;

Die Spalte MatrNr heißt in der Ergebnisrelation jetzt Matrikelnummer.

SELECT Titel, VorlNr
FROM Vorlesung
WHERE PersNr = 12;

listet alle Vorlesungen des Professors mit der Personalnummer 12 auf.

SELECT Titel, VorlNr FROM Vorlesung ORDER BY PersNr;

listet alle Vorlesungs-Titel sortiert nach unterrichtenden Professoren auf.

SELECT Vorlesung.VorlNr, Vorlesung.Titel, Professor.PersNr, Professor.Name
FROM Professor, Vorlesung
WHERE Professor.PersNr = Vorlesung.PersNr;

Dies ist äquivalent zu:
SELECT Vorlesung.VorlNr, Vorlesung.Titel, Professor.PersNr, Professor.Name
FROM Professor INNER JOIN Vorlesung
ON Professor.PersNr = Vorlesung.PersNr;

listet die Werte der Spalten VorlNr und Titel aus der Tabelle Vorlesung sowie der Spalten Name und Persnr aus der Tabelle Professor für alle Vorlesungen auf.

SELECT Vorlesung.VorlNr, Vorlesung.Titel, Professor.Name, Professor.PersNr FROM Professor
LEFT OUTER JOIN Vorlesung ON Professor.PersNr = Vorlesung.PersNr;

Äußerer linker natürlicher Verbund: Listet die Werte der Spalten VorlNr und Titel aus der Tabelle Vorlesung sowie der Spalten Name und PersNr aus der Tabelle Professor für alle Vorlesungen auf. Professoren die keine Vorlesungen halten werden auch mit aufgelistet.

SELECT Professor.Name, Professor.PersNr
FROM Professor LEFT OUTER JOIN Vorlesung ON Professor.PersNr = Vorlesung.PersNr
WHERE Vorlesung.PersNr IS NULL

Äußerer linker natürlicher Verbund, Selektion und Projektion: Listet alle Professoren auf, die keine Vorlesungen halten. Die Abfrage ist identisch mit der folgenden (Unterabfrage mit Existenz-Quantor):

SELECT Professor.Name, Professor.PersNr
FROM Professor
WHERE NOT EXISTS (SELECT * FROM Vorlesung WHERE PersNr = Profeesor.PersNr);

SELECT COUNT(Vorlesung.PersNr) AS Anzahl, Professor.PersNr, Professor.Name
FROM Professor LEFT OUTER JOIN Vorlesung
ON Professor.PersNr = Vorlesung.PersNr
GROUP BY Professor.Name, Professor.PersNr;

Gruppierung, Aggregation und äußerer linker natürlicher Verbund: Zählt die Anzahl der Vorlesungen pro Professor.
Bemerkung: COUNT(Professor.PersNr) oder COUNT(*) wären falsch (Nullwerte sollen nicht mitgezählt werden).

Für das nächste Beispiel werden die Relationen Landgang (LandgangID,Dauer, OrtID, Bezeichnung), Ort (OrtID, Ortsname, Staat) und Sehenswürdigkeit (SehenswürdikeitID, OrtID, Beschreibung, Dauer) genutzt.

Gesucht sind die Staaten der Orte, an denen ein Landgang der Kreuzfahrtroute mit der ID 'A' stattfindet. Jeder Staat soll nur einmal aufgeführt werden.

SELECT DISTINCT Staat
FROM Ort JOIN Landgang
ON Landgang.OrtID = Ort.OrtID
WHERE Landgang.RouteID = 'A';

Gesucht sind für alle Orte der Ortsname und die folgende Informationen: die Anzahl der Sehenswürdigkeiten an dem Ort und die Gesamtdauer, die für den Ort benötigt wird, um alle Sehenswürdigkeiten zu besichtigen.

SELECT Ortsname, COUNT(SehenswuerdigkeitID) AS Anzahl, SUM(Dauer) AS Gesamtdauer
FROM Ort JOIN Sehenswuerdigkeit ON Ort.OrtID = Sehenswuerdigkeit.OrtID
GROUP BY Ort.OrtID

Gesucht sind die Ortsnamen, an denen keine Landgänge verzeichnet sind.

SELECT Ortsname
FROM Ort
WHERE OrtID NOT IN (SELECT OrtID FROM Landgang);

Zu welchen Ergebnissen führen die folgenden Anfragen:
SELECT Bezeichnung, Dauer
FROM Landgang
WHERE RouteID = 'A'
ORDER BY Nummer

Gesucht sind die Bezeichnung und Dauer der Landgänge, deren Route die ID 'A' hat. Die Daten sind nach der Nummer des Landgangs sortiert. Es handelt sich also um eine Auflistung der geplanten Landgänge der Route A in der Reihenfolge, in der sie stattfinden sollen.

SELECT RouteID, COUNT(*) AS Anzahl
FROM Landgang
GROUP BY RouteID

Gesucht werden die RouteID der Relation Landgang, ergänzt um eine mit "Anzahl" bezeichnete Spalte. Die Datensätze werden nach dem Attribut RouteID gruppiert und in der Ergebnisspalte Anzahl wird die Anzahl der Datensätze in der jeweiligen Gruppe ermittelt. Es handelt sich also um eine Auflistung der Routen und die jeweilige Anzahl der Landgänge.

SELECT Beschreibung, Ortsname
FROM Sehenswuerdigkeit
JOIN Ort ON Sehenswuerdigkeit.OrtID = Ort.OrtID
JOIN Landgang ON Landgang.OrtID = Ort.OrtID
WHERE RouteID = 'A';

Gesucht ist eine Auflistung aller Orte mit Sehenswürdigkeiten der Route A.