vorheriges KapitelInhaltsverzeichnisIndexInfoseitenächstes Kapitel



5


Klauseln in SQL

Ziele

Die WHERE-Klausel

Die Klausel STARTING WITH

Ordnung ins Chaos bringen: Die Klausel ORDER BY

Die Klausel GROUP BY

Die Klausel HAVING

Klauseln kombinieren

Zusammenfassung

Fragen und Antworten

Workshop



Ziele

Das Thema des heutigen Tages ist den Klauseln in einer SELECT-Anweisung gewidmet. Am Ende dieser Lektion sind Sie mit den folgenden Klauseln vertraut:


Die allgemeine Syntax der SELECT-Anweisung soll Ihnen zunächst die Einordnung dieser Klauseln erleichtern:


Die Erfahrung mit SQL zeigt, daß der ANSI-Standard eigentlich mehr ein ANSI-»Vorschlag« ist. Die obige Syntax funktioniert im allgemeinen bei jeder SQL-Engine, man findet aber auch leichte Abweichungen.

Bis jetzt sind Ihnen komplizierte Syntaxdiagramme erspart geblieben. Insbesondere der Einsteiger betrachtet ein Syntaxdiagramm eher wie ein Puzzle. Die wesentlichen Elemente gehen schnell in der vielschichtigen Darstellung unter. Aus diesem Grund führen wir neue Konstruktionen anhand einfacher Beispiele ein. Allerdings haben wir momentan einen Punkt erreicht, an dem ein Syntaxdiagramm die bekannten Konzepte mit dem neuen Material verbinden kann.

Kümmern Sie sich nicht um die genaue Syntax. Diese kann ohnehin von Implementierung zu Implementierung verschieden sein. Konzentrieren Sie sich statt dessen auf die Beziehungen. Am Beginn dieser Anweisung findet sich das bekannte SELECT, das Sie in den letzten Tagen unzählige Male eingetippt haben. Nach SELECT steht ein FROM, das in allen SELECT-Anweisungen vorkommen sollte. (Einen neuen Einsatzfall für FROM lernen Sie morgen kennen.) Danach folgen die Klauseln WHERE, GROUP BY, HAVING und ORDER BY. (Die anderen Klauseln im Diagramm - UNION, UNION ALL, INTERSECT und MINUS - wurden in Lektion 3 behandelt.) Jeder Klausel fällt eine wichtige Rolle bei der Auswahl und Manipulation von Daten zu.


Die heutigen Beispiele arbeiten mit zwei verschiedenen SQL-Implementierungen. Die Version mit Personal Oracle8 ist an der Eingabeaufforderung SQL> und den Zeilennummern zu erkennen. Diese Elemente sind bei der zweiten Implementierung - ISQL von Borland - nicht vorhanden. Außerdem können die Ausgaben je nach Implementierung leicht voneinander abweichen.


Die WHERE-Klausel

Mit SELECT und FROM allein kann man lediglich alle Zeilen einer Tabelle zurückgeben. Wendet man beispielsweise diese beiden Schlüsselwörter auf die Tabelle SCHECKS an, erscheinen alle sieben Zeilen:


SQL> SELECT *
2 FROM SCHECKS;


SCHECK# ZAHL_EMPF BETRAG BEMERKUNGEN
--------- -------------------- --------- --------------------
1 Ma Bell 150 Schmuck
2 Reading R. R. 245,34 Zug nach Chicago
3 Ma Bell 200,32 Mobiltelefon
4 Energieversorgung 98 Gas
5 Joes Stale $ Dent 150 Lebensmittel
16 Bar 25 Nachtclub
17 Joans Gas 25,1 Gas

7 Zeilen ausgewählt.


Mit einem WHERE im Vokabular läßt sich die Auswahl schärfer formulieren. Beispielsweise können Sie mit der nachstehenden Anweisung alle Schecks heraussuchen, die Sie mit einem Betrag von mehr als 100 Dollar ausgestellt haben:


SQL> SELECT *
2 FROM SCHECKS
3 WHERE BETRAG > 100;


Die WHERE-Klausel liefert die vier Einträge der Tabelle, die der geforderten Bedingung genügen:


SCHECK# ZAHL_EMPF BETRAG BEMERKUNGEN
--------- -------------------- --------- --------------------
1 Ma Bell 150 Schmuck
2 Reading R. R. 245,34 Zug nach Chicago
3 Ma Bell 200,32 Mobiltelefon
5 Joes Stale $ Dent 150 Lebensmittel

4 Zeilen ausgewählt.


Mit WHERE kann man auch andere Rätsel lösen. Gegeben sei die folgende Tabelle mit Namen und Plätzen. Man kann nun die Frage stellen: Wo ist Waldo?


SQL> SELECT *
2 FROM PUZZLE;
NAME ORT
--------------- ---------------
TYLER GARTEN
MAJOR KÜCHE
SPEEDY WOHNZIMMER
WALDO GARAGE
LADDIE ABSTELLKAMMER
ARNOLD FERNSEHZIMMER

6 Zeilen ausgewählt.


SQL> SELECT ORT AS "WO IST WALDO?"
2 FROM PUZZLE
3 WHERE NAME = 'WALDO';

WO IST WALDO?
---------------
GARAGE

1 Zeile wurde ausgewählt.


Keine Angst, es kommen noch schwierigere Abfragen. Dennoch zeigt diese Abfrage, daß die in der Bedingung der WHERE-Klausel verwendete Spalte nicht in der SELECT-Klausel erscheinen muß. In diesem Beispiel haben Sie die Spalte ORT ausgewählt, aber die Spalte NAME in der WHERE-Klausel angegeben. Das ist absolut korrekt. Beachten Sie auch das AS auf der ersten Zeile. Der optionale Zuweisungsoperator AS weist der Spalte ORT den Alias "WO IST WALDO?" zu. In zukünftigen Anweisungen finden Sie diesen Operator nicht mehr, da er nur zusätzliche Tipparbeit verursacht. In den meisten SQL-Implementierungen kann man einfach schreiben:


SQL> SELECT ORT "WO IST WALDO?"
2 FROM PUZZLE
3 WHERE NAME = 'WALDO';


Das Ergebnis unterscheidet sich nicht von der vorherigen Abfrage mit AS:


