SQL Practice : 10 GROUP BY Exercices pratiques avec solutions détaillées

Besoin de pratiquer vos compétences en SQL ? Ces 10 exercices d'entraînement GROUP BY - avec explications et solutions - sont un bon début !

GROUP BY est une clause SQL puissante qui vous permet de créer des groupes d'enregistrements et de calculer des mesures récapitulatives (telles que des moyennes) pour ces groupes. Cependant, GROUP BY est souvent difficile à maîtriser pour les apprenants de SQL. Pourtant, la pratique de GROUP BY est très importante si vous envisagez d'utiliser SQL.

Dans cet article, nous avons rassemblé plusieurs exercices d'entraînement GROUP BY pour vous aider à solidifier votre compréhension du fonctionnement de cette clause.

Table des matières

• Introduction
• Revue de la clause GROUP BY
• Exercices
  • Exercice 1 : recettes totales pour chaque entreprise
  • Exercice 2 : jeux produits par année avec revenus et coûts moyens
  • Exercice 3 : Nombre de jeux rentables pour chaque type de jeu
  • Exercice 4 : recettes totales par type de jeu dans les systèmes PS2 et PS3
  • Exercice 5 : marge brute totale par entreprise
  • Exercice 6 : Marge brute annuelle par entreprise
  • Exercice 7 : Différence entre les bénéfices bruts annuels
  • Exercice 8 : Entreprises produisant plus d'un jeu
  • Exercice 9 : entreprises produisant de " bons " jeux avec un revenu de 4 millions et plus
  • Exercice 10 : entreprises leaders par type de jeu
  • Exercice 11 : Continuez à pratiquer vos compétences SQL

Entraînement GROUP BY

Cet article contient 10 exercices GROUP BY pour vous aider à vous entraîner. Apprendre SQL en faisant des exercices est l'une des meilleures façons d'affiner vos compétences.

Les exercices de cet article proviennent de notre Exercices Pratiques de SQL. Il contient plus de 80 exercices SQL interactifs couvrant des sujets tels que les requêtes simples, les JOIN, les sous-requêtes, - et, bien sûr, GROUP BY. Nous proposons également d'autres séries d'exercices, notamment

• Basic La pratique du SQL: Un magasin
• Base La pratique du SQL: Université
• Basic La pratique du SQL: Blog et données de trafic

Maintenant que vous connaissez les ressources disponibles pour les exercices SQL, nous allons nous pencher sur la clause GROUP BY. Si vous ne savez rien de GROUP BY, commencez par lire GROUP BY in SQL Explained et 5 Examples of GROUP BY .

La clause GROUP BY

Dans ces exercices d'entraînement GROUP BY, nous utiliserons une table appelée games qui contient des informations sur les jeux vidéo. Nous devons insister sur le fait que, bien que les noms des jeux soient réels, les autres champs de la table contiennent des données complètement inventées. Vous trouverez ci-dessous une vue partielle de la table :

Passons brièvement en revue la clause GROUP BY. Cette clause nous permet de créer des groupes d'enregistrements et de calculer différentes mesures pour chaque groupe (telles que la moyenne, le minimum ou le maximum des valeurs de chaque ensemble). Vous trouverez ci-dessous une requête simple qui utilise GROUP BY pour calculer le nombre de jeux produits par chaque société :

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

Cette requête demande à la base de données de créer des groupes de lignes de la table games qui ont la même valeur dans la colonne company. (Dans l'image ci-dessous, les lignes de même couleur sont dans le même groupe, c'est-à-dire qu'elles sont produites par la même société). La fonction COUNT(*) est ensuite utilisée pour compter la quantité de lignes dans chaque groupe ; elle renvoie le nombre de jeux produits par chaque société.

Voici le tableau avec les lignes codées par couleur. Chaque ligne de même couleur appartient au même groupe :

Et voici le résultat :

Exercice 1 : Recettes totales pour chaque entreprise

Exercice :

Obtenez le nom et le revenu total de chaque entreprise.

Solution :

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

Explication :

Dans la requête, nous utilisons la clause GROUP BY company pour créer des groupes de lignes ayant la même valeur dans la colonne company. Ensuite, la fonction SUM(revenue) est exécutée pour chaque groupe de lignes et le résultat est affiché avec le nom de la société.

Chaque ligne du résultat est associée à un groupe de lignes. Il s'agit d'un point important lors de l'utilisation de GROUP BY: les détails des lignes individuelles sont regroupés en une seule ligne par groupe, et les données renvoyées concernent le groupe de lignes.

Avant de passer à l'exercice suivant, j'aimerais vous recommander le cours Pratique SQL mensuelle, où vous trouverez une source supplémentaire d'exercices pratiques SQL. Nous publions un nouveau cours de pratique SQL chaque mois.

Exercice 2 : Jeux produits par année avec revenus et coûts moyens

Exercice :

Générer un rapport avec l'année de production et le nombre de jeux sortis cette année (nommé count), la moyenne des coûts de production pour tous les jeux produits cette année (nommé avg_cost) et le revenu moyen pour cette année (nommé avg_revenue).

Solution :

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

Explication :

Cet exercice est similaire au précédent. Ici, nous utilisons les fonctions d'agrégation COUNT() et AVG() pour calculer les mesures. En outre, nous renommons les colonnes du rapport pour décrire leur contenu de manière appropriée (comme count, avg_cost et avg_revenue). Pour renommer une colonne, nous utilisons la clause AS suivie du nom à attribuer à la colonne, par exemple :

AVG(production_cost) AS avg_cost

Exercice 3 : Nombre de jeux rentables de chaque type de jeu

Exercice :

Comptez le nombre de jeux d'un type donné qui sont rentables (c'est-à-dire dont les recettes sont supérieures au coût de production). Indiquez le type de jeu et le nombre de jeux rentables (nommés number_of_games) pour chaque type.

