Aufgabe 02 - Komplexe Abfragen
Aufgabe 02 - Komplexe Abfragen
Abschnitt betitelt „Aufgabe 02 - Komplexe Abfragen“- Vertiefung Subqueries (korreliert, verschachtelt)
- Fensterfunktionen (Ranking, Laufende Summen, LAG/LEAD)
- CTEs (Common Table Expressions, auch rekursiv)
- Kombination von Aggregation + Fenster
Voraussetzungen & Setup
Abschnitt betitelt „Voraussetzungen & Setup“- Northwind ist wie in Aufgabe 01 importiert (gleiche Container/DB).
- Relevante Tabellen:
customers,orders,order_details,products,categories,employees,suppliers.
Übungen
Abschnitt betitelt „Übungen“Übung 1 - Top-Produkte pro Jahr (Fensterfunktion)
Abschnitt betitelt „Übung 1 - Top-Produkte pro Jahr (Fensterfunktion)“Operator: Analysieren
Ermitteln Sie für jedes Jahr (aus order_date) das umsatzstärkste Produkt. Verwenden Sie eine Fensterfunktion mit RANK() über PARTITION BY year. Geben Sie year, product_name, revenue und rank aus. Filtern Sie danach auf rank = 1.
Übung 2 - Kundenumsatz im Vergleich (Fensterfunktion)
Abschnitt betitelt „Übung 2 - Kundenumsatz im Vergleich (Fensterfunktion)“Operator: Bewerten
Listen Sie alle Bestellungen mit order_id, customer_id, order_date, net_order_value. Fügen Sie hinzu:
- Gesamtumsatz des Kunden (
SUM(...) OVER (PARTITION BY customer_id)) - Rang der Bestellung innerhalb des Kunden nach Bestellwert (
RANK() OVER (...))
Interpretieren Sie: Welcher Kunde hat viele kleine, welcher wenige große Bestellungen?
Übung 3 - Veränderung zum Vormonat (LAG)
Abschnitt betitelt „Übung 3 - Veränderung zum Vormonat (LAG)“Operator: Analysieren
Ermitteln Sie pro Monat den Gesamtumsatz. Berechnen Sie mit LAG() den Umsatz des Vormonats und die Differenz (delta). Sortieren Sie chronologisch.
Übung 4 - Erstbestellung je Kunde (CTE + korrelierte Subquery)
Abschnitt betitelt „Übung 4 - Erstbestellung je Kunde (CTE + korrelierte Subquery)“Operator: Konstruieren
Variante A: Mit einer korrelierten Subquery das Datum der Erstbestellung je Kunde bestimmen.
Variante B: Mit einer CTE, die pro Kunde das MIN(order_date) berechnet.
Vergleichen Sie Lesbarkeit und Performance.
Übung 5 - „Best Seller pro Kategorie“ mit CTE
Abschnitt betitelt „Übung 5 - „Best Seller pro Kategorie“ mit CTE“Operator: Anwenden
Bestimmen Sie pro Kategorie das umsatzstärkste Produkt.
- Schritt 1: CTE
category_salesmit Umsatz pro Produkt und Kategorie. - Schritt 2: Ranking per
ROW_NUMBER()je Kategorie. - Schritt 3: Filtern auf Rang 1.
Übung 6 - Umsatz-Hierarchie nach Mitarbeitenden (rekursive CTE)
Abschnitt betitelt „Übung 6 - Umsatz-Hierarchie nach Mitarbeitenden (rekursive CTE)“Operator: Analysieren
In Northwind gibt es eine Hierarchie: employees.reports_to. Bauen Sie mit einer rekursiven CTE die Baumstruktur der Mitarbeitenden auf. Ergänzen Sie pro Mitarbeiter den gesamten betreuten Umsatz (Summe aller Bestellungen).
Tipp: Start bei reports_to IS NULL (oberste Ebene).
Übung 7 - Kombination Aggregat + Fenster
Abschnitt betitelt „Übung 7 - Kombination Aggregat + Fenster“Operator: Bewerten
Berechnen Sie für jede Bestellung:
order_id,customer_id,order_date,order_value- Durchschnittlicher Bestellwert aller Bestellungen (
AVG(...) OVER ()) - Differenz zum Durchschnitt
Diskutieren Sie: Wozu ist diese Sicht nützlich? (z. B. Outlier Detection).
- Vergleichen Sie die Ergebnisse von GROUP BY vs. Fensterfunktionen bei denselben Aufgaben.
- Diskutieren Sie, wann CTEs den Code verständlicher machen als Subqueries.
- SQL-Queries mit Kommentaren
- Kurze Reflexion: Welche Technik (Subquery, Fenster, CTE) haben Sie in welcher Aufgabe bevorzugt - und warum?
HTL Villach, 2025-2026,
https://www.htl-villach.at