Exercices SQL pour la base de données Northwind

Lorsqu'il s'agit d'apprendre le langage SQL, de nombreux débutants ont du mal à acquérir une expérience pratique qui puisse se traduire par des scénarios réels. Nous allons relever ce défi en présentant des exercices SQL de la base de données Northwind avec des solutions et des explications.

Dans cet article, nous allons couvrir 16 exercices Northwind tirés de notre cours de pratique SQL Basic La pratique du SQL: A Store. Comme vous l'avez peut-être deviné, le cours utilise la base de données classique Northwind de Microsoft. Il s'agit d'une base de données d'une petite société commerciale conçue pour donner aux apprenants SQL une expérience pratique. Elle contient des données réalistes sur les clients, les produits, les fournisseurs et les commandes, ce qui en fait un point de départ idéal pour ceux qui souhaitent acquérir des compétences fondamentales en SQL.

De plus en plus de données sont générées chaque jour, ce qui rend les bases de données indispensables pour un stockage sûr et organisé des informations. Le langage SQL (Structured Query Language) permet d'exploiter les données contenues dans ces bases de données. Le langage SQL nous permet d'extraire, d'analyser et de manipuler des données à des fins d'analyse. Si l'apprentissage du langage SQL peut sembler difficile au début, la maîtrise du langage se développe avec la pratique, comme pour toute autre langue.

Le cours Basic La pratique du SQL: A Store propose 169 exercices interactifs qui couvrent un large éventail de sujets SQL. Pour nous familiariser avec le type de contenu du cours, nous allons examiner les questions de chaque domaine. Ces exercices vous aideront à améliorer vos compétences et votre confiance en SQL.

Présentation de la base de données Northwind

Commençons par jeter un coup d'œil à la base de données que nous allons utiliser :

Les tables de cette base de données représentent chacune un objet unique et sont liées à d'autres tables par des clés primaires et étrangères. La clé primaire identifie de manière unique chaque enregistrement d'une table (par exemple, customer_id dans la table Customer ), ce qui nous permet de relier les tables et d'obtenir des données pertinentes en créant un pont entre elles.

Examinons brièvement chaque table :

• employee: Cette table contient des données sur les employés de l'entreprise, qui peuvent être utiles pour les RH et l'analyse des performances des employés.
• purchase: Cette table contient des données sur les transactions entre les clients et l'entreprise. Ces informations peuvent être utiles pour l'analyse des achats, la planification des stocks et l'analyse de la localisation.
• customer: Cette table contient des données sur les clients de l'entreprise. Ces données peuvent être utiles pour identifier les publics cibles et personnaliser les services.
• purchase_item: Ce tableau relie les achats aux produits et nous permet d'obtenir des informations sur les prix et les quantités des différents produits de chaque commande.
• product: Ce tableau affiche des données sur les produits de l'offre de l'entreprise. Il peut fournir des informations sur les produits les plus populaires et leurs détails.
• category: Ce tableau contient des informations sur les différentes catégories de produits, ce qui peut aider à obtenir des informations sur les catégories les plus populaires.

Exercices sur la base de données Northwind

Nous allons maintenant nous plonger dans des exercices SQL pratiques basés sur cette base de données. Nous allons diviser cet article en six sections qui introduisent chacune des compétences SQL différentes. Gardez notre aide-mémoire SQL à portée de main pour vous référer rapidement aux fonctions et à la syntaxe que nous utiliserons.

1. Requêtes sur une seule table

Pour commencer, nous allons nous pencher sur les bases : filtrer et extraire des données d'une seule table. Dans ces exercices, vous utiliserez les clauses SELECT et WHERE pour extraire des informations spécifiques de tables individuelles. La maîtrise de ces commandes SQL essentielles constituera une base solide pour l'élaboration ultérieure de requêtes plus complexes.

Exercice 1 : Tous les produits

Affichez toutes les données présentes dans la table product table.

Solution :

SELECT *
FROM product;

Explication :

Dans cette requête :

• SELECT * demande à SQL de sélectionner toutes les colonnes de la table product
• FROM product spécifie la table que nous utilisons.
• Cette requête renvoie toutes les lignes et colonnes de la table, ce qui donne une vue complète des détails de chaque produit dans la base de données. product fournissant ainsi une vue complète des détails de chaque produit dans la base de données.

Exercice 2 : produits plus chers que 3,5

Affichez les noms des produits dont le prix unitaire est supérieur ou égal à 3,5.

Solution :

