Kapitel 4 (Teil 2/3)

*HVFKDFKWHOWH$QIUDJHQ
‰ In einer SQL-Anweisung können in der
– where-Klausel,
– from-Klausel,
–
select-Klausel
wieder SQL-Anweisungen auftreten. Man spricht dann auch von einer geschachtelten
Anfrage oder Unteranfrage.
‰ Bei Unteranfragen werden häufig 7XSHOYDULDEOHQ benutzt.
– Eine Tupelvariable wird in der from-Klausel deklariert und dabei einer Relation
zugeordnet.
–
Tupelvariablen sind dann erfoderlich, wenn die gleiche Relation mehrfach in einer
Anfrage benutzt wird.
Welche Kunden haben die gleiche Ware bestellt?
select distinct a1.Name, a2.Name
from Auftrag a1, Auftrag a2
where a1.Ware = a2.Ware
Seite 123 von 135
8QWHUDQIUDJHLQGHUIURP.ODXVHO
‰ Innerhalb einer from-Klausel kann statt einer Relation eine Unteranfrage benutzt werden.
–
Dann ist es ebenfalls möglich, eine Tupelvariable an diese Unteranfrage zu binden.
‰ Bestimmte Anfragen lassen sich erst über solche Unterfragen formulieren:
Beispiel
– Wieviel Stück werden von einer Ware im Durchschnitt bestellt?
select avg(t.summe)
from (select Ware, count(*) as summe from Auftrag group by Ware) t
‰ Die having-Klausel kann dann auch durch eine äquivalente where-Klausel ersetzt werden.
Anfrage mit having-Klausel
select A, agg(B) as C
from R
where FW
group by A
having FH(C)
äquivalente Anfrage ohne having-Klausel
select *
from ( select A, agg(B) as C
from R
where FW
group by A) t
where FH(t.C)
Seite 124 von 135
8QWHUDQIUDJHQLQGHUVHOHFW.ODXVHO
‰ Einige Datenbanksysteme unterstützen auch Unteranfragen in der select-Klausel (z. B.
SQL Server):
–
Damit ist es möglich nach einem Attribut zu gruppieren und gleichzeitig im
Aggregat verschiedene where-Bedingungen zu berücksichtigen.
–
Beispiel:
select Ware,
(select count(*) from Auftrag A where A.Ware = B.Ware and Menge > 20),
(select count(*) from Auftrag A where A.Ware = B.Ware and Menge <= 20)
from Auftrag B
‰ Damit folgt daraus, dass eine Anfrage mit einer group-by-Klausel in eine äquivalente
Anfrage ohne diese Klausel umgewandelt werden kann.
Seite 125 von 135
8QWHUDQIUDJHQLQGHUZKHUH.ODXVHO
‰ In der where-Klausel wird dabei noch unterschieden, ob das Resultat der Unteranfrage
einen skalaren Wert oder eine Relation zurückliefert. Wir betrachten zunächst den ersten
Fall:
Skalare Unteranfragen
‰ Welche Lieferanten liefern Lampen, deren Preis 50% unter dem Durschnittspreis für
Lampen liegen?
select LName, Preis
from Lieferant
where Ware = 'Lampe' and Preis < (
select avg(Preis)/2
from Lieferant
where Ware = 'Lampe')
Skalare Unteranfragen mit Exists
‰ In der where Klausel werden auch Unteranfragen erlaubt, die einen Booleschen Wert
zurückliefern. Diese sind durch das Schlüsselwort exists gekennzeichnet. Dabei ist die
Bedingung
–
exists <Subquery>
wahr, falls die Unteranfrage nicht leer ist.
Seite 126 von 135
‰ Welche Lieferanten liefern irgendetwas, das Huber bestellt hat?
select distinct LName
from Lieferant L
where exists (select Ware
from Auftrag
where L.Ware = Ware and KName = ’Huber’)
Gültigkeit von Tupelvariablen in Unteranfragen
‰ Bei dieser Unteranfrage wird Bezug genommen auf eine Tupelvariable, die in der
äußeren Anfrage definiert wurde. Bei der Auswertung der Anfrage wird entsprechend so
wie beim Tupelkalkül vorgegangen (“von außen nach innen”). Man spricht dann auch
von einer NRUUHOLHUWHQ8QWHUDQIUDJH.
–
Eine XQNRUUHOLHUWH8QWHUDQIUDJH braucht nur einmal ausgewertet zu werden.
Dagegen muss eine korrelierte Anfrage für jedes äußere Tupel ausgewertet werden.
‰ Eine Tupelvariable ist in allen zugehörigen Unteranfragen gültig. Eine Redeklaration der
gleichen Tupelvariable überdeckt die äußere Deklaration der Variablen.
–
Sucht man die Deklaration einer Tupelvariable geht man also von innen nach außen.
Es wird die Deklaration benutzt, die zuerst gefunden wird (und alle anderen
ignoriert).
Seite 127 von 135
0HQJHQZHUWLJH8QWHUDQIUDJHQ
‰ Durch Verwendung vom Schlüsselwort in kann getestet werden, ob ein Attribut einen
Wert in einer Menge annimmt (oder auch nicht)
select KName, KAdr
from Kunde
where Kunde in
(select KName from Auftrag)
Wie kann die Anfrage formuliert werden, ohne dabei eine Unteranfrage zu benutzen?
‰ Soll nun getestet werden, ob ein Attribut mit allen Elementen einer Menge in einer
bestimmten Beziehung steht, kann das Schlüsselwort all benutzt werden.
Suche für alle Waren die Namen der günstigsten Lieferanten!
select LName, Ware
from Lieferant L
where Preis <= all(
select Preis
from Lieferant
where Ware = L.Ware)
Seite 128 von 135
Differenz durch Unteranfragen
‰ Eine Differenz zwischen Relationen kann über except ausgedrückt werden (wird nicht
von allen DBMS unterstützt) oder durch eine Unteranfrage mit not in und not exists.
‰ Beispiele:
Berechne alle Kunden, die derzeit keine Waren bestellt haben.
select *
from Kunde
where Kname not in (select Kname from Auftrag)
RGHU
select *
from Kunde K
where not exists (select KName from Auftrag where KName = K.KName)
Seite 129 von 135
Anfragen mit Allquantoren
‰ Da [ \ [ œ ™[ ™\ [ , können alle Anfragen mit einem Allquantor in
äquivalente Anfragen umgeformt werden, die nur noch Existenzquantoren benutzen.
‰ Welche Lieferanten liefern alles, was Bercken bestellt hat?
– Anfrage im Tupelkalkül:
{t{LName} | forall(u in Auftrag) u.KName = ’Bercken’ ex(v in Lieferant) v.Ware =
u.Ware und v.LName = t.LName}
– Anfrage in SQL
select distinct LName
from Lieferant L
where not exists( select Ware
from Auftrag
KName = 'Bercken' and
where
not Ware in (
select Ware
from Lieferant
where LName =L.LName))
Seite 130 von 135
$OOTXDQWLIL]LHUWH$QIUDJHQLQ64/
‰ Anfrage:
– Welche Kunden haben alle Waren bestellt?
– SQL
select *
from Auftrag
group by KName
having count(*) = (select count(*) from (select distinct Ware from Auftrag))
Seite 131 von 135
bQGHUQHLQHU5HODWLRQHQLQVWDQ]
‰ Tupelweises Einfügen:
insert
into <Relationen-Name> [(<Attributname> [, <Attributname>]*)]
values (<Konstante> [, Konstante]*)
oder mengenweises Einfügen
insert
into <Relationen-Name> [(<Attributname> [, <Attributname>]*)]
select ... from ... where ....
‰ Löschen:
delete
from <Relationen-Name>
[where <Bedingung>]
‰ Verändern:
update <Relationen-Name>
set <Attributname> = <Ausdruck> [, <Attributname> = <Ausdruck>]*
[where <Bedingung>]
Seite 132 von 135
%HLVSLHOH
‰ Füge den Kunden Zeitler mit dem Kontostand 0 DM ein!
insert into Kunde (KName, Kto)
values ('Zeitler', 0)
/* KAdresse wird automatisch auf den Defaultwertauf, in diesem Fall null, gesetzt */
‰ Erhöhe den Kontostand von Zeitler um 200!
update Kunde
set Kto = Kto + 200
where KName = 'Zeitler'
Seite 133 von 135
5HNXUVLRQ
Motivation
‰ In unserer Datenbank können Kunden auch wieder Lieferanten für andere Kunden sein.
Das ist in der Praxis sehr oft der Fall!
‰ Folgende Anfrage soll dann unterstützt werden:
Berechne alle Lieferanten, die Waren an VW liefern können:
select LName
from Auftrag A, Lieferant L
where A.Ware = L.Ware and A.KName = ’VW
Jetzt sollen noch die potentiellen Lieferanten der Lieferanten berechnet werden:
select LName
from Lieferant L, Auftrag A
where A.Ware = L.Ware and
KName in
(select LName from Auftrag A, Lieferant L
where A.Ware = L.Ware and A.KName = ’VW)
‰ Die Berechnung aller direkten und indirekten potentiellen Lieferanten von VW kann mit
den bisher vorgestellten Mitteln jedoch in SQL nicht formuliert werden.
Seite 134 von 135
5HNXUVLRQLQ2UDFOH
‰ Erst im SQL3-Standard wurden Möglichkeiten geschaffen, rekursive Anfragen zu
formulieren.
–
Wir werden später auf die spezielle Syntax von SQL 3 eingehen.
‰ Zuvor gab es für spezielle rekursive Anfragen verschiedene Insellösungen von den
Herstellern, wie z. B. eine Tiefensuche in Oracle:
–
In der Klausel start with wird der Ausgangspunkt der Tiefensuche angegeben, in
unserem Fall A.KName = ’VW’
–
Hinter der Klausel connect by wird angegeben, wie bei der Tiefensuche der
Vorgänger mit dem Nachfolger (über das Schlüsselwort prior) verbunden wird.
In Oracle könnte dann obige Anfrage folgendermaßen formuliert werden:
select LName
from Lieferant L join Auftrag A on L.Ware = A.Ware
connect by KName = prior LName
start with KName = ’VW’
Seite 135 von 135