SQL-DQL: Aufgaben#
import duckdb
import hashlib
class Check:
def __init__(self, con, hashes=None):
self.con = con
self.hashes = hashes or []
def get_hash(self, df, consider_col_name: bool = False, consider_sorting: bool = False):
"""
Get the hash of a DataFrame.
consider_col_name: If True, the column names are not considered for the hash.
consider_sorting: If True, the DataFrame is sorted before hashing.
"""
df_str = df.astype(str)
remaining = list(df_str.columns)
ordered = []
while remaining:
min_cell, min_col = None, None
for col in remaining:
this_min = min(df_str[col].tolist())
if min_cell is None or this_min < min_cell:
min_cell, min_col = this_min, col
ordered.append(min_col)
remaining.remove(min_col)
df_ord = df_str[ordered]
if not consider_sorting:
df_sorted = df_ord.sort_values(by=ordered, ignore_index=True)
else:
df_sorted = df_ord
dfString = df_sorted.to_csv(index=False, header=consider_col_name).encode("utf-8")
digest = hashlib.md5(dfString).hexdigest()
return digest
def sql(self, sql: str, hashPrint: bool = False):
"""
Execute SQL and check the result against a hash.
"""
sqlResult = self.con.sql(sql)
sqlDF = sqlResult.df()
for consider_col_name in [True, False]:
for consider_sorting in [True, False]:
digest = self.get_hash(
sqlDF, consider_col_name=consider_col_name, consider_sorting=consider_sorting
)
if hashPrint:
print(
f"Hash: {digest} (consider_col_name={consider_col_name}, consider_sorting={consider_sorting})"
)
for h in self.hashes:
if isinstance(h, str):
h = [h]
if digest == h[0]:
if len(h) > 1:
print(f"🎉 Richtige Lösung für Aufgabe {h[1]} 🎉")
else:
print("🎉 Richtige Lösung für eine Aufgabe 🎉")
return sqlResult
def close(self):
self.con.close()
duck_con = duckdb.connect(database="../resources/06_sql_dql/uni_ue.duckdb", read_only=False)
duck_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... |
from IPython.display import display_html
css = "style='display:inline; margin: 0px 20px 0px 0px; vertical-align: top;'"
display_html(
duck_con.sql("select * from professoren")
.to_df()
.style.set_table_attributes(css)
.set_caption("<strong>Professoren</strong>")
._repr_html_()
+ duck_con.sql("select * from studenten")
.to_df()
.style.set_table_attributes(css)
.set_caption("<strong>Studenten</strong>")
._repr_html_()
+ duck_con.sql("select * from vorlesungen")
.to_df()
.style.set_table_attributes(css)
.set_caption("<strong>Vorlesungen</strong>")
._repr_html_()
+ duck_con.sql("select * from voraussetzen")
.to_df()
.style.set_table_attributes(css)
.set_caption("<strong>Voraussetzungen</strong>")
._repr_html_()
+ duck_con.sql("select * from hoeren")
.to_df()
.style.set_table_attributes(css)
.set_caption("<strong>Hören</strong>")
._repr_html_()
+ duck_con.sql("select * from pruefen")
.to_df()
.style.set_table_attributes(css)
.set_caption("<strong>Prüfen</strong>")
._repr_html_()
+ duck_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 |
con = Check(
duck_con,
hashes=[
["82c5f00103c8fdc496e60e58a092ccd3", "1.1"],
["893e57904471455be8fe2e88be78b42f", "1.2"],
["9dc1d3ebc2f41494f72695cd4d85998e", "1.3"],
["45910b56c5f8023603d5d0b6edd8df4f", "1.4"],
["5ca534e9f807a0629b4fe17f7fa5352c", "1.5"],
["1cbbe6645d90c69fd180e67ce6269323", "1.6"],
["736c7e7a6f3009aa826b74e3e0a0b82e", "1.7.1"],
["16d7a32f47fd82ed90a8ea1c76a79dec", "1.7.2"],
["969baded3818069b0d4c1c37f63f3107", "1.7.3"],
["26affab1d343c6ddff7a5da3f4e190b4", "1.8"],
["f788bb6ffdff1f437abaeaf2b443adbf", "1.9"],
["550ce8cafb2b80935ccd517c43e68125", "1.10"],
],
)
Aufgabe 1#
Formulieren Sie zu den folgenden Aussagen passende SQL-DQL Anfragen.
Aufgabe 1.1#
Alle Studierenden.
con.sql(
"""
"""
)
---------------------------------------------------------------------------
AttributeError Traceback (most recent call last)
Cell In[6], line 1
----> 1 con.sql(
2 """
3
4 """
5 )
Cell In[2], line 44, in Check.sql(self, sql, hashPrint)
40 """
41 Execute SQL and check the result against a hash.
42 """
43 sqlResult = self.con.sql(sql)
---> 44 sqlDF = sqlResult.df()
45 for consider_col_name in [True, False]:
46 for consider_sorting in [True, False]:
AttributeError: 'NoneType' object has no attribute 'df'
Aufgabe 1.2#
Die Namen der Studierenden, die im zweiten Semester sind.
con.sql(
"""
"""
)
Aufgabe 1.3#
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.
con.sql(
"""
"""
)
Aufgabe 1.4#
Die Namen aller Studierenden, die keine Vorlesung besuchen.
con.sql(
"""
"""
)
Aufgabe 1.5#
Die Namen aller Studierenden und Professor*innen.
con.sql(
"""
"""
)
Aufgabe 1.6#
Die Matrikelnummern aller Studierenden und die Anzahl der von ihnen besuchten Vorlesungen. Geben Sie nur Studierende aus, welche mindestens 3 Vorlesungen besuchen.
con.sql(
"""
"""
)
Aufgabe 1.7#
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.
Aufgabe 1.7.1#
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.
Hinzufügen: Bei gleicher Anzahl sotiere nach der Matrikelnummer aufsteigend
con.sql(
"""
"""
)
Aufgabe 1.7.2#
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.
con.sql(
"""
"""
)
Aufgabe 1.7.3#
Bestimmen Sie nun das Ergebnis der Hauptanfrage:
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.
Hinzufügen: Bei gleicher Anzahl sotiere nach der Matrikelnummer aufsteigend
con.sql(
"""
"""
)
Aufgabe 1.8*#
Geben Sie die Namen der Vorlesungen an, welche keine Nachfolgevorlesungen haben.
con.sql(
"""
"""
)
Aufgabe 1.9*#
Die Namen von Professoren und Professorinnen und die Durchschnittsnote, welche Studierende bei diesen erzielen. Sortieren Sie das Ergebnis absteigend nach der Durchschnittsnote.
Hinzufügen: Bei gleicher Durchschnittssnote sotiere nach dem Namen.
con.sql(
"""
"""
)
Aufgabe 1.10*#
Die Namen der Studierenden, welche ein Modul belegen und auch parallel ein darauf aufbauendes (Nachfolger) Modul belegen.
con.sql(
"""
"""
)
con.close()
Aufgabe 2#
Im Folgenden arbeiten wir mit dem Ihnen bekannten Regattaschema.
Formulieren Sie zu den folgenden Aussagen passende SQL-DQL Anfragen.
Zuerst öffnen wir die Datenbank.
duck_con = duckdb.connect("../resources/06_sql_dql/wettfahrt_ue.duckdb", read_only=False)
con = Check(
duck_con,
hashes=[
["93a65e62fc34df872038fc57f8f9fa2a", "2.1"],
["933e5df76f96d65c4bfbe7145a1e301a", "2.2"],
["eaeedaa7cc7e4318807d5159906f8e1d", "2.3"],
["bb843a25cc58f37b2e6bfe1d75cd7373", "2.4"],
["c9b028e8e56cf2363badc3fe280906eb", "2.5"],
["511213b8a1e9862f238eccd8a1b9b1b6", "2.6"],
["8693f90eb469b6244b73eef97079aed7", "2.7.1"],
["18eac337c4439e181265f60340f14731", "2.7.2"],
["007c39966151322a90b8221820398e52", "2.7.3"],
["5a48f0574c3d62d26a08f3888732f792", "2.7.4"],
["2a669e47b6ecfa30b54a2aa2f1c50759", "2.7.5"],
["bb843a25cc58f37b2e6bfe1d75cd7373", "2.7.6"],
["bb843a25cc58f37b2e6bfe1d75cd7373", "2.8"],
["ff1136f19b74186291424cf7c817df52", "2.9"],
["7b36f4c1068af7b11e3a3c009808443f", "3.1"],
["ff1136f19b74186291424cf7c817df52", "3.2"],
],
)
Aufgabe 2.1#
Die Namen der Regatten und Boote, die sie gewonnen haben.
con.sql(
"""
"""
)
Aufgabe 2.2#
Die Bootsfarbe, die am häufigsten vertreten ist und die SegelNr der Boote, die in dieser Farbe gestrichen oder lackiert sind.
con.sql(
"""
"""
)
Aufgabe 2.3*#
Die Namen der Pokale, an denen keine Holzboote teilgenommen haben. Dabei soll jeder Name nur genau einmal vorkommen.
con.sql(
"""
"""
)
Aufgabe 2.4*#
Die Namen der Pokale, an denen ausschließlich Holzboote teilgenommen haben.
con.sql(
"""
"""
)
Aufgabe 2.5*#
Finde alle Teilnehmer (SegelNr und Name), die an mindestens zwei verschiedenen Wettfahrten teilgenommen haben.
con.sql(
"""
"""
)
Aufgabe 2.6*#
Finde alle Teilnehmer (SegelNr und Eigner) welche keine Platzierung erlangt haben.
con.sql(
"""
"""
)
Aufgabe 2.7: Vorbereitung Hausaufgabe#
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.
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.
Aufgabe 2.7.1#
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.
con.sql(
"""
"""
)
Aufgabe 2.7.2#
Bestimmen Sie die durchschnittliche Platzierung pro Boot (SegelNr).
con.sql(
"""
"""
)
Aufgabe 2.7.3#
Bestimmen Sie die Boote (SegelNr), welche im Durchschnitt besser Platzierungen erzielen, als der Durchschnitt aller Platzierungen. Nutzen Sie (Teil-)Anfragen von zuvor.
Aufgabe 2.7.4#
Bestimmen Sie die Anzahl an Teilnehmern (SegelNr) pro Regatta.
con.sql(
"""
"""
)
Aufgabe 2.7.5#
Bestimmen Sie die Anzahl der überdurchschnittlich guten Teilnehmer (SegelNr) pro Regatta. Nutzen Sie Ihr Ergebnis aus Teilaufgabe 2.7.3.
con.sql(
"""
"""
)
Aufgabe 2.7.6#
Lösen Sie nun die Hauptaufgabe.
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.
con.sql(
"""
"""
)
Aufgabe 2.8*#
Die Namen der Wettfahrten, an denen die wenigsten Boote teilgenommen haben.
con.sql(
"""
"""
)
Aufgabe 2.9*#
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.
(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/) (Alternativ kann auch ein Ansatz, die Division darzustellen, verwendet werden, den man gut mit CTEs kombinieren kann: https://www.geeksforgeeks.org/sql-division/).
Die Wettfahrten, an denen Boote aller Bauarten teilgenommen haben.
con.sql(
"""
"""
)
Aufgabe 3#
Formulieren Sie die folgenden Ausdrücke der relationalen Algebra zu äquivalenten SQL-DQL Anfragen.
Aufgabe 3.1#
\(\pi_{SegelNr, Wettfahrt, Platz, Name, Datum, Zeit}(\sigma_{Name="Herbstmeister"} (Wettfahrt\bowtie_{FahrtNr=Wettfahrt}(\sigma_{Platz=1}Platzierung)))\)
con.sql(
"""
"""
)
Aufgabe 3.2*#
\(\pi_{Name}(Wettfahrt \bowtie_{FahrtNr=Wettfahrt}(Platzierung \bowtie(\pi_{SegelNr}(\sigma_{Baujahr<1980}(Teilnehmer)))))\)
con.sql(
"""
"""
)
con.close()
Aufgabe 4*: SQL Injection#
Wieso sind SQL Injections relevant? Fragen wir xkcd:
Zuerst öffnen wir die die Uni Datenbank wieder.
con = duckdb.connect(database="../resources/06_sql_dql/uni_ue.duckdb", read_only=False)
Aufgabe 4.1*#
Gegeben sei die folgende Abfrage, um die Matrikelnummer eines:einer Studierenden mit mindestens 8 Semestern abzufragen.
Schreiben Sie einen Input, der mehr als einen Studierenden zurückgibt. Diskutieren Sie danach wie dies verhindert werden kann.
name = input("Gib einen Namen an: ")
query = (
"SELECT s.name, s.matrnr FROM studenten s WHERE s.name LIKE '"
+ name
+ "' AND s.semester >= 8;"
)
print(query)
con.sql(query)
Aufgabe 4.2*#
Schreiben Sie nun einen Input, der alle Studierenden zurückgibt.
name = input("Gib einen Namen an: ")
query = (
"SELECT s.name, s.matrnr FROM studenten s WHERE s.name LIKE '"
+ name
+ "' AND s.semester >= 8;"
)
print(query)
con.sql(query)
con.close()
Probleme bei der Nutzung von LLMs#
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.
Bedenken Sie, dass Sie in der Klausur das Wissen ohne LLM’s nachweisen müssen !#
Es gibt neben dem Lerneffekt jedoch auch zusätzliche Schwierigkeiten, die mit der Nutzung von LLM’s einhergeht:
Ambiguität in der Spezifikation#
Beispiel: Generiere eine SQL-Query, die alle deutschen Filmregisseure ausgibt Problem: Je nach Auffassung kann die korrekte Query eine andere sein: z.B.
deutsche Staatsbürger die Regisseure sind
Regisseure, die deutschsprachige Filme drehen
Regisseure, die in Deutschland Filme produzieren
etc.
Beispiel: Generiere eine SQL-Query, die die beste Marathon-Läuferin ermittelt Problem: gut/beste ist nicht eindeutig definiert. Gemeint sein könnte
Marathon-Läuferin mit geringster Bestzeit
Marathon-Läuferin mit geringster Durchschnittszeit
mit höchster Anzahl an Wettbewerben
etc.
Bedenke:#
Selbst wenn Ihnen, die Spezifikation bereits klar geworden ist, kann das LLM weiterhin mit der genauen Spezifikation verwirrt sein.
Schema Linking#
Beispiel: Generiere eine SQL-Query, die alle deutschen Filme ausgibt Problem: Wissen über Abbildung der Daten ist nötig, um korrekte Query zu schreiben. In Spalte “Nation” könnte Deutschland abgebildet sein als Germany, Deutschland, GER, DE, etc.