Solution :

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

Explication :

Dans cet exercice, la condition WHERE est exécutée en premier. Elle détermine les jeux qui sont rentables (dont les recettes sont supérieures au coût de production). Ensuite, GROUP BY regroupe les lignes (jeux) du même type. Enfin, la fonction COUNT(*) est appliquée à chaque groupe de lignes pour obtenir le nombre de jeux rentables de chaque type.

Exercice 4 : Recettes totales par type de jeu dans les systèmes PS2 et PS3

Exercice :

Obtenez le type de jeux et le revenu total généré pour les jeux dont le site production_year est postérieur à 2010 et qui sont équipés d'une PS2 ou d'une PS3 system. Classez les résultats de manière à ce que les types de jeux ayant les revenus les plus élevés apparaissent en premier.

Solution :

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

Explication :

Comme dans l'exercice précédent, nous commençons par filtrer les lignes ; cette fois, les conditions sont production_year > 2010 et system IN (‘PS2’, ‘PS3’). Une fois les lignes filtrées, nous avons créé des groupes de lignes à l'aide de la clause de type GROUP BY. Enfin, nous appliquons la fonction SUM(revenue) à chaque groupe de lignes pour obtenir le revenu total de ce groupe.

Exercice 5 : Marge brute totale par entreprise

Exercice :

Pour toutes les entreprises présentes dans le tableau, obtenez leur nom et la somme des bénéfices bruts pour toutes les années. (Supposez que la marge brute = recettes - coûts de production). Nommez cette colonne gross_profit_sum. Classez les résultats par marge brute, dans l'ordre décroissant.

Solution :

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

Explication :

Dans la requête, nous utilisons l'expression revenue - production_cost pour calculer la marge brute de chaque jeu. Si nous créons des groupes à l'aide de la colonne société et que nous additionnons la marge brute de tous les jeux de cette société, nous obtenons la marge brute globale de la société.

Remarquez la clause ORDER BY gross_profit_sum DESC. Le mot-clé DESC indique un ordre décroissant ; en d'autres termes, la valeur la plus élevée de gross_profit_sum apparaît en premier dans le résultat.

Exercice 6 : Marge brute annuelle par entreprise

Exercice :

Obtenir la marge brute annuelle de chaque entreprise. En d'autres termes, nous voulons un rapport contenant le nom de l'entreprise, l'année et le bénéfice brut pour cette année. Ordonnez le rapport par nom d'entreprise et par année.

Solution :

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

Explication :

Puisque nous voulons obtenir la marge brute ventilée par année, nous devons utiliser GROUP BY company, production_year clause. Cela crée un groupe de lignes pour chaque paire de valeurs company et production_year. Pour chaque groupe, nous calculons ensuite l'expression SUM(revenue - production_cost); il s'agit de la marge brute.

Exercice 7 : Différence entre les bénéfices bruts annuels

Exercice :

Générez un rapport indiquant la marge brute annuelle de chaque entreprise, la marge brute de l'année précédente et la différence entre les deux années. Je suggère d'utiliser la requête précédente comme point de départ.e

Solution :

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;

Explication :

Cette requête est basée sur un site CTE appelé company_gross_profit, qui est créé à l'aide de la clause WITH dans la première partie de la requête. Ensuite, la requête principale fait référence à la table CTE company_gross_profit deux fois dans une jointure automatique ; la table "gauche" ou "première" est appelée cur (actuelle) et l'autre est appelée prev (précédente). Ensuite, nous joignons deux lignes de la même entreprise mais provenant d'années contiguës. La condition pour ce faire est la suivante :

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

Enfin, la colonne profit_delta est un champ calculé. Elle est obtenue en utilisant la différence entre la marge brute de l'année en cours et celle de l'année précédente :

cur.gross_profit - prev.gross_profit AS profit_delta

