SQL

Diese Seite ist für die Oberstufe (Klasse 11+12). Für SQL in der 9. Klasse hier klicken: SQL-09

Hier finden sich Erläuterungen zu allen SQL-Techniken, die für das Zentralabitur wichtig sind.

Zu diesen Techniken gibt es auch Aufgaben, die man zur Übung selber bearbeiten kann.

Ein großer Teil der SQL-Abfragen realisiert die sog. Relationenalgebra, welche hier im Detail erklärt wird.

Um einen verständlichen Aufbau dieser Seite hinzukriegen, wird die Relationenalgebra in zwei Teile geteilt:

• Relationenalgebra 1
• Relationenalgebra 2

Dazwischengeschoben werden die Aggregatfunktionen, GROUP BY und mehrere Bedingungen (AND, OR, BETWEEN).

Grundlegende SQL-Befehle

• Informationen des Zentralabiturs (PDF): Datei:Datenbanken-Abi-2012.pdf
• SQL-Sprachelemente im Zentralabitur:
  • SELECT, SELECT DISTINCT, FROM, WHERE, GROUP BY, ORDER BY, ASC, DESC, JOIN, LEFT JOIN, RIGHT JOIN, ON, UNION, AS
  • Vergleichsoperatoren: =, !=, >, <, >=, <=, LIKE, BETWEEN, IN, NOT IN, IS NULL
  • Logische Operatoren: AND, OR, NOT
  • Funktionen: COUNT, SUM, MAX, MIN

Beispieldatenbank Schule

• An der Beispieldatenbank Schule können alle möglichen Datenbankabfragen durchprobiert werden.
• Die Beispieldatenbank ist mit Absicht sehr schmal gehalten, damit man die Ergebnisse von SQL-Abfragen noch überprüfen kann.
• Testen kann man SQL-Abfragen auf der Datenbank Schule hier. Die Zugangsdaten gibt's bei Herrn Kaibel

Aufbau von SQL-Abfragen

Für den Aufbau von SQL-Abfragen gilt folgende Reihenfolge:

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
ORDER BY ...
LIMIT ...

SELECT und FROM müssen auf jeden Fall dabei sein, die anderen Teile sind optional.

Relationenalgebra 1: Abfragen über eine Tabelle

Begriffsklärung: Relation: (hier:) Tabelle

Bei Datenbanken versteht man unter einer Relationenalgebra eine formale Sprache, mit der sich Abfragen über eine oder mehrere Tabellen (=Relationen) formulieren lassen. Sie erlaubt es, Tabellen miteinander zu verknüpfen, zu reduzieren oder komplexere Informationen daraus herzuleiten.

Selektion

Selektion bedeutet, dass Datensätze (=Zeilen) ausgewählt werden, die eine bestimmte Bedingung erfüllen.

Formuliere eine Selektionsabfrage, die alle Nummern der Räume mit mindestens 30 Plätzen zurückgibt.

 SELECT r.nummer
 FROM raum r
 WHERE r.plaetze >= 30

Beachte: Das entscheidende Schlüsselwort für die Selektion ist WHERE, denn hier wird die Bedingung festgelegt.

Formuliere eine Selektionsabfrage, die alle Informationen zu Räumen mit mindestens 30 Plätzen zurückgibt.

 SELECT *
 FROM raum r
 WHERE r.plaetze >= 30

Beachte: Mit dem Joker * im SELECT-Teil der Abfrage werden alle Attribute ausgewählt.

Projektion

Unter Projektion versteht man die Reduktion der Informationen in dem Ergebnis auf ein oder mehrere Attribute (=Spalten). Doppelte Zeilen werden dabei entfernt.

Formuliere für die Tabelle raum eine Projektionsabfrage, die die Etagen mit Projektion wiedergibt.

 SELECT DISTINCT r.etage
 FROM raum r

Beachte: Entscheidend für die Projektion ist es, dass man bei SELECT einzelne Attribute angibt (anstelle von *).

