SQL-Aufgaben-Lösungen

Hier finden sich die Lösungen zu den SQL-Aufgaben.

Beispieldatenbank Schule

• Die Lösungen beziehen sich auf die 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

Eine Tabelle

Die folgenden Aufgaben beziehen sich auf nur eine Tabelle.

SELECT, FROM, WHERE, ORDER BY, DISTINCT

Die folgenden Abfragen benutzen die Befehle SELECT, FROM, WHERE und ggf. ORDER BY.

1. Eine Liste aller Schüler mit Name und Vorname, alphabetisch sortiert nach dem Namen.
   SELECT s.name, s.vorname FROM schueler s ORDER BY s.name
2. Eine Liste der Räume: Raumnummer und Anzahl der Plätze, sortiert nach der Anzahl der Plätze und zwar so, dass die großen Räume zuerst kommen.
   SELECT r.nummer, r.plaetze FROM raum r ORDER BY r.plaetze DESC
3. Eine Liste der Etagen in dem Gebäude; jede Etage soll in der Liste nur einmal erscheinen.
   SELECT DISTINCT r.etage FROM raum r

Aggregatfunktionen: COUNT, SUM, AVG, MAX, MIN

1. Wieviele Schüler gibt es insgesamt?
   SELECT COUNT(*) from schueler s
2. Wieviele Stunden Unterricht werden insgesamt erteilt?
   SELECT SUM(u.stunden) FROM unterricht u
3. Wieviele Stunden Sport werden erteilt?
   SELECT SUM(u.stunden) FROM unterricht u WHERE u.fach = 'Sport'
4. Wieviele Plätze hat der größte Raum?
   SELECT MAX(r.plaetze) FROM raum r
5. Wieviele Plätze haben die Räume in der oberen Etage durchschnittlich?
   SELECT AVG(r.plaetze) FROM raum r WHERE r.etage='oben'

GROUP BY, AS

Die folgenden Abfragen benutzen zusätzlich die Befehle GROUP BY und AS.

1. Eine Liste der Etagen, in der vermerkt ist, wieviele Räume es jeweils in der Etage gibt.
   SELECT r.etage, COUNT(*)
FROM raum r
GROUP BY r.etage
2. Eine Liste der Etagen, in der vermerkt ist, wieviele Plätze es jeweils in der Etage gibt.
   SELECT r.etage, SUM(r.plaetze)
FROM raum r
GROUP BY r.etage
3. Eine Liste aller Unterrichtsfächer, in der steht, wieviele Stunden sie jeweils unterrichtet werden; die Unterrichtsfächer mit vielen Stunden sollen oben stehen.
   SELECT u.fach, SUM(u.stunden) AS stunden
FROM unterricht u
GROUP BY r.fach
ORDER BY stunden DESC

Mehrere Tabellen

Die folgenden Abfragen beziehen sich auf mehrere Tabellen. Dafür braucht man folgende Technik: Abfrage über mehrere verknüpfte Tabellen

SELECT, FROM, WHERE

Hier werden nur die Befehle SELECT, FROM und WHERE benutzt.

1. Eine Liste der Schüler, aus der hervorgeht, in welcher Klasse sie jeweils sind.
2. Eine Liste der Klassen, jeweils mit Klassenlehrer.
3. Eine Liste der Unterrichtsfächer der Klasse 8B.
4. Eine Liste der Räume, in denen die 8B Unterricht hat.
5. Eine Liste der Schüler, die Unterricht in Raum R112 Unterricht haben.

ORDER BY, GROUP BY, COUNT, SUM, AVG, MAX, MIN, AS

1. Eine Liste der Klassen mit der Anzahl der Schüler; sortiert nach der Anzahl der Schüler.
2. Eine Liste der Klassen, aus der hervorgeht, wieviele Stunden Unterricht die jeweilige Klasse hat. Die Liste soll nach der Anzahl der Stunden sortiert sein.
3. Die Klasse, die am meisten Unterricht hat; mit der Anzahl der Unterrichtsstunden.
   Man addiert für jede Klasse die Unterrichtsstunden, sortiert dann nach der Stundenzahl absteigend, und führt dann LIMIT 1 aus, um nur die erste Zeile zu bekommen.
4. Eine Liste der Räume, aus der hervorgeht, wie viele Stunden Unterricht in jedem Raum erteilt wird.
5. "Lehrerraumprinzip": Eine Liste der Lehrer, in der für jeden Lehrer vermerkt ist, in welchem Raum er am meisten Stunden unterrichtet.
   Hinweis: Man braucht ein GROUP BY für zwei Spalten: GROUP BY l.id, r.id

UNION

