200 likes | 280 Views
Datenbankentwicklung IV-LK. Warum Datenbanken? Anforderungsanalyse für Datenbanken Ableitung von Tabellenstrukturen Normalisierung Relationenalgebra Formalisierung von Tabellen in SQL SQL- Anfragen. 7. Einfache SQL-Anfragen IV-LK. Ausgabe der eingegebenen Informationen
E N D
DatenbankentwicklungIV-LK • Warum Datenbanken? • Anforderungsanalyse für Datenbanken • Ableitung von Tabellenstrukturen • Normalisierung • Relationenalgebra • Formalisierung von Tabellen in SQL • SQL- Anfragen Nicole Rottmann 2011/12
7. Einfache SQL-AnfragenIV-LK • Ausgabe der eingegebenen Informationen • Auswahlkriterien in der WHERE-Bedingung • Nutzung von Aggregatsfunktionen • Anfragen über mehrere Tabellen • Aufgaben Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen1. Ausgabe der eingegebenen InformationenIV-LK Art Gehege Tier Beispieltabellen für Anfragen: Anfrage: Wie lauten die Namen der eingetragenen Gehege? SELECT GName FROM Gehege Ergebnis: GName ---------- Wald Feld Weide Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen1. Ausgabe der eingegebenen InformationenIV-LK Anfrage: Welche Gattungen leben aktuell in den Gehegen. SELECT Tier.Gattung FROM Tier Ergebnis: GATTUNG -------------- Baer Baer Hase Hase Schaf Schaf Vermeidung von doppelten Werten: SELECT DISTINCT Tier.Gattung FROM Tier Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen1. Ausgabe der eingegebenen InformationenIV-LK Mehrere Ergebnisattribute in der Anfrage: Der Tiername soll zusammen mit der Gattung ausgegeben werden: SELECT Tier.TName, Tier.Gattung FROM Tier Ergebnis: TName Gattung --------- ---------- Laber Baer Sabber Baer Klopfer Hase Bunny Hase Harald Schaf Walter Schaf Anfrage: Gesamter Tabelleninhalt: SELECT * FROM Gehege Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen1. Ausgabe der eingegebenen InformationenIV-LK Berechnungen 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 Gehege Ergebnis: GName (Gehege.Flaeche/50)*100 ---------- ---------------------------------- Wald 40 Feld 20 Weide 18 Anfrage: Die Gattung eines Tieres, soll zusammen mit dem Tiernamen, getrennt durch zwei Doppelpunkte ausgegeben werden. SELECT Tier.Gattung || '::' || Tier.TName FROM Tier Umbenennung der Spaltenüberschrift: SELECT Gehege.GName Gatter Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen1. Ausgabe der eingegebenen InformationenIV-LK Erzeugung weiterer Ergebnisspalten: SELECT 'Unser Zoo' Zooname, Tier.TName Tiername, 2005 Einzug, 42 FROM Tier Ergebnis: Zooname Tiername Einzug 42 ------------ ------------ --------- --- Unser Zoo Laber 2005 42 Unser Zoo Sabber 2005 42 … Sortierung von Ausgaben: SELECT Gehege.GName, Gehege.Flaeche FROM Gehege ORDER BY Gehege.Flaeche ASC Ergebnis: GName Flaeche --------- ---------- Weide 9 Feld 10 Wald 20 Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen1. Ausgabe der eingegebenen InformationenIV-LK Mehrere Sortierkriterien zusammen: SELECT * FROM Tier ORDER BY Tier.Gattung DESC, Tier.TName ASC Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen2. Auswahlkriterien in der WHERE –BedingungIV-LK Anfrage: Wie lauten die Namen aller Schafe? SELECT Tier.TName FROM Tier Where Tier.Gattung = 'Schaf' Ergebnis: TName --------- Harald Walter Zusammengesetzte Bedingung: Anfrage: Welche Gattungen beanspruchen mindestens eine Flaeche von 4, sind aber keine Bären? SELECT Art.Gattung FROM Art WHERE Art.MinFlaeche >= 4 AND Art.Gattung<> 'Baer' Ergebnis: Gattung ---------- Schaf Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen 2. Auswahlkriterien in der WHERE –Bedingung IV-LK Textanalyse: SELECT Tier.TName FROM Tier WHERE Tier.TName LIKE '%a%' Ergebnis: TName --------- Laber Sabber Harald Walter Anfrage: Tiernamen, deren dritter Buchstabe ein n ist. SELECT Tier.TName FROM Tier WHERE Tier.TName LIKE '__n%' Ergebnis: TName --------- Bunny Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen2. Auswahlkriterien in der WHERE –Bedingung IV-LK Operationen auf Attribute: SELECT LOWER (Tier.TName) FROM Tier WHERE LOWER (Tier.Gattung) = 'schaf' Ergebnis: LOWER (T -------------- harald walter Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen3. Nutzung von AggregatsfunktionenIV-LK Statistische Auswertungen: Anfrage: Welches ist die größte Gehegefläche? SELECT MAX (Gehege.Flaeche) FROM Gehege Ergebnis: MAX(Gehege.Flaeche) ------------------------------ 20 Anfrage: Wie groß ist die Gesamtfläche aller Gehege? SELECT SUM (Gehege.Flaeche) Gesamtflaeche FROM Gehege Ergebnis: Gesamtflaeche ------------------- 39 Anfrage: 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-LK Anfrage: Wie groß ist die durchschnittliche Gehegefläche? SELECT AVG (Gehege.Flaeche) Durchschnitt FROM Gehege Ergebnis: Durchschnitt ---------------- 13 Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen4. Anfragen über mehrere Tabellen IV-LK Anfrage: Wie lauten die Namen der Tiere und in welchen Gehegen leben sie? SELECT Tier.TName, Gehege.GName FROM Tier, Gehege WHERE Tier.GNr=Gehege.GNr Ergebnis: TName GName -------- --------- Laber Wald Sabber Wald Harald 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.GNr AND Tier.Gattung = Art.Gattung --------- ---------- AND Art.MinFlaeche <= 6 Feld Hase Feld Schaf Weide Hase Weide Schaf Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen4. Anfragen über mehrere Tabellen IV-LK Grundsätzlicher Ansatz zur Formulierung einfacher SQL- Anfragen: • Es werden die für die Anfrage erforderlichen Tabellen ausgesucht und in die FROM – Zeile geschrieben. • Es wird die Verknüpfung der benutzen Tabellen in der WHERE- Bedingung festgelegt. • 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. • Es werden die Informationen, z.B. Attribute ausgewählt, die für die Ergebnisausgabe wichtig sind, und in die SELECT Zeile geschrieben. • Bei Bedarf wird in der ORDER BY Zeile angegeben, in welcher Reihenfolge die Ausgaben erfolgen sollen. Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen4. Anfragen über mehrere Tabellen IV-LK Umbenennung der Ergebnisspalten: SELECT G.GName Gebiet, T.TName Schaf FROM Gehege G, Tier T WHERE D.GNr= T.TNr AND T.Gattung ='Schaf' Ergebnis: Gebiet Schaf -------- ------- Feld Harald Weide Walter Anfrage: Es sollen die Nummern aller Gehege ausgegeben werden, in denen zwei unterschiedliche Gattungen leben. SELECT T1.GNr FROM Tier T1, Tier T2 WHERE T1.GNr = T2.GNr AND T1.Gattung <> T2.Gattung Ergebnis GNr ---- 2 3 2 3 Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen4. Anfragen über mehrere Tabellen IV-LK Anfrage: Statt der Gehegenummer soll der Name des Geheges ausgegeben werden. SELECT Gehege.GName FROM Tier T1, Tier T2, Gehege WHERE T1.GNr = T2.GNr AND Gehege.GNr = T1.GNr AND T1.Gattung < T2.Gattung Ergebnis: GName ---------- Feld Weide Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen4. Anfragen über mehrere Tabellen IV-LK Mitarbeiter Abteilung1 Abteilung2 Tabellen 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, Abteilung2 WHERE Mitarbeiter.Chef=Abteilung1.MiNr OR Mitarbeiter.Chef=Abteilung2.MiNr Ergebnis: Es wurden keine Zeilen ausgewählt. SELECT Mitarbeiter.Name FROM Mirarbeiter, Abteilung1 WHERE Mitarbeiter.Chef=Abteilung1.MiNr Ergebnis: Name ------- Udo Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen5. Aufgaben IV-LK Wiederholungsfragen: • Wozu wird DISTINCT benötigt? • Wozu kann der * in der SELECT Zeile genutzt werden? • Welche Berechnungsmöglichkeiten gibt es in der SELECT Zeile? • Wie kann man Ergebnisspalten umbenennen? • Wie kann man neue Spalten bei der Berechnung von Tabellen erzeugen? • Welche Möglichkeiten zur Sortierung der Ausgabe gibt es? • Welche Möglichkeiten zur Textbearbeitung gibt es in SQL? • Wie wird mit NULL Werten in WHERE Bedingungen umgegangen? • Was sind Aggregatsfunktionen, was ist bei ihrer Nutzung zu beachten? • Wie kann man mehrere Tabellen in SQL verknüpfen? • Erkläre den Aufbau und Ablauf der Auswertung einfacher SQL- Anfragen. • Beschreibe ein generelles Vorgehen zur Erstellung einer SQL- Anfrage. • Wie kann man eine Tabelle in Anfragen mehrmals nutzen, wieso kann dies sinnvoll sein? • Welche Besonderheit ist bei der Nutzung von OR Bedingungen und der Verknüpfung mehrerer Tabellen zu beachten? Nicole Rottmann 2011/12
7. Einfache SQL-Anfragen5. Aufgaben IV-LK Pruefung Student Veranstaltung Übungsaufgaben: Gegeben seine folgende Tabellen zur Notenverwaltung. Formuliere folgende Textzeilen als SQL-Anfragen. • Gib die Namen der Studierenden aus, die eine Prüfung im Fach "Wahl1" gemacht haben. • Gib den Titel der Veranstaltungen aus und die zugehörige Note für alle Prüfungen, die Simson gemacht hat aus. • Gib eine Liste aller Titel von Veranstaltungen mit den bisher in den Prüfungen erreichten Noten (Ausgabe: Titel, Note) aus. • Gib die Anzahl der Studierenden aus, die bereits eine Prüfung im Fach DB gemacht haben. • Gib die Namen aller Dozenten aus, die mindestens zwei Veranstaltungen anbieten. • Gib die Durchschnittsnote für alle Fächer zusammen aus, die von Hinz unterrichtet wurden. • Gib die Namen aller Studierenden aus, die mindestens genauso gut wie Simson in DB waren, aber nicht Simson sind. • 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. Nicole Rottmann 2011/12