Kurs
Reporting in SQL
4 Std.
39.7K
In diesem Tutorial konzentrierst du dich auf ANSI (American National Standards Institute) SQL, das auf allen Datenbanken funktioniert – etwa Oracle, MySQL, Microsoft SQL Server und mehr! Starten wir mit einer Einführung in SQL (Structured Query Language) und warum Datenexpertinnen und -experten es brauchen.
SQL und Data Science
In diesem Teil siehst du, warum du als Data Scientist SQL lernen solltest. So hilft dir SQL in deiner Karriere:
- SQL ist für die meisten Data-Science-Jobs gesetzt: Data Analyst, BI-Entwickler, Programmierer oder Datenbankentwickler. Mit SQL kommunizierst du mit der Datenbank und arbeitest direkt mit deinen Daten.
- Wenn du Software wie Tableau oder andere Reporting- bzw. Visualisierungstools genutzt hast, kennst du die Verbindung zur Datenbank: Du ziehst Diagramme ins Dashboard, wählst Felder aus – der Rest läuft im Hintergrund. Dort sorgt SQL für die Interaktion mit der Datenbank. Mit SQL kannst du diese Schritte selbst übernehmen.
- SQL funktioniert mit allen gängigen Programmiersprachen wie PHP oder Java. Du kannst Visualisierungen in Anwendungen integrieren oder Daten aus der Datenbank holen und in XML- oder JSON-Formate für Webservices oder APIs umwandeln.
- Datenbanken haben sich mit Big Data stark weiterentwickelt. Neben klassischen, strukturierten Systemen gewinnen NoSQL-Datenbanken an Bedeutung. SQL-Kenntnisse bilden eine solide Basis, um zu verstehen, wann du relationale und wann NoSQL-Datenbanken einsetzt – und worin ihre Stärken liegen.
Als Nächstes bekommst du eine kurze Einführung in SQL und zentrale Datenbankbegriffe, damit du schneller produktiv wirst. Wenn du bereits weißt, was eine relationale Datenbank ist und was SQL macht, spring direkt zum Code.
Einführung in SQL und Datenbanken
Eine Datenbank ist eine organisierte Sammlung von Informationen. Zur Verwaltung nutzen wir Datenbankmanagementsysteme (DBMS). Ein DBMS speichert, liest und ändert Daten in Datenbanken auf Anfrage.
Es gibt verschiedene Datenbanktypen: hierarchische, Netzwerk-, relationale und heute auch NoSQL-Datenbanken. Eine relationale Datenbank besteht aus Relationen, also zweidimensionalen Tabellen.
Wichtige Begriffe im RDBMS:
| Begriff | Beschreibung |
|---|---|
| Tabelle | Die grundlegende Speicherstruktur eines RDBMS. Eine Tabelle hält alle relevanten Daten zu einem Objekt aus der realen Welt. Beispiel: Mitarbeitende. |
| Zeile oder Tupel | Repräsentiert alle Daten zu einer bestimmten Person. Jede Zeile kann über einen Primärschlüssel identifiziert werden, der Duplikate verhindert. |
| Spalte oder Attribut | Bezeichnet typischerweise ein Merkmal einer Entität. |
| Primärschlüssel | Ein Feld, das eine Zeile eindeutig identifiziert. |
| Fremdschlüssel | Eine Spalte, die die Beziehung zwischen Tabellen herstellt. Ein Fremdschlüssel verweist auf einen Primärschlüssel in einer anderen Tabelle. |
Mehrere Tabellen lassen sich über Primär- und Fremdschlüssel verknüpfen. Jede Zeile in einer relationalen Datenbank ist über einen Primärschlüssel (PK) eindeutig. Mit einem Fremdschlüssel (FK) verweist du auf eine andere Tabelle. Beispiel:
Auf relationale Datenbanken greifst du mit der Structured Query Language, kurz SQL, zu. Jede Datenbank unterstützt den ANSI-Standard, ergänzt aber oft eigene Syntax-Erweiterungen. In diesem Tutorial lernst du ANSI SQL, damit du mit allen gängigen Systemen arbeiten kannst. ANSI SQL lässt sich in fünf Bereiche gliedern; für uns sind hier vor allem Datenabfrage und DML relevant:
- Datenabfrage:
- SELECT.
- Data Manipulation Language (DML):
- INSERT, UPDATE, DELETE, MERGE
- Data Definition Language (DDL):
- CREATE, ALTER, DROP, RENAME, TRUNCATE.
- Data Control Language (DCL):
- GRANT, REVOKE.
- Transaktionskontrolle:
- COMMIT, ROLLBACK, SAVEPOINT.
Zu den verbreitetsten RDBMS-Anbietern zählen:
- Oracle (Oracle Corporation)
- Microsoft SQL Server (Microsoft)
- MySQL (Oracle Corporation)
- PostgreSQL (PostgreSQL Global Development Group)
- SQLite (entwickelt von D. Richard Hipp)
Wo führst du SQL-Abfragen aus? Zum Beispiel:
- In Reporting-Software, die Daten aus der Datenbank liest und anzeigt (z. B. Tableau, Microsoft BI)
- In GUIs zur Datenbankadministration (z. B. TOAD, SQL Developer für Oracle, phpMyAdmin für MySQL)
- In einer Konsolenoberfläche mit Direktzugriff auf die Datenbank (z. B. SQL*Plus für Oracle)
Mit Zugangsdaten kannst du Datenbankobjekte per Abfragen oder GUI deines Anbieters einsehen.
Das war’s mit der Einführung – legen wir los mit SQL!
SQL-Datentypen
Jede Spalte in einer Datenbank hat einen Namen, einen Datentyp und oft auch eine definierte Länge. Datenbankentwicklerinnen und -entwickler entscheiden anhand von Anforderungen und Datenvolumen, welcher Datentyp passt.
Als Data Scientist solltest du Datentypen kennen, um Funktionen korrekt anzuwenden und präzise Abfragen zu schreiben. Es gibt passende Datentypen für Namen, Fließtext, Zahlen, Bilder in der Datenbank und vieles mehr.
Hier siehst du grundlegende Datentypen für Oracle Server, SQL Server und MySQL:
Weiterführende Infos:
SQL und Data Reporting
Für alle SQL-Beispiele in diesem Tutorial nutzen wir folgendes vereinfachtes Schema:
Es gibt zwei Tabellen: emp mit Mitarbeitendendaten und dept mit Informationen zu Abteilungen.
Die emp-Tabelle enthält Mitarbeitendennummer (empno), Name (ename), Gehalt (sal), Provision (comm), Stellenbezeichnung (job), Manager-ID (mgr), Einstellungsdatum (hiredate) und Abteilungsnummer (deptno). Da Manager ebenfalls Mitarbeitende sind, verweist mgr auf eine empno, deren job "MANAGER" ist.
Die dept-Tabelle enthält Abteilungsnummer (deptno), Abteilungsname (dname) und Standort (loc).
Beachte: Unterschiedliche Datenbanken nutzen unterschiedliche Datumsformate. DD-MON-YY ist das Standardformat in Oracle. Microsoft SQL Server und MySQL verwenden standardmäßig YYYY-MM-DD.
Deine Tabellen können abweichen – passe dann lediglich Tabellen- und Spaltennamen an. In diesem Tutorial liest du nur Daten; du schreibst, aktualisierst oder erstellst keine Tabellen oder Objekte. Es gibt also kein Risiko für Datenänderungen.
Info: Ein Schema ist die Sammlung von Objekten wie Tabellen, Funktionen, Prozeduren und Views, die zu einem Benutzer gehören.
In unserem Schema siehst du: emp hat sechs Attribute für die Entität Employee, dept hat drei Attribute für Department.
Daten mit SELECT abfragen
Eine SELECT-Anweisung holt Informationen aus der Datenbank. Damit kannst du:
1. Projektion: Bestimme, welche Spalten eine Abfrage zurückgeben soll – so viele oder wenige, wie du brauchst.
2. Selektion: Lege fest, welche Zeilen aufgrund bestimmter Kriterien zurückgegeben werden.
3. Joins: Führe Daten aus mehreren Tabellen über Verknüpfungen zusammen.
Die Basis-SELECT-Anweisung gibt an, welche Spalten aus welcher Tabelle kommen. Die SELECT-Klausel enthält Spalten und Ausdrücke, FROM bestimmt die Tabelle(n).
Beispiel: Wie heißen alle Mitarbeitenden und welche Jobs haben sie? SELECT ename, job FROM employee; Auf Basis des oben beschriebenen Schemas ergibt sich etwa folgende Ausgabe:
| ename | job |
|---|---|
| A | Salesman |
| B | Manager |
| C | Manager |
Mit dem Sternchen * holst du alle Spalten und alle Zeilen:
SELECT *
FROM employee;
The output will be:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
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
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
Tipps für SQL-Statements:
- SQL ist nicht case-sensitiv.
- Schreibe Klauseln in getrennten Zeilen für bessere Lesbarkeit.
- Statements können ein- oder mehrzeilig sein.
Du kannst Ausdrücke mit +, -, /, * auf Datum und Zahlen bilden. Beispiel: Gefragt sind 20 % des Gehalts aller Mitarbeitenden:
SELECT ename, sal*(20/100)
FROM employee;
Output:
ENAME SAL*(20/100)
---------- ------------
SMITH 160
ALLEN 320
WARD 250
JONES 595
BLAKE 570
Info:
- Klammern erhöhen die Klarheit.
- Division und Multiplikation haben Vorrang vor Addition und Subtraktion.
- Bei gleichem Vorrang gilt Links-nach-Rechts.
NULL-Werte werden speziell behandelt: NULL bedeutet „unbekannt“. Jede Operation mit NULL liefert NULL. Unterschiedliche Datenbanken haben verschiedene Funktionen zum Umgang mit NULL. Eine verbreitete Funktion in MySQL, Microsoft SQL Server und Oracle ist:
SELECT ename, sal+COALESCE(comm,0)
FROM employee;
Output:
ENAME SAL+COALESCE(COMM,0)
---------- --------------------
SMITH 800
ALLEN 1900
WARD 1750
JONES 2975
BLAKE 2850
Spaltenaliase und Verkettung
Wie du oben siehst, entsprechen die Spaltennamen standardmäßig den Feldnamen oder Ausdrücken. Für Reports möchtest du oft eigene Überschriften vergeben – das geht mit Aliasen.
SELECT ename AS "Emp Name", sal*(20/100) as "20% of Salary"
FROM employee;
Output:
Emp Name 20% of Salary
---------- ------------
SMITH 160
ALLEN 320
WARD 250
JONES 595
BLAKE 570
Enthält ein Alias Leerzeichen, setze ihn in doppelte Anführungszeichen. Ansonsten sind sie nicht nötig; AS kann auch weggelassen werden.
Du kannst Ausgaben per Verkettung formatieren. Je nach Datenbank nutzt du CONCAT-Funktionen oder Operatoren wie || bzw. +:
- Oracle unterstützt CONCAT() und ||, wobei CONCAT() nur zwei Argumente akzeptiert (bei Bedarf schachteln).
- MySQL nutzt CONCAT().
- Microsoft SQL Server nutzt den Operator + und CONCAT().
Oracle: SELECT '20% of salary of '||ename||' is '||sal*(20/100) as "20% of salary" FROM employee;
SELECT CONCAT(CONCAT('20% of salary of',ename),CONCAT(' is ',sal(20/100))) as "20% of salary" FROM employee; MySQL und Microsoft SQL Server: SELECT CONCAT('20% of salary of ',ename,' is ',sal(20/100)) as "20% of salary" FROM employee; Alle liefern dasselbe Ergebnis:
20% of salary
-----------------------------------------------------------------------
20% of salary of SMITH is 160
20% of salary of ALLEN is 320
20% of salary of WARD is 250
20% of salary of JONES is 595
20% of salary of BLAKE is 570
Doppelte Zeilen mit DISTINCT entfernen
SELECT deptno
FROM employee;
Above query will result in:
DEPTNO
----------
20
30
30
20
30
Hier wiederholen sich die Werte 20 und 30. Mit DISTINCT in der SELECT-Klausel entfernst du Duplikate.
SELECT distinct ename, deptno, job
FROM employee;
DEPTNO
----------
30
20
Daten filtern und sortieren
Mit WHERE filterst du Daten per Bedingung.
Finde alle Mitarbeitenden mit dem Job CLERK:
SELECT ename, job
FROM employee
WHERE job='CLERK';
ENAME JOB
---------- ---------
SMITH CLERK
Du kannst verschiedene Bedingungen kombinieren – per Operatoren, Vergleichssymbole und Schlüsselwörter:
Die Nutzung von =,<>,!=,>=,<=,>,< ist unkompliziert, wie im Beispiel mit = gezeigt.
AND & OR Syntax: SELECT column1, column2,.. FROM table_name WHERE condition1 AND condition2 AND condition 3...;
SELECT column1, column2,.. FROM table_name WHERE condition1 OR condition2 OR condition 3...; Finde Namen von Mitarbeitenden, deren Job MANAGER ist und die zur Abteilung 30 gehören:
SELECT ename
FROM employee
WHERE job='MANAGER' AND deptno=30;
ENAME
----------
BLAKE
SELECT ename
FROM employee
WHERE job='MANAGER' OR deptno=30;
ENAME
----------
ALLEN
WARD
JONES
BLAKE
NOT-Syntax: SELECT column1, column2, ... FROM table_name WHERE NOT condition; Finde alle Mitarbeitenden, deren Job nicht SALESMAN ist:
SELECT ename, job
from employee
WHERE NOT job='SALESMAN';
ENAME JOB
---------- ---------
SMITH CLERK
JONES MANAGER
BLAKE MANAGER
Du kannst komplexe Bedingungen mit AND, OR und NOT kombinieren. Die Reihenfolge ist:
- NOT
- AND
- OR
Finde alle Mitarbeitenden, deren Job nicht CLERK ist und die zur Abteilung 20 gehören:
SELECT ename, job
from employee
WHERE NOT job='SALESMAN' AND sal>800;
ENAME JOB
---------- ---------
JONES MANAGER
BLAKE MANAGER
Hier wurde zuerst NOT, dann AND ausgewertet.
Weitere nützliche Operatoren:
BETWEEN...AND:
SELECT *
FROM employee
WHERE sal BETWEEN 1000 AND 2000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
LIKE:
LIKE verwendet zwei Wildcards: Prozentzeichen % und Unterstrich _ für Muster mit variabler Länge.
- % bedeutet beliebig viele Zeichen (auch 0)
- %M%: Enthält irgendwo ein M
- M%: Beginnt mit M
- %M: Endet mit M
- M%A: Beginnt mit M und endet mit A
Muster sind case-sensitiv.
- _ steht für die Anzahl unbekannter Zeichen vor/nach einem bekannten Zeichen. Ein Unterstrich = ein Zeichen.
- _r%: r an zweiter Stelle.
Namen aller Mitarbeitenden, die mit „B“ beginnen:
SELECT *
FROM employee
WHERE ename LIKE 'B%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
Namen aller Mitarbeitenden, die mit „A“ beginnen und danach irgendwo ein „E“ enthalten:
SELECT *
FROM employee
WHERE ename LIKE 'A%E%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
IN(value1,value2, value3,..):
IN() akzeptiert einen oder mehrere Werte und vergleicht eine Spalte mit der Werteliste in Klammern in der WHERE-Klausel:
SELECT ename, job, hiredate
FROM employee
WHERE job IN ('CLERK','SALESMAN');
ENAME JOB HIREDATE
---------- --------- ---------
SMITH CLERK 17-DEC-80
ALLEN SALESMAN 20-FEB-81
WARD SALESMAN 22-FEB-81
Du kannst in IN() auch eine SELECT-Anweisung verwenden, die Werte zurückliefert. Beispiel:
SELECT ename, job, hiredate
FROM employee
WHERE deptno IN (select deptno FROM department WHERE loc='CHICAGO');
ENAME JOB HIREDATE
---------- --------- ---------
ALLEN SALESMAN 20-FEB-81
WARD SALESMAN 22-FEB-81
BLAKE MANAGER 01-MAY-81
Die SELECT-Anweisung in IN() nennt man auch Unterabfrage (Subquery). Mehr dazu später!
IS NULL:
Mit IS NULL prüfst du auf NULL-Werte in einer Spalte. Beispiel: Mitarbeitende ohne Provision:
SELECT ename, job, sal
FROM employee
WHERE comm IS NULL;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
JONES MANAGER 2975
BLAKE MANAGER 2850
Mit IS NOT NULL findest du Mitarbeitende mit Provision:
SELECT ename, job, sal, comm
FROM employee
WHERE comm IS NOT NULL;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
ALLEN SALESMAN 1600 300
WARD SALESMAN 1250 500
Für Datumsvergleiche nutze das Standardformat der Datenbank. Für andere Formate brauchst du Datumsfunktionen (später mehr). Finde alle Mitarbeitenden, die nach dem 21. Februar 1981 eingestellt wurden:
(Hier mit Oracle-Standardformat)
SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81';
ENAME JOB SAL COMM
---------- --------- ---------- ----------
WARD SALESMAN 1250 500
JONES MANAGER 2975
BLAKE MANAGER 2850
Komplexe Bedingungen sind durch Kombination aller obigen Operatoren möglich – achte dabei auf die Operatorrangfolge. Hier die Regeln nach Datenbank:
- Microsoft Transact-SQL Operatorrangfolge
- Oracle 10g Bedingungsrangfolge
- Oracle MySQL 9 Operatorrangfolge
- PostgreSQL Operatorrangfolge
- SQLite Operatorrangfolge
Zwei hilfreiche Funktionen für Bedingungen: ANY() und ALL(). Beispiel:
SELECT ename, job, sal, comm FROM employee WHERE deptno=ANY(SELECT deptno from dept WHERE loc='NEW YORK');
SELECT ename, job, sal, comm FROM employee WHERE deptno=ALL(SELECT deptno from dept WHERE dname='SALES');
Beachte die Nutzung von Subqueries – mehr dazu folgt.
Ergebnisse mit ORDER BY sortieren
Sortiere auf- (ASC) oder absteigend (DESC) nach einer oder mehreren Spalten. Du kannst auch nach Aliasen sortieren:
SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY sal desc;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
WARD SALESMAN 1250 500
Hinweis: Standard ist aufsteigend (ASC); das musst du nicht angeben.
SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY sal;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
WARD SALESMAN 1250 500
BLAKE MANAGER 2850
JONES MANAGER 2975
Mehrere Spalten in ORDER BY werden in der angegebenen Reihenfolge ausgewertet. Beispiel: zuerst nach deptno aufsteigend, dann nach Namen absteigend:
SELECT ename, job, sal, comm, deptno
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY deptno ASC, ename DESC;
ENAME JOB SAL COMM DEPTNO
---------- --------- ---------- ---------- ----------
JONES MANAGER 2975 20
WARD SALESMAN 1250 500 30
BLAKE MANAGER 2850 30
Einzeilige Funktionen zur Ausgabeformatierung
Alle RDBMS bieten zahlreiche Funktionen für Standardaufgaben wie String-Länge, String-Verkettung, Formatierungen, Mathematik usw. Es gibt zwei Arten von Zeilenfunktionen:
- Einzeilige Funktionen
- Mehrzeilige Funktionen
Einzeilige Funktionen:
Sie werden pro Zeile angewendet und liefern pro Zeile ein Ergebnis. CONCAT() ist z. B. eine Zeichenkettenfunktion. Du kannst sie in SELECT, WHERE und ORDER BY nutzen. Typische Kategorien in allen RDBMS:
- Zeichenkettenfunktionen
- Datums- und Zeitfunktionen
- Zahlenfunktionen
- Konvertierungsfunktionen
Die Funktionsnamen variieren je nach Datenbank, die Funktionalität ist ähnlich. Hier sind nützliche, weit verbreitete Funktionen in Microsoft SQL Server, Oracle und MySQL. Einige hast du oben schon gesehen. Am Ende dieses Abschnitts findest du Links zu vollständigen Listen für zusätzliche Übungen.
Gehen wir die Kategorien durch:
Zeichenkettenfunktionen
LOWER(): Wandelt einen String in Kleinbuchstaben.
SELECT lower(ename) as ename
FROM employee;
ENAME
----------
smith
allen
ward
jones
blake
UPPER(): Wandelt einen String in Großbuchstaben.
SELECT upper(ename) as ename
FROM employee;
ENAME
----------
SMITH
ALLEN
WARD
JONES
BLAKE
SUBSTR()[Oracle, MySQL]: Gibt einen Teilstring zurück: SUBSTR(string, Startposition, Länge)
SUBSTRING()[SQL Server]: Entspricht SUBSTRING(string, Startposition, Länge).
SELECT SUBSTR(ename,2,3) as substr_ename FROM employee;
Für SQL Server den Funktionsnamen durch SUBSTRING ersetzen.
SUBSTR_ENAME ------------ MIT LLE ARD ONE LAK
LENGTH()[Oracle, MySQL]: Gibt die Länge des Strings zurück
LEN()[SQL Server]: Gibt die Länge des Strings zurück
SELECT LENGTH(ename) as len_ename FROM employee;
Für SQL Server LENGTH durch LEN ersetzen.
LEN_ENAME
----------
5
5
4
5
5
Funktionen wie Padding links/rechts oder REPLACE unterscheiden sich in der Syntax je nach Datenbank. Listen der Zeichenkettenfunktionen:
Zahlenfunktionen
| Funktionsname | Funktion |
|---|---|
| ROUND(m,n): | Rundet den Wert m auf n Dezimalstellen. |
| ABS(m,n): | Liefert den Absolutwert einer Zahl. |
| FLOOR(n): | Gibt die größte ganze Zahl ≤ n zurück. |
| MOD(m,n): | Liefert den Rest von m geteilt durch n (in SQL Server stattdessen %: 35 % 6) |
Oracle:
SELECT ROUND(45.926,2),MOD(11,5),FLOOR(34.4),ABS(-24) from dual;
OUTPUT:
ROUND(45.926,2) MOD(11,5) FLOOR(34.4) ABS(-24)
--------------- ---------- ----------- ----------
45.93 1 34 24
MySQL:
SELECT ROUND(45.926,2),MOD(11,5),FLOOR(34.4),ABS(-24);
SQL Server:
SELECT ROUND(45.926,2) as round,11%5 as mod, FLOOR(34.4) as floor,ABS(-24) as abs;
Listen von Zahlenfunktionen:
Konvertierungsfunktionen
Konvertierungsfunktionen ändern Datentypen. Die konkreten Funktionen unterscheiden sich je nach Server. Beispiel für Oracle mit to_char, um die Ausgabe zu formatieren. Das Standarddatumsformat ist dort DD-MON-YY:
SELECT ename, to_char(hiredate,'DD, MONTH YYYY') as Hiredate, to_char(hiredate,'DY') as Day
from employee;
ENAME Hiredate DAY
---------- -------------------------------------------- ------------
SMITH 17,DECEMBER 1980 WED
ALLEN 20,FEBRUARY 1981 FRI
WARD 22,FEBRUARY 1981 SUN
JONES 02,APRIL 1981 THU
BLAKE 01,MAY 1981 FRI
Es gibt viele Konvertierungsfunktionen pro Datenbank. Weitere Informationen:
- Microsoft SQL Server Conversion Functions
- Oracle Server Conversion Functions
- MySQL Conversion Functions
Datums- und Zeitfunktionen
Damit addierst du Tage, berechnest Monate zwischen zwei Daten usw. – sehr nützlich im Reporting. Funktionsnamen variieren je nach System, die Aufgaben sind ähnlich. Hier ein Überblick und weiterführende Links zu Oracle, SQL Server und MySQL:
- Oracle Date and Time functions
- MySQL Date and Time functions
- Microsoft SQL Server Date and Time functions
SQL-Ergebnisse gruppieren
Gruppierungs- oder Mehrzeilenfunktionen arbeiten auf Gruppen und liefern je Gruppe ein Ergebnis. Du brauchst sie z. B. für: Umsatz pro Quartal, durchschnittliche Preise über Zeiträume, höchste Investition im Monat – und vieles mehr.
Aggregierte Daten mit Gruppenfunktionen reporten
Mit GROUP BY gruppierst du Ergebnisse – oft zusammen mit COUNT, MAX, MIN, AVG und SUM. Die Syntax:
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
Wichtige Punkte zu GROUP BY:
- Aliase kannst du in GROUP BY nicht verwenden.
- WHERE steht immer vor GROUP BY.
- HAVING folgt auf GROUP BY und filtert über Gruppenergebnisse.
- ORDER BY steht immer am Ende.
COUNT zählt Zeilen entsprechend einer Bedingung (oder aller Zeilen). NULL-Werte werden ignoriert.
Zähle alle Mitarbeitenden, deren Name mit „A“ beginnt:
SELECT count(ename)
FROM employee
WHERE ename LIKE 'A%';
Output:
COUNT(ENAME)
------------
1
Gesamt-, Durchschnitts-, Minimal- und Maximalgehalt in employee:
SELECT sum(sal) as "TOTAL SAL", avg(sal) as "AVG SAL", min(sal) as "MIN SAL", max(sal) as "MAX SAL"
FROM employee;
TOTAL SAL AVG SAL MIN SAL MAX SAL
---------- ---------- ---------- ----------
9475 1895 800 2975
Gruppiere das nach Abteilung:
SELECT sum(sal) as "TOTAL SAL", avg(sal) as "AVG SAL", min(sal) as "MIN SAL", max(sal) as "MAX SAL", deptno
FROM employee
GROUP BY deptno;
TOTAL SAL AVG SAL MIN SAL MAX SAL DEPTNO
---------- ---------- ---------- ---------- ----------
5700 1900 1250 2850 30
3775 1887.5 800 2975 20
Varianz und Standardabweichung berechnest du mit:
| Für Oracle und MySQL | MS SQL Server |
|---|---|
| SELECT STDDEV(column_name) FROM table_name; | SELECT STDEV(column_name) FROM table_name; |
| SELECT VARIANCE(column_name) FROM table_name; | SELECT VAR(column_name) FROM table_name; |
Du kannst nach mehreren Spalten gruppieren.
Summiere die Gehälter je Job innerhalb jeder Abteilung:
SELECT sum(sal) as "TOTAL SAL", deptno, job
FROM employee
GROUP BY deptno, job;
TOTAL SAL DEPTNO JOB
---------- ---------- ---------
800 20 CLERK
2850 30 SALESMAN
2975 20 MANAGER
2850 30 MANAGER
Gruppenergebnisse schränkst du mit HAVING ein. Das gilt nur für Bedingungen auf Aggregaten.
Hinweis: Verwende für Bedingungen auf Aggregaten nicht WHERE.
SELECT sum(sal) as "TOTAL SAL", deptno, job
FROM employee
GROUP BY deptno, job
HAVING sum(sal)>1000
ORDER BY sum(sal);
TOTAL SAL DEPTNO JOB
---------- ---------- ---------
2850 30 SALESMAN
2850 30 MANAGER
2975 20 MANAGER
Daten aus mehreren Tabellen anzeigen
In diesem Abschnitt behandeln wir folgende Joins:
- Kartesisches Produkt/CROSS JOIN
- Inner Join/Equijoin
- Natural Join
- Outer Joins (Left, Right, Full)
- Self Join
Für viele Reports brauchst du Daten aus mehreren Tabellen. Beispiel:
Um so einen Report zu bauen, verknüpfst du employee und department. Dafür nutzt du Joins.
Kartesisches Produkt/Cross Join:
Das kartesische Produkt entsteht, wenn jedes Tupel aus Relation R mit jedem Tupel aus Relation S kombiniert wird.
Ein CROSS JOIN multipliziert zwei Tabellen zu allen möglichen Paaren. Hat R i Tupel mit m Attributen und S j Tupel mit n Attributen, hat das Produkt i×j Tupel mit m+n Attributen. Beispiel mit ANSI-konformem CROSS JOIN:
SELECT empno, ename, dname
FROM employee, department;
OR
SELECT empno, ename, dname
FROM employee CROSS JOIN department;
EMPNO ENAME DNAME
---------- ---------- --------------
7369 SMITH ACCOUNTING
7499 ALLEN ACCOUNTING
7521 WARD ACCOUNTING
7566 JONES ACCOUNTING
7698 BLAKE ACCOUNTING
7369 SMITH RESEARCH
7499 ALLEN RESEARCH
7521 WARD RESEARCH
7566 JONES RESEARCH
7698 BLAKE RESEARCH
7369 SMITH SALES
EMPNO ENAME DNAME
---------- ---------- --------------
7499 ALLEN SALES
7521 WARD SALES
7566 JONES SALES
7698 BLAKE SALES
15 Zeilen ausgewählt.
Da employee 5 und department 3 Zeilen hat, liefert das Produkt 5×3=15 Zeilen. Ein kartesisches Produkt entsteht, wenn:
- keine Join-Bedingung vorhanden ist
- die Join-Bedingung ungültig oder falsch formuliert ist
Benötigst du Daten aus mehreren Tabellen, definierst du eine Join-Bedingung über gemeinsame Attribute, meist Primär- und Fremdschlüssel.
Kartesische Produkte eignen sich u. a. zum Simulieren großer Datenmengen für Tests.
Inner Join/Equijoin:
Ein Inner Join (in Oracle oft „Equijoin“) nutzt Primär-/Fremdschlüsselbeziehungen, um Tabellen zu verbinden:
SELECT ename, dname
FROM employee e,department d
WHERE e.deptno=d.deptno;
OR
SELECT ename, dname
FROM employee e
JOIN department d
ON e.deptno=d.deptno;
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
BLAKE SALES
Hier ist deptno Primärschlüssel in department und Fremdschlüssel in employee. Zusätzliche Bedingungen kannst du mit logischen Operatoren kombinieren.
Merke bei JOINS:
- Kommt derselbe Spaltenname in mehreren Tabellen vor, präge ihn mit dem Tabellennamen vor. Das erhöht generell die Klarheit.
- Zum Verbinden von n Tabellen brauchst du mindestens n−1 Join-Bedingungen. Vier Tabellen erfordern mindestens drei Joins.
Tabellenaliase: In FROM employee e, department d sind e und d Aliase. Sie helfen der Engine bei identischen Spaltennamen und sparen Tipparbeit.
Du kannst auch Nonequi-Joins formulieren, also Joins mit anderen Bedingungen als Gleichheit. Beispiel: salgrade enthält Gehaltsstufenbereiche:
Wir wollen pro Mitarbeiter die Gehaltsstufe ermitteln – die Stufen stehen in salgrade, die Gehälter in employee:
SELECT e.ename, e.sal, s.grade
FROM employee e, salgrade s
WHERE e.sal between s.losal AND s.hisal;
ENAME SAL GRADE
---------- ---------- ----------
JONES 2975 4
BLAKE 2850 4
ALLEN 1600 3
WARD 1250 2
SMITH 800 1
Beispiel: Gib Namen, Gehalt, Stufe und Abteilungsname aus. Gehalt kommt aus employee, Stufen aus salgrade, Abteilungsname aus department – also drei Tabellen joinen:
SELECT e.ename, e.sal, d.dname, s.grade FROM employee e, department d, salgrade s WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal; Output: ENAME SAL DNAME GRADE ---------- ---------- -------------- ---------- JONES 2975 RESEARCH 4 BLAKE 2850 SALES 4 ALLEN 1600 SALES 3 WARD 1250 SALES 2 SMITH 800 RESEARCH 1
Hier sind drei Tabellen beteiligt und zwei Join-Bedingungen, eine davon Nonequi.
Natural Join:
Natural Joins verbinden Tabellen automatisch über gleichnamige Spalten. Stimmen Namen, aber nicht die Datentypen, gibt es einen Fehler.
Syntax: SELECT FROM table1 NATURAL JOIN table2; SELECT FROM employee NATURAL JOIN department; Da Natural Join automatisch abgleicht, kann es mehrere gleichnamige Spalten finden, ggf. mit abweichenden Datentypen und damit Fehlern. Mit USING gibst du explizit die Join-Spalten für einen Equijoin an.
Hinweis: NATURAL JOIN und USING sind getrennte Klauseln und schließen sich gegenseitig aus.
SELECT e.ename, d.dname, e.sal FROM employee e JOIN department d USING (deptno) WHERE deptno=20; output: ENAME DNAME SAL ---------- -------------- ---------- SMITH RESEARCH 800 JONES RESEARCH 2975
Spalten im USING dürfen nirgends mit Tabellennamen oder Präfixen versehen werden. Folgendes ist falsch:
SELECT e.ename, d.dname, e.sal FROM employee e JOIN department d USING (d.deptno) WHERE d.deptno=20;
d.deptno ist falsch – es muss deptno heißen. Outer Joins:
Es gibt drei Outer Joins:
- Left Outer Join: Liefert die Ergebnisse des Inner Joins plus nicht passende Zeilen der linken Tabelle.
- Right Outer Join: Liefert die Ergebnisse des Inner Joins plus nicht passende Zeilen der rechten Tabelle.
- Full Outer Join: Liefert die Ergebnisse des Inner Joins plus nicht passende Zeilen beider Tabellen.
Schauen wir uns das an:
SELECT e.ename, s.grade FROM salgrade s LEFT OUTER JOIN employee e ON e.sal BETWEEN s.losal AND s.hisal; Output: ENAME DEPTNO DNAME ---------- ---------- -------------- SMITH 20 RESEARCH JONES 20 RESEARCH ALLEN 30 SALES WARD 30 SALES BLAKE 30 SALES.sal BETWEEN s.losal AND s.hisal; Output: ENAME DEPTNO DNAME ---------- ---------- -------------- SMITH 20 RESEARCH JONES 20 RESEARCH ALLEN 30 SALES WARD 30 SALES BLAKE 30 SALESSELECT e.ename, d.deptno, d.dname
FROM employee e RIGHT OUTER JOIN department d
ON e.deptno=d.deptno;
output:
ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
BLAKE 30 SALES
10 ACCOUNTINGSELECT e.ename, d.deptno, d.dname
FROM employee e FULL OUTER JOIN department d
ON e.deptno=d.deptno;
Output:
ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
BLAKE 30 SALES
10 ACCOUNTING
In department gibt es eine nicht passende Zeile für deptno 10; in employee keine. In salgrade ist die Stufe 5 ungematcht.
Self-Join:
Manchmal verknüpfst du eine Tabelle mit sich selbst. In employee stehen auch Manager. Willst du Mitarbeitende und ihre Manager ermitteln, brauchst du einen Self-Join:
SELECT e.ename as Employee, m.ename as Manager
FROM employee e, employee m
WHERE e.mgr=m.empno;
EMPLOYEE MANAGER
---------- ----------
ALLEN BLAKE
WARD BLAKE
Subqueries einsetzen
Eine Subquery ist eine Abfrage innerhalb einer Abfrage. Sie hilft, komplexe Probleme zu zerlegen. Du kannst Subqueries schachteln und in folgenden Klauseln einsetzen:
- WHERE
- FROM
- HAVING
Beispiel: Finde Mitarbeitende mit höherem Gehalt als James. Vorgehen:
- Finde das Gehalt von James
- Vergleiche dieses Gehalt mit allen Mitarbeitenden
Das löst du mit einer Subquery. Die innere Abfrage läuft einmal vor der äußeren:
SELECT empno, ename FROM employee WHERE sal>(SELECT sal from employee where ename='JAMES');
Arten von Subqueries:
-
Einzeilige Subquery: Liefert genau eine Zeile.
-
Mehrzeilige Subquery: Liefert mehrere Zeilen.
Merke:
- Setze Subqueries in Klammern.
- Platziere Subqueries rechts vom Vergleichsoperator.
- Nutze einzeilige Operatoren (>, <, >=, <=, <>) für einzeilige Subqueries.
- Nutze Mehrzeilen-Operatoren (IN, ANY, ALL) für mehrzeilige Subqueries.
Einzeilige Subquery:
Finde Namen von Mitarbeitenden mit derselben Stellenbezeichnung wie empno 7521:
SELECT ename, job
FROM employee
WHERE job=(SELECT job FROM employee WHERE empno=7521);
ENAME JOB
---------- ---------
ALLEN SALESMAN
WARD SALESMAN
Ermittle die Maximalgehälter der Abteilungen, die mindestens so hoch sind wie das Maximum in Abteilung 20:
SELECT deptno, max(sal)
FROM employee
GROUP BY deptno
HAVING max(sal)>=(SELECT max(sal) FROM employee WHERE deptno=20);
DEPTNO MAX(SAL)
---------- ----------
20 2975
Mehrzeilige Subquery:
Finde Mitarbeitende, deren Gehalt einem der Gehälter von MANAGERs entspricht. Die Subquery kann mehrere Zeilen liefern:
SELECT ename, job, sal
FROM employee
WHERE sal IN (SELECT sal FROM employee WHERE job='MANAGER');
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
Steht eine Subquery in FROM, wirkt sie wie eine temporäre Tabelle (View). Beispiel:
SELECT e.ename, e.job, e.sal
FROM employee e, (SELECT deptno FROM department WHERE loc='DALLAS') d
WHERE e.deptno=d.deptno;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
JONES MANAGER 2975
SET-Operatoren nutzen
Mit SET-Operatoren kombinierst du Ergebnisse mehrerer Abfragen in einem Resultset. Grundlage ist die Mengenlehre: UNION, MINUS, INTERSECT. So optimierst du Abfragen. In diesem Tutorial sehen wir:
Info: Abfragen mit SET-Operatoren heißen zusammengesetzte Anweisungen (compound statements).
- UNION und UNION ALL
- INTERSECT
- EXCEPT (SQL-Standard) und MINUS (Oracle-spezifisch)
UNION
Gegeben zwei Relationen R und S: UNION gibt alle Zeilen aus R und S zurück und entfernt Duplikate. Maximal r+s Zeilen.
Wähle alle Abteilungsnamen, inklusive der Abteilungen von Mitarbeitenden, die vor dem 23-OCT-1999 eingestellt wurden:
SELECT dname
FROM department
UNION
SELECT dname
FROM department, employee
WHERE department.deptno=employee.deptno AND employee.hiredate<to_date('23-OCT-1999');
DNAME
--------------
ACCOUNTING
OPERATIONS
RESEARCH
SALES
Merke zu UNION:
- Anzahl und Datentypen der ausgewählten Spalten müssen in allen SELECTs übereinstimmen.
- Spaltennamen müssen nicht identisch sein.
- Die Ausgabe wird nach der ersten SELECT-Spalte aufsteigend sortiert.
- NULL wird bei der Duplikatprüfung nicht ignoriert.
UNION ALL kombiniert Ergebnisse, entfernt aber keine Duplikate; DISTINCT ist hier nicht möglich. Angenommen, es gibt eine weitere Tabelle emp mit Mitarbeitenden ab dem Jahr 2000. Hole alle empno, ename und job aus employee und emp:
SELECT empno, ename, job
FROM employee
UNION ALL
SELECT empno, ename, job
FROM emp;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7698 BLAKE MANAGER
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
EMPNO ENAME JOB
---------- ---------- ---------
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
INTERSECT
INTERSECT liefert die Schnittmenge – gemeinsame Zeilen in R und S:
SELECT empno, ename, job
FROM employee
INTERSECT
SELECT empno, ename, job
FROM emp;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7698 BLAKE MANAGER
Merke zu INTERSECT:
- Anzahl und Datentypen der Spalten müssen übereinstimmen.
- Spaltennamen müssen nicht gleich sein.
- NULL-Werte werden nicht ignoriert.
EXCEPT und MINUS:
EXCEPT (in SQL Server) und MINUS (Oracle) leisten dasselbe: Sie geben alle unterschiedlichen Zeilen aus der ersten Abfrage zurück, die nicht in der zweiten vorkommen. Beispiel mit folgender Produkttabelle:
Finde Produkte mit Menge zwischen 1 und 100, aber nicht zwischen 50 und 75. In SQL Server nutzt du EXCEPT, in Oracle MINUS.
SELECT prod_name, qty FROM products WHERE qty BETWEEN 1 AND 100 EXCEPT SELECT prod_name, qty FROM products WHERE qty BETWEEN 50 AND 75;
Für Oracle:
SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 1 AND 100
MINUS
SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 50 AND 75;
PROD_NAME QTY
------------- ----------
COLGATE 1
SENSODYNE 100
SENSODYNE TOOTHBRUSH 30
Merke zu EXCEPT/MINUS:
- Anzahl und Datentypen der ausgewählten Spalten müssen in allen SELECTs identisch sein.
- Spaltennamen müssen nicht gleich sein.
- Alle Spalten in der WHERE-Klausel müssen in der SELECT-Klausel stehen, damit MINUS funktioniert.
Glückwunsch!
Du hast das Tutorial abgeschlossen. Du hast zentrale SQL-Konzepte gelernt, die dir auf deinem Weg in der Data Science helfen. SQL ist das Rückgrat vieler Reports. Du kennst jetzt die Grundlagen von Datenbanken und SQL, gängige Datentypen, Funktionen für Formatierung, Aggregation und Zusammenfassungen – und wie du Daten aus mehreren Tabellen passend zu deinen Anforderungen zusammenführst. Dieses Tutorial richtet sich speziell an Lernende aus der Data Science und hilft dir nicht nur bei relationalen Systemen, sondern erleichtert auch den Einstieg in NoSQL und den Einsatz von SQL-Skills im Big-Data-Umfeld.
Wenn du noch tiefer einsteigen willst, starte mit DataCamps kostenlosem Kurs Intro to SQL for Data Science.
Referenzen
Themen
