SQL Praxis: 10 GROUP BY-Übungen mit detaillierten Lösungen

Sie müssen Ihre SQL-Kenntnisse üben? Diese 10 GROUP BY-Übungsaufgaben - mit Erklärungen und Lösungen - sind ein guter Anfang!

GROUP BY GROUP BY ist eine leistungsstarke SQL-Klausel, mit der Sie Gruppen von Datensätzen erstellen und dann zusammenfassende Metriken (wie z. B. Durchschnittswerte) für diese Gruppen berechnen können. Die Beherrschung von GROUP BY ist jedoch für SQL-Lernende oft eine Herausforderung. Dennoch ist es sehr wichtig, GROUP BY zu üben, wenn Sie vorhaben, SQL zu verwenden.

In diesem Artikel haben wir mehrere GROUP BY Übungen zusammengestellt, die Ihnen helfen sollen, Ihr Verständnis für die Funktionsweise dieser Klausel zu festigen.

Inhaltsübersicht

• Einführung
• Wiederholung der GROUP BY-Klausel
• Übungen
  • Übung 1: Gesamteinnahmen für jedes Unternehmen
  • Übung 2: Produzierte Spiele pro Jahr mit durchschnittlichen Einnahmen und Kosten
  • Übung 3: Anzahl der gewinnbringenden Spiele jedes Spieltyps
  • Übung 4: Gesamteinnahmen pro Spieltyp für PS2- und PS3-Systeme
  • Übung 5: Gesamtbruttogewinn pro Unternehmen
  • Übung 6: Jährlicher Bruttogewinn pro Unternehmen
  • Übung 7: Differenz zwischen den jährlichen Bruttogewinnen
  • Übung 8: Unternehmen, die mehr als ein Spiel produzieren
  • Übung 9: Unternehmen, die "gute" Spiele mit mehr als 4 Millionen Umsatz produzieren
  • Übung 10: Führende Unternehmen nach Spieltyp
  • Übung 11: Üben Sie weiter Ihre SQL-Kenntnisse

GROUP BY-Übung

Dieser Artikel enthält 10 GROUP BY Übungen, die Sie beim Üben unterstützen. SQL zu lernen, indem man Übungen macht, ist eine der besten Möglichkeiten, seine Fähigkeiten zu verbessern.

Die Übungen in diesem Artikel stammen aus unserem SQL-Übungssatz. Es enthält mehr als 80 interaktive SQL-Übungen zu Themen wie einfache Abfragen, JOINs, Unterabfragen - und natürlich GROUP BY. Wir bieten auch andere Übungssätze an, darunter:

• Basic SQL-Praxis: Ein Speicher
• Basic SQL-Praxis: Universität
• Basic SQL-Praxis: Blog & Verkehrsdaten

Nachdem Sie nun wissen, welche SQL-Übungsressourcen zur Verfügung stehen, wollen wir uns nun mit der GROUP BY-Klausel beschäftigen. Wenn Sie noch nichts über GROUP BY wissen, lesen Sie zunächst GROUP BY in SQL Explained und 5 Examples of GROUP BY .

Die GROUP BY-Klausel

In diesen GROUP BY Übungen verwenden wir eine Tabelle namens games die Informationen über Videospiele speichert. Wir müssen betonen, dass die Namen der Spiele zwar echt sind, die anderen Felder der Tabelle aber Daten enthalten, die völlig frei erfunden sind. Unten sehen Sie eine Teilansicht der Tabelle:

Schauen wir uns kurz die GROUP BY Klausel an. Mit dieser Klausel können wir Gruppen von Datensätzen erstellen und verschiedene Metriken für jede Gruppe berechnen (z. B. Durchschnitts-, Mindest- oder Höchstwerte in jedem Satz). Im Folgenden finden Sie eine einfache Abfrage, die GROUP BY verwendet, um die Anzahl der von jeder Firma produzierten Spiele zu berechnen:

SELECT 
  company, 
  COUNT(*)
FROM games
GROUP BY company;

