2  💻 Notebook-Übung: SQL-Aggregatfunktionen

HinweisWie man dieses Notebook lokal ausführt

1. Erstelle einen Ordner
2. Erstelle eine virtuelle Python-Umgebung
   python -m venv .venv
3. Aktiviere die virtuelle Umgebung
   .venv\Scripts\activate
   
4. Installiere Jupyter und Pandas in dieser Umgebung
   pip install jupyter pandas
5. Lade diese Datei in deinen neu erstellten Ordner herunter
6. Starte den lokalen Jupyter-Server
   jupyter-lab

2.1 Analyse einer Verkaufsdatenbank

Kontext: Du analysierst eine vereinfachte Verkaufsdatenbank für einen Online-Shop. Die Datenbank enthält zwei Tabellen: products (Produktkatalog) und orders (Kundenbestellungen).

Ziel: Anwendung der SQL-Aggregatfunktionen COUNT, SUM, AVG, MIN und MAX in Kombination mit GROUP BY und HAVING in einer SQLite-Datenbank direkt in diesem Notebook.

Dauer: ca. 15–20 Minuten

Voraussetzungen: Grundkenntnisse in Python und pandas (DataFrame);
SQL-Grundlagen: SELECT, FROM, WHERE

Bootcamp Um deine SQL-Kenntnisse aufzufrischen, kannst du folgende Übungen machen:

1. Basics
2. SELECT

---

2.2 Datenbank-Schema

Tabelle: products

product_id	product_name	category	price
1	Laptop	Electronics	1200.00
2	Mouse	Electronics	25.00
3	Desk	Furniture	450.00
4	Chair	Furniture	320.00
5	Headset	Electronics	80.00

Tabelle: orders

order_id	product_id	quantity	customer
1	1	2	Anna
2	2	5	Ben
3	3	1	Clara
4	1	1	Daniel
5	4	3	Anna
6	5	2	Ben
7	2	8	Clara
8	3	2	Evi
9	5	1	Anna

---

2.3 Datenbank im Notebook

Die folgende Zelle stellt im Jupyter Notebook eine abfragbare Datenbank zur Verfügung.

# --- Setup (nicht verändern) ---
import sqlite3
import pandas as pd

# Hilfsfunktion: Führt eine SQL-Abfrage aus und gibt ein DataFrame zurück.
def sql(query):
    cursor.execute(query)
    cols = [d[0] for d in cursor.description]
    return pd.DataFrame(cursor.fetchall(), columns=cols)

# Erstelle eine SQLite-Datenbank im Arbeitsspeicher (RAM)
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

cursor.executescript("""
CREATE TABLE products (
    product_id   INTEGER PRIMARY KEY,
    product_name TEXT,
    category     TEXT,
    price        REAL
);
CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    product_id  INTEGER,
    quantity    INTEGER,
    customer    TEXT,
    FOREIGN KEY (product_id)
        REFERENCES products(product_id)
);
INSERT INTO products VALUES
    (1, 'Laptop',   'Electronics', 1200.00),
    (2, 'Mouse',    'Electronics',   25.00),
    (3, 'Desk',     'Furniture',    450.00),
    (4, 'Chair',    'Furniture',    320.00),
    (5, 'Headset',  'Electronics',   80.00);
INSERT INTO orders VALUES
    (1, 1, 2, 'Anna'),
    (2, 2, 5, 'Ben'),
    (3, 3, 1, 'Clara'),
    (4, 1, 1, 'Daniel'),
    (5, 4, 3, 'Anna'),
    (6, 5, 2, 'Ben'),
    (7, 2, 8, 'Clara'),
    (8, 3, 2, 'Evi'),
    (9, 5, 1, 'Anna');
""")
conn.commit()
print('Datenbank bereit. Viel Erfolg!')

2.4 Abfrageübungen

# --- Übungen ---
# Nutze sql(""" ... """) um deine Abfragen auszuführen.
# Ersetze den Platzhalter-Kommentar mit deinem SQL-Code.

# AUFGABE 1: Wie viele Bestellungen gibt es insgesamt?
# Nutze COUNT(*) auf der Tabelle 'orders'.
result1 = sql("""
-- Deine Abfrage hier
""")
print('Aufgabe 1:\n', result1)

# AUFGABE 2: Was ist der günstigste und der teuerste
# Produktpreis? Nutze MIN und MAX.
result2 = sql("""
-- Deine Abfrage hier              
""")
print('Aufgabe 2:\n', result2)

# AUFGABE 3: Wie viele Bestellungen und welche Gesamtmenge
# wurden pro Kategorie aufgegeben?
# Verbinde (JOIN) 'orders' und 'products', gruppiere nach 'category'.
result3 = sql("""
-- Deine Abfrage hier
""")
print('Aufgabe 3:\n', result3)

# AUFGABE 4: Zeige nur die Kategorien an, in denen die
# bestellte Gesamtmenge (quantity) größer als 5 ist.
# Nutze HAVING (nicht WHERE!).
result4 = sql("""
-- Deine Abfrage hier
""")
print('Aufgabe 4:\n', result4)

2.5 Weiterführendes Training

Für weiteres Training zu Aggregatfunktionen siehe SUM and COUNT auf sqlzoo.net.

2.6 Reflexion

Beantworte die folgenden Fragen in dieser Zelle:

1. Warum erzeugt WHERE category = 'Electronics' vor dem GROUP BY ein anderes Ergebnis als HAVING category = 'Electronics' nach dem GROUP BY?

   Ihre Antwort:

2. Was ist der Unterschied zwischen COUNT(*) und COUNT(product_id)? Wann könnte dies einen Unterschied machen?

   Ihre Antwort:

3. In welchen realen beruflichen Situationen würdest du Abfragen wie diese verwenden? Nenne zwei konkrete Beispiele.

   Ihre Antwort: