Datenbanken mit SELECT erforschen

Inhaltsübersicht

Fortgeschrittene Datenbank-Abfragen mit SELECT aus SQL
Der SELECT-Befehl ist das zentrale Element aus dem Befehlsumfang von SQL. Mit SELECT werden die benötigten Daten aus den Tabellen gefiltert und können anschliessend bearbeitet werden. Das Ergebnis einer SELECT-Abfrage ist ein sogenannter SQL-Cursor, eine temporäre Tabelle welche ein Abbild der Daten zum Zeitpunkt der Abfrage enthält.

Die folgenden Beispiele basieren auf Oracle 7.3 / SQLPlus. Die Tabellenstruktur sowie die darin enthaltenen Daten stammen aus einer Beispieldatenbank der TBZ-Technikerschule, Zürich.
Zuerst den Überblick gewinnen
Zwei grundlegende Kommandos sind SELECT und DESCRIBE. Mit diesen Befehlen können jederzeit die Strukturen einer Datenbank erforscht werden. Jedes Kommando wird in SQLPlus (Terminalfenster des Oracle-Servers) mit einem Semikolon (;) abgeschlossen.

Datenbank-Katalog
Um alle verfügbaren Tabellen oder Ansichten («Views») in einer Datenbank anzuzeigen, kann mit folgendem Befehl der «Tabellenkatalog» angezeigt werden:

SELECT * FROM CAT;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
BONUS                          TABLE
DALLAS                         VIEW
DATEN                          VIEW
DEPT                           TABLE
EMP                            TABLE
EMPDEPT                       VIEW
JAHR                           VIEW
NEWCOMM                        TABLE
SALGRADE                       TABLE
SCHNITT                        VIEW
10 rows selected.
Tabellen-Struktur
Die Struktur einer Tabelle bzw. deren Feld-Definition kann mit dem DESCRIBE-Befehl von Oracle «beschrieben» werden. Die Tabelle EMP wird in der Folge näher beleuchtet:

DESCRIBE emp;

Name        Null?               Type
------------------------------- --------
EMPNO       NOTNULL             NUMBER(4)
ENAME                          VARCHAR2(10)
JOB                             VARCHAR2(9)
MGR                             NUMBER(4)
HIREDATE                        DATE
SAL                             NUMBER(7,2)
COMM                            NUMBER(7,2)
DEPTNO                          NUMBER(2)


DESCRIBE zeigt die einzelnen Attribute bzw. Felder der Tabelle sowie deren weitere Defintion an:
  • NOTNULL bedeutet, dass dieses Feld nicht leer bleiben darf, meist handelt es sich bei einem NOT NULL-Feld um einen Primär- bzw. Fremd-Schlüsselfeld bei dem eine Eingabe zwingend ist
  • TYPE definiert den Inhalt des Feldes der entweder als Zeichenkette (VARCHAR), Zahl (NUMBER) oder Datum (DATE) festgelegt werden kann. Je nach Datenbank sind auch weitere Datentypen möglich. 
Bei «modernen» Datenbanken beliebt sind als Datentyp auch sogenannte BLOB's - Binary Large Objects. Ein BLOB kann ein eingebettetes Foto eine Ton- oder Videosequenz oder ein sonstiges binäres Objekt sein.
Feldinhalte anzeigen
Für die Anzeige von Feldinhalten aus Tabellen muss bei jedem SELECT-Kommando jeweils die FROM-Klausel verwendet werden. Die Tabellenbezeichnungen hinter der FROM-Klausel definieren die Datenherkunft. Um alle Angaben einer Tabelle mit dem Namen EMP anzuzeigen genügt die Eingabe des folgenden SQL-Befehls:

SELECT * FROM emp;

EMPNO     ENAME     JOB       MGR       HIREDATE  SAL       COMM      DEPTNO
--------- --------- --------- --------- --------- --------- --------- ---------
6420      HARTMANN  NETGURU          
7369      SMITH     CLERK     7902      17-DEC-80 800                 20
7499      ALLEN     SALESMAN  7698      20-FEB-81 1600      300       30
7521      WARD      SALESMAN  7698      22-FEB-81 1250      500       30
7566      JONES     MANAGER   7839      02-APR-81 2975                20
7654      MARTIN    SALESMAN  7698      28-SEP-81 1250     1400       30
7698      BLAKE     MANAGER   7839      01-MAY-81 2850                30 
7782      CLARK     MANAGER   7839      09-JUN-81 2450                10
7839      KING      PRESIDENT          17-NOV-81 5000                10
7788      SCOTT     ANALYST   7566      19-APR-87 3000                20
7934      MILLER    CLERK     7782      23-JAN-82 1300                10

