Aufgabe 02 - Komplexe Abfragen

Aufgabe 02 - Komplexe Abfragen

Worum geht es?

In dieser Übung geht es über einfache Joins und Subqueries hinaus: Fensterfunktionen, Common Table Expressions (CTEs) und rekursive CTEs an der Northwind-Datenbank (siehe Kapitel 1 - Komplexe Abfragen). Im Expertenteil wird die Mitarbeiter-Hierarchie mit einer rekursiven CTE aufgebaut.

Was Sie dafür brauchen

• Northwind wie in Aufgabe 01 importiert.
• Kapitel 1 - Komplexe Abfragen, Abschnitte Fensterfunktionen und CTEs.

Welche Kompetenzen Sie erwerben und zeigen

• Sie setzen Fensterfunktionen für Ranking, Vergleich und laufende Werte ein.
• Sie strukturieren Abfragen mit CTEs.
• Sie bauen eine rekursive CTE für eine Hierarchie.
• Sie vergleichen Fensterfunktionen, CTEs und Subqueries nach Lesbarkeit und Zweck.

Pädagogische Einordnung

• Reproduktion: einfache Fensterfunktionen anwenden (Teil A).
• Reorganisation und Transfer: LAG und CTEs einsetzen (Teile B und C).
• Reflexion, Problemlösung und Urteilsbildung: eine rekursive CTE entwickeln und beurteilen (Teil D).

Arbeitsaufträge

Die Übung ist auf etwa zwei Stunden ausgelegt. Teil D ist der Expertenteil.

Netto-Bestellwert durchgehend: Summe der Positionen unit_price * quantity * (1 - discount).

Teil A - Fensterfunktionen: Ranking und Vergleich

1. Top-Produkt je Jahr: je Jahr (aus order_date) das umsatzstärkste Produkt mit RANK() OVER (PARTITION BY year ORDER BY revenue DESC), danach auf rank = 1 filtern (year, product_name, revenue, rank).
2. Bestellung im Kundenkontext: alle Bestellungen mit order_id, customer_id, order_date, net_order_value, dazu SUM(...) OVER (PARTITION BY customer_id) als Kundenumsatz und RANK() OVER (PARTITION BY customer_id ORDER BY net_order_value DESC). Interpretieren: viele kleine gegen wenige große Bestellungen.

Teil B - Zeitreihen und Durchschnitt

1. Veränderung zum Vormonat: Monatsumsatz ermitteln, mit LAG() den Vormonat und die Differenz delta berechnen, chronologisch sortiert.
2. Abweichung vom Gesamtdurchschnitt: je Bestellung order_value, AVG(...) OVER () und die Differenz zum Durchschnitt. Diskutieren, wofür diese Sicht nützlich ist (Ausreißer-Erkennung).

Teil C - CTEs

1. Erstbestellung je Kunde: einmal mit korrelierter Subquery (Variante A) und einmal mit einer CTE, die je Kunde MIN(order_date) berechnet (Variante B). Lesbarkeit und Performance vergleichen.
2. Bestseller je Kategorie mit CTE: CTE category_sales (Umsatz je Produkt und Kategorie), dann ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC), dann auf Rang 1 filtern.

Teil D - Expertenteil: Rekursive CTE und laufende Summe

1. Mitarbeiter-Hierarchie (rekursive CTE): Mit WITH RECURSIVE die Baumstruktur der Mitarbeitenden über employees.reports_to aufbauen (Start bei reports_to IS NULL). Je Zeile die Ebene (Tiefe) im Baum mitführen und den Namen mit der Tiefe einrücken oder als Pfad ausgeben.
2. Betreuter Umsatz je Mitarbeiter: Die Hierarchie um den gesamten von jeder Person betreuten Umsatz ergänzen (Summe aller Bestellungen dieser Person). Erklären, warum der Start-(Anker-)Teil und der rekursive Teil einer WITH RECURSIVE getrennt formuliert werden.
3. Laufende Summe: Zusätzlich je Monat den Umsatz und die kumulierte Summe über die Zeit mit SUM(...) OVER (ORDER BY monat) berechnen. In zwei Sätzen erklären, worin sich diese laufende Summe von einem GROUP BY monat unterscheidet.
4. Reflexion: In vier bis fünf Sätzen zusammenfassen, wann eine Fensterfunktion, wann eine CTE und wann eine Subquery die klarste Lösung ist, mit je einem Beispiel aus dieser Übung.

Wissenscheck

1. Worin unterscheidet sich RANK() von ROW_NUMBER() bei Gleichständen?
2. Was leistet eine Fensterfunktion, das ein GROUP BY nicht kann?
3. Aus welchen zwei Teilen besteht eine WITH RECURSIVE-CTE?
4. Wie berechnet man eine laufende Summe?
5. Wann macht eine CTE den Code klarer als eine verschachtelte Subquery?

Abgabe

• Eine SQL-Datei mit allen Abfragen samt Kommentaren.
• Eine kurze Reflexion: welche Technik (Subquery, Fensterfunktion, CTE) in welcher Aufgabe bevorzugt wurde und warum.

---

HTL Villach, 2025-2026,
https://www.htl-villach.at