Mithilfe des Schlüsselwortes DISTINCT erreicht man, dass die doppelten Zeilen entfernt werden.

Aggregat-Funktionen (COUNT, MAX, MIN, SUM, AVG)

Mit den sog. Aggregat-Funktionen kann man Attributwerte aus mehreren Zeilen zusammenfassen.

Beispiel 1:

Mit der folgenden Abfrage kann man ermitteln, wie viele Schüler es gibt.

  SELECT COUNT(*)
  FROM schueler s

Hier werden die Zeilen gezählt. Mit COUNT(*) wird nicht ein Attribut gezählt, sondern jede Zeile, die überhaupt irgendein Attribut enthält.

Beispiel 2:

Mit der folgenden Abfrage kann man ermitteln, wie viele Stunden Sport unterrichtet werden.

  SELECT SUM(u.stunden)
  FROM unterricht u
  WHERE u.fach = 'Sport'

Hier werden die Attributwerte stunden summiert (d.h. zusammenaddiert).

GROUP BY

Mit GROUP BY kann man Zeilen, die einen gemeinsamen Attributwert aufweisen, zusammenfassen.

Beispiel:

Mit der folgenden Abfrage erhält man eine Liste der Etagen, und für jede Etage wird notiert, wie viele Plätze in ihr insgesamt enthalten sind.

  SELECT r.etage, SUM(plaetze)
  FROM raum r
  GROUP BY r.etage

Umbenennung

Attribute können umbenannt werden.

Beispiel 1:

Formuliere eine Abfrage über die Attribute klasse_id, lehrer_id, raum_id, fach und stunden der Tabelle unterricht. Dabei soll klasse_id in klassen-nr, lehrer_id in lehrer_nr und raum_id in raum-nr umbenannt werden.

SELECT u.klassen_id AS klassen-nr,u.lehrer_id AS Lehrer-nr,u.raum_id AS Raum-nr,u.fach,u.stunden
FROM unterricht u

Beispiel 2:

Formuliere eine Abfrage, die eine Liste der Etagen zurückgibt. Für jede Etage soll angegeben werden, wie viele Plätze es in der Etage gibt. Die Liste soll nach der Anzahl der Plätze sortiert sein.

  SELECT r.etage, SUM(plaetze) AS PlaetzeInEtage
  FROM raum r
  GROUP BY r.etage
  ORDER BY PlaetzeInEtage

Beachte: Für SUM(plaetze) wird ein Alias (PlaetzeInEtage) vergeben. Mithilfe des Alias kann man am Ende nach der Anzahl der Plätze sortieren!

Mehrere Bedingungen: AND, OR, BETWEEN

In SQL-Abfragen kann man mehrere Bedingungen kombinieren. Dafür gibt es die Schlüsselwörter AND, OR und BETWEEN

AND / OR

Mit AND / OR kann man zwei Bedingungen verknüpfen.

Beispiel:

  SELECT r.nummer, r.plaetze
  FROM raum r
  WHERE r.etage = 'unten' OR r.etage = 'Mitte'

BETWEEN

Mit BETWEEN ... AND kann man Intervalle angeben.

Beispiel:

  SELECT r.nummer, r.plaetze
  FROM raum r
  WHERE r.plaetze BETWEEN 30 AND 50

LIMIT

Mit LIMIT kann man angeben, wie viele Zeilen ausgegeben werden.

Beispiel: Die folgende SQL-Anweisung gibt den Raum (Nummer, Platzanzahl) mit den meisten Plätzen aus.

Es wird eine Liste von allen Räumen erstellt, aber nur die erste Zeile der Liste (d.h. der Raum mit den meisten Plätzen) wird ausgegeben.

  SELECT r.nummer, r.plaetze
  FROM raum r
  ORDER BY r.plaetze DESC
  LIMIT 1

Relationenalgebra 2: Abfrage über mehrere Tabellen

