SQL-Übungsfragen für Fortgeschrittene

Sind Sie bereit, Ihre SQL-Kenntnisse auf die nächste Stufe zu heben? Mit diesen Fragen für Fortgeschrittene können Sie reale Szenarien mit GROUP BY, CASE WHEN, Unterabfragen, CTEs und mehr üben.

Wenn Sie die SQL-Grundlagen bereits beherrschen und sich beim Schreiben einfacher Abfragen sicher fühlen, ist es an der Zeit, sich weiterzuentwickeln. SQL-Kenntnisse für Fortgeschrittene sind der Schlüssel zur Bearbeitung von Berichten, tieferen Analysen und komplexeren Datenaufgaben, die über einfache SELECT Anweisungen hinausgehen.

In diesem Artikel üben Sie grundlegende Techniken wie GROUP BY, CASE WHEN, Unterabfragen und Common Table Expressions (CTEs). Diese Übungen basieren auf realistischen Szenarien und sind daher ideal für angehende Datenanalysten, Entwickler oder alle, die sich auf technische Interviews vorbereiten. Versuchen Sie, jede Aufgabe zu lösen, bevor Sie die Lösung überprüfen, um Ihre Problemlösungsfähigkeiten zu verbessern.

Wenn Sie diese Konzepte auf eine strukturiertere Art und Weise vertiefen möchten, besuchen Sie den Erstellen einfacher SQL-Berichte Kurs auf LearnSQL.de. Dabei handelt es sich um einen praxisnahen, einsteigerfreundlichen Kurs, der sich darauf konzentriert, Rohdaten in aussagekräftige Berichte umzuwandeln - ein perfekter nächster Schritt, nachdem Sie diese Reihe von Übungsfragen abgeschlossen haben.

Datenbank für Übungen

Um die Übungen in diesem Artikel zu lösen, werden Sie drei Tabellen verwenden: Kunde, Produkt und Einkauf.

• Die Kundentabelle enthält: customer_id, first_name, last_name, email, signup_date, city, and country.
• Die Produkttabelle enthält: product_id, name, category, price und launch_date.
• Die Tabelle purchase zeichnet Transaktionen auf und enthält: purchase_id, customer_id, product_id, quantity, total_amount, und purchase_date.
  

Übung 1: Monatliche Umsatzzusammenfassung

Übung:
Schlüsseln Sie die Kaufdaten nach Jahr und Monat auf, und zeigen Sie die Anzahl der Bestellungen (orders) und den Gesamtumsatz (revenue) für jeden Zeitraum.

Lösung:

SELECT
	EXTRACT(YEAR FROM purchase_date) AS purchase_year,
	EXTRACT(MONTH FROM purchase_date) AS purchase_month,
	COUNT(*) AS orders,
	SUM(total_amount) AS revenue
FROM purchase
GROUP BY
	EXTRACT(YEAR FROM purchase_date),
	EXTRACT(MONTH FROM purchase_date)
ORDER BY
	purchase_year,
	purchase_month;

Erläuterung:

Um diese Aufgabe zu lösen, müssen wir die Einkäufe sowohl nach Jahr als auch nach Monat gruppieren, so dass jeder Zeitraum (z. B. Januar 2023, Februar 2023) separat behandelt wird. Wir beginnen damit, das Jahr und den Monat aus purchase_date zu extrahieren, indem wir die Funktion EXTRACT verwenden, die Teil des SQL-Standards ist und weitgehend unterstützt wird. Einige Datenbanken verfügen möglicherweise über eigene Alternativen wie DATEPART oder TO_CHAR. Die Gruppierung nach purchase_year und purchase_month verhindert, dass alle Januar-Käufe aus verschiedenen Jahren in einer Gruppe zusammengefasst werden. Anschließend zählen wir die Anzahl der Bestellungen und summieren den Gesamtumsatz für jeden Zeitraum.

Übung 2: Aktuelle Bestellungen finden

Übung:

Suchen Sie alle Einkäufe der letzten 30 Tage. Zeigen Sie alle Spalten der Einkaufstabelle an.

Lösung:

SELECT *
FROM purchase
WHERE purchase_date >= CURRENT_DATE - INTERVAL 30 DAY;

Erläuterung:

Um die Einkäufe der letzten 30 Tage zu finden, filtern wir die Tabelle purchase mit einer Datumsbedingung. Wir vergleichen purchase_date mit dem aktuellen Datum abzüglich eines 30-Tage-Intervalls. Das Schlüsselwort CURRENT_DATE gibt das heutige Datum an, und INTERVAL 30 DAY ist eine Standardmethode zur Subtraktion von Tagen in ANSI SQL. Dadurch wird sichergestellt, dass nur Zeilen zurückgegeben werden, in denen der Kauf innerhalb der letzten 30 Tage stattgefunden hat. Einige Datenbanken verwenden möglicherweise eine etwas andere Syntax für Datumsintervalle, aber die Logik bleibt dieselbe.