11 rows selected.


Dieses einfache SELECT-Kommando beinhaltet keine Bedingungs-Klauseln (WHERE) oder Gruppierungsfunktionen wie GROUP BY. Um beispielsweise alle Personen herauszufiltern welche zu Beginn des Feldes ENAME den Buchstaben H besitzen wird folgende WHERE-Bedingung eingesetzt:

SELECT empno,ename,job
FROM emp
WHERE ename like 'H%';

Das Ergebnis der Abfrage aus der Tabelle EMP sieht wie folgt aus:

EMPNO     ENAME      JOB
--------- ---------- ---------
6420      HARTMANN   NETGURU

Das SELECT-Kommando von SQL ist ein sehr mächtiger Befehl. Mit gezielten Argumenten können beispielsweise auch Spaltenüberschriften oder Zusatztexte zu den bestehenden Attributen hinzugefügt werden:

SELECT ename || ' angestellt als ' || job 'Angestellt als'
FROM emp;

Angestellt als
-----------------------------------
HARTMANN angestellt als NETGURU
SMITH angestellt als CLERK
ALLEN angestellt als SALESMAN
WARD angestellt als SALESMAN
JONES angestellt als MANAGER
MARTIN angestellt als SALESMAN
BLAKE angestellt als MANAGER
CLARK angestellt als MANAGER
SCOTT angestellt als ANALYST
KING angestellt als PRESIDENT
TURNER angestellt als SALESMAN
ADAMS angestellt als CLERK
JAMES angestellt als CLERK
FORD angestellt als ANALYST
MILLER angestellt als CLERK
SMITH angestellt als FRITZ

16 rows selected.
Tabellen verbinden mit «SQL-JOIN»
Um zwei Tabellen aus der Datenbank in einer Abfrage zu verbinden wird ein so genannter SQL-JOIN («verbinden») verwendet. Auf Basis der zu Beginn dieser Seite beschriebenen Datenbank («SELECT * from cat;») sollen nun einige Aufgaben gelöst werden.

Beispiel 1
  • Ausgabe von Angestellten-Name, Aufgabe, Abteilungsname
  • Abteilungsname wird zusammengesetzt werden über Verbindung von DEPTNO in Tabelle EMP und DEPTNO in Tabelle DEPT
Das notwendige SELECT-Kommando dazu beinhaltet zwei Tabellen in der FROM-Klausel, für beiden Tabellen wird innerhalb von FROM ein Alias (alternative Bezeichnung) vereinbart für EMP der Alias E für DEPT der Alias D.

SELECT E.ename, E.job, E.deptno, D.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

Variante der Tabellen-Verbindung mit INNER JOIN:

SELECT E.ename, E.job, E.deptno, D.dname
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno;

Hinweis: JOIN wird innerhalb des SELECT-Kommandos nicht als Schlüsselwort angewendet. Die Angabe von mindestens zwei Tabellennamen in der FROM-Klausel wird als SQL-JOIN bezeichnet.

ENAME     JOB       DEPTNO    DNAME
--------- --------- --------- ---------
ALLEN     SALESMAN 30        SALES
WARD     SALESMAN  30        SALES
JONES    MANAGER   20        RESEARCH
MARTIN   SALESMAN  30        SALES
BLAKE    MANAGER   30        SALES
CLARK    MANAGER   10        ACCOUNTING
SCOTT    ANALYST   20        RESEARCH
KING     PRESIDENT 10        ACCOUNTING
TURNER   SALESMAN  30        SALES
ADAMS    CLERK     20        RESEARCH
JAMES    CLERK     30        SALES
FORD      ANALYST   20        RESEARCH
MILLER   CLERK     10        ACCOUNTING
SMITH     FRITZ     10        ACCOUNTING

15 rows selected.


