WS 2014/15 Datenbanksysteme D0 15:15 – 16:45 R 4.080 Vorlesung #6 SQL (Teil 3)
-
Upload
gervas-rebstock -
Category
Documents
-
view
222 -
download
0
Transcript of WS 2014/15 Datenbanksysteme D0 15:15 – 16:45 R 4.080 Vorlesung #6 SQL (Teil 3)
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
Vorlesung #6
SQL (Teil 3)
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
„Fahrplan“ Wiederholung Korrelierte vs. Unkorrelierte Anfragen Entschachtelung der Anfragen Operationen der Mengenlehre Spezielle Sprachkonstrukte (BETWEEN,
CASE, LIKE)
2
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Nullwerte UPDATE studenten SET semester = '' WHERE matrnr = 24002;
select count (*) from Studentenwhere Semester < 13 or Semester >= 13
Tupel (Zeilen) mit Null-Werten werden einfach nicht mitgezählt !!!
3
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Nullwerte (2) NULL + 1 = NULL, NULL * 1 = NULL Beispiele (Oracle)SELECT NULL + 2 FROM Dual;
SELECT NULL * 2 FROM Dual;
Dual ist so etwas wie „Dummy“-Tabelle in Oracle Es gilt die sogenannte 3-wertige Logik in SQL
4
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Behandlung von NULL Werten (Oracle und SQL-92) In Oracle gibt es eine „null value“ Funktion nvl (if
NULL then):SELECT nvl(NULL,5) + 2 AS Result FROM Dual;
Result ------ 7
Man kann NULL Werte mit IS NULL oder IS NOT NULL testen (SQL-92)
5
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Geschachtelte Anfragen (2) ... in WHERE-Klausel Welche Prüfungen sind besser als
durchschnittlich verlaufen?
SELECT *FROM prüfen
WHERE Note < ( select avg (Note) from prüfen );
6
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Geschachtelte Anfragen (3) ... in SELECT-Klausel Professoren mit deren Lehrbelastung und
durchschnittlicher Lehrbelastung
SELECT PersNr, Name, sum(SWS) AS Lehrbelastung, ( select avg (sum(SWS))
from Vorlesungen group by gelesenVon) AS Durchschnitt FROM Professoren p, Vorlesungen vWHERE p.PersNr = v.gelesenVonGROUP BY PersNr, Name;
7
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Geschachtelte Anfragen (4) ... in SELECT-Klausel Professoren mit deren Lehrbelastung und
durchschnittlicher Lehrbelsatung – korreliert ohne GROUP BY:
SELECT PersNr, Name, (select sum (SWS) from Vorlesungen
where gelesenVon = p.PersNr) AS Lehrbelastung, (select sum(SWS) / count(DISTINCT gelesenVon) from Vorlesungen) AS Durchschnitt FROM Professoren p
8
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Geschachtelte Anfragen (5) ... in FROM-Klausel „fleißige“ Studenten – die mehr als 2 Vorlesungen
hören:SELECT tmp.MatrNr, tmp.Name, tmp.VorlAnzahl FROM (select s.MatrNr, s.Name, count(*) as VorlAnzahl from Studenten s, hoeren h where s.MatrNr=h.MatrNr group by s.MatrNr, s.Name) tmpWHERE tmp.VorlAnzahl > 2;
9
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Korreliert vs. unkorreliert Achtung: funktioniert in der Original-Beispiel-
Datenbank UNI nicht, da GebDatum fehlt! Alle Studenten, die älter als der jüngste
Professor sind - korrelierte Formulierungselect s.*from Studenten swhere exists(select p.*from Professoren pwhere p.GebDatum > s.GebDatum);
10
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Korreliert vs. Unkorreliert (2) Äquivalente unkorrelierte Formulierung
select s.*from Studenten swhere s.GebDatum <
(select max (p.GebDatum) from Professoren p);
Vorteil: Unteranfrageergebnis kann materialisiert werden
Unteranfrage braucht nur einmal ausgewertet zu werden
11
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Entschachtelung korrelierter Unteranfragen
Assistenten, die für eine(n) jüngere(n) Professor(in) arbeiten
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;
12
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Operationen der Mengenlehre
Vereinigung – UNION bzw. UNION ALL Durchschnitt – INTERSECT Differenz – MINUS (auch EXCEPT) UNION, INTERSECT und MINUS setzen
Schemagleichheit voraus Der Operator IN bzw. NOT IN testet auf
Mengenmitgliedschaft Der Operator ALL testet, ob alle Ergebnisse
der Unteranfrage den Vergleich erfüllen
13
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Operationen der Mengenlehre (2)
• UNION – mit Duplikatelimierung, • UNION ALL – ohne Duplikateliminierung
( select Name from Assistenten )
union ( select Name
from Professoren );
14
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Operationen der Mengenlehre (3)
Mengendurchschnitt – INTERSECT Alle Professoren, die eine Vorlesung halten
SELECT PersNr FROM ProfessorenINTERSECT SELECT gelesenVon FROM Vorlesungen
15
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Operationen der Mengenlehre (4)
Gleiche Abfrage, „alle Professoren, die eine Vorlesung halten“ mit IN Operator
SELECT PersNr FROM Professoren WHERE PersNr IN (SELECT gelesenVon
FROM Vorlesungen);
16
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Operationen der Mengenlehre (5)
Mengendifferenz – MINUS Alle Professoren, die keine Vorlesung halten
SELECT PersNr FROM ProfessorenMINUS SELECT gelesenVon FROM Vorlesungen;
17
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Operationen der Mengenlehre (6)
Gleiche Abfrage, „alle Professoren, die keine Vorlesung halten“ mit NOT IN
SELECT PersNr FROM Professoren WHERE PersNr NOT IN
( SELECT gelesenVon FROM Vorlesungen);
18
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
ALL Operator
Studenten mit der größten Semesterzahl
SELECT Name, Semester FROM Studenten WHERE Semester >= ALL (select Semester from
Studenten);äquivalent
SELECT Name, Semester FROM Studenten WHERE Semester >= (select max(Semester)
from Studenten);
19
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Spezielle Sprachkonstrukte
BETWEEN
select * from Studenten
where Semester > = 1 and Semester < = 4;
select * from Studenten
where Semester between 1 and 4;
select * from Studenten where Semester in (1,2,3,4);
20
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Spezielle Sprachkonstrukte (2)
CASE WHEN ... THEN ... ELSE ... END – die erste qualifizierende WHEN Klausel wird ausgeführt
select MatrNr,
( case when Note < 1.5 then ´sehr gut´
when Note < 2.5 then ´gut´
when Note < 3.5 then ´befriedigend´
when Note < 4.0 then ´ausreichend´
else ´nicht bestanden´end)
from pruefen;
21
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Spezielle Sprachkonstrukte (3)
LIKE Operator – Vergleich von Zeichenketten "%" steht für beliebig viele (auch gar kein) Zeichen "_" steht für genau ein Zeichen
select * from Studentenwhere Name like ´T%eophrastos´;
select distinct Namefrom Vorlesungen v, hören h, Studenten swhere s.MatrNr = h.MatrNr and h.VorlNr = v.VorlNr and
v.Titel LIKE ´%thik%´;
22
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
© Bojan Milijaš, 06.11.2014 Vorlesung #6 - SQL (Teil 3)
Ausblick Vorlesung #6 Quantifizierte Anfragen Joins in SQL 92 Rekursion
Rekursion in SQL-92 Rekursion in DBMS-“Dialekten“ (Oracle und DB2)
Views (Sichten) - gespeicherte Abfragen Gewährleistung der logischen Datenunabhängigkeit Modellierung von Generalisierung UPDATE-fähige Sichten
Datenintegrität Statische und dynamische Bedingungen Referentielle Integrität (primary key, foreign key)
23
WS 2014/15Datenbanksysteme
D0 15:15 – 16:45R 4.080
Vorlesung #6
Ende