WO IST WALDO?
---------------
GARAGE

1 Zeile wurde ausgewählt.



Die Klausel STARTING WITH

Die Klausel STARTING WITH ergänzt eine WHERE-Klausel und arbeitet genau wie LIKE(<Ausdruck>%). Vergleichen Sie die Ergebnisse der Abfrage


SELECT ZAHL_EMPF, BETRAG, BEMERKUNGEN
FROM SCHECKS
WHERE ZAHL_EMPF LIKE('Ba%');


ZAHL_EMPF BETRAG BEMERKUNGEN
==================== =========== ====================

Bar 25.00 Nachtclub
Bar 60.00 Fahrt nach Boston
Bar 34.00 Fahrt nach Dayton


mit den Ergebnissen dieser Abfrage:


SELECT ZAHL_EMPF, BETRAG, BEMERKUNGEN
FROM SCHECKS
WHERE ZAHL_EMPF STARTING WITH('Ba');

ZAHL_EMPF BETRAG BEMERKUNGEN
==================== =========== ====================

Bar 25.00 Nachtclub
Bar 60.00 Fahrt nach Boston
Bar 34.00 Fahrt nach Dayton


Die Ergebnisse sind identisch. Man kann die beiden Versionen sogar zusammen verwenden, wie es das folgende Beispiel zeigt:


SELECT ZAHL_EMPF, BETRAG, BEMERKUNGEN
FROM SCHECKS
WHERE ZAHL_EMPF STARTING WITH('Ba')
OR
BEMERKUNGEN LIKE 'G%';

ZAHL_EMPF BETRAG BEMERKUNGEN
==================== =========== ====================

Energieversorgung 98.00 Gas
Bar 25.00 Nachtclub
Joans Gas 25.10 Gas
Bar 60.00 Fahrt nach Boston
Bar 34.00 Fahrt nach Dayton


Die Klausel STARTING WITH ist zwar in vielen SQL-Implementierungen zu finden, dennoch sollten Sie erst in der Dokumentation nachsehen, ob diese Klausel tatsächlich zur Verfügung steht.


Ordnung ins Chaos bringen: Die Klausel ORDER BY

Manchmal sollen die Ergebnisse einer Abfrage in einer bestimmten Reihenfolge erscheinen. Wie Sie wissen, erzeugt die Anweisung SELECT FROM lediglich eine Liste. Solange Sie keinen Primärschlüssel definiert haben (siehe dazu Tag 10), erhalten Sie die Ergebnisse der Abfrage in der Reihenfolge, in der die Daten eingegeben wurden. Sehen Sie sich die erweiterte Tabelle SCHECKS an:


SQL> SELECT * FROM SCHECKS;


SCHECK# ZAHL_EMPF BETRAG BEMERKUNGEN
---------- -------------------- ---------- --------------------
1 Ma Bell 150 Schmuck
2 Reading R. R. 245,34 Zug nach Chicago
3 Ma Bell 200,32 Mobiltelefon
4 Energieversorgung 98 Gas
5 Joes Stale $ Dent 150 Lebensmittel
16 Bar 25 Nachtclub
17 Joans Gas 25,1 Gas
9 Abes Cleaners 24,35 Xtra stark
20 Abes Cleaners 10,5 All Dry Clean
8 Bar 60 Fahrt nach Boston
21 Bar 34 Fahrt nach Dayton

11 Zeilen ausgewählt.


Das Ergebnis zeigt die Datensätze in genau der Reihenfolge, in der sie eingegeben wurden. Nachdem Sie sich mit der Manipulation von Daten in Lektion 8 beschäftigt haben und wissen, wie man die INSERT-Anweisung beim Aufbau von Tabellen einsetzt, können Sie sich von der vorgegebenen Einordnung der Daten selbst überzeugen.

Mit der Klausel ORDER BY lassen sich nun die Ergebnisse in eine bestimmte Reihenfolge bringen. Die folgende Anweisung ordnet zum Beispiel die obige Liste nach Schecknummern:


SQL> SELECT *
2 FROM SCHECKS
3 ORDER BY SCHECK#;

SCHECK# ZAHL_EMPF BETRAG BEMERKUNGEN
---------- -------------------- ---------- --------------------
1 Ma Bell 150 Schmuck
2 Reading R. R. 245,34 Zug nach Chicago
3 Ma Bell 200,32 Mobiltelefon
4 Energieversorgung 98 Gas
5 Joes Stale $ Dent 150 Lebensmittel
8 Bar 60 Fahrt nach Boston
9 Abes Cleaners 24,35 Xtra stark
16 Bar 25 Nachtclub
17 Joans Gas 25,1 Gas
20 Abes Cleaners 10,5 All Dry Clean
21 Bar 34 Fahrt nach Dayton

11 Zeilen ausgewählt.


Die Daten sind nun in der gewünschten Weise geordnet und erscheinen nicht mehr in der Eingabereihenfolge. Der nachstehende Test zeigt, daß das Schlüsselwort BY in der Klausel ORDER BY nicht optional ist:


SQL> SELECT * FROM SCHECKS ORDER SCHECK#;
SELECT * FROM SCHECKS ORDER SCHECK#
*
FEHLER in Zeile 1:
ORA-00924: Schlüsselwort BY fehlt


Wie läßt sich nun die Liste in umgekehrter Reihenfolge anzeigen - mit der größten Zahl an erster Stelle bzw. in alphabetisch umgekehrter Ordnung? Die Ergebnisliste der folgenden Abfrage ist so geordnet, daß die Zahlungsempfänger in umgekehrter alphabetischer Reihenfolge erscheinen:


SQL> SELECT *
2 FROM SCHECKS
3 ORDER BY ZAHL_EMPF DESC;

SCHECK# ZAHL_EMPF BETRAG BEMERKUNGEN
---------- -------------------- ---------- --------------------
2 Reading R. R. 245,34 Zug nach Chicago
1 Ma Bell 150 Schmuck
3 Ma Bell 200,32 Mobiltelefon
5 Joes Stale $ Dent 150 Lebensmittel
17 Joans Gas 25,1 Gas
4 Energieversorgung 98 Gas
16 Bar 25 Nachtclub
8 Bar 60 Fahrt nach Boston
21 Bar 34 Fahrt nach Dayton
9 Abes Cleaners 24,35 Xtra stark
20 Abes Cleaners 10,5 All Dry Clean

