SQL-Aufgaben

Dies ist eine Sammlung von SQL-Aufgaben zur Beispieldatenbank Schule:

• Die Aufgaben sind nach technischen Gesichtspunkten und Schwierigkeitsgrad gegliedert.
• Lösungen zu den Aufgaben finden sich hier: SQL-Aufgaben-Lösungen.
• Erläuterungen zu SQL am Beispiel der Datenbank "Schule" finden sich im Artikel SQL.
• Testen kann man SQL-Abfragen auf der Online-Datenbank "Schule".
  Die Zugangsdaten werden im Informatikunterricht am SIBI mitgeteilt.
  Wer nicht am SIBI ist, kann einen Dump der Datenbank "Schule" hier herunterladen:
  Datei:Beispieldatenbank-schule.zip

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

1. Eine Liste aller Schüler mit Name und Vorname, alphabetisch sortiert nach dem Namen.
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.
3. Eine Liste der Etagen in dem Gebäude; jede Etage soll in der Liste nur einmal erscheinen.

Aggregatfunktionen: COUNT, SUM, AVG, MAX, MIN

1. Wieviele Schüler gibt es insgesamt?
2. Wieviele Stunden Unterricht werden insgesamt erteilt?
3. Wieviele Stunden Sport werden erteilt?
4. Wieviele Plätze hat der größte Raum?
5. Wieviele Plätze haben die Räume in der oberen Etage durchschnittlich?

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.
2. Eine Liste der Etagen, in der vermerkt ist, wiviele Plätze es jeweils in der Etage gibt.
3. Eine Liste aller Unterrichtsfächer, in der steht, wieviele Stunden sie jeweils unterrichtet werden; die Unterrichtsfächer mit vielen Stunden sollen oben stehen.

Mehrere Tabellen

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

SELECT, FROM, WHERE

Hier werden nur die Befehle SELECT, FROM, JOIN...ON 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 wie viele 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)
   Zur Kontrolle: 8A 4/0; 8B 4/0; 8C 4/2; 8D 0/0
2. Eine Liste aller Klassen, in der angezeigt wird, wieviel Prozent Sport sie haben.
   Zur Kontrolle: 8A 3/11 = 27,3%; 8B 3/7 = 42,9%; 8C 3/13 = 23,1%; 8D hat gar keinen Unterricht.
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
   Zur Kontrolle: Meier 2, Schwarzmüller 2, Schmidt 3, Ebert 2, Zimmermann und Wiesenhoff 0
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)
   Zur Kontrolle: Deutsch 12 -> 38,7%; Mathe 4 -> 12,9%; Sport 9 -> 29,0%; Englisch 4 -> 12,9%; Geschichte 2 -> 6,5%
5. Wieviele Stunden Unterricht haben die Klassen durchschnittlich?
   Zur Kontrolle: 31 Stunden geteilt durch 4 Klassen = 7,75 Stunden
6. Die "Streber-Klassen": Welche Klassen haben überdurchschnittlich viele Stunden Unterricht?
   Zur Kontrolle: Die 8A (11) und die 8C (13).
7. Das Fach, das am meisten unterrichtet wird, mit Anzahl der Stunden.
   Zur Kontrolle: Deutsch (12 Std.)
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. Dann folgen Schmidt (2 weniger), Meier und Ebert (6 weniger), Zimmermann und Wiesenhoff (13 weniger)

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.
   Zur Kontrolle: Zimmermann (8A) und Buttenmüller (8C)
2. Wieviel Prozent der insgesamt erteilten Unterrichtsstunden sind Sportstunden?
   Zur Kontrolle: 9/31 = 29,0%
3. Welcher Lehrer unterrichtet am meisten? (Mit Angabe der Stundenzahl.)
   Zur Kontrolle: Huber mit 3+3+4+3 = 13 Stunden
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).
   Zur Kontrolle: Buttenmüller 4, Zimmermann 10, Amann 4, Huber 13, Rees 0
5. Eine Liste der Etagen mit den durchschnittlichen Anzahl an Plätzen. Die Liste soll nach den Durchschnittswerten sortiert werden.
   Zur Kontrolle: Mitte 23; oben 25; unten 39,5
6. Eine Liste der Schüler, die nie in Raum R112 Unterricht haben.
   Zur Kontrolle: Meier, Ebert, Zimmermann, Wiesenhoff
7. Eine Liste aller Schüler und Lehrer, die in Raum R112 Unterricht haben / unterrichten.
   Zur Kontrolle: Schüler Schmidt, Schwarzmüller und Lehrer Buttenmüller, Zimmermann
8. In welchem Raum finden die meisten Unterrichtsstunden statt?
   Zur Kontrolle: In Raum R112 und R205 - jeweils 8 Stunden.
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.
   Zur Kontrolle: R110, R203, R306, Sporthalle, R208, R101
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.
    Zur Kontrolle: Deutsch 4/8/0; Mathe Mitte 0/4/0; Sport 9/0/0; Englisch 4/0/0; Geschichte 0/0/2

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)

Hier lohnt es sich zuerst zu überlegen, welche Technik man einsetzt!

1. Eine Liste der Klassen, die in Raum R112 Unterricht haben.
   Ergebnis: 8A, 8C.
2. Eine Liste aller AGs mit Teilnehmerzahl.
   Ergebnis: Z.B. Fotografie 2
   
3. Eine Liste der Räume, in denen nie Deutsch stattfindet.
   Ergebnis: R110, R306, Sporthalle (=wenig erstaunlich...), R208, R101
4. Eine Liste der Fachkollegen von Josef Zimmermann.
   Ergebnis: Erika Huber und Josef Zimmermann selber.
5. 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.
6. 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.
7. 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
8. 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