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 Online-Datenbank "Schule".
  Die Zugangsdaten gibt's bei Herrn Kaibel.
  • Wer nicht am SIBI ist, kann hier einen SQL-Dump der Beispieldatenbank Schule herunterladen:
    Datei:Beispieldatenbank-schule.zip

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.

• 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

• 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

• 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

• Wieviele Schüler gibt es insgesamt?

SELECT COUNT(*)
FROM schueler s

• Wieviele Stunden Unterricht werden insgesamt erteilt?

SELECT SUM(u.stunden)
FROM unterricht u

• Wieviele Stunden Sport werden erteilt?

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

• Wieviele Plätze hat der größte Raum?

SELECT MAX(r.plaetze)
FROM raum r

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

• 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

• 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

• 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 summe
FROM unterricht u
GROUP BY u.fach
ORDER BY summe DESC

Mehrere Tabellen

Die folgenden Abfragen beziehen sich auf mehrere Tabellen. Dafür braucht man folgende Technik: SQL Join

SELECT, FROM, WHERE

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

• Eine Liste der Schüler, aus der hervorgeht, in welcher Klasse sie jeweils sind.

SELECT s.name, s.vorname, k.name
FROM schueler s
JOIN klasse k
ON s.klasse_id = k.id

• Eine Liste der Klassen, jeweils mit Klassenlehrer.

SELECT k.name, l.name
FROM klasse k 
JOIN lehrer l
ON k.klassenlehrer_id = l.id

• Eine Liste der Unterrichtsfächer der Klasse 8B.

SELECT u.fach
FROM klasse k
JOIN unterricht u
ON k.id = u.klasse_id
WHERE k.name = '8B'

• Eine Liste der Räume, in denen die 8B Unterricht hat.

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

• Eine Liste der Schüler, die Unterricht in Raum R112 Unterricht haben.

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

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

• Eine Liste der Klassen mit der Anzahl der Schüler; sortiert nach der Anzahl der Schüler.

SELECT k.name, COUNT(s.id)
FROM klasse k JOIN schueler s
ON k.id = s.klasse_id
GROUP BY k.id

• Eine Liste der Klassen, aus der hervorgeht, wieviele Stunden Unterricht die jeweilige Klasse hat. Die Liste soll nach der Anzahl der Stunden sortiert sein.

SELECT k.name, SUM(u.stunden) AS anzahl
FROM klasse k JOIN unterricht u
ON k.id = u.klasse_id
GROUP BY k.id
ORDER BY anzahl DESC

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

SELECT k.name, SUM(u.stunden) AS anzahl
FROM klasse k JOIN unterricht u
ON k.id = u.klasse_id
GROUP BY k.id
ORDER BY anzahl DESC
LIMIT 1

• Eine Liste der Räume, aus der hervorgeht, wie viele Stunden Unterricht in jedem Raum erteilt wird.

SELECT r.nummer, SUM(u.stunden) AS anzahl
FROM raum r
LEFT JOIN unterricht u
ON r.id = u.raum_id
GROUP BY r.id

• "Lehrerraumprinzip": Eine Liste der Lehrer, in der für jeden Lehrer vermerkt ist, in welchem Raum er am wie viele Stunden unterrichtet.
  Hinweis: Man braucht ein GROUP BY für zwei Spalten: GROUP BY l.id, r.id

SELECT l.name, r.nummer, SUM(u.stunden) AS anzahl
FROM lehrer l
JOIN unterricht u
ON l.id = u.lehrer_id
JOIN raum r
ON u.raum_id = r.id
GROUP BY l.id, r.id
ORDER BY l.name, r.nummer

UNION

• Eine Liste, in der Vor- und Nachnamen von Lehrern und Schülern auftauchen.

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

• Disziplinarkonferenz für Schüler Schmidt: Eingeladen werden seine Fachlehrer und alle Klassenlehrer.