Vereinigung

Vereinigung bedeutet, dass die Ergebnis-Datensätze von zwei Abfragen zu einem Ergebnis zusammengefügt werden. Voraussetzung dafür ist, dass die Attribute gleich sind.

Formuliere eine Abfrage, die Name und Vorname von allen Lehrern UND Schülern wiedergibt.

SELECT l.name, l.vorname
FROM lehrer l
UNION
SELECT s.name, s.vorname
FROM schueler s

Differenz

Differenz bedeutet, dass die Ergebnis-Datensätze einer Abfrage a1 von den Ergebnis-Datensätzen einer Abfrage a0 abgezogen werden - es bleibt dann ein Rest übrig.

Formuliere eine Abfrage, die alle Klassen ausgibt, die KEIN Mathematik haben.

Idee: Die Schüler, die Mathematik haben, von allen Schülern abziehen.

SELECT k.name
FROM klasse k
WHERE k.id NOT IN(SELECT u.klasse_id 
                  FROM unterricht u 
                  WHERE u.fach='Mathe')

Kartesisches Produkt

In der Mathematik bezeichnet man als kartesisches Produkt (nach René Descartes) zweier Mengen A und B die Menge aller geordneten Paare (a,b), wobei a aus A und b aus B ist. (Kombination: „Jedes mit jedem“.) Geschrieben wird es als A x B gelesen als A kreuz B.

Kurz: Jeder mit jedem.

Formuliere eine Abfrage für das kartesische Produkt von den Tabellen klasse und schueler.

SELECT *
FROM klasse k, schueler s 

Diese Abfrage liefert als Ergebnis "Jeder mit jedem". Inhaltlich ist das nicht sinnvoll.

Damit das kartesische Produkt sinnvoll wird, braucht man einen Abgleich zwischen den Tabellen; vgl. SQL-Abfrage über mehrere verknüpfte Tabellen

Join

Join ist die Bildung eines kartesischen Produktes gefolgt von einer Selektion, die den (richtigen) Abgleich zwischen den beiden Tabellen leistet.

Formuliere eine Abfrage, die einen Join zwischen den Tabellen klasse und schueler durchführt. Bewerte das Ergebnis inhaltlich: In welchen Zusammenhängen braucht man Join?

SELECT s.name AS schuelername, k.name AS klassename
FROM schueler s JOIN klasse k
ON s.klasse_id = k.id

Mit einem JOIN werden nur VOLLSTÄNDIGE Datensätze angezeigt. D.h. im Beispiel fallen Schüler, die keine Klasse haben, und Klassen, die keine Schüler haben, raus.

Der Join entspricht der SQL-Abfrage über mehrere verknüpfte Tabellen

Left-Join

Beim Left Join werden auch die Zeilen der linken Tabelle aufgeführt, die keinen Partner in der rechten Tabelle haben. In der rechten Tabelle wird ggf. mit NULL aufgefüllt.

Formuliere eine Abfrage, die einen Left-Join zwischen den Tabellen klasse und schueler durchführt. Bewerte das Ergebnis inhaltlich: In welchen Zusammenhängen braucht man Left-Join?

 SELECT s.name AS schuelername, k.name AS klassename
 FROM schueler s LEFT JOIN klasse k
 ON s.klasse_id = k.id

Beim LEFT JOIN werden ALLE Daten der LINKEN Entitätsmenge angezeigt. Im Beispiel werden also auch die Schüler angezeigt, die keine Klasse haben.

Right-Join

Beim Right Join werden auch die Zeilen der rechten Tabelle aufgeführt, die keinen Partner in der linken Tabelle haben. In der linken Tabelle wird ggf. mit NULL aufgefüllt.

Formuliere eine Abfrage, die einen Right-Join zwischen den Tabellen klasse und schueler durchführt.

 SELECT s.name AS schuelername, k.name AS klassename
 FROM schueler s RIGHT JOIN klasse k
 ON s.klasse_id = k.id

