SQL-Aufgaben-Lösungen: Unterschied zwischen den Versionen

Aus SibiWiki
Zur Navigation springen Zur Suche springen
 
(32 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt)
Zeile 10: Zeile 10:
* An der Beispieldatenbank Schule können alle möglichen Datenbankabfragen durchprobiert werden.
* 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.
* 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 [http://sibi-wiki.de/informatikag/videodb/index.php hier]. Die Zugangsdaten gibt's bei Herrn Kaibel
* Testen kann man SQL-Abfragen auf der '''[http://sibi-wiki.de/sql/ Online-Datenbank "Schule"]'''. <br/>Die Zugangsdaten gibt's bei Herrn Kaibel.
** ''Wer nicht am SIBI ist, kann hier einen SQL-Dump der Beispieldatenbank Schule herunterladen: <br/>[[Datei:Beispieldatenbank-schule.zip]]''


[[Datei:Beispieldatenbank-schule.jpg]]
[[Datei:Beispieldatenbank-schule.jpg]]
Zeile 18: Zeile 19:
==SELECT, FROM,  WHERE, ORDER BY, DISTINCT ==
==SELECT, FROM,  WHERE, ORDER BY, DISTINCT ==
Die folgenden Abfragen benutzen die Befehle SELECT, FROM, WHERE und ggf. ORDER BY.
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.<br/>''SELECT s.name, s.vorname FROM schueler s ORDER BY s.name''
* Eine Liste aller Schüler mit Name und Vorname, alphabetisch sortiert nach dem Namen.
# 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.<br/>''SELECT r.nummer, r.plaetze FROM raum r ORDER BY r.plaetze '''DESC''' ''
<code>
# Eine Liste der Etagen in dem Gebäude; jede Etage soll in der Liste nur einmal erscheinen.<br/>''SELECT '''DISTINCT''' r.etage FROM raum r''
SELECT s.name, s.vorname
FROM schueler s
ORDER BY s.name
</code>
 
* 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.
<code>
SELECT r.nummer, r.plaetze
FROM raum r
ORDER BY r.plaetze '''DESC'''
</code>
* Eine Liste der Etagen in dem Gebäude; jede Etage soll in der Liste nur einmal erscheinen.
<code>
SELECT '''DISTINCT''' r.etage
FROM raum r
</code>


==Aggregatfunktionen: COUNT, SUM, AVG, MAX, MIN==
==Aggregatfunktionen: COUNT, SUM, AVG, MAX, MIN==
# Wieviele Schüler gibt es insgesamt?<br/>''SELECT '''COUNT(*)''' from schueler s''
* Wieviele Schüler gibt es insgesamt?
# Wieviele Stunden Unterricht werden insgesamt erteilt?<br/>''SELECT '''SUM(u.stunden)''' FROM unterricht u''
<code>
# Wieviele Stunden Sport werden erteilt? <br/>''SELECT '''SUM(u.stunden)''' FROM unterricht u WHERE u.fach = 'Sport' ''
SELECT '''COUNT(*)'''
# Wieviele Plätze hat der größte Raum?<br/>''SELECT '''MAX(r.plaetze)''' FROM raum r''
FROM schueler s
# Wieviele Plätze haben die Räume in der oberen Etage durchschnittlich?<br/>''SELECT '''AVG(r.plaetze)''' FROM raum r WHERE r.etage='oben' ''
</code>
* Wieviele Stunden Unterricht werden insgesamt erteilt?
<code>
SELECT '''SUM(u.stunden)'''
FROM unterricht u
</code>
* Wieviele Stunden Sport werden erteilt?
<code>
SELECT '''SUM(u.stunden)'''
FROM unterricht u
WHERE u.fach = 'Sport'
</code>
* Wieviele Plätze hat der größte Raum?
<code>
SELECT '''MAX(r.plaetze)'''
FROM raum r
</code>
* Wieviele Plätze haben die Räume in der oberen Etage durchschnittlich?
<code>
SELECT '''AVG(r.plaetze)'''
FROM raum r
WHERE r.etage='oben'
</code>


==GROUP BY, AS==
==GROUP BY, AS==
Die folgenden Abfragen benutzen ''zusätzlich'' die Befehle GROUP BY und 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.<br/><code>SELECT r.etage, COUNT(*)<br/>FROM raum r<br/>'''GROUP BY r.etage'''</code>
*Eine Liste der Etagen, in der vermerkt ist, wieviele Räume es jeweils in der Etage gibt.
# Eine Liste der Etagen, in der vermerkt ist, wieviele Plätze es jeweils in der Etage gibt.<br/><code>SELECT r.etage, SUM(r.plaetze)<br/>FROM raum r<br/>'''GROUP BY r.etage'''</code>
<code>
# Eine Liste aller Unterrichtsfächer, in der steht, wieviele Stunden sie jeweils unterrichtet werden; die Unterrichtsfächer mit vielen Stunden sollen oben stehen.<br/><code>SELECT u.fach, SUM(u.stunden) '''AS stunden'''<br/>FROM unterricht u<br/>'''GROUP BY r.fach'''<br/>'''ORDER BY stunden DESC'''</code>
SELECT r.etage, COUNT(*)
FROM raum r
'''GROUP BY r.etage'''
</code>
 
* Eine Liste der Etagen, in der vermerkt ist, wieviele Plätze es jeweils in der Etage gibt.
<code>
SELECT r.etage, SUM(r.plaetze)
FROM raum r
'''GROUP BY r.etage'''
</code>
 
* Eine Liste aller Unterrichtsfächer, in der steht, wieviele Stunden sie jeweils unterrichtet werden; die Unterrichtsfächer mit vielen Stunden sollen oben stehen.
<code>
SELECT u.fach, SUM(u.stunden) '''AS summe'''
FROM unterricht u
'''GROUP BY u.fach'''
'''ORDER BY summe DESC'''
</code>


=Mehrere Tabellen=
=Mehrere Tabellen=
Zeile 40: Zeile 96:
==SELECT, FROM, WHERE==
==SELECT, FROM, WHERE==
Hier werden nur die Befehle SELECT, FROM und WHERE benutzt.
Hier werden nur die Befehle SELECT, FROM und WHERE benutzt.
# Eine Liste der Schüler, aus der hervorgeht, in welcher Klasse sie jeweils sind.
*Eine Liste der Schüler, aus der hervorgeht, in welcher Klasse sie jeweils sind.
# Eine Liste der Klassen, jeweils mit Klassenlehrer.
<code>
# Eine Liste der Unterrichtsfächer der Klasse 8B.
SELECT s.name, s.vorname, k.name
# Eine Liste der Räume, in denen die 8B Unterricht hat.
FROM schueler s
# Eine Liste der Schüler, die Unterricht in Raum R112 Unterricht haben.
'''JOIN''' klasse k
'''ON''' s.klasse_id = k.id
</code>
* Eine Liste der Klassen, jeweils mit Klassenlehrer.
<code>
SELECT k.name, l.name
FROM klasse k
'''JOIN''' lehrer l
'''ON''' k.klassenlehrer_id = l.id
</code>
* Eine Liste der Unterrichtsfächer der Klasse 8B.
<code>
SELECT u.fach
FROM klasse k
'''JOIN''' unterricht u
'''ON''' k.id = u.klasse_id
WHERE k.name = '8B'
</code>
* Eine Liste der Räume, in denen die 8B Unterricht hat.
<code>
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'
</code>
* Eine Liste der Schüler, die Unterricht in Raum R112 Unterricht haben.
<code>
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'
</code>


==ORDER BY, GROUP BY, COUNT, SUM, AVG, MAX, MIN, AS==
==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.
* Eine Liste der Klassen mit der Anzahl der Schüler; sortiert nach der Anzahl der Schüler.
# Eine Liste der Klassen, aus der hervorgeht, wieviele Stunden Unterricht die jeweilige Klasse hat. Die Liste soll nach der Anzahl der Stunden sortiert sein.
<code>
# Die Klasse, die am meisten Unterricht hat; mit der Anzahl der Unterrichtsstunden.<br/>''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, COUNT(s.id)
# Eine Liste der Räume, aus der hervorgeht, wie viele Stunden Unterricht in jedem Raum erteilt wird.
FROM klasse k JOIN schueler s
# "Lehrerraumprinzip": Eine Liste der Lehrer, in der für jeden Lehrer vermerkt ist, in welchem Raum er am meisten Stunden unterrichtet.<br/>''Hinweis: Man braucht ein GROUP BY für zwei Spalten: GROUP BY l.id, r.id''
ON k.id = s.klasse_id
'''GROUP BY''' k.id
</code>
* Eine Liste der Klassen, aus der hervorgeht, wieviele Stunden Unterricht die jeweilige Klasse hat. Die Liste soll nach der Anzahl der Stunden sortiert sein.
<code>
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
</code>
* Die Klasse, die am meisten Unterricht hat; mit der Anzahl der Unterrichtsstunden.<br/>''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.''
<code>
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'''
</code>
* Eine Liste der Räume, aus der hervorgeht, wie viele Stunden Unterricht in jedem Raum erteilt wird.
<code>
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
</code>
* "Lehrerraumprinzip": Eine Liste der Lehrer, in der für jeden Lehrer vermerkt ist, in welchem Raum er am wie viele Stunden unterrichtet.<br/>''Hinweis: Man braucht ein GROUP BY für zwei Spalten: GROUP BY l.id, r.id''
<code>
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
</code>


==UNION==
==UNION==
# Eine Liste, in der Vor- und Nachnamen von Lehrern und Schülern auftauchen.
* Eine Liste, in der Vor- und Nachnamen von Lehrern und Schülern auftauchen.
# Disziplinarkonferenz für Schüler Schmidt: Eingeladen werden seine Fachlehrer und alle Klassenlehrer.
<code>
# "Die Sportler: " Eine Liste, in der alle Schüler und Lehrer vermerkt werden, die mit Sport etwas zu tun haben.
SELECT s.name, s.vorname
FROM schueler s
'''UNION'''
SELECT l.name, l.vorname
FROM lehrer l
</code>
* Disziplinarkonferenz für Schüler Schmidt: Eingeladen werden seine Fachlehrer und alle Klassenlehrer.
<code>
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'
</code>
* "Die Sportler: " Eine Liste, in der alle Schüler und Lehrer vermerkt werden, die mit Sport etwas zu tun haben.
<code>TODO</code>


==IN, NOT IN==
==IN, NOT IN==
# Welche Klassen haben keinen Sportunterricht?
* Welche Klassen haben keinen Sportunterricht?
# In welchen Räumen findet nie Sport statt?  
<code>
# Welche Schüler sind Klassenkameraden von Anne Ebert?
SELECT k.name
# Welche Lehrer sind Fachkollegen von Josef Zimmermann? (Es reicht, dass sie ein gemeinsames Fach haben.)
FROM klasse k
WHERE k.id '''NOT IN'''
(
  SELECT u.klasse_id
  FROM unterricht u
  WHERE u.fach = 'Sport'
)
</code>
* In welchen Räumen findet nie Sport statt?
<code>
SELECT r.nummer
FROM raum r
WHERE r.id '''NOT IN'''
(
  SELECT u.raum_id
  FROM unterricht u
  WHERE u.fach = 'Sport'
)
</code>
* Welche Schüler sind Klassenkameraden von Anne Ebert?
<code>
SELECT s.name, s.vorname
FROM schueler s
WHERE s.klasse_id '''IN'''
(
  SELECT s2.klasse_id
  FROM schueler s2
  WHERE s2.name = 'Ebert'
)
</code>
* Welche Lehrer sind Fachkollegen von Josef Zimmermann? (Es reicht, dass sie ein gemeinsames Fach haben.)
<code>
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'
)
</code>


=LEFT JOIN, RIGHT JOIN=
=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: [[SQL#Left-Join|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: '''[[SQL#Left-Join|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.
* 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.
# Eine Liste ALLER Klassen, jeweils mit Klassenlehrer.


<code>
SELECT s.name, s.vorname, k.name
FROM schueler s
'''LEFT JOIN''' klasse k
ON s.klasse_id = k.id
</code>
* Eine Liste ALLER Klassen, jeweils mit Klassenlehrer.
<code> TODO</code>
==LEFT JOIN, RIGHT JOIN und UNION==
==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.
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:
Dann muss man zwei SQL-Abfragen mit UNION kombinieren:<br/>
* eine Abfrage über mehrere Tabellen
- eine Abfrage über mehrere Tabellen<br/>
* einen LEFT JOIN für die Datensätze, denen die Entsprechung fehlt.
 
-einen LEFT JOIN für die Datensätze, denen die Entsprechung fehlt.<br/>
*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.


# 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.
<code>
# 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.
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'''
</code>
* 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.
<code> TODO</code>


==IS NULL==
==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.
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.
* Eine Liste der Schüler, die keine Klasse haben.
# Eine Liste der Lehrer, die nicht unterrichten.
<code>
# Eine Liste der Räume, in denen kein Unterricht stattfindet.
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'''
</code>
* Eine Liste der Lehrer, die nicht unterrichten.
<code> TODO</code>
* Eine Liste der Räume, in denen kein Unterricht stattfindet.
<code> TODO</code>


=Abfragen mit mehreren Variablen für eine Tabelle=
=Abfragen mit mehreren Variablen für eine Tabelle=
# Welche Schüler sind in der Klasse von Anne Ebert?
* Welche Schüler sind in der Klasse von Anne Ebert?<br/>''Es gibt zwei Schüler: Anne Ebert (s1) und der "unbekannte Schüler" aus ihrer Klasse (s2).''
# Welche Fachlehrer unterrichten in der Klasse von Lehrer Buttenmüller?
<code>
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
</code>
* Welche Fachlehrer unterrichten in der Klasse von Lehrer Buttenmüller?<br/>''Es gibt zwei Lehrer: Den Lehrer Buttenmüller (l1) und den "unbekannten Fachlehrer" seiner Klasse (l2).
<code>
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'
</code>


=SQL-Abfragen über selbstdefinierte Tabellen=
=SQL-Abfragen über selbstdefinierte Tabellen=
Für die folgenden Fragen braucht man [[SQL#SQL-Abfragen über selbstdefinierte Tabellen|SQL-Abfragen über selbstdefinierte Tabellen]].
Für die folgenden Fragen braucht man [[SQL#SQL-Abfragen über selbstdefinierte Tabellen|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)
* 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)<br/>''Es gibt eine innere Abfrage für Deutsch und eine für Geschichte.''
# Eine Liste aller Klassen, in der angezeigt wird, wieviel Prozent Sport sie haben.
<code>
# 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.<br/>''Innere Abfrage: ein LEFT JOIN über die Tabellen schueler und teilnahme''
SELECT deutsch.klasse AS klasse, deutsch.stunden AS deutsch, geschichte.stunden AS geschichte
# 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)''
FROM
# Wieviele Stunden Unterricht haben die Klassen durchschnittlich?
(
# Die "Streber-Klassen": Welche Klassen haben überdurchschnittlich viele Stunden Unterricht?
  SELECT k.name '''AS klasse''', SUM(u.stunden) '''AS stunden'''
# Das Fach, das am meisten unterrichtet wird, mit Anzahl der Stunden.<br>''Zur Kontrolle: Deutsch.''
  FROM klasse k JOIN unterricht u
# 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. <br> ''Zur Kontrolle: Die meisten Stunden hat Schwarzmüller mit 13 Stunden''
  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'''
</code>
* Eine Liste aller Klassen, in der angezeigt wird, wieviel Prozent Sport sie haben.
<code>TODO</code>
* 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.<br/>''Innere Abfrage: ein LEFT JOIN über die Tabellen schueler und teilnahme''
<code>TODO</code>
* 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)''
<code>TODO</code>
* Wieviele Stunden Unterricht haben die Klassen durchschnittlich?<br/>''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.''
<code>
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'''</code>
* Die "Streber-Klassen": Welche Klassen haben überdurchschnittlich viele Stunden Unterricht?
<code>TODO</code>
* Das Fach, das am meisten unterrichtet wird, mit Anzahl der Stunden.<br>''Zur Kontrolle: Deutsch.''
<code>TODO</code>
* 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. <br> ''Zur Kontrolle: Die meisten Stunden hat Schwarzmüller mit 13 Stunden''
<code>TODO</code>


=Weitere schwierige Abfragen=
=Weitere schwierige Abfragen=
Für die folgenden Fragen muss man geeignete SQL-Sprachelemente kombinieren.
Für die folgenden Fragen muss man geeignete SQL-Sprachelemente kombinieren.
# Raum R112 sieht immer furchtbar aus. Der Direktor möchte deswegen mit den Klassenlehrern von allen Klassen sprechen, die in R112 Unterricht haben.
# Raum R112 sieht immer furchtbar aus. Der Direktor möchte deswegen mit den Klassenlehrern von allen Klassen sprechen, die in R112 Unterricht haben.<br/>''JOIN über die Tabellen raum, unterricht, klasse, lehrer''
# Wieviel Prozent der insgesamt erteilten Unterrichtsstunden sind Sportstunden?
# Wieviel Prozent der insgesamt erteilten Unterrichtsstunden sind Sportstunden?<br/>''zwei innere Abfragen: eine für die Gesamtzahl der Stunden, eine für den Sportunterricht.''
# Welcher Lehrer unterrichtet am meisten? (Mit Angabe der Stundenzahl.)
# Welcher Lehrer unterrichtet am meisten? (Mit Angabe der Stundenzahl.)<br/>''GROUP BY , ORDER BY anzahl DESC und LIMIT 1''
# 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).
# 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).<br/>''Die "Drückeberger" muss man mit UNION anfügen. Für die Drückeberger braucht man LEFT JOIN und IS NULL.''
# Eine Liste der Etagen mit den durchschnittlichen Raumgrößen. Die Liste soll nach den Durchschnittswerten sortiert werden.
# Eine Liste der Etagen mit den durchschnittlichen Raumgrößen. Die Liste soll nach den Durchschnittswerten sortiert werden.<br/>''AVG, GROUP BY, ORDER BY durchschnitt DESC
# Eine Liste der Schüler, die nie in Raum R112 Unterricht haben.
# Eine Liste der Schüler, die nie in Raum R112 Unterricht haben.<br/>''NOT IN''
# Eine Liste aller Schüler und Lehrer, die in Raum R112 Unterricht haben / unterrichten.
# Eine Liste aller Schüler und Lehrer, die in Raum R112 Unterricht haben / unterrichten.<br/>''UNION''
# In welchem Raum finden die meisten Unterrichtsstunden statt?
# In welchem Raum finden die meisten Unterrichtsstunden statt?<br/>''GROUP BY, ORDER BY anzahl DESC, LIMIT 1''
# 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.
# 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.<br/>''Zwei Räume: r1 ist Raum R112, r2 ist ein Raum, in dem weniger Unterricht ist.<br/>Die Räume, in denen gar kein Unterricht stattfindet muss man mit UNION anhängen.''
# 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.
# 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.<br/>''GROUP BY über zwei Attribute''
 
<code>
=Data-Mining: Filmempfehlungen=
SELECT u.fach, r.etage, SUM(u.stunden)
''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. ''
FROM unterricht u JOIN raum r
 
ON u.raum_id = r.id
Jetzt soll Data-Mining für die Filmdatenbank betrieben werden mit dem Ziel, sinnvolle Filmempfehlungen auszusprechen.
'''GROUP BY u.fach, r.etage'''
ORDER BY u.fach, r.etage
</code>


# 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. <br> <u>Wichtiger Hinweis:</u> 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!<br> ''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'
## Dasselbe für Grace Gertz!
# 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. <br>
''Zur Kontrolle: Der ähnlichste Kunde ist Nikodemus Schönnagel''
=Gemischte Aufgaben (schwierig bis sehr schwierig)=
=Gemischte Aufgaben (schwierig bis sehr schwierig)=


Eine Liste der Klassen, die in Raum R112 Unterricht haben.<br/>''Ergebnis: 8A, 8C.''
Eine Liste der Klassen, die in Raum R112 Unterricht haben.<br/>''Ergebnis: 8A, 8C.''
<code>
<code>
  ''JOIN über 3 Tabellen''
  ''JOIN über 3 Tabellen''
   
   
Zeile 132: Zeile 418:
  ON k.id = u.klasse_id AND u.raum_id = r.id
  ON k.id = u.klasse_id AND u.raum_id = r.id
  WHERE r.nummer = 'R112'
  WHERE r.nummer = 'R112'
</code>
</code>
Eine Liste aller AGs mit Teilnehmerzahl. <br/>''Ergebnis: Z.B. Fotografie 2<br/>''
Eine Liste aller AGs mit Teilnehmerzahl. <br/>''Ergebnis: Z.B. Fotografie 2<br/>''
<code>
  ''JOIN über 2 Tabellen und GROUP BY''
  ''JOIN über 2 Tabellen und GROUP BY''
   
  <code>
  SELECT a.name, COUNT(t.schueler_id) AS anzahl
  SELECT a.name, COUNT(t.schueler_id) AS anzahl
  FROM ag a JOIN teilnahme t
  FROM ag a JOIN teilnahme t
  ON a.id = t.ag_id
  ON a.id = t.ag_id
  GROUP BY a.id
  GROUP BY a.id
</code>
</code>
Eine Liste der Räume, in denen nie Deutsch stattfindet.<br/>''Ergebnis: R110, R306, Sporthalle (=wenig erstaunlich...), R208, R101''
Eine Liste der Räume, in denen nie Deutsch stattfindet.<br/>''Ergebnis: R110, R306, Sporthalle (=wenig erstaunlich...), R208, R101''
<code>
   ''Differenz''
   ''Differenz''
    
   <code>
   SELECT r.nummer
   SELECT r.nummer
   FROM raum r
   FROM raum r
Zeile 153: Zeile 439:
     WHERE u.fach = 'Deutsch'
     WHERE u.fach = 'Deutsch'
   )
   )
</code>
</code>
Eine Liste der Fachkollegen von Josef Zimmermann.<br/>
Eine Liste der Fachkollegen von Josef Zimmermann.<br/>
''Ergebnis: Erika Huber und Josef Zimmermann selber.''
''Ergebnis: Erika Huber und Josef Zimmermann selber.''
   
   
<code>
  ''Zwei Variablen für eine Entitätsmenge:''<br/>
  ''Zwei Variablen für eine Entitätsmenge:''
  ''l1 = der gesuchte Lehrer; u1 = sein Unterricht''<br/>
  ''l1 = der gesuchte Lehrer; u1 = sein Unterricht''
  ''l2 = Lehrer Zimmermann; u2 = sein Unterricht''
  ''l2 = Lehrer Zimmermann; u2 = sein Unterricht''
   
  <code>
  SELECT DISTINCT l1.name
  SELECT DISTINCT l1.name
  FROM lehrer l1
  FROM lehrer l1
Zeile 171: Zeile 456:
  AND u2.lehrer_id = l2.id
  AND u2.lehrer_id = l2.id
  WHERE l2.name = 'Zimmermann'
  WHERE l2.name = 'Zimmermann'
</code>
</code>


Eine Liste aller Schüler, in der aufgeführt wird, an welchen AGs sie teilnehmen. <br/>
Eine Liste aller Schüler, in der aufgeführt wird, an welchen AGs sie teilnehmen. <br/>
Wenn ein Schüler an mehreren AGs teilnimmt, dann soll er mehrfach aufgeführt werden. <br/>
Wenn ein Schüler an mehreren AGs teilnimmt, dann soll er mehrfach aufgeführt werden. <br/>
Zeile 180: Zeile 464:
''Die Schüler Zimmermann und Wiesenhoff tauchen in der Liste nicht auf.''
''Die Schüler Zimmermann und Wiesenhoff tauchen in der Liste nicht auf.''


<code>
  ''2x JOIN''
  ''2x JOIN''
   
  <code>
  SELECT s.name, a.name
  SELECT s.name, a.name
  FROM schueler s JOIN teilnahme t JOIN ag a
  FROM schueler s JOIN teilnahme t JOIN ag a
  ON s.id = t.schueler_id AND t.ag_id = a.id
  ON s.id = t.schueler_id AND t.ag_id = a.id
</code>
</code>
Wie die vorhergehende Abfrage, aber: Auch Schüler, die an keiner AG teilnehmen, sollen aufgeführt werden.<br/>
Wie die vorhergehende Abfrage, aber: Auch Schüler, die an keiner AG teilnehmen, sollen aufgeführt werden.<br/>
''Ergebnis: Jetzt sollen die Schüler Zimmermann und Wiesenhoff auftauchen.''
''Ergebnis: Jetzt sollen die Schüler Zimmermann und Wiesenhoff auftauchen.''


<code>
  ''1x LEFT JOIN, dann JOIN''
  ''1x LEFT JOIN, dann JOIN''
   
   
<code>
  SELECT s.name, a.name
  SELECT s.name, a.name
  FROM schueler s LEFT JOIN (teilnahme t JOIN ag a)
  FROM schueler s LEFT JOIN (teilnahme t JOIN ag a)
  ON s.id = t.schueler_id AND t.ag_id = a.id
  ON s.id = t.schueler_id AND t.ag_id = a.id
</code>
</code>
Es soll eine Liste erstellt werden, aus der hervorgeht, wie viele Stunden jeder Lehrer arbeitet! <br/>
Es soll eine Liste erstellt werden, aus der hervorgeht, wie viele Stunden jeder Lehrer arbeitet! <br/>
Dabei zählen die Unterrichtsstunden normal und jede AG, die ein Lehrer leitet, zählt 1 Stunde.<br/>
Dabei zählen die Unterrichtsstunden normal und jede AG, die ein Lehrer leitet, zählt 1 Stunde.<br/>
''Ergebnis: Z.B. für den Lehrer Zimmermann: 1AG + 4Deutsch + 4Deutsch + 2Geschichte = 11''
''Ergebnis: Z.B. für den Lehrer Zimmermann: 1AG + 4Deutsch + 4Deutsch + 2Geschichte = 11''
<code>


  ''2 innere Abfragen.''
  ''2 innere Abfragen.''
  ''Es werden hier nur die Lehrer aufgeführt, die sowohl unterrichten als auch AGs haben.''
  ''Es werden hier nur die Lehrer aufgeführt, die sowohl unterrichten als auch AGs haben.''
   
   
<code>
  SELECT ustunden.name AS name, ustunden.anzahl + agstunden.anzahl AS anzahl
  SELECT ustunden.name AS name, ustunden.anzahl + agstunden.anzahl AS anzahl
  FROM
  FROM
Zeile 220: Zeile 502:
  ) AS agstunden
  ) AS agstunden
  WHERE ustunden.name = agstunden.name
  WHERE ustunden.name = agstunden.name
</code>
</code>


   
   
Zeile 228: Zeile 510:
''Ergebnis: Z.B. R112: 4 Deutsch, 4 Englisch''
''Ergebnis: Z.B. R112: 4 Deutsch, 4 Englisch''


<code>
  ''2 innere Abfragen''
  ''2 innere Abfragen''
   
   
  SELECT x.name, x.anzahl + y.stunden
<code>
  SELECT deutsch.raum, deutsch.stunden, englisch.stunden
  FROM
  FROM
  ( SELECT l.name AS name, COUNT(a.id) AS anzahl
  (
  FROM lehrer l LEFT join ag a
  SELECT r.nummer AS raum, SUM(u.stunden) AS stunden
  ON l.id = a.lehrer_id
  FROM raum r JOIN unterricht u
  GROUP BY l.id
  ON r.id = u.raum_id
  ) AS x
  WHERE u.fach = 'deutsch'
,
  UNION
  ( SELECT l.name AS name, SUM(u.stunden) AS stunden
  SELECT r.nummer AS raum, 0 AS stunden
  FROM lehrer l LEFT join unterricht u
  FROM raum r
  ON l.id = u.lehrer_id
  WHERE r.id NOT IN
  GROUP BY l.id
  (
  ) AS y
    SELECT u.raum_id FROM unterricht u WHERE u.fach = 'deutsch'
  WHERE x.name = y.name
  )
</code>
  ) '''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'''
</code>

Aktuelle Version vom 23. Januar 2024, 17:38 Uhr


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.

Beispieldatenbank-schule.jpg

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