Zurück zur StartseiteInformatik · Q1 Grundkurs · Fach wechseln
Kostenlos starten
Q1 Grundkurs · Informatik
Datenbanken, Datenstrukturen und Automaten in der Qualifikationsphase · Datenbanken und SQL

SQL-Abfragen über mehrere Tabellen

Ein Sportverein in Münster verwaltet seine Mitglieder, Kurse und Anmeldungen in einer Datenbank. Die Vorsitzende fragt: „Welche Mitglieder besuchen den Schwimmkurs, und wie viele Teilnehmer hat jeder Kurs im Durchschnitt?“ Die Antwort steht nicht in einer einzigen Tabelle. Sie ergibt sich erst, wenn Sie Tabellen über Schlüssel verknüpfen. Genau das lernen Sie hier: Abfragen formulieren, die Daten aus mehreren Tabellen holen, und ihre Bedeutung (Semantik) erläutern.

Grundform: SELECT – FROM – WHERE – ORDER BY

Eine Datenbankabfrage (Query) beantwortet eine Frage an die Daten. Im Datenbanksystem (DBS) stellt der Client die Abfrage, der Server wertet sie aus und liefert eine Ergebnistabelle zurück. Die Abfragesprache heißt SQL. Ihre Grundform hat vier Teile:

Bedingungen verwenden die Vergleiche =, <>, <, >, <=, >= sowie AND, OR, NOT. Zeichenketten stehen in einfachen Hochkommas: 'Kehlmann'. Mit LIKE 'Ke%' suchen Sie nach Mustern (% steht für beliebig viele Zeichen). SELECT DISTINCT unterdrückt doppelte Ergebniszeilen.

Beispieltabelle Buch(ISBN, Titel, Autor, Jahr, Preis):

ISBN Titel Autor Jahr Preis
101 Die Vermessung der Welt Kehlmann 2005 12,00
102 Tyll Kehlmann 2017 24,00
103 Ruhm Kehlmann 2009 10,00
104 Tschick Herrndorf 2010 9,95
Üben★☆☆
Gegeben ist die Tabelle Buch(ISBN, Titel, Autor, Jahr, Preis). a) Schreiben Sie eine SQL-Abfrage, die alle Titel mit Erscheinungsjahr nach 2010 ausgibt. b) Schreiben Sie…

Gegeben ist die Tabelle Buch(ISBN, Titel, Autor, Jahr, Preis).

  1. a) Schreiben Sie eine SQL-Abfrage, die alle Titel mit Erscheinungsjahr nach 2010 ausgibt.
  1. b) Schreiben Sie eine Abfrage, die alle Bücher von „Kehlmann“ absteigend nach Preis sortiert ausgibt.
Lösen

Aggregatfunktionen, GROUP BY und HAVING

Oft interessiert nicht jeder einzelne Datensatz, sondern eine Kennzahl: Anzahl, Summe, Durchschnitt. Dafür gibt es Aggregatfunktionen:

Funktion Ergebnis
COUNT(*) Anzahl der Datensätze
SUM(a) Summe der Werte von a
AVG(a) arithmetisches Mittel
MIN(a), MAX(a) kleinster bzw. größter Wert

SELECT COUNT(*) FROM Kunde WHERE Ort = 'Bremen'; liefert eine einzige Zahl.

Soll die Kennzahl je Gruppe berechnet werden (je Ort, je Kurs), gruppieren Sie mit GROUP BY. Alle Datensätze mit gleichem Wert im Gruppierungsattribut bilden eine Gruppe, und die Aggregatfunktion wird für jede Gruppe einzeln berechnet:

SELECT Ort, COUNT(*) FROM Kunde GROUP BY Ort;

Mit HAVING filtern Sie anschließend ganze Gruppen, zum Beispiel nur Orte mit mehr als drei Kunden: ... GROUP BY Ort HAVING COUNT(*) > 3;

Die Reihenfolge, in der das DBS eine Abfrage auswertet, unterscheidet sich von der Schreibreihenfolge:

Auswertungsreihenfolge einer SQL-Abfrage: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY FROM WHERE GROUP BY HAVING SELECT ORDER BY 123456
Üben★☆☆
Gegeben ist Kunde(KNr, Name, Ort). a) Geben Sie eine Abfrage an, die die Anzahl der Kunden aus „Bremen“ bestimmt. b) Geben Sie eine Abfrage an, die jeden Ort mit der…

