Aufgabe 01 Plus - Sonderübungsblatt Subqueries
Aufgabe 01 Plus - Sonderübungen Subqueries
Abschnitt betitelt „Aufgabe 01 Plus - Sonderübungen Subqueries“- Subqueries sicher anwenden (einfach & korreliert)
- Anti-Joins mit
NOT EXISTSverstehen - Durchschnitts-/Maximalwerte mit Subquery vergleichen
Voraussetzungen
Abschnitt betitelt „Voraussetzungen“- Northwind in PostgreSQL importiert (wie in Aufgabe 01).
- Nützliche Tabellen:
orders,order_details,customers,employees,products,categories,shippers,territories,employee_territories,region.
Teil A – Warm-up (Basic)
Abschnitt betitelt „Teil A – Warm-up (Basic)“1. Durchschnittlicher Umsatz aller Bestellungen 1998
Abschnitt betitelt „1. Durchschnittlicher Umsatz aller Bestellungen 1998“Aufgabe: Ein Wert. Netto: unit_price * quantity * (1 - discount).
Hinweis: Summiere Positionen je Bestellung in einer Subquery und bilde darüber den Durchschnitt.
Erwartete Spalten: avg_order_total_1998.
2. Größte Bestellung 1998 (OrderID, Datum, Kunde, Summe)
Abschnitt betitelt „2. Größte Bestellung 1998 (OrderID, Datum, Kunde, Summe)“Hinweis: Subquery mit SUM(…) je order_id; anschließend die Zeile(n) mit dem Maximalwert per Subquery auf MAX(total) auswählen (kein ORDER BY … LIMIT 1).
Erwartete Spalten: order_id, order_date, company_name, order_total.
3. Land mit den meisten Kunden
Abschnitt betitelt „3. Land mit den meisten Kunden“Hinweis: Zähle pro country, vergleiche mit MAX(count_per_country) via Subquery.
Erwartete Spalten: country, customers_count.
4. Welche Zusteller (Shippers) haben noch nichts zugestellt?
Abschnitt betitelt „4. Welche Zusteller (Shippers) haben noch nichts zugestellt?“Hinweis: NOT EXISTS gegen orders (Match über ship_via = sh.shipper_id).
Erwartete Spalten: shipper_id, company_name.
5. An welche Kunden wurde nie per „Speedy Express“ versendet?
Abschnitt betitelt „5. An welche Kunden wurde nie per „Speedy Express“ versendet?“Hinweis: NOT EXISTS gegen orders mit Bedingung auf den Shipper „Speedy Express“ (per Join oder Subquery auf shippers).
Erwartete Spalten: customer_id, company_name.
6. Bestellungen von Kunden aus London im Jahr 1997
Abschnitt betitelt „6. Bestellungen von Kunden aus London im Jahr 1997“Hinweis: Kunden per Subquery/IN auswählen (city = 'London'), dann orders filtern (Jahresfilter).
Erwartete Spalten: order_id, order_date, customer_id.
Teil B – Aufbau (mit Vergleich zu Durchschnittswerten)
Abschnitt betitelt „Teil B – Aufbau (mit Vergleich zu Durchschnittswerten)“7. Produkte, die überdurchschnittlich oft verkauft wurden
Abschnitt betitelt „7. Produkte, die überdurchschnittlich oft verkauft wurden“Aufgabe: Anzahl verkaufter Einheiten pro Produkt (SUM(quantity)), vergleiche mit dem Durchschnitt aller Produkte (Subquery).
Erwartete Spalten: product_id, product_name, units_sold, avg_units_all.
8. Produkte, die teurer sind als der Durchschnittspreis ihrer Kategorie
Abschnitt betitelt „8. Produkte, die teurer sind als der Durchschnittspreis ihrer Kategorie“Hinweis: Korreliert: vergleiche products.unit_price mit AVG(unit_price) pro Kategorie in einer Subquery, die auf category_id der Außenzeile referenziert.
Erwartete Spalten: category_name, product_name, unit_price, category_avg_price.
9. Kategorien mit überdurchschnittlich hohem bestellten Wert
Abschnitt betitelt „9. Kategorien mit überdurchschnittlich hohem bestellten Wert“Aufgabe: Bestellwert pro Kategorie (über products → order_details). Vergleiche Kategorie-Wert mit Durchschnitt aller Kategorien (Subquery).
Erwartete Spalten: category_name, category_revenue, avg_revenue_all.
10. Bestellungen, deren Positionen-Menge < 10 % des Durchschnitts je Produkt
Abschnitt betitelt „10. Bestellungen, deren Positionen-Menge < 10 % des Durchschnitts je Produkt“Hinweis: In der Außenabfrage steht eine einzelne Bestellposition (order_details). Vergleiche od.quantity mit 0.1 * (SELECT AVG(quantity) FROM order_details od2 WHERE od2.product_id = od.product_id).
Erwartete Spalten: order_id, product_id, quantity, product_avg_qty.
Teil C – Personen & Zuordnungen (Medium)
Abschnitt betitelt „Teil C – Personen & Zuordnungen (Medium)“11. Wer ist der beste Kunde? (höchster Gesamtumsatz)
Abschnitt betitelt „11. Wer ist der beste Kunde? (höchster Gesamtumsatz)“Hinweis: Umsatz je Kunde aggregieren; Top-Wert via Subquery auf MAX(total_per_customer) auswählen.
Erwartete Spalten: customer_id, company_name, address, city, country, total_revenue.
12. Welcher Mitarbeiter betreut die wenigsten Kunden?
Abschnitt betitelt „12. Welcher Mitarbeiter betreut die wenigsten Kunden?“Interpretation: „Betreut“ = hat Bestellungen dieser Kunden bearbeitet (orders.employee_id). Zähle distinct customers je Mitarbeiter; wähle den Minimalwert via Subquery.
Erwartete Spalten: employee_id, first_name, last_name, distinct_customers.
13. Bestellungen, die in die Wohnstadt des zuständigen Mitarbeiters geliefert wurden
Abschnitt betitelt „13. Bestellungen, die in die Wohnstadt des zuständigen Mitarbeiters geliefert wurden“Hinweis: Vergleiche orders.ship_city mit employees.city des orders.employee_id. Setze die Mitarbeiterstadt per Subquery (oder Join) – hier bitte Subquery.
Erwartete Spalten: order_id, order_date, employee, ship_city.
Teil D – Optional (für Fortgeschrittene)
Abschnitt betitelt „Teil D – Optional (für Fortgeschrittene)“14. Welche Kunden haben noch nie ein Produkt der Kategorie „Seafood“ bestellt?
Abschnitt betitelt „14. Welche Kunden haben noch nie ein Produkt der Kategorie „Seafood“ bestellt?“Hinweis: NOT EXISTS über eine Kette orders → order_details → products → categories (Filter category_name = 'Seafood').
Erwartete Spalten: customer_id, company_name.
15. Welche Produkte haben Kunden aus den USA bestellt, nicht jedoch Kunden aus Kanada?
Abschnitt betitelt „15. Welche Produkte haben Kunden aus den USA bestellt, nicht jedoch Kunden aus Kanada?“Hinweis: IN (…USA…) AND NOT IN (…Canada…) auf Produktebene anhand vorkommender order_details.
Erwartete Spalten: product_id, product_name.
16. Welcher Mitarbeiter hat mehr Bestellungen bearbeitet als sein Chef?
Abschnitt betitelt „16. Welcher Mitarbeiter hat mehr Bestellungen bearbeitet als sein Chef?“Hinweis: Chefbeziehung über employees.reports_to. Zähle Bestellungen pro Mitarbeiter und vergleiche je Paar (Mitarbeiter vs. Chef) mit korrelierter Subquery.
Erwartete Spalten: employee, boss, emp_orders, boss_orders.
Mini-Checkliste (Selbstkontrolle)
Abschnitt betitelt „Mini-Checkliste (Selbstkontrolle)“- Wird Netto-Wert konsequent als
unit_price * quantity * (1 - discount)berechnet? - Stehen Zeitfilter (z. B. Jahr 1997/1998) an der richtigen Stelle (in der Subquery, wenn mit
NOT EXISTSgearbeitet wird)? - Nutzt du bei „nie gekauft/zugestellt“ das Muster
NOT EXISTS? - Bei Vergleichen „größer/kleiner als Durchschnitt“: Ist die Subquery korreliert (bezieht sich auf das Element der Außenabfrage, z. B. Kategorie/Produkt)?
- SQL-Datei mit allen gelösten Aufgaben (Dateiname:
sonderuebung_subqueries.sql). - Kurze Textdatei mit 1–2 Sätzen pro Aufgabe: gewähltes Muster (
IN,EXISTS, korreliert`) + Begründung.
HTL Villach, 2025-2026,
https://www.htl-villach.at