Lösungsvorschlag

Informatik 9, Datenbanken
Abfragen auf mehreren Tabellen (Lösung)
Beantworte die folgenden Aufgaben mit Hilfe von SQL-Abfragen:
1. Liste alle Gitarrenspieler auf.
2.
Nenne alle Mitglieder der Band „Doktoren“.
3.
Nenne alle Stücke der CD „Spain“.
4.
Welche Lieder dauern länger als 4 Minuten?
Kreuzprodukt und Join
Beispiel: Liste alle CD-Titel mit dem jeweiligen Stil der Band auf.
Lösungsversuch:
SELECT CDTitel, Stil
FROM cd, band;
Überprüfe die
Ergebnistabelle.
Markiere die
passenden Zeilen.
cd
CDTitel
Der Blinddarm
Hell out of it
Mountains
When the Devils ride
Bandname
Doktoren
Devils
Tears
Devils
band
Bandname Stil
Devils
Pop
Doktoren Punk
Tears
Gothic
CDTitel
Der Blinddarm
Der Blinddarm
Der Blinddarm
Hell out of it
Hell out of it
Hell out of it
Mountains
Mountains
Mountains
When the Devils ride
When the Devils ride
When the Devils ride
Stil
Pop
Punk
Gothic
Pop
Punk
Gothic
Pop
Punk
Gothic
Pop
Punk
Gothic
Bei der Verwendung mehrerer Tabellen in einer Abfrage erhält man zunächst alle möglichen
(auch unbeabsichtigten) Kombinationen von Datensätzen aller beteiligten Tabellen
(Kreuzprodukt).
Richtige Lösung:
SELECT CDTitel, Stil
FROM cd, band
WHERE cd.Bandname = band.Bandname;
Joinbedingung: Sie verknüpft durch den Vergleich des
Fremdschlüssels mit dem zugehörigen Primärschlüssel die
CDTitel
Der Blinddarm
Hell out of it
Mountains
When the devils ride
zusammengehörigen Datensätze der beiden Tabellen.
Allgemein bezeichnet man die Verknüpfung zweier Tabellen als Join.
Stil
Punk
Pop
Gothic
Pop
Informatik 9, Datenbanken
Abfragen auf mehreren Tabellen: SQL (Lösung für MSAccess)
1.
2.
3.
4.
5.
6.
7.
8.
9.
Liste alle Musiker aus Pop-Bands auf.
SELECT Vorname, Nachname
FROM person, band
WHERE person.Bandname = band.Bandname
AND Stil = ’Pop’;
In welchen Bands spielen Musiker, die
SELECT DISTINCT Bandname
vor 1970 geboren wurden?
FROM person
WHERE Geburtsjahr < 1970;
Welchen Stil (keine Mehrfachnennungen) SELECT DISTINCT Stil
haben die Bands, in denen Musiker
FROM person, band
spielen, die vor 1970 geboren wurden?
WHERE band.Bandname = person.Bandname
AND Geburtsjahr < 1970;
Liste alle Lieder auf, die von Rock-Bands SELECT LiedTitel
gespielt werden.
FROM band, cd, lied
WHERE band.Bandname = cd.Bandname
AND cd.CDTitel = lied.CDTitel
AND Stil = ’Rock’;
Erzeuge eine Liste aller Daten, so wie du SELECT Vorname, Nachname, Geburtsjahr,
sie als Papierkopie der redundanten
Instrument, band.Bandname, Stil, cd.CDTitel,
Tabelle erhalten hast. Sortiere sie
LiedTitel, Länge
alphabetisch nach Bandname und
FROM person, band, cd, lied
CDTitel.
WHERE person.Bandname = band.Bandname
AND person.Bandname = cd.Bandname
AND cd.CDTitel = lied.CDTitel
ORDER BY band.Bandname, cd.CDTitel;
Auf welchen CDs wirkt Katja Biller mit? SELECT CDTitel
FROM person, cd
WHERE person.Bandname = cd.Bandname
AND Vorname = ’Katja’
AND Nachname = ’Biller’;
Welche Musiker (Vor- und Nachname)
SELECT Vorname, Nachname
spielen auf der CD „Spain“?
FROM person, cd
WHERE person.Bandname = cd.Bandname
AND CDTitel = ’Spain’;
Wer singt in dem Lied mit dem Titel
SELECT Vorname, Nachname
„Hot Temptation“?
FROM person, cd, lied
WHERE person.Bandname = cd.Bandname
AND cd.CDTitel = lied.CDTitel
AND Instrument = ’Stimme’
AND LiedTitel = ’Hot Temptation’;
Welche Lieder, bei denen Jill Hutu
SELECT LiedTitel
mitwirkt, dauern länger als 3:30
FROM person, cd,lied
Minuten?
WHERE person.Bandname = cd.Bandname
AND cd.CDTitel = lied.CDTitel
AND Vorname = ’Jill’
AND Nachname = ’Hutu’
AND Länge > #0:3:30#;
Informatik 9, Datenbanken
10. Berechne die Mitgliederzahlen aller
Bands mit Hilfe der COUNT-Funktion.
11. Berechne die Spieldauer jeder erfassten
CD.
12. Wie lange dauert das längste Lied der
Band „Katzen“?
13. Wie heißt das längste Lied der Band
„Katzen“?
SELECT Bandname, Count(*) AS
Mitgliederzahl
FROM person
GROUP BY Bandname;
SELECT CDTitel, SUM(Länge)*24*60 AS
Spieldauer
FROM lied
GROUP BY CDTitel;
Hinweis: Die Spieldauer wird zunächst in
Bruchteilen eines Tages berechnet. Die
Multiplikation mit 24*60 liefert die Zeit in
Minuten.
SELECT MAX(Länge)
FROM lied, cd
WHERE cd.CDTitel = lied.CDTitel
AND Bandname = ’Katzen’;
Hinweis: Das Ergebnis 00:04:00 kann dann
für die nächste Aufgabe verwendet werden.
SELECT LiedTitel
FROM lied, cd
WHERE cd.CDTitel = lied.CDTitel
AND Bandname = ’Katzen’
AND Länge = #00:04:00#;
Geschachtelte Abfrage als Alternative für
fortgeschrittene Schüler:
SELECT LiedTitel
FROM lied, cd
WHERE cd.CDTitel = lied.CDTitel
AND Bandname = ’Katzen’
AND Länge = (
SELECT MAX(Länge)
FROM lied, cd
WHERE cd.CDTitel = lied.CDTitel
AND Bandname = ’Katzen’
);