11 Zeilen ausgewählt.


Mit dem Schlüsselwort DESC (von englisch descending - absteigend) am Ende der Klausel ORDER BY läßt sich die absteigende Reihenfolge der Datensätze in der Ausgabe anstelle der (aufsteigenden) Standardreihenfolge festlegen. Das selten verwendete Schlüsselwort ASC (von englisch ascending - aufsteigend) ist in der folgenden Anweisung zu sehen:

SQL> SELECT ZAHL_EMPF, BETRAG
2 FROM SCHECKS
3 ORDER BY SCHECK# ASC;

ZAHL_EMPF BETRAG
-------------------- ----------
Ma Bell 150
Reading R. R. 245,34
Ma Bell 200,32
Energieversorgung 98
Joes Stale $ Dent 150
Bar 60
Abes Cleaners 24,35
Bar 25
Joans Gas 25,1
Abes Cleaners 10,5
Bar 34

11 Zeilen ausgewählt.


Die Reihenfolge der Datensätze in dieser Liste entspricht genau der weiter vorn - ohne ASC - erzeugten Liste, weil die aufsteigende Reihenfolge (ASC) als Standard voreingestellt ist. Die Abfrage zeigt weiterhin, daß der in der Klausel ORDER BY verwendete Ausdruck nicht in der SELECT-Anweisung erscheinen muß. Obwohl nur ZAHL_EMPF und BETRAG ausgewählt wurden, kann man die Liste trotzdem nach SCHECK# ordnen.

Die Klausel ORDER BY läßt sich auch auf mehrere Felder beziehen. Die folgende Abfrage nimmt zum Beispiel eine Sortierung nach ZAHL_EMPF und BEMERKUNGEN vor:


SQL> SELECT *
2 FROM SCHECKS
3 ORDER BY ZAHL_EMPF, BEMERKUNGEN;

SCHECK# ZAHL_EMPF BETRAG BEMERKUNGEN
---------- -------------------- ---------- --------------------
20 Abes Cleaners 10,5 All Dry Clean
9 Abes Cleaners 24,35 Xtra stark
8 Bar 60 Fahrt nach Boston
21 Bar 34 Fahrt nach Dayton
16 Bar 25 Nachtclub
4 Energieversorgung 98 Gas
17 Joans Gas 25,1 Gas
5 Joes Stale $ Dent 150 Lebensmittel
3 Ma Bell 200,32 Mobiltelefon
1 Ma Bell 150 Schmuck
2 Reading R. R. 245,34 Zug nach Chicago

11 Zeilen ausgewählt.


Beachten Sie die Reihenfolge der Bar-Einträge in der Spalte ZAHL_EMPF. In der weiter vorn gezeigten Abfrage, bei der lediglich ZAHL_EMPF in der Klausel ORDER BY aufgeführt ist, lautet die Reihenfolge der betreffenden Schecknummern 16, 8, 21. Die letzte Abfrage mit Hinzunahme des Feldes BEMERKUNGEN in die Klausel ORDER BY hat eine zusätzliche Sortierung entsprechend der Spalte BEMERKUNGEN gebracht. Hat die Stellung der Spaltennamen in der Klausel ORDER BY einen Einfluß auf das Ergebnis? Probieren Sie letzte Abfrage erneut aus, und vertauschen Sie jetzt ZAHL_EMPF mit BEMERKUNGEN:

SQL> SELECT *
2 FROM SCHECKS
3 ORDER BY BEMERKUNGEN, ZAHL_EMPF;

SCHECK# ZAHL_EMPF BETRAG BEMERKUNGEN
---------- -------------------- ---------- --------------------
20 Abes Cleaners 10,5 All Dry Clean
8 Bar 60 Fahrt nach Boston
21 Bar 34 Fahrt nach Dayton
4 Energieversorgung 98 Gas
17 Joans Gas 25,1 Gas
5 Joes Stale $ Dent 150 Lebensmittel
3 Ma Bell 200,32 Mobiltelefon
16 Bar 25 Nachtclub
1 Ma Bell 150 Schmuck
9 Abes Cleaners 24,35 Xtra stark
2 Reading R. R. 245,34 Zug nach Chicago

11 Zeilen ausgewählt.


Wie nicht anders zu erwarten, erhält man völlig andere Ergebnisse.

Es ist auch möglich, eine Spalte in alphabetischer Ordnung und eine zweite Spalte in umgekehrt alphabetischer Ordnung aufzulisten:


SQL> SELECT *
2 FROM SCHECKS
3 ORDER BY ZAHL_EMPF ASC, BEMERKUNGEN DESC;

SCHECK# ZAHL_EMPF BETRAG BEMERKUNGEN
---------- -------------------- ---------- --------------------
9 Abes Cleaners 24,35 Xtra stark
20 Abes Cleaners 10,5 All Dry Clean
16 Bar 25 Nachtclub
21 Bar 34 Fahrt nach Dayton
8 Bar 60 Fahrt nach Boston
4 Energieversorgung 98 Gas
17 Joans Gas 25,1 Gas
5 Joes Stale $ Dent 150 Lebensmittel
1 Ma Bell 150 Schmuck
3 Ma Bell 200,32 Mobiltelefon
2 Reading R. R. 245,34 Zug nach Chicago

11 Zeilen ausgewählt.


In diesem Beispiel ist ZAHL_EMPF in alphabetisch aufsteigender und BEMERKUNGEN in absteigender Reihenfolge sortiert. Beachten Sie, wie die Bemerkungen in den drei Schecks mit ZAHL_EMPF gleich Bar sortiert sind.

Ist Ihnen die Nummer der Spalte bekannt, nach der Sie die Ergebnisse ordnen möchten, können Sie auch ORDER BY Nummer schreiben und müssen nicht den vollständigen Spaltennamen angeben. Die folgende Anweisung zeigt dazu ein Beispiel.

SQL> SELECT *
2 FROM SCHECKS
3 ORDER BY 1;