Übung 3: Produkte, deren Preis über dem Durchschnitt ihrer Kategorie liegt

Übung:

Geben Sie alle Produkte zurück, die teurer sind als der Durchschnittspreis in ihrer eigenen Kategorie. Zeigen Sie alle Spalten aus der Einkaufstabelle an.

Lösung:

SELECT *
FROM product p
WHERE price >
  	(SELECT AVG(price)
   	FROM product
   	WHERE category = p.category);

Erläuterung:

Um diese Aufgabe zu lösen, vergleichen wir den Preis jedes Produkts mit dem Durchschnittspreis der Produkte innerhalb der gleichen Kategorie. Wir verwenden eine Unterabfrage, die den Durchschnittspreis price für eine bestimmte category berechnet und dann prüft, ob der Preis des aktuellen Produkts über diesem Durchschnitt liegt. Dies ist eine korrelierte Unterabfrage - sie wird einmal für jede Zeile in der äußeren Abfrage ausgeführt und verwendet p.category, um Kategorien abzugleichen. Wir verwenden SELECT *, um alle Spalten aus der Produkttabelle für Produkte zurückzugeben, die die Bedingung erfüllen.

Übung 4: Preissegmente: Budget, Mittel, Premium

Übung:

Ordnen Sie jedes Produkt einem Preissegment zu: "Budget" für unter $20, "Mittel" für $20 - 99,99 und "Premium" für $100 und mehr.

Lösung:

SELECT
	product_id,
	name,
	price,
	CASE
    		WHEN price < 20 THEN 'budget'
    		WHEN price BETWEEN 20 AND 99.99 THEN 'middle'
    		ELSE 'premium'
	END AS price_segment
FROM product;

Erläuterung:

In dieser Abfrage verwenden wir den CASE-Ausdruck, um jedes Produkt auf der Grundlage seines price einem Preissegment zuzuordnen. Die Syntax CASE funktioniert wie eine if-else-Struktur: Sie prüft die Bedingungen der Reihe nach und gibt den entsprechenden Wert zurück. In diesem Fall werden Produkte unter $20 mit 'budget' gekennzeichnet, Produkte zwischen $20 und $99,99 mit 'middle' und Produkte über $100 mit 'premium'. Das Ergebnis enthält die ursprünglichen Produktdetails zusammen mit einer neuen Spalte price_segment.

Übung 5: Kunden nach Region gruppieren

Übung:
Gruppieren Sie Kunden in Regionen auf der Grundlage ihres Landes. Nehmen Sie an, dass Länder wie 'USA', 'Canada' und 'Mexico' zu 'North America' gehören, während alle anderen unter 'Other' fallen. Zählen Sie für jede Region, wie viele Kunden ihr zugewiesen sind. Das Ergebnis sollte region und customer_count einschließen.

Lösung:

SELECT
	CASE
    	WHEN country IN ('USA', 'Canada', 'Mexico') THEN 'North America'
    	ELSE 'Other'
	END AS region,
	COUNT(*) AS customer_count
FROM customer
GROUP BY
	CASE
    	WHEN country IN ('USA', 'Canada', 'Mexico') THEN 'North America'
    	ELSE 'Other'
	END;

Erläuterung:

Diese Abfrage verwendet den Ausdruck CASE, um Länder in benutzerdefinierte Regionen zu gruppieren - 'North America' für 'USA', 'Canada' und 'Mexico' und 'Other' für den Rest. CASE wird häufig verwendet, um eigene Kategorien auf der Grundlage von Spaltenwerten zu erstellen. Sobald wir diese benutzerdefinierten Regionen definiert haben, können wir nach ihnen gruppieren, um zu zählen, wie viele Kunden in die einzelnen Regionen fallen. Dies ist ein gängiges Muster bei der Arbeit mit geografischen Daten, Preisstufen oder einer benutzerdefinierten Klassifizierungslogik.

Übung 6: Kundenausgaben nach Kategorie

Übung:

Berechnen Sie für jeden Kunden, wie viel er für Produkte in den Kategorien "Elektronik", "Kleidung" und "Wohnen" ausgegeben hat. Das Ergebnis sollte customer_id, electronics_spend, clothing_spend und home_spend als Spaltennamen enthalten.

Lösung:

SELECT
	customer_id,
	SUM(CASE 
WHEN category = 'Electronics' 
THEN total_amount ELSE 0 END) AS electronics_spend,
	SUM(CASE 
WHEN category = 'Clothing'	
THEN total_amount ELSE 0 END) AS clothing_spend,
	SUM(CASE 
WHEN category = 'Home'    	
THEN total_amount ELSE 0 END) AS home_spend
FROM purchase  p
JOIN product pr 
ON p.product_id = pr.product_id
GROUP BY customer_id;

Erläuterung:

In dieser Abfrage berechnen wir, wie viel jeder Kunde für Produkte aus bestimmten Kategorien ausgegeben hat, indem wir CASE WHEN innerhalb von Aggregatfunktionen verwenden. CASE ermöglicht es uns, die Kategorie jedes Produkts zu überprüfen und total_amount nur dann zurückzugeben, wenn sie mit der Kategorie übereinstimmt, an der wir interessiert sind - andernfalls wird 0 zurückgegeben. Wir wiederholen dieses Muster für 'Electronics', 'Clothing' und 'Home' und verpacken jedes in ein SUM(), um die Gesamtausgaben pro Kategorie zu erhalten. Das Ergebnis enthält eine Zeile pro customer_id mit separaten Spalten für die Ausgaben in jeder Kategorie. Diese Technik ist nützlich für die Erstellung von Pivot-ähnlichen Zusammenfassungen direkt in SQL.

Übung 7: Durchschnittliche Ausgaben pro Kunde

Übung:
Berechnen Sie den Gesamtbetrag, den jeder Kunde ausgegeben hat, und geben Sie dann den Durchschnitt der Gesamtsummen dieser Kunden zurück. Das Endergebnis soll eine einzige Spalte enthalten: avg_customer_spend.

Lösung:

WITH customer_spend AS (
	SELECT customer_id, SUM(total_amount) AS spend
	FROM purchase
	GROUP BY customer_id
)
SELECT AVG(spend) AS avg_customer_spend
FROM customer_spend;

Erläuterung:

Um diese Aufgabe zu lösen, berechnen wir zunächst, wie viel jeder Kunde insgesamt ausgegeben hat, indem wir die Daten von purchase nach customer_id gruppieren und die Daten von total_amount summieren. Wir tun dies in einer Common Table Expression (CTE) namens customer_spend. In der Hauptabfrage berechnen wir dann den Durchschnitt dieser Summen mit AVG(spend) und geben ihn als avg_customer_spend zurück. Dieser Ansatz hilft Ihnen, Zwischenberechnungen zu trennen und Ihre Abfrage sauber und lesbar zu halten.

Übung 8: Überdurchschnittlich viele Kunden

Übung:

Ermitteln Sie den Gesamtbetrag, den jeder Kunde ausgegeben hat, und die durchschnittlichen Ausgaben aller Kunden. Geben Sie nur die Kunden zurück, deren Gesamtausgaben über dem Durchschnitt liegen. Das Ergebnis sollte die Spalten customer_id und total_spend enthalten.

Lösung:

WITH customer_spend AS ( -- total spend per customer
	SELECT
    	customer_id,
    		SUM(total_amount) AS total_spend
	FROM purchase
	GROUP BY customer_id
), avg_spend AS ( -- 2) average of those totals
	SELECT
    	AVG(total_spend) AS avg_total_spend
	FROM customer_spend
)
SELECT
	cs.customer_id,
	cs.total_spend
FROM customer_spend cs, avg_spend a -- join to include the average in each row
WHERE cs.total_spend > a.avg_total_spend
ORDER BY cs.total_spend DESC;

Erläuterung:

Zunächst werden die Gesamtausgaben pro Kunde mit einem CTE namens customer_spend berechnet. Dann berechnen wir in einer zweiten CTE mit der Bezeichnung avg_spend den Durchschnitt dieser Gesamtsummen. In der Hauptabfrage vergleichen wir die Gesamtausgaben jedes Kunden mit dem Durchschnitt, indem wir beide CTEs verbinden. Kunden, deren total_spend größer als der Durchschnitt ist, werden zusammen mit ihrer customer_id zurückgegeben. Die Verwendung von zwei CTEs macht die Logik einfacher zu verfolgen und hält die Abfrage sauber.

Übung 9: Produktverkäufe als Prozentsatz der Gesamtsumme

Übung:

Berechnen Sie den Gesamtumsatz für jedes Produkt. Berechnen Sie dann für jedes Produkt, wie viel Prozent es zum Gesamtumsatz beiträgt. Das Ergebnis sollte product_id, revenue, und pct_of_total enthalten.