SELECT l.name, l.vorname
FROM lehrer l
JOIN unterricht u
ON l.id = u.lehrer_id
JOIN klasse k
ON u.klasse_id = k.id
JOIN schueler s
ON k.id = s.klasse_id
WHERE s.name = 'Schmidt'
UNION
SELECT l.name, l.vorname
FROM lehrer l
JOIN klasse k
ON l.id = k.klassenlehrer_id
JOIN schueler s
ON k.id = s.klasse_id
WHERE s.name = 'Schmidt'

• "Die Sportler: " Eine Liste, in der alle Schüler und Lehrer vermerkt werden, die mit Sport etwas zu tun haben.

TODO

IN, NOT IN

• Welche Klassen haben keinen Sportunterricht?

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

• In welchen Räumen findet nie Sport statt?

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

• Welche Schüler sind Klassenkameraden von Anne Ebert?

SELECT s.name, s.vorname
FROM schueler s
WHERE s.klasse_id IN
(
  SELECT s2.klasse_id
  FROM schueler s2
  WHERE s2.name = 'Ebert'
)

• Welche Lehrer sind Fachkollegen von Josef Zimmermann? (Es reicht, dass sie ein gemeinsames Fach haben.)

SELECT DISTINCT l.name
FROM lehrer l
JOIN unterricht u
ON l.id = u.lehrer_id
WHERE u.fach IN
(
  SELECT u2.fach
  FROM lehrer l2
  JOIN unterricht u2
  ON l2.id = u2.lehrer_id
  WHERE l2.name = 'Zimmermann'
)

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

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

SELECT s.name, s.vorname, k.name
FROM schueler s
LEFT JOIN klasse k
ON s.klasse_id = k.id

• Eine Liste ALLER Klassen, jeweils mit Klassenlehrer.

 TODO

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.

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

SELECT s.name, SUM(u.stunden) AS anzahl
FROM schueler s JOIN unterricht u
ON s.klasse_id = u.klasse_id
GROUP BY s.id
UNION
SELECT s.name, 0 AS anzahl
FROM schueler s LEFT JOIN unterricht u
ON s.klasse_id = u.klasse_id
WHERE u.klasse_id IS NULL

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

 TODO

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.

• Eine Liste der Schüler, die keine Klasse haben.

SELECT s.name, s.vorname
FROM schueler s LEFT JOIN unterricht u
ON s.klasse_id = u.klasse_id
WHERE u.klasse_id IS NULL

• Eine Liste der Lehrer, die nicht unterrichten.

 TODO

• Eine Liste der Räume, in denen kein Unterricht stattfindet.

 TODO

Abfragen mit mehreren Variablen für eine Tabelle

• Welche Schüler sind in der Klasse von Anne Ebert?
  Es gibt zwei Schüler: Anne Ebert (s1) und der "unbekannte Schüler" aus ihrer Klasse (s2).

SELECT s2.name, s2.vorname
FROM schueler s1 JOIN schueler s2
ON s1.klasse_id = s2.klasse_id
WHERE s1.name = 'Ebert' AND s1.vorname = 'Anne'
AND s2.id != s1.id

• Welche Fachlehrer unterrichten in der Klasse von Lehrer Buttenmüller?
  Es gibt zwei Lehrer: Den Lehrer Buttenmüller (l1) und den "unbekannten Fachlehrer" seiner Klasse (l2).

SELECT l2.name, l2.vorname
FROM lehrer l2
JOIN unterricht u
ON l2.id = u.lehrer_id
JOIN klasse k
ON u.klasse_id = k.id
JOIN lehrer l1
ON k.klassenlehrer_id = l1.id
WHERE l1.name = 'Buttenmüller'

SQL-Abfragen über selbstdefinierte Tabellen

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

• 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)
  Es gibt eine innere Abfrage für Deutsch und eine für Geschichte.

SELECT deutsch.klasse AS klasse, deutsch.stunden AS deutsch, geschichte.stunden AS geschichte
FROM
(
 SELECT k.name AS klasse, SUM(u.stunden) AS stunden
 FROM klasse k JOIN unterricht u
 ON k.id = u.klasse_id
 WHERE u.fach = 'deutsch'
 GROUP BY k.id
) AS deutsch , 
(
 SELECT k.name AS klasse, SUM(u.stunden) AS stunden
 FROM klasse k JOIN unterricht u
 ON k.id = u.klasse_id
 WHERE u.fach = 'geschichte'
 GROUP BY k.id
) AS geschichte  
WHERE geschichte.klasse = deutsch.klasse

