Nicole Rottmann 2011/12
Datenbankentwicklung
IV-LK
1. Warum Datenbanken?2. Anforderungsanalyse für Datenbanken3. Ableitung von Tabellenstrukturen4. Normalisierung5. Relationenalgebra6. Formalisierung von Tabellen in SQL7. SQL- Anfragen
Nicole Rottmann 2011/12
7. Einfache SQL-AnfragenIV-LK
1. Ausgabe der eingegebenen Informationen
2. Auswahlkriterien in der WHERE-Bedingung
3. Nutzung von Aggregatsfunktionen4. Anfragen über mehrere Tabellen5. Aufgaben
Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen1. Ausgabe der eingegebenen Informationen
IV-LKBeispieltabellen für Anfragen:
Anfrage: Wie lauten die Namen der eingetragenen Gehege?SELECT GName
FROM GehegeErgebnis:
GName----------WaldFeldWeide
GNr TName Gattung
1 Laber Baer
1 Sabber Baer
2 Klopfer Hase
3 Bunny Hase
2 Harald Schaf
3 Walter Schaf
Gattung MinFlaeche
Baer 8
Hase 2
Schaf 5
GNr GName Flaeche
1 Wald 20
2 Feld 10
3 Weide 9
Gehege ArtTier
Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen1. Ausgabe der eingegebenen Informationen
IV-LKAnfrage: Welche Gattungen leben aktuell in den Gehegen.
SELECT Tier.GattungFROM Tier
Ergebnis:GATTUNG--------------BaerBaerHaseHaseSchafSchaf
Vermeidung von doppelten Werten:SELECT DISTINCT Tier.Gattung
FROM Tier
Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen1. Ausgabe der eingegebenen Informationen
IV-LKMehrere Ergebnisattribute in der Anfrage: Der Tiername soll zusammen mit der Gattung
ausgegeben werden:SELECT Tier.TName, Tier.Gattung
FROM TierErgebnis:
TName Gattung--------- ----------Laber BaerSabber BaerKlopfer HaseBunny HaseHarald SchafWalter Schaf
Anfrage: Gesamter Tabelleninhalt:SELECT *
FROM Gehege
Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen1. Ausgabe der eingegebenen Informationen
IV-LKBerechnungen in der SELECT – Zeile:Anfrage: Wie groß ist der prozentuale Anteil eines jeden Geheges an der Gesamtfläche, wenn
die Gesamtfläche 50 beträgt?SELECT Gehege.GName, (Gehege.Flaeche/50)*100
FROM GehegeErgebnis:GName (Gehege.Flaeche/50)*100---------- ----------------------------------Wald 40Feld 20Weide 18
Anfrage: Die Gattung eines Tieres, soll zusammen mit dem Tiernamen, getrennt durch zwei Doppelpunkte ausgegeben werden.
SELECT Tier.Gattung || '::' || Tier.TNameFROM Tier
Umbenennung der Spaltenüberschrift:SELECT Gehege.GName Gatter
Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen1. Ausgabe der eingegebenen Informationen
IV-LKErzeugung weiterer Ergebnisspalten:SELECT 'Unser Zoo' Zooname, Tier.TName Tiername, 2005 Einzug, 42
FROM TierErgebnis:Zooname Tiername Einzug 42------------ ------------ --------- ---Unser Zoo Laber 2005 42Unser Zoo Sabber 2005 42…
Sortierung von Ausgaben:SELECT Gehege.GName, Gehege.Flaeche
FROM GehegeORDER BY Gehege.Flaeche ASC
Ergebnis:GName Flaeche--------- ----------Weide 9Feld 10Wald 20
Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen1. Ausgabe der eingegebenen Informationen
IV-LKMehrere Sortierkriterien zusammen:SELECT *
FROM TierORDER BY Tier.Gattung DESC, Tier.TName ASC
Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen2. Auswahlkriterien in der WHERE –Bedingung
IV-LKAnfrage: Wie lauten die Namen aller Schafe?SELECT Tier.TName
FROM TierWhere Tier.Gattung = 'Schaf'
Ergebnis:TName---------HaraldWalterZusammengesetzte Bedingung:Anfrage: Welche Gattungen beanspruchen mindestens eine Flaeche von 4, sind aber keine Bären?SELECT Art.Gattung
FROM ArtWHERE Art.MinFlaeche >= 4AND Art.Gattung<> 'Baer'
Ergebnis:Gattung----------Schaf
Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen 2. Auswahlkriterien in der WHERE –Bedingung
IV-LKTextanalyse:SELECT Tier.TName
FROM TierWHERE Tier.TName LIKE '%a%'
Ergebnis:TName---------LaberSabberHaraldWalterAnfrage: Tiernamen, deren dritter Buchstabe ein n ist.SELECT Tier.TName
FROM TierWHERE Tier.TName LIKE '__n%'
Ergebnis:TName---------Bunny
Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen 2. Auswahlkriterien in der WHERE –Bedingung
IV-LKOperationen auf Attribute:SELECT LOWER (Tier.TName)
FROM TierWHERE LOWER (Tier.Gattung) = 'schaf'
Ergebnis:LOWER (T--------------haraldwalter
Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen3. Nutzung von Aggregatsfunktionen
IV-LKStatistische Auswertungen:Anfrage: Welches ist die größte Gehegefläche?SELECT MAX (Gehege.Flaeche)
FROM GehegeErgebnis:MAX(Gehege.Flaeche)------------------------------20Anfrage: Wie groß ist die Gesamtfläche aller Gehege?SELECT SUM (Gehege.Flaeche) Gesamtflaeche
FROM GehegeErgebnis:Gesamtflaeche-------------------39Anfrage: Wie viele Tiere leben im Zoo Ergebnis:SELECT COUNT(*) Tieranzahl Tieranzahl
FROM Tier -------------6
Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen 3. Nutzung von Aggregatsfunktionen
IV-LKAnfrage: Wie groß ist die durchschnittliche Gehegefläche?SELECT AVG (Gehege.Flaeche) Durchschnitt
FROM GehegeErgebnis:Durchschnitt----------------13
Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen 4. Anfragen über mehrere Tabellen
IV-LKAnfrage: Wie lauten die Namen der Tiere und in welchen Gehegen leben sie?SELECT Tier.TName, Gehege.GName
FROM Tier, GehegeWHERE Tier.GNr=Gehege.GNr
Ergebnis:TName GName-------- ---------Laber WaldSabber WaldHarald Feld….
Anfrage: Gib zu jedem Gehegenamen, die Gattungen der darin lebenden Tiere aus, die höchstens eine Fläche von 6 verbrauchen.
SELECT DESTINCT Gehege.GName, Art.Gattung Ergebnis:FROM Gehege, Tier, Art GName Gattung
WHERE Gehege.GNr = Tier.GNrAND Tier.Gattung = Art.Gattung --------- ----------AND Art.MinFlaeche <= 6 Feld Hase
Feld SchafWeide HaseWeide Schaf
Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen 4. Anfragen über mehrere Tabellen
IV-LKGrundsätzlicher Ansatz zur Formulierung einfacher SQL- Anfragen:1. Es werden die für die Anfrage erforderlichen Tabellen ausgesucht und in die FROM –
Zeile geschrieben.2. Es wird die Verknüpfung der benutzen Tabellen in der WHERE- Bedingung festgelegt.3. Falls es die Anfrage erfordert, werden die weiteren Anforderungen in Teilbedingungen
übersetzt. Die resultierenden Teilbedingung wird typischerweise mit AND mit der Bedingung aus 2. verknüpft.
4. Es werden die Informationen, z.B. Attribute ausgewählt, die für die Ergebnisausgabe wichtig sind, und in die SELECT Zeile geschrieben.
5. Bei Bedarf wird in der ORDER BY Zeile angegeben, in welcher Reihenfolge die Ausgaben erfolgen sollen.
Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen 4. Anfragen über mehrere Tabellen
IV-LKUmbenennung der Ergebnisspalten:SELECT G.GName Gebiet, T.TName Schaf
FROM Gehege G, Tier TWHERE D.GNr= T.TNrAND T.Gattung ='Schaf'
Ergebnis:Gebiet Schaf-------- -------FeldHaraldWeide WalterAnfrage: Es sollen die Nummern aller Gehege ausgegeben werden, in denen zwei
unterschiedliche Gattungen leben.SELECT T1.GNr
FROM Tier T1, Tier T2WHERE T1.GNr = T2.GNrAND T1.Gattung <> T2.Gattung
ErgebnisGNr----2323
Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen 4. Anfragen über mehrere Tabellen
IV-LKAnfrage: Statt der Gehegenummer soll der Name des Geheges ausgegeben werden.SELECT Gehege.GName
FROM Tier T1, Tier T2, GehegeWHERE T1.GNr = T2.GNr
AND Gehege.GNr = T1.GNrAND T1.Gattung < T2.Gattung
Ergebnis:GName----------FeldWeide
Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen 4. Anfragen über mehrere Tabellen
IV-LKTabellen für kritisches Anfrageergebnis:
Abfrage: Wie heißen die Mitarbeiter, deren Chef in der Abteilung 1 oder Abteilung 2 arbeitet.SELECT Mitarbeiter.Name
FROM Mirarbeiter, Abteilung1, Abteilung2WHERE Mitarbeiter.Chef=Abteilung1.MiNrOR Mitarbeiter.Chef=Abteilung2.MiNr
Ergebnis:Es wurden keine Zeilen ausgewählt.SELECT Mitarbeiter.Name
FROM Mirarbeiter, Abteilung1WHERE Mitarbeiter.Chef=Abteilung1.MiNr
Ergebnis:Name-------Udo
MiNr Name Chef
401 Udo 1
402 Ute 3
MiNr Leiter
401 Erna
402 Edna
MiNr LeiterAbteilung1 Abteilung2Mitarbeiter
Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen 5. Aufgaben
IV-LKWiederholungsfragen:1. Wozu wird DISTINCT benötigt?2. Wozu kann der * in der SELECT Zeile genutzt werden?3. Welche Berechnungsmöglichkeiten gibt es in der SELECT Zeile?4. Wie kann man Ergebnisspalten umbenennen?5. Wie kann man neue Spalten bei der Berechnung von Tabellen erzeugen?6. Welche Möglichkeiten zur Sortierung der Ausgabe gibt es?7. Welche Möglichkeiten zur Textbearbeitung gibt es in SQL?8. Wie wird mit NULL Werten in WHERE Bedingungen umgegangen?9. Was sind Aggregatsfunktionen, was ist bei ihrer Nutzung zu beachten?10. Wie kann man mehrere Tabellen in SQL verknüpfen?11. Erkläre den Aufbau und Ablauf der Auswertung einfacher SQL- Anfragen.12. Beschreibe ein generelles Vorgehen zur Erstellung einer SQL- Anfrage.13. Wie kann man eine Tabelle in Anfragen mehrmals nutzen, wieso kann dies sinnvoll
sein?14. Welche Besonderheit ist bei der Nutzung von OR Bedingungen und der Verknüpfung
mehrerer Tabellen zu beachten?
Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen 5. Aufgaben
IV-LKÜbungsaufgaben:Gegeben seine folgende Tabellen zur Notenverwaltung.
Formuliere folgende Textzeilen als SQL-Anfragen.1. Gib die Namen der Studierenden aus, die eine Prüfung im Fach "Wahl1" gemacht haben.2. Gib den Titel der Veranstaltungen aus und die zugehörige Note für alle Prüfungen, die Simson gemacht hat aus.3. Gib eine Liste aller Titel von Veranstaltungen mit den bisher in den Prüfungen erreichten Noten (Ausgabe: Titel,
Note) aus.4. Gib die Anzahl der Studierenden aus, die bereits eine Prüfung im Fach DB gemacht haben.5. Gib die Namen aller Dozenten aus, die mindestens zwei Veranstaltungen anbieten.6. Gib die Durchschnittsnote für alle Fächer zusammen aus, die von Hinz unterrichtet wurden.7. Gib die Namen aller Studierenden aus, die mindestens genauso gut wie Simson in DB waren, aber nicht
Simson sind.8. Gib die Namen aller Studierenden aus, die mindestens eine Prüfung bei Hinz gemacht haben. Geh davon aus,
dass der veranstaltende Dozent auch Prüfer ist.
MatNr Name
42 Simson
43 Milhuse
44 Monz
MatNr Fach Note
42 Wahl1 3,0
42 DB 1,7
43 Wahl2 4,0
43 DB 1,3
44 Wahl1 5,0
Kürzel Titel Dozent
Wahl1 Controlling Hinz
Wahl2 Java Hinz
DB Datenbanken Kunz
StudentPruefung
Veranstaltung