Lösung:

WITH product_sales AS (
	SELECT product_id, SUM(total_amount) AS revenue
	FROM purchase
	GROUP BY product_id
), total_revenue AS (
	SELECT SUM(revenue) AS total
	FROM product_sales
)
SELECT
	ps.product_id,
	ps.revenue,
	ROUND(100.0 * ps.revenue / tr.total, 2) AS pct_of_total
FROM product_sales ps
CROSS JOIN total_revenue tr
ORDER BY pct_of_total DESC;

Erläuterung:

Wir beginnen mit der Berechnung des Gesamtumsatzes pro Produkt mithilfe eines Common Table Expression (CTE) namens product_sales. Anschließend erstellen wir eine weitere CTE mit dem Namen total_revenue, um den Gesamtumsatz aller Produkte zu berechnen. In der letzten Abfrage verbinden wir beide CTEs mit CROSS JOIN, so dass jede Produktzeile Zugriff auf den Gesamterlös hat. Anschließend berechnen wir den prozentualen Beitrag jedes Produkts, indem wir seinen Umsatz durch den Gesamtwert dividieren und mit 100 multiplizieren. Das Ergebnis umfasst product_id, revenue und pct_of_total, sortiert vom höchsten zum niedrigsten Prozentsatz.

Übung 10: Aktiv, aber nicht im Dezember

Übung:

Finden Sie die Kunden, die im Dezember mehr als eine Bestellung aufgegeben, aber nie etwas gekauft haben. Das Ergebnis sollte customer_id, first_name, und last_name enthalten.

Lösung:

WITH multi_order AS (
	SELECT customer_id
	FROM purchase
	GROUP BY customer_id
	HAVING COUNT(*) > 1
), december_buyers AS (
	SELECT DISTINCT customer_id
	FROM purchase
	WHERE EXTRACT(MONTH FROM purchase_date) = 12
)
SELECT
	c.customer_id,
	c.first_name,
	c.last_name
FROM customer c
JOIN multi_order mo 
ON c.customer_id = mo.customer_id
LEFT JOIN december_buyers d 
ON c.customer_id = d.customer_id
WHERE d.customer_id IS NULL;

Erläuterung:

Diese Abfrage findet Kunden, die im Dezember mehr als eine Bestellung aufgegeben, aber keinen Kauf getätigt haben. Zunächst identifiziert die multi_order CTE Kunden mit mehr als einem Kauf, indem sie nach customer_id gruppiert und HAVING COUNT(*) > 1 verwendet. Die december_buyers CTE wählt alle Kunden aus, die im Dezember mindestens einen Kauf getätigt haben, indem sie den Monat aus purchase_date extrahiert. In der letzten Abfrage verknüpfen wir die Tabelle customer mit multi_order, um die relevanten Kunden zu erhalten, und verwenden dann einen LEFT JOIN mit december_buyers, um zu prüfen, wer im Dezember keinen Kauf getätigt hat. Wir filtern diejenigen heraus, die einen Kauf getätigt haben, indem wir WHERE d.customer_id IS NULL überprüfen. Das Ergebnis enthält nur customer_id, first_name und last_name.

Weitere SQL-Praxis

Regelmäßiges Üben von SQL ist eine der effektivsten Methoden, um vom theoretischen Verständnis zur Sicherheit in realen Szenarien zu gelangen. Die Übungen in diesem Artikel behandelten Kernthemen für Fortgeschrittene wie GROUP BY, CASE WHEN, Unterabfragen und CTEs - alles Fertigkeiten, die bei Datenanalysen, Berichten und technischen Interviews häufig vorkommen. Wenn Sie bis hierher gekommen sind, sind Sie auf dem besten Weg, SQL-Kenntnisse zu erwerben.

Aber hören Sie hier nicht auf. Beständigkeit ist der Schlüssel zum Erlernen von SQL, und der beste Weg, sich weiter zu verbessern, ist das Lösen praktischer Probleme. Wenn Sie auf der Suche nach strukturierten, praktischen Übungen mit sofortigem Feedback sind, empfehlen wir Ihnen den SQL-Praxis Track auf LearnSQL.de. Er soll Ihnen dabei helfen, Ihre Fähigkeiten durch echte, aufeinander aufbauende Abfrageaufgaben zu verbessern - keine Floskeln, nur Praxis, die zählt.

Ganz gleich, ob Sie einen Job im Bereich Datenverarbeitung anstreben, Berichte automatisieren oder effektiver mit Datenbanken arbeiten möchten, der SQL-Praxis Track ist der nächste Schritt, den Sie machen sollten.