Gegeben ist Kunde(KNr, Name, Ort).

  1. a) Geben Sie eine Abfrage an, die die Anzahl der Kunden aus „Bremen“ bestimmt.
  1. b) Geben Sie eine Abfrage an, die jeden Ort mit der Anzahl seiner Kunden ausgibt.
Lösen
Üben★★☆
Gegeben ist die Tabelle Film(FNr, Titel, Jahr) mit den Zeilen (1, 'Das Boot', 1981), (2, 'Lola rennt', 1998), (3, 'Good Bye, Lenin!', 2003), und Vorstellung(VNr, FNr,…

Gegeben ist die Tabelle Film(FNr, Titel, Jahr) mit den Zeilen (1, 'Das Boot', 1981), (2, 'Lola rennt', 1998), (3, 'Good Bye, Lenin!', 2003), und Vorstellung(VNr, FNr, Kino, Besucher).

  1. a) Geben Sie das Ergebnis von SELECT Titel FROM Film WHERE Jahr > 1990 ORDER BY Titel; an.
  1. b) Schreiben Sie eine Abfrage, die für jedes Kino die Summe der Besucher für Filme vor 2000 ausgibt.
  1. c) Erläutern Sie den Unterschied zwischen WHERE und HAVING.
Lösen

Verbund (JOIN): Tabellen verknüpfen

Ein sauberes Datenbankschema verteilt die Daten auf mehrere Tabellen, um Redundanz zu vermeiden. Im Verein gibt es Mitglied(MNr, Name, ANr) und Abteilung(ANr, Bezeichnung). Dabei ist Mitglied.ANr ein Fremdschlüssel, der auf den Primärschlüssel Abteilung.ANr verweist. Um Name und Abteilungsbezeichnung gemeinsam auszugeben, müssen die Tabellen über diesen Schlüssel verbunden werden.

Verbund der Tabellen Mitglied und Abteilung über das Attribut ANr Mitglied MNrNameANr 1Lea2 2Can1 3Mia2 Abteilung ANrBezeichnung 1Fußball 2Schwimmen Verbinde Zeilen mit gleichem ANr-Wert

SELECT Mitglied.Name, Abteilung.Bezeichnung FROM Mitglied JOIN Abteilung ON Mitglied.ANr = Abteilung.ANr;

Das Ergebnis für obige Daten: (Lea, Schwimmen), (Can, Fußball), (Mia, Schwimmen). Gleichwertig ist die ältere Schreibweise FROM Mitglied, Abteilung WHERE Mitglied.ANr = Abteilung.ANr. Haben zwei Tabellen gleich benannte Attribute, schreiben Sie sie qualifiziert (Tabelle.Attribut) oder verwenden einen Alias: FROM Mitglied m JOIN Abteilung a ON m.ANr = a.ANr.

Über drei Tabellen verknüpfen Sie, indem Sie einen weiteren JOIN anhängen.

Kunden ohne Datensätze: LEFT JOIN

Der normale Verbund (INNER JOIN) liefert nur Zeilen, für die es einen Partner gibt. Ein Mitglied ohne Anmeldung fehlt im Ergebnis. Der LEFT JOIN behält alle Zeilen der linken Tabelle. Fehlt ein Partner, steht in den Spalten der rechten Tabelle NULL.

SELECT m.Name, a.KNr FROM Mitglied m LEFT JOIN Anmeldung a ON m.MNr = a.MNr;

Wichtig bei Aggregation: COUNT(a.KNr) zählt nur Werte ungleich NULL, also 0 für Mitglieder ohne Anmeldung. COUNT(*) würde dagegen 1 liefern, weil die Zeile selbst existiert.

Üben★☆☆
Gegeben sind Autor(ANr, Name) und Buch(ISBN, Titel, ANr) mit Buch.ANr als Fremdschlüssel. a) Erklären Sie, was ein Verbund (JOIN) bewirkt. b) Schreiben Sie eine Abfrage,…

Gegeben sind Autor(ANr, Name) und Buch(ISBN, Titel, ANr) mit Buch.ANr als Fremdschlüssel.

  1. a) Erklären Sie, was ein Verbund (JOIN) bewirkt.
  1. b) Schreiben Sie eine Abfrage, die zu jedem Buch Titel und Autorenname ausgibt.
Lösen
Üben★★☆
Gegeben sind: Schueler(SNr, Name, Klasse), Kurs(KNr, Bezeichnung, Lehrer) und Belegung(SNr, KNr, Punkte). a) Schreiben Sie eine Abfrage, die die Namen aller Schüler…

