SQ
L -
Str
uctu
red
Que
ry L
angu
age
AIF
B
SS
200
1
1
2.3.8 Verknüpfung von Relationen mit Subqueries (1/7)(1/7)
32. „Namen aller Angestellten, die mit 100 % ihrer Arbeitszeit an einem Projekt mitarbeiten.“1. Schritt: Bestimme aus ang-pro alle ANG-NR, die zu PROZ-ARBZEIT=100 gehören (Query a)
(das sind geschachtelte Queries) anstelle von Join
Ergebnis: ang-pro (#=m)
P-NR ANG-NR PROZ-ARBZEIT761235 3207 100761235 3115 50761235 3190 50761235 1435 40761235 3425 50770008 2244 20770008 1237 40770008 2814 70770008 2454 40770114 2814 30770114 1435 60770114 1237 60770114 2454 60770114 3425 50770114 2412 100770231 3190 50770231 2314 100770231 2244 80770231 3115 50770231 1324 100
ANG-NR3207241223141324
SELECTFROMWHERE
ANG-NRang-proPROZ-ARBZEIT=100;
SQ
L -
Str
uctu
red
Que
ry L
angu
age
AIF
B
SS
200
1
2
angestellte (#=n)ANG-NR NAME WOHNORT ABT-NR
3115 Meyer Karlsruhe 353207 Müller Mannheim 302814 Klein Mannheim 323190 Maus Karlsruhe 302314 Groß Karlsruhe 351324 Schmitt Heidelberg 351435 Mayerlein Bruchsal 322412 Müller Karlsruhe 322244 Schulz Bruchsal 311237 Krämer Ludwigshafen 313425 Meier Pforzheim 302454 Schuster Worms 31
2.3.8 Verknüpfung von Relationen mit Subqueries (2/7)(2/7)
2. Schritt: Wähle in angestellte alle Tupel aus, deren ANG-NR in der oben gegebenen Ergebnismenge liegt: (Query b)
Ergebnis:
NAMEMüllerGroß
SchmittMüller
SELECTFROMWHERE
NAMEangestellte
ANG-NR IN (3207, 2412, 2314,1324);
SQ
L -
Str
uctu
red
Que
ry L
angu
age
AIF
B
SS
200
1
3
2.3.8 Verknüpfung von Relationen mit Subqueries (3/7)(3/7)
{äußere Query b}
SELECT NAME
FROM angestellte
WHERE ANG-NR IN
{Subquery a}
(SELECT ANG-NR
FROM ang-pro
WHERE PROZ-ARBZEIT = 100)
Vorgehensweise des Systems:
a) Abarbeitung Subquery (a)
b) Übergabe Ergebnis an übergeordnete (äußere) Query (b)
c) Abarbeitung Query b mit dem übergebenen Ergebnis
Zusammenfassung zu geschachtelter Query:
SQ
L -
Str
uctu
red
Que
ry L
angu
age
AIF
B
SS
200
1
4
2.3.8 Verknüpfung von Relationen mit Subqueries (4/7)(4/7)
SELECT NAME
FROM angestellte, ang-pro
WHERE PROZ-ARBZEIT = 100
AND ang-pro.ANG-NR = angestellte.ANG-NR;
33. Äquivalente Abfrage mit Join:
Vergleich 32. mit 33.: (vereinfachte Betrachtung!)
man betrachte Anzahl der notwendigen DB-Zugriffe!
bei 32.: n+m
bei 33.: nm
Ergebnis von 32. SELECT NAMEFROM angestellte WHERE ANG-NR IN
(SELECT ANG-NR FROM ang-pro WHERE PROZ-ARBZEIT = 100)
SQ
L -
Str
uctu
red
Que
ry L
angu
age
AIF
B
SS
200
1
5
2.3.8 Verknüpfung von Relationen mit Subqueries (5/7)(5/7)
34. „Namen aller Angestellten, die an mindestens einem Projekt in Karlsruhe mitarbeiten.“
Vorgehensweise:
a) in projekt: Auswahl P-NR der Projekte in Karlsruhe
b) in ang-pro: Auswahl ANG-NR, deren P-NR in der obigen Ergebnismenge liegen
c) in angestellte: Auswahl Tupel mit o. best. ANG-NR
c) SELECT NAME FROM angestellte WHERE ANG-NR IN
b) (SELECT ANG-NR FROM ang-pro WHERE P-NR IN
a) (SELECT P-NR FROM projekt WHERE P-FILIALE =‘Karlsruhe‘));
Mehrfach geschachtelte Queries
Animation und Ergebnis dieses Beispiels (siehe Seite 72)
SQ
L -
Str
uctu
red
Que
ry L
angu
age
AIF
B
SS
200
1
6
2.3.8 Verknüpfung von Relationen mit Subqueries (6/7)(6/7)
SELECTa.NAME
FROM angestellte a, ang-pro ap, projekt p
WHERE a.ANG-NR = ap.ANG-NR AND ap.P-NR=p.P-NR AND
P-FILIALE=‘Karlsruhe‘
35. äquivalente Formulierung mit 2-fach Join:
SQ
L -
Str
uctu
red
Que
ry L
angu
age
AIF
B
SS
200
1
7
2.3.8 Verknüpfung von Relationen mit Subqueries (7/7)(7/7)
„Correlated subquery“:Subquery bezieht Größen der umgebenden äußeren Query ein (als Variablen).
:36. Gib alle Orte an, an denen mehr als ein Projekt angesiedelt ist.“
b) SELECT DISTINCT P-FILIALEFROM projekt a WHERE 1 <
a) (SELECT COUNT (*) FROM projekt b WHERE b.P-FILIALE = a.P-FILIALE
Jetzt: b) kann nicht vorab ausgewertet werden, da abhängig von P-Filiale aus a).
Animation und Ergebnis dieses Beispiels (siehe Seite 73)
SQ
L -
Str
uctu
red
Que
ry L
angu
age
AIF
B
SS
200
1
8
Beispiel für Mehrfach geschachtelte QueriesBeispiel für Mehrfach geschachtelte Queries
SELECT NAME FROM angestellte WHERE ANG-NR IN
angestellte (#=n)ANG-NR NAME WOHNORT ABT-NR
3115 Meyer Karlsruhe 353207 Müller Mannheim 302814 Klein Mannheim 323190 Maus Karlsruhe 302314 Groß Karlsruhe 351324 Schmitt Heidelberg 351435 Mayerlein Bruchsal 322412 Müller Karlsruhe 322244 Schulz Bruchsal 311237 Krämer Ludwigshafen 313425 Meier Pforzheim 302454 Schuster Worms 31
projekt (#=k)P-NAME P-NR P-FILIALE P-LEITER
p-1 761235 Karlsruhe 3115p-2 770008 Karlsruhe 3115p-3 770114 Heidelberg 1324P-4 770231 Mannheim 2814
ang-pro (#=m)P-NR ANG-NR PROZ-ARBZEIT
761235 3207 100761235 3115 50761235 3190 50761235 1435 40761235 3425 50770008 2244 20770008 1237 40770008 2814 70770008 2454 40770114 2814 30770114 1435 60770114 1237 60770114 2454 60
770114 3425 50770114 2412 100770231 3190 50770231 2314 100770231 2244 80
770231 3115 50770231 1324 100
(SELECT P-NR FROM projekt WHERE P-FILIALE =‘Karlsruhe‘));
(SELECT ANG-NRFROM ang-proWHERE P-NR IN
Ergebnis: Ergebnis:NAMEMeyer
Müller
Klein
Maus
Mayerlein
Krämer
Meier
Schuster
SQ
L -
Str
uctu
red
Que
ry L
angu
age
AIF
B
SS
200
1
9
Beispiel für Beispiel für „Correlated subquery“„Correlated subquery“
SELECT DISTINCT P-FILIALE FROM projekt a WHERE 1 <
(SELECT COUNT (*) FROM projekt b WHERE b.P-FILIALE = a.P-FILIALE);
projekt a P-NAME P-NR P-FILIALE P-LEITER
p-1 761235 Karlsruhe 3115p-2 770008 Karlsruhe 3115p-3 770114 Heidelberg 1324P-4 770231 Mannheim 2814
Ergebnis: Ergebnis:
P-FILIALEKalrsruhe
projekt bP-NAME P-NR P-FILIALE P-LEITER
p-1 761235 Karlsruhe 3115p-2 770008 Karlsruhe 3115p-3 770114 Heidelberg 1324P-4 770231 Mannheim 2814
211