Einführung in SQL

Fachhochschule
Deggendorf
IT-Kompaktkurs
Einführung in SQL
EINSATZ VON ORACLE AM TERMINAL ........................................................................ 3
BESONDERE MERKMALE ........................................................................................................... 3
DATENBANK UND TABELLEN ..................................................................................................... 3
BEISPIEL EINER DATENBANK .................................................................................................... 3
SPALTEN UND ZEILEN ................................................................................................................ 4
FELDER UND WERTE ................................................................................................................. 4
AUSWAHL VON DATEN IN EINER TABELLE ............................................................... 4
BEFEHL SELECT ...................................................................................................................... 4
SELECT* .................................................................................................................................. 4
AUSWÄHLEN EINZELNER SPALTEN UND ZEILEN IN EINER TABELLE ............. 5
AUSWÄHLEN EINZELNER SPALTEN ........................................................................................... 5
FESTLEGEN DER SPALTENFOLGE .............................................................................................. 5
AUSWÄHLEN EINZELNER ZEILEN .............................................................................................. 5
MEHRFACHE SUCHBEDINGUNGEN ............................................................................................ 6
ALTERNATIVE SUCHBEDINGUNGEN .......................................................................................... 6
NEGATIVE SUCHBEDINGUNGEN ................................................................................................ 7
SUCHE NACH EINEM BEREICH ................................................................................................... 7
SUCHE NACH WERTEN IN EINER LISTE .................................................................................... 8
VERGLEICH ZU EINEM ZEICHENMUSTER .................................................................................. 8
REIHENFOLGE DER ZEILEN IN EINEM ABFRAGEERGEBNIS ............................... 9
REIHENFOLGE DER ZEILEN ....................................................................................................... 9
VERHINDERN DER AUSWAHL VON DOPPELZEILEN ............................................. 10
ABFRAGE MEHRERER TABELLEN ............................................................................... 11
JOIN-ABFRAGE ....................................................................................................................... 11
JOIN-BEDINGUNG ................................................................................................................... 12
FORMATIEREN EINER ABFRAGE ZU EINEM BERICHT......................................... 12
BEFEHL COLUMN ................................................................................................................. 13
BEFEHL BREAK ..................................................................................................................... 13
BEFEHL COMPUTE ............................................................................................................... 13
BEFEHL TTITLE UND BTITLE ............................................................................................. 13
FUNKTIONEN FÜR ARITHMETISCHE AUSDRÜCKE UND ZEICHENFOLGEN.. 13
ARITHMETISCHE AUSDRÜCKE ................................................................................................ 13
ZEICHENFOLGEFUNKTIONEN .................................................................................................. 14
FORMATIEREN UND BERECHNEN VON DATUMSANGABEN ............................... 15
FORMATIEREN DES DATUMS ................................................................................................... 15
DATUMSARITHMETIK .............................................................................................................. 16
FUNKTIONEN ZUR BERECHNUNG BEI ZEILENGRUPPEN .................................... 16
GRUPPENFUNKTIONEN ............................................................................................................ 16
KLAUSEL GROUP BY............................................................................................................. 17
PROZEDURAL GEGENÜBER NICHTPROZEDURAL ..................................................................... 18
KLAUSEL HAVING ................................................................................................................. 18
SUBQUERIES ........................................................................................................................ 19
Einführung in SQL
Seite 2 von 20
EINSATZ VON ORACLE AM TERMINAL
Besondere Merkmale
Nachstehend folgt eine weitere Erläuterung der relationalen Konzeption in Verbindung mit
einer Reihe von Beispielen, die die Leistungsfähigkeit der relationalen Datensprache SQL
veranschaulichen. In diesem Abschnitt lernen Sie, wie Sie SQL zur Lösung folgender
Aufgaben einsetzen können:



