{ "cells": [ { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "# SQL-DQL: Aufgaben\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "import duckdb" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "import hashlib\n", "\n", "\n", "class Check:\n", " def __init__(self, con, hashes=None):\n", " self.con = con\n", " self.hashes = hashes or []\n", "\n", " def get_hash(self, df, consider_col_name: bool = False, consider_sorting: bool = False):\n", " \"\"\"\n", " Get the hash of a DataFrame.\n", "\n", " consider_col_name: If True, the column names are not considered for the hash.\n", " consider_sorting: If True, the DataFrame is sorted before hashing.\n", " \"\"\"\n", " df_str = df.astype(str)\n", " remaining = list(df_str.columns)\n", " ordered = []\n", " while remaining:\n", " min_cell, min_col = None, None\n", " for col in remaining:\n", " this_min = min(df_str[col].tolist())\n", " if min_cell is None or this_min < min_cell:\n", " min_cell, min_col = this_min, col\n", " ordered.append(min_col)\n", " remaining.remove(min_col)\n", "\n", " df_ord = df_str[ordered]\n", " if not consider_sorting:\n", " df_sorted = df_ord.sort_values(by=ordered, ignore_index=True)\n", " else:\n", " df_sorted = df_ord\n", "\n", " dfString = df_sorted.to_csv(index=False, header=consider_col_name).encode(\"utf-8\")\n", " digest = hashlib.md5(dfString).hexdigest()\n", "\n", " return digest\n", "\n", " def sql(self, sql: str, hashPrint: bool = False):\n", " \"\"\"\n", " Execute SQL and check the result against a hash.\n", " \"\"\"\n", " sqlResult = self.con.sql(sql)\n", " sqlDF = sqlResult.df()\n", " for consider_col_name in [True, False]:\n", " for consider_sorting in [True, False]:\n", " digest = self.get_hash(\n", " sqlDF, consider_col_name=consider_col_name, consider_sorting=consider_sorting\n", " )\n", " if hashPrint:\n", " print(\n", " f\"Hash: {digest} (consider_col_name={consider_col_name}, consider_sorting={consider_sorting})\"\n", " )\n", " for h in self.hashes:\n", " if isinstance(h, str):\n", " h = [h]\n", " if digest == h[0]:\n", " if len(h) > 1:\n", " print(f\"🎉 Richtige Lösung fĂŒr Aufgabe {h[1]} 🎉\")\n", " else:\n", " print(\"🎉 Richtige Lösung fĂŒr eine Aufgabe 🎉\")\n", " return sqlResult\n", "\n", " def close(self):\n", " self.con.close()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "duck_con = duckdb.connect(database=\"../resources/06_sql_dql/uni_ue.duckdb\", read_only=False)\n", "duck_con.sql(\"SELECT * FROM sqlite_master\").to_df()" ] }, { "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", " duck_con.sql(\"select * from professoren\")\n", " .to_df()\n", " .style.set_table_attributes(css)\n", " .set_caption(\"Professoren\")\n", " ._repr_html_()\n", " + duck_con.sql(\"select * from studenten\")\n", " .to_df()\n", " .style.set_table_attributes(css)\n", " .set_caption(\"Studenten\")\n", " ._repr_html_()\n", " + duck_con.sql(\"select * from vorlesungen\")\n", " .to_df()\n", " .style.set_table_attributes(css)\n", " .set_caption(\"Vorlesungen\")\n", " ._repr_html_()\n", " + duck_con.sql(\"select * from voraussetzen\")\n", " .to_df()\n", " .style.set_table_attributes(css)\n", " .set_caption(\"Voraussetzungen\")\n", " ._repr_html_()\n", " + duck_con.sql(\"select * from hoeren\")\n", " .to_df()\n", " .style.set_table_attributes(css)\n", " .set_caption(\"Hören\")\n", " ._repr_html_()\n", " + duck_con.sql(\"select * from pruefen\")\n", " .to_df()\n", " .style.set_table_attributes(css)\n", " .set_caption(\"PrĂŒfen\")\n", " ._repr_html_()\n", " + duck_con.sql(\"select * from assistenten\")\n", " .to_df()\n", " .style.set_table_attributes(css)\n", " .set_caption(\"Assistenten\")\n", " ._repr_html_(),\n", " raw=True,\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con = Check(\n", " duck_con,\n", " hashes=[\n", " [\"82c5f00103c8fdc496e60e58a092ccd3\", \"1.1\"],\n", " [\"893e57904471455be8fe2e88be78b42f\", \"1.2\"],\n", " [\"9dc1d3ebc2f41494f72695cd4d85998e\", \"1.3\"],\n", " [\"45910b56c5f8023603d5d0b6edd8df4f\", \"1.4\"],\n", " [\"5ca534e9f807a0629b4fe17f7fa5352c\", \"1.5\"],\n", " [\"1cbbe6645d90c69fd180e67ce6269323\", \"1.6\"],\n", " [\"736c7e7a6f3009aa826b74e3e0a0b82e\", \"1.7.1\"],\n", " [\"16d7a32f47fd82ed90a8ea1c76a79dec\", \"1.7.2\"],\n", " [\"969baded3818069b0d4c1c37f63f3107\", \"1.7.3\"],\n", " [\"26affab1d343c6ddff7a5da3f4e190b4\", \"1.8\"],\n", " [\"f788bb6ffdff1f437abaeaf2b443adbf\", \"1.9\"],\n", " [\"550ce8cafb2b80935ccd517c43e68125\", \"1.10\"],\n", " ],\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "### Aufgabe 1\n", "\n", "Formulieren Sie zu den folgenden Aussagen passende SQL-DQL Anfragen." ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 1.1\n", "Alle Studierenden." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 1.2\n", "Die Namen der Studierenden, die im zweiten Semester sind. " ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 1.3\n", "Die Namen aller Studierenden und die Vorlesungsnummern, die Sie hören. Hört eine studierende Person keine Vorlesung, so soll diese Person nicht in der Ergebnisrelation auftauchen." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 1.4\n", "Die Namen aller Studierenden, die keine Vorlesung besuchen." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", " \n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 1.5\n", "Die Namen aller Studierenden und Professor*innen." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 1.6\n", "Die Matrikelnummern aller Studierenden und die Anzahl der von ihnen besuchten Vorlesungen. Geben Sie nur Studierende aus, welche mindestens 3 Vorlesungen besuchen. " ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 1.7\n", "Die Matrikelnummern aller Studierenden und die Anzahl der von ihnen besuchten Vorlesungen. Auch Studierende, die keine Vorlesungen besuchen, sollen ausgegeben werden. Geben Sie die Ergebnisinstanzen absteigend sortiert nach der Anzahl der besuchten Vorlesungen an. " ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 1.7.1 \n", "\n", "Bestimmen Sie im ersten Schritt nur die Matrikelnummern aller Studierenden und die Anzahl der von ihnen besuchten Vorlesungen, wenn diese Studierenden mindestens eine Vorlesung besuchen. Geben Sie die Ergebnisinstanzen absteigend sortiert nach der Anzahl der besuchten Vorlesungen an." ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "HinzufĂŒgen: Bei gleicher Anzahl sotiere nach der Matrikelnummer aufsteigend" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 1.7.2\n", "\n", "Bestimmen Sie nun die Matrikelnummern aller Studierenden, welche keine Vorlesungen besuchen, und gebe sie die Matrikelnummer aus zusammen mit dem Wert 0 in einer Spalte `Anzahl`. " ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 1.7.3 \n", "\n", "Bestimmen Sie nun das Ergebnis der Hauptanfrage: \n", "\n", "Die Matrikelnummern aller Studierenden und die Anzahl der von ihnen besuchten Vorlesungen. Auch Studierende, die keine Vorlesungen besuchen, sollen ausgegeben werden. Geben Sie die Ergebnis-Instanzen absteigend sortiert nach der Anzahl der besuchten Vorlesungen an. " ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "HinzufĂŒgen: Bei gleicher Anzahl sotiere nach der Matrikelnummer aufsteigend" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 1.8*\n", "Geben Sie die Namen der Vorlesungen an, welche keine Nachfolgevorlesungen haben." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", " \n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 1.9*\n", "Die Namen von Professoren und Professorinnen und die Durchschnittsnote, welche Studierende bei diesen erzielen. Sortieren Sie das Ergebnis absteigend nach der Durchschnittsnote. " ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "HinzufĂŒgen: Bei gleicher Durchschnittssnote sotiere nach dem Namen." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 1.10*\n", "Die Namen der Studierenden, welche ein Modul belegen und auch parallel ein darauf aufbauendes (Nachfolger) Modul belegen. " ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.close()" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "### Aufgabe 2\n", "\n", "Im Folgenden arbeiten wir mit dem Ihnen bekannten Regattaschema. \n", "\n", "Formulieren Sie zu den folgenden Aussagen passende SQL-DQL Anfragen.\n", "\n", "![](../resources/05_relationale_algebra/regatta.png)\n", "\n", "Zuerst öffnen wir die Datenbank." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "duck_con = duckdb.connect(\"../resources/06_sql_dql/wettfahrt_ue.duckdb\", read_only=False)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con = Check(\n", " duck_con,\n", " hashes=[\n", " [\"93a65e62fc34df872038fc57f8f9fa2a\", \"2.1\"],\n", " [\"933e5df76f96d65c4bfbe7145a1e301a\", \"2.2\"],\n", " [\"eaeedaa7cc7e4318807d5159906f8e1d\", \"2.3\"],\n", " [\"bb843a25cc58f37b2e6bfe1d75cd7373\", \"2.4\"],\n", " [\"c9b028e8e56cf2363badc3fe280906eb\", \"2.5\"],\n", " [\"511213b8a1e9862f238eccd8a1b9b1b6\", \"2.6\"],\n", " [\"8693f90eb469b6244b73eef97079aed7\", \"2.7.1\"],\n", " [\"18eac337c4439e181265f60340f14731\", \"2.7.2\"],\n", " [\"007c39966151322a90b8221820398e52\", \"2.7.3\"],\n", " [\"5a48f0574c3d62d26a08f3888732f792\", \"2.7.4\"],\n", " [\"2a669e47b6ecfa30b54a2aa2f1c50759\", \"2.7.5\"],\n", " [\"bb843a25cc58f37b2e6bfe1d75cd7373\", \"2.7.6\"],\n", " [\"bb843a25cc58f37b2e6bfe1d75cd7373\", \"2.8\"],\n", " [\"ff1136f19b74186291424cf7c817df52\", \"2.9\"],\n", " [\"7b36f4c1068af7b11e3a3c009808443f\", \"3.1\"],\n", " [\"ff1136f19b74186291424cf7c817df52\", \"3.2\"],\n", " ],\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 2.1\n", "Die Namen der Regatten und Boote, die sie gewonnen haben." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", " \n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 2.2\n", "Die Bootsfarbe, die am hĂ€ufigsten vertreten ist und die SegelNr der Boote, die in dieser Farbe gestrichen oder lackiert sind." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 2.3*\n", "Die Namen der Pokale, an denen keine Holzboote teilgenommen haben. Dabei soll jeder Name nur genau einmal vorkommen." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 2.4*\n", "Die Namen der Pokale, an denen ausschließlich Holzboote teilgenommen haben." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 2.5*\n", "Finde alle Teilnehmer (SegelNr und Name), die an mindestens zwei verschiedenen Wettfahrten teilgenommen haben." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 2.6*\n", "Finde alle Teilnehmer (SegelNr und Eigner) welche keine Platzierung erlangt haben." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 2.7: Vorbereitung Hausaufgabe \n", "\n", "Die folgende Anfrage Ă€hnelt von der KomplexitĂ€t der Anfrage dem, was Sie unter anderem in der Hausaufgabe erwarten können. Lösen Sie die Aufgabe, indem Sie zuvor schrittweise die einzelnen Teilaufgaben bzw. Teilanfragen lösen. Nutzen Sie, wo möglich, die zuvor eingefĂŒhrten Common Table Expressions.\n", "\n", "Hauptanfrage: Bestimmen Sie die Namen der Wettfahrten, bei denen alle Teilnehmer eine im Durchschnitt höhere Platzierung haben, als der Durchschnitt aller Boote bei allen Wettfahrten." ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "##### Aufgabe 2.7.1\n", "\n", "Bestimmen Sie den Durchschnitt aller Platzierungen. Wenn ein Boot ein Rennen nicht abgeschlossen hat (dnf/NULL), dann soll dies in den Durchschnitt mit dem Wert 5 einfließen. Der Wert von dnf bzw. NULL gilt auch fĂŒr die folgenden Teilaufgaben." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "##### Aufgabe 2.7.2 \n", "\n", "Bestimmen Sie die durchschnittliche Platzierung pro Boot (SegelNr). " ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "##### Aufgabe 2.7.3\n", "\n", "Bestimmen Sie die Boote (SegelNr), welche im Durchschnitt besser Platzierungen erzielen, als der Durchschnitt aller Platzierungen. Nutzen Sie (Teil-)Anfragen von zuvor." ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "##### Aufgabe 2.7.4\n", "\n", "Bestimmen Sie die Anzahl an Teilnehmern (SegelNr) pro Regatta." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "##### Aufgabe 2.7.5\n", "\n", "Bestimmen Sie die Anzahl der ĂŒberdurchschnittlich guten Teilnehmer (SegelNr) pro Regatta. Nutzen Sie Ihr Ergebnis aus Teilaufgabe 2.7.3." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "##### Aufgabe 2.7.6\n", "\n", "Lösen Sie nun die Hauptaufgabe. \n", "\n", "Bestimmen Sie die Namen der Wettfahrten, bei denen alle Teilnehmer eine im Durchschnitt höhere Platzierung haben, als der Durchschnitt aller Boote bei allen Wettfahrten." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 2.8*\n", "Die Namen der Wettfahrten, an denen die wenigsten Boote teilgenommen haben." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 2.9* \n", "\n", "In der Relationalen Algebra gibt es den `Divisions` Operator. Die Division ermöglicht es, EntitĂ€ten zu finden, die mit allen EntitĂ€ten einer anderen Relation interagieren. Da es die Division in SQL nicht direkt gibt, ist es nun Ihre Aufgabe, die Division durch existierende SQL-Operationen darzustellen.\n", "\n", "(Link zur Division: https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/divided-we-stand-the-sql-of-relational-division/)\n", "(Alternativ kann auch ein Ansatz, die Division darzustellen, verwendet werden, den man gut mit CTEs kombinieren kann: https://www.geeksforgeeks.org/sql-division/).\n", "\n", "Die Wettfahrten, an denen Boote aller Bauarten teilgenommen haben. " ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "### Aufgabe 3\n", "\n", "Formulieren Sie die folgenden AusdrĂŒcke der relationalen Algebra zu Ă€quivalenten SQL-DQL Anfragen." ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 3.1\n", "\n", "$\\pi_{SegelNr, Wettfahrt, Platz, Name, Datum, Zeit}(\\sigma_{Name=\"Herbstmeister\"}\n", "(Wettfahrt\\bowtie_{FahrtNr=Wettfahrt}(\\sigma_{Platz=1}Platzierung)))$" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\"\"\"\n", ")" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 3.2*\n", "\n", "$\\pi_{Name}(Wettfahrt \\bowtie_{FahrtNr=Wettfahrt}(Platzierung \\bowtie(\\pi_{SegelNr}(\\sigma_{Baujahr<1980}(Teilnehmer)))))$" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.sql(\n", " \"\"\"\n", "\n", "\"\"\"\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.close()" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "### Aufgabe 4*: SQL Injection\n", "\n", "Wieso sind SQL Injections relevant? Fragen wir xkcd:\n", "\n", "![](https://imgs.xkcd.com/comics/exploits_of_a_mom.png)\n", "\n", "Zuerst öffnen wir die die Uni Datenbank wieder. " ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con = duckdb.connect(database=\"../resources/06_sql_dql/uni_ue.duckdb\", read_only=False)" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 4.1*\n", "\n", "Gegeben sei die folgende Abfrage, um die Matrikelnummer eines:einer Studierenden mit mindestens 8 Semestern abzufragen.\n", "\n", "Schreiben Sie einen Input, der mehr als einen Studierenden zurĂŒckgibt. Diskutieren Sie danach wie dies verhindert werden kann. " ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "name = input(\"Gib einen Namen an: \")\n", "query = (\n", " \"SELECT s.name, s.matrnr FROM studenten s WHERE s.name LIKE '\"\n", " + name\n", " + \"' AND s.semester >= 8;\"\n", ")\n", "print(query)\n", "\n", "con.sql(query)" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 4.2*\n", "\n", "Schreiben Sie nun einen Input, der alle Studierenden zurĂŒckgibt." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "name = input(\"Gib einen Namen an: \")\n", "\n", "query = (\n", " \"SELECT s.name, s.matrnr FROM studenten s WHERE s.name LIKE '\"\n", " + name\n", " + \"' AND s.semester >= 8;\"\n", ")\n", "print(query)\n", "\n", "con.sql(query)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "con.close()" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "### Probleme bei der Nutzung von LLMs \n", "Wir wĂŒrden Sie gerne dazu motivieren, die Vorleistung ohne die Nutzung von LLM's zu lösen, um den grĂ¶ĂŸt möglichen Lerneffekt zu erzielen.\n", "##### Bedenken Sie, dass Sie in der Klausur das Wissen ohne LLM's nachweisen mĂŒssen !\n", "Es gibt neben dem Lerneffekt jedoch auch zusĂ€tzliche Schwierigkeiten, die mit der Nutzung von LLM's einhergeht:" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### AmbiguitĂ€t in der Spezifikation\n", "Beispiel: Generiere eine SQL-Query, die alle deutschen Filmregisseure ausgibt\n", "Problem: Je nach Auffassung kann die korrekte Query eine andere sein: z.B. \n", "- deutsche StaatsbĂŒrger die Regisseure sind\n", "- Regisseure, die deutschsprachige Filme drehen\n", "- Regisseure, die in Deutschland Filme produzieren\n", "- etc.\n", "\n", "Beispiel: Generiere eine SQL-Query, die die beste Marathon-LĂ€uferin ermittelt\n", "Problem: gut/beste ist nicht eindeutig definiert. Gemeint sein könnte\n", "- Marathon-LĂ€uferin mit geringster Bestzeit\n", "- Marathon-LĂ€uferin mit geringster Durchschnittszeit\n", "- mit höchster Anzahl an Wettbewerben\n", "- etc.\n", "\n", "\n", "##### Bedenke:\n", "Selbst wenn Ihnen, die Spezifikation bereits klar geworden ist, kann das LLM weiterhin mit der genauen Spezifikation verwirrt sein." ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Schema Linking\n", "Beispiel: Generiere eine SQL-Query, die alle deutschen Filme ausgibt\n", "Problem: Wissen ĂŒber Abbildung der Daten ist nötig, um korrekte Query zu schreiben.\n", "In Spalte “Nation” könnte Deutschland abgebildet sein als Germany, Deutschland, GER, DE, etc." ] } ], "metadata": {}, "nbformat": 4, "nbformat_minor": 4 }