SQL> SELECT * FROM pruefung
Werbung
SQL 2 • Order by • null • Aggregatfunktionen • group by • Join • subselect Worzyk FH Anhalt Datenbanksysteme für FÜ WS04/05 SQL2 - 1 SQL> SELECT * FROM pruefung; NACHNAME ---------Lehmann Müller Lehmann Schmidt Bauer Schulze Huber Müller Müller Müller Schulze Schulze Maier Bauer Bäcker Müller Maier Köhler Bauer Worzyk FH Anhalt VORNAME ---------Hans Else Hans Hermann Jutta Anton Emma Jutta Anna Jutta Anton Anton Fritz Else Ewald Jutta Fritz Emil Else FACH -------------------Betriebssysteme Mathematik Diplomarbeit Diplomarbeit Datenorganisation Datenorganisation TI Diplomarbeit Diplomarbeit Mathematik DBS1 TI DBS1 TI Softwareprojekt DBS1 Datenorganisation DBS1 DBS1 DATUM NOTE FAKTOR -------- --------- --------03.01.00 1,7 1 01.10.98 1,3 1 01.10.98 2 01.10.98 4,7 2 01.12.98 2 1 25.10.98 2,3 1 25.10.98 3,3 1 25.10.99 2,7 2 25.10.98 2 2 01.09.99 3 1 03.09.99 3,3 1 03.09.99 2,3 1 05.09.99 2,3 1 06.09.99 4,7 1 30.09.99 30.09.99 01.10.98 2,3 1 01.10.99 2,7 1 01.10.99 2 1 1 Datenbanksysteme für FÜ WS04/05 SQL2 - 2 select SELECT command ::= SELECT * , DISTINCT ALL column t_alias. c_alias table. , FROM table t_alias WHERE condition , ORDER BY column ASC DESC Worzyk FH Anhalt Datenbanksysteme für FÜ WS04/05 SQL2 - 3 SQL> SELECT * FROM pruefung ORDER BY nachname, vorname, datum; NACHNAME VORNAME FACH DATUM NOTE FAKTOR Softwareprojekt 30.09.99 Bäcker Ewald 30.09.99 Bauer Jutta Datenorganisation 01.12.98 2 1 Bauer Else TI 06.09.99 4,7 1 Bauer Else DBS1 1 Huber Emma TI 25.10.98 3,3 1 Köhler Emil DBS1 01.10.99 2 1 Lehmann Hans Betriebssysteme 03.01.00 1,7 1 Lehmann Hans Diplomarbeit 01.10.98 2 Maier Fritz DBS1 05.09.99 2,3 1 Maier Fritz Datenorganisation 01.10.99 2,7 1 Müller Jutta DBS1 01.10.98 2,3 1 Müller Jutta Mathematik 01.09.99 3 1 Müller Jutta Diplomarbeit 25.10.99 2,7 2 Müller Else Mathematik 01.10.98 1,3 1 Müller Anna Diplomarbeit 25.10.98 2 2 Schmidt Hermann Diplomarbeit 01.10.98 4,7 2 Schulze Anton Datenorganisation 25.10.98 2,3 1 Schulze Anton DBS1 03.09.99 3,3 1 Schulze Anton TI 03.09.99 2,3 1 Worzyk FH Anhalt Datenbanksysteme für FÜ WS04/05 SQL2 - 4 null • Markierung für nicht vorhandene Attributwerte • Nullmarken können verschiedene Bedeutungen haben: – Der Wert ist nicht bekannt – Der Wert ist nicht eingegeben worden • führen zu einer dreiwertigen Logik Worzyk FH Anhalt Datenbanksysteme für FÜ WS04/05 SQL2 - 5 null Oracle8 SQL Reference, Release 8.0 Any arithmetic expression containing a null always evaluates to null. All scalar functions (except NVL and TRANSLATE) return null when given a null argument. Most group functions ignore nulls. Worzyk FH Anhalt Datenbanksysteme für FÜ WS04/05 SQL2 - 6 null SQL> 2 3 4 5 SELECT nachname, vorname, fach, note FROM pruefung WHERE fach = 'Diplomarbeit' AND (note<2 or note>= 2) ORDER BY note; NACHNAME ---------Müller Müller Schmidt Worzyk FH Anhalt VORNAME ---------Anna Jutta Hermann FACH NOTE -------------------- --------Diplomarbeit 2 Diplomarbeit 2,7 Diplomarbeit 4,7 Datenbanksysteme für FÜ WS04/05 SQL2 - 7 null SQL> SELECT nachname, vorname, fach 2 FROM pruefung 3 WHERE note IS NULL; NACHNAME ---------Lehmann Bäcker Bauer Worzyk FH Anhalt VORNAME FACH ---------- -------------------Hans Diplomarbeit Ewald Softwareprojekt Else DBS1 Datenbanksysteme für FÜ WS04/05 SQL2 - 8 Anzeigen von Nullmarken SQL> SELECT NVL(nachname,'Kein Wert'), 2 NVL(vorname,'Kein Wert') 3 FROM pruefung; NVL(NACHNA ---------Maier Bauer Bäcker Müller Maier Worzyk FH Anhalt NVL(VORNAM ---------Fritz Else Ewald Kein Wert Jutta Fritz Datenbanksysteme für FÜ WS04/05 SQL2 - 9 Anzeigen von Nullmarken SQL> SELECT '!' || nachname || '!' || vorname || '!' 2 FROM pruefung; '!'||NACHNAME|| '!'||VOR ----------------------!Maier!Fritz! !Bauer!Else! !Bäcker!Ewald! ! !! !Müller!Jutta! !Maier!Fritz! Worzyk FH Anhalt Datenbanksysteme für FÜ WS04/05 SQL2 - 10 Aggregatfunktionen avg count max min sum Worzyk FH Anhalt Durschnitt Anzahl M aximum M inimum Summe SQL Standard ja ja ja ja ja Datenbanksysteme für FÜ WS04/05 SQL2 - 11 count SELECT COUNT (*) – Anzahl aller Zeilen SELECT COUNT (expr) – Anzahl aller Zeilen, die einen Wert in der angegebenen Spalte haben SELECT COUNT (DISTINCT expr) – Anzahl aller unterschiedlichen Zeilen, die einen Wert in der angegebenen Spalte haben Worzyk FH Anhalt Datenbanksysteme für FÜ WS04/05 SQL2 - 12 count SQL> SELECT COUNT(*), COUNT(note), 2 COUNT(DISTINCT note) 3 FROM pruefung; COUNT(*) COUNT(NOTE) COUNT(DISTINCTNOTE) --------- ----------- ------------------20 16 8 Worzyk FH Anhalt Datenbanksysteme für FÜ WS04/05 SQL2 - 13 Auswerten von Nullmarken SQL> SELECT AVG(note), SUM(note)/COUNT(*) 2 FROM pruefung; AVG(NOTE) SUM(NOTE)/COUNT(*) --------- -----------------2,6625 2,13 Worzyk FH Anhalt Datenbanksysteme für FÜ WS04/05 SQL2 - 14 Group by Group by kennzeichnet die Zeilen einer Spalte, die durch eine Aggregatfunktion zusammengefaßt werden sollen. Worzyk FH Anhalt Datenbanksysteme für FÜ WS04/05 SQL2 - 15 select SELECT command ::= SELECT * , DISTINCT ALL column t_alias. c_alias table. , FROM table t_alias WHERE condition , GROUP BY expr , ORDER BY Worzyk FH Anhalt column ASC DESC Datenbanksysteme für FÜ WS04/05 SQL2 - 16 group by Wieviel Prüfungen wurden pro Fach durchgeführt? SQL> SELECT fach, COUNT(note) "Anzahl Pruefungen" 2 FROM pruefung 3 GROUP BY fach; FACH Anzahl Pruefungen -------------------- ----------------Betriebssysteme 1 DBS1 4 Datenorganisation 3 Diplomarbeit 3 Mathematik 2 Softwareprojekt 0 TI 3 0 Worzyk FH Anhalt Datenbanksysteme für FÜ WS04/05 SQL2 - 17 group by Wie lautet der Notendurchschnitt pro Fach? SQL> SELECT fach, AVG(note) "Durchschnitt" 2 FROM pruefung 3 GROUP BY fach 4 ORDER BY "Durchschnitt"; FACH Durchschnitt -------------------- -----------Betriebssysteme 1,7 Mathematik 2,15 Datenorganisation 2,3333333 DBS1 2,475 Diplomarbeit 3,1333333 TI 3,4333333 Softwareprojekt Worzyk FH Anhalt 8 Zeilen ausgewählt. Datenbanksysteme für FÜ WS04/05 SQL2 - 18 group by Wie lautet der Notendurchschnitt pro Fach im Jahr 1999? SQL> SELECT fach, AVG(note) "Durchschnitt" 2 FROM pruefung 3 WHERE datum > '01.01.99' 4 GROUP BY fach 5 ORDER BY "Durchschnitt"; FACH Durchschnitt -------------------- -----------DBS1 2,5333333 Datenorganisation 2,7 Diplomarbeit 2,7 Mathematik 3 TI 3,5 Softwareprojekt Worzyk FH Anhalt 7 Zeilen ausgewählt. Datenbanksysteme für FÜ WS04/05 SQL2 - 19 Relationenalgebra Produkt Das Produkt der Tabellen T1 und T2 hat die Attribute von T1 und T2. Jedes Tupel von T1 wird mit jedem Tupel von T2 verbunden. Worzyk FH Anhalt Datenbanksysteme für FÜ WS04/05 SQL2 - 20 name Richter Lange Worzyk nummer 852 140 119 Name Richter Richter Richter Lange Lange Lange Worzyk Worzyk Worzyk Worzyk FH Anhalt Relationen Produkt Nummer 852 852 852 140 140 140 119 119 119 Lehrgebiet Englisch Betriebssysteme Datenbanksysteme Lehrgebiet Englisch Betriebssysteme Datenbanksysteme Englisch Betriebssysteme Datenbanksysteme Englisch Betriebssysteme Datenbanksysteme Datenbanksysteme für FÜ WS04/05 SQL2 - 21 Kreuzprodukt SQL> SELECT * FROM belegung; NACHNAME VORNAME VORLESUNG ---------- ---------- --------------Müller Jutta Mathematik Köhler Emil DBS1 Maier Fritz Programmierung Worzyk FH Anhalt SQL> SELECT * FROM stuga; NACHNAME VORNAME STUDIENGANG ---------- ---------- --------------Müller Jutta Informatik Köhler Emil Fachübersetzen Maier Fritz InfManagement Datenbanksysteme für FÜ WS04/05 SQL2 - 22 Kreuzprodukt SQL> SELECT vorlesung, studiengang 2 FROM belegung, stuga; VORLESUNG --------------Mathematik DBS1 Programmierung Mathematik DBS1 Programmierung Mathematik DBS1 Programmierung Worzyk FH Anhalt STUDIENGANG --------------Informatik Informatik Informatik Fachübersetzen Fachübersetzen Fachübersetzen InfManagement InfManagement InfManagement Datenbanksysteme für FÜ WS04/05 SQL2 - 23 Kreuzprodukt SQL> SELECT vorlesung, studiengang 2 FROM stuga, belegung; VORLESUNG --------------Mathematik Mathematik Mathematik DBS1 DBS1 DBS1 Programmierung Programmierung Programmierung Worzyk FH Anhalt STUDIENGANG --------------Informatik Fachübersetzen InfManagement Informatik Fachübersetzen InfManagement Informatik Fachübersetzen InfManagement Datenbanksysteme für FÜ WS04/05 SQL2 - 24 Namenskonventionen Tabellen werden in Zukunft immer den Präfix „ta_“ haben. alt belegung stuga Worzyk FH Anhalt neu ta_belegung ta_stuga Datenbanksysteme für FÜ WS04/05 SQL2 - 25 equijoun SQL> 2 3 4 5 SELECT ta_stuga.nachname, ta_stuga.vorname, ta_stuga.studiengang, ta_belegung.vorlesung FROM ta_stuga, ta_belegung WHERE ta_stuga.nachname = ta_belegung.nachname AND ta_stuga.vorname = ta_belegung.vorname; NACHNAME ---------Köhler Maier Müller Worzyk FH Anhalt VORNAME ---------Emil Fritz Jutta STUDIENGANG --------------Fachübersetzen InfManagement Informatik VORLESUNG -------------DBS1 Programmierung Mathematik Datenbanksysteme für FÜ WS04/05 SQL2 - 26 select SELECT command ::= SELECT * , DISTINCT ALL column t_alias. c_alias table. , FROM table t_alias WHERE condition , GROUP BY expr HAVING condition , ORDER BY Worzyk FH Anhalt column ASC DESC Datenbanksysteme für FÜ WS04/05 SQL2 - 27 equijoun SQL> 2 3 4 5 SELECT s.nachname, s.vorname, s.studiengang, b.vorlesung FROM ta_stuga s, ta_belegung b WHERE s.nachname = b.nachname AND s.vorname = b.vorname; NACHNAME ---------Köhler Maier Müller Worzyk FH Anhalt VORNAME ---------Emil Fritz Jutta STUDIENGANG --------------Fachübersetzen InfManagement Informatik VORLESUNG --------------DBS1 Programmierung Mathematik Datenbanksysteme für FÜ WS04/05 SQL2 - 28 Selektion Beispiel Person Vorlesung Hans M athematik Emil Programmierung Person Emil Anna Jutta M athematik Fritz Fritz DBS1 Else Anna Else M athematik DBS1 Hans Jutta Studiengang Studiengang Informatik FachüElektrotechnik bersetzen InfM anage- InfM anagement ment Informatik Fachübersetzen Informatik M aschinenbau Informatik wie heißen die Informatiker in der Mathematik-Vorlesung wie lang ist die durchschnittliche Studiendauer der DBS1-Hörer Worzyk FH Anhalt Datenbanksysteme für FÜ WS04/05 SQL2 - 29 Regel 9 11 10 8 equijoin wie heißen die Informatiker in der Mathematik-Vorlesung SQL> SELECT b.vorname, b.nachname, s.studiengang 2 FROM ta_belegung b, ta_stuga s 3 WHERE b.vorname = s.vorname 4 AND b.nachname = s.nachname 5 AND b.vorlesung = 'Mathematik' 6 AND s.studiengang = 'Informatik'; VORNAME NACHNAME STUDIENGANG ---------- ---------- --------------Hans Lehmann Informatik Jutta Müller Informatik Worzyk FH Anhalt Datenbanksysteme für FÜ WS04/05 SQL2 - 30 equijoin wie lang ist die durchschnittliche Studiendauer der DBS1-Hörer SQL> SELECT b.vorlesung, avg(d.dauer) 2 FROM ta_belegung b, ta_stuga s, ta_dauer d 3 WHERE b.vorname = s.vorname 4 AND b.nachname = s.nachname 5 AND s.studiengang = d.studiengang 6 AND b.vorlesung = 'DBS1' 7 GROUP BY b.vorlesung; VORLESUNG AVG(D.DAUER) --------------- -----------DBS1 9,5 Worzyk FH Anhalt Datenbanksysteme für FÜ WS04/05 SQL2 - 31 selfjoin SQL> SELECT * FROM ta_personal; PERS# NAME --------10 11 12 13 14 100 Worzyk FH Anhalt ---------Jutta Emil Fritz Hans Hermann Else ABTEILUNG CHEF --------------- --------DV 14 DV 14 Management 100 Entwicklung 12 DV 12 Datenbanksysteme für FÜ WS04/05 SQL2 - 32 selfjoin Welcher Chef hat welche Mitarbeiter? SQL> SELECT p1.name "Chef", p2.name "Mitarbeiter" 2 from ta_personal p1, 3 ta_personal p2 4 where p1.pers# = p2.Chef 5 ORDER BY p1.name; Chef Mitarbeite ---------- ---------Else Fritz Fritz Hans Fritz Hermann Hermann Jutta Hermann Emil Worzyk FH Anhalt Datenbanksysteme für FÜ WS04/05 SQL2 - 33 Zusammenfassung • • • • order by sortiert ungeordnete Zeilen group by bildet Teilmengen für Aggregatfunktionen join verbindet mehrere Tabellen selfjoin verbindet eine Tabelle mit sich selbst Worzyk FH Anhalt Datenbanksysteme für FÜ WS04/05 SQL2 - 34