Les CTE, ou expressions de table commune, sont une fonctionnalité avancée de SQL. Je vous recommande les articles A Guide to SQL Common Table Expressions, SQL CTEs Explained with Examples, et How to Learn SQL Common Table Expressions (CTEs ) si vous voulez en savoir plus sur les CTEs.

Exercice 8 : Entreprises produisant plus d'un jeu

Exercice :

Pour chaque entreprise, sélectionnez son nom, le nombre de jeux qu'elle a produits (colonne number_of_games ) et le coût moyen de production (colonne avg_cost ). N'indiquez que les entreprises produisant plus d'un jeu.

Solution :

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

Explication :

Dans cette requête, nous utilisons la clause HAVING au lieu de la clause WHERE. Alors que WHERE filtre des enregistrements individuels, HAVING est utilisé pour appliquer des filtres à des groupes d'enregistrements.

Dans cette requête, nous voulons un rapport montrant uniquement les entreprises qui ont produit plus d'un jeu. Après avoir consulté GROUP BY company, nous pouvons utiliser la condition COUNT(*) > 1 pour écarter les sociétés qui n'ont produit qu'un seul jeu.

En SQL, vous ne pouvez utiliser directement des conditions impliquant des fonctions d'agrégation que dans la clause HAVING; dans la clause WHERE, vous devez utiliser une sous-requête. En effet, la clause WHERE est traitée avant les fonctions agrégées dans l'ordre des opérations SQL.

Si vous avez des doutes sur la clause HAVING, je vous recommande notre article La clause SQL HAVING expliquée, dans lequel nous expliquons en détail comment utiliser cette clause.

Exercice 9 : entreprises produisant de "bons" jeux avec des revenus de plus de 4 millions d'euros

Exercice :

Nous nous intéressons aux bons jeux produits entre 2000 et 2009. Un bon jeu est un jeu rentable dont la note est supérieure à 6. Pour chaque entreprise, indiquez le nom de l'entreprise, son revenu total provenant des bons jeux produits entre 2000 et 2009 (colonne revenue_sum ) et le nombre de bons jeux qu'elle a produits au cours de cette période (colonne number_of_games ). N'indiquez que les entreprises dont les recettes provenant des bons jeux sont supérieures à 4 000 000.

Solution :

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;

Explication :

Dans la requête, nous utilisons l'entreprise GROUP BY parce que les mesures que nous voulons obtenir (number_of_games et revenue_sum) se situent au niveau de l'entreprise. Ce qui est intéressant dans cette requête, ce sont les filtres que nous appliquons aux données. Certains d'entre eux se situent au niveau de la ligne et doivent figurer dans la clause WHERE:

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

Cependant, il existe un autre filtre au niveau du groupe, qui doit être placé dans la colonne HAVING:

• SUM(revenue) > 4000000

Exercice 10 : Sociétés leaders par type de jeu

Exercice :

Renvoyer une liste des entreprises et des types de jeux pour lesquels l'entreprise est leader sur le marché. Un leader du marché pour un type de jeu est une entreprise dont le revenu total pour ce type de jeu dépasse le revenu total de toutes les autres entreprises pour ce type de jeu.

Indiquez le nom de l'entreprise, le type de jeu et le revenu total de l'entreprise pour ce type de jeu.

Solution :

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 );

Explication :

Tout d'abord, notez la clause GROUP BY company, type. Nous l'utilisons parce que nous voulons connaître les recettes de l'entreprise pour tous les jeux du même type.

Cependant, le point le plus intéressant est l'utilisation d'une sous-requête dans la clause HAVING. Nous voulons des entreprises dont la somme des revenus est supérieure au total des revenus des autres entreprises ; nous utilisons une sous-requête pour obtenir le total des revenus des autres entreprises.

La sous-requête fait référence aux colonnes g1.company et g1.type; il s'agit des colonnes "entreprise" et "type" de la requête externe. Ces références dans la sous-requête nous permettent d'obtenir le revenu total des autres entreprises pour le même type de jeu.

Exercice 11 : Continuez à pratiquer vos compétences en SQL

Nous avons couvert dix exercices GROUP BY avec différents niveaux de complexité, des simples agrégats aux CTE et aux sous-requêtes. Apprendre le GROUP BY à l'aide d'exercices d'entraînement est l'une des meilleures approches pour maîtriser cette clause. L'article TOP 9 SQL GROUP BY Interview Questions vous guide à travers les questions les plus courantes posées lors d'un entretien d'embauche en SQL ; c'est également un excellent moyen de s'entraîner si vous n'avez pas d'entretien à venir.

Notre Tout à vie SQL Package est un autre moyen de s'entraîner au langage SQL - et un moyen très complet ! Ce pack contient l'ensemble de nos 70+ cours SQL actuels dans quatre dialectes SQL différents (et l'accès aux futurs cours que nous créerons). Tous les cours sur notre plateforme sont pratiques et interactifs. Alors, choisissez votre chemin de pratique et commencez à être génial dans l'utilisation de GROUP BY!