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.
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.
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:
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.
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.
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
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.
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.
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
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
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:
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
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.
Ä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
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
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.