Einzelne Zeilen in einer Tabelle auswählen (SELECT)
Eine Tabelle erstellen (CREATE)
Zeilen in eine Tabelle einfügen (INSERT).
Zunächst etwas mehr über den Aufbau einer Datenbank und ein paar Definitionen.
Datenbank und Tabellen
Eine ORACLE-Datenbank ist aus Tabellen aufgebaut. Bei der Mehrzahl aller in dieser
Broschüre enthaltenen Beispielen wird DEPT als Mustertabelle verwendet, die Angaben über
die Abteilungen in einer Firma enthält. Als weiteres Beispiel wird EMP als Mustertabelle
herangezogen, die Angaben über die Beschäftigten in dieser Firma enthält.
Beispiel einer Datenbank
Tabelle DEPT
DEPTNO DNAME
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
Tabelle EMP
EMPNO ENAME
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
Einführung in SQL
LOC
NEW YORK
DALLAS
CHICAGO
BOSTON
JOB
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
MGR
7902
7698
7698
7839
7698
7839
7893
7566
7689
7788
7698
7566
7782
HIREDATE
17-DEC-80
20-FEB-81
22-FEB-81
02-APR-81
28-SEP-81
01-MAY-81
09-JUN-81
09-NOV-81
17-NOV-81
08-SEP-81
23-SEP-81
03-DEC-81
03-DEC-81
23-JAN-82
SAL COMM DEPTNO
800.00
20
1,600.00
300.00
30
1,250.00
500.00
30
2,975.00
20
1,250.00 1,400.00
30
2,850.00
30
2,450.00
10
3,000.00
20
5,000.00
10
1,500.00
0.00
30
1,100.00
20
950.00
30
3,000.00
20
1,300.00
10
Seite 3 von 20
Spalten und Zeilen
Jede Tabelle besteht aus (vertikalen) Spalten und (horizontalen) Zeilen bzw. Reihen. Die
Tabelle DEPT ist aus drei Spalten (mit der Bezeichnung DEPTNO für die
Abteilungsnummer, DNAME für die Abteilungsbezeichnung und LOC für den Standort der
Abteilung) und vier Zeilen aufgebaut (jeweils eine Zeile für die Abteilung Nr. 10, 20, 30 und
40).
Felder und Werte
Eine Zeile baut sich aus Feldern auf. Jedes Feld enthält am Schnittpunkt zwischen einer Zeile
und einer Spalte einen Datenwert. Bei dem obigen Beispiel der Tabelle DEPT ist der Wert 10
im Feld DEPTNO abgespeichert, der Wert ACCOUNTING (Rechnungswesen) als
Bezeichnung im Feld DNAME, das Feld LOC enthält den Wert NEW YORK für die
Standortangabe.
(Anmerkung: Soweit nichts anderes angegeben ist, wird hier und in den folgenden
Tabellenabbildungen ein SQL-Spaltenformat von 999,999.99 für die Spalte SAL, COMM und
BUDGET aus Gründen der leichteren Lesbarkeit angenommen).
AUSWAHL VON DATEN IN EINER TABELLE
Befehl SELECT
Der häufigste Vorgang in SQL ist das Auffinden von Daten in einer Datenbank. Diesen
Vorgang bezeichnet man als Abfrage. Zum Absetzen einer Abfrage arbeiten Sie mit dem
Befehl SELECT (wählen). Der Befehl SELECT besteht in seiner Grundform aus zwei Teilen,
die als Klauseln bezeichnet werden:
SELECT
FROM
einige Daten (Spaltenbezeichnung(en)) [auswählen]
[aus] einer oder mehreren Tabellen (Tabellenbezeichnung(en))
Die Klausel SELECT wird immer zuerst eingegeben, die Klausel FROM schließt sich
unmittelbar an.
Nun ein Blick auf die Daten in den Tabellen DEPT und EMP, wozu ein paar einfache SQLAbfragen abgesetzt werden. Zunächst listen wir alle Spalten und Zeilen in der Tabelle DEPT
auf. Folgender SQL-Befehl bringt uns dieses Ergebnis:
SELECT
FROM
DEPTNO, DNAME, LOC
DEPT;
SELECT*
Bei diesem Beispiel einer Abfrage haben wir in der Klausel SELECT die Bezeichnung jeder
Spalte in der Tabelle DEPT aufgelistet, also DEPNO, DNAME, LOC. Doch wenn Sie alle
Spalten in einer Tabelle ansteuern wollen, verwenden Sie normalerweise ein Sternchen
(SELECT*) als Kurzfassung anstelle der Aufzählung der Spaltenbezeichnung wie beim
vorhergehenden Beispiel.
SELECT
FROM
Einführung in SQL
*
EMP;
 Befehlsabschluß
Seite 4 von 20
Bei den Beispielen in dieser Broschüre haben wir die meisten unserer SQL-Befehle auf
mehrere Zeilen aufgeteilt und in Großbuchstaben ausgedruckt, damit sie leichter lesbar sind.
Sie können aber auch ganze SQL-Befehle in einer Zeile in Groß- oder Kleinbuchstaben
eingeben, wenn Ihnen dies lieber ist. Bei den Schreibweisen machen Sie das Ende Ihres SQLBefehls dadurch kenntlich, dass Sie ein Semikolon (;) al letztes Zeichen der letzen Zeile
eineben. ORACLE erkennt dieses Semikolon als Befehlsabschluß und führt den Befehl aus.
AUSWÄHLEN EINZELNER SPALTEN UND ZEILEN IN
EINER TABELLE
Auswählen einzelner Spalten
Bei den ersten Abfragebeispielen haben wir alle Spalten und alle Zeilen aus der Tabelle
ausgewählt. Wenn Sie aber nicht alle Spalten in der Tabelle sehen wollen, so geben Sie in der
Klausel SELECT nur die Bezeichnung der Spalten an, die erscheinen sollen.
SELECT
FROM
DNAME, DEPTNO
DEPT;
DEPTNO
-------------10
20
30
40
DNAME
-----------------ACCOUNTING
RESEARCH
SALES
OPERATIONS
Festlegen der Spaltenfolge
Wie Sie bereits gesehen haben, ist das Ergebnis jeder Abfrage selbst wieder eine Tabelle mit
Spalten und Zeilen. Die Reihenfolge, in der Sie Ihre Spalten in der Klausel SELECT angeben,
legt fest, in welcher Aufeinanderfolge die Spalten von links nach rechts in der Ergebnistabelle
erscheinen sollen. Geben Sie SELECT* ein, so enthält die Ergebnistabelle dann die Spalten in
der Abfolge von links nach rechts, die Sie im Befehl CREATE TABLE beim Aufbau dieser
Tabelle festgelegt haben.
Auswählen einzelner Zeilen
Aus dem letzten Beispiel konnten Sie entnehmen, wie Sie mit der Klausel SELECT einzelne
Spalten in Ihrer Tabelle auswählen können. Wie können Sie aber nun bestimmte Zeilen in
der Tabelle auffinden? Hierzu müssen Sie Ihren Befehl SELECT um eine WHERE-Klausel
(Suchbedingung) erweitern.
SELECT
FROM
WHERE
Einführung in SQL
*
EMP
DEPTNO = 30;
 WHERE-Klausel
Seite 5 von 20
EMPNO
7499
7521
7654
7698
7844
7900
ENAME
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES
JOB
SALESMAN
SALESMAN
SALESMAN
MANAGER
SALESMAN
CLERK
MGR
7698
7698
7698
7839
7689
7698
HIREDATE
20-FEB-81
22-FEB-81
28-SEP-81
01-MAY-81
08-SEP-81
03-DEC-81
SAL COMM DEPTNO
1,600.00
300.00
30
1,250.00
500.00
30
1,250.00 1,400.00
30
2,850.00
30
1,500.00
0.00
30
950.00
30
Mit der Klausel WHERE weisen Sie ORACLE an, die Daten in Ihrer Tabelle abzusuchen und
nur die Zeilen herauszugreifen, die Ihrer Suchbedingung entsprechen. Beim obigen Beispiel
gab ORACLE nur die Zeilen wieder, in denen die Kennzahl der Abteilung des betreffenden
Mitarbeiters gleich 30 war (WHERE DEPNO = 30).
Mehrfache Suchbedingungen
Gelegentlich müssen Sie mehr als eine Suchbedingung in einer WHERE-Klausel angeben.
Nehmen wir beispielsweise an, dass Sie eine Liste der Manager in Ihrer Firma brauchen, die
mehr als $ 2,800.00 verdienen.
SELECT
FROM
WHERE
AND
ENAME, JOB, SAL
EMP
JOB = 'MANAGER'
SAL > 2800;
ENAME
------------JONES
BLAKE
JOB
-----------------MANAGER
MANAGER
 AND-Verbindung