• Eine Liste aller Klassen, in der angezeigt wird, wieviel Prozent Sport sie haben.

TODO

• 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

TODO

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

TODO

• Wieviele Stunden Unterricht haben die Klassen durchschnittlich?
  Als innere Tabelle hat man eine Liste der Klassen mit ihrer Stundenzahl. In dieser muss man ein UNION verwenden, damit die 8D, die gar keinen Unterricht hat, mitgezählt wird.

SELECT AVG(klassentabelle.gesamt)FROM
(
  SELECT k.name AS name, SUM(u.stunden) AS gesamt
  FROM klasse k JOIN unterricht u
  ON k.id = u.klasse_id
  GROUP BY k.id
  UNION
  SELECT k.name AS name, 0 AS gesamt
  FROM klasse k LEFT JOIN unterricht u
  ON k.id = u.klasse_id
  WHERE u.klasse_id IS NULL
) AS klassentabelle

• Die "Streber-Klassen": Welche Klassen haben überdurchschnittlich viele Stunden Unterricht?

TODO

• Das Fach, das am meisten unterrichtet wird, mit Anzahl der Stunden.
  Zur Kontrolle: Deutsch.

TODO

• 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

TODO

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.
   JOIN über die Tabellen raum, unterricht, klasse, lehrer
2. Wieviel Prozent der insgesamt erteilten Unterrichtsstunden sind Sportstunden?
   zwei innere Abfragen: eine für die Gesamtzahl der Stunden, eine für den Sportunterricht.
3. Welcher Lehrer unterrichtet am meisten? (Mit Angabe der Stundenzahl.)
   GROUP BY , ORDER BY anzahl DESC und LIMIT 1
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).
   Die "Drückeberger" muss man mit UNION anfügen. Für die Drückeberger braucht man LEFT JOIN und IS NULL.
5. Eine Liste der Etagen mit den durchschnittlichen Raumgrößen. Die Liste soll nach den Durchschnittswerten sortiert werden.
   AVG, GROUP BY, ORDER BY durchschnitt DESC
6. Eine Liste der Schüler, die nie in Raum R112 Unterricht haben.
   NOT IN
7. Eine Liste aller Schüler und Lehrer, die in Raum R112 Unterricht haben / unterrichten.
   UNION
8. In welchem Raum finden die meisten Unterrichtsstunden statt?
   GROUP BY, ORDER BY anzahl DESC, LIMIT 1
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.
   Zwei Räume: r1 ist Raum R112, r2 ist ein Raum, in dem weniger Unterricht ist.
   Die Räume, in denen gar kein Unterricht stattfindet muss man mit UNION anhängen.
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.
    GROUP BY über zwei Attribute

SELECT u.fach, r.etage, SUM(u.stunden)
FROM unterricht u JOIN raum r
ON u.raum_id = r.id
GROUP BY u.fach, r.etage
ORDER BY u.fach, r.etage

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 deutsch.raum, deutsch.stunden, englisch.stunden
FROM
(
 SELECT r.nummer AS raum, SUM(u.stunden) AS stunden
 FROM raum r JOIN unterricht u
 ON r.id = u.raum_id
 WHERE u.fach = 'deutsch'
 UNION
 SELECT r.nummer AS raum, 0 AS stunden
 FROM raum r
 WHERE r.id NOT IN
 (
   SELECT u.raum_id FROM unterricht u WHERE u.fach = 'deutsch'
 )
) AS deutsch ,
(
 SELECT r.nummer AS raum, SUM(u.stunden) AS stunden
 FROM raum r LEFT JOIN unterricht u
 ON r.id = u.raum_id
 WHERE u.fach = 'englisch'
 UNION
 SELECT r.nummer AS raum, 0 AS stunden
 FROM raum r
 WHERE r.id NOT IN
 (
   SELECT u.raum_id FROM unterricht u WHERE u.fach = 'englisch'
 )
) AS englisch
where deutsch.raum = englisch.raum