SQL-Abfragen über mehrere Tabellen · Informatik Q1 Leistungskurs

Einfache Abfragen auf einer Tabelle

Eine SQL-Abfrage beschreibt, was Sie wissen wollen, nicht, wie das Datenbanksystem es berechnet. Das Grundgerüst besteht aus drei Teilen. SELECT nennt die gewünschten Spalten, FROM die Tabelle und WHERE die Bedingung, die eine Zeile erfüllen muss. Mit ORDER BY sortieren Sie das Ergebnis. Das Standardverhalten ist aufsteigend (ASC), absteigend schreiben Sie DESC.

Als Beispiel dient die Tabelle Buch(ISBN, Titel, Jahr, Preis). Der Primärschlüssel ist unterstrichen gedacht, hier ISBN.

SELECT Titel, Preis

FROM Buch

WHERE Jahr >= 2005

ORDER BY Preis DESC;

Diese Abfrage liefert Titel und Preis aller Bücher ab 2005, das teuerste zuerst. Bedingungen verknüpfen Sie mit AND, OR und NOT. Vergleichsoperatoren sind =, <>, <, >, <= und >=. Zeichenketten stehen in einfachen Anführungszeichen, zum Beispiel Autor = 'Kehlmann'. Zahlen stehen ohne Anführungszeichen und mit Dezimalpunkt, zum Beispiel Preis < 9.99.

Ü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).

a) Schreiben Sie eine SQL-Abfrage, die alle Titel mit Erscheinungsjahr nach 2010 ausgibt.

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 die einzelne Zeile, sondern eine Kennzahl. Dafür gibt es Aggregatfunktionen, die viele Zeilen zu einem Wert zusammenfassen: COUNT, SUM, AVG, MIN und MAX.

SELECT COUNT(*) FROM Kunde WHERE Ort = 'Münster';

Mit GROUP BY bilden Sie Gruppen von Zeilen mit gleichem Wert und berechnen die Funktion für jede Gruppe getrennt:

SELECT Ort, COUNT(*) AS Anzahl

FROM Kunde

GROUP BY Ort;

Das Ergebnis hat eine Zeile pro Ort. Nach GROUP BY dürfen im SELECT nur die Gruppierungsspalten und Aggregatfunktionen stehen.

Bedingungen an Gruppen formulieren Sie mit HAVING. WHERE filtert dagegen einzelne Zeilen vor dem Gruppieren. Die Reihenfolge, in der das Datenbanksystem die Teile auswertet, zeigt die Abbildung.

Daraus folgt: In WHERE darf keine Aggregatfunktion stehen, denn die Gruppen existieren zu diesem Zeitpunkt noch nicht.

Orte mit mindestens zwei Kunden

Gegeben ist Kunde(KNr, Name, Ort). Gesucht sind alle Orte, in denen mindestens zwei Kunden wohnen, mit der Anzahl.

Gruppieren: Wir brauchen eine Zeile pro Ort, also GROUP BY Ort.

Zählen: Pro Gruppe zählen wir die Zeilen mit COUNT(*).

Gruppen filtern: Die Bedingung betrifft die gezählte Anzahl, also gehört sie in HAVING:

SELECT Ort, COUNT(*) AS Anzahl

FROM Kunde

GROUP BY Ort

HAVING COUNT(*) >= 2;

Wie ändert sich die Abfrage, wenn nur Kunden berücksichtigt werden sollen, deren Name mit „M“ beginnt? Schreiben Sie die Zeile in Ihr Heft.

Eine Bedingung an einzelne Zeilen kommt in WHERE, und zwar vor GROUP BY: WHERE Name LIKE 'M%'.

Ü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).

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 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).

a) Geben Sie das Ergebnis von SELECT Titel FROM Film WHERE Jahr > 1990 ORDER BY Titel; an.

b) Schreiben Sie eine Abfrage, die für jedes Kino die Summe der Besucher für Filme vor 2000 ausgibt.

c) Erläutern Sie den Unterschied zwischen WHERE und HAVING.

Lösen

Tabellen verknüpfen: der Verbund (JOIN)

Gut entworfene Datenbanken speichern jede Information nur einmal. Deshalb steht der Name eines Autors in Autor und im Buch nur dessen Nummer als Fremdschlüssel. Wollen Sie Titel und Autorenname gemeinsam ausgeben, müssen Sie die Tabellen über den Schlüssel verknüpfen. Das leistet der Verbund (JOIN).

Die Syntax lautet:

SELECT Buch.Titel, Autor.Name

FROM Buch

JOIN Autor ON Buch.ANr = Autor.ANr;

