Questions d'entraînement SQL de niveau intermédiaire

Prêt à passer au niveau supérieur de vos compétences en SQL ? Ces questions intermédiaires vous aideront à mettre en pratique des scénarios réels utilisant GROUP BY, CASE WHEN, des sous-requêtes, des CTE, etc.

Si vous maîtrisez déjà les bases du langage SQL et que vous vous sentez à l'aise pour écrire des requêtes simples, il est temps de passer à la vitesse supérieure. Les compétences SQL intermédiaires sont essentielles pour gérer les rapports du monde réel, les analyses plus approfondies et la résolution de tâches de données plus complexes qui vont au-delà des instructions de base SELECT.

Dans cet article, vous pratiquerez des techniques essentielles telles que GROUP BY, CASE WHEN, les sous-requêtes et les expressions de table communes (CTE). Ces exercices sont basés sur des scénarios réalistes, ce qui les rend idéaux pour les analystes de données en herbe, les développeurs ou toute personne se préparant à des entretiens techniques. Essayez de résoudre chaque défi avant de vérifier la solution afin d'affiner vos compétences en matière de résolution de problèmes.

Si vous souhaitez approfondir ces concepts de manière plus structurée, consultez le cours de Création de rapports basiques en SQL sur LearnSQL.fr. Il s'agit d'un cours pratique, adapté aux débutants, qui se concentre sur la transformation de données brutes en rapports pertinents - une étape parfaite après avoir terminé cette série de questions pratiques.

Base de données pour les exercices

Pour résoudre les exercices de cet article, vous utiliserez trois tables : client, produit et achat.

• La table client comprend : customer_id, first_name, last_name, email, signup_date, city, and country.
• La table des produits contient les éléments suivants product_id, name, category, price, et launch_date.
• La table purchase enregistre les transactions et comprend les éléments suivants : , , , , et : purchase_id, customer_id, product_id, quantity, total_amount, et purchase_date.
  

Exercice 1 : Résumé des recettes mensuelles

Exercice :
Répartissez les données d'achat par année et par mois, et indiquez le nombre de commandes (orders) et le chiffre d'affaires total (revenue) pour chaque période.

Solution :

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;

Explication :

Pour résoudre ce problème, nous devons regrouper les achats par année et par mois afin que chaque période (par exemple, janvier 2023, février 2023) soit traitée séparément. Nous commençons par extraire l'année et le mois du site purchase_date à l'aide de la fonction EXTRACT, qui fait partie de la norme SQL et qui est largement supportée. Certaines bases de données peuvent avoir leurs propres alternatives, comme DATEPART ou TO_CHAR. Le regroupement par purchase_year et purchase_month permet d'éviter que tous les achats de janvier de différentes années ne soient combinés en un seul groupe. Nous comptons ensuite le nombre de commandes et additionnons les recettes totales pour chaque période.

Exercice 2 : Recherche de commandes récentes

Exercice :

Trouvez tous les achats effectués au cours des 30 derniers jours. Affichez toutes les colonnes du tableau des achats.

Solution :

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

Explication :

Pour trouver les achats effectués au cours des 30 derniers jours, nous filtrons le tableau purchase à l'aide d'une condition de date. Nous comparons purchase_date à la date du jour moins un intervalle de 30 jours. Le mot-clé CURRENT_DATE donne la date du jour et INTERVAL 30 DAY est un moyen standard de soustraire des jours dans ANSI SQL. Cela permet de s'assurer que seules les lignes où l'achat a eu lieu au cours des 30 derniers jours sont renvoyées. Certaines bases de données peuvent utiliser une syntaxe légèrement différente pour les intervalles de dates, mais la logique reste la même.

Exercice 3 : Produits dont le prix est supérieur à la moyenne de leur catégorie

Exercice :

Retournez tous les produits dont le prix est supérieur au prix moyen de leur catégorie. Affichez toutes les colonnes de la table des achats.

Solution :

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

Explication :