Mit dieser Abfrage wird die Datenbank angewiesen, Gruppen von Zeilen aus der Tabelle games zu erstellen, die den gleichen Wert in der Spalte company haben. (In der Abbildung unten befinden sich die Zeilen mit der gleichen Farbe in der gleichen Gruppe, d. h. sie wurden von der gleichen Firma hergestellt). Dann wird die Funktion COUNT(*) verwendet, um die Anzahl der Zeilen in jeder Gruppe zu zählen; dies ergibt die Anzahl der Spiele, die von jeder Firma hergestellt wurden.

Hier ist die Tabelle mit den farbcodierten Zeilen. Jede Zeile mit der gleichen Farbe gehört zur gleichen Gruppe:

Und hier ist das Ergebnis:

Übung 1: Gesamteinnahmen für jedes Unternehmen

Übung:

Ermitteln Sie den Namen und die Gesamteinnahmen für jedes Unternehmen.

Lösung:

SELECT
  company, 
  SUM(revenue)
FROM games
GROUP BY company;

Erläuterung:

In der Abfrage verwenden wir die Klausel GROUP BY company, um Gruppen von Zeilen mit demselben Wert in der Spalte company zu erstellen. Dann wird die Funktion SUM(revenue) für jede Gruppe von Zeilen ausgeführt und das Ergebnis wird zusammen mit dem Firmennamen angezeigt.

Jede Zeile des Ergebnisses ist mit einer Gruppe von Zeilen verbunden. Dies ist ein wichtiger Punkt bei der Verwendung von GROUP BY: Die einzelnen Zeilendetails werden zu einer Zeile pro Gruppe zusammengefasst, und die zurückgegebenen Daten beziehen sich auf die Gruppe von Zeilen.

Bevor wir zur nächsten Übung übergehen, möchte ich Ihnen den Kurs SQL Monthly Practice empfehlen, in dem Sie eine zusätzliche Quelle für SQL-Übungen finden. Wir veröffentlichen jeden Monat einen neuen SQL-Übungskurs.

Übung 2: Produzierte Spiele pro Jahr mit Durchschnittserlös und Kosten

Übung:

Erstellen Sie einen Bericht mit dem Produktionsjahr und der Anzahl der in diesem Jahr veröffentlichten Spiele (mit dem Namen count), dem Durchschnitt der Produktionskosten für alle in diesem Jahr produzierten Spiele (mit dem Namen avg_cost) und dem durchschnittlichen Umsatz für dieses Jahr (mit dem Namen avg_revenue).

Lösung:

SELECT   
  production_year,
  COUNT(*) AS count,
  AVG(production_cost) AS avg_cost,
  AVG(revenue) AS avg_revenue
FROM games
GROUP BY production_year;

Erläuterung:

Diese Aufgabe ist ähnlich wie die vorherige. Hier verwenden wir die Aggregatfunktionen COUNT() und AVG(), um die Metriken zu berechnen. Außerdem benennen wir die Spalten im Bericht um, um ihren Inhalt angemessen zu beschreiben (als Anzahl, avg_cost und avg_revenue). Um eine Spalte umzubenennen, verwenden wir die Klausel AS, gefolgt von dem Namen, der der Spalte zugewiesen werden soll, z. B.:

AVG(production_cost) AS avg_cost

Übung 3: Anzahl der gewinnbringenden Spiele jedes Spieltyps

Übung:

Zählen Sie, wie viele Spiele eines bestimmten Typs profitabel sind (d.h. die Einnahmen waren größer als die Produktionskosten). Geben Sie den Spieltyp und die Anzahl der profitablen Spiele ( number_of_games) für jeden Typ an.

Lösung:

SELECT    
  type,
  COUNT(*) AS number_of_games
FROM games
WHERE revenue > production_cost
GROUP BY type;

Erläuterung:

In dieser Übung wird zuerst die Bedingung WHERE ausgeführt. Sie bestimmt, welche Spiele profitabel sind (mehr Einnahmen als Produktionskosten). Danach gruppiert GROUP BY Zeilen (Spiele) desselben type. Schließlich wird die Funktion COUNT(*) auf jede Gruppe von Zeilen angewendet, um die Anzahl der profitablen Spiele jedes Typs zu ermitteln.