SAL
--------------2,975.00
2,850.00
Mehrere Suchbedingungen werden mit dem Wort AND (JOB = 'MANAGER' AND SAL >
2800) verbunden. AND bedeutet, dass Ihre Daten allen Suchbedingungen entsprechen
müssen, bevor ORACLE eine bestimmte Zeile auswählt. Sie können mit AND beliebig viele
Suchbedingungen in eine WHERE-Klausel miteinander verknüpfen.
Alternative Suchbedingungen
Sie können nicht nur Zeilen herausgreifen, die allen von mehreren Suchbedingungen
entsprechen, sondern Sie können auch Zeilen ansteuern, die eine von mehreren
Suchbedingungen erfüllen.
SELECT
FROM
WHERE
OR
Einführung in SQL
ENAME, JOB, SAL
EMP
JOB = 'MANAGER'
SAL > 2800;
 OR-Verbindung
Seite 6 von 20
ENAME
------------JONES
BLAKE
CLARK
SCOTT
KING
FORD
JOB
-----------------MANAGER
MANAGER
MANAGER
ANALYST
PRESIDENT
ANALYST
SAL
--------------2,975.00
2,850.00
2,450.00
3,000.00
5,000.00
3,000.00
Bei diesem Beispiel verknüpfen wir unsere Suchkriterien durch das Wort OR (JOB =
'MANAGER' OR SAL > 2800). OR bedeutet, dass bei der Erfüllung einer Ihrer
Suchbedingungen ORACLE die entsprechende Zeile herausgreift.
Negative Suchbedingungen
Sie können auch Zeilen herausgreifen, die bestimmte Suchkriterien nicht erfüllen. Nehmen
wir zum Beispiel alle Manager, die nicht in Abteilung 30 arbeiten.
SELECT
FROM
WHERE
AND
ENAME, JOB, DEPTNO
EMP
JOB = 'MANAGER'
DEPTNO != 30;
ENAME
-------------JONES
CLARK
JOB
-----------------MANAGER
MANAGER
 != bedeutet 'ungleich'
DEPTNO
-----------20
10
Sie können in einer einzigen Abfrage mit AND, OR und NOT soviele Suchkriterien
miteinander verbinden, wie sie zum Auffinden der von Ihnen gewünschten Zeilen benötigen.
Suche nach einem Bereich
Mit der Bedingungsangabe BETWEEN (zwischen) können Sie alle Zeilen herausgreifen, die
Werte innerhalb eines von Ihnen vorgesehenen Bereichs enthalten. Stellen wir also eine
Übersicht über alle Beschäftigten zusammen, deren Gehalt zwischen 1200 und 1400 leigt.
SELECT
FROM
WHERE
ENAME
----------------WARD
MARTIN
MILLER
Einführung in SQL
ENAME, SAL
EMP
SAL BETWEEN 1200 AND 1400;
 Bedingungsangabe BETWEEN
SAL
------------1,250.00
1,250.00
1,300.00
Seite 7 von 20
Suche nach Werten IN einer Liste
Mit der Bedingungsangabe IN können Sie einzelne Zeilen auswählen, die einen Wert
enthalten, der einem Wert in einer Werteliste entspricht, die Sie angeben. Stellen wir also eine
Übersicht über alle Abteilungen zusammen, die die Nummer 10 oder 30 tragen.
SELECT
FROM
WHERE
*
DEPT
DEPTNO IN (10,30);
DEPTNO
------------10
30
DNAME
-------------------ACCOUNTING
SALES
 Bedingungsangabe IN
LOC
-----------------NEW YORK
CHICAGO
Bitte beachten Sie, dass wir unsere Werteliste in Klammern (10,30) gesetzt haben. Diese
Abfrage hätten wir genauso gut mit OR (WHERE DEPTNO = 10 OR DEPTNO = 30)
formulieren können, um das gleiche Ergebnis zu erhalten.
Vergleich zu einem Zeichenmuster
Es lassen sich auch Zeilen herausgreifen, die einem Muster aus Zeichen oder Zahlen
entsprechen, das Sie vorgeben. Nachstehend nun eine Auflistung aller Angestellten, bei denen
R der dritte Buchstabe im Namen ist.
SELECT
FROM
WHERE
ENAME
EMP
ENAME LIKE '__R%';
 Bedingungsangabe LIKE
ENAME
-------------WARD
MARTIN
TURNER
FORD
Bei diesem Beispiel haben wir als SQL-Bedingungsangabe LIKE (wie) verwendet, damit
ORACLE alle die Zeilen in der Tabelle EMP herausgreift, in denen das Feld ENAME einen
Wert enthält, der dem vorgegebenen Muster ('__R%') entspricht, also gleich (LIKE) diesem
ist. Mit der Unterstreichung wird jeweils eine Zeichenstelle angegeben, und das
Prozentzeichen (%) steht für jede Folge von null oder mehr Zeichen.
Vor die Bedingungsangaben BETWEEN, IN und LIKE kann die Angabe NOT gesetzt und
mit AND und OR so angeschlossen werden, dass sie zusammen genau die WHERE-Klausel
bilden, die Sie zum Auswählen der gewünschten Zeilen brauchen.
Einführung in SQL
Seite 8 von 20
REIHENFOLGE DER ZEILEN IN EINEM
ABFRAGEERGEBNIS
Reihenfolge der Zeilen
Bei allen bisher vorgestellten Beispielen erschienen die Zeilen im Abfrageergebnis in einer
von ORACLE vorgegebenen Reihenfolge. Sie können jedoch auch die Reihenfolge vorgeben,
in der die angewählten Zeilen auf dem Bildschirm erscheinen. Hierzu fügen Sie an den Befehl
SELECT den Zusatz ORDER BY (geordnet nach) an. Angenommen, Sie brauchen eine
Aufstellung der Beschäftigten in Abteilung 30, geordnet nach Einkommenshöhe.
SELECT
SAL, JOB, ENAME
FROM
EMP
WHERE
DEPTNO = 30
ORDER BY SAL;
SAL
-------------------950.00
1,250.00
1,250.00
1,500.00
1,600.00
2,850.00
JOB
----------------CLERK
SALESMAN
SALESMAN
SALESMAN
SALESMAN
MANAGER
 Klausel ORDER BY
