Unterabfragen
Immer wieder werden zur Durchführung einer Abfrage oder eines anderen Befehls Informationen benötigt, die zuerst durch eine eigene Abfrage geholt werden müssen. Solche „Unterabfragen“ werden in diesem Kapitel behandelt.
- Wenn eine Abfrage als Ergebnis einen einzelnen Wert liefert, kann sie anstelle eines Wertes benutzt werden.
- Wenn eine Abfrage als Ergebnis eine Liste von Werten liefert, kann sie anstelle einer solchen Liste benutzt werden.
- Wenn eine Abfrage eine Ergebnismenge, also etwas in Form einer Tabelle liefert, kann sie anstelle einer Tabelle benutzt werden.
Bitte beachten Sie, dass die Unterabfrage immer in Klammern gesetzt wird. Auch wenn ein DBMS das nicht verlangen sollte, ist es wegen der Übersichtlichkeit dringend zu empfehlen.
Allgemeiner Hinweis: Unterabfragen arbeiten in vielen Fällen langsamer als andere Verfahren. Soweit es irgend möglich ist, versuchen Sie, eine der JOIN-Varianten vorzuziehen.
Ergebnis als einzelner Wert
[Bearbeiten]Ergebnisse einfacher Abfragen
[Bearbeiten]Immer wieder kennt der Anwender den Namen eines Objekts, benötigt aber für Abfragen die ID. Diese holt er sich mit einer Unterabfrage und übergibt das Ergebnis an die eigentliche Abfrage.
Nenne alle Mitarbeiter der Abteilung „Schadensabwicklung“.
- Lösung Teil 1: Hole die ID dieser Abteilung anhand des Namens.
- Lösung Teil 2: Hole die Mitarbeiter dieser Abteilung unter Benutzung der gefundenen ID.
select Personalnummer, Name, Vorname
from Mitarbeiter
where Abteilung_ID =
( select ID from Abteilung
where Kuerzel = 'ScAb' );
AusgabePERSONALNUMMER NAME VORNAME
-------------- --------- -------
80001 Schindler Christina
80002 Aliman Zafer
80003 Langer Norbert
80004 Kolic Ivana
Teil 1 der Lösung ist der SELECT-Befehl innerhalb der Klammern. Das Ergebnis ist eine einzelne ID. Diese kann anstelle einer konkreten Zahl in die WHERE-Klausel der eigentlichen Abfrage übernommen werden. Das Wörtchen „Diese“ hat in diesem Fall sprachlich eine doppelte Bedeutung: zum einen steht es für die Unterabfrage, zum anderen für die ID als Ergebnis.
Hinweis: Dies funktioniert nur deshalb auf einfache Weise, weil die Kurzbezeichnung faktisch eindeutig ist und deshalb genau eine ID geliefert wird. Wenn wir uns darauf nicht verlassen wollen oder können oder wenn das DBMS „empfindlich“ ist und die Eindeutigkeit des Ergebnisses nicht erkennt, können wir daraus bewusst einen einzelnen Wert machen:
select Personalnummer, Name, Vorname
from Mitarbeiter
where Abteilung_ID =
( select MAX(ID) from Abteilung
where Kuerzel = 'ScAb' );
Eine solche Aufgabe kann auch zweimal dieselbe Tabelle benutzen.
Nenne alle anderen Mitarbeiter der Abteilung, deren Leiterin Christina Schindler ist.
- Lösung Teil 1: Hole die Abteilung_ID, die bei Christina Schindler registriert ist.
- Lösung Teil 2: Hole die Mitarbeiter dieser Abteilung unter Benutzung der gefundenen Abteilung_ID.
Zur Sicherheit prüfen wir auch die Eigenschaft Ist_Leiter.
select Personalnummer, Name, Vorname
from Mitarbeiter
where ( Abteilung_ID =
( SELECT Abteilung_ID
from Mitarbeiter
where ( Name = 'Schindler' )
and ( Vorname = 'Christina' )
and ( Ist_Leiter = 'J' )
)
) and ( Ist_Leiter = 'N' );
AusgabePERSONALNUMMER NAME VORNAME
-------------- ------ -------
80002 Aliman Zafer
80003 Langer Norbert
80004 Kolic Ivana
Ergebnisse von Spaltenfunktionen
[Bearbeiten]Häufig werden Ergebnisse von Aggregatfunktionen als Teil der WHERE-Klausel benötigt.
Hole die Schadensfälle mit unterdurchschnittlicher Schadenshöhe.
- Lösung Teil 1: Berechne die durchschnittliche Schadenshöhe aller Schadensfälle.
- Lösung Teil 2: Übernimm das Ergebnis als Vergleichswert in die eigentliche Abfrage.
SELECT ID, Datum, Ort, Schadenshoehe
from Schadensfall
where Schadenshoehe <
( select AVG(Schadenshoehe) from Schadensfall );
AusgabeID DATUM ORT SCHADENSHOEHE
-- ---------- ----------------------------------- -------------
1 03.02.2007 Recklinghausen, Bergknappenstr. 144 1.234,50
2 11.07.2007 Haltern, Hauptstr. 46 2.066,00
4 27.05.2008 Recklinghausen, Südgrabenstr. 23 1.438,75
5 05.10.2008 Dorsten, Oberhausener Str. 18 1.983,00
7 21.06.2009 Recklinghausen, Bergknappenstr. 144 865,00
Bestimme alle Schadensfälle, die von der durchschnittlichen Schadenshöhe eines Jahres maximal 300 € abweichen.
- Lösung Teil 1: Bestimme den Durchschnitt aller Schadensfälle innerhalb eines Jahres.
- Lösung Teil 2: Hole alle Schadensfälle, deren Schadenshöhe im betreffenden Jahr innerhalb des Bereichs „Durchschnitt plus/minus 300“ liegen.
select sf.ID, sf.Datum, sf.Schadenshoehe, EXTRACT(YEAR from sf.Datum) AS Jahr
from Schadensfall sf
where ABS(Schadenshoehe - ( select AVG(sf2.Schadenshoehe)
from Schadensfall sf2
where EXTRACT(YEAR from sf2.Datum) = EXTRACT(YEAR from sf.Datum)
)
) <= 300;
AusgabeID DATUM SCHADENSHOEHE JAHR
-- ---------- ------------- ----
2 11.07.2007 2.066,00 2007
4 27.05.2008 1.438,75 2008
5 05.10.2008 1.983,00 2008
8 01.08.2009 2.471,50 2009
Zuerst muss für jeden einzelnen Schadensfall aus sf das Jahr bestimmt werden. In der Unterabfrage, die in der inneren Klammer steht, wird für alle Schadensfälle des betreffenden Jahres die durchschnittliche Schadenshöhe bestimmt. Dieser Wert wird mit der aktuellen Schadenshöhe verglichen; dazu wird die ABS-Funktion benutzt, also der absolute Betrag der Differenz der beiden Werte.
|
|
Dies ist ein Paradebeispiel dafür, wie Unterabfragen nicht benutzt werden sollen. |
|---|
Für jeden einzelnen Datensatz muss in der WHERE-Bedingung eine neue Unterabfrage gestartet werden – mit eigener WHERE-Klausel und Durchschnittsberechnung. Viel besser ist eine der JOIN-Varianten oder eine der Lösungen im Abschnitt „Ergebnis in Form einer Tabelle“ (siehe unten).
Ergebnis als Liste mehrerer Werte
[Bearbeiten]Das Ergebnis einer Abfrage kann als Filter für die eigentliche Abfrage benutzt werden.
Bestimme alle Fahrzeuge eines bestimmten Herstellers.
- Lösung Teil 1: Hole die ID des gewünschten Herstellers.
- Lösung Teil 2: Hole alle IDs der Tabelle Fahrzeugtyp zu dieser Hersteller-ID.
- Lösung Teil 3: Hole alle Fahrzeuge, die zu dieser Liste von Fahrzeugtypen-IDs passen.
select ID, Kennzeichen, Fahrzeugtyp_ID as TypID
from Fahrzeug
where Fahrzeugtyp_ID in
( select ID
from Fahrzeugtyp
where Hersteller_ID =
( select ID
from Fahrzeughersteller
where Name = 'Volkswagen' ) );
AusgabeID KENNZEICHEN TYPID
22 BOR-PQ 567 3
23 BOR-RS 890 2
Teil 1 der Lösung ist die „innere“ Klammer; dies ist das gleiche Verfahren wie im Abschnitt „Ergebnisse einfacher Abfragen“. Teil 2 der Lösung ist die „äußere“ Klammer; Ergebnis ist eine Liste von IDs der Tabelle Fahrzeugtyp, die als Werte für den Vergleich der WHERE-IN-Klausel verwendet werden.
Wenn im Ergebnis der Fahrzeugtyp als Text angezeigt werden soll, muss die Abfrage erweitert werden, weil die Bezeichnung in der Tabelle Fahrzeugtyp zu finden ist. Dafür kann diese Tabelle ein zweites Mal benutzt werden, wie es im Kapitel Mehrere Tabellen erläutert wird; es ist auch ein Verfahren möglich, wie es unten im Abschnitt „Ergebnis in Form einer Tabelle“ erläutert wird.
Das obige Beispiel mit der durchschnittlichen Schadenshöhe kann auch so gebaut werden:
Gib alle Informationen zu den Schadensfällen des Jahres 2008, die von der durchschnittlichen Schadenshöhe 2008 maximal 300 € abweichen.
- Lösung Teil 1: Bestimme den Durchschnitt aller Schadensfälle innerhalb von 2008.
- Lösung Teil 2: Hole alle IDs von Schadensfällen, deren Schadenshöhe innerhalb des Bereichs „Durchschnitt plus/minus 300“ liegen.
- Lösung Teil 3: Hole alle anderen Informationen zu diesen IDs.
select *
from Schadensfall
where ID in ( SELECT ID
from Schadensfall
where ( ABS(Schadenshoehe
- ( select AVG(sf2.Schadenshoehe)
from Schadensfall sf2
where EXTRACT(YEAR from sf2.Datum) = 2008
)
) <= 300 )
and ( EXTRACT(YEAR from Datum) = 2008 )
);
AusgabeID DATUM ORT BESCHREIBUNG SCHADENSHOEHE VERLETZTE MIT-ID
4 27.05.2008 Recklinghausen, Fremdes parkendes 1.438,75 N 16
Südgrabenstr. 23 Auto gestreift
5 05.10.2008 Dorsten, beim Ausparken hat 1.983,00 N 14
Oberhausener Str. 18 ein fremder Wagen die
Vorfahrt missachtet
Diese Situation wird dadurch einfacher, dass das Jahr 2008 fest vorgegeben ist. Die innerste Klammer bestimmt als Teil 1 der Lösung die durchschnittliche Schadenshöhe dieses Jahres. Die nächste Klammer vergleicht diesen Wert (absolut gesehen) mit der Schadenshöhe eines jeden einzelnen Schadensfalls im Jahr 2008; alle „passenden“ IDs werden in der äußersten Klammer als Teil 2 der Lösung in einer weiteren Unterabfrage zusammengestellt. Diese Liste liefert die Werte für die eigentliche Abfrage.
Ganz offensichtlich ist dieses Beispiel konstruiert: Weil immer dieselbe Tabelle verwendet wird, kann die WHERE-Klausel der Unterabfrage in der äußersten Klammer auch als WHERE-Klausel der Hauptabfrage verwendet werden (die Einrückungen wurden zum besseren Vergleich nicht geändert):
select *
from Schadensfall
where ( ABS(Schadenshoehe
- ( select AVG(sf2.Schadenshoehe)
from Schadensfall sf2
where EXTRACT(YEAR from sf2.Datum) = 2008
)
) <= 300 )
and ( EXTRACT(YEAR from Datum) = 2008 )
;
Ergebnis in Form einer Tabelle
[Bearbeiten]Das Ergebnis einer Abfrage kann in der Hauptabfrage überall dort eingesetzt werden, wo eine Tabelle vorgesehen ist. Die Struktur dieser Situation sieht so aus:
SELECT <spaltenliste>
FROM <haupttabelle>,
( SELECT <spaltenliste>
FROM <zusatztabellen>
<weitere Bestandteile der Unterabfrage>
) <name>
<weitere Bestandteile der Hauptabfrage>
Eine solche Unterabfrage kann grundsätzlich alle SELECT-Bestandteile enthalten. Bitte beachten Sie dabei:
- Nach der schließenden Klammer muss ein Name als Tabellen-Alias angegeben werden, der als Ergebnistabelle in der Hauptabfrage verwendet wird.
- Die Unterabfrage kann eine oder mehrere Tabellen umfassen – wie jede andere Abfrage auch.
- In der Spaltenliste sollte jeweils ein Name als Spalten-Alias vor allem dann vorgesehen werden, wenn mehrere Tabellen verknüpft werden; andernfalls erzeugt SQL selbständig Namen wie ID, ID1 usw., die man nicht ohne Weiteres versteht und zuordnen kann.
- ORDER BY kann nicht sinnvoll genutzt werden, weil das Ergebnis der Unterabfrage als Tabelle behandelt wird und mit der Haupttabelle oder einer anderen Tabelle verknüpft wird, wodurch eine Sortierung sowieso verlorenginge.
Wie gesagt: Eine solche Unterabfrage kann überall stehen, wo eine Tabelle vorgesehen ist. In der vorstehenden Syntax steht sie nur beispielhaft innerhalb der FROM-Klausel.
Überarbeiten wir jetzt, wie oben angekündigt, einige Beispiele. Dabei wird die Unterabfrage, die bisher zur WHERE-Klausel gehörte, als Tabelle in die FROM-Klausel eingebaut.
Bestimme alle Schadensfälle, die von der durchschnittlichen Schadenshöhe eines Jahres maximal 300 € abweichen.
- Lösung Teil 1: Stelle alle Jahre zusammen und bestimme den Durchschnitt aller Schadensfälle innerhalb eines Jahres.
- Lösung Teil 2: Hole alle Schadensfälle, deren Schadenshöhe im jeweiligen Jahr innerhalb des Bereichs „Durchschnitt plus/minus 300“ liegen.
SELECT sf.ID, sf.Datum, sf.Schadenshoehe, temp.Jahr, temp.Durchschnitt
FROM Schadensfall sf,
( SELECT AVG(sf2.Schadenshoehe) AS Durchschnitt,
EXTRACT(YEAR FROM sf2.Datum) as Jahr
FROM Schadensfall sf2
group by EXTRACT(YEAR FROM sf2.Datum)
) temp
WHERE temp.Jahr = EXTRACT(YEAR FROM sf.Datum)
and ABS(Schadenshoehe - temp.Durchschnitt) <= 300;
Zuerst stellen wir durch eine Gruppierung alle Jahreszahlen und die durchschnittlichen Schadenshöhen zusammen (Teil 1 der Lösung). Für Teil 2 der Lösung muss für jeden Schadensfall nur noch Jahr und Schadenshöhe mit dem betreffenden Eintrag in der Ergebnistabelle temp verglichen werden.
Das ist der wesentliche Unterschied und entscheidende Vorteil zur obigen Lösung: Die Durchschnittswerte werden einmalig zusammengestellt und nur noch abgerufen; sie müssen nicht bei jedem Datensatz neu (und ständig wiederholt) berechnet werden.
Bestimme alle Fahrzeuge eines bestimmten Herstellers mit Angabe des Typs.
- Lösung Teil 1: Hole die ID des gewünschten Herstellers.
- Lösung Teil 2: Hole alle IDs und Bezeichnungen der Tabelle Fahrzeugtyp, die zu dieser Hersteller-ID gehören.
- Lösung Teil 3: Hole alle Fahrzeuge, die zu dieser Liste von Fahrzeugtyp-IDs gehören.
SELECT Fahrzeug.ID, Kennzeichen, Typen.ID As TYP, Typen.Bezeichnung
FROM Fahrzeug,
( SELECT ID, Bezeichnung
FROM Fahrzeugtyp
WHERE Hersteller_ID =
( SELECT ID
FROM Fahrzeughersteller
WHERE Name = 'Volkswagen' )
) Typen
WHERE Fahrzeugtyp_ID = Typen.ID;
AusgabeID KENNZEICHEN TYP BEZEICHNUNG
23 BOR-RS 890 2 Golf
22 BOR-PQ 567 3 Passat
Teil 1 der Lösung ist die „innere“ Klammer; dies entspricht dem obigen Verfahren. Teil 2 der Lösung ist die „äußere“ Klammer; Ergebnis ist eine Tabelle von IDs und Bezeichnungen, also ein Teil der Tabelle Fahrzeugtyp, deren Werte für den Vergleich der WHERE-Klausel und außerdem für die Ausgabe verwendet werden.
Hinweis: Mit den Möglichkeiten des nächsten Kapitels Erstellen von Views ergeben sich wesentliche Verbesserungen: Mit einer VIEW lassen sich Unterabfragen, die – wie die Liste von Typen und Herstellern – immer wieder benötigt werden, dauerhaft bereitstellen. Und mit einer Inline-View werden verschachtelte Abfragen deutlich übersichtlicher.
Verwendung bei Befehlen zum Speichern
[Bearbeiten]Bisher hatten wir Abfragen als Teil von anderen Abfragen benutzt; deshalb wird das Ganze auch als „Unterabfrage“ bezeichnet. Man kann das Ergebnis einer Abfrage aber auch zum Speichern verwenden: sowohl für einen einzelnen Wert als auch als vollständigen Datensatz.
Verwendung bei INSERT INTO ... SELECT
[Bearbeiten]Eine Abfrage in einen INSERT-Befehl einzubinden, ist eines der Standardverfahren für INSERT:
INSERT INTO <zieltabelle>
( <spaltenliste> )
SELECT <spaltenliste>
FROM <quelltabelle/n>
[ <weitere Festlegungen> ]
[ WHERE <bedingungen> ]
Der SELECT-Befehl kann dabei beliebig aufgebaut sein: Daten aus einer oder mehreren Tabellen holen, mit oder ohne Einschränkungen, mit oder ohne weitere Festlegungen. Lediglich drei Punkte sind zu beachten:
- Die Spaltenliste in der Zieltabelle muss mit den Spalten in der SELECT-Auflistung genau übereinstimmen; genauer: Die Datentypen müssen zueinander passen, also gleich sein oder automatisch konvertiert werden können.
- Es ist möglich, Daten aus der Zieltabelle zu holen und somit zu verdoppeln. Dann muss die ID automatisch vergeben werden können; und es ist unbedingt mit WHERE zu arbeiten, weil es andernfalls zu einer Endlosschleife kommen kann (siehe das erste nachfolgende Beispiel).
- ORDER BY kann nicht sinnvoll genutzt werden, weil das Ergebnis in die Zieltabelle eingefügt wird, wodurch eine Sortierung sowieso verlorenginge.
Um weitere Testdaten zu erhalten, könnte so verfahren werden:
Kopiere die vorhandenen Schadensfälle.
INSERT into Schadensfall
( Datum, Ort, Beschreibung, Schadenshoehe, Verletzte, Mitarbeiter_ID )
SELECt Datum, Ort, Beschreibung, Schadenshoehe, Verletzte, Mitarbeiter_ID
from Schadensfall
where ID < 10000;
Auf die doppelte Angabe der Spalten kann nicht verzichtet werden, weil ID nicht benutzt werden darf (sie soll automatisch neu vergeben werden) und das DBMS wissen muss, welche Werte wie zugeordnet werden sollen. Auf diese Weise kann man ganz leicht 100 oder 1000 Testdatensätze erzeugen. Für den produktiven Betrieb wird man diese Syntax wohl eher seltener brauchen.
- In einem ersten Versuch fehlte die WHERE-Bedingung; erwartet wurde, dass nur die vor dem Befehl vorhandenen Datensätze bearbeitet würden. Tatsächlich hatte Firebird endlos kopiert, bis mit Strg+Alt+Entf der „Stecker gezogen“ wurde. Danach war die Datenbank von 3 MB auf 740 MB aufgebläht worden und (natürlich) beschädigt, sodass auf diese Tabelle nicht mehr richtig zugegriffen werden konnte. Weitere Versuche mit WHERE-Bedingung arbeiteten wie vorgesehen: nur die vorhandenen Datensätze wurden einmalig kopiert.
Die „neuen“ Daten können auch aus einer anderen Tabelle geholt und mit konstanten Werten gemischt werden, wie es in der Beispieldatenbank geschieht:
Jeder Abteilungsleiter erhält einen persönlichen Dienstwagen.
INSERT INTO Dienstwagen
( Kennzeichen, Farbe, Fahrzeugtyp_ID, Mitarbeiter_ID )
SELECT 'DO-WB 42' || Abteilung_ID,
'elfenbein', 14, ID
FROM Mitarbeiter
WHERE Ist_Leiter = 'J';
Die Spaltenliste der Zieltabelle Dienstwagen enthält alle Spalten mit Ausnahme der ID; diese wird automatisch vergeben. Diesen Spalten werden die Spalten der SELECT-Abfrage zugewiesen:
- Die Mitarbeiter_ID ist das einzige Feld aus der Quelltabelle Mitarbeiter, das unbedingt benötigt wird und übernommen werden muss.
- Die Farbe wird als Konstante eingetragen: Alle Abteilungsleiter bekommen den gleichen Wagentyp.
- Der Fahrzeugtyp wird ebenso als Konstante eingetragen. Auch eine Unterabfrage wie oben als Ergebnis einer einfachen Abfrage wäre möglich.
- Für das Kennzeichen ist eine String-Verknüpfung vorgesehen, bei der zusätzlich die Abteilung_ID übernommen wird. Bitte benutzen Sie die für Ihr DBMS richtige Art der String-Verknüpfung.
Die WHERE-Bedingung beschränkt alles auf Abteilungsleiter.
Verwendung bei INSERT INTO ... VALUES
[Bearbeiten]Die andere Version des INSERT-Befehls nutzt direkt eine Liste von Werten:
INSERT INTO <zieltabelle>
[ ( <spaltenliste> ) ]
VALUES ( <werteliste> )
Ein einzelner Befehl sieht dabei wie folgt aus:
Der Mitarbeiter 2 bekommt einen gelben Dienstwagen Typ 2.
INSERT INTO Dienstwagen
( Kennzeichen, Farbe, Fahrzeugtyp_ID, Mitarbeiter_ID )
VALUES ( 'DO-WB 202', 'gelb', 2, 2 );
Versuchen wir, diesen Befehl variabel zu gestalten, sodass Mitarbeiter_ID und Kennzeichen (wie oben) aus einer Abfrage kommen können. Dazu setzen wir diese beiden Spalten hintereinander und ordnen diesen das „Ergebnis als Liste mehrerer Werte“ zu unter Verwendung der LPAD-Funktion (siehe Funktionen (2)).
FehlerINSERT INTO Dienstwagen
( Kennzeichen, Mitarbeiter_ID, Farbe, Fahrzeugtyp_ID )
VALUES ( ( SELECT 'DO-WB 2' || LPAD(ID, 2, '0'), ID
FROM Mitarbeiter WHERE ID = 2 ),
'gelb', 2 );
AusgabeSQL error code = -804.
Count of read-write columns does not equal count of values.
Aha, der SELECT-Befehl wird nur als ein Wert interpretiert, obwohl er zwei passende Werte liefert. Können wir die Abfrage an beiden Stellen verwenden?
So kann es funktionieren.INSERT INTO Dienstwagen
( Kennzeichen, Mitarbeiter_ID, Farbe, Fahrzeugtyp_ID )
VALUES ( ( SELECT 'DO-WB 2' || LPAD(ID, 2, '0') FROM Mitarbeiter WHERE ID = 2 ),
( SELECT ID FROM Mitarbeiter WHERE ID = 2 ),
'gelb', 2 );
So funktioniert es, aber es ist natürlich nicht schön, wenn eine fast identische Abfrage doppelt auftauchen muss (auch wenn davon auszugehen ist, dass ein DBMS einen vernünftigen Ausführungsplan erstellt). Dann ist die obige Version mit INSERT INTO ... SELECT mit einer Mischung aus Konstanten und Tabellenspalten die bessere Lösung.
Verwendung bei UPDATE
[Bearbeiten]Schauen wir uns die grundsätzliche Struktur eines UPDATE-Befehls an:
UPDATE <tabellenname>
SET <spalte1> = <wert1> [ ,
<spalte2> = <wert2> ]
WHERE <bedingungsliste>;
Daraus ergibt sich, dass Abfragen benutzt werden können, um einen oder mehrere Werte zu speichern oder um Vergleichswerte für die Bedingungsliste zu liefern (ebenso wie in verschiedenen früheren Beispielen).
Die Mitarbeiter am Dienstort „Bochum“ erhalten eine neue Telefonnummer, die neben der Zentrale die Abteilung und die Personalnummer enthält.
- Lösung 1: Die WHERE-Bedingung muss die betreffenden Datensätze (genauer: die IDs) der Tabelle Abteilung prüfen und vergleichen.
- Lösung 2: Der zugeordnete neue Wert muss passend gestaltet werden. (LPAD und SUBSTRING werden nur verwendet, damit mit festen Längen gearbeitet werden kann.)
update Mitarbeiter
set Telefon = '0234/66' || LPAD(Abteilung_ID, 3, '0')
|| SUBSTRING(LPAD(Personalnummer, 6, '0') from 4 for 3)
where Abteilung_ID in ( SELECT ID
from Abteilung
where Ort = 'Bochum' );
Diese Lösung enthält nichts Neues: Die Abfrage wird mit der IN-Abfrage in die WHERE-Klausel eingebunden; die neuen Werte werden aus den vorhandenen (Abteilung und Personalnummer) gebildet.
Die Abteilung „Ausbildung“ soll dorthin umziehen, wo die Abteilung „Personalverwaltung“ sitzt.
- Lösung: Der gewünschte Ort wird aus einer Abfrage geholt.
update Abteilung
set Ort = ( SELECT Ort
from Abteilung
where Kuerzel = 'Pers' )
where Kuerzel = 'Ausb';
Bitte wundern Sie sich nicht über ein solch konstruiertes Beispiel; der neue Ort könnte natürlich im Klartext angegeben werden. Bei der vorgegebenen Datenstruktur ist es manchmal schwer, sinnvolle Beispiele zu entwickeln. Wichtig ist, dass Sie die möglichen Zusammenhänge zwischen einer Abfrage und einem Speichern-Befehl erkennen.
Man kann sich in der Unterabfrage auch auf Spalten beziehen, die aus der Tabelle stammen, die geändert werden soll. Beispiel (nur teilweiser Bezug auf die Beispieldatenbank):
Fehlerupdate Abteilung
set Ort = ( SELECT Ort
from Adressbuch
where Abteilung.PLZ = Adressbuch.PLZ )
;
Diese Abfrage führt zu folgendem Problem: Sie funktioniert nur dann, wenn es im Adressbuch nur exakt einen einzigen Eintrag zu einer bestimmten PLZ gibt. Sobald man zu einer PLZ mehrere Adressen notiert hat, findet die Unterabfrage mehrere Sätze; das ist bei dieser Unterabfrage nicht zulässig. Damit der Update auch in solchen Fällen funktioniert, muss ein DISTINCT eingefügt werden, oder man verwendet die MAX-oder die MIN-Funktion:
Genau ein Datensatz wird geliefert.update Abteilung
set Ort = ( SELECT MAX(Ort)
from Adressbuch
where Abteilung.PLZ = Adressbuch.PLZ )
;
Bei solchen Unterabfragen mit einem Bezug zu dem Satz, der verändert werden soll, kann es vorkommen, dass die Ausführung dieser Anweisung ziemlich lange dauert. Das liegt daran, dass alle Sätze aus der Tabelle Abteilung verändert werden sollen. Für jeden Satz muss die Unterabfrage erneut ausgeführt werden. Wenn eine einzelne Ausführung dieser Unterabfrage eine Sekunde dauert, und wir haben z. B. 1000 Sätze in der Tabelle Abteilung, dann dauert die Ausführung des gesamten Statements 1000 Sekunden, also ca. 16 Minuten.
Man kann auch mehrere Spalten aus Unterabfragen befüllen. Beispiel:
update Abteilung
set Ort = ( SELECT MAX(Ort)
from Telefonbuch
where PLZ = '12345' ),
Leiter = ( SELECT Manager
from Mitarbeiter
where Kuerzel = 'A073' )
where Kuerzel = 'Ausb';
;
Wenn man mehrere Werte aus derselben Unterabfrage übernehmen will, dann könnte man dieselbe Unterabfrage mehrfach angeben. Aber oft kann das Datenbanksystem nicht erkennen, dass es sich immer wieder um dieselbe Unterabfrage handelt, und müsste sie mehrfach ausführen. Einfacher, übersichtlicher und dann auch schneller ist die folgende Variante, die aber nicht jedes DBMS kennt.
In der Tabelle Abteilung werden die Spalten Ort, Leiter, Telefon gemeinsam geändert.
update Abteilung
set ( Ort, Leiter, Telefon )
= ( SELECT Ort, Name, Telefon
from Adressbuch
where Adressbuch.Personalnummer = Abteilung.Chef_Personalnummer
) ;
Hier werden alle Sätze in der Tabelle Abteilung aktualisiert unter Verwendung der Tabelle Adressbuch; die Personalnummer des Abteilungsleiters kann wie oben bestimmt werden.
Verwendung bei DELETE
[Bearbeiten]Schauen wir uns noch die grundsätzliche Struktur eines DELETE-Befehls an:
DELETE FROM <tabellenname> [ WHERE <bedingungsliste> ];
Daraus ergibt sich, dass Abfragen nur für Vergleichswerte der Bedingungsliste sinnvoll sind (ebenso wie in verschiedenen früheren Beispielen).
Die Abteilung „Forschung und Entwicklung“ wird ausgelagert; alle zugeordneten Mitarbeiter werden in der Datenbank gelöscht.
delete from Mitarbeiter
where Abteilung_ID in ( SELECT ID
from Abteilung
where Bezeichnung = 'Forschung und Entwicklung' ) ;
Zusammenfassung
[Bearbeiten]In diesem Kapitel benutzten wir Unterabfragen:
- Sowohl einzelne Werte als auch Listen als Ergebnis einer Abfrage können als Vergleichswerte in der WHERE-Klausel verwendet werden.
- Eine Tabelle als Ergebnis von Abfragen kann wie jede „echte“ Tabelle als Teil der FROM- oder JOIN-Klausel verwendet werden.
Ähnlich können Ergebnisse von Abfragen beim Speichern genutzt werden:
- Ganze Datensätze werden mit INSERT in eine Tabelle eingefügt werden.
- Einzelne Werte kommen in die WHERE-Klausel oder SET-Anweisung.
Übungen
[Bearbeiten]
| Übung 1 | Definitionen | Zur Lösung |
Welche der folgenden Feststellungen sind richtig, welche sind falsch?
- Das Ergebnis einer Unterabfrage kann verwendet werden, wenn es ein einzelner Wert oder eine Liste in Form einer Tabelle ist. Andere Ergebnisse sind nicht möglich.
- Ein einzelner Wert als Ergebnis kann durch eine direkte Abfrage oder durch eine Spaltenfunktion erhalten werden.
- Unterabfragen sollten nicht verwendet werden, wenn die WHERE-Bedingung für jede Zeile der Hauptabfrage einen anderen Wert erhält und deshalb die Unterabfrage neu ausgeführt werden muss.
- Mehrere Unterabfragen können verschachtelt werden.
- Für die Arbeitsgeschwindigkeit ist es gleichgültig, ob mehrere Unterabfragen oder JOINs verwendet werden.
- Eine Unterabfrage mit einer Tabelle als Ergebnis kann GROUP BY nicht sinnvoll nutzen.
- Eine Unterabfrage mit einer Tabelle als Ergebnis kann ORDER BY nicht sinnvoll nutzen.
- Bei einer Unterabfrage mit einer Tabelle als Ergebnis ist ein Alias-Name für die Tabelle sinnvoll, aber nicht notwendig.
- Bei einer Unterabfrage mit einer Tabelle als Ergebnis sind Alias-Namen für die Spalten sinnvoll, aber nicht notwendig.
| Übung 2 | Ein einzelner Wert | Zur Lösung |
Welche Verträge (mit einigen Angaben) hat der Mitarbeiter „Braun, Christian“ abgeschlossen? Ignorieren Sie die Möglichkeit, dass es mehrere Mitarbeiter dieses Namens geben könnte.
| Übung 3 | Ein einzelner Wert | Zur Lösung |
Zeigen Sie alle Verträge, die zum Kunden „Heckel Obsthandel GmbH“ gehören. Ignorieren Sie die Möglichkeit, dass der Kunde mehrfach gespeichert sein könnte.
| Übung 4 | Eine Liste von Werten | Zur Lösung |
Ändern Sie die Lösung von Übung 3, sodass auch mehrere Kunden mit diesem Namen als Ergebnis denkbar sind.
| Übung 5 | Eine Liste von Werten | Zur Lösung |
Zeigen Sie alle Fahrzeuge, die im Jahr 2008 an einem Schadensfall beteiligt waren.
| Übung 6 | Eine Liste von Werten | Zur Lösung |
Zeigen Sie alle Fahrzeugtypen (mit ID, Bezeichnung und Name des Herstellers), die im Jahr 2008 an einem Schadensfall beteiligt waren.
| Übung 7 | Eine Tabelle als Ergebnis | Zur Lösung |
Bestimmen Sie alle Fahrzeuge eines bestimmten Herstellers mit Angabe des Typs.
Hinweis: Es handelt sich um das letzte Beispiel aus dem Abschnitt „Ergebnis in Form einer Tabelle“. Benutzen Sie jetzt JOIN.
| Übung 8 | Eine Tabelle als Ergebnis | Zur Lösung |
Zeigen Sie zu jedem Mitarbeiter der Abteilung „Vertrieb“ den ersten Vertrag (mit einigen Angaben) an, den er abgeschlossen hat. Der Mitarbeiter soll mit ID und Name/Vorname angezeigt werden.
| Übung 9 | Speichern mit Unterabfrage | Zur Lösung |
Von der Deutschen Post AG wird eine Tabelle PLZ_Aenderung mit folgenden Inhalten geliefert:
ID PLZalt Ortalt PLZneu Ortneu 1 45658 Recklinghausen 45659 Recklinghausen 2 45721 Hamm-Bossendorf 45721 Haltern OT Hamm 3 45772 Marl 45770 Marl 4 45701 Herten 45699 Herten
Ändern Sie die Tabelle Versicherungsnehmer so, dass bei allen Adressen, bei denen PLZ/Ort mit PLZalt/Ortalt übereinstimmen, diese Angaben durch PLZneu/Ortneu geändert werden.
Hinweise: Beschränken Sie sich auf die Änderung mit der ID=3. (Die vollständige Lösung ist erst mit SQL-Programmierung möglich.) Bei dieser Änderungsdatei handelt es sich nur um fiktive Daten, keine echten Änderungen.
| Lösung zu Übung 1 | Definitionen | Zur Übung |
Richtig sind 2, 3, 4, 7, 9; falsch sind 1, 5, 6, 8.
| Lösung zu Übung 2 | Ein einzelner Wert | Zur Übung |
select ID, Vertragsnummer, Abschlussdatum, Art
from Versicherungsvertrag
where Mitarbeiter_ID
in ( select ID
from Mitarbeiter
where Name = 'Braun'
and Vorname = 'Christian' );
| Lösung zu Übung 3 | Ein einzelner Wert | Zur Übung |
select ID, Vertragsnummer, Abschlussdatum, Art
from Versicherungsvertrag
where Versicherungsnehmer_ID
= ( select ID from Versicherungsnehmer
where Name ='Heckel Obsthandel GmbH' );
| Lösung zu Übung 4 | Eine Liste von Werten | Zur Übung |
select ID, Vertragsnummer, Abschlussdatum, Art
from Versicherungsvertrag
where Versicherungsnehmer_ID
in ( select ID from Versicherungsnehmer
where Name ='Heckel Obsthandel GmbH' );
| Lösung zu Übung 5 | Eine Liste von Werten | Zur Übung |
select ID, Kennzeichen, Fahrzeugtyp_ID as TypID
from Fahrzeug fz
where ID in ( select Fahrzeug_ID
from Zuordnung_sf_fz zu
join Schadensfall sf on sf.ID = zu.Schadensfall_ID
where EXTRACT(YEAR from sf.Datum) = 2008 );
| Lösung zu Übung 6 | Eine Liste von Werten | Zur Übung |
SELECT distinct ft.ID as TypID, ft.Bezeichnung as Typ, fh.Name as Hersteller
FROM Fahrzeugtyp ft
inner join Fahrzeughersteller fh on fh.ID = ft.Hersteller_ID
right join Fahrzeug fz on ft.ID = fz.Fahrzeugtyp_ID
WHERE fz.ID IN ( SELECT Fahrzeug_ID
FROM Zuordnung_sf_fz zu
JOIN Schadensfall sf ON sf.ID = zu.Schadensfall_ID
WHERE EXTRACT(YEAR FROM sf.Datum) = 2008 );
Beachten Sie vor allem, dass die WHERE-Bedingung übernommen werden konnte, aber die Tabellen anders zu verknüpfen sind. Die Bedingung könnte in die ON-Klausel einbezogen werden; da sie aber die Auswahl beschränken soll, ist die WHERE-Klausel vorzuziehen.
| Lösung zu Übung 7 | Eine Tabelle als Ergebnis | Zur Übung |
SELECT fz.ID, fz.Kennzeichen, Typen.ID AS TYP, Typen.Bezeichnung
FROM Fahrzeug fz
join ( SELECT ID, Bezeichnung
FROM Fahrzeugtyp
WHERE Hersteller_ID =
( SELECT ID FROM Fahrzeughersteller
WHERE Name = 'Volkswagen' )
) Typen on fz.Fahrzeugtyp_ID = Typen.ID;
| Lösung zu Übung 8 | Eine Tabelle als Ergebnis | Zur Übung |
SELECT vv.ID as VV, vv.Vertragsnummer, vv.Abschlussdatum, vv.Art,
mi.ID as MI, mi.Name, mi.Vorname
from Versicherungsvertrag vv
right join ( select MIN(vv2.ID) as ID, vv2.Mitarbeiter_ID
from Versicherungsvertrag vv2
group by vv2.Mitarbeiter_id ) Temp
on Temp.ID = vv.ID
right join Mitarbeiter mi on mi.ID = vv.Mitarbeiter_ID
where mi.Abteilung_ID = ( select ID from Abteilung
where Bezeichnung = 'Vertrieb' );
Erläuterungen: Wir benötigen eine einfache Unterabfrage, um die Liste der Mitarbeiter für „Vertrieb“ zu erhalten, und wir benötigen eine Unterabfrage, die uns zur Mitarbeiter-ID die kleinste Vertrags-ID liefert. Wegen der Aufgabenstellung „zu jedem Mitarbeiter“ sowie „mit einigen Angaben“ muss es sich bei beiden Verknüpfungen um einen RIGHT JOIN handeln.
| Lösung zu Übung 9 | Speichern mit Unterabfrage | Zur Übung |
update Versicherungsnehmer
set PLZ, Ort
= ( Select PLZneu, Ortneu
from PLZ_Aenderg
where ID = 3 )
where PLZ = ( Select PLZalt
from PLZ_Aenderg
where ID = 3 )
and Ort = ( Select Ortalt
from PLZ_Aenderg
where ID = 3 );
Vielleicht funktioniert diese Variante bei Ihrem DBMS nicht; dann ist die folgende Version nötig:
update Versicherungsnehmer
set PLZ = ( Select PLZneu
from PLZ_Aenderg
where ID = 3 ),
Ort = ( Select Ortneu
from PLZ_Aenderg
where ID = 3 )
where PLZ = ( Select PLZalt
from PLZ_Aenderg
where ID = 3 )
and Ort = ( Select Ortalt
from PLZ_Aenderg
where ID = 3 );