SCHECK# ZAHL_EMPF BETRAG BEMERKUNGEN
---------- -------------------- ---------- --------------------
1 Ma Bell 150 Schmuck
2 Reading R. R. 245,34 Zug nach Chicago
3 Ma Bell 200,32 Mobiltelefon
4 Energieversorgung 98 Gas
5 Joes Stale $ Dent 150 Lebensmittel
8 Bar 60 Fahrt nach Boston
9 Abes Cleaners 24,35 Xtra stark
16 Bar 25 Nachtclub
17 Joans Gas 25,1 Gas
20 Abes Cleaners 10,5 All Dry Clean
21 Bar 34 Fahrt nach Dayton

11 Zeilen ausgewählt.


Dieses Ergebnis ist identisch mit dem Ergebnis, das Sie weiter oben mit der SELECT-Anweisung

SELECT * FROM SCHECKS ORDER BY SCHECK#


erzeugt haben.



Die Klausel GROUP BY

In Lektion 3 haben Sie die Aggregatfunktionen (COUNT, SUM, AVG, MIN und MAX) kennengelernt. Zur Demonstration der Klausel GROUP BY kommt die leicht geänderte Tabelle SCHECKS zum Einsatz:


SELECT *
FROM SCHECKS;

SCHECKNR ZAHL_EMPF BETRAG BEMERKUNGEN
=========== ==================== =========== ====================

1 Ma Bell 150.00 Schmuck
2 Reading R. R. 245.34 Zug nach Chicago
3 Ma Bell 200.32 Mobiltelefon
4 Energieversorgung 98.00 Gas
5 Joes Stale $ Dent 150.00 Lebensmittel
16 Bar 25.00 Nachtclub
17 Joans Gas 25.10 Gas
9 Abes Cleaners 24.35 Xtra stark
20 Abes Cleaners 10.50 All Dry Clean
8 Bar 60.00 Fahrt nach Boston
21 Bar 34.00 Fahrt nach Dayton
30 Energieversorgung 87.50 Wasser
31 Energieversorgung 34.00 Abwasser
25 Joans Gas 15.75 Gas


Die Summe aller Ausgaben läßt sich mit der folgenden Anweisung ermitteln:


SELECT SUM(BETRAG)
FROM SCHECKS;

SUM
===========

1159.86


Diese Anweisung gibt die Summe der Spalte BETRAG zurück. Wie lassen sich nun die Teilbeträge herausfinden, die an die einzelnen Zahlungsempfänger gegangen sind? Für diesen Zweck bietet SQL die Klausel GROUP BY. Die folgende Abfrage ermittelt, wem wieviel bezahlt wurde:

SELECT ZAHL_EMPF, SUM(BETRAG)
FROM SCHECKS
GROUP BY ZAHL_EMPF;

ZAHL_EMPF SUM
==================== ===========

Abes Cleaners 34.85
Bar 119.00
Energieversorgung 219.50
Joans Gas 40.85
Joes Stale $ Dent 150.00
Ma Bell 350.32
Reading R. R. 245.34


In der SELECT-Klausel steht eine normale Spaltenauswahl ZAHL_EMPF gefolgt von der Aggregatfunktion SUM(BETRAG). Wenn man die Abfrage nur mit dem sich anschließenden FROM SCHECKS ausführt, erhält man die folgende Fehlermeldung:

SELECT ZAHL_EMPF, SUM(BETRAG)
FROM SCHECKS;

Dynamic SQL Error
-SQL error code = -104
-invalid column reference


SQL bemängelt die Kombination der normalen Spalte mit der Aggregatfunktion. Diese Bedingung erfordert eine GROUP-BY-Klausel. Die Klausel GROUP BY führt die in der SELECT-Anweisung angegebene Aggregatfunktion für jede Gruppierung der Spalte aus, die nach der Klausel GROUP BY steht. Die Tabelle SCHECKS gibt 14 Zeilen zurück, wenn man die Abfrage mit SELECT * FROM SCHECKS ausführt. Mit der Anweisung SELECT ZAHL_EMPF, SUM(BETRAG) FROM SCHECKS GROUP BY ZAHL_EMPF faßt man die 14 Zeilen zu sieben Gruppen zusammen und erhält die Summe für jede Gruppe zurück.

Wir wollen nun wissen, wieviel wir wem mit wie vielen Schecks bezahlt haben. Kann man mehrere Aggregatfunktionen einsetzen?


SELECT ZAHL_EMPF, SUM(BETRAG), COUNT(ZAHL_EMPF)
FROM SCHECKS
GROUP BY ZAHL_EMPF;

ZAHL_EMPF SUM COUNT
==================== =========== ===========

Abes Cleaners 34.85 2
Bar 119.00 3
Energieversorgung 219.50 3
Joans Gas 40.85 2
Joes Stale $ Dent 150.00 1
Ma Bell 350.32 2
Reading R. R. 245.34 1


Dieses SQL wird immer nützlicher! Im obigen Beispiel konnten wir Gruppenfunktionen auf eindeutigen Gruppen mit Hilfe der Klausel GROUP BY ausführen. Außerdem sind die Ergebnisse nach ZAHL_EMPF geordnet. GROUP BY erledigt also auch die Aufgaben einer ORDER-BY-Klausel. Was passiert nun, wenn man nach mehreren Spalten gruppiert? Probieren Sie die folgende Anweisung aus:

SELECT ZAHL_EMPF, SUM(BETRAG), COUNT(ZAHL_EMPF)
FROM SCHECKS
GROUP BY ZAHL_EMPF, BEMERKUNGEN;

ZAHL_EMPF SUM COUNT
==================== =========== ===========

Abes Cleaners 10.50 1
Abes Cleaners 24.35 1
Bar 60.00 1
Bar 34.00 1
Bar 25.00 1
Energieversorgung 34.00 1
Energieversorgung 98.00 1
Energieversorgung 87.50 1
Joans Gas 40.85 2
Joes Stale $ Dent 150.00 1
Ma Bell 200.32 1
Ma Bell 150.00 1
Reading R. R. 245.34 1


Die Ergebnismenge hat sich von 7 Gruppen aus 14 Zeilen auf 13 Gruppen erweitert. Was gibt es Besonderes in der einen Gruppe, zu der mehrere Schecks gehören? Sehen Sie sich die Einträge für Joans Gas an:

SELECT ZAHL_EMPF, BEMERKUNGEN
FROM SCHECKS
WHERE ZAHL_EMPF = 'Joans Gas';

ZAHL_EMPF BEMERKUNGEN
==================== ====================

Joans Gas Gas
Joans Gas Gas


Die Kombination von ZAHL_EMPF mit BEMERKUNGEN erzeugt identische Einheiten, die SQL mit der Klausel GROUP BY auf einer Zeile zusammenfaßt. Die anderen Zeilen liefern voneinander verschiedene Kombinationen aus ZAHL_EMPF und BEMERKUNGEN. Daher erhalten diese Einträge ihre eigenen Gruppen.

Das nächste Beispiel sucht den größten und kleinsten Betrag. Die Gruppierung erfolgt nach BEMERKUNGEN:


SELECT MIN(BETRAG), MAX(BETRAG)
FROM SCHECKS
GROUP BY BEMERKUNGEN;

MIN MAX
=========== ===========

34.00 34.00
10.50 10.50
60.00 60.00
34.00 34.00
15.75 98.00
150.00 150.00
200.32 200.32
25.00 25.00
150.00 150.00
87.50 87.50
24.35 24.35
245.34 245.34


Wenn man in der SELECT-Anweisung eine Spalte angibt, die verschiedene Werte innerhalb der durch GROUP BY gebildeten Gruppe aufweist, entsteht der folgende Fehler:


SELECT ZAHL_EMPF, MAX(BETRAG), MIN(BETRAG)
FROM SCHECKS
GROUP BY BEMERKUNGEN;

Dynamic SQL Error
-SQL error code = -104
-invalid column reference


Diese Abfrage versucht, die Schecks nach BEMERKUNGEN zu gruppieren. Der Fehler wird ausgelöst, wenn die Abfrage zwei Datensätze mit der gleichen Bemerkung aber verschiedenen Zahlungsempfängern findet - beispielsweise trifft das auf die Zeilen zu, in denen Gas als BEMERKUNG steht, die aber als ZAHL_EMPF die Werte Energieversorgung und Joans Gas enthalten.

Man sollte also keine SELECT-Anweisung auf Spalten verwenden, die mehrere Werte für die in der Klausel GROUP BY aufgeführte Spalte enthalten. Der umgekehrte Fall ist dagegen zulässig. Man kann die Klausel GROUP BY auf Spalten anwenden, die nicht in der SELECT-Anweisung angegeben sind. Dazu ein Beispiel:


SELECT ZAHL_EMPF, COUNT(BETRAG)
FROM SCHECKS
GROUP BY ZAHL_EMPF, BETRAG;

ZAHL_EMPF COUNT
==================== ===========

Abes Cleaners 1
Abes Cleaners 1
Bar 1
Bar 1
Bar 1
Energieversorgung 1
Energieversorgung 1
Energieversorgung 1
Joans Gas 1
Joans Gas 1
Joes Stale $ Dent 1
Ma Bell 1
Ma Bell 1
Reading R. R. 1


Diese im Prinzip nutzlose Abfrage zeigt, wie viele Schecks Sie mit identischen Beträgen an denselben Zahlungsempfänger ausgestellt haben. Eigentlich soll diese Abfrage nur demonstrieren, daß man die Spalte BETRAG in der Klausel GROUP BY verwenden kann, selbst wenn diese Spalte nicht in der SELECT-Klausel erwähnt ist. Verschieben Sie nun BETRAG aus der Klausel GROUP BY in die SELECT-Klausel:

SELECT ZAHL_EMPF, BETRAG, COUNT(BETRAG)
FROM SCHECKS
GROUP BY ZAHL_EMPF;

Dynamic SQL Error
-SQL error code = -104
-invalid column reference


SQL kann diese Abfrage nicht ausführen. Versetzen Sie sich einmal in die Lage von SQL. Nehmen wir an, Sie sollen die folgenden Zeilen gruppieren:

SELECT ZAHL_EMPF, BETRAG, BEMERKUNGEN
FROM SCHECKS
WHERE ZAHL_EMPF = 'Bar';

ZAHL_EMPF BETRAG BEMERKUNGEN
==================== =========== ====================

Bar 25.00 Nachtclub
Bar 60.00 Fahrt nach Boston
Bar 34.00 Fahrt nach Dayton


Wenn der Benutzer verlangt, daß Sie alle drei Spalten ausgeben und nur nach ZAHL_EMPF gruppieren sollen, wo bringen Sie dann die eindeutig unterscheidbaren Bemerkungen unter? Denken Sie daran, daß Ihnen bei Einsatz von GROUP BY nur eine Zeile pro Gruppe zur Verfügung steht. SQL kann nicht zwei Dinge auf einmal tun. Die Fehlermeldung Error #31: Can't do two things at once drückt diese Tatsache aus.



Die Klausel HAVING

Wie kann man die in der Klausel GROUP BY verwendeten Daten näher spezifizieren? Probieren Sie das Ganze an der Tabelle ORGDIAGRAMM aus:


SELECT * FROM ORGDIAGRAMM;


NAME TEAM GEHALT KRANKHEIT URLAUB
=============== =============== =========== =========== ===========

ADAMS FORSCHUNG 34000.00 34 12
WILKES MARKETING 31000.00 40 9
STOKES MARKETING 36000.00 20 19
MEZA KONSTRUKTION 40000.00 30 27
MERRICK FORSCHUNG 45000.00 20 17
RICHARDSON MARKETING 42000.00 25 18
FURY KONSTRUKTION 35000.00 22 14
PRECOURT PR 37500.00 24 24


Die folgende Anweisung gruppiert die Abteilungen und zeigt das durchschnittliche Gehalt jeder Abteilung an:


SELECT TEAM, AVG(GEHALT)
FROM ORGDIAGRAMM
GROUP BY TEAM;

TEAM AVG
=============== ===========

FORSCHUNG 39500.00
KONSTRUKTION 37500.00
MARKETING 36333.33
PR 37500.00


Die folgende Anweisung ist schärfer formuliert und soll nur diejenigen Abteilungen zurückgeben, in denen das durchschnittliche Gehalt unter 38000 liegt:


SELECT TEAM, AVG(GEHALT)
FROM ORGDIAGRAMM
WHERE AVG(GEHALT) < 38000
GROUP BY TEAM;

