Datenbankentwicklung

IV-LK

1. Warum Datenbanken?2. Anforderungsanalyse für Datenbanken3. Ableitung von Tabellenstrukturen4. Normalisierung5. Relationenalgebra6. Formalisierung von Tabellen in SQL7. SQL- Anfragen8. Gruppierungen in SQL9. Verschachtelte Anfragen in SQL

9. Verschachtelte Anfragen in SQLIV-LK

1. Nutzung von Mengen- Operationen2. Teilanfragen in der SELECT-Zeile3. Teilanfragen in der WHERE-Bedingung4. Teilanfragen in der HAVING-Zeile5. Teilanfragen in der FROM-Zeile6. Aufgaben

9. Verschachtelte Anfragen in SQL – 1.Nutzung von Mengen-Operatoren

IV-LKAls Beispiel dient weiterhin die Datenbank Zoo

Gehege

Art

Tier

9. Verschachtelte Anfragen in SQL – 1.Nutzung von Mengen-Operatoren

IV-LKVereinigung: 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 Mengen-Operatoren

IV-LKVereinigung durch UNIONFrage: Man möchte alle Gattungen bestimmen, die in den Gehegen mit den

Nummern 2 und 3 leben. => Abfrage 13SELECT Tier.GattungFROM TierWHERE Tier.Gnr =2UNIONSELECT Tier.GattungFROM TierWHERE Tier.Gnr = 3

Alternative Berechnung mit OR => Abfrage 14SELECT DISTINCT Tier.GattungFROM TierWHERE Tier.Gnr = 2OR Tier.Gnr =3

9. Verschachtelte Anfragen in SQL – 1.Nutzung von Mengen-Operatoren

IV-LKRandbedingungen: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 15SELECT Tier.GattungFROM TierWHERE Tier.Gnr =2UNION ALLSELECT Tier.GattungFROM TierWHERE Tier.Gnr = 3

9. Verschachtelte Anfragen in SQL – 1.Nutzung von Mengen-Operatoren

IV-LKDurchschnitt – INTERSECT (lässt sich nicht in Access verwenden)Frage: Welche Gattungen kommen in beiden Gehegen vor?SELECT Tier.GattungFROM TierWHERE Tier.Gnr=2INTERSECTSELECT Tier.GattungFROM TierWHERE Tier.Gnr =3Mengendifferenz – EXCEPT (MINUS)Frage: Welche Gattungen kommen im Gehege mit der Nummer 2, nicht aber im Gehege

mit der Nummer 3 vor?SELECT Tier.GattungFROM TierWHERE Tier.Gnr=2MINUSSELECT Tier.GattungFROM TierWHERE Tier.Gnr =3

9. Verschachtelte Anfragen in SQL – 1.Nutzung von Mengen-Operatoren

IV-LKVermeidung von NULL Werten in Ausgaben:

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 PersonalWHERE Personal.Status Is NOT NULLUNIONSELECT Personal.Pnr, Personal.Name, 'unbekannt'FROM PersonalWHERE Personal.Status IS NULL

PNr Name Status1 Anton Junior

2 Berti

3 Conni

4 Det Senior

9. Verschachtelte Anfragen in SQL – 2.Teilanfragen in der SELECT-Zeile

IV-LKFrage: Zusätzlich zu den Spalten der Tabelle Gehege soll noch die insgesamt

von den Gehegen verbrauchte Fläche angezeigt werden. =>Abfrage 17SELECT Gehege.*,(SELECT SUM(Gehege.Flaeche)FROM Gehege) AS GesamtflaecheFROM 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 SELECT-Zeile

IV-LKStatt 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 AnteilFROM Gehege

9. Verschachtelte Anfragen in SQL – 3.Teilanfragen in der WHERE-Bedingung

IV-LKBisher 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.FlaecheFROM GehegeWHERE 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.GattungFROM TierWHERE Tier.Tname=(SELECT MIN (Tier.Tname) FROM Tier)

9. Verschachtelte Anfragen in SQL – 3.Teilanfragen in der SELECT-Zeile

IV-LKFrage: Gib alle Gattungen aus, die mindestens so viel Fläche verbrauchen, wie

der Durchschnitt aller Gattungen. => Abfrage 21SELECT Art.GattungFROM ArtWHERE 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.TNameFROM TierWHERE 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 Mengen-Operatoren

IV-LKFrage: Gib die Namen der Gehege aus, in denen ein Hase vorkommt.SELECT Gehege.GnameFROM GehegeWHERE '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.GnameFROM 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 HAVING-Bedingung

IV-LKMit 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.GnameFROM Gehege, Tier, ArtWHERE Gehege.Gnr = Tier.Gnr AND Tier.Gattung=Art.GattungGROUP BY Gehege.GnameHAVING 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 25SELECT Gehege.Gname As Gehegename, COUNT (*) As HasenanzahlFROM Gehege, TierWHERE Gehege.Gnr=Tier.GnrAND Tier.Gattung='Hase'GROUP BY Gehege.Gname2. Anzahl der Hasen wird mit der benötigten Fläche multipliziert. => Abfrage 26SELECT Hasentabelle.Gehegename,Hasentabelle.Hasenanzahl*Art.MinFlaeche As HasenflaechenverbrauchFROM 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 HasentabelleWHERE 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 28SELECT Gehege.Gname, Gehege.Flaeche - Belegung.Verbraucht AS FreiFROM Gehege, (SELECT Gehege.Gname, SUM(Art.Minflaeche) AS Verbraucht

=Abfrage 29 FROM Gehege, Tier, Art WHERE Gehege.Gnr=Tier.Gnr AND Tier.Gattung = Art.Gattung GROUP BY Gehege.Gname) AS BelegungWHERE Gehege.Gname=Belegung.Gname

9. Verschachtelte Anfragen in SQL- 6. AufgabenIV-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.

FID Titel Laenge1 Die Nase 90

2 Die Hand 85

3 Der Arm 120

4 Das Bein 75

Film Kino1 Gloria

2 Gloria

3 Gloria

2 Apollo

4 Apollo

Name Plätze SaeleGloria 200 3

Apollo 300 2

Film Vorfuehrung Kino

9. Verschachtelte Anfragen in SQLIV-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.