SQL
Werbung
Informatik für Ökonomen II: SQL Prof. Dr. Carl-Christian Kanne Monday, October 6, 2008 1 Die Anfragesprache SQL • “Structured Query Language” • Ursprung: SEQUEL (IBM Research) • Standard (ANSI/ISO) • • • • • Monday, October 6, 2008 SQL1 (SQL-86) SQL2 (SQL-92) SQL:1999 (SQL-3) SQL:2003 SQL:2006 2 SQL-Teilsprachen • Data Definition Language DDL • Logisches und physisches Schema beschreiben • Data Manipulation Language DML • • Monday, October 6, 2008 Anfragen Updates 3 Uni-Schema voraussetzen MatrNr Name Semester Vorgänger Studenten N N Name Fachgebiet Monday, October 6, 2008 N Titel lesen 1 arbeitenFür VorlNr SWS Vorlesungen prüfen N Assistenten M M M Note PersNr hören N Nachfolger 1 1 Professoren PersNr Rang Raum Name 4 Die relationale Uni-DB PersNr 2125 2126 2127 2133 2134 2136 2137 Professoren Name Rang Sokrates C4 Russel C4 Kopernikus C3 Popper C3 Augustinus C3 Curie C4 Kant C4 Raum 226 232 310 52 309 36 7 voraussetzen Vorgänger Nachfolger 5001 5041 5001 5043 5001 5049 5041 5216 5043 5052 5041 5052 5052 5259 prüfen MatrNr VorlNr PersNr Note 28106 5001 2126 1 25403 5041 2125 2 27550 4630 2137 2 Monday, October 6, 2008 Studenten MatrNr Name Semester 24002 Xenokrates 18 25403 Jonas 12 26120 Fichte 10 26830 Aristoxenos 8 27550 Schopenhauer 6 28106 Carnap 29120 Theophrastos 29555 Feuerbach 3 2 2 hören MatrNr VorlNr 26120 5001 27550 5001 27550 4052 28106 5041 28106 5052 28106 5216 28106 5259 29120 5001 29120 5041 29120 5049 29555 5022 25403 5022 Assistenten Persl Nr Name Fachgebiet Boss 3002 Platon Ideenlehre 2125 Syllogistik 2125 3003 Aristoteles 3004 Wittgenstein Sprachtheorie 3005 Rhetikus 3006 Newton 3007 Spinoza 2126 Planeten bewegung Keplersche Gesetze 2127 2127 Gott und Natur 2126 Vorlesungen VorlNr Titel SWS gelesen von 5001 Grundzüge 4 2137 5041 Ethik 4 2125 5043 Erkenntnistheorie 3 2126 5049 Mäeutik 2 2125 4052 5052 5216 Logik Wissenschaftstheorie Bioethik 4 3 2 2125 2126 2126 5259 Der Wiener Kreis 2 2133 5022 Glaube und Wissen 2 2134 4630 Die 3 Kritiken 4 2137 5 SQL-Datenmodell • SQL verwendet keine Relationen, sondern “Multisets” als grundlegenden Kollektionstyp • Duplikate erlaubt • Aber Schlüsselangabe möglich • Reihenfolge aber nicht festgelegt • Monday, October 6, 2008 Bestimmte Anfragekonstrukte erlauben jedoch Tupelordnung (siehe ORDER BY) 6 SQL-Datenmodell: Domänen • character (n), char (n) • character varying (n), varchar (n) • numeric (p,s), integer • blob oder raw für sehr große binäre Daten • clob für sehr große String-Attribute • date für Datumsangaben • xml für XML-Dokumente Monday, October 6, 2008 7 DDL: Tabelle anlegen create table Professoren NULL “character(2) ” (PersNr integer not null, Name varchar (30) not null Rang character (2) ); character(2)NULL Monday, October 6, 2008 8 Einfache SQL-Anfrage select PersNr, Name from Professoren where Rang= ´C4´; PersNr 2125 2126 2136 2137 Monday, October 6, 2008 Name Sokrates Russel Curie Kant 9 Duplikateliminierung select distinct Rang from Professoren Rang C3 C4 Monday, October 6, 2008 10 Verknüpfung von Relationen Wer liest Mäutik? select Name from Professoren,Vorlesungen where PersNr = gelesenVon and Titel = `Grundzüge‘ ; ΠName(σPersNr=gelesenVon∧Titel=’Grundzüge’(Professoren × Vorlesungen)) Monday, October 6, 2008 11 FROM-Klausel from Professoren,Vorlesungen Vorlesungen Professoren PersNr Name Rang Raum VorlNr 2125 2126 Sokrates Russel C4 C4 226 232 5001 5041 Grundzüge Ethik 4 4 gelesen von 2137 2125 2127 Kopernikus C3 310 5043 Erkenntnistheorie 3 2126 2133 Popper C3 52 2134 Augustinus C3 309 5049 4052 Mäeutik Logik 2 4 2125 2125 2136 2137 Curie Kant C4 C4 36 7 Titel SWS ... × Monday, October 6, 2008 12 gelesen SWS von 4 2137 2 2125 PersNr Name Rang Raum VorlNr Titel 2125 2125 Sokrates Sokrates C4 C4 226 226 5001 5049 Grundzüge Mäeutik 2126 ... Russel ... C4 ... 232 ... 5001 ... Grundzüge ... 4 ... 2137 ... 2127 Kopernikus C3 310 5049 Mäeutik 2 2125 ... ... ... ... ... ... ... ... 2134 Augustinus C3 309 5001 Grundzüge 4 2137 2134 Augustinus C3 309 5041 Ethik 4 2125 ... 2136 ... 2137 ... ... Curie ... Kant ... ... C4 ... C4 ... ... 36 ... 7 ... ... 5041 ... 5049 ... ... Ethik ... Mäeutik ... ... 4 ... 2 ... ... 2125 ... 2125 ... where PersNr = gelesenVon and Titel = `Mäeutik‘ Monday, October 6, 2008 13 Projektion auf Ergebnisschema PersNr Name Rang Raum VorlNr Titel 2137 Sokrates C4 226 5001 Grundzüge gelesen SWS von 4 2137 select Name Name Sokrates Monday, October 6, 2008 14 Duplikate select Rang from Professoren • Vorsicht: SQL erzeugt ohne distinct immer Duplikate, im relationalen Modell nicht möglich • Korrekte SQL-Seite der Übersetzung also... Monday, October 6, 2008 15 Kanonische Übersetzung Allgemein hat eine (ungeschachtelte) SQLAnfrage die Form: Übersetzung in die relationale Algebra: Π A1, ..., An(σP (R1 x ... x Rk )) Π A1, ..., An σP select distinct A1, ..., An x from R1, ..., Rk x where P; x R1 Monday, October 6, 2008 Rk R3 R2 16 Namen in FROM-Klausel •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; •(Um-)Benennung der Relationen select s.Name, v.Titel from Studenten s, hören h,Vorlesungen v where s. MatrNr = h. MatrNr and h.VorlNr = v.VorlNr Monday, October 6, 2008 17 Mengenoperationen ( select Name from Assistenten ) union ( select Name from Professoren) •union Vereinigung •union all Vereinigung ohne Duplikateleminierung •intersect Durchschnitt •minus Mengendifferenz •Schemata müssen gleich sein! Monday, October 6, 2008 18 Aggregatfunktionen select avg (Semester) from Studenten min, max, avg, count, sum,... Monday, October 6, 2008 19 Gruppierung select gelesenVon, sum (SWS) from Vorlesungen group by gelesenVon; Monday, October 6, 2008 20 Filtern von Gruppen select gelesenVon, Name, sum (SWS) from Vorlesungen, Professoren where gelesenVon = PersNr and Rang = ´C4´ group by gelesenVon, Name having avg (SWS) >= 3; Monday, October 6, 2008 21 Besonderheiten bei Aggregatoperationen • • pro Gruppe ein Ergebnistupel alle Attribute in select-Klausel müssen auch in group by-Klausel stehen • stellt eindeutigen Wert pro Gruppe sicher Monday, October 6, 2008 22 Beispielauswertung from Professoren,Vorlesungen Vorlesungen Professoren PersNr Name Rang Raum VorlNr 2125 2126 Sokrates Russel C4 C4 226 232 5001 5041 Grundzüge Ethik 4 4 gelesen von 2137 2125 2127 Kopernikus C3 310 5043 Erkenntnistheorie 3 2126 2133 Popper C3 52 2134 Augustinus C3 309 5049 4052 Mäeutik Logik 2 4 2125 2125 2136 2137 Curie Kant C4 C4 36 7 Titel SWS ... × Monday, October 6, 2008 23 gelesen SWS von 4 2137 2 2125 PersNr Name Rang Raum VorlNr Titel 2125 2125 Sokrates Sokrates C4 C4 226 226 5001 5049 Grundzüge Mäeutik 2126 ... Russel ... C4 ... 232 ... 5001 ... Grundzüge ... 4 ... 2137 ... 2127 Kopernikus C3 310 5049 Mäeutik 2 2125 ... ... ... ... ... ... ... ... 2134 Augustinus C3 309 5001 Grundzüge 4 2137 2134 Augustinus C3 309 5041 Ethik 4 2125 ... 2136 ... 2137 ... ... Curie ... Kant ... ... C4 ... C4 ... ... 36 ... 7 ... ... 5041 ... 5049 ... ... Ethik ... Mäeutik ... ... 4 ... 2 ... ... 2125 ... 2125 ... where gelesenVon = PersNr and Rang = ´C4´ Monday, October 6, 2008 24 Gruppierung VorlN r Titel 5001 Grundzüge 5041 Ethik 5043 Erkenntnistheorie 5049 Mäeutik 4052 Logik 5052 Wissenschaftstheorie 5216 Bioethik 4630 Die 3 Kritiken SWS 4 4 3 2 4 3 2 4 gelesen PersNr von 2137 2125 2126 2125 2125 2126 2126 2137 2137 2125 2126 2125 2125 2126 2126 2137 Name Kant Sokrates Russel Sokrates Sokrates Russel Russel Kant Rang Raum C4 C4 C4 C4 C4 C4 C4 C4 7 226 232 226 226 232 232 7 group by gelesenVon, Name Monday, October 6, 2008 25 Gruppen filtern VorlN r Titel SWS 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 5216 Bioethik 5043 Erkenntnistheorie 5052 Wissenschaftstheorie 2 3 3 2126 2126 2126 2126 2126 2126 Russel Russel Russel C4 C4 C4 232 232 232 5001 4630 4 4 2137 2137 2137 2137 Kant Kant C4 C4 7 7 Grundzüge Die 3 Kritiken gelesen PersNr von Name Rang Raum having avg(SWS)>=2 VorlN r gelesen PersNr von Titel SWS 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 Name Rang Raum select gelesenVon, Name, sum(SWS) Monday, October 6, 2008 26 Ergebnis Monday, October 6, 2008 gelesenVon Name sum(SWS) 2125 Sokrates 10 2137 Kant 8 27 Sortieren select PersNr, Name, Rang from Professoren order by Rang desc, Name asc; PersNr 2136 2137 2126 2125 2134 2127 2133 Monday, October 6, 2008 Name Curie Kant Russel Sokrates Augustinus Kopernikus Popper Rang C4 C4 C4 C4 C3 C3 C3 28 Attributauswahl in SELECT select * from studenten select Titel, (SWS * 1.5) as ECTS from Vorlesungen Monday, October 6, 2008 29
