Berechnete Spalten
Mit Abfragen kann man nicht nur Spalten auswählen, sondern auch völlig neue Spalten aus anderen Spalten oder mit Funktionen erstellen.
Allgemeine Regel
BearbeitenEine neue Spalte wird als Teil einer Abfrage wie folgt erstellt:
<Ausdruck> [AS] <Name der neuen Spalte>
Ausdruck ist allgemein der Hinweis auf etwas, das an der betreffenden Stelle verwendet wird: ein konstanter Wert, der Inhalt einer oder mehrerer Spalten, eine Berechnung mit diesen Spalten, das Ergebnis einer Funktion oder das Ergebnis einer Unterabfrage. Der Spalte, die das Ergebnis aufnimmt, wird mit AS ein eigener Name, ein Alias zugewiesen; das AS kann auch entfallen.
In manchen DBMS ist bei berechneten Spalten die Angabe eines Alias Pflicht, und das Vergessen führt zu einem Fehler, manche erzeugen automatisch einen zufälligen Namen, wenn kein Alias eingegeben wird, und andere meckern erst, wenn man versucht, eine physische Tabelle mit einer namenlosen Spalte zu füllen.
Wenn eine Unterabfrage als berechnete Spalte benutzt wird, ist es unbedingt wichtig, dass sie – wie bei einer Funktion, Berechnung oder Verknüpfung – für jede Zeile der Hauptabfrage genau einen Wert ergibt. In dem untenstehenden Beispiel wird dies durch eine passende WHERE-Bedingung erreicht.
Hinweis: Fast alles, was in diesem Abschnitt über Ausdrücke, Funktionen und Konvertierungen gesagt wird, ist auch bei der Manipulation oder Verarbeitung von Daten wichtig. Die Funktionen und Konvertierungen wurden bereits in den Kapiteln Funktionen und Funktionen (2) behandelt.
Ergebnis von Berechnungen
BearbeitenAlle Werte einer Spalte werden für eine Berechnung verwendet, vorzugsweise eine der Grundrechenarten. Das folgende Beispiel errechnet für alle Zeilen der Tabelle Schadensfall aus dem Feld Schadenshoehe den Nettobetrag (ohne MWSt) und fügt alle weiteren Felder an:
SELECT Schadenshoehe / 1.19 AS Netto,
*
FROM Schadensfall;
Dieses Ergebnis kann auch für weitere Berechnungen verwendet werden, wenn auch nicht direkt mit dem Alias:
Der Alias-Name der neuen Spalte Netto kann nicht nochmals verwendet werden.SELECT Schadenshoehe AS Brutto,
Schadenshoehe / 1.19 AS Netto,
Schadenshoehe - Netto AS MWSt
FROM Schadensfall;
Aber die Berechnung kann erneut zugewiesen werden.SELECT Schadenshoehe AS Brutto,
Schadenshoehe / 1.19 AS Netto,
Schadenshoehe - (Schadenshoehe / 1.19) AS MWSt
FROM Schadensfall;
Man kann davon ausgehen, dass das DBMS die Abfrage soweit optimiert, dass die Berechnung tatsächlich nur einmal ausgeführt werden muss.
Zeichenketten verbinden und bearbeiten
BearbeitenEbenso wie Zahlen für Berechnungen verwendet werden, gibt es die Bearbeitung von Zeichenketten. Das einfachste ist die Verknüpfung (Verkettung) von Strings durch den Operator || bzw. +:
SELECT Name || ', ' || Vorname AS Gesamtname
FROM Mitarbeiter;
AusgabeGesamtname
Müller, Kurt
Schneider, Daniela
In diesem Beispiel wird zwischen die Spalten noch das Komma und ein Leerzeichen als konstanter Text gesetzt.
Bei Textspalten mit fester Feldlänge, die wir in der Beispieldatenbank nicht haben, sollten überzählige Leerzeichen mit der Funktion TRIM abgeschnitten werden:
SELECT TRIM(Name) || ', ' || TRIM(Vorname) AS Gesamtname
FROM Mitarbeiter;
Auf diese Weise kann man auch Teile von Texten übernehmen und den Rest abschneiden:
SELECT Name || ', ' || TRIM( SUBSTRING(Vorname FROM 1 FOR 1) ) || '.' AS Name_kurz
FROM Mitarbeiter;
AusgabeName_kurz
Müller, K.
Schneider, D.
Hinweis: Nach SQL-Standard liefert das Ergebnis von SUBSTRING einen Text von gleicher Länge wie der ursprüngliche Text; die jetzt zwangsläufig folgenden Leerzeichen müssen mit TRIM entfernt werden. Vielleicht ist Ihr DBMS „pfiffiger“ und macht das automatisch.
Ergebnis von Funktionen
BearbeitenIn den bisherigen Beispielen werden Feldinhalte direkt modifiziert und ausgegeben. In den beiden Kapiteln über „Funktionen“ werden neue Informationen berechnet und unabhängig von vorhandenen Zeilen und Spalten ausgegeben:
SELECT COUNT(Farbe) AS Anzahl_Farbe
FROM Fahrzeug;
Bei diesem Beispiel mit einer Aggregatfunktion besteht das Ergebnis aus einer einzelnen Zahl in einer einzigen Zeile. Dies muss aber nicht so sein; vor allem mit Gruppierungen gibt es viele Varianten.
Viele andere Funktionen können besondere Abfragen ermöglichen oder stark erleichtern. EXTRACT nach Monat liefert die Liste aller Geburtstage, sortiert nach Monat:
SELECT Name, Vorname,
EXTRACT(MONTH FROM Geburtsdatum) AS Monat,
Geburtsdatum
FROM Mitarbeiter
ORDER BY Monat;
AusgabeNAME VORNAME MONAT GEBURTSDATUM
-------------- --------- ----- ------------
Aagenau Karolin 1 02.01.1950
Langer Norbert 1 13.01.1968
Wagner Gaby 1 18.01.1970
Müller Kurt 1 05.01.1977
Kolic Ivana 2 14.02.1971
Schneider Daniela 2 16.02.1980 // usw.
Unterabfragen
BearbeitenDiese werden ausführlich im Kapitel Unterabfragen besprochen. Hier soll deshalb ein Beispiel genügen, wie es für eine berechnete Spalte geeignet ist.
Gesucht sind alle Abteilungen zusammen mit der Anzahl ihrer Mitarbeiter:
SELECT ab.ID,
ab.Kuerzel,
ab.Ort,
( SELECT COUNT(*)
FROM Mitarbeiter mi
WHERE mi.Abteilung_ID = ab.ID
) AS Mitarbeiterzahl
FROM Abteilung ab;
Von innen nach außen heißt das: In den Klammern gehört zu jeder ab.ID die Anzahl der Einträge in Mitarbeiter mit dieser Abteilung; dies wird mit den anderen Spalten von Abteilung zusammengefasst. Weil es ID in beiden Tabellen gibt, muss die gewünschte Tabelle ausdrücklich erwähnt werden.
Zusammenfassung
BearbeitenIn diesem Kapitel bekamen wir verschiedene Erläuterungen dafür, wie aus Berechnungen, Verkettung von Zeichenketten oder als Ergebnis von Skalar- oder Spaltenfunktionen neue Spalten für die Ergebnistabelle entstehen.
Übungen
Bearbeiten
| Übung 1 | Zusatzspalten durch Berechnung | Zur Lösung |
Zur Tabelle Versicherungsvertrag sollen Versicherungsnummer, Basisprämie und Prämiensatz angegeben sowie die aktuelle Prämie berechnet werden.
| Übung 2 | Zusatzspalten durch Berechnung | Zur Lösung |
Geben Sie (unter Verwendung der Lösung von Übung 1) die Gesamtzahl der Versicherungsverträge sowie den Gesamtbetrag aller aktuellen Prämien an.
| Übung 3 | Zusatzspalten durch String-Verknüpfung | Zur Lösung |
Erstellen Sie zur Tabelle Versicherungsnehmer per Abfrage die Druckanschrift:
- Zeile 1 mit Anrede (22 Zeichen, basierend auf der Spalte Geschlecht mit den Inhalten 'W' bzw. 'M') und der ID am rechten Rand (8 Zeichen rechtsbündig)
- Zeile 2 mit Vorname und Name
- Zeile 3 mit Straße und Hausnummer
- Zeile 4 mit PLZ und Ort
Benutzen Sie CASE, CAST, RPAD und CONCAT; auf TRIM können Sie verzichten.
| Übung 4 | Neue Spalten durch Spaltenfunktion | Zur Lösung |
Bestimmen Sie, wie viele Fahrzeuge in Bochum ('BO') und wie viele in Gelsenkirchen ('GE') angemeldet sind.
Gruppierungen werden erst im nächsten Kapitel behandelt; verwenden Sie stattdessen UNION.
| Lösung zu Übung 1 | Zusatzspalten durch Berechnung | Zur Übung |
select Vertragsnummer,
Basispraemie,
Praemiensatz,
Basispraemie * Praemiensatz / 100 as Aktuell
from Versicherungsvertrag;
| Lösung zu Übung 2 | Zusatzspalten durch Berechnung | Zur Übung |
select COUNT(*) as Gesamtzahl,
SUM(Basispraemie * Praemiensatz / 100) as Praemiensumme
from Versicherungsvertrag;
| Lösung zu Übung 3 | Zusatzspalten durch String-Verknüpfung | Zur Übung |
select CONCAT( CAST( ( CASE Geschlecht
WHEN 'M' THEN 'Herrn'
WHEN 'W' THEN 'Frau'
ELSE ''
END ) AS CHAR(22) ),
RPAD( CAST( ID as VARCHAR(8) ), 8 )
) as Zeile1,
CASE
WHEN Vorname is null THEN Name
ELSE CONCAT(Vorname, ' ', Name)
END as Zeile2,
CONCAT(Strasse, ' ', Hausnummer) as Zeile3,
CONCAT(PLZ, ' ', Ort) as Zeile4
from Versicherungsnehmer vn;
| Lösung zu Übung 4 | Neue Spalten durch Spaltenfunktion | Zur Übung |
select COUNT(*) as Anzahl, 'BO' as Kreis
from Fahrzeug
where Kennzeichen STARTING WITH 'BO-'
union
select COUNT(*), 'GE'
from Fahrzeug
where Kennzeichen STARTING WITH 'GE-';
Für STARTING WITH gibt es Alternativen; wir haben verschiedentlich SUBSTRING mit POSITION o. a. verwendet.