1 / 79

Kapitel 2

Kapitel 2. SQL Anfragen. Lernziele. . Syntax von SQL Anfragen Präzise Semantik von SQL Anfragen: Abbildung auf Relationale Algebra Erweiterte Konstrukte in SQL Abfragen: Aggregation, Gruppierung, rekursive Anfragen. Einfache SQL-Anfragen. select * : Wählt alles aus. select *

isadora-watson
Download Presentation

Kapitel 2

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. Kapitel 2 SQL Anfragen

  2. Lernziele  • Syntax von SQL Anfragen • Präzise Semantik von SQL Anfragen:Abbildung auf Relationale Algebra • Erweiterte Konstrukte in SQL Abfragen:Aggregation, Gruppierung, rekursive Anfragen .. Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen

  3. Einfache SQL-Anfragen select *: Wählt alles aus select * from Professoren; from wählt die Tabelle

  4. Einfache SQL-Anfragen Bei select können auch bestimmte Spalten gewählt werden select PersNr, Name from Professoren where Rang= ´C4´; Bei where können logische Einschränkungen gemacht werden

  5. Einfache SQL-Anfragen Mit order by kann die Reihenfolge der Anzeige verändert werden Sortierung select PersNr, Name, Rang from Professoren order by Rang desc, Name asc; Was zuerst steht, danach wird zuerst sortiert desc ist für absteigende Ordnung, asc für aufsteigende

  6. Duplikateliminierung Mit distinct werden Duplikate herausgenommen select distinct Rang from Professoren

  7. Anfragen über mehrere Relationen Welcher Professor liest "Mäeutik"? select Name, Titel from Professoren, Vorlesungen where PersNr = gelesenVon and Titel = `Mäeutik‘ ; Hinter from werden nun zwei Tabellen genannt

  8. Anfragen über mehrere Relationen Professoren PersNr Name Rang Raum 2125 2126 2137 Sokrates Russel Kant C4 C4 C4 226 232 7 Vorlesungen VorlNr Titel SWS gelesen Von 5001 Grundzüge 4 2137 5041 Ethik 4 2125 5049 Mäeutik 2 2125 4630 Die 3 Kritiken 4 2137 Verknüpfung 

  9. PersNr Name Rang Raum VorlNr Titel SWS gelesen Von 2125 Sokrates C4 226 5001 Grundzüge 4 2137 1225 Sokrates C4 226 5041 Ethik 4 2125 Jeder mit jedem einmal 2125 Sokrates C4 226 5049 Mäeutik 2 2125 2126 Russel C4 232 5001 Grundzüge 4 2137 2126 Russel C4 232 5041 Ethik 4 2125 2137 Kant C4 7 4630 Die 3 Kritiken 4 2137 where PersNr = gelesenVon and Titel = `Mäeutik‘ select Name, Titel

  10. Anfragen über mehrere Relationen Welche Studenten hören welche Vorlesungen? select Name, Titel from Studenten, hören, Vorlesungen where Studenten.MatrNr = hören.MatrNr and hören.VorlNr = Vorlesungen.VorlNr; Alternativ: select s.Name, v.Titel from Studenten s, hören h, Vorlesungen v where s. MatrNr = h. MatrNr and h.VorlNr = v.VorlNr Mit Korrelationsvariablen

  11. Mengenoperationen und geschachtelte Anfragen Mengenoperationen union, intersect, minus (Vereinigung, Schnittmenge, Differenz) ( select Name from Assistenten ) union ( select Name from Professoren); Was passiert hier? Jeder für sich mit Zettel und Stift; 5 min. Was würde bei intersect Passieren oder bei minus?

  12. Existenzquantor exists select p.Name from Professoren p where not exists ( select * from Vorlesungen v where v.gelesenVon = p.PersNr ); Korrelation

  13. Existenzquantor exists select p.Name from Professoren p where not exists ( select * from Vorlesungen v where v.gelesenVon = p.PersNr );

  14. Mengenvergleich Hatten wir das nicht gerade? select Name from Professoren where PersNr not in ( select gelesenVon from Vorlesungen ); Unkorrelierte Unterabfragen sind meist effizienter, da sienicht pro Zeile ausgewertetwerden müssen

  15. "Quantifizierte" Subqueries Die Bedingung Wert  ANY Menge mit  {{=, , , , , } ist erfüllt, wenn es in der Menge ein Element gibt, für das Wert Element gilt. (=ANY ist äquivalent zu IN) .. SOME ist equivalent to ANY Die Bedingung Wert  ALL Menge mit  {=, , , , , } ist erfüllt, wenn für alle Elemente der Menge gilt: Wert  Element. (<>ALL ist äquivalent zu NOT IN.) Die Bedingung EXISTS Menge ist erfüllt, wenn die Menge nicht leer ist (dies ist äquivalent zur Bedingung 0 < SELECT COUNT(*) FROM ...) Achtung: Wert  ALL (query_returns_no_rows) = TRUE Wert  ANY (query_returns_no_rows) = FALSE Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen

  16. "Quantifizierte" Subqueries Anfrage: Studenten im höchsten Semester: SELECT * FROM StudentenWHERE Semester >= ALL (SELECT Semester FROM Studenten) ; Anfrage: Studenten, für die keine Prüfungen erfasst sind: SELECT * FROM Studenten sWHERENOTEXISTS ( SELECT * FROM Prüfen p WHERE p.MatrNr = s.MatrNr ); Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen

  17. Aggregatfunktion und Gruppierung Aggregatfunktionen avg, max, min, count, sum select avg (Semester) from Studenten; select gelesenVon, sum (SWS) from Vorlesungen group by gelesenVon; select gelesenVon, Name, sum (SWS) from Vorlesungen, Professoren where gelesenVon = PersNr and Rang = ´C4´ group by gelesenVon, Name having avg (SWS) >= 3;

  18. Besonderheiten bei Aggregatoperationen • SQL erzeugt pro Gruppe ein Ergebnistupel • Deshalb müssen alle in der select-Klausel aufgeführten Attribute - außer den aggregierten – auch in der group by-Klausel aufgeführt werden • Nur so kann SQL sicherstellen, dass sich das Attribut nicht innerhalb der Gruppe ändert

  19. Ausführen der Anfrage mit group by where gelesenVon = PersNr and Rang = ´C4´

  20. group by gelesenVon, Name

  21. VorlNr Titel SWS gelesenVon PersNr Name Rang Raum 5041 5049 4052 Ethik Mäeutik Logik 4 2 4 2125 2125 2125 2125 2125 2125 Sokrates Sokrates Sokrates C4 C4 C4 226 226 226 5001 4630 Grundzüge Die 3 Kritiken 4 4 2137 2137 2137 2137 Kant Kant C4 C4 7 7 VorlNr Titel SWS gelesenVon PersNr Name Rang Raum 5041 Ethik 4 2125 2125 Sokrates C4 226 5049 Mäeutik 2 2125 2125 Sokrates C4 226 4052 Logik 4 2125 2125 Sokrates C4 226 5043 Erkenntnistheorie 3 2126 2126 Russel C4 232 5052 Wissenschaftstheo. 3 2126 2126 Russel C4 232 5216 Bioethik 2 2126 2126 Russel C4 232 5001 Grundzüge 4 2137 2137 Kant C4 7 4630 Die 3 Kritiken 4 2137 2137 Kant C4 7 having avg (SWS) >= 3 Aggregation (sum(SWS)) pro Gruppe

  22. Geschachtelte Anfrage (Forts.) • Unteranfrage in der select-Klausel • Für jedes Ergebnistupel wird die Unteranfrage ausgeführt • Man beachte, dass die Unteranfrage korreliert ist (greift auf Attribute der umschließenden Anfrage zu) select PersNr, Name, ( select sum (SWS) as Lehrbelastung from Vorlesungen where gelesenVon=PersNr ) from Professoren;

  23. Unkorrelierte versus korrelierte Unteranfragen • korrelierte Formulierung • select s.* • from Studenten s • whereexists • (select p.* • from Professoren • where p.GebDatum > s.GebDatum);

  24. Äquivalente unkorrelierte Formulierung • select s.* • from Studenten s • where s.GebDatum < • (select max (p.GebDatum) • from Professoren p); • Vorteil: Unteranfrageergebnis kann materialisiert werden • Unteranfrage braucht nur einmal ausgewertet zu werden

  25. Entschachtelung korrelierter Unteranfragen -- Forts. • select a.* • from Assistenten a • where exists • ( select p.* • from Professoren p • where a.Boss = p.PersNr and p.GebDatum>a.GebDatum); • Entschachtelung durch Join • select a.* • from Assistenten a, Professoren p • where a.Boss=p.PersNr and p.GebDatum > a.GebDatum;

  26. Verwertung der Ergebnismenge einer Unteranfrage • select tmp.MatrNr, tmp.Name, tmp.VorlAnzahl • from (select s.MatrNr, s.Name, count(*) as VorlAnzahl • from Studenten s, hören h • where s.MatrNr=h.MatrNr • group by s.MatrNr, s.Name) tmp • where tmp.VorlAnzahl > 2;

  27. Decision-Support-Anfragen mit geschachtelten Unteranfragen select h.VorlNr, h.AnzProVorl, g.GesamtAnz, h.AnzProVorl/g.GesamtAnz as Marktanteil from ( select VorlNr, count(*) as AnzProVorl from hören group by VorlNr ) h, ( select count (*) as GesamtAnz from Studenten) g;

  28. Das Ergebnis der Anfrage

  29. Simulation allquantifizierter Suchprädikate  • Anfrage: • Wer hat alle vierstündigen Vorlesungen gehört? • Problem:  ist in SQL nicht vorgesehen, nur exists • Idee: Elimination von  und  • Dazu sind folgende Äquivalenzen anzuwenden: Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen

  30. Umformung der Logik (1/3)  • Schritt 1: Elimination Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen

  31. Umformung der Logik (2/3)  • Schritt 2: Elimination Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen

  32. Umformung der Logik (3/3)  • Schritt 3: Transformation ergibt schließlich: Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen

  33. Umsetzung der Logik in SQL Anfrage: Wer hat allevierstündigen Vorlesungen gehört? SELECT s.MatrNr FROM Studenten s WHERE NOT EXISTS (SELECT * FROM Vorlesungen v WHERE v.SWS = 4 AND NOT EXISTS (SELECT * FROM hören h WHERE h.VorlNr = v.VorlNr AND h.MatrNr=s.MatrNr ) ); Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen

  34. Simulation allquantifizierter Suchprädikate:alternative Form mit Mengenoperation Anfrage: Wer hat allevierstündigen Vorlesungen gehört? SELECT s.MatrNr FROM Studenten sWHERE NOT EXISTS ( SELECT v.VorlNr FROM Vorlesungen v WHERE v.SWS = 4 MINUS SELECT h.VorlNr FROM hören hWHERE h.MatrNr = s.MatrNr ) ; Zwischenanfrage: Alle Vorlesungen, die 4 SWS haben MINUS Alle Vorlesungen, die der Student s gehört hat Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen

  35. Allquantifizierung durch count-Aggregation • Allquantifizierung kann immer auch durch eine count-Aggregation ausgedrückt werden • Wir betrachten dazu eine etwas einfachere Anfrage, in der wir die MatrNr der Studenten ermitteln wollen, die alle Vorlesungen hören: select h.MatrNr from hören h group by h.MatrNr having count (*) = (select count (*) from Vorlesungen);

  36. Herausforderung • Wie formuliert man die komplexere Anfrage: „Wer hat alle vierstündigen Vorlesungen gehört“ohne Korrelation nur mit Zählen • Grundidee besteht darin, vorher durch einen Join die Studenten/Vorlesungs-Paare einzuschränken und danach das Zählen durchzuführen Jeder für sich mit Zettel und Stift; 5 min

  37. Simulation allquantifizierter Suchprädikatedurch count-Aggregation Anfrage: Wer hat allevierstündigen Vorlesungen gehört? SELECT h.MatrNr FROM hören h, Vorlesungen v WHERE v.SWS = 4 AND h.VorlNr = v.VorlNr GROUP BY h.MatrNr HAVING count (*) = (SELECT count (*) FROM Vorlesungen v1 WHERE v1.SWS = 4); Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen

  38. Count-Aggregation: Fehlerquellen Anfrage: Namen der Studenten, die allevierstündigen Vorlesungen gehört haben Vorsicht: so geht das nicht.. SELECTh.MatrNr, s.Name FROM hören h, Vorlesungen v, Studenten s WHERE v.SWS = 4 AND h.VorlNr = v.VorlNr AND h.MatrNr = s.MatrNr GROUP BY h.MatrNr HAVING count (*) = (SELECT count (*) FROM Vorlesungen v1 WHERE v1.SWS = 4); Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen

  39. Count-Aggregation: Fehlerquellen Anfrage: Namen der Studenten, die allevierstündigen Vorlesungen gehört haben • SELECTh.MatrNr, s.Name • FROM hören h, Vorlesungen v, Studenten s • WHERE • v.SWS = 4 AND h.VorlNr = v.VorlNr AND h.MatrNr = s.MatrNr • GROUP BY h.MatrNr, s.Name • HAVING count (*) = ( SELECT count (*) • FROM Vorlesungen v1 • WHERE v1.SWS = 4); • SQL erzeugt pro Gruppe ein Ergebnistupel. Deshalb müssen alle in derSELECT-Klausel aufgeführten Attribute - außer den aggregierten – auch in der GROUP BY-Klausel aufgeführt werden! Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen

  40. Allquantifizierung - Fazit • Es geht zwar nicht direkt, aber indirekt • Man kann per deMorgan den Ausdruck auf exists zurückführen • Man kann per Mengenoperation minus die Fälle eliminieren, in denen es nicht übereinstimmt • Man kann zählen, ob alle Fälle betrachtet sind und dies dann mit der Gesamtmenge der Fälle vergleichen • Für die Prüfung reicht eine Methode Datenbanken für Mathematiker, WS 11/12 Kapitel 2: SQL Anfragen

  41. Nullwerte  • unbekannter Wert (wird vielleicht später nachgereicht) • Nullwerte können auch im Zuge der Anfrageauswertung entstehen (Bsp. äußere Joins) • manchmal sehr überraschende Anfrageergebnisse: select count (*) from Studenten where Semester < 13 or Semester > =13 • Wenn es Studenten gibt, deren Semester-Attribut den Wert null hat, werden diese nicht mitgezählt • Der Grund liegt in folgenden Regeln für den Umgang mit null-Werten begründet:

  42. Auswertung bei Null-Werten  • In arithmetischen Ausdrücken werden Nullwerte propagiert, d.h. sobald ein Operand null ist, wird auch das Ergebnis null. Dementsprechend wird z.B. null + 1 zu null ausgewertet-aber auch null * 0 wird zu null ausgewertet. • SQL hat eine dreiwertige Logik, die nicht nur true und false kennt, sondern auch einen dritten Wert unknown. Diesen Wert liefern Vergleichsoperationen zurück, wenn mindestens eines ihrer Argumente null ist. Beispielsweise wertet SQL das Prädikat (PersNr=...) immer zu unknown aus, wenn die PersNr des betreffenden Tupels den Wert null hat. • Logische Ausdrücke werden nach den folgenden Tabellen berechnet:

  44. Diese Berechnungsvorschriften sind recht intuitiv. Unknown or true wird z.B. zu true - die Disjunktion ist mit dem true-Wert des rechten Arguments immer erfüllt, unabhängig von der Belegung des linken Arguments. Analog ist unknownand false automatisch false - keine Belegung des linken Arguments könnte die Konjunktion mehr erfüllen. 4. In einer where-Bedingung werden nur Tupel weitergereicht, für die die Bedingung true ist. Insbesondere werden Tupel, für die die Bedingung zu unknown auswertet, nicht ins Ergebnis aufgenommen. 5. Bei einer Gruppierung wird null als ein eigenständiger Wert aufgefasst und in eine eigene Gruppe eingeordnet.

More Related