SELECT product_name
FROM product
WHERE unit_price >= 3.5;

Explication :

Dans cette requête :

• SELECT product_name spécifie que nous voulons seulement les noms des produits.
• FROM product spécifie la table utilisée.
• WHERE unit_price >= 5 limite la sortie aux produits dont le prix est égal ou supérieur à 3,5.
• Cette requête est utile pour identifier les articles les plus chers du magasin.

Exercice 3 : noms et courriels des clients

Affichez les noms et les courriels des clients. Vous trouverez le nom dans la colonne contact_name et l'email dans la colonne contact_email du tableau customer de la table. Renommez ces colonnes en name et email, respectivement.

Solution :

SELECT
  contact_name AS name,
  contact_email AS email
FROM customer;

Explication :

Dans cette requête :

• SELECT nom_contact AS nom prend la colonne nom_contact et la renomme en nom dans la sortie. La même chose est faite avec contact_email AS email.
• FROM client spécifie la table que nous utilisons.
• L'alias avec AS rend les résultats plus lisibles et plus conviviaux. Cela peut s'avérer particulièrement utile lorsque les résultats sont communiqués à des parties prenantes non techniques.

Vous trouverez d'autres exemples d'exercices pratiques et simples dans notre article La pratique du SQL pour les débutants : Exercices AdventureWorks.

2. Requêtes à partir de plusieurs tables

Cette section se concentre sur la combinaison de données provenant de différentes tables à l'aide de JOINs; cela nous permet de créer des requêtes plus complexes.

Exercice 4 : Produits et catégories

Sélectionnez des noms de produits ainsi que leurs catégories. Affichez deux colonnes : product_name et category_name.

Solution :

SELECT
  product_name,
  category.name AS category_name
FROM product
JOIN category
  ON product.category_id = category.category_id;

Explication :

Dans cette requête :

• SELECT product_name, category.name AS category_name identifie les colonnes qui sont demandées dans notre résultat.
• FROM product spécifie la première table de notre JOIN.
• JOIN category spécifie la deuxième table de notre site JOIN.
• ON product.category_id = category.category_id agit comme un pont entre les deux tables et s'assure que seules les lignes dont les ID de catégorie correspondent sont incluses dans l'ensemble de résultats.

Exercice 5 : Employés et achats, partie 1

Indiquez le nom et le prénom des employés qui s'occupent des achats ainsi que le site shipped_date de l'achat.

Solution :

SELECT
  last_name,
  first_name,
  shipped_date
FROM employee
JOIN purchase
  ON employee.employee_id = purchase.employee_id;

Explication :

Dans cette requête :

• SELECT last_name, first_name, shipped_date identifie les colonnes qui sont affichées dans notre résultat.
• FROM employee indique la première table de notre JOIN.
• JOIN purchase indique la deuxième table dans notre JOIN.
• ON employee.employee_id = purchase.employee_id joint les deux tables sur les lignes où l'ID de l'employé dans la table correspond à l'ID de l'employé dans la table. employee correspond à l'ID de l'employé dans la table purchase Seules les lignes dont les ID correspondent sont incluses dans l'ensemble de résultats.

Exercice 6 : Employés et achats, partie 2

