Nützliche Erweiterungen
In diesem Kapitel werden verschiedene Erweiterungen des SELECT-Befehls genauer behandelt.
Die Beispiele beziehen sich auch hier auf den Anfangsbestand der Beispieldatenbank; auf die Ausgabe der selektierten Datensätze wird wiederum weitgehend verzichtet. Bitte probieren Sie alle Beispiele aus und nehmen Sie verschiedene Änderungen vor, um die Auswirkungen zu erkennen.
DISTINCT – keine doppelten Einträge
BearbeitenWenn Sie den DISTINCT-Parameter bei einem SELECT-Befehl angeben, erhalten Sie nur eindeutige Ergebnisse:
SELECT distinct Fahrzeug_ID
from Zuordnung_SF_FZ;
AusgabeFahrzeug_ID
3
4
5
6
7
Bitte beachten Sie: Als „eindeutig“ gilt immer die gesamte Zeile, also alle Spalten zusammen. Die folgende Abfrage liefert alle Datensätze; Fahrzeuge mit mehreren Schadensfällen stehen auch mehrfach in der Liste.
Nur theoretisch DISTINCT, praktisch nichtSELECT distinct Fahrzeug_ID, ID
from Zuordnung_SF_FZ;
Als Gegenstück gibt es den ALL-Parameter, der ausdrücklich alle Datensätze abfragt. Da dies der Standardwert ist, wird er äußerst selten benutzt:
SELECT all Fahrzeug_ID
from Zuordnung_SF_FZ
Beschränkung auf eine Anzahl Zeilen
BearbeitenHäufig will man nicht sofort das gesamte Ergebnis sehen, sondern nur einen Teil der Zeilen.
Vor allem im Netzwerk kostet es seine Zeit, eine größere Menge von Datensätzen zu übertragen. Es ist deshalb oft praktisch, zunächst einen Teil des Ergebnisses zu holen und anzuzeigen. Während der Anwender sich mit diesem Teilergebnis beschäftigt, wird „im Hintergrund“ der nächste Abschnitt geholt usw.
Im SQL-Standard gibt es dafür (noch) kein Verfahren. Abweichend vom üblichen Vorgehen in diesem Buch erhalten Sie Lösungen für verschiedene DBMS.
Anstelle konstanter Werte (ohne Klammern) kann in allen folgenden Fällen auch ein SQL-Ausdruck (in Klammern) angegeben werden.
Firebird: FIRST SKIP oder ROWS
BearbeitenFirebird bietet gleich zwei Lösungen an, die erste mit FIRST / SKIP:
SELECT [DISTINCT] [ FIRST <value1> ] [ SKIP <value2> ] <select list> FROM ... /* usw. */
Der FIRST-Parameter gibt an, wie viele Zeilen am Anfang anzuzeigen sind; der SKIP-Parameter legt fest, wie viele Zeilen davor übersprungen werden sollen. Beide Parameter werden einzeln oder zusammen benutzt; sie folgen direkt als erste Klausel nach DISTINCT, noch vor der Spaltenliste. Einige Beispiele:
➤ Nur FIRST zeigt die ersten Zeilen.
select FIRST 10
ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name;
➤ Nur SKIP überspringt die ersten Zeilen.
select SKIP 10
ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name;
➤ FIRST zeigt die ersten 10 Zeilen an, aber wegen SKIP werden vorher 5 Zeilen übersprungen.
select FIRST 10 SKIP 5
ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name;
➤ Mit einem Ausdruck kann dafür gesorgt werden, dass etwa das erste Viertel der Datensätze abgerufen wird:
select FIRST ( (SELECT count(*) from Mitarbeiter) / 4 )
ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name;
Die zweite Firebird-Variante benutzt mit ROWS direkt Zeilennummern:
SELECT ... FROM ... WHERE ... ORDER BY ... ROWS <value1> [ TO <value2> ]
Die ROWS-Parameter legen fest, dass (nur) eine bestimmte Anzahl Zeilen angezeigt werden sollen, die durch die Zeilennummern gekennzeichnet sind.
- Wenn nur ROWS benutzt wird, bezeichnet <value1> die Gesamtzahl der angezeigten Zeilen.
- Wenn ROWS zusammen mit TO benutzt wird, ist <value1> die erste Zeilennummer und <value2> die letzte Zeilennummer.
Einige Beispiele:
➤ Ausgabe der Zeilen 10 bis 20 (also insgesamt 11 Zeilen)
select ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name
ROWS 10 TO 20;
➤ Der erste Datensatz gemäß Sortierung
select ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name
ROWS 1;
➤ Der letzte Datensatz gemäß Sortierung
select ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name desc
ROWS 1;
Vor allem das letzte Beispiel, bei dem mit DESC die Sortierung umgekehrt wird, ist oft sehr nützlich.
Bei einer Interbase-Datenbank sind auch prozentuale Angaben möglich; das ist bei Firebird entfallen.
Microsoft SQL: TOP
BearbeitenDer MS-SQL Server benutzt diese Syntax:
SELECT [DISTINCT] TOP ( <value> ) [PERCENT] [WITH TIES] <select list> FROM ... /* usw. */
Mit den TOP-Parametern wird festgelegt, dass (nur) eine bestimmte Anzahl von Zeilen angezeigt werden sollen, die durch die Zeilennummern gekennzeichnet sind. Diese Parameter folgen direkt als erste Klausel nach DISTINCT, noch vor der Spaltenliste.
- <value> bezeichnet die Gesamtzahl der angezeigten Zeilen. Es wird empfohlen, die Klammern immer zu setzen.
- Wenn TOP zusammen mit PERCENT benutzt wird, handelt es sich dabei um die prozentuale Angabe der Zeilenzahl.
- WITH TIES muss zusammen mit ORDER BY benutzt werden und liefert dann zusätzliche doppelte Zeilen, wenn der letzte Wert nach der Reihenfolge gleich ist den Werten in danach folgenden Zeilen.
Einige Beispiele:
➤ Nur TOP zeigt die ersten Zeilen.
select
TOP 10
ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name;
➤ TOP + PERCENT zeigt z. B. das erste Viertel an.
select
TOP 25 PERCENT
ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name;
➤ OFFSET NEXT für einen Teil der Daten (Paging), z.B. Zeile 11-20
select
ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;;
➤ Der letzte Datensatz gemäß Sortierung
select
TOP 1
ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name desc;
Vor allem das letzte Beispiel, bei dem mit DESC die Sortierung umgekehrt wird, ist oft sehr nützlich.
MySQL und PostgreSQL: LIMIT
BearbeitenDiese DBMS benutzen den LIMIT-Parameter. Dieser Parameter folgt nach ORDER BY, wobei die Sortierung nicht angegeben werden muss.
SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT <value1> OFFSET <value2>
Dabei wird mit <value1> angegeben, wie viele Zeilen am Anfang angezeigt werden sollen. Mit <value2> kann nach dem Begriff OFFSET außerdem angegeben werden, wie viele Zeilen davor übersprungen werden sollen.
➤ Es werden die ersten 10 Zeilen angezeigt.
select ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name
LIMIT 10;
➤ Es werden die ersten 10 Zeilen angezeigt, aber vorher werden 5 Zeilen übersprungen.
select ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name
LIMIT 10 OFFSET 5;
➤ Der letzte Datensatz gemäß Sortierung
select ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name desc
LIMIT 1;
Vor allem das letzte Beispiel, bei dem mit DESC die Sortierung umgekehrt wird, ist oft sehr nützlich.
Eine andere Schreibweise für diesen Parameter verzichtet auf das Wort OFFSET:
SELECT ... FROM ... WHERE ORDER BY ... LIMIT [ <value2>, ] <value1>
Bei dieser Variante wird genauso mit <value1> angegeben, wie viele Zeilen am Anfang angezeigt werden sollen. Mit <value2> kann außerdem angegeben werden, wie viele Zeilen davor übersprungen werden sollen; dieser Wert wird jedoch zuerst angegeben und durch ein Komma von der gewünschten Zeilenzahl getrennt.
Die Bedeutung beider Varianten ist identisch, sodass Beispiele nicht nötig sind. Es ist wohl Geschmackssache, welche Version „eingängiger“ ist.
Oracle: ROWNUM
BearbeitenBei dem DBMS Oracle gibt es bei jedem SELECT-Ergebnis eine implizite Spalte Rownum. Man kann diese Spalte mit ausgeben lassen. Solange man kein ORDER BY angibt, ist die Reihenfolge der ausgegebenen Sätze nicht festgelegt. Dieselbe Abfrage kann an einem anderen Tag durchaus eine andere Nummerierung der Sätze hervorbringen. Das liegt z. B. daran, dass jemand die Datensätze in der Zwischenzeit reorganisiert hat.
SELECT Name, rownum
FROM Mitarbeiter;
AusgabeNAME ROWNUM
------- ------
Müller 1
Schneider 2
Meyer 3
Schmitz 4 /* usw. */
Wenn man diese Spalte Rownum nicht angibt, dann wird sie auch nicht ausgegeben. Ihr Vorteil ist, dass man sie auch bei WHERE verwenden kann:
SELECT Name
FROM Mitarbeiter
WHERE Rownum <= 2;
AusgabeNAME ROWNUM
------- ------
Müller 1
Schneider 2
Folgende Formulierung funktioniert allerdings nicht, wenn man nur den 10. Satz ausgeben will:
Oracle-VersionSELECT Name
FROM Mitarbeiter;
WHERE Rownum = 10;
Das liegt daran, dass der Zähler Rownum nur die Zeilen zählt, die auch wirklich ausgegeben werden. Wenn die Tabelle 500 Sätze hat, dann wird bei jedem Satz geprüft, ob Rownum bereits den Wert 10 erreicht hat. Das ist jedoch nie der Fall, da der Zähler immer den Wert 0 behält.
Da muss man schon das DBMS zwingen, die Ergebnismenge mit der Rownum zwischenzuspeichern. Dann geht es:
Oracle-VersionSELECT Name
FROM ( SELECT Name, rownum R
FROM Mitarbeiter )
WHERE R = 10;
Welcher Satz dabei ausgegeben wird, ist jedoch dem Zufall überlassen.
Wenn man die Ergebnismenge sortiert ausgeben will und dabei nur die ersten 6 Sätze ausgegeben haben will, dann funktioniert die folgende Formulierung nicht:
Oracle-VersionSELECT Name
FROM Mitarbeiter;
WHERE Rownum <= 6
ORDER BY Name;
Man wird feststellen, dass zwar nur 6 Sätze ausgegeben werden, dass das aber keinesfalls immer die alphabetisch ersten 6 Namen sind. Das liegt daran, dass zunächst WHERE ausgewertet wird und danach erst sortiert wird. Es werden also – wie bei den vorangegangenen Beispielen beschrieben – beliebige 6 Sätze gelesen, und nur diese 6 Sätze werden sortiert.
Für die richtige Lösung muss die Datenbank schon etwas mehr tun. Sie muss zuerst alle vorhandenen Sätze sortieren und dann die ersten 6 Sätze ausgeben:
Oracle-VersionSELECT Name
FROM ( SELECT Name
FROM Mitarbeiter
ORDER BY Name )
WHERE Rownum <= 6;
Sybase: ROWCOUNT
BearbeitenBei diesem DBMS wird zuerst die Anzahl der gewünschten Zeilen angegeben, danach folgt der SELECT-Befehl.
SET rowcount 10;
SELECT Name, rownum
FROM Mitarbeiter;
Mehrere Abfragen zusammenfassen
BearbeitenUNION – Vereinigung
BearbeitenMit der UNION-Klausel werden mehrere Abfragen verknüpft und als einheitliche Ergebnismenge geliefert. Dieses Verfahren ist Standard und steht in jedem DBMS zur Verfügung. Es ist vor allem in zwei Situationen sinnvoll:
- Die Daten stammen aus verschiedenen Tabellen mit ähnlicher Struktur und sollen gemeinsam angezeigt werden.
- Die Daten stammen aus derselben Tabelle; die Auswahlbedingungen sind so komplex, dass eine einzelne Abfrage nicht möglich, nicht sinnvoll oder zu unübersichtlich wäre.
Die Syntax einer solchen Verknüpfung sieht aus wie folgt, wobei auch mehr als zwei Abfragen verknüpft werden können:
SELECT <Spaltenliste1>
FROM <Tabellenliste1>
WHERE <Bedingungen1>
UNION [ DISTINCT | ALL ]
SELECT <Spaltenliste2>
FROM <Tabellenliste2>
WHERE <Bedingungen2>
Bei den einzelnen Abfragen können grundsätzlich alle Klauseln benutzt werden. Bitte beachten Sie folgende Bedingungen:
- Alle Einzelabfragen müssen in der Anzahl und Reihenfolge der Ergebnis-Spalten übereinstimmen. Die Datentypen müssen je nach DBMS und Version genau gleich sein oder zumindest so ähnlich, dass sie automatisch konvertiert werden können.
- Die Spaltennamen werden aus der ersten Spaltenliste übernommen, ggf. unter Berücksichtigung von Alias-Namen.
- Grundsätzlich werden doppelte Zeilen aus den Einzelabfragen unterdrückt, d. h. DISTINCT ist Standard und kann weggelassen werden. Wenn doppelte Zeilen angezeigt werden sollen, ist ALL anzugeben.
- Die Benutzung von Klammern sowie ORDER BY für das Gesamtergebnis wird je nach DBMS unterschiedlich geregelt.
Das folgende Beispiel ist eine einfache Zusammenfassung aller Fahrzeuge aus den Tabellen Fahrzeuge und Dienstwagen:
select ID, Kennzeichen, Farbe
from Dienstwagen
union
select ID, Kennzeichen, Farbe
from Fahrzeug;
Im folgenden Beispiel werden als Spalte Var Konstanten eingetragen, die die Herkunft der Daten angeben, und es werden verschiedene Auswahlbedingungen benutzt.
select 'D' as Var, ID, Kennzeichen, Farbe
from Dienstwagen
where Fahrzeugtyp_ID <= 3
union
select 'F', ID, Kennzeichen, Farbe
from Fahrzeug
where Kennzeichen LIKE 'B%';
Wenn die Ergebnismenge sortiert werden soll, werden je nach DBMS unterschiedliche Schreibweisen benötigt.
( select 'D' as Var, ID, Kennzeichen, Farbe
from Dienstwagen
where Fahrzeugtyp_ID <= 3 )
union
( select 'F', ID, Kennzeichen, Farbe
from Fahrzeug
where Fahrzeugtyp_ID <= 3 )
order by Kennzeichen;
select * from
( select 'D' as Var, ID, Kennzeichen, Farbe
from Dienstwagen
where Fahrzeugtyp_ID <= 3
union
select 'F', ID, Kennzeichen, Farbe
from Fahrzeug
where Fahrzeugtyp_ID <= 3 )
order by Kennzeichen;
Mit der folgenden Abfrage werden verschiedene Listen von Mitarbeitern zusammengefasst: zum einen diejenigen, die im September Geburtstag haben, zum anderen die Leiter mit einer Mobil-Rufnummer:
select Personalnummer, Name, Vorname, Geburtsdatum, Ist_Leiter as Leiter, Mobil
from Mitarbeiter
where EXTRACT(month from Geburtsdatum) = 9
union
select Personalnummer, Name, Vorname, Geburtsdatum, Ist_Leiter, Mobil
from Mitarbeiter
where Ist_Leiter = 'J' and Mobil <> '';
Offensichtlich kann diese Abfrage auch ohne UNION erreicht werden, nämlich durch: WHERE <bedingung1> OR ( <bedingung2> AND <bedingung3> ). Sie können sich aber sicherlich noch viel kompliziertere Situationen vorstellen, vor allem wenn die WHERE-Bedingungen auf Informationen in unterschiedlichen weiteren Tabellen zugreifen müssen.
Andere Varianten
BearbeitenUnter den gleichen Voraussetzungen wie UNION gibt es weitere Wege, um Einzelabfragen zusammenzufassen. Diese stehen aber nur vereinzelt zur Verfügung; wir beschränken uns deshalb auf Hinweise.
Mit INTERSECT (MS-SQL) erhält man den Durchschnitt der Teilabfragen.
SELECT <Spaltenliste1> FROM <Tabellenliste1> WHERE <Bedingungen1>
INTERSECT
SELECT <Spaltenliste2> FROM <Tabellenliste2> WHERE <Bedingungen2>
Die Ergebnismenge besteht aus allen Zeilen, die sowohl zum ersten SELECT-Befehl als auch zum zweiten SELECT-Befehl gehören.
Mit EXCEPT (MS-SQL) oder MINUS (Oracle) wird die Differenz der Teilabfragen zurückgegeben.
SELECT <Spaltenliste1> FROM <Tabellenliste1> WHERE <Bedingungen1>
EXCEPT | MINUS
SELECT <Spaltenliste2> FROM <Tabellenliste2> WHERE <Bedingungen2>
Die Ergebnismenge besteht aus allen Zeilen, die zum ersten SELECT-Befehl gehören, aber beim zweiten SELECT-Befehl nicht enthalten sind.
Wie bei UNION können auch solche Verknüpfungen durch sehr komplizierte JOINs und WHERE-Klauseln nachgebildet werden.
CASE WHEN – Fallunterscheidungen
BearbeitenImmer wieder möchte man innerhalb eines SQL-Befehls je nach Situation unterschiedliche Werte erhalten. Das einfachste Beispiel ist die Tabelle Mitarbeiter: Anstelle des Feldinhalts Ist_Leiter mit 'J' oder 'N' kann 'Leiter' oder eine leere Zeichenkette angezeigt werden. Dafür ist der CASE-Ausdruck vorgesehen, den es in zwei Varianten gibt.
Als <expression> können wahlweise konstante Werte oder komplexe Ausdrücke verwendet werden. Der CASE-Ausdruck ist nicht nur für SELECT, sondern auch für Speichern-Befehle geeignet und nicht nur (wie in den meisten Beispielen) als Teil der Spaltenliste, sondern auch in der WHERE-Klausel oder an anderen Stellen, an denen entsprechende Werte benötigt werden.
Der Wert, den der CASE-Ausdruck zurückgibt, erhält einen passenden Datentyp, der sich aus den Result-Werten ergibt.
Simple Case – die einfache Fallunterscheidung
BearbeitenDie einfache Variante hat folgende Syntax:
CASE <expression>
WHEN <expression1> THEN <result1>
[ WHEN <expression2> THEN <result2> ] /* usw. */
[ ELSE <default> ]
END
Bei dieser Version wird der Wert, der sich durch den ersten Ausdruck beim CASE ergibt, nacheinander mit den Ausdrücken nach WHEN verglichen. Sobald eine Gleichheit festgestellt wird, wird der Wert, der sich durch den Result-Ausdruck ergibt, als Wert für den CASE-Ausdruck übernommen. Wenn es keine Übereinstimmung gibt, wird der Wert des Default-Ausdrucks übernommen; wenn ELSE nicht vorhanden ist, wird NULL als Wert genommen. Beispiele:
Ein vorhandener Feldinhalt, nämlich 'J' oder 'N', wird für die Ausgabe durch andere Texte ersetzt:
select Personalnummer as Pers, Name, Vorname, Geburtsdatum as Geb,
CASE Ist_Leiter
WHEN 'J' THEN 'Leiter'
ELSE ''
END as Leiter,
Mobil
from Mitarbeiter;
AusgabePERS NAME VORNAME GEB LEITER MOBIL
--------- ------------ --------- ---------- ------ -----
40001 Langmann Matthias 28.03.1976 Leiter
40002 Peters Michael 15.11.1973
50001 Pohl Helmut 27.10.1980 Leiter (0171) 4123456
50002 Braun Christian 05.09.1966 (0170) 8351647
Dieselbe Lösung ohne ELSE-Zweig ändert nur die Anzeige:
select Personalnummer as Pers, Name, Vorname, Geburtsdatum as Geb,
CASE Ist_Leiter
WHEN 'J' THEN 'Leiter'
END as Leiter,
Mobil
from Mitarbeiter;
AusgabePERS NAME VORNAME GEB LEITER MOBIL
--------- ------------ --------- ---------- ------ -----
40001 Langmann Matthias 28.03.1976 Leiter
40002 Peters Michael 15.11.1973 <null>
50001 Pohl Helmut 27.10.1980 Leiter (0171) 4123456
50002 Braun Christian 05.09.1966 <null> (0170) 8351647
Bei der Art der Versicherungsverträge möchten wir die Varianten im „Klartext“ lesen:
select Vertragsnummer as Vertrag, Abschlussdatum as Datum,
CASE Art
WHEN 'VK' THEN 'Vollkasko'
WHEN 'TK' THEN 'Teilkasko'
WHEN 'HP' THEN 'Haftpflicht'
END
from Versicherungsvertrag;
AusgabeVERTRAG DATUM CASE
------- ---------- -----------
DB-04 25.01.2008 Haftpflicht
RH-01 11.12.1976 Vollkasko
RD-02 29.01.1988 Haftpflicht
RM-03 13.01.1996 Haftpflicht
RD-04 23.11.2006 Haftpflicht
RR-05 29.06.1990 Teilkasko
Searched Case – die komplexe Fallunterscheidung
BearbeitenDie komplexe Variante hat keinen Ausdruck hinter dem CASE; sie arbeitet nach folgender Syntax:
CASE
WHEN <bedingung1> THEN <result1>
[ WHEN <bedingung2> THEN <result2> ] /* usw. */
[ ELSE <default> ]
END
Bei dieser Variante werden nacheinander Bedingungen geprüft. Sobald eine Bedingung als WAHR festgestellt wird, wird der Wert, der sich durch den Result-Ausdruck ergibt, als Wert für den CASE-Ausdruck übernommen. Wenn es keine Übereinstimmung gibt, wird der Wert des Default-Ausdrucks übernommen; wenn ELSE nicht vorhanden ist, wird NULL als Wert genommen. Beispiele:
In einer einzigen Zeile wird angegeben, wieviele Versicherungsverträge innerhalb eines Jahrzehnts abgeschlossen wurden.
select
SUM( CASE WHEN EXTRACT(YEAR from Abschlussdatum) between 1970 and 1979
THEN 1
ELSE 0
END ) as S_197_,
SUM( CASE WHEN EXTRACT(YEAR from Abschlussdatum) between 1980 and 1989
THEN 1
ELSE 0
END ) as S_198_,
SUM( CASE WHEN EXTRACT(YEAR from Abschlussdatum) between 1990 and 1999
THEN 1
ELSE 0
END ) as S_199_,
SUM( CASE WHEN EXTRACT(YEAR from Abschlussdatum) >= 2000
THEN 1
ELSE 0
END ) as S_200_
from Versicherungsvertrag;
AusgabeS_197_ S_198_ S_199_ S_200_
6 6 6 5
Dazu wird für jedes Jahrzehnt eine Spalte vorgesehen. Jede Spalte enthält einen CASE-Ausdruck mit einer WHEN-Bedingung. Wenn für eine Zeile diese Bedingung TRUE ergibt, wird 1 zur Summe dieses Jahrzehnts addiert, andernfalls 0.
Der CASE-Ausdruck soll innerhalb von ORDER BY eine unterschiedliche
Sortierung, abhängig von der Art der Versicherung, erreichen:
select ID, Art,
Abschlussdatum as Datum, Vertragsnummer as Vertr,
Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
from Versicherungsvertrag
order by Art,
CASE WHEN Art = 'TK' THEN ID
WHEN Art = 'VK' THEN Mitarbeiter_ID
WHEN Art = 'HP' THEN Fahrzeug_ID
END;
AusgabeID ART DATUM VERTR MIT FZG
------------------------------------
14 HP 15.03.1998 KG-03 9 16
18 HP 17.05.2000 HG-03 9 17
19 HP 21.09.2004 HB-04 9 19
10 HP 23.11.2006 RD-04 9 20
6 HP 25.01.2008 DB-04 9 21
15 HP 27.03.1988 KV-04 10 22
11 TK 29.06.1990 RR-05 9 23
12 TK 14.02.1978 KB-01 10 6
21 VK 20.06.1982 XH-02 9 8
23 VK 19.07.2002 XO-04 9 18
7 VK 11.12.1976 RH-01 10 5
22 VK 05.06.1992 XW-03 10 13
Hier wurden alle Abschnitte numerisch sortiert. Auch die Teilsortierung nach Datum funktioniert, muss aber unter Umständen überarbeitet werden:
Firebird-Versionselect ID, Art,
Abschlussdatum as Datum, Vertragsnummer as Vertr,
Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
from Versicherungsvertrag
order by Art,
CASE WHEN Art = 'TK' THEN ID
WHEN Art = 'VK' THEN Mitarbeiter_ID
WHEN Art = 'HP' THEN Abschlussdatum
END;
AusgabeInvalid token.
SQL error code = -104.
Datatypes are not comparable in expression CASE.
Auch wenn Firebird (wie im ersten Beispiel) Namen von Spalten akzeptiert, werden die Datentypen der Spalten vergleichen. Diese sind offensichtlich nicht kompatibel; also bricht Firebird diese Abfrage ab. Aber in der ORDER BY-Klausel können auch die Nummern der Spalten angegeben werden; dann klappt es:
Firebird-Versionselect ID, Art,
Abschlussdatum as Datum, Vertragsnummer as Vertr,
Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
from Versicherungsvertrag
order by Art,
CASE WHEN Art = 'TK' THEN ID
WHEN Art = 'VK' THEN Mitarbeiter_ID
WHEN Art = 'HP' THEN 2
END;
Bitte wundern Sie sich nicht über das identische Ergebnis wie oben bei der Sortierung nach Fahrzeug_ID: Die Fahrzeuge wurden in der gleichen Reihenfolge erfasst wie die Verträge; also stimmen in der ersten Zeit beide Sortierungen überein.
Die Sortierung kann nur einheitlich festgelegt werden, aber nicht mal so, mal so:
Art der Sortierung kann nicht gemischt werden.select ID, Art,
Abschlussdatum as Datum, Vertragsnummer as Vertr,
Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
from Versicherungsvertrag
order by Art,
CASE
WHEN Art = 'TK' THEN ID ASC
WHEN Art = 'VK' THEN Mitarbeiter_ID ASC
WHEN Art = 'HP' THEN 2 DESC
END;
AusgabeInvalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 7, column 39. ASC.
Einheitliche Sortierung DESC sollte möglich seinselect ID, Art,
Abschlussdatum as Datum, Vertragsnummer as Vertr,
Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
from Versicherungsvertrag
order by Art,
CASE
WHEN Art = 'TK' THEN ID
WHEN Art = 'VK' THEN Mitarbeiter_ID
WHEN Art = 'HP' THEN 2
END DESC;
Zumindest unter Firebird gelingt dennoch keine korrekte Sortierung: nur der Abschnitt 'TK' wird absteigend sortiert, die beiden anderen nicht. Sie müssen aber sowieso selbst ausprobieren, was in welchem DBMS möglich ist und was nicht.
CASE-Ausdruck beim Speichern
BearbeitenWie beim SELECT-Befehl kann ein CASE-Ausdruck auch beim Speichern an verschiedenen Stellen benutzt werden, vor allem bei den Zuweisungen der Werte für INSERT und UPDATE sowie bei der WHERE-Klausel für UPDATE und DELETE. Da sich diese Kapitel vor allem mit Abfragen beschäftigen, beschränken wir uns auf ein Beispiel.
Die Zuständigkeit der Mitarbeiter für die Versicherungsverträge wird neu geregelt:
- Vollkasko-Verträge gehören zum Aufgabenbereich des Abteilungsleiters (Mitarbeiter-ID 9).
- Teilkasko-Verträge gehören zum Aufgabenbereich des Mitarbeiters mit ID 12.
- Die Haftpflicht-Verträge werden in Abhängigkeit von der ID aufgeteilt auf die Mitarbeiter 10 und 11.
update Versicherungsvertrag
set Mitarbeiter_id =
CASE WHEN Art = 'VK' THEN 9
WHEN Art = 'TK' THEN 12
WHEN Art = 'HP' THEN 10 + MOD(ID, 2)
END;
Es wird also eine normale SET-Anweisung geschrieben. Die CASE-Anweisung liefert die benötigten Werte, wobei für den Fall „Haftpflicht“ für gerade IDs der Wert (10+0) und für ungerade IDs der Wert (10+1) gesetzt wird. Eine WHERE-Klausel ist nicht erforderlich, weil alle Verträge neu zugeordnet werden sollen.
Zusammenfassung
BearbeitenIn diesem Kapitel lernten Sie einige nützliche Erweiterungen vor allem für Abfragen kennen:
- Auf den DISTINCT-Parameter zur Auswahl wird genauer eingegangen.
- Je nach DBMS wird unterschiedlich geregelt, wenn nur eine gewisse Anzahl von Zeilen gewünscht wird.
- Mit UNION wird das Ergebnis von zwei oder mehr Abfragen in einer gemeinsamen Ergebnistabelle zusammengefasst.
- Mit CASE WHEN sind Fallunterscheidungen für gewisse Werte möglich; dies ist auch beim Speichern von Datensätzen sowohl für die Werte als auch für die WHERE-Klausel hilfreich.
Übungen
Bearbeiten
| Übung 1 | Richtig oder falsch? | Zur Lösung |
Welche der folgenden Aussagen sind richtig, welche sind falsch?
- Es ist Standard, den Parameter ALL bei einem SELECT-Befehl anzugeben.
- Bei SELECT DISTINCT werden nur solche Zeilen angezeigt, die sich in mindestens einer Spalte unterscheiden.
- Es ist äußerst selten erforderlich, das Ergebnis einer Abfrage nur in Teilen zu holen.
- Für die Anzeige oder Verwendung einer ROW_NUMBER hat der SQL-Standard (2003) ein Verfahren vorgeschrieben.
- Ein solches Verfahren wird von den meisten DBMS verwendet, ist aber in unterschiedlicher Weise verwirklicht.
- Bei UNION, INTERSECT usw. müssen die ausgewählten Spalten von der Anzahl her übereinstimmen.
- Bei UNION, INTERSECT usw. müssen die ausgewählten Spalten vom Datentyp her genau übereinstimmen.
| Übung 2 | DISTINCT und ALL | Zur Lösung |
Bitte überprüfen Sie die folgenden Befehle:
-- Variante 1
select distinct COUNT(*)
from Mitarbeiter
group by Abteilung_ID
-- Variante 2
select all COUNT(*)
from Mitarbeiter
group by Abteilung_ID
Worin unterscheidet sich die Ausgabe? Welche wichtige Information fehlt vor allem bei Variante 2?
| Übung 3 | UNION, INTERSECT usw. | Zur Lösung |
Was ist an den folgenden Befehlen falsch oder fragwürdig? Wie kann man das DBMS dazu bringen, den „fragwürdigen“ Befehl auf jeden Fall auszuführen?
-- Befehl 1
select Name, Vorname from Mitarbeiter
union
select Name, Vorname, Geburtsdatum from Versicherungsnehmer
-- Befehl 2
select Name, Vorname, Abteilung_ID from Mitarbeiter
union
select Name, Vorname, Geburtsdatum from Versicherungsnehmer
| Übung 4 | UNION, INTERSECT usw. | Zur Lösung |
Erstellen Sie eine Abfrage für die Tabellen Versicherungsvertrag, Fahrzeug, Zuordnung_SF-FZ, in der folgende Bedingungen berücksichtigt werden:
- Es sollen Vertragsnummer, Abschlussdatum, Kennzeichen sowie ggf. anteilige Schadenshöhe angezeigt werden.
- Fahrzeuge mit einem Schaden sollen vollständig angezeigt werden.
- Fahrzeuge ohne Schaden sollen nur angezeigt werden, wenn der Vertrag vor 1990 abgeschlossen wurde.
- Das Ergebnis soll nach Schadenshöhe und Kennzeichen sortiert werden.
Benutzen Sie UNION zur Verknüpfung der Verträge mit und ohne Schaden.
| Übung 5 | Fallunterscheidung für Nachschlagewerte | Zur Lösung |
Schreiben Sie einen SELECT-Befehl, bei dem die korrekte Briefanrede für die Mitarbeiter erstellt wird.
Hinweis: Benutzen Sie CONCAT zum Verknüpfen mehrerer Zeichenketten.
| Übung 6 | Fallunterscheidung für Bereiche | Zur Lösung |
Zur Tabelle Fahrzeug soll aus dem Kennzeichen die regionale Herkunft abgeleitet und angezeigt werden. Schreiben Sie eine Abfrage für diese Spalten Kennzeichen und Herkunft.
Hinweis: Benutzen Sie POSITION zur Feststellung des Bindestrichs sowie SUBSTRING.
Zusatzfrage: Wie müsste ein solches Problem sinnvollerweise gelöst werden, falls eine solche Zuordnung häufiger und allgemeiner benötigt wird?
| Übung 7 | Fallunterscheidung für mehrere Varianten | Zur Lösung |
Aus der Tabelle Versicherungsnehmer sollen Name, Vorname und Anschrift angezeigt werden. Außerdem soll jede Adresse eine Markierung bekommen: F = (eigene) Firmenkunden, P = (eigene) Privatkunden, X = eXterne Verträge (d. h. Kunden fremder Versicherungsgesellschaften). Begründen Sie die Reihenfolge der WHEN-ELSE-Bedingungen.
| Übung 8 | Fallunterscheidung beim Speichern | Zur Lösung |
Schreiben Sie einen UPDATE-Befehl, der nach Ablauf eines Versicherungsjahres den Prämiensatz für das nächste Jahr ändert. Berücksichtigen Sie dabei folgende Bedingungen:
- Neue Verträge, für die noch kein Prämiensatz gilt, werden auf 200 [Prozent] gesetzt.
- Verträge mit einem Prämiensatz von mindestens 100 werden um 20 reduziert.
- Verträge mit einem Prämiensatz von weniger als 100 werden um 10 reduziert.
- Der Mindestsatz von 30 darf nicht unterschritten werden.
Ignorieren Sie dabei zunächst, dass dies nur im Fall von Schadensfreiheit gelten darf und innerhalb eines Jahres nur einmal neu berechnet werden darf.
| Übung 9 | Fallunterscheidung beim Speichern | Zur Lösung |
Bei genauerer Untersuchung von Übung 8 sind weitere Bedingungen im UPDATE-Befehl erforderlich.
- In welcher Weise könnte die Schadensfreiheit berücksichtigt werden? Abhängig von der Höhe des Schadens soll sich der Prämiensatz gar nicht, wenig oder mehr erhöhen.
- Wie kann unter Verwendung des Datums Prämienänderung gesichert werden, dass die Neuberechnung nur einmal jährlich stattfinden darf?
Versuchen Sie, dies im selben UPDATE-Befehl zu berücksichtigen. Sie sollen keinen Befehl schreiben, sondern die notwendigen Klauseln erwähnen und erläutern.
Lösungen
Bearbeiten
| Lösung zu Übung 1 | Richtig oder falsch? | Zur Übung |
Die Aussagen 2, 5, 6 sind richtig. Die Aussagen 1, 3, 4, 7 sind falsch.
| Lösung zu Übung 2 | DISTINCT und ALL | Zur Übung |
Variante 1 nennt jede Anzahl von Mitarbeitern pro Abteilung genau einmal. Bei Variante 2 gibt es diese Anzeige für jede Abteilung einzeln. Dabei fehlt vor allem die Angabe der Abteilung_ID, ohne die die Ausgabe ziemlich sinnlos ist.
| Lösung zu Übung 3 | UNION, INTERSECT usw. | Zur Übung |
Bei Befehl 1 werden unterschiedlich viele Spalten ausgewählt, das ist unzulässig.
Bei Befehl 2 unterscheiden sich die Datentypen der dritten Spalte; es ist unsicher, ob das DBMS die unterschiedlichen Spalten einheitlich als Zeichenkette (zulässig) oder nicht-kompatibel (unzulässig) interpretieren will.
Ein CAST beider Spalten auf VARCHAR macht die Datentypen kompatibel.
| Lösung zu Übung 4 | UNION, INTERSECT usw. | Zur Übung |
Eine mögliche Variante lautet so (Firebird-Version für ORDER BY):
select * from (
select Vertragsnummer, Abschlussdatum, Kennzeichen, Schadenshoehe
from Versicherungsvertrag vv
inner join Fahrzeug fz on fz.ID = vv.Fahrzeug_ID
right join Zuordnung_SF_FZ zu on fz.ID = zu.Fahrzeug_ID
union
select Vertragsnummer, Abschlussdatum, Kennzeichen, 0
from Versicherungsvertrag vv
inner join Fahrzeug fz on fz.ID = vv.Fahrzeug_ID
where Abschlussdatum < '01.01.1990' and not
EXISTS ( select ID from Zuordnung_SF_FZ zu where zu.Fahrzeug_ID = fz.ID )
) order by Schadenshoehe, Kennzeichen;
Der zweite SELECT kann auch so geschrieben werden:
select Vertragsnummer, Abschlussdatum, Kennzeichen, 0
from Versicherungsvertrag vv
inner join Fahrzeug fz on fz.ID = vv.Fahrzeug_ID
left join Zuordnung_SF_FZ zu on fz.ID = zu.Fahrzeug_ID
where Abschlussdatum < '01.01.1990' and zu.ID is null
| Lösung zu Übung 5 | Fallunterscheidung für Nachschlagewerte | Zur Übung |
select CONCAT( 'Sehr geehrte',
CASE Geschlecht
WHEN 'M' THEN 'r Herr '
WHEN 'W' THEN ' Frau '
ELSE '/r Frau/Herr '
END,
Name ) as Anrede
from Mitarbeiter;
Hinweise: CONCAT oder die String-Verknüpfung erzeugen u. U. eine falsche Länge des ersten Teils. Das kann mit TRIM „repariert“ werden; dann ist aber das abschließende Leerzeichen hinzuzufügen. Der ELSE-Zweig ist überflüssig, weil Geschlecht nicht NULL sein kann; er wird nur der Vollständigkeit halber genannt.
| Lösung zu Übung 6 | Fallunterscheidung für Bereiche | Zur Übung |
select Kennzeichen,
CASE SUBSTRING(Kennzeichen from 1 for POSITION('-', Kennzeichen)-1 )
WHEN 'RE' THEN 'Kreis Recklinghausen'
WHEN 'GE' THEN 'Stadt Gelsenkirchen'
WHEN 'E' THEN 'Stadt Essen'
WHEN 'BO' THEN 'Stadt Bochum'
WHEN 'HER' THEN 'Stadt Herne'
ELSE 'unbekannt'
END as Herkunft
from Fahrzeug
order by 2, 1;
Zur Zusatzfrage: Eine Tabelle Region o. ä. würde mit LEFT JOIN über den o. g. SUBSTRING verknüpft. Auch ein INNER JOIN wäre möglich, dann würden aber Fahrzeuge fehlen, deren Kennzeichen in der Tabelle Region fehlen.
| Lösung zu Übung 7 | Fallunterscheidung für mehrere Varianten | Zur Übung |
select Name, Vorname, PLZ, Strasse, Hausnummer,
CASE
WHEN Eigener_Kunde = 'N' THEN 'X'
WHEN Vorname is null or Fuehrerschein is null THEN 'F'
ELSE 'P'
END as Markierung
from Versicherungsnehmer;
Zur Reihenfolge: Bei den Fremdverträgen wird nicht zwischen Firmen- und Privatkunden unterschieden; dies muss also zuerst geprüft werden. Bei Firmenkunden sind weder Vorname noch Führerschein-Datum registriert, das wird als nächstes geprüft (eines der WHEN-Kriterien würde genügen). Alle übrigen Fälle sind Privatkunden.
| Lösung zu Übung 8 | Fallunterscheidung beim Speichern | Zur Übung |
update Versicherungsvertrag
set Praemiensatz = CASE
WHEN Praemiensatz is null THEN 200
WHEN Praemiensatz >= 100 THEN Praemiensatz - 20
WHEN Praemiensatz >= 40 THEN Praemiensatz - 10
ELSE 30
END
where -- passende Bedingungen
| Lösung zu Übung 9 | Fallunterscheidung beim Speichern | Zur Übung |
- Es wird eine geschachtelte CASE-Anweisung verwendet. Zunächst wird unterschieden, ob überhaupt Schadensfälle aufgetreten sind. Im ersten Zweig „kein Schaden“ wird die Lösung von Übung 7 benutzt. Im zweiten Zweig „mit Schaden“ wird eine ähnliche CASE-Anweisung eingefügt, die abhängig von der Schadenshöhe den Prämiensatz erhöht.
- In der WHERE-Klausel wird geprüft, ob die letzte Prämienänderung überschritten ist. Gleichzeitig mit der Neuberechnung wird dieses Datum um ein Jahr weitergesetzt.
Hinweis: Dieses Verfahren wird ausführlich im Kapitel Prozeduren: Automatisches UPDATE behandelt. Bei Interesse können Sie im dortigen Code den abschließenden UPDATE-Befehl vergleichen.