Übung 4: Gesamteinnahmen pro Spieltyp auf PS2- und PS3-Systemen

Übung:

Ermitteln Sie die Art der Spiele und den Gesamtumsatz, der für Spiele mit einer production_year nach 2010 und mit einer PS2 oder PS3 system erzielt wurde. Ordnen Sie das Ergebnis so, dass die Spieltypen mit dem höchsten Umsatz an erster Stelle stehen.

Lösung:

SELECT
  type,
  SUM(revenue) AS total_revenue
FROM games
WHERE production_year > 2010 AND systems IN ('PS2','PS3')
GROUP BY type;

Erläuterung:

Wie in der vorherigen Übung filtern wir zunächst die Zeilen; diesmal lauten die Bedingungen production_year > 2010 und system IN (‘PS2’, ‘PS3’). Nachdem wir die Zeilen gefiltert haben, erstellen wir mit der Klausel GROUP BY type Gruppen von Zeilen. Schließlich wenden wir die Funktion SUM(revenue) auf jede Gruppe von Zeilen an, um den Gesamtumsatz in dieser Gruppe zu erhalten.

Übung 5: Gesamtbruttogewinn pro Unternehmen

Übung:

Ermitteln Sie für alle in der Tabelle vorhandenen Unternehmen deren Namen und die Summe des Bruttogewinns über alle Jahre. (Gehen Sie davon aus, dass Bruttogewinn = Umsatz - Produktionskosten). Benennen Sie diese Spalte gross_profit_sum. Ordnen Sie die Ergebnisse nach dem Bruttogewinn, in absteigender Reihenfolge.

Lösung:

SELECT
  company,
  SUM(revenue - production_cost) AS gross_profit_sum
FROM games
GROUP BY company
ORDER BY gross_profit_sum DESC;

Erläuterung:

In der Abfrage verwenden wir den Ausdruck revenue - production_cost, um den Bruttogewinn für jedes Spiel zu berechnen. Wenn wir Gruppen unter Verwendung der Unternehmensspalte erstellen und dann den Bruttogewinn aller Spiele dieses Unternehmens summieren, erhalten wir den globalen Bruttogewinn des Unternehmens.

Beachten Sie die Klausel ORDER BY gross_profit_sum DESC. Das Schlüsselwort DESC gibt die absteigende Reihenfolge an, d. h. der höchste Wert von gross_profit_sum erscheint zuerst im Ergebnis.

Übung 6: Jährlicher Bruttogewinn pro Unternehmen

Übung:

Ermitteln Sie den jährlichen Bruttogewinn der einzelnen Unternehmen. Mit anderen Worten, wir wollen einen Bericht mit dem Firmennamen, dem Jahr und dem Bruttogewinn für dieses Jahr. Ordnen Sie den Bericht nach Firmenname und Jahr.

Lösung:

SELECT
  company,
  production_year,
  SUM(revenue - production_cost) AS gross_profit_sum
FROM  games
GROUP BY company, production_year 
ORDER BY company, production_year;

Erläuterung:

Da wir den Bruttogewinn nach Jahr aufgeschlüsselt erhalten möchten, müssen wir GROUP BY company, production_year clause verwenden. Dadurch wird für jedes Wertepaar company und production_year eine Gruppe von Zeilen erstellt. Für jede Gruppe berechnen wir dann den Ausdruck SUM(revenue - production_cost); dies ist der Bruttogewinn.

Übung 7: Differenz zwischen den jährlichen Bruttogewinnen

Übung:

Erstellen Sie einen Bericht, der den jährlichen Bruttogewinn für jedes Unternehmen, den Bruttogewinn des Vorjahres und die Differenz zwischen den beiden Jahren anzeigt. Ich schlage vor, die vorherige Abfrage als Ausgangspunkt zu verwenden.

Lösung:

WITH company_gross_profit AS (
  SELECT
    company,
    production_year AS year,
    SUM(revenue - production_cost) AS gross_profit
  FROM games
  GROUP BY company, production_year 
  ORDER BY company, production_year 
)
SELECT 
  cur.company,
  cur.year,
  cur.gross_profit,
  prev.gross_profit,
  cur.gross_profit - prev.gross_profit AS profit_delta
FROM company_gross_profit AS cur, 
LEFT JOIN company_gross_profit AS prev 
ON cur.company = prev.company AND cur.year = prev.year + 1;

Erläuterung:

Diese Abfrage basiert auf einer CTE namens company_gross_profit, die mit der Klausel WITH im ersten Teil der Abfrage erstellt wird. Danach verweist die Hauptabfrage in einem Self-Join zweimal auf die Tabelle CTE company_gross_profit ; die "linke" oder "erste" Tabelle wird als cur (current) und die andere als prev (previous) bezeichnet. Dann verbinden wir zwei Zeilen desselben Unternehmens, aber aus zusammenhängenden Jahren. Die Bedingung dafür lautet:

cur.company = prev.company AND cur.year = prev.year + 1

Schließlich ist die Spalte profit_delta ein berechnetes Feld. Sie wird aus der Differenz zwischen dem Bruttogewinn des aktuellen Jahres und dem Bruttogewinn des Vorjahres ermittelt:

cur.gross_profit - prev.gross_profit AS profit_delta

CTEs (common table expressions) sind eine erweiterte Funktion von SQL. Ich empfehle die Artikel A Guide to SQL Common Table Expressions, SQL CTEs mit Beispielen erklärt und How to Learn SQL Common Table Expressions (CTEs), wenn Sie mehr über CTEs erfahren möchten.

Übung 8: Unternehmen, die mehr als ein Spiel produzieren

Übung:

Wählen Sie für jedes Unternehmen den Namen, die Anzahl der produzierten Spiele (in der Spalte number_of_games ) und die durchschnittlichen Produktionskosten (in der Spalte avg_cost ). Geben Sie nur Unternehmen an, die mehr als ein Spiel produzieren.

Lösung:

SELECT 
  company,
  COUNT(company) AS number_of_games,
  AVG(production_cost) AS avg_cost
FROM games
GROUP BY company
HAVING COUNT(*) > 1;

Erläuterung:

In dieser Abfrage verwenden wir die HAVING Klausel anstelle der WHERE Klausel. Während WHERE einzelne Datensätze filtert, wird HAVING verwendet, um Filter auf Gruppen von Datensätzen anzuwenden.

In dieser Abfrage möchten wir einen Bericht erstellen, der nur Unternehmen anzeigt, die mehr als ein Spiel produziert haben. Nachdem wir GROUP BY company eingegeben haben, können wir die Bedingung COUNT(*) > 1 verwenden, um Unternehmen auszuschließen, die nur ein Spiel produziert haben.

In SQL können Sie Bedingungen mit Aggregatfunktionen nur direkt in der HAVING -Klausel verwenden; in der WHERE-Klausel müssen Sie eine Unterabfrage verwenden. Das liegt daran, dass WHERE in der SQL-Operationsreihenfolge vor den Aggregatfunktionen verarbeitet wird.

Wenn Sie sich bei der HAVING-Klausel unsicher sind, empfehle ich Ihnen unseren Artikel Die SQL HAVING-Klausel erklärt, in dem wir die Verwendung dieser Klausel im Detail erläutern.

Übung 9: Unternehmen, die "gute" Spiele mit mehr als 4 Millionen Umsatz produzieren

Übung:

Wir interessieren uns für gute Spiele, die zwischen 2000 und 2009 produziert wurden. Ein gutes Spiel ist ein profitables Spiel mit einer Bewertung von über 6. Zeigen Sie für jedes Unternehmen den Firmennamen, den Gesamtumsatz mit guten Spielen, die zwischen 2000 und 2009 produziert wurden (in der Spalte revenue_sum ), und die Anzahl der guten Spiele, die in diesem Zeitraum produziert wurden (in der Spalte number_of_games ). Zeigen Sie nur Unternehmen mit einem Umsatz mit guten Spielen von über 4 000 000.