Dynamic SQL Error
-SQL error code = -104
-Invalid aggregate reference


Der Fehler tritt auf, weil die WHERE-Klausel nicht mit Aggregatfunktionen arbeitet. Um eine derartige Abfrage zu realisieren, braucht man ein neues Element: die Klausel HAVING. Das folgende Beispiel bringt die gewünschten Ergebnisse:

SELECT TEAM, AVG(GEHALT)
FROM ORGDIAGRAMM
GROUP BY TEAM
HAVING AVG(GEHALT) < 38000;

TEAM AVG
=============== ===========

KONSTRUKTION 37500.00
MARKETING 36333.33
PR 37500.00


Die Klausel HAVING ermöglicht den Einsatz von Aggregatfunktionen in einer Vergleichsanweisung und bietet damit den Aggregatfunktionen ein konstruktives Element, das der WHERE-Klausel für einzelne Zeilen entspricht. Funktioniert HAVING auch mit Nicht-Aggregatfunktionen? Probieren Sie die folgende Anweisung aus:

SELECT TEAM, AVG(GEHALT)
FROM ORGDIAGRAMM
GROUP BY TEAM
HAVING GEHALT < 38000;

TEAM AVG
=============== ===========

PR 37500.00


Warum unterscheidet sich dieses Ergebnis von der letzten Abfrage? Die Klausel HAVING AVG(GEHALT) < 38000 hat jede Gruppe ausgewertet und nur diejenigen mit einem durchschnittlichen Gehalt unter 38000 zurückgegeben - genau wie erwartet. Der Ausdruck HAVING SALARY < 38000 liefert dagegen ein anderes Ergebnis. Übernehmen Sie nun wieder die Rolle von SQL. Wenn Sie der Benutzer auffordert, Abteilungsgruppen mit einem Gehalt < 38000 auszuwerten und zurückzugeben, würden Sie jede Gruppe untersuchen und diejenigen zurückweisen, bei denen das einzelne Gehalt größer als 38000 ist. Außer in der PR-Abteilung findet man in jeder Abteilung mindestens ein Gehalt, das größer als 38000 ist:

SELECT NAME, TEAM, GEHALT
FROM ORGDIAGRAMM
ORDER BY TEAM;

NAME TEAM GEHALT
=============== =============== ===========

ADAMS FORSCHUNG 34000.00
MERRICK FORSCHUNG 45000.00
FURY KONSTRUKTION 35000.00
MEZA KONSTRUKTION 40000.00
WILKES MARKETING 31000.00
STOKES MARKETING 36000.00
RICHARDSON MARKETING 42000.00
PRECOURT PR 37500.00


Demzufolge würden Sie alle Gruppen mit Ausnahme von PR zurückweisen. Die von der Anweisung gestellte Aufgabe lautet in Wirklichkeit: Wähle alle Gruppen aus, in denen keine Einzelperson mehr als 38000 verdient. Hassen Sie es nicht manchmal, wenn der Computer genau das tut, was man ihm sagt?

Manche SQL-Implementierungen erzeugen einen Fehler, wenn man in einer HAVING-Klausel etwas anderes als eine Aggregatfunktionen verwendet. Daher sollten Sie Konstruktionen wie im vorherigen Beispiel nur dann einsetzen, wenn Sie sich anhand der jeweiligen Dokumentation von der Zulässigkeit überzeugt haben.

Lassen sich in einer HAVING-Klausel auch mehrere Bedingungen verwenden? Probieren Sie die folgende Anweisung aus:


SELECT TEAM, AVG(KRANKHEIT), AVG(URLAUB)
FROM ORGDIAGRAMM
GROUP BY TEAM
HAVING AVG(KRANKHEIT)>25 AND
AVG(URLAUB)<20;


Die Ergebnistabelle ist nach TEAM gruppiert und zeigt alle Teams mit einem durchschnittlichen Krankenstand von über 25 Tagen und einem durchschnittlichen Jahresurlaub von weniger als 20 Tagen an.

TEAM AVG AVG
=============== =========== ===========

FORSCHUNG 27 15
MARKETING 28 15


In der HAVING-Klausel kann man auch eine Aggregatfunktion verwenden, die nicht in der SELECT-Anweisung aufgeführt ist. Das folgende Beispiel zeigt eine derartige Konstruktion:


SELECT TEAM, AVG(KRANKHEIT), AVG(URLAUB)
FROM ORGDIAGRAMM
GROUP BY TEAM
HAVING COUNT(TEAM) > 1;

TEAM AVG AVG
=============== =========== ===========

FORSCHUNG 27 15
KONSTRUKTION 26 21
MARKETING 28 15


Diese Abfrage gibt die Anzahl der Teams mit mehr als einem Mitglied zurück. Die Aggregatfunktion COUNT(TEAM) erscheint zwar nicht in der SELECT-Anweisung, funktioniert aber trotzdem wie vorgesehen in der HAVING-Klausel.

Die anderen logischen Operatoren kann man ebenfalls in der HAVING-Klausel einsetzen. Sehen Sie sich dazu folgendes Beispiel an:


SELECT TEAM, MIN(GEHALT), MAX(GEHALT)
FROM ORGDIAGRAMM
GROUP BY TEAM
HAVING AVG(GEHALT) > 37000
OR
MIN(GEHALT) > 32000;

TEAM MIN MAX
=============== =========== ===========

FORSCHUNG 34000.00 45000.00
KONSTRUKTION 35000.00 40000.00
PR 37500.00 37500.00


Das nächste Beispiel zeigt, daß in einer HAVING-Klausel auch der Operator IN funktioniert:


SELECT TEAM, AVG(GEHALT)
FROM ORGDIAGRAMM
GROUP BY TEAM
HAVING TEAM IN ('PR','FORSCHUNG');

TEAM AVG
=============== ===========

FORSCHUNG 39500.00
PR 37500.00



Klauseln kombinieren

Nichts existiert für sich allein. Daher zeigt dieser Abschnitt anhand von Beispielen, wie die einzelnen Klauseln ineinandergreifen und sich im Verbund einsetzen lassen.



Beispiel 5.1

