Aufgabe 01 Plus - Sonderübungsblatt Subqueries

Aufgabe 01 Plus - Sonderübungen Subqueries

Worum geht es?

In dieser Zusatzübung werden Subqueries an der Northwind-Datenbank vertieft: einfache und korrelierte Subqueries, Vergleiche gegen Durchschnitts- und Maximalwerte sowie Anti-Joins mit NOT EXISTS (siehe Kapitel 1 - Komplexe Abfragen). Der Expertenteil bringt mehrstufige Anti-Joins und einen Chef-Mitarbeiter-Vergleich.

Was Sie dafür brauchen

• Northwind in PostgreSQL importiert (wie in Aufgabe 01).
• Nützliche Tabellen: orders, order_details, customers, employees, products, categories, shippers.

Welche Kompetenzen Sie erwerben und zeigen

• Sie setzen einfache und korrelierte Subqueries sicher ein.
• Sie vergleichen Werte gegen Durchschnitte und Maxima per Subquery.
• Sie verstehen Anti-Joins mit NOT EXISTS.
• Sie lösen mehrstufige Subquery-Ketten über mehrere Tabellen.

Pädagogische Einordnung

• Reproduktion: einfache Subqueries anwenden (Teil A).
• Reorganisation und Transfer: Durchschnittsvergleiche und Zuordnungen lösen (Teile B und C).
• Reflexion, Problemlösung und Urteilsbildung: mehrstufige Anti-Joins und Vergleiche entwickeln (Teil D).

Arbeitsaufträge

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

Netto-Wert durchgehend: unit_price * quantity * (1 - discount).

Teil A - Warm-up

1. Durchschnittlicher Bestellwert 1998: ein Wert. Positionen je Bestellung in einer Subquery summieren und darüber den Durchschnitt bilden. Spalte: avg_order_total_1998.
2. Größte Bestellung 1998: Subquery mit SUM(...) je order_id, dann die Zeile(n) mit dem Maximum per Subquery auf MAX(total) (kein ORDER BY ... LIMIT 1). Spalten: order_id, order_date, company_name, order_total.
3. Land mit den meisten Kunden: je country zählen, mit MAX(count_per_country) vergleichen. Spalten: country, customers_count.
4. Zusteller ohne Zustellung: NOT EXISTS gegen orders (ship_via = sh.shipper_id). Spalten: shipper_id, company_name.
5. Nie per „Speedy Express” beliefert: NOT EXISTS gegen orders mit Bedingung auf diesen Shipper. Spalten: customer_id, company_name.
6. Bestellungen Londoner Kunden 1997: Kunden per IN (city = 'London'), dann orders nach Jahr filtern. Spalten: order_id, order_date, customer_id.

Teil B - Vergleich mit Durchschnittswerten

7. Überdurchschnittlich verkaufte Produkte: SUM(quantity) je Produkt gegen den Durchschnitt aller Produkte. Spalten: product_id, product_name, units_sold, avg_units_all.
8. Teurer als der Kategorie-Durchschnitt: korreliert products.unit_price gegen AVG(unit_price) je Kategorie. Spalten: category_name, product_name, unit_price, category_avg_price.
9. Kategorien über dem Durchschnitts-Bestellwert: Bestellwert je Kategorie gegen den Durchschnitt aller Kategorien. Spalten: category_name, category_revenue, avg_revenue_all.
10. Positionen unter 10 % der Produkt-Durchschnittsmenge: od.quantity gegen 0.1 * (SELECT AVG(quantity) FROM order_details od2 WHERE od2.product_id = od.product_id). Spalten: order_id, product_id, quantity, product_avg_qty.

Teil C - Personen und Zuordnungen

11. Bester Kunde (höchster Gesamtumsatz): Umsatz je Kunde, Top-Wert per Subquery auf MAX(total_per_customer). Spalten: customer_id, company_name, address, city, country, total_revenue.
12. Mitarbeiter mit den wenigsten betreuten Kunden: distinct customers je Mitarbeiter (orders.employee_id), Minimum per Subquery. Spalten: employee_id, first_name, last_name, distinct_customers.
13. Lieferung in die Wohnstadt des Mitarbeiters: orders.ship_city gegen employees.city des orders.employee_id, Mitarbeiterstadt per Subquery. Spalten: order_id, order_date, employee, ship_city.

Teil D - Expertenteil

14. Nie „Seafood” bestellt: NOT EXISTS über die Kette orders → order_details → products → categories (Filter category_name = 'Seafood'). Spalten: customer_id, company_name.
15. USA ja, Kanada nein: Produkte, die Kunden aus den USA bestellt haben, aber keine aus Kanada (IN (...USA...) AND NOT IN (...Canada...) auf Produktebene). Spalten: product_id, product_name.
16. Fleißiger als der Chef: Chefbeziehung über employees.reports_to. Bestellungen je Mitarbeiter zählen und je Paar (Mitarbeiter vs. Chef) mit korrelierter Subquery vergleichen. Spalten: employee, boss, emp_orders, boss_orders.

Selbstkontrolle

• Wird der Netto-Wert konsequent als unit_price * quantity * (1 - discount) berechnet?
• Stehen Zeitfilter an der richtigen Stelle (in der Subquery bei NOT EXISTS)?
• Wird bei „nie gekauft/zugestellt” das Muster NOT EXISTS genutzt?
• Sind Durchschnittsvergleiche korreliert, wo sie sich auf die Außenzeile beziehen?

Wissenscheck

1. Wann ist eine Subquery korreliert?
2. Warum löst man „größte Bestellung” mit MAX(...) statt ORDER BY ... LIMIT 1, wenn Gleichstände möglich sein sollen?
3. Wie prüft man „nie X bestellt” über mehrere Tabellen hinweg?
4. Worin unterscheiden sich NOT IN und NOT EXISTS beim Umgang mit NULL?
5. Wie vergleicht man einen Mitarbeiter mit seinem Chef in einer Abfrage?

Abgabe

• Eine SQL-Datei sonderuebung_subqueries.sql mit allen Lösungen.
• Eine kurze Textdatei mit 1–2 Sätzen je Aufgabe: gewähltes Muster (IN, EXISTS, korreliert) und Begründung.

---

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