SQL-DQL: Eine Einführung#
In diesem Tutorium setzen wir uns mit der Data Query Language von SQL auseinander, also dem Teil von SQL, welcher es uns ermöglicht, Anfragen auf unserer Datenbank und dessen Daten auszuführen und diese Daten dann genauer zu interpretieren. Wir beginnen damit, dass wir unsere Datenbank öffnen. Und uns einen Überblick über diese verschaffen, indem wir die sqlite_master Relation ausgeben lassen. Hier stellen wir somit schon unsere Anfrage an unsere Datenbank. Mit der SELECT-Klausel sagen wir, welche Daten, spezifisch welche Spalten wir haben wollen und mit der FROM-Klausel spezifizieren wir, von wo wir diese Daten beziehen wollen. Der * im SELECT gibt dabei an, dass wir alle Spalten selektieren wollen.
import duckdb
con = duckdb.connect(database="../resources/06_sql_dql/uni_ue.duckdb", read_only=False)
con.sql("SELECT * FROM sqlite_master").to_df()
| type | name | tbl_name | rootpage | sql | |
|---|---|---|---|---|---|
| 0 | table | assistenten | assistenten | 0 | CREATE TABLE assistenten(persnr INTEGER PRIMAR... |
| 1 | table | hoeren | hoeren | 0 | CREATE TABLE hoeren(matrnr INTEGER, vorlnr INT... |
| 2 | table | professoren | professoren | 0 | CREATE TABLE professoren(persnr INTEGER PRIMAR... |
| 3 | table | pruefen | pruefen | 0 | CREATE TABLE pruefen(matrnr INTEGER, vorlnr IN... |
| 4 | table | studenten | studenten | 0 | CREATE TABLE studenten(matrnr INTEGER PRIMARY ... |
| 5 | table | voraussetzen | voraussetzen | 0 | CREATE TABLE voraussetzen(vorgaenger INTEGER, ... |
| 6 | table | vorlesungen | vorlesungen | 0 | CREATE TABLE vorlesungen(vorlnr INTEGER PRIMAR... |
Das Ergebnis unserer Anfrage zeigt, dass wir im Folgenden erneut mit dem Uni-Datenbankschema arbeiten werden. Zusätzlich zum Ergebnis dieser Anfrage geben wir Ihnen aber auch einen konkreten Überblick über diese Relationen. Die Relationen und ihre konkreten Instanzen sehen wie folgt aus:
from IPython.display import display_html
css = "style='display:inline; margin: 0px 20px 0px 0px; vertical-align: top;'"
display_html(
con.sql("select * from professoren")
.to_df()
.style.set_table_attributes(css)
.set_caption("<strong>Professoren</strong>")
._repr_html_()
+ con.sql("select * from studenten")
.to_df()
.style.set_table_attributes(css)
.set_caption("<strong>Studenten</strong>")
._repr_html_()
+ con.sql("select * from vorlesungen")
.to_df()
.style.set_table_attributes(css)
.set_caption("<strong>Vorlesungen</strong>")
._repr_html_()
+ con.sql("select * from voraussetzen")
.to_df()
.style.set_table_attributes(css)
.set_caption("<strong>Voraussetzungen</strong>")
._repr_html_()
+ con.sql("select * from hoeren")
.to_df()
.style.set_table_attributes(css)
.set_caption("<strong>Hören</strong>")
._repr_html_()
+ con.sql("select * from pruefen")
.to_df()
.style.set_table_attributes(css)
.set_caption("<strong>Prüfen</strong>")
._repr_html_()
+ con.sql("select * from assistenten")
.to_df()
.style.set_table_attributes(css)
.set_caption("<strong>Assistenten</strong>")
._repr_html_(),
raw=True,
)
| persnr | name | rang | raum | |
|---|---|---|---|---|
| 0 | 2125 | Sokrates | C4 | 226 |
| 1 | 2126 | Russel | C4 | 232 |
| 2 | 2127 | Kopernikus | C3 | 310 |
| 3 | 2133 | Popper | C3 | 52 |
| 4 | 2134 | Augustinus | C3 | 309 |
| 5 | 2136 | Curie | C4 | 36 |
| 6 | 2137 | Kant | C4 | 7 |
| matrnr | name | semester | |
|---|---|---|---|
| 0 | 24002 | Xenokrates | 18 |
| 1 | 25403 | Jonas | 12 |
| 2 | 26120 | Fichte | 10 |
| 3 | 26830 | Aristoxenos | 8 |
| 4 | 27550 | Schopenhauer | 6 |
| 5 | 28106 | Carnap | 3 |
| 6 | 29120 | Theophrastos | 2 |
| 7 | 29555 | Feuerbach | 2 |
| vorlnr | titel | sws | gelesenvon | |
|---|---|---|---|---|
| 0 | 4052 | Logik | 4 | 2125 |
| 1 | 4630 | Die 3 Kritiken | 4 | 2137 |
| 2 | 5001 | Grundzuege | 4 | 2137 |
| 3 | 5022 | Glaube und Wissen | 2 | 2134 |
| 4 | 5041 | Ethik | 4 | 2125 |
| 5 | 5043 | Erkenntnistheorie | 3 | 2126 |
| 6 | 5049 | Maeeutik | 2 | 2125 |
| 7 | 5052 | Wissenschaftstheorie | 3 | 2126 |
| 8 | 5216 | Bioethik | 2 | 2126 |
| 9 | 5259 | Der Wiener Kreis | 2 | 2133 |
| vorgaenger | nachfolger | |
|---|---|---|
| 0 | 5001 | 5041 |
| 1 | 5001 | 5043 |
| 2 | 5001 | 5049 |
| 3 | 5041 | 5216 |
| 4 | 5043 | 5052 |
| 5 | 5041 | 5052 |
| 6 | 5052 | 5259 |
| matrnr | vorlnr | |
|---|---|---|
| 0 | 26120 | 5001 |
| 1 | 27550 | 5001 |
| 2 | 27550 | 4052 |
| 3 | 28106 | 5041 |
| 4 | 28106 | 5052 |
| 5 | 28106 | 5216 |
| 6 | 28106 | 5259 |
| 7 | 29120 | 5001 |
| 8 | 29120 | 5041 |
| 9 | 29120 | 5049 |
| 10 | 29555 | 5022 |
| 11 | 25403 | 5022 |
| 12 | 29555 | 5001 |
| matrnr | vorlnr | persnr | note | |
|---|---|---|---|---|
| 0 | 28106 | 5001 | 2126 | 1.000000 |
| 1 | 25403 | 5041 | 2125 | 2.000000 |
| 2 | 27550 | 4630 | 2137 | 2.000000 |
| persnr | name | fachgebiet | boss | |
|---|---|---|---|---|
| 0 | 3002 | Platon | Ideenlehre | 2125 |
| 1 | 3003 | Aristoteles | Syllogistik | 2125 |
| 2 | 3004 | Wittgenstein | Sprachtheorie | 2126 |
| 3 | 3005 | Rhetikus | Planetenbewegung | 2127 |
| 4 | 3006 | Newton | Keplersche Gesetze | 2127 |
| 5 | 3007 | Spinoza | Gott und Natur | 2134 |
Einführung DQL#
Zuvor haben wir schon besprochen, was die Funktionalität der SELECT- und FROM-Klauseln sind. Nun wählen wir zuerst alle Daten über Professoren (welche direkt in der Tabelle stehen) aus und demonstrieren weitere Möglichkeiten, die innerhalb dieser Klauseln bestehen.
con.sql("SELECT * FROM professoren;")
┌────────┬────────────┬─────────┬───────┐
│ persnr │ name │ rang │ raum │
│ int32 │ varchar │ varchar │ int32 │
├────────┼────────────┼─────────┼───────┤
│ 2125 │ Sokrates │ C4 │ 226 │
│ 2126 │ Russel │ C4 │ 232 │
│ 2127 │ Kopernikus │ C3 │ 310 │
│ 2133 │ Popper │ C3 │ 52 │
│ 2134 │ Augustinus │ C3 │ 309 │
│ 2136 │ Curie │ C4 │ 36 │
│ 2137 │ Kant │ C4 │ 7 │
└────────┴────────────┴─────────┴───────┘
Nun wollenn wir vielleicht aber nur den Namen der Professoren wissen, als auch das Büro (Raumnummer) der Professoren. Wenn wir nun ein Attribut umbennen wollen, dann können wir dies innerhalb der SELECT-Klausel mittels dem “keyword” AS machen. Hinweis: Groß- bzw. Kleinschreibung wird in SQL ignoriert, aber oftmals werden für die Lesbarkeit die Bausteine des SQL Dialekts Großgeschrieben und die Informationen des Schemas (Tabellen, Attribute, etc.) kleingeschrieben.
con.sql("SELECT name AS ProfessorenName, RAUM FROM PROFESSOREN;")
┌─────────────────┬───────┐
│ ProfessorenName │ raum │
│ varchar │ int32 │
├─────────────────┼───────┤
│ Sokrates │ 226 │
│ Russel │ 232 │
│ Kopernikus │ 310 │
│ Popper │ 52 │
│ Augustinus │ 309 │
│ Curie │ 36 │
│ Kant │ 7 │
└─────────────────┴───────┘
Des Weiteren können in der SELECT-Klausel die ausgegebenen Tupel auch arithmetisch verändert werden. Sollte ein Professor zum Beispiel entscheiden, dass eine Prüfung zu schlecht ausgefallen ist und entsprechend den Noten aller Studierenden verändert abfragen möchte, so könnte dies in der SELECT-Klausel angegeben werden.
Hinweis: Da unsere Datenbank nur ein minimales Beispiel ist, mit insgesamt 3 Prüfungsergebnissen, fragen wir hier alle Prüfungsergebnisse ab und geben uns diese um einen Notenpunkt verbessert aus.
con.sql("SELECT matrnr, vorlnr, persnr, note - 0.1 FROM pruefen;")
┌────────┬────────┬────────┬──────────────┐
│ matrnr │ vorlnr │ persnr │ (note - 0.1) │
│ int32 │ int32 │ int32 │ decimal(3,1) │
├────────┼────────┼────────┼──────────────┤
│ 28106 │ 5001 │ 2126 │ 0.9 │
│ 25403 │ 5041 │ 2125 │ 1.9 │
│ 27550 │ 4630 │ 2137 │ 1.9 │
└────────┴────────┴────────┴──────────────┘
Zusätzlich zu einfachen arithmetischen Operationen pro Instanz können wir auch Aggregationsfunktionen im SELECT benutzen, z.B. um die Anzahl an Studierenden in unserer Datenbank zu bestimmen.
con.sql("SELECT COUNT(persnr) AS AnzahlProfessoren FROM professoren;")
┌───────────────────┐
│ AnzahlProfessoren │
│ int64 │
├───────────────────┤
│ 7 │
└───────────────────┘
Wenn wir nun mehrere Tabellen in der FROM-Klausel separiert durch Kommata angeben, so wird dies als Kreuzprodukt interpretiert. Es werden also alle Instanzen der einen Tabelle mit allen Instanzen der anderen Tabelle kombiniert.
con.sql("SELECT * FROM studenten, pruefen;")
┌────────┬──────────────┬──────────┬────────┬────────┬────────┬──────────────┐
│ matrnr │ name │ semester │ matrnr │ vorlnr │ persnr │ note │
│ int32 │ varchar │ int32 │ int32 │ int32 │ int32 │ decimal(2,1) │
├────────┼──────────────┼──────────┼────────┼────────┼────────┼──────────────┤
│ 24002 │ Xenokrates │ 18 │ 28106 │ 5001 │ 2126 │ 1.0 │
│ 25403 │ Jonas │ 12 │ 28106 │ 5001 │ 2126 │ 1.0 │
│ 26120 │ Fichte │ 10 │ 28106 │ 5001 │ 2126 │ 1.0 │
│ 26830 │ Aristoxenos │ 8 │ 28106 │ 5001 │ 2126 │ 1.0 │
│ 27550 │ Schopenhauer │ 6 │ 28106 │ 5001 │ 2126 │ 1.0 │
│ 28106 │ Carnap │ 3 │ 28106 │ 5001 │ 2126 │ 1.0 │
│ 29120 │ Theophrastos │ 2 │ 28106 │ 5001 │ 2126 │ 1.0 │
│ 29555 │ Feuerbach │ 2 │ 28106 │ 5001 │ 2126 │ 1.0 │
│ 24002 │ Xenokrates │ 18 │ 25403 │ 5041 │ 2125 │ 2.0 │
│ 25403 │ Jonas │ 12 │ 25403 │ 5041 │ 2125 │ 2.0 │
│ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │
│ 29120 │ Theophrastos │ 2 │ 25403 │ 5041 │ 2125 │ 2.0 │
│ 29555 │ Feuerbach │ 2 │ 25403 │ 5041 │ 2125 │ 2.0 │
│ 24002 │ Xenokrates │ 18 │ 27550 │ 4630 │ 2137 │ 2.0 │
│ 25403 │ Jonas │ 12 │ 27550 │ 4630 │ 2137 │ 2.0 │
│ 26120 │ Fichte │ 10 │ 27550 │ 4630 │ 2137 │ 2.0 │
│ 26830 │ Aristoxenos │ 8 │ 27550 │ 4630 │ 2137 │ 2.0 │
│ 27550 │ Schopenhauer │ 6 │ 27550 │ 4630 │ 2137 │ 2.0 │
│ 28106 │ Carnap │ 3 │ 27550 │ 4630 │ 2137 │ 2.0 │
│ 29120 │ Theophrastos │ 2 │ 27550 │ 4630 │ 2137 │ 2.0 │
│ 29555 │ Feuerbach │ 2 │ 27550 │ 4630 │ 2137 │ 2.0 │
├────────┴──────────────┴──────────┴────────┴────────┴────────┴──────────────┤
│ 24 rows (20 shown) 7 columns │
└────────────────────────────────────────────────────────────────────────────┘
Oftmals wollen wir jedoch kein Kreuzprodukt verwenden, sondern viel eher einen Join. Wobei ein Join ein Kreuzproduk mit einer Bedingung bzw. einer Einschränkung ist. Diese Bedingung kann durch ein ON signalisiert werden. So erhalten wir in der folgenden Anfrage nur Instanzen von Studierenden, die (aktuell) Prüfungen belegt haben.
con.sql("SELECT * FROM studenten JOIN pruefen ON studenten.matrnr = pruefen.matrnr;")
┌────────┬──────────────┬──────────┬────────┬────────┬────────┬──────────────┐
│ matrnr │ name │ semester │ matrnr │ vorlnr │ persnr │ note │
│ int32 │ varchar │ int32 │ int32 │ int32 │ int32 │ decimal(2,1) │
├────────┼──────────────┼──────────┼────────┼────────┼────────┼──────────────┤
│ 25403 │ Jonas │ 12 │ 25403 │ 5041 │ 2125 │ 2.0 │
│ 27550 │ Schopenhauer │ 6 │ 27550 │ 4630 │ 2137 │ 2.0 │
│ 28106 │ Carnap │ 3 │ 28106 │ 5001 │ 2126 │ 1.0 │
└────────┴──────────────┴──────────┴────────┴────────┴────────┴──────────────┘
Wie Sie im vorletzten Ergebnis sehen, kann es auch zu Dopplungen von Attributnamen in unserem Ergebnis kommen. Wenn wir nun eines dieser Attribute verwenden wollen, so müssen wir konkret angeben, von welcher Tabelle dieses Attribut stammt oder wir benennen eines der Attribute mit dem AS “keyword” um, wenn dies in einer Subanfrage zuvor möglich ist. Alternativ können Tabellen auch durch eine Variable abgekürzt werden; in der folgenden Anfrage ist dies der Fall bei der Tabelle pruefen, welche durch p abgekürzt wird.
con.sql("SELECT stud.matrnr, name, semester, vorlnr, persnr FROM studenten stud, pruefen;")
┌────────┬──────────────┬──────────┬────────┬────────┐
│ matrnr │ name │ semester │ vorlnr │ persnr │
│ int32 │ varchar │ int32 │ int32 │ int32 │
├────────┼──────────────┼──────────┼────────┼────────┤
│ 24002 │ Xenokrates │ 18 │ 5001 │ 2126 │
│ 25403 │ Jonas │ 12 │ 5001 │ 2126 │
│ 26120 │ Fichte │ 10 │ 5001 │ 2126 │
│ 26830 │ Aristoxenos │ 8 │ 5001 │ 2126 │
│ 27550 │ Schopenhauer │ 6 │ 5001 │ 2126 │
│ 28106 │ Carnap │ 3 │ 5001 │ 2126 │
│ 29120 │ Theophrastos │ 2 │ 5001 │ 2126 │
│ 29555 │ Feuerbach │ 2 │ 5001 │ 2126 │
│ 24002 │ Xenokrates │ 18 │ 5041 │ 2125 │
│ 25403 │ Jonas │ 12 │ 5041 │ 2125 │
│ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │
│ 29120 │ Theophrastos │ 2 │ 5041 │ 2125 │
│ 29555 │ Feuerbach │ 2 │ 5041 │ 2125 │
│ 24002 │ Xenokrates │ 18 │ 4630 │ 2137 │
│ 25403 │ Jonas │ 12 │ 4630 │ 2137 │
│ 26120 │ Fichte │ 10 │ 4630 │ 2137 │
│ 26830 │ Aristoxenos │ 8 │ 4630 │ 2137 │
│ 27550 │ Schopenhauer │ 6 │ 4630 │ 2137 │
│ 28106 │ Carnap │ 3 │ 4630 │ 2137 │
│ 29120 │ Theophrastos │ 2 │ 4630 │ 2137 │
│ 29555 │ Feuerbach │ 2 │ 4630 │ 2137 │
├────────┴──────────────┴──────────┴────────┴────────┤
│ 24 rows (20 shown) 5 columns │
└────────────────────────────────────────────────────┘
Teils kann es auch zu Duplikaten bei Instanzen in unserer Ergebnisrelation kommen. Dies ist der wesentliche Unterschied zur Relationalen Algebra, wo das Ergebnis immer eine Menge ist, also keine Duplikate beinhaltet. Wenn wir trotzdem in SQL Duplikate eliminieren wollen, dann können wir das DISTINCT “keyword” verwenden.
con.sql("SELECT DISTINCT studenten.matrnr FROM studenten, pruefen;")
┌────────┐
│ matrnr │
│ int32 │
├────────┤
│ 24002 │
│ 28106 │
│ 29120 │
│ 29555 │
│ 25403 │
│ 26120 │
│ 26830 │
│ 27550 │
└────────┘
Ohne das Distinct sähe das Ergebnis wie folgt aus:
con.sql("SELECT studenten.matrnr FROM studenten, pruefen;")
┌────────────┐
│ matrnr │
│ int32 │
├────────────┤
│ 24002 │
│ 25403 │
│ 26120 │
│ 26830 │
│ 27550 │
│ 28106 │
│ 29120 │
│ 29555 │
│ 24002 │
│ 25403 │
│ · │
│ · │
│ · │
│ 29120 │
│ 29555 │
│ 24002 │
│ 25403 │
│ 26120 │
│ 26830 │
│ 27550 │
│ 28106 │
│ 29120 │
│ 29555 │
├────────────┤
│ 24 rows │
│ (20 shown) │
└────────────┘
Als nächstes führen wir die WHERE-Klausel ein. Die WHERE-Klausel kann verwendet werden, um Einschränkungen bezüglich der Daten anzugeben, welche ausgegeben werden sollen. Im Folgenden selektieren wir z.B. Vorlesungen anhand der persnr bzw. dem Attribut gelesenvon, da wir die Vorlesungen dieses Professors womöglich als besonders gut empfinden.
con.sql("SELECT * FROM Vorlesungen WHERE gelesenvon = 2125;")
┌────────┬──────────┬───────┬────────────┐
│ vorlnr │ titel │ sws │ gelesenvon │
│ int32 │ varchar │ int32 │ int32 │
├────────┼──────────┼───────┼────────────┤
│ 4052 │ Logik │ 4 │ 2125 │
│ 5041 │ Ethik │ 4 │ 2125 │
│ 5049 │ Maeeutik │ 2 │ 2125 │
└────────┴──────────┴───────┴────────────┘
Mittels der WHERE-Klausel können wir ein Join auch umschreiben als ein Kreuzprodukt mit einer Bedingung im WHERE. Des Weiteren beginnen wir nun, wo wir mehrere Bausteine des SQL-DQL Syntax kennengelernt haben, bei den unterschiedlichen Klauseln Zeilenumbrüche einzuführen, um die Lesbarkeit unserer Anfragen zu erhöhen. Damit diese Zeilenumbrüche nicht als separate Zeilen an Code von Python angesehen werden, müssen Sie die Anfragen in dreifache Anführungszeichen einwickeln.
con.sql(
"""
SELECT *
FROM studenten, pruefen
WHERE studenten.matrnr = pruefen.matrnr;
"""
)
┌────────┬──────────────┬──────────┬────────┬────────┬────────┬──────────────┐
│ matrnr │ name │ semester │ matrnr │ vorlnr │ persnr │ note │
│ int32 │ varchar │ int32 │ int32 │ int32 │ int32 │ decimal(2,1) │
├────────┼──────────────┼──────────┼────────┼────────┼────────┼──────────────┤
│ 25403 │ Jonas │ 12 │ 25403 │ 5041 │ 2125 │ 2.0 │
│ 27550 │ Schopenhauer │ 6 │ 27550 │ 4630 │ 2137 │ 2.0 │
│ 28106 │ Carnap │ 3 │ 28106 │ 5001 │ 2126 │ 1.0 │
└────────┴──────────────┴──────────┴────────┴────────┴────────┴──────────────┘
Genauso wie in anderen Programmiersprachen haben Sie auch logische Operationen im SQL-DQL Dialekt. So könnten wir z.B. nur die Noten von Jonas und Carnap selektieren.
con.sql(
"""
SELECT *
FROM studenten, pruefen
WHERE studenten.matrnr = pruefen.matrnr AND (name = 'Jonas' OR name = 'Carnap');
"""
)
┌────────┬─────────┬──────────┬────────┬────────┬────────┬──────────────┐
│ matrnr │ name │ semester │ matrnr │ vorlnr │ persnr │ note │
│ int32 │ varchar │ int32 │ int32 │ int32 │ int32 │ decimal(2,1) │
├────────┼─────────┼──────────┼────────┼────────┼────────┼──────────────┤
│ 28106 │ Carnap │ 3 │ 28106 │ 5001 │ 2126 │ 1.0 │
│ 25403 │ Jonas │ 12 │ 25403 │ 5041 │ 2125 │ 2.0 │
└────────┴─────────┴──────────┴────────┴────────┴────────┴──────────────┘
Wie zuvor erwähnt ist einer der wesentlichen Unterschiede zwischen SQL und der Relationalen Algebra, dass SQL auf Multimengen arbeitet, dass es also Duplikate geben kann, während die Relationale Algebra auf Mengen arbeitet, wo keine Duplikate existieren. In SQL gibt es jedoch auch die typischen Mengenoperationen Vereinigung (UNION), Schnittmenge (INTERSECT) und Differenz (EXCEPT), welche dann auch direkt Ergebnisse in Form von Mengen produzieren. Ein DISTINCT ist nach diesen Operationen also explizit nicht notwendig. Sollten wir keine Eliminierung der Duplikate wünschen, so können wir diese Mengenoperationen mit dem ALL “keyword” verbinden.
Hinweis: Bei der Vereinigung und der Schnittmenge ist es auch wichtig, dass die beiden Schemata der Daten übereinstimmen.
con.sql(
"""
SELECT *
FROM studenten, pruefen
WHERE studenten.matrnr = pruefen.matrnr AND name = 'Jonas'
UNION
SELECT *
FROM studenten, pruefen
WHERE studenten.matrnr = pruefen.matrnr AND name = 'Carnap';
"""
)
┌────────┬─────────┬──────────┬────────┬────────┬────────┬──────────────┐
│ matrnr │ name │ semester │ matrnr │ vorlnr │ persnr │ note │
│ int32 │ varchar │ int32 │ int32 │ int32 │ int32 │ decimal(2,1) │
├────────┼─────────┼──────────┼────────┼────────┼────────┼──────────────┤
│ 28106 │ Carnap │ 3 │ 28106 │ 5001 │ 2126 │ 1.0 │
│ 25403 │ Jonas │ 12 │ 25403 │ 5041 │ 2125 │ 2.0 │
└────────┴─────────┴──────────┴────────┴────────┴────────┴──────────────┘
Als Nächstes führen wir die GROUP BY-Klausel ein, welche es uns ermöglicht, nach einem oder mehreren Attributen Gruppen zu erstellen. Oftmals ist dies mit Aggregations-Funktionen verbunden. So bestimmen wir im Folgenden z.B. die Anzahl der Studierenden in jeder Vorlesung. Dies schränkt konkret auch die Attribute ein, welche wir in der SELECT-Klausel angeben dürfen, da manche Informationen nicht mehr klar zu den zusammengefassten Daten zuzuweisen sind. Spezifisch bedeutet dies, dass bei einer gruppierten Aggregation nur die Gruppierungsattribute im SELECT zusätzlich zu den gruppierten Aggregationen auftauchen dürfen. Die matrnr im SELECT würde z.B. konkret zu einem Fehler führen, da bei der Vorlesung 5001 vier unterschiedliche Studierende teilnehmen und somit kein einziger Studierender bzw. keine einzige matrnr zu dem Wert 4 zuzuweisen ist.
Eine weitere Möglichkeit, um komplexe SQL-Anfragen lesbarer zu machen, ist die Verwendung von “Common Table Expressions (CTEs)”. CTEs erlauben es, SQL-Anfragen Namen zu geben. Diese Namen können dann in späteren Anfragen verwendet werden. Syntax Beispiel:
WITH named_query AS (
SELECT * FROM some_table WHERE some_condition
)
SELECT some_column FROM named_query;
Es können mehrere CTEs erstellt werden, wenn diese durch ein Komma getrennt werden. Die Dokumentation zu CTEs in DuckDB befindet sich hier: https://duckdb.org/docs/sql/query_syntax/with.html
Beispiel#
Das folgende Beispiel ist etwas komplexer als die vorherigen Anfragen, die wir bisher gesehen haben. Insbesondere dadurch, dass wir verschachtelte Anfragen verwenden - also das Ergebnis einer Anfrage als Eingabe für eine andere nutzen.
SELECT *
FROM pruefen
JOIN (
SELECT *
FROM Studenten
WHERE semester < (
SELECT avg(Semester)
FROM Studenten
)
) AS unterdurchschnittlich ON pruefen.matrnr = unterdurchschnittlich.matrnr;
Die oben und untenstehenden Anfragen sind äquivalent.
With durchschnitt AS (SELECT avg(Semester) FROM Studenten),
unterdurchschnittlich AS (SELECT * FROM Studenten WHERE semester < (SELECT * FROM durchschnitt))
SELECT * FROM pruefen JOIN unterdurchschnittlich ON pruefen.matrnr = unterdurchschnittlich.matrnr;
con.sql(
"""
SELECT vorlnr, COUNT(matrnr) AS AnzahlStudierende
FROM hoeren
GROUP BY vorlnr;
"""
)
┌────────┬───────────────────┐
│ vorlnr │ AnzahlStudierende │
│ int32 │ int64 │
├────────┼───────────────────┤
│ 4052 │ 1 │
│ 5001 │ 4 │
│ 5022 │ 2 │
│ 5041 │ 2 │
│ 5049 │ 1 │
│ 5052 │ 1 │
│ 5216 │ 1 │
│ 5259 │ 1 │
└────────┴───────────────────┘
Wenn wir unsere Ergebnisrelation bzgl. einer Gruppierung einschränken wollen, so gibt es dafür extra die HAVING-Klausel. So schränken wir im Folgenden unser vorheriges Ergebnis ein, indem wir nur Vorlesungen aussuchen, an denen mehr als eine studierende Person teilnimmt.
con.sql(
"""
SELECT vorlnr, COUNT(matrnr) AS AnzahlStudierende
FROM hoeren
GROUP BY vorlnr
HAVING COUNT(matrnr) > 1;
"""
)
┌────────┬───────────────────┐
│ vorlnr │ AnzahlStudierende │
│ int32 │ int64 │
├────────┼───────────────────┤
│ 5001 │ 4 │
│ 5022 │ 2 │
│ 5041 │ 2 │
└────────┴───────────────────┘
Wenn wir generell unsere Ergebnisrelation in der Anzahl der Instanzen beschränken wollen, verwenden wir LIMIT.
con.sql(
"""
SELECT vorlnr, COUNT(matrnr) AS AnzahlStudierende
FROM hoeren
GROUP BY vorlnr
HAVING COUNT(matrnr) > 1
LIMIT 1;
"""
)
┌────────┬───────────────────┐
│ vorlnr │ AnzahlStudierende │
│ int32 │ int64 │
├────────┼───────────────────┤
│ 5001 │ 4 │
└────────┴───────────────────┘
Zuletzt führen wir die EXISTS UND IN Operatoren ein, welche auch mit dem logischen NOTverknüpft werden können. Der EXISTS Operator prüft, ob mindestens eine Instanz existiert, während der IN Operator prüft, ob ein gewisser Wert in einer Menge vorkommt. Im Folgenden sehen Sie zwei Beispiele, in denen wir diese Operatoren verwenden, um sowohl die Professoren und Professor*innen auszusuchen, welche bzw. welche keine Vorlesungen halten.
con.sql(
"""
SELECT *
FROM professoren p
WHERE EXISTS (SELECT *
FROM vorlesungen v
WHERE p.persnr = v.gelesenvon);
"""
)
┌────────┬────────────┬─────────┬───────┐
│ persnr │ name │ rang │ raum │
│ int32 │ varchar │ varchar │ int32 │
├────────┼────────────┼─────────┼───────┤
│ 2125 │ Sokrates │ C4 │ 226 │
│ 2126 │ Russel │ C4 │ 232 │
│ 2133 │ Popper │ C3 │ 52 │
│ 2134 │ Augustinus │ C3 │ 309 │
│ 2137 │ Kant │ C4 │ 7 │
└────────┴────────────┴─────────┴───────┘
con.sql(
"""
SELECT *
FROM professoren p
WHERE p.persnr NOT IN (SELECT v.gelesenvon
FROM vorlesungen v);
"""
)
┌────────┬────────────┬─────────┬───────┐
│ persnr │ name │ rang │ raum │
│ int32 │ varchar │ varchar │ int32 │
├────────┼────────────┼─────────┼───────┤
│ 2127 │ Kopernikus │ C3 │ 310 │
│ 2136 │ Curie │ C4 │ 36 │
└────────┴────────────┴─────────┴───────┘
Zuletzt schließen wir die Datenbankverbindung wieder.
con.close()