Datenbankentwicklung Teil 9_1

Datenbankentwicklung
IV-LK
1.
2.
3.
4.
5.
6.
7.
8.
9.
Warum Datenbanken?
Anforderungsanalyse für Datenbanken
Ableitung von Tabellenstrukturen
Normalisierung
Relationenalgebra
Formalisierung von Tabellen in SQL
SQL- Anfragen
Gruppierungen in SQL
Verschachtelte Anfragen in SQL
9. Verschachtelte Anfragen in SQL
IV-LK
1.
2.
3.
4.
5.
6.
Nutzung von Mengen- Operationen
Teilanfragen in der SELECT-Zeile
Teilanfragen in der WHERE-Bedingung
Teilanfragen in der HAVING-Zeile
Teilanfragen in der FROM-Zeile
Aufgaben
9. Verschachtelte Anfragen in SQL – 1.Nutzung von MengenOperatoren
IV-LK
Als Beispiel dient weiterhin die Datenbank Zoo
Gehege
Art
Tier
9. Verschachtelte Anfragen in SQL – 1.Nutzung von MengenOperatoren
IV-LK
Vereinigung: Es werden die Elemente von zwei Mengen zusammen gefasst.
Durchschnitt: Menge von Elementen, die in beiden Mengen vorkommen.
Differenz: Menge von Elementen, die in der ersten Menge, nicht aber in der
zweiten Menge vorkommen.
In SQL ist es auch möglich, dass in einer Menge Werte doppelt vorkommen.