1. Eine Liste, in der Vor- und Nachnamen von Lehrern und Schülern auftauchen.
2. Disziplinarkonferenz für Schüler Schmidt: Eingeladen werden seine Fachlehrer und alle Klassenlehrer.
3. "Die Sportler: " Eine Liste, in der alle Schüler und Lehrer vermerkt werden, die mit Sport etwas zu tun haben.

IN, NOT IN

1. Welche Klassen haben keinen Sportunterricht?
2. In welchen Räumen findet nie Sport statt?
3. Welche Schüler sind Klassenkameraden von Anne Ebert?
4. Welche Lehrer sind Fachkollegen von Josef Zimmermann? (Es reicht, dass sie ein gemeinsames Fach haben.)

LEFT JOIN, RIGHT JOIN

Jetzt sollen bei der Abfrage über mehrere Tabellen auch die Datensätze berücksichtigt werden, die keine Entsprechung in der anderen Tabelle haben, wie z.B. der Schüler Wiesenhoff, der in keiner Klasse ist. Dafür braucht man folgende Technik: Left Join / Right Join

1. Eine Liste ALLER Schüler, aus der hervorgeht, in welcher Klasse sie jeweils sind. Auch Schüler ohne Klasse (z.B. Wiesenhoff) sollen aufgeführt werden.
2. Eine Liste ALLER Klassen, jeweils mit Klassenlehrer.

LEFT JOIN, RIGHT JOIN und UNION

Manchmal braucht man für jeden Eintrag in der Liste eine Zahl, auch wenn die Entsprechung in einer Tabelle fehlt.

Dann muss man zwei SQL-Abfragen mit UNION kombinieren:

• eine Abfrage über mehrere Tabellen
• einen LEFT JOIN für die Datensätze, denen die Entsprechung fehlt.

1. Eine Liste ALLER Schüler, in der steht, wieviel Unterricht sie haben. Für die Schüler Zimmermann und Wiesenhoff soll in dieser Übersicht als Stundenzahl '0' erscheinen.
2. Eine Liste aller Räume, in der steht, wie viele verschiedene Klassen dort Unterricht haben. Bei Räumen, in denen kein Unterricht stattfindet, soll eine 0 stehen.

IS NULL

Hier sollen die "Drückeberger" gesucht werden, d.h. man ist nur an Datensätzen interessiert, die keine Entsprechung in der anderen Tabelle haben.

1. Eine Liste der Schüler, die keine Klasse haben.
2. Eine Liste der Lehrer, die nicht unterrichten.
3. Eine Liste der Räume, in denen kein Unterricht stattfindet.

Abfragen mit mehreren Variablen für eine Tabelle

1. Welche Schüler sind in der Klasse von Anne Ebert?
2. Welche Fachlehrer unterrichten in der Klasse von Lehrer Buttenmüller?

SQL-Abfragen über selbstdefinierte Tabellen

Für die folgenden Fragen braucht man SQL-Abfragen über selbstdefinierte Tabellen.

1. Eine Liste aller Klassen, in der für jede Klasse vermerkt ist, wie viele Stunden Geschichte und wie viele Stunden Deutsch sie hat. (D.h. 3 Spalten)
2. Eine Liste aller Klassen, in der angezeigt wird, wieviel Prozent Sport sie haben.
3. Eine Liste aller Schüler, in der für jeden Schüler steht, wie viele AGs er belegt hat. Es sollen auch Schüler ohne AGs aufgeführt werden.
   Innere Abfrage: ein LEFT JOIN über die Tabellen schueler und teilnahme
4. Eine Liste der Fächer, in der die Anzahl der Unterrichtsstunden und der Prozentsatz am gesamt erteilten Unterricht aufgeführt ist. (Innere Tabelle: die Summe der Unterrichtsstunden)
5. Wieviele Stunden Unterricht haben die Klassen durchschnittlich?
6. Die "Streber-Klassen": Welche Klassen haben überdurchschnittlich viele Stunden Unterricht?
7. Das Fach, das am meisten unterrichtet wird, mit Anzahl der Stunden.
   Zur Kontrolle: Deutsch.
8. Eine Liste aller Schüler, in der für jeden Schüler vermerkt ist, wie viele Stunden er weniger hat als der Schüler mit der maximalen Stundenanzahl.
   Zur Kontrolle: Die meisten Stunden hat Schwarzmüller mit 13 Stunden

Weitere schwierige Abfragen

Für die folgenden Fragen muss man geeignete SQL-Sprachelemente kombinieren.

