{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "# SQL-DQL: Eine Einführung\n",
    "\n",
    "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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import duckdb\n",
    "\n",
    "con = duckdb.connect(database=\"../resources/06_sql_dql/uni_ue.duckdb\", read_only=False)\n",
    "\n",
    "con.sql(\"SELECT * FROM sqlite_master\").to_df()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "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: \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from IPython.display import display_html\n",
    "\n",
    "css = \"style='display:inline; margin: 0px 20px 0px 0px; vertical-align: top;'\"\n",
    "\n",
    "\n",
    "display_html(\n",
    "    con.sql(\"select * from professoren\")\n",
    "    .to_df()\n",
    "    .style.set_table_attributes(css)\n",
    "    .set_caption(\"<strong>Professoren</strong>\")\n",
    "    ._repr_html_()\n",
    "    + con.sql(\"select * from studenten\")\n",
    "    .to_df()\n",
    "    .style.set_table_attributes(css)\n",
    "    .set_caption(\"<strong>Studenten</strong>\")\n",
    "    ._repr_html_()\n",
    "    + con.sql(\"select * from vorlesungen\")\n",
    "    .to_df()\n",
    "    .style.set_table_attributes(css)\n",
    "    .set_caption(\"<strong>Vorlesungen</strong>\")\n",
    "    ._repr_html_()\n",
    "    + con.sql(\"select * from voraussetzen\")\n",
    "    .to_df()\n",
    "    .style.set_table_attributes(css)\n",
    "    .set_caption(\"<strong>Voraussetzungen</strong>\")\n",
    "    ._repr_html_()\n",
    "    + con.sql(\"select * from hoeren\")\n",
    "    .to_df()\n",
    "    .style.set_table_attributes(css)\n",
    "    .set_caption(\"<strong>Hören</strong>\")\n",
    "    ._repr_html_()\n",
    "    + con.sql(\"select * from pruefen\")\n",
    "    .to_df()\n",
    "    .style.set_table_attributes(css)\n",
    "    .set_caption(\"<strong>Prüfen</strong>\")\n",
    "    ._repr_html_()\n",
    "    + con.sql(\"select * from assistenten\")\n",
    "    .to_df()\n",
    "    .style.set_table_attributes(css)\n",
    "    .set_caption(\"<strong>Assistenten</strong>\")\n",
    "    ._repr_html_(),\n",
    "    raw=True,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Einführung DQL "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "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. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con.sql(\"SELECT * FROM professoren;\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "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. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con.sql(\"SELECT name AS ProfessorenName, RAUM FROM PROFESSOREN;\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "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.\n",
    "\n",
    "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. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con.sql(\"SELECT matrnr, vorlnr, persnr, note - 0.1 FROM pruefen;\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "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. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con.sql(\"SELECT COUNT(persnr) AS AnzahlProfessoren FROM professoren;\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "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. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con.sql(\"SELECT * FROM studenten, pruefen;\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con.sql(\"SELECT * FROM studenten JOIN pruefen ON studenten.matrnr = pruefen.matrnr;\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "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. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con.sql(\"SELECT stud.matrnr, name, semester, vorlnr, persnr FROM studenten stud, pruefen;\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "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. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con.sql(\"SELECT DISTINCT studenten.matrnr FROM studenten, pruefen;\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "Ohne das `Distinct` sähe das Ergebnis wie folgt aus:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con.sql(\"SELECT studenten.matrnr FROM studenten, pruefen;\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "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. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con.sql(\"SELECT * FROM Vorlesungen WHERE gelesenvon = 2125;\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "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. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "SELECT * \n",
    "FROM studenten, pruefen \n",
    "WHERE studenten.matrnr = pruefen.matrnr;\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "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. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "SELECT * \n",
    "FROM studenten, pruefen \n",
    "WHERE studenten.matrnr = pruefen.matrnr AND (name = 'Jonas' OR name = 'Carnap');\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "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. \n",
    "\n",
    "Hinweis: Bei der Vereinigung und der Schnittmenge ist es auch wichtig, dass die beiden Schemata der Daten übereinstimmen. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "SELECT * \n",
    "FROM studenten, pruefen \n",
    "WHERE studenten.matrnr = pruefen.matrnr AND name = 'Jonas'\n",
    "\n",
    "UNION\n",
    "\n",
    "SELECT * \n",
    "FROM studenten, pruefen \n",
    "WHERE studenten.matrnr = pruefen.matrnr AND name = 'Carnap';\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "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. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "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.\n",
    "Syntax Beispiel:\n",
    "```SQL\n",
    "WITH named_query AS (\n",
    "    SELECT * FROM some_table WHERE some_condition\n",
    ")\n",
    "SELECT some_column FROM named_query;\n",
    "```\n",
    "\n",
    "Es können mehrere CTEs erstellt werden, wenn diese durch ein Komma getrennt werden.\n",
    "Die Dokumentation zu CTEs in DuckDB befindet sich hier: https://duckdb.org/docs/sql/query_syntax/with.html\n",
    "\n",
    "#### Beispiel \n",
    "\n",
    "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. \n",
    "\n",
    "```SQL\n",
    "SELECT * \n",
    "FROM pruefen \n",
    "JOIN (\n",
    "    SELECT * \n",
    "    FROM Studenten \n",
    "    WHERE semester < (\n",
    "        SELECT avg(Semester) \n",
    "        FROM Studenten\n",
    "    )\n",
    ") AS unterdurchschnittlich ON pruefen.matrnr = unterdurchschnittlich.matrnr;\n",
    "```\n",
    "\n",
    "\n",
    "Die oben und untenstehenden Anfragen sind äquivalent. \n",
    "\n",
    "\n",
    "```SQL\n",
    "With durchschnitt AS (SELECT avg(Semester) FROM Studenten), \n",
    "\n",
    "unterdurchschnittlich AS (SELECT * FROM Studenten WHERE semester < (SELECT * FROM durchschnitt))\n",
    "\n",
    "\n",
    "SELECT * FROM pruefen JOIN unterdurchschnittlich ON pruefen.matrnr = unterdurchschnittlich.matrnr;\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "SELECT vorlnr, COUNT(matrnr) AS AnzahlStudierende\n",
    "FROM hoeren\n",
    "GROUP BY vorlnr;\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "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. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "SELECT vorlnr, COUNT(matrnr) AS AnzahlStudierende\n",
    "FROM hoeren\n",
    "GROUP BY vorlnr\n",
    "HAVING COUNT(matrnr) > 1;\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "Wenn wir generell unsere Ergebnisrelation in der Anzahl der Instanzen beschränken wollen, verwenden wir `LIMIT`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "SELECT vorlnr, COUNT(matrnr) AS AnzahlStudierende\n",
    "FROM hoeren\n",
    "GROUP BY vorlnr\n",
    "HAVING COUNT(matrnr) > 1\n",
    "LIMIT 1;\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "Zuletzt führen wir die `EXISTS` UND `IN` Operatoren ein, welche auch mit dem logischen `NOT`verknü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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "SELECT * \n",
    "FROM professoren p \n",
    "WHERE EXISTS (SELECT * \n",
    "              FROM vorlesungen v\n",
    "              WHERE p.persnr = v.gelesenvon);\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con.sql(\n",
    "    \"\"\"\n",
    "SELECT * \n",
    "FROM professoren p \n",
    "WHERE p.persnr NOT IN (SELECT v.gelesenvon\n",
    "                       FROM vorlesungen v);\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "Zuletzt schließen wir die Datenbankverbindung wieder."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "con.close()"
   ]
  }
 ],
 "metadata": {},
 "nbformat": 4,
 "nbformat_minor": 4
}