Beispiel 2
Im folgenden Beispiel wird zusätzlich zum JOIN eine Spaltenbezeichnung «Manager» hinzugefügt.
  • Ausgabe von Angestelltennummer, Angestelltenname, Angestellten-Nummer des Vorgesetzten und dessen Name
  • Vorgesetzter ist in gleicher Tabelle MGR-Nummer zeigt auf EMPNO
SELECT E1.empno, E1.ename, E1.mgr, E2.ename Manager 
FROM EMP E1, EMP E2 
WHERE E1.mgr = E2.empno;

Das Ergebnis dieser Abfrage bringt als Antwort die folgende Bildschirmausgabe:

EMPNO     ENAME      MGR       MANAGER
--------- ---------- --------- ---------
7369      SMITH      7902      FORD
7499      ALLEN      7698      BLAKE
7521      WARD       7698      BLAKE
7566      JONES      7839      KING
7654      MARTIN     7698      BLAKE
7698      BLAKE      7839      KING
7782      CLARK      7839      KING
7788      SCOTT      7566      JONES
7844      TURNER    7698      BLAKE
7876      ADAMS     7788      SCOTT
7900      JAMES     7698      BLAKE
7902      FORD      7566      JONES
7934      MILLER    7782      CLARK
6666      SMITH      7782      CLARK

14 rows selected.

Beispiel 3
Der Name des Angestellten sowie Lohn und Eintrittsdatum sollen angezeigt werden. Zusätzlich sollen nur Datensätze von Mitarbeitern aufgelistet sein, die länger angestellt sind als der Mitarbeiter «TURNER».

SELECT E1.ename, E1.sal, E1.hiredate
FROM emp E1, emp E2
WHERE E2.hiredate > E1.hiredate
AND E2.ename = 'TURNER';

Bei dieser Abfrage wird die Tabelle EMP mit zwei Aliasen benutzt, einerseits E1, andererseits E2. In der WHERE-Klausel wird eine AND-Verbindung angewendet, die einerseits das Datum zwischen den beiden Tabellen (E1 und E2, eigentlich aber nur EMP) vergleicht, andererseits zunächst aber für die Tabelle E2 nur den Datensatz mit dem Namen TURNER herausfiltert. Das folgende Ergebnis ist korrekt:

ENAME     SAL        HIREDATE
--------- ---------- ---------
SMITH     800        17-DEC-80
ALLEN     1600       20-FEB-81
WARD      1250       22-FEB-81
JONES     2975       02-APR-81
BLAKE     2850       01-MAY-81
CLARK     2450       09-JUN-81

6 rows selected.

Grundsatz:
Jede SQL-Abfrage in SQL Plus (Oracle) wird zunächst vom SQL-Interpreter auf die Richtigkeit in Bezug auf Befehle, Anordnung und Feldnamen überprüft. Anschliessend wird die Abfrage rückwärts aufgelöst.
Berechnungen in SQL-Abfragen
Ähnlich einer Tabellenkalkulation wie Microsoft Excel kann auch SQL mit Formeln mathematische Operationen vornehmen und Berechnungen ausführen. Im Unterschied zu einer interaktiven Anwendung wie Excel müssen bei SQL jedoch die mathematischen Funktionen in das Abfrage-Kommando integriert werden. Die daraus entstehende Tabelle beinhaltet bereits das Ergebnis.

Summen bilden mit SUM()
In der bereits verwendeten Datenbank soll die Summe der Salär-Spalte in der Tabelle EMP gebildet werden. Die Datensätze sollen nach der Höhe des Salärs sortiert werden. Datensätze ohne Salär («Frondienst») werden durch die Abfrag ausgefiltert.

SELECT ename, empno, sal 
FROM emp 
WHERE sal > 0
UNION SELECT 'Total', 0, SUM(sal) 
FROM emp
ORDER BY sal;

In dieser SQL-Abfrage werden über die UNION-Klausel zwei Abfragen nacheinander ausgeführt. Das Ergebnis präsentiert sich wie folgt:

ENAME        EMPNO         SAL
---------    ----------    ---------
SMITH        7369          800
JAMES        7900          950
SMITH        6666          1200
MARTIN       7654          1250
WARD         7521          1250
ADAMS        7876          1300
MILLER       7934          1300
TURNER       7844          1500
ALLEN        7499          1600
CLARK        7782          2450
BLAKE        7698          2850
JONES        7566          2975
FORD         7902          3000
SCOTT        7788          3000
KING         7839          5000
Total        0            30425

16 rows selected.


Die Total-Zeile stellt einen virtuellen Datensatz dar, sie wird in der zweiten SQL-Abfrage gebildet. Die Angabe von «0» ist notwendig, weil jede Spalte eines virtuellen Datensatzes einen Wert beinhalten muss.

Zählen von Datensätzen mit COUNT()
Grundsätzlich müssen alle Berechnungen eines SQL-Statements zwischen SELECT und der FROM-Klausel verfasst werden. Um Datensätze zählen zu können wird die COUNT()-Funktion angewendet. Um beispelsweise alle Datensätze der Tabelle EMP zu zählen wird folgende Abfrage formuliert:

SELECT count(empno)
FROM emp;

Die Ausgabe dieser Abfrage umfasst lediglich die Anzahl der gefundenen Datensätze, die Feldinhalte werden nicht angezeigt.

Mittelwert berechnen mit AVG()
Um beispielsweise das durchschnittliche Salär aller Mitarbeiter zu berechnen kann die AVERAGE() bzw. AVG()-Funktion eingesetzt werden. In der folgenden Fragestellung sollen zudem die mittleren Saläre der einzelnen Abteilungen berechnet werden, dazu wird das Schlüsselwort GROUP BY verwendet. Folgende Spalten sollen angezeigt werden:
  • Abteilungsname
  • Ort an welchem sich Abteilung befindet
  • Anzahl Angestellte pro Abteilung
  • Mittlerer Lohn der Angestellten pro Abteilung
Die Datensätze werden pro Abteilung aggregiert bzw. gruppiert, zusätzlich werden die Anzahl Datensätze pro Abteilung angezeigt.

SELECT dname, loc, COUNT(empno), AVG(sal)
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY dname, loc;

Das Ergebnis dieser anspruchsvollen Abfrage sieht wie folgt aus:

DNAME          LOC           COUNT(EMPNO) AVG(SAL)
-------------- ------------- ------------ ---------
ACCOUNTING     NEW YORK      4            2487.5
RESEARCH       DALLAS        5            2215
SALES          CHICAGO       6            1566.6667

Die etwas unleserlichen Spaltenüberschriften wie COUNT(EMPNO) könnten noch optimiert werden, für die Spaltenbezeichnung kann ebenfalls ein Alias vergeben werden. 

Wenn nun zusätzlich zu den bisherigen Kriterien nur die Datensätze mit mehr als 5 Angestellten angezeigt werden sollen, so ist das SQL-Kommando wie folgt zu erweitern:

SELECT dname, loc, COUNT(empno), AVG(sal)
FROM emp, dept
WHERE emp.deptno = dept.deptno 
GROUP BY dname, loc
HAVING COUNT(empno) >=5;

Mit Hilfe der HAVING-Klausel kann die Gruppierung weiter eingeschränkt werden:

DNAME          LOC           COUNT(EMPNO) AVG(SAL)
-------------- ------------- ------------ ---------
RESEARCH       DALLAS        5            2215
SALES          CHICAGO       6            1566.6667


Da in NEW YORK nur 4 Mitarbeiter angestellt sind, wird diese Gruppierung nicht angezeigt.

Minimum und Maximum-Werte
Um die Werte innerhalb der Ober- und Untergrenze einer Spalte anzuzeigen stellt SQL die Funktionen MIN() und MAX() zur Verfügung. Um die Angaben überprüfen zu können sollen zusätzlich die Spalten
  • Angestelltenname
  • Lohn
angezeigt werden. Da gleichzeitig zwei Funktionen angewendet werden, wird eine ODER-Verküpfung angewendet:

SELECT ename, sal
FROM emp
WHERE sal = (SELECT MIN(sal) FROM emp) OR
sal = (SELECT MAX(sal) FROM emp);

In dieser Abfrage wird zunächst eine Tabelle mit den beiden Spalten ENAME und SAL bereitgestellt, anschliessend werden die integrierten SELECT-Befehle (innerhalb der Klammern) interpretiert.

ENAME      SAL
---------- ---------
SMITH      800
KING       5000


Auf Basis dieser kleinen Sammlung von Beispielen wird deutlich, welche mächtigen Abfragen mit Kommandos wie SQL-SELECT möglich sind.