1. Raum R112 sieht immer furchtbar aus. Der Direktor möchte deswegen mit den Klassenlehrern von allen Klassen sprechen, die in R112 Unterricht haben.
2. Wieviel Prozent der insgesamt erteilten Unterrichtsstunden sind Sportstunden?
3. Welcher Lehrer unterrichtet am meisten? (Mit Angabe der Stundenzahl.)
4. Der Direktor hätte gerne eine Liste der Lehrer, in der aufgeführt wird, wie viele Unterrichtsstunden sie erteilen. Auch die "Drückeberger" sollen in der Liste aufgeführt werden (mit 0 Stunden).
5. Eine Liste der Etagen mit den durchschnittlichen Raumgrößen. Die Liste soll nach den Durchschnittswerten sortiert werden.
6. Eine Liste der Schüler, die nie in Raum R112 Unterricht haben.
7. Eine Liste aller Schüler und Lehrer, die in Raum R112 Unterricht haben / unterrichten.
8. In welchem Raum finden die meisten Unterrichtsstunden statt?
9. In welchen Räumen finden weniger Unterrichtsstunden statt als in Raum R112? Die Liste soll auch die Räume einschließen, in denen kein Unterricht stattfindet.
10. Eine Liste der Unterrichtsfächer, in der für jedes Fach vermerkt ist, wie viele Stunden in der unteren, in der mittleren bzw. in der oberen Etage unterrichtet werden.

Fragen zur Videodatenbank

Mithilfe der SQL-Abfragen kann man interessante Informationen aus der Videodatenbank herauslesen.
Hier einige Vorschläge - der eigenen Kreativität sind aber keine Grenzen gesetzt!

1. Eine Liste der Filme, die am häufigsten ausgeliehen wurden, jeweils mit Anzahl der Ausleihvorgänge.
   Eine Liste der Filme, die von den meisten Kunden gesehen wurden, jeweils mit Anzahl der Kunden. (Dabei soll egal sein, wie oft ein Kunde einen Film gesehen hat.)
2. Gibt es Filme, die noch nie jemand ausgeliehen hat?
3. Alle Filme, in denen sowohl Orlando Bloom als auch Christopher Lee als Hauptdarsteller mitspielen.
4. Mit welchen Schauspielern arbeitet der Regisseur Steven Spielberg am liebsten zusammen?
5. Mit welchen Schauspielern hat Steven Spielberg noch nie zusammengearbeitet?
6. Wieviele Filme hat Harrison Ford MEHR gedreht als Orlando Bloom?
7. Eine Liste der Genres, in der steht, wie oft Filme des jeweiligen Genres ausgeliehen wurden.
8. Eine Liste der Genres, in der steht, wie oft Filme des jeweiligen Genres im Jahr 2001 bzw. 2002 ausgeliehen wurden.
9. Welche Genres sollte man mal aussortieren, weil sie eh fast niemand (oder gar niemand!) schaut?
10. Die Lieblingsgenres der Kundin "Christina Olt".
11. Eine Liste der Filme, die mehr als 10 mal ausgeliehen wurden - mit Anzahl der Ausleihen.
    Hier ist der Befehl HAVING sehr nützlich.

Data-Mining: Filmempfehlungen

Unter Data-Mining (englisch Daten-Bergbau, sinngemäß "in einem Datenberg nach wertvollem Wissen suchen") versteht man die systematische Anwendung statistischer Methoden auf einen Datenbestand mit dem Ziel, neue Muster zu erkennen.

Jetzt soll Data-Mining für die Filmdatenbank betrieben werden mit dem Ziel, sinnvolle Filmempfehlungen auszusprechen.

1. Eine Filmempfehlung für Christina Olt: Ihr soll ein Film ihres Lieblingsgenres empfohlen werden! Empfohlen werden soll der beliebteste Film, den sie noch nicht gesehen hat. "Beliebtheit" misst sich daran, wie viele Leute den Film schon gesehen haben.
   Wichtiger Hinweis: Das Genre "Drama" sollte man dabei ausschließen! Fast jeder Film in der Datenbank gehört zum Genre Drama - deswegen ist dieses Genre überhaupt nicht aussagekräftig!
   Zur Kontrolle: Sie schaut am liebsten Western; die folgenden drei Filme kann man ihr empfehlen: 'Gold Rush','Buono, il brutto, il cattivo', 'No Country for Old Men'
   1. Dasselbe für Grace Gertz!
2. Eine Filmempfehlung für Claudius Freudenberger: Hier soll der möglichst ähnliche Kunde gesucht werden, der einen möglichst ähnlichen Filmgeschmack hat wie Claudius Freudenberger, d.h. er soll möglichst viele Filme ausgeliehen haben, die auch Claudius Freudenberger ausgeliehen hat. Von den Filmen, die dieser Kunde ausgeliehen hat, soll Claudius Freudenberger sein "Lieblingsfilm" empfohlen werden, d.h. der Film, den der Kunde am häufigsten gesehen hat - natürlich soll Claudius Freudenberger den Film noch nicht gesehen haben.
   

Zur Kontrolle: Der ähnlichste Kunde ist Nikodemus Schönnagel

Gemischte Aufgaben (schwierig bis sehr schwierig)

Eine Liste der Klassen, die in Raum R112 Unterricht haben.
Ergebnis: 8A, 8C.

JOIN über 3 Tabellen

SELECT k.name
FROM klasse k JOIN unterricht u JOIN raum r
ON k.id = u.klasse_id AND u.raum_id = r.id
WHERE r.nummer = 'R112'

Eine Liste aller AGs mit Teilnehmerzahl.
Ergebnis: Z.B. Fotografie 2

JOIN über 2 Tabellen und GROUP BY

SELECT a.name, COUNT(t.schueler_id) AS anzahl
FROM ag a JOIN teilnahme t
ON a.id = t.ag_id
GROUP BY a.id

Eine Liste der Räume, in denen nie Deutsch stattfindet.
Ergebnis: R110, R306, Sporthalle (=wenig erstaunlich...), R208, R101

 Differenz
 
 SELECT r.nummer
 FROM raum r
 WHERE r.id NOT IN
 ( SELECT u.raum_id
   FROM unterricht u
   WHERE u.fach = 'Deutsch'
 )

Eine Liste der Fachkollegen von Josef Zimmermann.
Ergebnis: Erika Huber und Josef Zimmermann selber.

Zwei Variablen für eine Entitätsmenge:
l1 = der gesuchte Lehrer; u1 = sein Unterricht
l2 = Lehrer Zimmermann; u2 = sein Unterricht

SELECT DISTINCT l1.name
FROM lehrer l1
JOIN unterricht u1
JOIN unterricht u2
JOIN lehrer l2
ON l1.id = u1.lehrer_id
AND u1.fach = u2.fach
AND u2.lehrer_id = l2.id
WHERE l2.name = 'Zimmermann'

Eine Liste aller Schüler, in der aufgeführt wird, an welchen AGs sie teilnehmen.
Wenn ein Schüler an mehreren AGs teilnimmt, dann soll er mehrfach aufgeführt werden.
Schüler, die an keiner AG teilnehmen, sollen nicht aufgeführt werden.
Ergebnis: Z.B. Johannes Meyer nimmt an Holzwerken und Fotografie teil.
Die Schüler Zimmermann und Wiesenhoff tauchen in der Liste nicht auf.

2x JOIN

SELECT s.name, a.name
FROM schueler s JOIN teilnahme t JOIN ag a
ON s.id = t.schueler_id AND t.ag_id = a.id

Wie die vorhergehende Abfrage, aber: Auch Schüler, die an keiner AG teilnehmen, sollen aufgeführt werden.
Ergebnis: Jetzt sollen die Schüler Zimmermann und Wiesenhoff auftauchen.

1x LEFT JOIN, dann JOIN

SELECT s.name, a.name
FROM schueler s LEFT JOIN (teilnahme t JOIN ag a)
ON s.id = t.schueler_id AND t.ag_id = a.id

Es soll eine Liste erstellt werden, aus der hervorgeht, wie viele Stunden jeder Lehrer arbeitet!
Dabei zählen die Unterrichtsstunden normal und jede AG, die ein Lehrer leitet, zählt 1 Stunde.
Ergebnis: Z.B. für den Lehrer Zimmermann: 1AG + 4Deutsch + 4Deutsch + 2Geschichte = 11

2 innere Abfragen.
Es werden hier nur die Lehrer aufgeführt, die sowohl unterrichten als auch AGs haben.

SELECT ustunden.name AS name, ustunden.anzahl + agstunden.anzahl AS anzahl
FROM
( SELECT l.name AS name, SUM(u.stunden) AS anzahl
  FROM lehrer l, unterricht u
  WHERE l.id = u.lehrer_id
  GROUP BY l.name
) AS ustunden
,
( SELECT l.name AS name, COUNT(*) AS anzahl
  FROM lehrer l, ag a
  WHERE l.id = a.lehrer_id
  GROUP BY l.name
) AS agstunden
WHERE ustunden.name = agstunden.name

Eine Liste, in der alle Räume aufgeführt sind.
Zu jedem Raum soll in einer Spalte angegeben werden, wie viele Stunden Deutsch dort unterrichtet wird
und in einer zweiten Spalte, wie viele Stunden Englisch.
Ergebnis: Z.B. R112: 4 Deutsch, 4 Englisch

2 innere Abfragen

SELECT x.name, x.anzahl + y.stunden
FROM
( SELECT l.name AS name, COUNT(a.id) AS anzahl
  FROM lehrer l LEFT join ag a
  ON l.id = a.lehrer_id
  GROUP BY l.id
) AS x
,
( SELECT l.name AS name, SUM(u.stunden) AS stunden
  FROM lehrer l LEFT join unterricht u
  ON l.id = u.lehrer_id
  GROUP BY l.id
) AS y
WHERE x.name = y.name