ENAME
---------------------JAMES
WARD
MARTIN
TURNER
ALLEN
BLAKE
Durch die Angabe der Klausel ORDER BY wurde ein Sortierlauf in den Zeilen in
aufsteigender Folge (beginnend mit dem kleinsten Einkommen) veranlaßt. Doch kann nicht
nur in aufsteigender Folge oder in nur einer einzigen Spalte geordnet werden. Wir können
vielmehr auch alle Beschäftigten in der alphabetischen Reihenfolge ihrer Tätigkeit und
innerhalb jeder Tätigkeit in absteigender Folge des Einkommens auflisten.
SELECT
JOB, SAL, ENAME
FROM
EMP
ORDER BY JOB, SAL DESC;
JOB
-------------------ANALYST
ANALYST
CLERK
CLERK
CLERK
CLERK
MANAGER
MANAGER
PRESIDENT
SALESMAN
SALESMAN
SALESMAN
SALESMAN
Einführung in SQL
SAL
-----------------3,000.00
3,000.00
1,300.00
1,100.00
950.00
800.00
2,850.00
2,450.00
5,000.00
1,600.00
1,500.00
1,250.00
1,250.00
 Absteigende Folge (DESC)
ENAME
-----------------SCOTT
FORD
MILLER
ADAMS
JAMES
SMITH
BLAKE
CLARK
KING
ALLEN
TURNER
WARD
MARTIN
Seite 9 von 20
Die Angaben in der Spalte JOB erscheinen in unserer Ergebnistabelle alphabetisch (wird für
die gewünschte Spalten nichts angegeben, so erfolgt die Reihenfolge aufsteigend), wobei die
Beschäftigten innerhalb jeder Tätigkeitsgruppe in absteigender Folge ihres Gehalts (SAL
DESC) aufgeführt sind.
VERHINDERN DER AUSWAHL VON DOPPELZEILEN
Angenommen, Sie wünschen eine Liste der verschiedenen Tätigkeitsangaben innerhalb der
Tabelle EMP.
SELECT
FROM
JOB
EMP;
JOB
-------------------CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
Da unsere Abfrage keine WHERE-Klausel enthielt, zeigt ORACLE in der Spalte JOB der
Tabelle EMP alle Werte. Daraus ersehen Sie, daß viele Werte in der Spalte doppelt
vorkommen (es üben also mehrere Angestellte die gleiche Tätigkeit aus). Diese
Doppeltangaben können Sie durch die Vorgabe DISTINCT (verschieden) in der SELECTKlausel vermeiden. Das Schlüsselwort DISTINCT, so wie wir es hier in der folgenden
Abfrage verwenden, bedeutet "zeige mir alle verschiedenen (DISTINCT) Arten der Tätigkeit
in der Tabelle EMP an.".
SELECT
FROM
DISTINCT JOB
EMP;
 Schlüsselwort DISTINCT