Beim RIGHT JOIN werden ALLE Daten der RECHTEN Entitätsmenge angezeigt. Im Beispiel werden also auch die Klassen angezeigt, die keine Schüler haben.

SQL-Abfrage über mehrere verknüpfte Tabellen

Formuliere eine Abfrage, die angibt, in welchen Räumen die Klasse 8a unterrichtet wird. Die Abfrage läuft über die Tabellen klasse, unterricht und raum.

SELECT r.nummer
FROM klasse k, unterricht u, raum r  
WHERE r.id=u.raum_id
AND u.klasse_id=k.id
AND k.name = '8a'

Wichtig: bei Abfragen über mehrere Tabellen braucht man einen Abgleich weniger als man Tabellen abfragt.

Im Beispiel werden drei Tabellen abgefragt; deswegen braucht man zwei Abgleiche.

IS NULL

Formuliere eine Abfrage, die alle Schüler zurückgibt, die keine Klasse haben.

 SELECT s.name AS schuelername, k.name AS klassename
 FROM schueler s LEFT JOIN klasse k
 ON s.klasse_id = k.id
 WHERE k.id IS NULL

Beim LEFT JOIN werden ALLE Daten der LINKEN Entitätsmenge angezeigt, also auch die Schüler, die keine Klasse haben. Diese Schüler werden durch k.id IS NULL ausgewählt.

Abfragen, bei denen man mehrere Variablen einer Entitätsmenge braucht

Formuliere eine Abfrage, die alle Räume wiedergibt, die mehr Plätze haben als Raum R203.

Idee:

• Man hat den bekannten Raum r1 mit der Nummer "R203".
• Außerdem hat man den gesuchten Raum r2, der mehr Plätze hat als Raum r1.

 SELECT r2.nummer
 FROM raum r1, raum r2
 WHERE r1.nummer = "R203"
 AND r2.plaetze > r1.plaetze

Verschachtelte SQL-Abfragen

Verschachtelte SQL-Abfragen können nicht nur bei Abfragen mit IN oder NOT IN vorkommen.

So kann man z.B. auch die Tabellen, die man bei FROM angibt, durch eigene SELECT-Anfrage selber definieren.

Die selbstdefinierten Tabellen können wie "normale" Tabellen genutzt werden und müssen auch durch Komma voneinander getrennt werden.

Beispiel: Eine Liste der Klassen, in der steht, wieviel Prozent Sportunterricht sie haben.

SELECT gesamtstunden.klasse AS klasse, sportstunden.stunden/gesamtstunden.stunden * 100 AS prozent
FROM
  (SELECT k.name AS klasse, SUM(u.stunden) AS stunden
   FROM klasse k, unterricht u
   WHERE k.id = u.klasse_id
   GROUP BY k.name
  ) AS gesamtstunden
  ,
  (SELECT k.name AS klasse, SUM(u.stunden) AS stunden
   FROM klasse k, unterricht u
   WHERE k.id = u.klasse_id
   AND u.fach = 'Sport'
   GROUP BY k.name
  ) AS sportstunden
WHERE sportstunden.klasse = gesamtstunden.klasse

Erläuterung:

• In FROM werden zwei Tabellen definiert: Die Tabelle gesamtstunden und die Tabelle sportstunden.
• Diese Tabellen werden durch ein Komma getrennt.
• Außerdem erhalten sie mithilfe von AS Aliasnamen: AS gesamtstunden (bzw. AS sportstunden).
• Mithilfe dieser Aliasnamen kann die äußere Abfrage auf die beiden Tabellen zugreifen.
• Die äußere Abfrage gleicht die beiden Tabellen mithilfe von WHERE sportstunden.klasse = gesamtstunden.klasse ab.

WICHTIG: Man muss jeder inneren SELECT-Abfrage mithilfe von AS einen Namen geben - nur dann ist sie in der äußeren SELECT-Abfrage ansprechbar!