SS 2004 Datenbanken 4W Mi 13:30 – 15:00 G 2.30 Vorlesung #7 SQL (Teil 2)

SS 2004Datenbanken 4WMi 13:30 – 15:00

G 2.30

Vorlesung #7

SQL (Teil 2)

© Bojan Milijaš, 28.04.2004 Vorlesung #7 - SQL (Teil 2) 2


G 2.30„Fahrplan“

Besprechung der Übungsaufgaben Geschachtelte Anfragen in SQL Korrelierte vs. Unkorrelierte Anfragen Entschachtelung der Anfragen Operationen der Mengenlehre Spezielle Sprachkonstrukte (BETWEEN,

CASE, LIKE) Joins in SQL-92



G 2.30Geschachtelte Anfragen

In SQL ist es möglich, SELECT Anweisungen auf viele Weisen zu verknüpfen und zu verschachteln

Man unterscheidet zwischen Anfragen, die ein Tupel (eine Zeile) zurückliefern, von denen, die mehrere Tupeln (Zeilen) ergeben

Die Anfragen können dann als Unterfragen in SELECT, FROM oder WHERE Teil eingesetzt werden



G 2.30Geschachtelte 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 );




... in SELECT-Klausel Professoren mit deren Lehrbelastung und

durchschnittlicher Lehrbelsatung

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;




... 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




... 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) tmp

WHERE tmp.VorlAnzahl > 2;



G 2.30Korreliert 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 Formulierung

select s.*from Studenten swhere exists

(select p.*from Professoren pwhere p.GebDatum >

s.GebDatum);



G 2.30Korreliert vs. Unkorreliert (2)

Ä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



G 2.30

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;



G 2.30Operationen 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



G 2.30Operationen der Mengenlehre (2)

• UNION – mit Duplikatelimierung, • UNION ALL – ohne Duplikateliminierung

( select Name

from Assistenten )

union

( select Name

from Professoren );




Mengendurchschnitt – INTERSECT Alle Professoren, die eine Vorlesung halten

SELECT PersNr

FROM Professoren

INTERSECT

SELECT gelesenVon

FROM Vorlesungen




Gleiche Abfrage, „alle Professoren, die eine Vorlesung halten“ mit IN Operator

SELECT PersNr

FROM Professoren

WHERE PersNr IN (SELECT gelesenVon

FROM Vorlesungen);




Mengendifferenz – MINUS Alle Professoren, die keine Vorlesung halten

SELECT PersNr

FROM Professoren

MINUS

SELECT gelesenVon

FROM Vorlesungen;




Gleiche Abfrage, „alle Professoren, die keine Vorlesung halten“ mit NOT IN

SELECT PersNr

FROM Professoren

WHERE PersNr NOT IN

( SELECT gelesenVon

FROM Vorlesungen);



G 2.30ALL 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);



G 2.30Spezielle Sprachkonstrukte

BETWEEN

select * from Studenten

where Semester > = 1 and Semester < = 4;


where Semester between 1 and 4;

select * from Studenten where Semester in (1,2,3,4);



G 2.30Spezielle 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 áusreichend´

else ńicht bestandenénd)

from pruefen;



G 2.30Spezielle Sprachkonstrukte (3)

LIKE Operator – Vergleich von Zeichenketten "%" steht für beliebig viele (auch gar kein) Zeichen "_" steht für genau ein Zeichen


where Name like ´T%eophrastos´;

select distinct Name

from Vorlesungen v, hören h, Studenten s

where s.MatrNr = h.MatrNr and h.VorlNr = v.VorlNr and

v.Titel LIKE ´%thik%´;



G 2.30Quantifizierte Anfragen in SQL

• Es gibt keinen expliziten Allquantor

• Es gibt aber den Existenzquantor: exists, not exist

• wieder: „Professoren, die keine Vorlesung halten“

select Name, PersNr

from Professoren p

where not exists

( select *

from Vorlesungen

where gelesenVon = p.PersNr );



G 2.30



G 2.30

Quantifizierte Anfragen in SQL (5)• Wer hat alle vierstündigen Vorlesungen gehört?

select s.*

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 ) );



G 2.30

Quantifizierte Anfragen in SQL (4)

• 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);



G 2.30JOINs in SQL-92

cross join: Kreuzprodukt natural join: natürlicher Join join oder inner join: Theta-Join left, right oder full outer join: äußerer Join

select *

from R1, R2

where = R1.A = R2.B;

select *

from R1 join R2 on R1.A = R2.B;



G 2.30



G 2.30JOINs in SQL-92

FULL OUTER JOIN

select p.PersNr, p.Name, f.PersNr, f.Note, f.MatrNr, s.MatrNr, s.Name

from Professoren p full outer join

(pruefen f full outer join Studenten s

on f.MatrNr= s.MatrNr)

on p.PersNr=f.PersNr;


G 2.30

Vorlesung #7

Ende

SS 2004 Datenbanken 4W Mi 13:30 – 15:00 G 2.30 Vorlesung #7 SQL (Teil 2)

Documents

Transcript of SS 2004 Datenbanken 4W Mi 13:30 – 15:00 G 2.30 Vorlesung #7 SQL (Teil 2)