Erg ¨anzungen zu SQL mit Oracle
von
Prof. Dr. Rainer Schwenkert Hochschule M ¨unchen
c Vervielf ¨altigung nur mit Zustimmung des Autors
SELECT u.Name, u.Vorname FROM Chef u, Chef v
WHERE (u.vorg = v.pnr) AND (u.Gehalt > v.Gehalt);
Chef u Chef v
PNR NAME u.GEHALT u.VORG v.PNR NAME v.GEHALT VORG
100 Stocker 3 111 111 Meier 5 111
500 M ¨uller 4 333 333 Wenzel 6 333
111 Meier 5 111 111 Meier 5 111
102 Huber 7 222 222 Stoer 3 222
222 Stoer 3 222 222 Stoer 3 222
333 Wenzel 6 333 333 Wenzel 6 333
112 J ¨ager 5 111 111 Meier 5 111
444 M ¨uller 6 333 333 Wenzel 6 333
R. Schwenkert - HM Oktober 2013 Seite 60, i
Korrelierte Unterabfragen (Correlated Subqueries)
Korrelierte Unterabfragen
• erkennt man daran, dass in ihnen mindestens ein Spaltenname aus der
¨außeren Abfrage auftritt. Diese Spalte nennt man Korrelationsvariable.
• sind wegen der Korrelationsvariablen f ¨ur sich alleine nicht ausf ¨uhrbar.
• werden f ¨ur jede Zeile der ¨außeren Abfrage einmal ausgewertet. Der Wert der Korrelationsvariablen wird dabei aus der aktuellen Zeile der ¨außeren Abfrage importiert.
Achtung: Korrelationsvariablen sind immer in Punktnotation anzugeben!
R. Schwenkert - HM Januar 2010 Seite 75
Korrelierte Unterabfrage – Beispiel I
“Ermittle f ¨ur jede Abteilung den Mitarbeiter mit der niedrigsten Tarifstufe!“
SELECT u.Abtnr, u.Name, u.Gehalt FROM Pers u WHERE u.Gehalt =
(SELECT MIN(v.gehalt) FROM Pers v WHERE v.Abtnr = u.Abtnr);
Der Befehl liefert die Ergebnistabelle:
Abtnr Name Gehalt AN Stocker 3
AS Stoer 3
AW M ¨uller 4
Die Hauptabfrage ¨ubergibt pro Zeile von PERS jeweils den aktuellen ABTNR- Wert an die Korrelationsvariable u.Abtnr: Mit diesem Wert ermittelt die Unterab- frage die niedrigste Tarifstufe der betreffenden Abteilung. Dann wird die WHERE- Klausel ausgewertet.
R. Schwenkert - HM Januar 2010 Seite 76
SELECT u.Abtnr, u.Name, u.Gehalt FROM Pers u WHERE u.Gehalt =
(SELECT MIN(v.gehalt) FROM Pers v WHERE v.Abtnr = u.Abtnr);
1. Zeile der Hauptabfrage:
Pnr Name u.Abtnr u.Gehalt
100 Stocker AN 3
500 M ¨uller AW 4
111 Meier AN 5
Unterabfrage: SELECT MIN(v.gehalt) FROM Pers v WHERE v.Abtnr = ’AN’);
WHERE-Bed. = TRUE, da Ergebnis der Unterabfrage = 3 1. Ergebniszeile der Hauptabfrage: AN Stocker 3
R. Schwenkert - HM Oktober 2013 Seite 76, i
SELECT u.Abtnr, u.Name, u.Gehalt FROM Pers u WHERE u.Gehalt =
(SELECT MIN(v.gehalt) FROM Pers v WHERE v.Abtnr = u.Abtnr);
2. Zeile der Hauptabfrage:
Pnr Name u.Abtnr u.Gehalt
100 Stocker AN 3
500 M ¨uller AW 4
111 Meier AN 5
Unterabfrage: SELECT MIN(v.gehalt) FROM Pers v WHERE v.Abtnr = ’AW’);
WHERE-Bed. = TRUE, da Ergebnis der Unterabfrage = 4 2. Ergebniszeile der Hauptabfrage: AW M ¨uller 4
R. Schwenkert - HM Oktober 2013 Seite 76, ii
SELECT u.Abtnr, u.Name, u.Gehalt FROM Pers u WHERE u.Gehalt =
(SELECT MIN(v.gehalt) FROM Pers v WHERE v.Abtnr = u.Abtnr);
3. Zeile der Hauptabfrage:
Pnr Name u.Abtnr u.Gehalt
100 Stocker AN 3
500 M ¨uller AW 4
111 Meier AN 5
Unterabfrage: SELECT MIN(v.gehalt) FROM Pers v WHERE v.Abtnr = ’AN’);
WHERE-Bed. = FALSE, da Ergebnis der Unterabfrage = 3 (5 6= 3!) Keine Ergebniszeile f ¨ur die Hauptabfrage!
R. Schwenkert - HM Oktober 2013 Seite 76, iii
Korrelierte Unterabfrage – Beispiel II
Alternative f ¨ur die schon gel ¨oste Anfrage: “Wer verdient mehr als sein Chef ?“
SELECT Name, Vorname FROM Pers u, Abtl v WHERE u.Abtnr = v.Abtnr
AND Gehalt >
(SELECT w.gehalt FROM Pers w WHERE w.Pnr = v.Pnr);
FAZIT: Man kann bei vielen Anfragen auf Unterabfragen verzichten, da man auch mit anderen Mitteln (z.B. Join) zum Ergebnis kommt. Bei Datenmanipu- lationen mit UPDATE und DELETE (sp ¨ater!) kommt man in der Regel aber nicht ohne Unterabfragen aus!
R. Schwenkert - HM Januar 2010 Seite 77
SELECT Name, Vorname FROM Pers u, Abtl v WHERE u.Abtnr = v.Abtnr AND
Gehalt > (SELECT w.gehalt FROM Pers w WHERE w.Pnr = v.Pnr);
1. Zeile HA:
u.PNR NAME VORNAME u.ABTNR GEHALT v.ABTNR v.PNR
100 Stocker Manfred AN 3 AN 111
500 M ¨uller Andreas AW 4 AW 333
111 Meier Stefan AN 5 AN 111
102 Huber Herbert AS 7 AS 222
222 Stoer Michael AS 3 AS 222
333 Wenzel Ludger AW 6 AW 333
112 J ¨ager Heinz AN 5 AN 111
444 M ¨uller Stefan AW 6 AW 333
SELECT w.gehalt FROM Pers w WHERE w.Pnr = 111 == 5
1. Zeile nicht im Ergebnis, da WHERE-Bed. = FALSE (3 < 5!)
R. Schwenkert - HM Oktober 2013 Seite 77, i
SELECT Name, Vorname FROM Pers u, Abtl v WHERE u.Abtnr = v.Abtnr AND
Gehalt > (SELECT w.gehalt FROM Pers w WHERE w.Pnr = v.Pnr);
4. Zeile HA:
u.PNR NAME VORNAME u.ABTNR GEHALT v.ABTNR v.PNR
100 Stocker Manfred AN 3 AN 111
500 M ¨uller Andreas AW 4 AW 333
111 Meier Stefan AN 5 AN 111
102 Huber Herbert AS 7 AS 222
222 Stoer Michael AS 3 AS 222
333 Wenzel Ludger AW 6 AW 333
112 J ¨ager Heinz AN 5 AN 111
444 M ¨uller Stefan AW 6 AW 333
SELECT w.gehalt FROM Pers w WHERE w.Pnr = 222 == 3
4. Zeile ist im Ergebnis, da WHERE-Bed. = TRUE (7 > 3!)
R. Schwenkert - HM Oktober 2013 Seite 77, ii
Der NOT EXISTS-Operator – Allquantifizierung
Einen SQL-Operator f ¨ur den Allquantor (∀) gibt es nicht. Es gilt aber:
∀ TUPEL gilt: Eigenschaft E ⇐⇒6 ∃ TUPEL mit: ¬E
Man kann also “FORALL-Formulierungen“ durch doppelte Verneinungen mit Hil- fe des NOT EXISTS-Operators ausdr ¨ucken.
Beispiel: ∀ Autos gilt: sie sind rot ⇐⇒6 ∃ Auto mit: es ist nicht rot
“Alle Autos sind rot“ ⇐⇒ “Es gibt kein Auto mit einer anderen Farbe als Rot“
R. Schwenkert - HM Januar 2010 Seite 84
Allquantifizierung – Beispiel
“Welche Mitarbeiter (MA) sind an allen Kraftfahrzeugen (KFZ) ausgebildet?“
∀ TUPEL gilt: Eigenschaft E ⇐⇒6 ∃ TUPEL mit: ¬E
• Finde MA, f ¨ur den f ¨ur alle KFZ (← TUPEL) gilt, dass er dieses KFZ f ¨ahrt (←
E).
• Finde MA, f ¨ur den kein KFZ (← TUPEL) existiert, das von diesem MA nicht gefahren wird. (← ¬E).
R. Schwenkert - HM Januar 2010 Seite 85
Allquantifizierung – Beispielfortsetzung
Finde MA, f ¨ur den kein KFZ (← TUPEL) existiert, das von diesem MA nicht gefahren wird. (← ¬E).
SELECT Pnr FROM Pers p WHERE NOT EXISTS
(SELECT Kfznr FROM Kfz k WHERE NOT EXISTS
(SELECT kfznr FROM Pkfzz
WHERE kfznr=k.kfznr AND pnr=p.pnr));
R. Schwenkert - HM Januar 2010 Seite 86
1. Zeile der HA (Tabelle PERS):
SELECT Pnr FROM Pers p WHERE NOT EXISTS (SELECT Kfznr FROM Kfz k WHERE NOT EXISTS
(SELECT kfznr FROM Pkfzz WHERE kfznr=k.kfznr AND pnr=p.pnr));
1. Unterabfrage (UA), 1. Zeile (Tabelle KFZ): 1 LKW
2. UA: SELECT kfznr FROM Pkfzz WHERE kfznr=1 AND pnr=100;
Ergebnis = 1, d.h. WHERE-Bed. 1. Unterabfrage == FALSE 1. UA, 2. Zeile (Tabelle KFZ): 2 Kran
2. UA: SELECT kfznr FROM Pkfzz WHERE kfznr=2 AND pnr=100;
Ergebnis = 2, d.h. WHERE-Bed. 1. Unterabfrage == FALSE
1. UA, 3. Zeile: Ergebnis = 3, d.h. WHERE-Bed. 1. Unterabfrage == FALSE DAMIT: 1. UA liefert ∅, d.h. WHERE-Bed. HA == TRUE, also 100 im Ergebnis!
R. Schwenkert - HM Oktober 2013 Seite 86, i
2. Zeile der HA (Tabelle PERS): 500 M ¨uller Andreas AW 4
SELECT Pnr FROM Pers p WHERE NOT EXISTS (SELECT Kfznr FROM Kfz k WHERE NOT EXISTS
(SELECT kfznr FROM Pkfzz WHERE kfznr=k.kfznr AND pnr=p.pnr));
1. Unterabfrage (UA), 1. Zeile (Tabelle KFZ): 1 LKW
2. UA: SELECT kfznr FROM Pkfzz WHERE kfznr=1 AND pnr=500;
Ergebnis = 1, d.h. WHERE-Bed. 1. Unterabfrage == FALSE 1. UA, 2. Zeile (Tabelle KFZ): 2 Kran
2. UA: SELECT kfznr FROM Pkfzz WHERE kfznr=2 AND pnr=500;
Ergebnis = ∅, d.h. WHERE-Bed. 1. Unterabfrage == TRUE
DAMIT: 1. UA liefert eine Zeile (2), d.h. WHERE-Bed. HA == FALSE, also 500 nicht im Ergebnis!
R. Schwenkert - HM Oktober 2013 Seite 86, ii