Gegeben sind: Schueler(SNr, Name, Klasse), Kurs(KNr, Bezeichnung, Lehrer) und Belegung(SNr, KNr, Punkte).

  1. a) Schreiben Sie eine Abfrage, die die Namen aller Schüler ausgibt, die den Kurs „Informatik“ belegen.
  1. b) Schreiben Sie eine Abfrage, die für jeden Kurs die Durchschnittspunktzahl ausgibt.
  1. c) Schreiben Sie eine Abfrage, die nur Kurse mit mehr als 15 Belegungen ausgibt.
Lösen
Üben★★☆
Gegeben sind Kunde(KNr, Name, Ort) und Bestellung(BNr, KNr, Datum, Betrag). a) Schreiben Sie eine Abfrage, die Name und Gesamtumsatz jedes Kunden ausgibt, der mindestens…

Gegeben sind Kunde(KNr, Name, Ort) und Bestellung(BNr, KNr, Datum, Betrag).

  1. a) Schreiben Sie eine Abfrage, die Name und Gesamtumsatz jedes Kunden ausgibt, der mindestens eine Bestellung hat.
  1. b) Erklären Sie, wie sich das Ergebnis ändert, wenn auch Kunden ohne Bestellung angezeigt werden sollen, und geben Sie die passende Abfrage an (LEFT JOIN).
Lösen

Unterabfragen und Mengenfragen

Manchmal braucht eine Abfrage das Ergebnis einer anderen Abfrage. Eine Unterabfrage steht in Klammern innerhalb von WHERE (oder FROM).

Beispiel mit Artikel(ArtNr, Name, Preis, KatNr): Alle Artikel, die teurer sind als der Durchschnitt aller Artikel.

SELECT Name FROM Artikel WHERE Preis > (SELECT AVG(Preis) FROM Artikel);

Hier liefert die Unterabfrage genau einen Wert, den man mit > vergleichen kann. Liefert sie mehrere Werte, verwenden Sie IN bzw. NOT IN.

Korrelierte Unterabfrage

Soll jeder Artikel mit dem Durchschnitt seiner eigenen Kategorie verglichen werden, muss die Unterabfrage auf die äußere Zeile Bezug nehmen:

SELECT a1.Name FROM Artikel a1 WHERE a1.Preis > (SELECT AVG(a2.Preis) FROM Artikel a2 WHERE a2.KatNr = a1.KatNr);

„Kein“ und „sowohl … als auch“

Zwei typische Mengenfragen mit den Tabellen Mitglied(MNr, Name), Kurs(KNr, Titel) und Anmeldung(MNr, KNr):

Mitglieder ohne Kurs: Entweder mit NOT IN:

SELECT Name FROM Mitglied WHERE MNr NOT IN (SELECT MNr FROM Anmeldung);

oder mit LEFT JOIN und der Prüfung auf NULL:

SELECT m.Name FROM Mitglied m LEFT JOIN Anmeldung a ON m.MNr = a.MNr WHERE a.MNr IS NULL;

Mitglieder, die Kurs A und Kurs B belegen: Eine einzelne Zeile des Verbunds hat nur eine Kursbezeichnung. Die Bedingung Titel = 'A' AND Titel = 'B' kann daher nie erfüllt werden. Man braucht zwei Teilmengen, die man schneidet:

SELECT Name FROM Mitglied WHERE MNr IN (SELECT MNr FROM Anmeldung JOIN Kurs ON Anmeldung.KNr = Kurs.KNr WHERE Titel = 'A') AND MNr IN (SELECT MNr FROM Anmeldung JOIN Kurs ON Anmeldung.KNr = Kurs.KNr WHERE Titel = 'B');

Eine Alternative ist, die Zeilen mit Titel A oder B zu gruppieren und HAVING COUNT(DISTINCT Titel) = 2 zu verwenden.

Üben★★★
Gegeben sind Mitarbeiter(MNr, Name, Gehalt, AbtNr) und Abteilung(AbtNr, Bezeichnung, Standort). a) Schreiben Sie eine Abfrage, die die Namen der Mitarbeiter ausgibt,…

Gegeben sind Mitarbeiter(MNr, Name, Gehalt, AbtNr) und Abteilung(AbtNr, Bezeichnung, Standort).

  1. a) Schreiben Sie eine Abfrage, die die Namen der Mitarbeiter ausgibt, deren Gehalt über dem Durchschnittsgehalt ihrer eigenen Abteilung liegt (Unterabfrage).
  1. b) Schreiben Sie eine Abfrage, die für jeden Standort die Anzahl der Mitarbeiter ausgibt, aber nur für Standorte mit mindestens 10 Mitarbeitern.
  1. c) Erklären Sie die Semantik von Teilaufgabe a) als Abfolge von Auswertungsschritten.