Es gibt jeden Mengenoperator in zwei Ausführungen. In der ersten
Ausführung wird jede Zeile im Ergebnis nur einmal vorkommen, in der
zweiten Ausführung werden die Anzahlen der vorhandenen Elemente
berücksichtigt.
9. Verschachtelte Anfragen in SQL – 1.Nutzung von MengenOperatoren
IV-LK
Vereinigung durch UNION
Frage: Man möchte alle Gattungen bestimmen, die in den Gehegen mit den
Nummern 2 und 3 leben. => Abfrage 13
SELECT Tier.Gattung
FROM Tier
WHERE Tier.Gnr =2
UNION
SELECT Tier.Gattung
FROM Tier
WHERE Tier.Gnr = 3
Alternative Berechnung mit OR => Abfrage 14
SELECT DISTINCT Tier.Gattung
FROM Tier
WHERE Tier.Gnr = 2
OR Tier.Gnr =3
9. Verschachtelte Anfragen in SQL – 1.Nutzung von MengenOperatoren
IV-LK
Randbedingungen:
1.
Die resultierenden Tabellen der Teilanfragen müssen im Ergebnis die
gleiche Spaltenanzahl haben.
2.
Die Spalten müssen vom Typ zusammen passen.
3.
Die Einträge müssen mit den Datentypen zusammen passen.
Mit UNION ALL werden doppelte Elemente nicht gelöscht. => Abfrage 15
SELECT Tier.Gattung
FROM Tier
WHERE Tier.Gnr =2
UNION ALL
SELECT Tier.Gattung
FROM Tier
WHERE Tier.Gnr = 3
9. Verschachtelte Anfragen in SQL – 1.Nutzung von MengenOperatoren
IV-LK
Durchschnitt – INTERSECT
(lässt sich nicht in Access verwenden)
Frage: Welche Gattungen kommen in beiden Gehegen vor?
SELECT Tier.Gattung
FROM Tier
WHERE Tier.Gnr=2
INTERSECT
SELECT Tier.Gattung
FROM Tier
WHERE Tier.Gnr =3
Mengendifferenz – EXCEPT (MINUS)
Frage: Welche Gattungen kommen im Gehege mit der Nummer 2, nicht aber im Gehege
mit der Nummer 3 vor?
SELECT Tier.Gattung
FROM Tier
WHERE Tier.Gnr=2
MINUS
SELECT Tier.Gattung
FROM Tier
WHERE Tier.Gnr =3
9. Verschachtelte Anfragen in SQL – 1.Nutzung von MengenOperatoren
IV-LK
Vermeidung von NULL Werten in Ausgaben:
PNr
Name
Status
1
Anton
Junior
2
Berti
3
Conni
4
Det
Senior
In einer Ausgabe sollen die NULL Werte durch unbekannt ersetzt werden:
Dazu werden zunächst die Personen ausgegeben, deren Status bekannt
ist, und diese Ausgabe dann mit einem Ergebnis einer Anfrage vereinigt,
in der nur Personen betrachtet werden, deren Status-Wert NULL ist.
SELECT *
FROM Personal
WHERE Personal.Status Is NOT NULL
UNION
SELECT Personal.Pnr, Personal.Name, 'unbekannt'
FROM Personal
WHERE Personal.Status IS NULL
9. Verschachtelte Anfragen in SQL – 2.Teilanfragen in der SELECTZeile
IV-LK
Frage: Zusätzlich zu den Spalten der Tabelle Gehege soll noch die insgesamt
von den Gehegen verbrauchte Fläche angezeigt werden. =>Abfrage 17
SELECT Gehege.*,
(SELECT SUM(Gehege.Flaeche)
FROM Gehege) AS Gesamtflaeche
FROM Gehege
Grundsätzlich kann man davon ausgehen, dass zuerst die innere Anfrage
ausgewertet wird und im nächsten Schritt die Auswertung der
umgebenden Anfrage geschieht.
9. Verschachtelte Anfragen in SQL – 2.Teilanfragen in der SELECTZeile
IV-LK
Statt einer einfachen Ausgabe in einer zusätzlichen Spalte, können die
Ergebnisse einer Anfrage in einer SELECT-Zeile auch zur Berechnung
genutzt werden. Dabei kann der berechnete Wert, wie andere Werte
auch, mit anderen Attributwerten verknüpft werden.
Frage: Gib den Anteil jedes Geheges an der Gesamtfläche aus. =>Abfrage 18
SELECT Gehege.GName, Gehege.Flaeche*100/(SELECT SUM(Gehege.Flaeche)
FROM Gehege) As Anteil
FROM Gehege
9. Verschachtelte Anfragen in SQL – 3.Teilanfragen in der WHEREBedingung
IV-LK
Bisher war es nicht möglich, zu der Größe des größten Geheges auch den
Namen auszugeben. Diese Möglichkeit wird durch folgende Anfrage
geschaffen: => Abfrage 19
SELECT Gehege.GName, Gehege.Flaeche
FROM Gehege
WHERE Gehege.Flaeche = (SELECT MAX(Gehege.Flaeche) FROM Gehege)
Die Minimum und Maximum Berechnung ist auch für Texte möglich. Das Tier
mit dem alphabetisch zuerst vorkommenden Namen, zusammen mit
seiner Gattung, kann folgendermaßen gefunden werden. => Abfrage 20
SELECT Tier.Tname, Tier.Gattung
FROM Tier
WHERE Tier.Tname=(SELECT MIN (Tier.Tname) FROM Tier)
9. Verschachtelte Anfragen in SQL – 3.Teilanfragen in der SELECTZeile
IV-LK
Frage: Gib alle Gattungen aus, die mindestens so viel Fläche verbrauchen, wie
der Durchschnitt aller Gattungen. => Abfrage 21
SELECT Art.Gattung
FROM Art
WHERE Art.MinFlaeche>=(SELECT AVG(Art.MinFlaeche) FROM Art)
Frage: Gib die Namen aller Tiere aus, deren Gattungen im Gehege Feld
vorkommen. => Abfrage 22
SELECT Tier.TName
FROM Tier
WHERE Tier.Gattung IN
(SELECT Tier.Gattung
FROM Gehege, Tier
WHERE Gehege.Gnr = Tier.Gnr
AND Gehege.Gname ='Feld')
9. Verschachtelte Anfragen in SQL – 1.Nutzung von MengenOperatoren
IV-LK
Frage: Gib die Namen der Gehege aus, in denen ein Hase vorkommt.
SELECT Gehege.Gname
FROM Gehege
WHERE 'Hase' IN (SELECT Tier.Gattung
FROM Tier
WHERE Gehege.Gnr=Tier.Gnr)
In der äußeren Anfrage wird das Gehege betrachtet. Für jedes dieser Gehege
wird in der WHERE Bedingung untersucht, ob sich dort ein Hase
befindet. Deshalb muss in der inneren Bedingung auf das Gehege Bezug
genommen werden. Lokale Umbenennungen müssen auch in der
inneren Anfrage berücksichtigt werden.
In diesem Fall wäre man auch ohne den IN-Operator ausgekommen.
SELECT DISTINCT Gehege.Gname
FROM Gehege, Tier
WHERE Gehege.Gnr=Tier.Gnr
AND Tier.Gattung = 'Hase'
Eine weitere Möglichkeit zur Auswahl in der WHERE-Bedingung bietet der
EXISTS-Operator
9. Verschachtelte Anfragen in SQL – 4.Teilanfragen in der HAVINGBedingung
IV-LK
Mit der HAVING-Bedingung werden Gruppen für das Ergebnis der Anfrage
ausgewählt, mit der WHERE-Bedingung einzelne Zeilen. Die
Bedingungen für die Teilanfragen in der WHERE-Bedingung können also
auf die HAVING-Bedingung übertragen werden.
Frage: Es sollen die Gehege gesucht werden, deren Tiere zusammen auch im
kleinsten Gehege leben könnten. Z.B. passen die beiden Bären im
Gehege Wald nicht in das kleinste Gehege.
SELECT Gehege.Gname
FROM Gehege, Tier, Art
WHERE Gehege.Gnr = Tier.Gnr
AND Tier.Gattung=Art.Gattung
GROUP BY Gehege.Gname
HAVING SUM (Art.Minflaeche)<=(SELECT MIN(Gehege.Flaeche)
FROM Gehege)
In Teilanfragen dürfen entweder nur vollständig neue Berechnungen stehen,
oder Aggregatsfunktionen für Berechnungen in den jeweiligen Gruppen
und Attributen, die in der GROUP-BY Zeile genannt wurden.
9. Verschachtelte Anfragen in SQL – 5.Teilanfragen in der FROM-Zeile
IV-LK
Damit können komplexe Aufgabenstellungen in einfachere Teilanfragen zerlegt werden.
Diese Teilanfragen liefern Tabellen als Ergebnisse und werden dann in der
FROM-Zeile als benötigte Tabellen angegeben und zur Lösung der
Gesamtaufgabe genutzt.
Frage: Wie viel Fläche wird in jedem Gehege von Hasen verbraucht?
1.
Wie viel Hasen gibt es pro Gehege? => Abfrage 25
SELECT Gehege.Gname As Gehegename,
COUNT (*) As Hasenanzahl
FROM Gehege, Tier
WHERE Gehege.Gnr=Tier.Gnr
AND Tier.Gattung='Hase'
GROUP BY Gehege.Gname
2. Anzahl der Hasen wird mit der benötigten Fläche multipliziert. => Abfrage 26
SELECT Hasentabelle.Gehegename,
Hasentabelle.Hasenanzahl*Art.MinFlaeche As Hasenflaechenverbrauch
FROM Art,
(SELECT Gehege.Gname AS Gehegename, COUNT(*) AS Hasenanzahl
FROM Gehege,Tier
WHERE Gehege.Gnr=Tier.Gnr
AND Tier.Gattung ='Hase'
GROUP BY Gehege.Gname) As Hasentabelle
WHERE Art.Gattung ='Hase'
9. Verschachtelte Anfragen in SQL – 5.Teilanfragen in der FROM-Zeile
IV-LK
Frage: Welcher Anteil hat jedes Tier an der insgesamt für Gehege zur
Verfügung stehenden Fläche? => Abfrage 27
SELECT Gehege.Gname, Tier.Tname, Art.Minflaeche/Alle.Gesamtflaeche*100 AS Gehegeanteil
FROM Gehege, Tier, Art,
(SELECT SUM(Gehege.Flaeche) AS Gesamtflaeche
FROM Gehege) AS Alle
WHERE Gehege.Gnr=Tier.Gnr
AND Tier.Gattung=Art.Gattung
Frage: Wie viel Fläche ist in den einzelnen Gehegen noch frei? => Abfrage 28
SELECT Gehege.Gname,
Gehege.Flaeche - Belegung.Verbraucht AS Frei
FROM Gehege,
(SELECT Gehege.Gname, SUM(Art.Minflaeche) AS Verbraucht
FROM Gehege, Tier, Art
WHERE Gehege.Gnr=Tier.Gnr
AND Tier.Gattung = Art.Gattung
GROUP BY Gehege.Gname) AS Belegung
WHERE Gehege.Gname=Belegung.Gname
=Abfrage 29
9. Verschachtelte Anfragen in SQL- 6. Aufgaben
IV-LK
Gegeben sind folgende Tabellen zur Beschreibung, welche Filme in welchen
Kinos laufen. Im Gloria gibt es beispielsweise 3 Säle mit jeweils 20 Plätzen.
Film
Vorfuehrung
Kino
FID
Titel
Laenge
Film
Kino
1
Die Nase
90
Name
Plätze
Saele
1
Gloria
2
Die Hand
85
Gloria
200
3
2
Gloria
3
Der Arm
120
Apollo
300
2
3
Gloria
4
Das Bein
75
2
Apollo
4
Apollo
9. Verschachtelte Anfragen in SQL
IV-LK
Formuliere folgende SQL- Anfragen:
1.
Gib die Namen aller Kinos aus, in denen der Film „Die Hand“ läuft.
2.
Gib zu jedem Kino die Gesamtzahl aller zur Verfügung stehenden Plätze
aus (Ausgabe: Kinoname, Gesamtplatzzahl)
3.
Gib die Titel der Filme aus, die in mindestens zwei Kinos laufen.
4.
Gib zu jedem Kino die Länge des längsten Films aus, der in diesem Kino
läuft.
5.
Gib zu jedem Filmtitel die maximale Anzahl von Zuschauern aus, die den
Film gleichzeitig sehen können. Es ist davon auszugehen, dass alle
Vorführungen gleichzeitig und nur einmal am Tag stattfinden.
6.
Gib für jeden Film und jedes Kino an, ob dieser Film in diesem Kino läuft
oder nicht (Ausgabe: Filmtitel, Kino, Anzahl). Dabei soll eine Anzahl>0
ausgegeben werden, wenn der Film in dem Kino läuft, und sonst soll
Anzahl= 0 ausgegeben werden.