JOB
------------------CLERK
SALESMAN
MANAGER
ANALYST
PRESIDENT
Einführung in SQL
Seite 10 von 20
Somit sind nur fünf verschiedene (DISTINCT) Tätigkeitsprofile (JOBS) in der
Ergebnistabelle enthalten, auch wenn EMP vierzehn JOBS, nämlich je einen für alle vierzehn
Angestellten, enthält.
ABFRAGE MEHRERER TABELLEN
JOIN-Abfrage
Bisher haben wir bei allen Beispielen nur eine Tabelle abgefragt (EMP oder DEPT). Was aber
ist zu tun, wenn die gewünschten Informationen gar nicht in der selben Tabelle erfaßt sind?
Mit der ORACLE-Funktion Join (verknüpfen) können Sie Daten aus zwei oder mehr
Tabellen herausgreifen und die gefundenen Daten in einer einzigen Ergebnistabelle
zusammenfassen.
Angenommen, es interessiert Sie, wo der Angestellte mit dem Namen ALLEN arbeitet.
Schauen Sie sich beide Tabellen in unserer Datenbank an. Sie sehen, daß die Tabelle EMP
keine Spalte für den Beschäftigungsort (LOC) enthält, daß aber diese Spalte in Tabelle DEPT
vorgesehen ist. Beide Tabellen, EMP und DEPT, haben aber eine gemeinsame Spalte, in der
die Abteilungskennzahlen (DEPTNO) angegeben sind. Eben über diese Abteilungskennzahl
in beiden Tabellen können Sie eine Beziehung zwischen einzelnen Zeilen in der Tabelle EMP
und Zeilen in der Tabelle DEPT herstellen. Durch Abfrage der Tabelle EMP können Sie so
die zu ALLEN gehörende Abteilungskennzahl auffinden.
SELECT
FROM
WHERE
ENAME, DEPTNO
EMP
ENAME ='ALLEN'
ENAME
------------ALLEN
DEPTNO
-----------30
Und durch Abfrage der Tabelle DEPT läßt sich auch der Standort der Abteilung 30
feststellen.
SELECT
FROM
WHERE
LOC
DEPT
DEPTNO = 30
LOC
--------------CHICAGO
Durch Absetzten dieser beiden Anfragen erfahren wir, daß ALLEN in CHICAGO arbeitet.
Zu dem gleichen Ergebnis, das Sie mit den beiden vorhergehenden Abfragen erreicht haben,
können Sie aber auch durch eine einzige Abfrage mit JOIN gelangen. Bei einer Abfrage JOIN
geben Sie in der FROM-Klausel die Bezeichnung der Tabellen an, die Sie abfragen wollen,
und in der WHERE-Klausel die Bezeichnung der Spalte, die bei der Zusammenfügung
übereinstimmen müssen (also die Spalte, die die Tabellen gemeinsam haben).
Einführung in SQL
Seite 11 von 20
SELECT
FROM
WHERE
AND
ENAME, LOC
EMP, DEPT
ENAME = 'ALLEN'
EMP.DEPTNO = DEPT.DEPTNO;
NAME
-----------ALLEN
LOC
--------------CHICAGO
JOIN-Bedingung
Die Join-Bedingung (die Bedingung zur Verknüpfung) in der WHERE-Klausel legt die
Beziehung zwischen der Tabelle EMP und der Tabelle DEPT fest. Bei dem obigen Beispiel
sind die Zeilen miteinander zu verbinden, falls ein Eintrag in Spalte DEPTNO in einer Zeile
der Tabelle DEPT übereinstimmen (EMP.DEPTNO = DEPT.DEPTNO).
Neben der Join-Bedingung enthält die WHERE-Klausel die Suchbedingung ENAME =
'ALLEN'. Daran erkennt ORACLE, daß nur die Zeile für den Angestellten ALLEN
herauszugreifen ist. Und so schließt ORACLE einfach die Zeile ALLEN aus der Tabelle
EMP, die im Feld DEPTNO den Wert 30 enthält, an die Zeile aus der Tabelle EMP an, die
ebenfalls im Feld DEPTNO den Wert 30 aufweist. Die Spalten, die wir in der SELECTKlausel angegeben haben, bedeuten, daß nur das Feld ENAME aus der Tabelle EMP und nur
das Feld LOC aus der Tabelle DEPT in der zusammengesetzten Zeile als Ergebnis der
Abfrage herauszugreifen sind.
FORMATIEREN EINER ABFRAGE ZU EINEM BERICHT
Wir können das äußere Bild der Ausgabe auf unsere soeben abgesetzte Anfrage dadurch
verbessern, daß wir die Spaltenüberschriften und die Daten in den Spalten leichter lesbar
darstellen, oben und unten auf der Seite einen Titel einfügen und bei bestimmten Spalten
Zwischensummen bilden.
Benutzereingabe
COLUMN
DNAME
HEADING 'DEPARTMENT'
COLUMN
ENAME
HEADING 'EMPLYEE|NAME'
COLUMN
SAL
HEADING 'MONTHLY|SALARY' FORMAT $99,999.99
BREAK ON DNAME SKIP 1
COMPUTE SUM OF SAL ON DNAME
TTITLE
'ACME WIDGET||PERSONNEL REPORT'
BTITLE
'- Company Confidental -'
SELECT
DNAME, ENAME, JOB, SAL
FROM
EMP, DEPT
WHERE
EMP, DEPTNO = DEPT.DEPTNO
ORDER BY DNAME, SAL DESC;
Einführung in SQL
Seite 12 von 20
Befehl COLUMN
Mit dem Befehl COLUMN haben wir die Überschrift der Spalte SAL in MONTHLY
SALARY (Gehalt pro Monat) geändert, die zweizeilig ausgewiesen wird (HEADING
'MONTHLY|SALARY') (Überschrift 'Gehalt pro Monat') und die Daten mit dem
Dollarzeichen, dem Komma und dem Dezimalkomma bzw. -punkt (FORMAT $99,999.99)
formatiert.
Befehl BREAK
Mit dem Befehl BREAK wurde unser Bericht in Zeilengruppen unterteilt, nämlich jeweils
eine Gruppe für DNAME (BREAK ON DNAME), während 1 Zeile (SKIP 1) zwischen den
Gruppen freigelassen wurde.
Befehl COMPUTE
Durch den Befehl COMPUTE wurden Zwischensummen bei den Abteilungsgruppen gebildet,
die mit dem Befehl BREAK vorher festgelegt wurden (COMPUTE SUM OF SAL ON
DNAME - Berechne die Gehaltssumme bei DNAME).
Befehl TTITLE und BTITLE
Mit den Befehlen TTITLE und BTITLE (Titel oben, Titel unten) werden oben und unten auf
der Seite jeweils Titel eingefügt. Für TTITLE werden automatisch Datum und Seitenzahl
eingesetzt. Der senkrechte Strich (|) bei der Angabe TTITLE und bei einigen Anweisungen zu
den einzelnen Spalten (COLUMN) heißt für das System, daß eine neue Zeile beginnen soll.
FUNKTIONEN FÜR ARITHMETISCHE AUSDRÜCKE UND
ZEICHENFOLGEN
Während SQL von IBM nur mit den vier Grundrechenarten (+, -, *, /) arbeitet, so verfügt
ORACLE über einen ganzen Satz verschiedener Rechenfunktionen und Funktionen zur
Bearbeitung von Zeichenfolgen.
Arithmetische Ausdrücke
Zur Bildung eines arithmetischen Ausrucks verknüpfen Sie Spaltenbezeichnungen und
numerische Konstanten mit Hilfe eines arithmetischen Operationszeichens.
Nun listen wir die Namen, das Gehalt, die Provision und die Summe aus Gehalt plus
Provision für alle Verkäufer auf.
SELECT
FROM
WHERE
ENAME, SAL, COMM, SAL + COMM
EMP
JOB = 'SALESMAN';
ENAME
------------------ALLEN
WARD
MARTIN
TURNER
Einführung in SQL
SAL
--------------1,600.00
1,250.00
1,250.00
1,500.00
COM
----------------300.00
500.00
1,400.00
0.00
 Ausdruck SAL + COMM