Dieses Beispiel sucht alle auf Bar und Gas ausgestellten Schecks aus der Tabelle SCHECKS heraus und ordnet sie nach BEMERKUNGEN.


SELECT ZAHL_EMPF, BEMERKUNGEN
FROM SCHECKS
WHERE ZAHL_EMPF = 'Bar'
OR BEMERKUNGEN LIKE 'Ga%'
ORDER BY BEMERKUNGEN;

ZAHL_EMPF BEMERKUNGEN
==================== ====================

Bar Fahrt nach Boston
Bar Fahrt nach Dayton
Energieversorgung Gas
Joans Gas Gas
Joans Gas Gas
Bar Nachtclub


Mit dem Operator LIKE werden alle BEMERKUNGEN ermittelt, die mit Ga beginnen. Der Operator OR bewirkt, daß die Abfrage alle Daten zurückgibt, wenn in der WHERE-Klausel mindestens eine der beiden Bedingungen erfüllt ist.

Wie könnte man die gleichen Informationen abrufen und sie nach ZAHL_EMPF gruppieren? Die Abfrage würde etwa wie folgt aussehen:


SELECT ZAHL_EMPF, BEMERKUNGEN
FROM SCHECKS
WHERE ZAHL_EMPF = 'Bar'
OR BEMERKUNGEN LIKE 'Ga%'
GROUP BY ZAHL_EMPF
ORDER BY BEMERKUNGEN;


Diese Abfrage funktioniert nicht, da die SQL-Engine nicht weiß, was mit den Bemerkungen geschehen soll. Denken Sie daran, daß alle in der SELECT-Klausel angegebenen Spalten auch in der Klausel GROUP BY erscheinen müssen - außer wenn Sie überhaupt keine Spalten in der SELECT-Klausel spezifizieren.


Beispiel 5.2

Ermitteln Sie aus der Tabelle ORGDIAGRAMM das Gehalt aller Mitarbeiter mit weniger als 25 Krankheitstagen. Ordnen Sie die Ergebnisse nach dem Namen.


SELECT NAME, GEHALT
FROM ORGDIAGRAMM
WHERE KRANKHEIT < 25
ORDER BY NAME;


NAME GEHALT
=============== ===========

FURY 35000.00
MERRICK 45000.00
PRECOURT 37500.00
STOKES 36000.00


In dieser unkomplizierten Abfrage können Sie Ihre neu erworbenen Kenntnisse zu WHERE und ORDER BY anwenden.


Beispiel 5.3

Zeigen Sie unter Verwendung der Tabelle ORGDIAGRAMM die Spalten TEAM, AVG(GEHALT), AVG(KRANKHEIT) und AVG(URLAUB) für jedes Team an:


SELECT TEAM,
AVG(GEHALT),
AVG(KRANKHEIT),
AVG(URLAUB)
FROM ORGDIAGRAMM
GROUP BY TEAM;


TEAM AVG AVG AVG
=============== =========== =========== ===========

FORSCHUNG 39500.00 27 15
KONSTRUKTION 37500.00 26 21
MARKETING 36333.33 28 15
PR 37500.00 24 24


Es folgt eine interessante Variante dieser Abfrage. Versuchen Sie, die Abläufe nachzuvollziehen:


SELECT TEAM,
AVG(GEHALT),
AVG(KRANKHEIT),
AVG(URLAUB)
FROM ORGDIAGRAMM
GROUP BY TEAM
ORDER BY NAME;

TEAM AVG AVG AVG
=============== =========== =========== ===========

FORSCHUNG 39500.00 27 15
KONSTRUKTION 37500.00 26 21
PR 37500.00 24 24
MARKETING 36333.33 28 15


Eine einfachere Abfrage mit der Klausel ORDER BY soll als Anhaltspunkt dienen:


SELECT NAME, TEAM
FROM ORGDIAGRAMM
ORDER BY NAME, TEAM;

NAME TEAM
=============== ===============

ADAMS FORSCHUNG
FURY KONSTRUKTION
MERRICK FORSCHUNG
MEZA KONSTRUKTION
PRECOURT PR
RICHARDSON MARKETING
STOKES MARKETING
WILKES MARKETING


Die SQL-Engine hat beim Sortieren der Abfrageergebnisse die Spalte NAME verwendet (wie Sie wissen, kann man ohne weiteres eine Spalte verwenden, die nicht in der SELECT-Anweisung angegeben ist), hat doppelte Einträge in der Spalte TEAM ignoriert und die Reihenfolge FORSCHUNG, KONSTRUKTION, PR und MARKETING ermittelt. Das Einbinden von TEAM in die Klausel ORDER BY ist überflüssig, da in der Spalte NAME eindeutige Werte enthalten sind. Das gleiche Ergebnis läßt sich mit der folgenden Anweisung erhalten:

SELECT NAME, TEAM
FROM ORGDIAGRAMM
ORDER BY NAME;

NAME TEAM
=============== ===============

ADAMS FORSCHUNG
FURY KONSTRUKTION
MERRICK FORSCHUNG
MEZA KONSTRUKTION
PRECOURT PR
RICHARDSON MARKETING
STOKES MARKETING
WILKES MARKETING


Auf der Suche nach Variationen sollten Sie nicht übersehen, daß man die Ausgaben auch in umgekehrter Reihenfolge sortieren kann:


SELECT NAME, TEAM
FROM ORGDIAGRAMM
ORDER BY NAME DESC;

NAME TEAM
=============== ===============

WILKES MARKETING
STOKES MARKETING
RICHARDSON MARKETING
PRECOURT PR
MEZA KONSTRUKTION
MERRICK FORSCHUNG
FURY KONSTRUKTION
ADAMS FORSCHUNG



Beispiel 5.4: Das große Finale

Läßt sich alles bisher Gelernte in einer Abfrage zusammenfassen? Prinzipiell ist das möglich, wobei aber eigenwillige Ergebnisse entstehen, da man hier in gewisser Hinsicht Äpfel und Birnen miteinander vermischt - das heißt, Aggregatfunktionen und Nicht-Aggregatfunktionen. Beispielsweise findet man WHERE und ORDER BY gewöhnlich in Abfragen, die sich wie im folgenden Beispiel auf einzelne Zeilen beziehen:


SELECT *
FROM ORGDIAGRAMM
ORDER BY NAME DESC;


NAME TEAM GEHALT KRANKHEIT URLAUB
=============== =============== =========== =========== ===========

WILKES MARKETING 31000.00 40 9
STOKES MARKETING 36000.00 20 19
RICHARDSON MARKETING 42000.00 25 18
PRECOURT PR 37500.00 24 24
MEZA KONSTRUKTION 40000.00 30 27
MERRICK FORSCHUNG 45000.00 20 17
FURY KONSTRUKTION 35000.00 22 14
ADAMS FORSCHUNG 34000.00 34 12


Die Klauseln GROUP BY und HAVING setzt man normalerweise in Verbindung mit Aggregatfunktionen ein:


SELECT ZAHL_EMPF,
SUM(BETRAG) GESAMT,
COUNT(ZAHL_EMPF) ANZAHL_AUSGESTELLT
FROM SCHECKS
GROUP BY ZAHL_EMPF
HAVING SUM(BETRAG) > 50;

ZAHL_EMPF GESAMT ANZAHL_AUSGESTELLT
==================== =========== ==================

Bar 119.00 3
Energieversorgung 219.50 3
Joes Stale $ Dent 150.00 1
Ma Bell 350.32 2
Reading R. R. 245.34 1


Wie Sie sich überzeugen konnten, führt die Kombination der beiden Gruppen unter Umständen zu unerwarteten Ergebnissen. Dazu gehören auch Anweisungen wie die folgende:


SELECT ZAHL_EMPF,
SUM(BETRAG) GESAMT,
COUNT(ZAHL_EMPF) ANZAHL_AUSGESTELLT
FROM SCHECKS
WHERE BETRAG >= 100
GROUP BY ZAHL_EMPF
HAVING SUM(BETRAG) > 50;


ZAHL_EMPF GESAMT ANZAHL_AUSGESTELLT
==================== =========== ==================

Joes Stale $ Dent 150.00 1
Ma Bell 350.32 2
Reading R. R. 245.34 1


Vergleichen Sie diese beiden Ergebnismengen, und untersuchen Sie die Ausgangsdaten:


SELECT ZAHL_EMPF, BETRAG
FROM SCHECKS
ORDER BY ZAHL_EMPF;

ZAHL_EMPF BETRAG
==================== ===========

Abes Cleaners 10.50
Abes Cleaners 24.35
Bar 25.00
Bar 34.00
Bar 60.00
Energieversorgung 34.00
Energieversorgung 87.50
Energieversorgung 98.00
Joans Gas 15.75
Joans Gas 25.10
Joes Stale $ Dent 150.00
Ma Bell 150.00
Ma Bell 200.32
Reading R. R. 245.34


Die WHERE-Klausel filtert alle Schecks mit einem Betrag kleiner als 100 Dollar heraus, bevor die Klausel GROUP BY auf der Abfrage ausgeführt wird. Wir versuchen jetzt nicht, Ihnen die Mixtur dieser Gruppen auszureden - irgendwann ist es gerade eine derartige Konstruktion, die Ihren Anforderungen entspricht. Allerdings sollte man nicht wahllos Aggregatfunktionen mit Nicht-Aggregatfunktionen mischen. Die obigen Beispiele arbeiten mit Tabellen, die gerade mal eine Handvoll Zeilen enthalten. (Sonst hätten Sie das Buch nicht mehr unter den Arm klemmen können!) In der Praxis arbeitet man mit Tausenden oder Millionen von Zeilen. Dann sind raffinierte Änderungen durch vermischte Klauseln kaum noch auszumachen.


Zusammenfassung

Heute haben Sie alle Klauseln kennengelernt, mit denen Sie die Leistung der SELECT-Anweisung effektiv nutzen können. Formulieren Sie Ihre Anfragen sorgfältig, da der Computer genau das liefert, was man von ihm verlangt. Nunmehr verfügen Sie über umfassende Kenntnisse der SQL-Grundlagen. Mit einzelnen Tabellen dürften Sie keine Probleme mehr haben. In der morgigen Lektion haben Sie Gelegenheit, mit mehreren Tabellen zu arbeiten.



Fragen und Antworten

Frage:

Einige Funktionen wurden bereits am Beginn dieser Woche behandelt. Warum sind wir dann noch einmal darauf zurückgekommen?

Antwort:

In der Tat wurde WHERE bereits am Tag 3 besprochen. Um die Arbeitsweise der Operatoren zu erläutern, waren grundlegende Kenntnisse von WHERE erforderlich. Heute haben wir WHERE näher beleuchtet, da es sich um eine Klausel handelt und Klauseln im Mittelpunkt dieser Lektion standen.


Workshop


Kontrollfragen

1. Welche Klausel ist in ihrer Arbeitsweise mit dem Operator LIKE(<Ausdruck>%) vergleichbar?


2. Welche Aufgabe hat die Klausel GROUP BY, und welche andere Klausel verhält sich ähnlich?


3. Funktioniert die folgende SELECT-Anweisung?


SQL> SELECT NAME, AVG(GEHALT), ABTEILUNG
FROM ZAHL_TBL
WHERE ABTEILUNG = 'BUCHHALTUNG'
ORDER BY NAME
GROUP BY ABTEILUNG, GEHALT;


4. Muß man bei Einsatz der HAVING-Klausel immer auch eine GROUP-BY-Klausel vorsehen?


5. Kann man ORDER BY auf eine Spalte anwenden, die nicht in der SELECT-Anweisung aufgeführt ist?



Übungen

1. Ermitteln Sie aus der Tabelle ORGDIAGRAMM der obigen Beispiele, wie viele Mitarbeiter in jedem Team 30 oder mehr Krankheitstage aufweisen.


2. Schreiben Sie für die Tabelle SCHECKS eine SELECT-Anweisung, die folgendes Ergebnis liefert:


SCHECK# ZAHL_EMPF BETRAG
--------- -------------------- ---------
1 Ma Bell 150



Ein Imprint des Markt&Technik Buch- und Software-Verlag GmbH.
Elektronische Fassung des Titels: SQL in 21 Tagen, ISBN: 3-8272-2020-3


vorheriges KapitelTop Of PageInhaltsverzeichnisIndexInfoseitenächstes Kapitel