Pour chaque employé, affichez last_name, first_name, et l'ID du ou des achats qu'il a traités (s'il y en a). Veillez à afficher les données de tous les employés, même s'ils n'ont participé à aucun achat.

Solution :

SELECT
  last_name,
  first_name,
  purchase_id
FROM employee
LEFT JOIN purchase
  ON employee.employee_id = purchase.employee_id;

Explication :

Dans cette requête :

• SELECT last_name, first_name, purchase_id spécifie les colonnes à inclure dans la sortie.
• FROM employee désigne la première table de notre site LEFT JOIN, qui contient des informations sur les employés.
• LEFT JOIN purchase spécifie la deuxième table de la jointure, qui contient des données sur les achats. L'utilisation de LEFT JOIN garantit que toutes les lignes de la table employee sont incluses, même s'il n'y a pas d'enregistrement correspondant dans la table purchase
• ON employee.employee_id = purchase.employee_id relie les deux tables, en faisant correspondre les enregistrements sur la base de l'ID de l'employé. Si un employé n'a pas d'enregistrement correspondant dans la table purchase il sera quand même inclus dans le résultat et les champs d'achat de l'ensemble de résultats s'afficheront comme suit : NULL.

Comme vous pouvez le constater, l'écriture de requêtes vous permet de développer vos compétences en SQL ! Une autre excellente façon de développer vos compétences en SQL est de réaliser des projets tels que ceux présentés dans SQL Project for Beginners : Tableau de bord des ventes AdventureWorks.

3. ORDER BY : Trier vos résultats

La clause ORDER BY de SQL est particulièrement utile pour organiser les résultats de manière plus lisible et significative. Que vous souhaitiez trier les données numériquement, alphabétiquement ou par date, cette clause est très utile dans les rapports et les analyses.

Exercice 7 : employés par date de naissance

Affichez les noms, les prénoms et les dates de naissance des employés. Triez les employés en fonction de leur date de naissance par ordre décroissant (c'est-à-dire que les dates les plus récentes viennent en premier).

Solution :

SELECT
  last_name,
  first_name,
  birth_date
FROM employee
ORDER BY birth_date DESC;

Explication :

Dans cette requête :

• SELECT last_name, first_name, birth_date spécifie les colonnes à inclure dans la sortie.
• FROM employee indique la table à partir de laquelle les données sont extraites.
• ORDER BY birth_date DESC trie les résultats en fonction de la colonne birth_date dans l'ordre décroissant, de sorte que les employés dont les dates de naissance sont les plus récentes apparaissent en premier. Cela permet de contourner la limitation liée à l'absence de colonne d'âge.

Exercice 8 : Noms et prix des produits

Affichez les noms et les prix unitaires de tous les produits. Affichez d'abord les produits les moins chers.

Solution :

SELECT
  product_name,
  unit_price
FROM product
ORDER BY unit_price;

Explication :

Dans cette requête :

• SELECT product_name, unit_price spécifie les colonnes à inclure dans la sortie.
• FROM product identifie la table dont les données sont extraites.
• ORDER BY unit_price trie les résultats en fonction de la colonne unit_price dans l'ordre croissant, ce qui signifie que les produits dont les prix sont les plus bas apparaissent en premier.
• Comme vous pouvez le constater, il n'est pas nécessaire de spécifier explicitement ASCending ; il s'agit de l'ordre de tri par défaut.

Exercice 9 : Données d'achat

Affichez toutes les données relatives à tous les achats. Triez les lignes par ville d'expédition (ordre croissant) et par date d'expédition (ordre décroissant).

Solution :

SELECT *
FROM purchase
ORDER BY
  ship_city ASC,
  shipped_date DESC;

Explication :

Dans cette requête :

• SELECT * spécifie que toutes les colonnes de la table doivent être incluses dans la sortie.
• FROM purchase identifie la table à partir de laquelle les données sont extraites.
• ORDER BY ship_city ASC, shipped_date DESC trie les résultats d'abord par ship_city dans l'ordre croissant (de sorte que les villes soient triées par ordre alphabétique). Ensuite, pour chaque ville, les résultats sont triés par shipped_date dans l'ordre décroissant (de sorte que les dates d'expédition les plus récentes apparaissent en premier pour chaque ville).

Si vous souhaitez trouver d'autres exercices avec ORDER BY, consultez notre article 10 Exercices débutants La pratique du SQL avec solutions.

4. Fonctions GROUP BY et Aggregate

La section suivante permet d'acquérir les compétences nécessaires pour résumer les données par le biais du regroupement et de l'agrégation. Les fonctions d'agrégation permettent d'obtenir des informations en combinant des lignes et en effectuant des calculs au sein de chaque groupe.

Exercice 10 : nombre de catégories

Comptez le nombre de catégories présentes dans le category tableau. Nommez la colonne number_of_categories.

Solution :

SELECT
  COUNT(category_id) AS number_of_categories
FROM category;

Explication :

Dans cette requête :

• SELECT COUNT(category_id) AS number_of_categories compte le nombre de lignes qui n'ont pas de NULL dans la colonne category_id Elle affiche la colonne résultante sous la forme number_of_categories.
• FROM category spécifie la table à partir de laquelle les données sont extraites.

Exercice 11 : achats des clients

Comptez le nombre d'achats effectués par chaque client. Affichez les colonnes customer_id, contact_name, et purchases_number. Ignorez les clients qui ne sont pas présents dans la table. purchase tableau.

Solution :

SELECT
  purchase.customer_id,
  contact_name,
  COUNT(*) AS purchases_number
FROM purchase
JOIN customer
  ON purchase.customer_id = customer.customer_id
GROUP BY
  purchase.customer_id,
  contact_name;

Explication :

Dans cette requête :

• SELECT purchase.customer_id, contact_name, COUNT(*) AS purchases_number spécifie les colonnes à inclure dans le résultat. Elle récupère l'identifiant et le nom du contact de chaque client, ainsi que le nombre de leurs achats (étiqueté purchases_number).
• FROM purchase identifie la première table de la base de données JOIN.
• JOIN customer spécifie la deuxième table dans la base de données JOIN.
• ON purchase.customer_id = customer.customer_id relie les deux tables, en faisant correspondre les lignes où le customer_id est le même dans les deux tables et en s'assurant que seuls les achats associés à des clients existants sont inclus.
• GROUP BY purchase.customer_id, contact_name regroupe les résultats en fonction de l'identifiant et du nom du contact de chaque client, ce qui permet à la fonction COUNT(*) de calculer le nombre d'achats pour chaque client.

Exercice 12 : Chiffre d'affaires pour les paires de clients et d'employés

Pour chaque client et chaque employé, trouvez le prix total de tous les achats qu'ils ont effectués et auxquels un employé donné est affecté. Affichez trois colonnes : customer_id, employee_id, et le prix total des achats. Renommez la troisième colonne en total_purchases_price.

Solution :

SELECT
  customer_id,
  employee_id,
  SUM(total_price) AS total_purchases_price
FROM purchase
GROUP BY
  customer_id,
  employee_id;

Explication :

Dans cette requête :

• SELECT customer_id, employee_id, SUM(total_price) AS total_purchases_price indique les colonnes à inclure dans le résultat, en montrant chaque customer_id et employee_id ainsi que la somme de total_price pour les achats associés à chaque combinaison. Cette somme est appelée total_purchases_price dans le résultat.
• FROM purchase identifie la table dont les données sont extraites.
• GROUP BY customer_id, employee_id regroupe les résultats par customer_id et employee_id, ce qui permet à la fonction SUM(total_price) de calculer le prix d'achat total pour chaque combinaison client-employé unique.

5. Autres fonctions SQL

Nous allons maintenant aborder brièvement d'autres fonctions SQL qui sont incluses dans le cours mais qui sont également utiles dans l'analyse quotidienne. Ces fonctions, combinées à d'autres présentées dans cet article, peuvent vous aider à améliorer vos compétences en SQL.

Exercice 13 : date d'embauche inconnue

Afficher le nom et le prénom des employés dont la date d'embauche est inconnue.

Solution :

SELECT
  last_name,
  first_name
FROM employee
WHERE hire_date IS NULL;

Explication :

Dans cette requête :

• SELECT last_name, first_name spécifie les colonnes à inclure dans la sortie.
• FROM employee indique la table dont les données sont extraites.
• WHERE hire_date IS NULL filtre les résultats pour n'inclure que les employés dont hire_date est NULL, ce qui signifie que leur date d'embauche est inconnue.

Exercice 14 : Prix moyen par catégorie

Pour chaque catégorie, comptez le prix moyen de ses produits. N'affichez que les catégories pour lesquelles le prix unitaire moyen est supérieur au prix unitaire moyen global. Nommez les colonnes category_name et average_price.

Solution :

SELECT
  category.name AS category_name,
  AVG(unit_price) AS average_price
FROM product
JOIN category
  ON product.category_id = category.category_id
GROUP BY category.name
HAVING AVG(unit_price) > (
  SELECT AVG(unit_price)
  FROM product
);

Explication :

Dans cette requête :

• SELECT category.name AS category_name, AVG(unit_price) AS average_price spécifie les colonnes à inclure dans la sortie.
• FROM product identifie la première table dans JOIN.
• JOIN category spécifie la deuxième table de la base de données JOIN.
• ON product.category_id = category.category_id relie les deux tables, garantissant que seuls les produits dont les identifiants de catégorie correspondent dans les deux tables sont inclus.
• GROUP BY category.name regroupe les résultats par nom de catégorie, ce qui permet à la fonction AVG(unit_price) de calculer le prix moyen pour chaque catégorie.
• HAVING AVG(unit_price) > (SELECT AVG(unit_price) FROM product) filtre les résultats groupés pour n'inclure que les catégories dans lesquelles le prix moyen des produits est supérieur au prix moyen global de tous les produits de la base de données. product
• La sous-requête (SELECT AVG(unit_price) FROM product) calcule le prix unitaire moyen global de tous les produits.

6. SQL avancé Fonctionnalités

Enfin, nous allons nous pencher sur des requêtes SQL plus avancées. Pour ce faire, nous ferons référence aux fonctions que nous avons étudiées précédemment.

Exercice 15 : catégories avec produits actifs

Pour chaque catégorie, affichez le nombre de ses produits qui ne sont pas abandonnés (ils sont poursuivis ou il y a un NULL dans la colonne discontinued ). Affichez les colonnes nommées category_name et products_number. N'affichez que les lignes pour lesquelles le nombre de ces produits est supérieur à 1. N'affichez pas non plus la ligne correspondant à la catégorie Autres.

Solution :

SELECT
  category.name AS category_name,
  COUNT(product_id) AS products_number
FROM product
JOIN category
  ON product.category_id = category.category_id
WHERE category.name <> 'Other'
  AND discontinued IS NOT TRUE
GROUP BY category.name
HAVING COUNT(product_id) > 1;

Explication :

Dans cette requête :

• SELECT category.name AS category_name, COUNT(product_id) AS products_number spécifie les colonnes à inclure dans la sortie.
• FROM product identifie le premier tableau du site JOIN.
• JOIN category spécifie la deuxième table de la base de données JOIN.
• ON product.category_id = category.category_id relie les deux tables, en veillant à ce que seuls les produits dont les identifiants de catégorie correspondent dans les deux tables soient inclus.
• WHERE category.name <> 'Other' AND discontinued IS NOT TRUE filtre les résultats pour exclure les catégories nommées "Other" et pour n'inclure que les produits qui ne sont pas abandonnés.
• GROUP BY category.name regroupe les résultats par nom de catégorie, ce qui permet à la fonction COUNT(product_id) de compter le nombre de produits dans chaque catégorie.
• HAVING COUNT(product_id) > 1 filtre les résultats groupés pour n'inclure que les catégories comportant plus d'un produit.

Exercice 16 : statistiques sur les vins

Tous les vins de la table des produits ont un nom commençant par Wine. Trouvez le :

• Le nombre de produits de ce type dans la table (products_number).
• Le nombre total d'unités en stock (units_number).
• Prix moyen du produit (average_price).
• Rapport entre le prix maximum et le prix minimum (max_to_min_ratio).
• Différence entre le prix maximum et le prix moyen (max_to_average).
• Différence entre le prix moyen et le prix minimum (average_to_min).

Arrondissez les quatre dernières colonnes à deux décimales.

Solution :

SELECT
  COUNT(*) AS products_number,
  SUM(units_in_stock) AS units_number,
  ROUND(AVG(unit_price), 2) AS average_price,
  ROUND(MAX(unit_price) / MIN(unit_price), 2) AS max_to_min_ratio,
  ROUND(MAX(unit_price) - AVG(unit_price), 2) AS max_to_average,
  ROUND(AVG(unit_price) - MIN(unit_price), 2) AS average_to_min
FROM product
WHERE product_name LIKE 'Wine%';

Explication :

Dans cette requête :

• Commençons par décomposer l'instruction SELECT:
  • products_number calcule le nombre total de produits.
  • units_number calcule le nombre total d'unités en stock.
  • average_price obtient le prix unitaire moyen des produits, arrondi à 2 décimales.
  • max_to_min_ratio trouve le rapport entre le prix unitaire maximum et le prix unitaire minimum, arrondi à la deuxième décimale.
  • max_to_average calcule la différence entre le prix unitaire maximum et le prix unitaire moyen, arrondie à la deuxième décimale.
  • average_to_min calcule la différence entre le prix unitaire moyen et le prix unitaire minimum, arrondie à la deuxième décimale.
• FROM product identifie le tableau dont les données sont extraites.
• WHERE product_name LIKE 'Wine%' filtre les résultats pour n'inclure que les produits dont le nom commence par 'Wine'.

Vous voulez plus d'exercices Northwind pour les apprenants SQL ?

En vous entraînant à écrire des requêtes, vous pouvez renforcer vos compétences en SQL. Travailler avec des données réelles, telles que la base de données Northwind, vous permet d'acquérir de l'expérience en matière d'accès et d'évaluation rapides des informations.

Si vous avez trouvé les exercices Northwind de cet article intéressants, n'hésitez pas à consulter le cours Basic La pratique du SQL: A Store. Vous y trouverez de nombreux autres exercices interactifs. Nous disposons également de SQL Project for Portfolio : Northwind Store, un article sur la construction d'un projet basé sur la base de données Northwind. Bon apprentissage !