Lösen
Üben★★★
Gegeben sind Schueler(SNr, Name), Kurs(KNr, Bezeichnung) und Belegung(SNr, KNr). a) Schreiben Sie eine Abfrage für alle Schüler, die keinen Kurs belegt haben. b)…

Gegeben sind Schueler(SNr, Name), Kurs(KNr, Bezeichnung) und Belegung(SNr, KNr).

  1. a) Schreiben Sie eine Abfrage für alle Schüler, die keinen Kurs belegt haben.
  1. b) Schreiben Sie eine Abfrage für alle Schüler, die sowohl „Informatik“ als auch „Mathematik“ belegen.
  1. c) Begründen Sie, warum die naive Bedingung Bezeichnung = 'Informatik' AND Bezeichnung = 'Mathematik' in einer einzelnen Zeile kein Ergebnis liefert.
Lösen

Aggregation über mehrere Tabellen und der Entwurf

Die stärksten Abfragen verbinden beide Techniken: erst verknüpfen, dann gruppieren. Gegeben seien Position(BNr, ArtNr, Menge) und Artikel(ArtNr, Name, Preis). Der Wert einer Bestellung ist die Summe aller Positionen, also Menge mal Einzelpreis:

SELECT p.BNr, SUM(p.Menge * a.Preis) FROM Position p JOIN Artikel a ON p.ArtNr = a.ArtNr GROUP BY p.BNr;

Das Produkt Menge * Preis wird je Zeile des Verbunds berechnet, danach summiert SUM je Bestellung.

Üben★★★
Gegeben sind Bestellung(BNr, KNr, Datum), Position(BNr, ArtNr, Menge) und Artikel(ArtNr, Name, Preis). a) Formulieren Sie eine Abfrage, die zu jeder Bestellung den…

Gegeben sind Bestellung(BNr, KNr, Datum), Position(BNr, ArtNr, Menge) und Artikel(ArtNr, Name, Preis).

  1. a) Formulieren Sie eine Abfrage, die zu jeder Bestellung den Gesamtwert ausgibt.
  1. b) Formulieren Sie eine Abfrage, die den Artikel mit dem höchsten verkauften Gesamtumsatz ermittelt.
  1. c) Beschreiben Sie, wie sich das Ergebnis von a) verändert, wenn die Tabelle Position doppelte Zeilen enthält, und welche Konsequenz das für den Entwurf hat.
Lösen
Los geht's! 9 Aufgaben in diesem Abschnitt
★☆☆ Gegeben ist die Tabelle Buch(ISBN, Titel, Autor, Jahr, Preis). a) Schreiben Sie eine…★☆☆ Gegeben ist Kunde(KNr, Name, Ort). a) Geben Sie eine Abfrage an, die die Anzahl der…★☆☆ Gegeben sind Autor(ANr, Name) und Buch(ISBN, Titel, ANr) mit Buch.ANr als Fremdschlüssel.…

Auf einen Blick

Zurück zum Anfang: Die Frage der Vereinsvorsitzenden beantworten Sie mit einem Verbund der Tabellen Mitglied, Anmeldung und Kurs (Teilnehmer) und mit GROUP BY über die Kurse (Teilnehmerzahl).

  • Grundform: SELECT … FROM … WHERE … ORDER BY …; Text in einfachen Hochkommas.

  • Aggregation: COUNT, SUM, AVG, MIN, MAX; mit GROUP BY je Gruppe; WHERE filtert Datensätze vor, HAVING Gruppen nach der Gruppierung.

  • Auswertungsreihenfolge: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

  • Verbund: JOIN … ON Fremdschlüssel = Primärschlüssel; bei $n$ Tabellen $n-1$ Bedingungen; LEFT JOIN behält alle Zeilen der linken Tabelle (NULL bei fehlendem Partner).

  • Unterabfragen: skalar (> (SELECT AVG …)), mit IN/NOT IN für Mengen, korreliert bei Bezug auf die äußere Zeile.

  • Duplikate in Verbindungstabellen verfälschen Summen: Abhilfe ist ein passender (zusammengesetzter) Primärschlüssel.

Checkliste (Operatoren): Sie können eine Abfrage formulieren, ihre Semantik erläutern, ihr Ergebnis ermitteln und Entwurfsfehler begründen.

Alle Aufgaben zu diesem Abschnitt anzeigen