SAL + COMM
-------------------1,900.00
1,750.00
2,650.00
1,500.00
Seite 13 von 20
Bitte beachten Sie, daß unser arithmetischer Ausdruck (SAL + COMM) als neue Spalte in
unserer Ergebnistabelle ausgeworfen wird. Auch wenn eine Spalte wie SAL + COMM keine
echte Spalte ist, also nicht in der Datenbank abgespeichert wird, sondern nur als Ergebnis
einer Abfrage erscheint, können Sie damit wie mit einer echten Spalte arbeiten.
Nachstehend finden Sie eine Übersicht über weitere Rechenoperationen, die sich mit
ORACLE ausführen lassen.
Zeichenfolgefunktionen
Mit den arithmetischen Funktionen können Sie Zahlenangaben bearbeiten; ähnlich einfach ist
es mit den Zeichenfolgefunktionen, wie sie beispielsweise im Folgenden gegeben sind,
Zeichendaten zu bearbeiten. Zum Beispiel können Sie mit der Funktion SOUNDEX Namen
feststellen, die ähnlich oder gleich klingen, aber verschiedene Schreibweise haben. Suchen
wir also alle Angestellten, deren Name so ähnlich wie SCHMIDT klingt.
SELECT
FROM
WHERE
ENAME
EMP
SOUNDEX(ENAME) = SOUNDEX('SCHMIDT')
ENAME
------------SMITH
ORACLE arbeitet mit den folgenden Rechenzeichen, arithmetischen Funktionen und
Zeichenfolgefunktionen:
ARITHMETISCHE OPERATOREN
+
Addieren
Subtrahieren
*
Multiplizieren
/
Dividieren
POWER
Potenzieren
ROUND
Runden
TRUNC
Abstreichen
ABS
Absolutwert
ZEICHENFOLGEFUNKTIONEN
|
Verkettung
DECODE
Übersetzen
LENGTH
Länge der Zeichenfolge
SUBSTR
Substring
INSTR
Instring
UPPER
Großbuchstaben
LOWER
Kleinbuchstaben
SOUNDEX Klangbildabgleich
Dies ist nur die Übersicht über einen Teil des sehr umfassenden Vorrats an arithmetischen
und Zeichenfolgefunktionen bei ORACLE (zu den Gruppierungsfunktionen von ORACLE,
AVG, SUM, COUNT, MIN und MAX wird auf den Abschnitt über GROUP BY verwiesen).
Einführung in SQL
Seite 14 von 20
FORMATIEREN UND BERECHNEN VON
DATUMSANGABEN
Formatieren des Datums
Erinnern wir uns daran, daß wir bei Erstellung der Tabelle EMP für die Spalte HIREDATE
(Einstellungsdatum) den Datentyp Datum festgelegt haben. Wenn Sie nun die Spalte
HIREDATE oder jede andere Spalte, die für eine Datumsangabe vorgesehen ist, ansteuern, so
erscheint sie im Standardformat DD-MON-YY (Tag, Monat, Jahr, z. B. 03-SEP-84). Sie
können Datenfelder in verschiedenen Formaten anzeigen lassen, indem Sie die
Formatierfunktion TO_CHAR (Spaltenbezeichnung, 'Darstellungsform') aufrufen. Schauen
wir uns die Namen, Tätigkeitsbezeichnungen und Einstelldaten aller Angestellten in
Abteilung 20 an und lassen die Einstelldaten in der Form DY DD MON YYYY (Wochentag,
Datum, Monat, Jahr) erscheinen
SELECT
FROM
WHRE
ENAME, JOB, TO_CHAR(HIREDATE, 'DY DD MON YYYY) HIREDATE
EMP
DEPTNO = 20;
ENAME
-----------------SMITH
JONES
SCOTT
ADAMS
FORD
JOB
-----------------CLERK
MANAGER
ANALYST
CLERK
ANALYST
HIREDATE
-----------------------WED 17 DEC 1980
THU 02 APR 1981
THU 09 DEC 1982
WED 12 JAN 1983
THU 03 DEC 1981
Beim Aufrufen der Funktion TO_CHAR zur Veränderung der Bildschirmdarstellung unserer
Daten HIREDATE mit TO_CHAR(HIREDATE....) haben wir das gewünschte
Darstellungsformat beschrieben (....DY DD MON YYYY), worauf die vollständige Aufgabe
dessen folgte, was wir als Überschrift für diese neue Spalte (HIREDATE) vorgesehen hatten.
Mit der Datumsfunktion von ORACLE lassen sich viele verschiedene Formate wählen. Hier
ein paar Beispiele:
FORMAT PICTURE
- Standard Default Format DAY MONTH DD, YYYY
Dy DD Mon YYYY
Day "the" ddth "of" Month YYYY
MM/DD/YY HH:MIPM
FOMATTED DATE
17-DEC-80
WEDNESDAY DECEMBER 17, 1980
Wed 17 Dec 1980
The Seventeenth of December One
Thousand Nine Hundred Eighty
12/17/80 12:00AM
Bitte achten Sie auf die Angabe der Uhrzeit beim letzten Datumbsbeispiel. Eine Spalte, die
Sie für den Datentyp Datum vorsehen, speichert tatsächlich die Werte für das Jahr, den
Monat, den Tag und die Uhrzeit ab.
Einführung in SQL
Seite 15 von 20
Datumsarithmetik
Sie können nicht nur nach Belieben Datumsfelder formatieren, sondern mit diesen auch
Rechenoperationen ausführen.
Angenommen, Sie überprüfen alle Neueingestellten nach 90 Tagen ab dem
Einstellungsdatum. Sie können nun zur Berechnung der Überprüfungsdaten für die
Beschäftigten in Abteilung 20 mit der Datumsarithmetik bei ORACLE arbeiten.
SELECT
FROM
WHERE
AND
ENAME, HIREDATE, SYSDATE, HIREDATE+90
EMP
HIREDATE+90 > SYSDATE
DEPTNO = 20;
ENAME
---------------SCOTT
ADAMS
HIREDATE
-----------------09-DEC-82
12-JAN-83
SYSDATE
-----------------04-MAR-83
04-MAR-83
HIREDATE+90
-------------------09-MAR-83
12-APR-83
Mit dem Schlüsselwort SYSDATE erfolgt immer die Angabe des heutigen Datums als
Antwort. Der Ausdruck HIREDATE + 90 in der SELECT-Klausel errechnet nun das
Überprüfungsdatum, und der Ausdruck HIREDATE + 90 > SYSDATE in der WHEREKlausel veranlaßt ORACLE zur Ergebnisangabe nur der Zeilen, die sich auf diejenigen unter
den Beschäftigten beziehen, deren Überprüfungsdaten nach dem heutigen Datum liegen, also
einen höheren Zahlenwert haben. ORACLE kann zur Datumsberechnung die folgenden
Operationen ausführen:
FUNKTION
ADD_MONTHS
LAST_DAY
MONTHS_BETWEEN
NEXT_DAY
ROUND
TRUNC
TO_DATE
ZWECK
Datum durch Addition bzw. Subtraktion von Monaten
ändern
Datum zu Ultimo des Monats vortragen
Subtraktion zweier Datumsangaben zur Ermittlung
des Unterschieds in Monaten
Datum zum angegebenen Wochentag vortragen
Datum mit vorgegebener Genauigkeit runden
Datum mit vorgegebener Genauigkeit abstreichen
Datumseingabe in anderem als dem vorgegebenen
Format möglich
Sie können ein Datumsfeld in jeder SQL-Klausel verwenden (z. B. SELECT, WHERE,
ORDER BY, etc.), in der Sie eine Spalte für Zahlen oder Zeichen benützen können.
FUNKTIONEN ZUR BERECHNUNG BEI ZEILENGRUPPEN
Gruppenfunktionen
Gruppenfunktionen sind ein weiteres Beispiel für die Leistungsfähigkeit von SQL. Mit
Gruppenfunktionen können Sie aus Zeilengruppen zusammenfassende Informationen gezielt
abfragen. Beispielsweise angenommen, Sie möchten alle Beschäftigten mit der gleichen
Abteilungskennzahl in einer Gruppe zusammenfassen und anschließend das höchste Gehalt in
jeder abteilungsbezogenen Gruppe ermitteln.
Einführung in SQL
Seite 16 von 20
SELECT
DEPTNO, MAX(SAL)
FROM
EMP
GROUP BY DEPTNO;
DEPTNO
----------10
20
30
 MAX-Funktion
 Klausel GROUP BY
MAX(SAL)
-------------5,000.00
3,000.00
2,850.00
Klausel GROUP BY
Bei einer Abfrage GROUP BY stellt jede Zeile der Abfrage eine Gruppe von Zeilen dar, wie
sie in Ihrer Tabelle abgespeichert sind. Die Spalte, die Sie in der Klausel GROUP BY
bezeichnen (DEPTNO beim vorliegenden Beispiel) ist die Spalte, nach der die Zeilen in Ihren
Tabellen zu gruppieren (GROUP) bzw. in Kategorien aufzuschlüsseln sind. Im obigen
Beispiel gibt es für jeden der 3 unterschiedlichen DEPTNO-Werte in der Tabelle EMP eine
Zeile in der Ergebnistabelle. ORACLE gruppiert gemäß der SELECT-Klausel alle Zeilen der
EMP-Tabelle nach DEPTNO, bildet pro Gruppe MAX(SAL) und gibt das Resultat in der
Ergebnistabelle aus.
Genauso wie mit den Befehlen ORDER BY können Sie auch mit GROUP BY jede bliebige
Anzahl von Spalten in einer einzigen Abfrage erfassen.
Verbinden wir nun die Benutzung der Gruppierungsfunktionen mit einer JOIN-Abfrage.
Dieses Mal wollen wir nach zwei Spalten (GROUP BY DNAME, JOB) gruppieren und drei
neue Funktionen verwenden: SUM zu Aufaddierung der Werte in speziell bezeichneten
Feldern, die in die Gruppen fallen, die wir in unserer Klausel GROUP BY festlegen;
COUNT(*) zum Zählen der Anzahl von Zeilen, die in jeder der gebildeten Gruppen fallen;
und AVG zum Ermitteln des Durchschnitts der Werte in jeder Gruppe.
Bei der Vorgabe dieser Abfrage haben wir uns zum Ziel gesetzt, festzustellen, wie viele
Angestellte in jeder Position in jeder Abteilung beschäftigt sind - z.B. wie viele als CLERK in
der Abteilung RESEARCH arbeiten - und innerhalb der Gruppe die Gesamtsummen und
Mittelwerte der Gehälter dieser Beschäftigten zu ermitteln.
SELECT
DNAME, JOB, SUM(SAL),
COUNT(*),
AVG(SAL)
FROM
EMP, DEPT
WHERE
EMP.DEPTNO = DEPT.DEPTNO
GROUP BY DNAME, JOB;
Einführung in SQL
 Funktion SUM
 Funktion COUNT(*)
 Funktion AVG
 nach 2 Spalten gruppieren
(GROUP BY)
Seite 17 von 20
DNAME
ACCOUNTING
ACCOUNTING
ACCOUNTING
RESEARCH
RESEARCH
RESEARCH
SALES
SALES
SALES
JOB
CLERK
MANAGER
PRESIDENT
ANALYST
CLERK
MANAGER
CLERK
MANAGER
SALESMAN
SUM(SAL)
1,300.00
2,450.00
5,000.00
6,000.00
1,900.00
2,975.00
950.00
2,850.00
5,600.00
COUNT(*)
1
1
1
2
2
1
1
1
4
AVG(SAL)
1,300,00
2,450.00
5,000.00
3,000.00
950.00
9,975.00
950.00
2,850.00
1,400.00
Aus der Ergebnisausgabe auf dem Bildschirm erkennen wir hier zum Beispiel, dass zwei
Angestellte als CLERK in der Forschungsabteilung (RESEARCH) beschäftigt sind, und dass
die Summe ihrer Gehälter 1,900.00 beträgt, während ihr Gehalt durchschnittlich 950.00
beträgt.
Prozedural gegenüber nichtprozedural
Betrachten wir einmal die Arbeit, die ein Programmierer zur Erstellung der gleichen Ausgabe
bei der Benützung eines prozduralen DBMS-Systems mit Einzelsatzverarbeitung oder mit
einem herkömmlichen Dateisystem zu leisten hätte: zunächst muß eine Liste der Angestellten
erstellt und nach Abteilungskennzahl und Tätigkeit sortiert werden. Als nächstes müssen die
Addition, die Zählung und die Mitteilung des Gehalts durch Berechnung nach
Abteilungskennzahl und Tätigkeit durchgeführt werden. Schließlich ist anstelle der
Abteilungskennzahl in der Ausgabe die Bezeichnung der Abteilung einzusetzen. Jeder dieser
Arbeitsabläufe verlangt vom Programmierer, daß er Einträge liest, Zwischenergebnisse
abspeichert, auf Fehler prüft, etc. Bei der nichtpozeduralen SQL-Lösung mit
Mehrzeilenverarbeitung können Sie Berichte wie diesen einfach dadurch erstellen, daß Sie
angeben, WELCHE Daten Sie brauchen: ORACLE GENERIERT DANN AUTOMATISCH
DEN ERFORDERLICHEN Ablauf zur Festlegung, WIE die Daten aus dem Datenbestand zu
nehmen sind. Bei anderen Systemen müssen Sie diesen Ablauf dagegen in Ihr Programme
einbauen.
Klausel HAVING
Genauso wie bei der Angabe von Suchkriterien für einzelne Zeilen mit der Klausel WHERE
können wir auch die SQL-Klausel HAVING dazu einsetzten, Suchbedingungen für Gruppen
von Zeilen vorzugeben. Angenommen, es interessiert Sie ein ähnliches Abfrageergebnis wie
das vorstehende, bloß mit dem Unterschied, daß Sie diesmal nur die Gruppe abrufen wollen,
die mindestens zwei Angestellte enthalten.
SELECT
DNAME, JOB, SUM(SAL), COUNT(*), AVG(SAL)
FROM
EMP, DEPT
WHERE
EMP, DEPTNO = DEPT-DEPTNO
GROUP BY DNAME, JOB
HAVING COUNT(*) >= 2;
DNAME
-------------RESEARCH
RESEARCH
Einführung in SQL
JOB
-------------ANALYST
CLERK
SUM(SAL)
-------------6,000.00
1,900.00
COUNT(*)
------------2
2
AVG(SAL)
-------------3,000.00
950.00
Seite 18 von 20
SALES
SALESMAN
5,600.00
4
1,400.00
Das Suchkriterium in unserer Klausel HAVING schloß aus dem Ergebnis als Antwort auf
unsere Anfrage alle Gruppen aus, die nicht mindestens zwei Zeilen pro Gruppe enthielten COUNT(*) >= 2
SUBQUERIES
Einer der Gründe für die hohe Leistung von SQL besteht darin, daß sich aus mehreren
einfachen Abfragen komplexe Anfragen aufbauen lassen. Zum Beispiel kann eine WHREKlausel in einer Anfrage eine weitere Anfrage enthalten, die dann als Subquery bezeichnet
wird. Diese Subqueries werden für den dynamischen Aufbau einer Suchbedingung für die
Hauptanfrage verwendet.
Angenommen, Sie brauchen eine Liste aller Angestellten mit der gleichen Tätigkeit wie sie
JONES ausübt.
SELECT
FROM
WHERE
ENAME, JOB
Hauptanfrage
EMP
JOB =
(SELECT
JOB
Subquery
FROM
EMP
WHERE
ENAME = 'JONES');
ENAME
----------JONES
BLAKE
CLARK
JOB
-----------------MANAGER
MANAGER
MANAGER
ORACLE arbeitet Subqueries vor den Hauptanfragen ab, da das Ergebnis der Unteranfrage
zur Ermittlung des Ergebnisses der Hauptanfrage benötigt wird. Der zweite Befehl SELECT
bei diesem Beispiel - hier die Subquery - ermittelte den MANAGER als gesuchten Wert; dies
können Sie auch aus dem obigen Beispiel mit der Tätigkeit von JONES in der EMP-Tabelle
erkennen. ORACLE setzt diesen Wert dann beim dynamischen Aufbau der Suchbedingung
WHERE JOB = 'MANAGER' ein, der zur Auffindung der gewünschten Zeilen im ersten
Befehl SELECT eingesetzt wird.
Als nächstes wollen wir eine Liste aller Angestellten haben, deren Gehalt über dem
Durchschnitt der Gehälter aller Beschäftigten liegt.
SELECT
FROM
WHERE
Einführung in SQL
ENAME, SAL
EMP
SAL >
(SELECT
AVG(SAL)
FROM
EMP);
Seite 19 von 20
ENAME
----------JONES
BLAKE
CLARK
SCOTT
KING
FORD
JOB
----------2,975.00
2,850.00
2,450.00
3,000.00
5,000.00
3,000.00
Eine einzelne ORACLE-Anfrage kann aus mehreren Subqueries aufgebaut sein. Subqueries
können genauso komplex wie die Hauptanfragen sein, d.h. sie können beispielsweise ihre
eigenen Suchkriterien und JOIN-Anfragen enthalten. Und diese Subqueries können auch bei
den Befehlen INSERT, UPDATE und DELETE eingesetzt werden, die im nächsten Abschnitt
erläutert werden, wie auch bei SELECT-Befehlen.
Einführung in SQL
Seite 20 von 20