Die Semantik lässt sich in drei Schritten beschreiben. Das System bildet erstens alle Paarungen aus je einer Zeile von Buch und einer von Autor. Es behält zweitens nur die Paare, die die ON-Bedingung erfüllen, also gleiche ANr haben. Aus diesen Zeilen wählt es drittens die Spalten des SELECT. Ein solcher Verbund heißt auch Inner Join.

Gleichwertig ist die ältere Schreibweise FROM Buch, Autor WHERE Buch.ANr = Autor.ANr. Über mehr als zwei Tabellen verbinden Sie mit weiteren JOIN … ON …. Existiert ein Spaltenname in mehreren Tabellen, setzen Sie den Tabellennamen davor (Buch.ANr). Kürzer geht es mit Alias-Namen: FROM Buch b JOIN Autor a ON b.ANr = a.ANr.

Bücher von Kehlmann

Gesucht sind die Titel aller Bücher von „Kehlmann“, wie in der Bibliothek vom Anfang.

Tabellen bestimmen: Der Titel steht in Buch, der Name in Autor. Wir brauchen beide.

Verknüpfen: Verbindungsspalte ist ANr, also JOIN Autor ON Buch.ANr = Autor.ANr.

Filtern und auswählen:

SELECT Titel

FROM Buch

JOIN Autor ON Buch.ANr = Autor.ANr

WHERE Name = 'Kehlmann';

Ergebnis mit den Daten aus der Abbildung: „Die Vermessung der Welt“ und „Ruhm“. Damit ist die Frage vom Anfang beantwortet.

Wie lautet die Abfrage, die für jeden Autor die Anzahl seiner Bücher ausgibt? Denken Sie an GROUP BY.

SELECT Name, COUNT(*) AS Anzahl

FROM Autor

JOIN Buch ON Autor.ANr = Buch.ANr

GROUP BY Name;

Zeh erscheint nicht, weil es für ihn keine passende Zeile in Buch gibt. Dazu mehr im nächsten Abschnitt.

Ü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.

a) Erklären Sie, was ein Verbund (JOIN) bewirkt.

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).

a) Schreiben Sie eine Abfrage, die die Namen aller Schüler ausgibt, die den Kurs „Informatik“ belegen.

b) Schreiben Sie eine Abfrage, die für jeden Kurs die Durchschnittspunktzahl ausgibt.

c) Schreiben Sie eine Abfrage, die nur Kurse mit mehr als 15 Belegungen ausgibt.

Lösen

LEFT JOIN: auch Zeilen ohne Partner

Der gewöhnliche JOIN verliert Zeilen ohne Partner. Autorin Zeh hat in unserem Beispiel kein Buch und taucht deshalb nicht im Ergebnis auf. Der LEFT JOIN behält alle Zeilen der linken Tabelle (die vor dem Schlüsselwort stehende). Gibt es keinen Partner, füllt das System die Spalten der rechten Tabelle mit NULL.

SELECT Autor.Name, Buch.Titel

FROM Autor

LEFT JOIN Buch ON Autor.ANr = Buch.ANr;

Name	Titel
Kehlmann	Die Vermessung der Welt
Kehlmann	Ruhm
Funke	Tintenherz
Herrndorf	Tschick
Zeh	NULL

Diese Eigenschaft nutzt man auch, um fehlende Partner zu finden: … WHERE Buch.ISBN IS NULL liefert nur Zeh. Den Vergleich mit NULL schreiben Sie immer mit IS NULL, nie mit = NULL.

Ü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).

a) Schreiben Sie eine Abfrage, die Name und Gesamtumsatz jedes Kunden ausgibt, der mindestens eine Bestellung hat.

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 zusammengesetzte Abfragen

Manchmal hängt eine Abfrage vom Ergebnis einer anderen ab. Dann setzen Sie eine Unterabfrage (Subquery) in Klammern in die Bedingung ein. Das System wertet sie zuerst aus.

Skalare Unterabfrage (liefert genau einen Wert):

SELECT Titel FROM Buch

WHERE Preis > (SELECT AVG(Preis) FROM Buch);

Mengen-Unterabfrage mit IN oder NOT IN (liefert eine Spalte mit mehreren Werten):

SELECT Name FROM Autor

WHERE ANr NOT IN (SELECT ANr FROM Buch);

Das liefert alle Autoren ohne Buch, also Zeh. Alternativ funktioniert NOT EXISTS oder der LEFT JOIN mit IS NULL.

Korrelierte Unterabfrage: Sie bezieht sich auf die äußere Zeile. Ein Beispiel ist der Vergleich eines Buchpreises mit dem Durchschnitt des eigenen Autors:

