1 / 18

Datenbankentwicklung IV-LK

Datenbankentwicklung IV-LK. 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.

ianthe
Download Presentation

Datenbankentwicklung IV-LK

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DatenbankentwicklungIV-LK • 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

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

  3. 9. Verschachtelte Anfragen in SQL – 1.Nutzung von Mengen-OperatorenIV-LK Als Beispiel dient weiterhin die Datenbank Zoo Gehege Tier Art

  4. 9. Verschachtelte Anfragen in SQL – 1.Nutzung von Mengen-OperatorenIV-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.

  5. 9. Verschachtelte Anfragen in SQL – 1.Nutzung von Mengen-OperatorenIV-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

  6. 9. Verschachtelte Anfragen in SQL – 1.Nutzung von Mengen-OperatorenIV-LK Randbedingungen: • Die resultierenden Tabellen der Teilanfragen müssen im Ergebnis die gleiche Spaltenanzahl haben. • Die Spalten müssen vom Typ zusammen passen. • 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

  7. 9. Verschachtelte Anfragen in SQL – 1.Nutzung von Mengen-OperatorenIV-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

  8. 9. Verschachtelte Anfragen in SQL – 1.Nutzung von Mengen-OperatorenIV-LK Vermeidung 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 Personal WHERE Personal.Status Is NOT NULL UNION SELECT Personal.Pnr, Personal.Name, 'unbekannt' FROM Personal WHERE Personal.Status IS NULL

  9. 9. Verschachtelte Anfragen in SQL – 2.Teilanfragen in der SELECT-ZeileIV-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.

  10. 9. Verschachtelte Anfragen in SQL – 2.Teilanfragen in der SELECT-ZeileIV-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

  11. 9. Verschachtelte Anfragen in SQL – 3.Teilanfragen in der WHERE-BedingungIV-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)

  12. 9. Verschachtelte Anfragen in SQL – 3.Teilanfragen in der SELECT-ZeileIV-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')

  13. 9. Verschachtelte Anfragen in SQL – 1.Nutzung von Mengen-OperatorenIV-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

  14. 9. Verschachtelte Anfragen in SQL – 4.Teilanfragen in der HAVING-BedingungIV-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.

  15. 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? • 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'

  16. 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 =Abfrage 29 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

  17. 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. Film Vorfuehrung Kino

  18. 9. Verschachtelte Anfragen in SQLIV-LK Formuliere folgende SQL- Anfragen: • Gib die Namen aller Kinos aus, in denen der Film „Die Hand“ läuft. • Gib zu jedem Kino die Gesamtzahl aller zur Verfügung stehenden Plätze aus (Ausgabe: Kinoname, Gesamtplatzzahl) • Gib die Titel der Filme aus, die in mindestens zwei Kinos laufen. • Gib zu jedem Kino die Länge des längsten Films aus, der in diesem Kino läuft. • 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. • 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.

More Related