Lösung:

SELECT 
  company,
  COUNT(company) AS number_of_games,
  SUM(revenue) AS revenue_sum
FROM games
WHERE production_year BETWEEN 2000 AND 2009
  AND rating > 6
  AND revenue - production_cost > 0
GROUP BY company
HAVING SUM(revenue) > 4000000;

Erläuterung:

In der Abfrage verwenden wir das Unternehmen GROUP BY, weil die Kennzahlen, die wir erhalten möchten (number_of_games und revenue_sum), auf Unternehmensebene liegen. Der interessante Punkt in dieser Abfrage sind die Filter, die wir auf die Daten anwenden. Einige von ihnen befinden sich auf Zeilenebene und müssen in der Klausel WHERE enthalten sein:

• production_year BETWEEN 2000 AND 2009
• rating > 6
• revenue - production_cost > 0

Es gibt jedoch einen weiteren Filter auf Gruppenebene, der in die Spalte HAVING eingefügt werden muss:

• SUM(revenue) > 4000000

Übung 10: Führende Unternehmen nach Spieltyp

Übung:

Geben Sie eine Liste der Unternehmen und Spieltypen zurück, bei denen das Unternehmen Marktführer ist. Ein Marktführer für eine Spielart ist ein Unternehmen, dessen Gesamtumsatz für diese Spielart den Gesamtumsatz aller anderen Unternehmen für diese Spielart übersteigt.

Geben Sie den Namen des Unternehmens, die Art des Spiels und den Gesamtumsatz des Unternehmens für diese Art von Spiel an.

Lösung:

SELECT 
  company, 
  type, 
  SUM(revenue) as total_revenue
FROM games g1
GROUP BY company, type
HAVING SUM(revenue) > ( SELECT SUM(revenue) 
                        FROM games g2
                        WHERE g2.type = g1.type
                        AND g2.company <> g1.company );

Erläuterung:

Beachten Sie zunächst die Klausel GROUP BY company, type. Wir verwenden sie, weil wir die Einnahmen des Unternehmens für alle Spiele desselben Typs benötigen.

Der interessanteste Punkt ist jedoch die Verwendung einer Unterabfrage in der Klausel HAVING. Wir wollen Unternehmen mit einer Summe von Einnahmen, die größer ist als die Gesamteinnahmen der übrigen Unternehmen; wir verwenden eine Unterabfrage, um die Gesamteinnahmen der anderen Unternehmen zu erhalten.

Die Unterabfrage verweist auf die Spalten g1.company und g1.type; dies sind die Spalten Unternehmen und Typ in der äußeren Abfrage. Diese Verweise in der Subquery ermöglichen es uns, die Gesamteinnahmen für die übrigen Unternehmen für denselben Spieltyp zu erhalten.

Übung 11: Üben Sie weiter Ihre SQL-Kenntnisse

Wir haben zehn GROUP BY Übungen mit unterschiedlichen Komplexitätsgraden behandelt, von einfachen Aggregaten bis hin zu CTEs und Unterabfragen. Das Erlernen von GROUP BY anhand von Übungsaufgaben ist eine der besten Methoden, um diese Klausel zu beherrschen. Der Artikel TOP 9 SQL GROUP BY Interview Questions führt Sie durch die häufigsten Fragen, die bei einem SQL-Vorstellungsgespräch gestellt werden; dies ist auch eine gute Möglichkeit zum Üben, wenn Sie kein Vorstellungsgespräch haben.

Unser Alle für immer SQL-Paket ist eine weitere Möglichkeit, SQL zu üben - und zwar eine sehr umfassende! Dieses Paket enthält alle unsere über 70 aktuellen SQL-Kurse in vier verschiedenen SQL-Dialekten (und Zugang zu den zukünftigen Kursen, die wir erstellen). Alle Kurse auf unserer Plattform sind praktisch und interaktiv. Wählen Sie also Ihren Übungsweg und fangen Sie an, GROUP BY zu beherrschen!