Pour résoudre ce problème, nous comparons le prix de chaque produit au prix moyen des produits de la même catégorie. Nous utilisons une sous-requête qui calcule la moyenne price pour un site category donné, puis nous vérifions si le prix du produit actuel est supérieur à cette moyenne. Il s'agit d'une sous-requête corrélée - elle s'exécute une fois pour chaque ligne de la requête externe, en utilisant p.category pour faire correspondre les catégories. Nous utilisons SELECT * pour renvoyer toutes les colonnes de la table des produits pour les produits qui remplissent la condition.

Exercice 4 : Segments de prix : Budget, milieu de gamme, haut de gamme

Exercice :

Attribuez à chaque produit un segment de prix : "budget" pour les produits de moins de 20 dollars, "moyen" pour les produits de 20 à 99,99 dollars et "premium" pour les produits de 100 dollars et plus.

Solution :

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;

Explication :

Dans cette requête, nous utilisons l'expression CASE pour affecter chaque produit à un segment de prix en fonction de son adresse price. La syntaxe CASE fonctionne comme une structure if-else : elle vérifie les conditions dans l'ordre et renvoie la valeur correspondante. Ici, les produits de moins de 20 $ sont étiquetés 'budget', ceux compris entre 20 $ et 99,99 $ sont 'middle', et ceux de 100 $ ou plus sont 'premium'. Le résultat comprend les détails originaux du produit ainsi qu'une nouvelle colonne price_segment.

Exercice 5 : regroupement des clients par région

Exercice :
Regroupez les clients par région en fonction de leur pays. Supposez que des pays comme 'USA', 'Canada', et 'Mexico' appartiennent à 'North America', tandis que tous les autres font partie de 'Other'. Pour chaque région, comptez le nombre de clients qui lui sont affectés. Le résultat doit inclure region et customer_count.

Solution :

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;

Explication :

Cette requête utilise l'expression CASE pour regrouper les pays dans des régions personnalisées - 'North America' pour 'USA', 'Canada', et 'Mexico', et 'Other' pour le reste. CASE est souvent utilisé pour créer vos propres catégories sur la base des valeurs des colonnes. Une fois que nous avons défini ces régions personnalisées, nous pouvons les regrouper pour compter combien de clients appartiennent à chacune d'elles. Il s'agit d'un modèle courant lorsque l'on travaille avec des données géographiques, des niveaux de prix ou toute autre logique de classification personnalisée.

Exercice 6 : Dépenses des clients par catégorie

Exercice :

Pour chaque client, calculez le montant qu'il a dépensé pour des produits des catégories "Électronique", "Vêtements" et "Maison". Le résultat doit inclure customer_id, electronics_spend, clothing_spend, et home_spend comme noms de colonnes.

Solution :

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;

Explication :

Dans cette requête, nous calculons le montant dépensé par chaque client pour des produits appartenant à des catégories spécifiques en utilisant CASE WHEN dans les fonctions d'agrégation. CASE nous permet de vérifier la catégorie de chaque produit et de renvoyer total_amount uniquement s'il correspond à la catégorie qui nous intéresse - sinon, il renvoie 0. Nous répétons ce schéma pour 'Electronics', 'Clothing', et 'Home', et enveloppons chacun dans SUM() pour obtenir le total des dépenses par catégorie. Le résultat comprend une ligne par customer_id avec des colonnes distinctes pour les dépenses dans chaque catégorie. Cette technique est utile pour créer des résumés de type pivot directement en SQL.

Exercice 7 : Dépenses moyennes par client

Exercice :
Calculez le montant total dépensé par chaque client, puis renvoyez la moyenne des totaux de ces clients. Le résultat final doit inclure une seule colonne : avg_customer_spend.

Solution :

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;

Explication :

Pour résoudre ce problème, nous commençons par calculer le montant total dépensé par chaque client en regroupant les données purchase par customer_id et en additionnant les données total_amount. Nous faisons cela dans une expression de table commune (CTE) nommée customer_spend. Ensuite, dans la requête principale, nous calculons la moyenne de ces totaux à l'aide de AVG(spend) et la renvoyons sous la forme avg_customer_spend. Cette approche vous permet de séparer les calculs intermédiaires et de conserver une requête propre et lisible.

Exercice 8 : Clients supérieurs à la moyenne

Exercice :