SELECT b.Titel

FROM Buch b

WHERE b.Preis > (SELECT AVG(Preis) FROM Buch

                 WHERE ANr = b.ANr);

Die Semantik als Abfolge: Das System nimmt jede Zeile b der äußeren Tabelle, berechnet für deren ANr den Durchschnitt in der inneren Abfrage und behält b, wenn die Bedingung gilt.

Schüler in zwei Kursen

Gegeben sind Schueler(SNr, Name) und Belegung(SNr, KNr). Gesucht sind die Namen der Schüler, die mindestens zwei Kurse belegt haben.

Idee: Wir zählen pro Schüler die Belegungen und behalten Gruppen mit mindestens 2.

Abfrage:

SELECT Name

FROM Schueler

JOIN Belegung ON Schueler.SNr = Belegung.SNr

GROUP BY Schueler.SNr, Name

HAVING COUNT(*) >= 2;

Warum gruppieren wir nach Schueler.SNr und nicht nur nach Name? Überlegen Sie, was bei zwei Schülern mit gleichem Namen passiert.

Zwei verschiedene Schüler mit gleichem Namen würden in einer Gruppe landen, ihre Belegungen würden addiert. Der Primärschlüssel unterscheidet sie sicher.

Eine Bedingung wie „belegt Informatik und Mathematik“ lässt sich nicht in einer einzigen Zeile prüfen. Die Spalte Bezeichnung hat in einer Zeile nur einen Wert, also kann Bezeichnung = 'Informatik' AND Bezeichnung = 'Mathematik' nie wahr werden. Sie brauchen zwei Zeilen desselben Schülers. Das erreichen Sie mit zwei IN-Unterabfragen, mit einem Verbund der Tabelle mit sich selbst (Alias-Namen) oder mit GROUP BY … HAVING COUNT(DISTINCT …) = 2.

Bei mehreren Tabellen gehen Sie am besten schrittweise vor. Erstens: Welche Spalten brauche ich, und in welchen Tabellen stehen sie? Zweitens: Über welche Schlüssel verknüpfe ich? Drittens: Welche Zeilen filtere ich mit WHERE? Viertens: Muss ich gruppieren, und welche Bedingung gilt für Gruppen? Fünftens: Wie sortiere ich?

Ü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).

a) Schreiben Sie eine Abfrage, die die Namen der Mitarbeiter ausgibt, deren Gehalt über dem Durchschnittsgehalt ihrer eigenen Abteilung liegt (Unterabfrage).

b) Schreiben Sie eine Abfrage, die für jeden Standort die Anzahl der Mitarbeiter ausgibt, aber nur für Standorte mit mindestens 10 Mitarbeitern.

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).

a) Schreiben Sie eine Abfrage für alle Schüler, die keinen Kurs belegt haben.

b) Schreiben Sie eine Abfrage für alle Schüler, die sowohl „Informatik“ als auch „Mathematik“ belegen.

c) Begründen Sie, warum die naive Bedingung Bezeichnung = 'Informatik' AND Bezeichnung = 'Mathematik' in einer einzelnen Zeile kein Ergebnis liefert.

Lösen

Ü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).

a) Formulieren Sie eine Abfrage, die zu jeder Bestellung den Gesamtwert ausgibt.

b) Formulieren Sie eine Abfrage, die den Artikel mit dem höchsten verkauften Gesamtumsatz ermittelt.

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

Eine SQL-Abfrage hat das Grundgerüst SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …. Ausgewertet wird in der Reihenfolge FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.
Aggregatfunktionen (COUNT, SUM, AVG, MIN, MAX) fassen Zeilen zusammen. WHERE filtert Zeilen vor dem Gruppieren, HAVING filtert Gruppen danach.
Ein JOIN … ON verknüpft Tabellen über Primär- und Fremdschlüssel. Ohne Verbundbedingung entsteht das kartesische Produkt.
Ein LEFT JOIN behält alle Zeilen der linken Tabelle, fehlende Partner werden NULL. Mit IS NULL finden Sie Zeilen ohne Partner. Zählen Sie dann eine Spalte der rechten Tabelle.
Unterabfragen (skalar, mit IN/NOT IN, korreliert) lösen Aufgaben, bei denen ein Zwischenergebnis nötig ist.
Mit den Operatoren erläutern und analysieren beschreiben Sie Syntax und Semantik einer Abfrage Schritt für Schritt, so wie in den Beispielen oben.

Vom Anfang: Die Bibliotheksfrage lösen Sie mit einem Verbund von Buch und Autor über ANr und der Bedingung Name = 'Kehlmann'.