Trouvez le montant total dépensé par chaque client et le montant moyen dépensé par tous les clients. Retournez uniquement les clients dont les dépenses totales sont supérieures à la moyenne. Le résultat doit inclure les colonnes customer_id et total_spend.

Solution :

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;

Explication :

Nous commençons par calculer les dépenses totales par client à l'aide d'un ETC appelé customer_spend. Ensuite, dans un deuxième ETC appelé avg_spend, nous calculons la moyenne de ces totaux. Dans la requête principale, nous comparons les dépenses totales de chaque client à la moyenne en joignant les deux ETC. Les clients dont total_spend est supérieur à la moyenne sont renvoyés, ainsi que leur customer_id. L'utilisation de deux CTE rend la logique plus facile à suivre et permet de conserver une requête propre.

Exercice 9 : Ventes de produits en pourcentage du total

Exercice :

Calculez le revenu total pour chaque produit. Ensuite, pour chaque produit, calculez le pourcentage qu'il représente dans le revenu total. Le résultat doit inclure product_id, revenue, et pct_of_total.

Solution :

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;

Explication :

Nous commençons par calculer le revenu total par produit à l'aide d'une expression de table commune (CTE) appelée product_sales. Ensuite, nous créons une autre CTE appelée total_revenue pour calculer le revenu global de tous les produits. Dans la requête finale, nous joignons les deux CTE à l'aide de la fonction CROSS JOIN afin que chaque ligne de produit ait accès à la valeur totale des recettes. Nous calculons ensuite le pourcentage de contribution de chaque produit en divisant ses recettes par le total et en multipliant par 100. Le résultat comprend product_id, revenue, et pct_of_total, triés du pourcentage le plus élevé au plus bas.

Exercice 10 : actif mais pas en décembre

Exercice :

Trouvez les clients qui ont passé plus d'une commande mais n'ont jamais effectué d'achat en décembre. Le résultat doit inclure customer_id, first_name, et last_name.

Solution :

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;

Explication :

Cette requête recherche les clients qui ont passé plus d'une commande, mais qui n'en ont effectué aucune en décembre. Tout d'abord, l'ETC multi_order identifie les clients ayant effectué plus d'un achat en les regroupant par customer_id et en utilisant HAVING COUNT(*) > 1. L'ETC december_buyers sélectionne tous les clients qui ont effectué au moins un achat en décembre en extrayant le mois de purchase_date. Dans la requête finale, nous joignons la table customer à multi_order pour obtenir les clients concernés, puis nous utilisons une JONCTION DE GAUCHE avec december_buyers pour vérifier qui n'a pas effectué d'achat en décembre. Nous filtrons ceux qui ont effectué un achat en vérifiant WHERE d.customer_id IS NULL. Le résultat ne comprend que customer_id, first_name et last_name.

Pour aller plus loin La pratique du SQL

Pratiquer régulièrement le langage SQL est l'un des moyens les plus efficaces pour passer de la compréhension de la théorie à la confiance dans les scénarios du monde réel. Les exercices de cet article couvrent des sujets intermédiaires de base tels que GROUP BY, CASE WHEN, les sous-requêtes et les CTE - toutes les compétences qui reviennent fréquemment dans les analyses de données, les rapports et les entretiens techniques. Si vous êtes arrivé jusqu'ici, vous êtes sur la bonne voie pour maîtriser le langage SQL.

Mais ne vous arrêtez pas là. La cohérence est la clé de l'apprentissage du langage SQL, et la meilleure façon de continuer à s'améliorer est de résoudre des problèmes plus pratiques. Si vous recherchez une pratique structurée et pratique avec un retour d'information instantané, nous vous recommandons vivement la piste La pratique du SQL sur LearnSQL.fr. Il est conçu pour vous aider à renforcer vos compétences par le biais de véritables défis de requêtes qui s'appuient les uns sur les autres - rien de superflu, juste de la pratique qui compte.

Que vous souhaitiez décrocher un emploi dans le domaine des données, automatiser des rapports ou travailler plus efficacement avec des bases de données, la piste La pratique du SQL est la prochaine étape qui mérite d'être franchie.