Jointures SQL : 12 questions pratiques avec des réponses détaillées

Dans cet article, nous avons approfondi notre cours sur les jointures SQL et nous vous avons donné 12 exercices de jointure à résoudre. Mais ne vous inquiétez pas - tous les exercices ont des solutions et des explications. Si vous êtes bloqué, l'aide est là ! Après tout, ce cours est fait pour pratiquer et apprendre.

Les jointures SQL peuvent être délicates. Il ne s'agit pas seulement de la syntaxe, mais aussi de savoir quelles jointures utiliser dans quels scénarios.

Les jointures sont utilisées pour combiner des données provenant de deux tables ou plus en SQL. Les tables peuvent être jointes de plusieurs façons et, selon les tables, chaque façon de les joindre peut donner un résultat complètement différent. Il n'y a pas d'autre moyen d'apprendre cela que la pratique. Oui, vous pouvez lire des explications et des utilisations typiques des jointures SQL. Cela aide, c'est certain ! Mais la pratique s'appuie sur la résolution de problèmes et la répétition, ce qui permet d'assimiler les connaissances. Plus vous pratiquez, plus vous avez de chances que les problèmes de données réels que vous aurez à résoudre soient similaires ou complètement identiques à ce que vous avez déjà fait !

Et c'est ce que nous allons faire dans cet article ! Nous vous montrerons des exercices pour des utilisations basiques et plus avancées des jointures SQL. Si vous les appréciez, vous apprécierez d'autant plus notre cours. Les jointures en SQL car tous les exercices sont tirés de ce cours. Au total, le cours vous propose 93 exercices sur les jointures SQL. Ils couvrent des sujets allant des types de jointures en SQL au filtrage des données, en passant par la jointure de plus de deux tables, l'auto-jointure d'une table et l'utilisation de jointures non égales.

OK, présentons les ensembles de données et commençons les exercices, d'accord ? N'hésitez pas à vous aider de l'aide-mémoire SQL JOIN au fur et à mesure que vous avancez.

Liste des exercices

Voici la liste de tous les exercices de l'article :

• Exercice 1 : Liste de tous les livres et de leurs auteurs
• Exercice 2 : Liste des auteurs et des livres publiés après 2005
• Exercice 3 : Afficher les livres adaptés dans les 4 ans et moins bien notés que l'adaptation
• Exercice 4 : Afficher tous les livres et leurs adaptations (le cas échéant)
• Exercice 5 : Afficher tous les livres et leurs adaptations cinématographiques
• Exercice 6 : Afficher tous les livres avec leurs critiques (le cas échéant)
• Exercice 7 : Liste de tous les livres et de tous les auteurs
• Exercice 8 : Afficher les produits de moins de 150 calories et leur rayon
• Exercice 9 : Liste de tous les produits avec leurs producteurs, départements et glucides
• Exercice 10 : Afficher tous les produits, les prix, les producteurs et les départements
• Exercice 11 : Liste de tous les travailleurs et de leurs superviseurs directs
• Exercice 12 : Afficher les voitures dont le kilométrage est supérieur à celui d'une voiture spécifique

INNER JOIN

INNER JOIN est un type de jointure SQL qui renvoie uniquement les lignes correspondantes des tables jointes.

Pour vous montrer comment cela fonctionne, nous allons utiliser le jeu de données 1 du cours.

Jeu de données 1

L'ensemble de données se compose de quatre tables : author, book, adaptation, et book_review.

Le premier tableau présente les données relatives à l'auteur dans les colonnes suivantes :

• id - L'identifiant unique de l'auteur dans la base de données.
• name - Le nom de l'auteur.
• birth_year - L'année de naissance de l'auteur.
• death_year - L'année de décès de l'auteur (le champ est vide si l'auteur est encore en vie).

Voici les premières lignes de la table :

id	name	birth_year	death_year
1	Marcella Cole	1983	NULL
2	Lisa Mullins	1891	1950
3	Dennis Stokes	1935	1994
4	Randolph Vasquez	1957	2004
5	Daniel Branson	1965	1990
…	…	…	…

La deuxième table, bookprésente des informations sur les livres. Les colonnes sont les suivantes

• id - L'ID d'un livre donné.
• author_id - L'ID de l'auteur qui a écrit ce livre.
• title - Le titre du livre.
• publish_year - L'année de publication du livre.
• publishing_house - Le nom de la maison d'édition qui a imprimé le livre.
• note - La note moyenne du livre.

Il s'agit des cinq premières lignes :

id	author_id	title	publish_year	publishing_house	rating
1	NULL	Soulless girl	2008	Golden Albatros	4.3
2	NULL	Weak Heart	1980	Diarmud Inc.	3.8
3	4	Faith Of Light	1995	White Cloud Press	4.3
4	NULL	Memory Of Hope	2000	Rutis Enterprises	2.7
5	6	Warrior Of Wind	2005	Maverick	4.6
…	…	…	…	…	…

Le tableau adaptation comporte les colonnes suivantes :

• book_id - L'ID du livre adapté.
• type - Le type d'adaptation (par exemple, film, jeu, pièce de théâtre, comédie musicale).
• title - Le nom de cette adaptation.
• release_year - L'année de création de l'adaptation.
• rating - La note moyenne de l'adaptation.

Voici un aperçu des données de ce tableau :

book_id	type	title	release_year	rating
1	movie	Gone With The Wolves: The Beginning	2008	3
3	movie	Companions Of Tomorrow	2001	4.2
5	movie	Homeless Warrior	2008	4
2	movie	Blacksmith With Silver	2014	4.3
4	movie	Patrons And Bearers	2004	3.2
…	…	…	…	…

Le tableau final est le suivant book_review. Il se compose des colonnes suivantes :

• book_id - L'ID d'un livre chroniqué.
• review - Le résumé de la critique.
• author - Le nom de l'auteur de la critique.

Voici les données :

book_id	review	author
1	An incredible book	Sylvia Jones
1	Great, although it has some flaws	Jessica Parker
2	Dennis Stokes takes the reader for a ride full of emotions	Thomas Green
3	Incredible craftsmanship of the author	Martin Freeman
4	Not the best book by this author	Jude Falth
5	Claudia Johnson at her best!	Joe Marqiz
6	I cannot recall more captivating plot	Alexander Durham

Exercice 1 : Liste de tous les livres et de leurs auteurs

Exercice : Affichez le nom de chaque auteur avec le titre du livre qu'il a écrit et l'année de publication de ce livre.

Solution :

SELECT
  name,
  title,
  publish_year
FROM author
JOIN book
  ON author.id = book.author_id;

Explication de la solution : La requête sélectionne le nom de l'auteur, le titre du livre et son année de publication. Il s'agit de données provenant des deux tableaux : author et book. Nous pouvons accéder aux deux tables en utilisant INNER JOIN. La requête renvoie uniquement les lignes dont les valeurs correspondent (valeurs qui satisfont à la condition de jointure) dans les deux tables.

Nous référençons d'abord la table author dans la clause FROM. Ensuite, nous ajoutons la clause JOIN (qui peut également s'écrire INNER JOIN en SQL) et référençons la table book.

Les tables sont jointes sur la colonne commune. Dans ce cas, il s'agit de id de la table author et author_id de la table book. Nous voulons joindre les lignes où ces colonnes partagent la même valeur. Pour ce faire, nous utilisons la clause ON et spécifions les noms des colonnes. Nous indiquons également le nom de la table avant chaque colonne afin que la base de données sache où chercher. C'est principalement parce qu'il y a une colonne id dans les deux tables, mais nous voulons que la colonne id ne provienne que de la table author mais nous voulons que la colonne d'identification soit uniquement celle de la table En faisant référence au nom de la table, la base de données saura de quelle table nous avons besoin pour cette colonne.

Résultat de la solution :

Voici le résultat de la solution. Nous avons obtenu toutes ces données en joignant deux tables :

name	title	publish_year
Marcella Cole	Gone With The Wolves	2005
Lisa Mullins	Companions And Officers	1930
Dennis Stokes	Blacksmith With Silver	1984
Randolph Vasquez	Faith Of Light	1995
Michael Rostkovsky	Warrior Of Wind	2005
…	…	…

Exercice 2 : Liste des auteurs et des livres publiés après 2005

Exercice : Affichez le nom de chaque auteur avec le titre du livre qu'il a écrit et l'année de publication de ce livre. N'indiquez que les livres publiés après 2005.

Solution :

SELECT
  name,
  title,
  publish_year
FROM author
JOIN book
  ON author.id = book.author_id
WHERE publish_year > 2005;

Explication de la solution : Cet exercice et sa solution sont presque les mêmes que le précédent. En effet, la requête sélectionne les mêmes colonnes et joint les tables de la même manière que précédemment.

La différence est que l'exercice nous demande maintenant de n' afficher que les livres publiés après 2005. Cela nécessite de filtrer la sortie, ce que nous faisons à l'aide de la clause WHERE.

WHERE est une clause qui accepte les conditions utilisées pour filtrer les données. Elle est écrite après avoir joint les tables. Dans notre exemple, nous filtrons en référençant la colonne publish_year après WHERE et en utilisant l'opérateur de comparaison "supérieur à" (>) pour trouver les années postérieures à 2005.

Résultat de la solution :

Le résultat ne montre qu'un seul livre publié après 2005.

name	title	publish_year
Darlene Lyons	Temptations In Nature	2007

Exercice 3 : afficher les livres adaptés dans les 4 ans et dont la cote est inférieure à celle de l'adaptation

Exercice : Pour chaque livre, indiquez son titre, le titre de l'adaptation, l'année d'adaptation et l'année de publication.

N'incluez que les livres dont la note est inférieure à celle de l'adaptation correspondante. En outre, n'indiquez que les livres pour lesquels une adaptation a été publiée dans les quatre ans suivant la publication du livre.

Renommez la colonne title du tableau book en book_title et la colonne title du tableau adaptation en adaptation_title.

Solution :

SELECT
  book.title AS book_title,
  adaptation.title AS adaptation_title,
  book.publish_year,
  adaptation.release_year
FROM book
JOIN adaptation
  ON book.id = adaptation.book_id
WHERE adaptation.release_year - book.publish_year <= 4
  AND book.rating < adaptation.rating;

Explication de la solution : Commençons par expliquer la solution à partir des clauses FROM et JOIN. Les colonnes que nous devons montrer proviennent des tableaux book et adaptation. Nous faisons référence à la première table dans FROM et à la seconde dans JOIN.

Dans la clause ON, nous égalisons les deux colonnes d'ID de livre et spécifions la table de chaque colonne. Il s'agit de la même chose que précédemment, mais avec des noms de table et de colonne différents.

Nous devons maintenant sélectionner les colonnes requises. Le problème ici est qu'il y a une colonne title dans les deux tables. Pour éviter toute ambiguïté, la meilleure pratique consiste à faire référence au nom de la table avant chaque colonne dans SELECT.

Remarque : cette pratique n'est obligatoire que pour les colonnes ambiguës. Cependant, c'est une bonne idée de le faire pour toutes les colonnes ; cela améliore la lisibilité du code et l'approche reste cohérente.

Après avoir sélectionné les colonnes, nous devons renommer certaines d'entre elles. Pour ce faire, nous utilisons le mot-clé AS et écrivons ensuite un nouveau nom de colonne. Ainsi, une colonne title devient book_title, l'autre adaptation_title. Le fait de donner des alias aux noms de colonnes permet également de se débarrasser de toute ambiguïté.

Nous devons maintenant filtrer les résultats. La première condition est que l'adaptation doit être publiée quatre ans ou moins après le livre. Nous utilisons à nouveau WHERE et déduisons simplement l'année de publication du livre de l'année de sortie de l'adaptation. Nous disons ensuite que la différence doit être inférieure ou égale à (<=) 4.

Nous devons également ajouter la deuxième condition, à savoir que le livre soit moins bien noté que l'adaptation. C'est simple ! La question implique que la première et la deuxième condition doivent être remplies. L'indice se trouve dans AND, un opérateur logique que nous utilisons pour ajouter la deuxième condition. Ici, il utilise l'opérateur "moins de" (<) pour comparer les deux notes.

Résultat de la solution :

Le résultat montre trois paires livre-adaptation qui remplissent les conditions.

book_title	adaptation_title	publish_year	release_year
Memory Of Hope	Patrons And Bearers	2000	2004
Music At The Lake	Music At The Lake	2004	2007
Companion Of Tomorrow	Lighting Faith	1949	1952

JOINT DE GAUCHE

Maintenant que vous avez compris l'essentiel de INNER JOIN, passons à LEFT JOIN. Il s'agit d'un type de jointure externe qui renvoie toutes les colonnes du tableau de gauche (le premier) et uniquement les lignes correspondantes du tableau de droite (le second). S'il y a des données non correspondantes, elles sont affichées à l'adresse NULL.

Pour en savoir plus, consultez notre article sur le LEFT JOIN.

Exercice 4 : Afficher tous les livres et leurs adaptations (le cas échéant)

Exercice : Affichez le titre de chaque livre ainsi que le titre de son adaptation et la date de sortie. Affichez tous les livres, qu'ils aient ou non fait l'objet d'une adaptation.

Solution :

SELECT
  book.title,
  adaptation.title,
  adaptation.release_year
FROM book
LEFT JOIN adaptation
  ON book.id = adaptation.book_id;

Explication de la solution : Nous sélectionnons d'abord les colonnes nécessaires dans les deux tableaux. Ensuite, nous joignons book (la table de gauche) avec adaptation (la table de droite) à l'aide de LEFT JOIN. Vous voyez que la syntaxe de jointure SQL est la même pour INNER JOIN. La seule chose qui change est le mot-clé de jointure.

Remarque : SQL accepte à la fois LEFT JOIN et LEFT OUTER JOIN. Il s'agit de la même commande.

Résultat de la solution :

L'instantané de sortie montre les données requises, certaines d'entre elles étant affichées sous la forme NULL. Il s'agit des livres sans l'adaptation.

title	title-2	release_year
Soulless girl	Gone With The Wolves: The Beginning	2008
Faith Of Light	Companions Of Tomorrow	2001
Warrior Of Wind	Homeless Warrior	2008
…	…	…
Guarding The Emperor	NULL	NULL
Blacksmith With Silver	NULL	NULL
…	…	…

Exercice 5 : Afficher tous les livres et leurs adaptations cinématographiques

Exercice : Affichez tous les livres avec leurs adaptations cinématographiques. Sélectionnez le titre de chaque livre, le nom de sa maison d'édition, le titre de son adaptation et le type d'adaptation. Conservez les livres sans adaptation dans le résultat.

Solution :

SELECT
  book.title,
  publishing_house,
  adaptation.title,
  adaptation.type
FROM book
LEFT JOIN adaptation
  ON book.id = adaptation.book_id
WHERE type = 'movie'
  OR type IS NULL;

Explication de la solution :

La question demande d'afficher toutes les lignes, même celles qui ne comportent pas d'adaptations. Il est possible qu'il y ait des livres sans adaptations, nous utilisons donc LEFT JOIN.

Nous sélectionnons d'abord le titre du livre, sa maison d'édition, le titre de l'adaptation et son type.

Ensuite, nous joignons book (la table de gauche) avec adaptation (la table de droite) en utilisant LEFT JOIN. Nous joignons les tables sur l'ID du livre. Tous les livres qui ne remplissent pas les conditions auront NULLs comme titre et type d'adaptation.

Nous filtrons les données à l'aide de WHERE. La première condition est que le type d'adaptation doit être un film, nous égalisons donc la colonne type avec un film en utilisant le signe égal (=). Remarque : lorsque des données textuelles sont utilisées dans la condition WHERE, elles doivent être placées entre guillemets simples ('').

La deuxième condition de filtrage est ajoutée à l'aide de l'opérateur logique OR. Elle indique que le type peut également être NULL s'il ne s'agit pas d'un film. L'exercice nous demande de conserver dans les résultats les livres qui n'ont pas été adaptés.

Résultat de la solution :

Voici l'instantané du résultat. Vous pouvez voir qu'il ne montre que les livres adaptés en films ou non adaptés du tout.

title	publishing_house	title-2	type
Soulless girl	Golden Albatros	Gone With The Wolves: The Beginning	movie
Faith Of Light	White Cloud Press	Companions Of Tomorrow	movie
Warrior Of Wind	Maverick	Homeless Warrior	movie
…	…	…	…
Guarding The Emperor	Flying Pen Media	NULL	NULL
Blacksmith With Silver	Diarmud Inc.	NULL	NULL

JONCTION À DROITE

Là où il y a LEFT JOIN, il y a aussi RIGHT JOIN, n'est-ce pas ? Bien qu'il s'agisse de l'image inversée du LEFT JOIN, il fait toujours partie de la pratique des jointures SQL.

Il s'agit d'un type de jointure qui renvoie toutes les colonnes de la table de droite (la deuxième) et uniquement les lignes correspondantes de la table de gauche (la première). S'il y a des données non correspondantes, elles sont affichées à l'adresse NULL.

Exercice 6 : Afficher tous les livres avec leurs critiques (le cas échéant)

Exercice : Joindre les tableaux book_review et book à l'aide d'un RIGHT JOIN. Affichez le titre du livre, la critique correspondante et le nom de l'auteur de la critique. Considérez tous les livres, même ceux qui n'ont pas fait l'objet d'une critique.

Solution :

SELECT
  book.title,
  book_review.review,
  book_review.author
FROM book_review
RIGHT JOIN book
  ON book.id = book_review.book_id;

Explication de la solution :

Nous sélectionnons d'abord les colonnes nécessaires. Ensuite, nous faisons ce qui nous est demandé : nous joignons les tables à l'aide de RIGHT JOIN. Nous joignons les tables sur l'ID du livre. La table book est la bonne table ; nous voulons toutes les données qu'elle contient, quelles que soient les critiques.

Comme vous pouvez le constater, la syntaxe reste la même que pour INNER JOIN et LEFT JOIN.

Remarque : SQL accepte à la fois RIGHT JOIN et RIGHT OUTER JOIN.

Résultat de la solution :

La requête renvoie tous les titres de livres, leurs critiques et leurs auteurs. Lorsqu'il n'y a pas de critique ou d'information sur l'auteur, une adresse NULL est affichée.

title	review	author
Soulless girl	An incredible book	Sylvia Jones
Soulless girl	Great, although it has some flaws	Jessica Parker
…	…	…
Guarding The Emperor	NULL	NULL
Companions And Officers	NULL	NULL
Blacksmith With Silver	NULL	NULL
…	…	…

JOINTURE COMPLÈTE

Voici un autre type de jointure utile dans certains scénarios : la jointure FULL JOIN. Il s'agit d'une combinaison de LEFT JOIN et RIGHT JOIN. Elle montre les lignes correspondantes des deux tables, les lignes qui n'ont pas de correspondance dans la table de gauche et les lignes qui n'ont pas de correspondance dans la table de droite. En bref, il affiche toutes les données des deux tables.

Vous pouvez en savoir plus sur la manière et le moment d'utiliser le FULL JOIN.

Exercice 7 : Lister tous les livres et tous les auteurs

Exercice : Affichez le titre de chaque livre ainsi que le nom de son auteur. Afficher tous les livres, même ceux qui n'ont pas d'auteur. Afficher tous les auteurs, même ceux qui n'ont pas encore publié de livre. Utilisez une adresse FULL JOIN.

Solution :

SELECT
  title,
  name
FROM book
FULL JOIN author
  ON book.author_id = author.id;

Explication de la solution : La question demande d'afficher tous les livres, mais aussi tous les auteurs - FULL JOIN est parfait pour faire cela de manière élégante.

Nous sélectionnons le titre du livre et le nom de l'auteur. Ensuite, nous FULL JOIN le tableau book avec le tableau author. La condition de jonction est que l'identifiant de l'auteur doit être le même dans les deux tables. Là encore, la syntaxe est la même que pour tous les types de jointure précédents.

Remarque : SQL accepte à la fois FULL JOIN et FULL OUTER JOIN.

Résultat de la solution :

La sortie indique tous les livres et tous les auteurs, que les auteurs ou les livres existent ou non dans les deux tables.

title	name
Gone With The Wolves	Marcella Cole
Companions And Officers	Lisa Mullins
…	…
NULL	Daniel Branson
…	…
Weep Of The West	NULL

Jointure de 3 tables ou plus

Oui, les jointures SQL permettent de joindre plus de deux tables. Nous verrons comment le faire dans cette partie de la pratique des jointures SQL. Vous trouverez une explication plus détaillée des jointures multiples ici.

Nous avons également besoin d'un nouvel ensemble de données, alors présentons-le.

Jeu de données 2

La première table de l'ensemble de données est department. Ses colonnes sont les suivantes :

• id - L'identifiant unique du département.
• name - Le nom du département, c'est-à-dire l'endroit où un type de produit particulier est vendu.

Voici les données de la table.

id	name
1	fruits
2	vegetables
3	seafood
4	deli
5	bakery
6	meat
7	dairy

La deuxième table est productet se compose des colonnes suivantes :

• id - L'ID d'un produit donné.
• name - Le nom du produit.
• department_id - L'ID du rayon où se trouve le produit.
• shelf_id - L'ID de l'étagère du département où se trouve le produit.
• producer_id - L'identifiant de l'entreprise qui fabrique ce produit.
• price - Le prix du produit.

Voici l'aperçu des données :

id	name	department_id	shelf_id	producer_id	price
1	Apple	1	1	NULL	0.5
2	Avocado	1	1	7	1
3	Banana	1	1	7	0.5
4	Grapefruit	NULL	1	1	0.5
5	Grapes	1	1	4	2
…	…	…	…	…	…

Le tableau suivant est nutrition_data. Ses colonnes et ses données sont indiquées ci-dessous :

• product_id - L'ID d'un produit.
• calories - La valeur calorifique de ce produit.
• fat - La quantité de matières grasses dans ce produit.
• carbohydrate - La quantité d'hydrates de carbone dans ce produit.
• protein - La quantité de protéines dans ce produit.

product_id	calories	fat	carbohydrate	protein
1	130	0	5	1
2	50	4.5	3	1
3	110	0	30	1
4	60	0	15	1
NULL	90	0	23	0
…	…	…	…	…

Le quatrième tableau s'appelle producer. Il comporte les colonnes suivantes

• id - L'identifiant d'un producteur alimentaire donné.
• name - Le nom du producteur.

Les données de ce tableau sont présentées ci-dessous :

id	name
1	BeHealthy
2	HealthyFood Inc.
3	SupremeFoods
4	Foodie
5	Gusto
6	Baker n Sons
7	GoodFoods
8	Tasty n Healthy

Le dernier tableau de l'ensemble de données est sales_history. Elle comporte les colonnes suivantes :

• date - La date de la vente.
• product_id - L'identifiant du produit vendu.
• amount - La quantité de ce produit vendue un jour donné.

Voici également les données :

date	product_id	amount
2015-01-14	1	14
2015-01-14	1	13
2015-01-15	2	2
2015-01-16	2	6
2015-01-17	3	8
…	…	…

Exercice 8 : Afficher les produits de moins de 150 calories et leur rayon

Exercice : Dressez la liste de tous les produits qui contiennent moins de 150 calories. Pour chaque produit, indiquez son nom (renommez la colonne product) et le nom du rayon où il se trouve (nommez la colonne department).

Solution :

SELECT
  p.name AS product,
  d.name AS department
FROM department d
JOIN product p
  ON d.id = p.department_id
JOIN nutrition_data nd
  ON nd.product_id = p.id
WHERE nd.calories < 150;

Explication de la solution : Le principe général de la jonction d'un troisième (quatrième, cinquième...) tableau est qu'il suffit d'ajouter un autre JOIN. Vous pouvez voir comment procéder dans cet article expliquant les jointures multiples. Nous allons procéder de la même manière ici.

Nous joignons d'abord la table department avec la table product sur l'ID du département en utilisant JOIN. Mais nous avons également besoin de la troisième table. Pour obtenir les données, il suffit d'ajouter un autre JOIN, qui joindra la table sur l'ID du département à la table sur l'ID du département. product avec la table nutrition_data table. La syntaxe est la même que pour la première jointure. Dans ce cas, la requête joint les tables sur l'ID du produit.

Nous utilisons ensuite WHERE pour trouver les produits contenant moins de 150 calories. Enfin, nous sélectionnons les noms des produits et des départements et renommons les colonnes conformément aux instructions de l'exercice.

Remarque : vous avez probablement remarqué que les deux colonnes sélectionnées ont le même nom d'origine. Vous avez également remarqué que nous avons résolu cette ambiguïté en plaçant d'étranges noms de tables abrégés devant toutes les colonnes de la requête. Ces noms abrégés sont des alias de table, que vous donnez en les écrivant simplement après le nom de la table dans FROM ou JOIN. En donnant des alias aux tables, vous pouvez abréger leurs noms. Par conséquent, vous ne devez pas écrire leur nom complet (qui peut parfois être très long !), mais les alias courts à la place. Cela permet de gagner du temps et de l'espace.

Résultat de la solution :

Le résultat présente une liste des produits et du département auquel ils appartiennent. Elle ne comprend que les produits contenant moins de 150 calories.

product	department
Apple	fruits
Avocado	fruits
Banana	fruits
Kiwi	fruits
Lemon	fruits
…	…

Exercice 9 : Liste de tous les produits avec leurs producteurs, leurs départements et leurs glucides

Exercice : Pour chaque produit, affichez

• Le nom de l'entreprise qui l'a produit (nommez la colonne producer_name).
• Le nom du département où se trouve le produit (nommez la colonne department_name).
• Le nom du produit (nommez-le product_name).
• Nombre total de glucides dans le produit.

Votre requête doit toujours prendre en compte les produits ne contenant pas d'informations sur producer_id ou department_id.

Solution :

SELECT
  prod.name AS producer_name,
  d.name AS department_name,
  p.name AS product_name,
  nd.carbohydrate
FROM product p
LEFT JOIN producer prod
  ON prod.id = p.producer_id
LEFT JOIN department d
  ON d.id = p.department_id
LEFT JOIN nutrition_data nd
  ON nd.product_id = p.id;

Explication de la solution : La requête sélectionne les colonnes requises. Elle joint ensuite la table product avec la table producer sur l'ID du producteur à l'aide de LEFT JOIN. Nous choisissons ce type de jointure parce que nous devons inclure des produits sans données sur le producteur.

Nous ajoutons ensuite un autre LEFT JOIN. Celui-ci ajoute la table department et la jointure avec la table product table. Là encore, nous choisissons LEFT JOIN parce que nous devons afficher les produits qui n'ont pas de département.

Il existe également une troisième jointure ! Nous l'ajoutons simplement à la chaîne des jointures précédentes. Il s'agit à nouveau de LEFT JOIN, car nous ajoutons la table nutrition_data et nous la joignons à la table product table.

Il s'agit d'un sujet intéressant à explorer. Voici donc un article qui explique les jointures multiples à gauche pour vous aider.

Résultat de la solution :

Le résultat montre tous les produits avec le nom de leur producteur et de leur service, ainsi que les quantités d'hydrates de carbone :

producer_name	department_name	product_name	carbohydrate
BeHealthy	fruits	Kiwi	20
BeHealthy	vegetables	Broccoli	8
BeHealthy	meat	Chicken	NULL
BeHealthy	NULL	Grapefruit	15
HealthyFood Inc.	vegetables	Celery	4
…	…	…	…

Si vous avez besoin de plus de détails, veuillez lire comment joindre à gauche plusieurs tables en SQL.

Exercice 10 : Afficher tous les produits, les prix, les producteurs et les départements

Exercice : Pour chaque produit, affichez son nom, son prix, le nom du producteur et le nom du département.

Alias des colonnes : product_name, product_price, producer_name, et department_name, respectivement. Incluez tous les produits, même ceux qui n'ont pas de producteur ou de département. Incluez également les producteurs et les départements sans produit.

Solution :

SELECT
  p.name AS product_name,
  p.price AS product_price,
  prod.name AS producer_name,
  d.name AS department_name
FROM product p
FULL JOIN producer prod
  ON p.producer_id = prod.id
FULL JOIN department d
  ON d.id = p.department_id;

Explication de la solution : Cet exercice nécessite l'utilisation de FULL JOIN, car nous avons besoin de toutes les données des tables que nous utiliserons : product, producer, et department.

La syntaxe est la même que dans les exemples précédents. Nous joignons simplement les différentes tables (product et producer) sur l'ID du producteur et utilisons un type de jointure différent : FULL JOIN.

Le second FULL JOIN joint la table product avec la table department table.

Après avoir sélectionné les colonnes nécessaires et les avoir renommées, nous obtenons le résultat suivant.

Résultat de la solution :

La solution affiche toutes les données des tables et des colonnes sélectionnées :

product_name	product_price	producer_name	department_name
Chicken	5.5	BeHealthy	meat
Broccoli	2.5	BeHealthy	vegetables
Kiwi	0.3	BeHealthy	fruits
Grapefruit	0.5	BeHealthy	NULL
Cucumber	0.7	HealthyFood Inc.	vegetables
…	…	…	…

Auto-jonction

Une jointure automatique n'est pas un type distinct de SQL JOIN - n'importe quelle jointure peut être utilisée pour la jointure automatique d'une table. Il s'agit simplement d'une jointure utilisée pour joindre la table à elle-même. En donnant des alias différents à la même table, celle-ci est traitée comme deux tables différentes lorsqu'elle est jointe.

Pour plus de détails, consultez notre guide illustré de l'auto-jointure SQL.

Ensemble de données 3

L'ensemble de données de cet exemple se compose d'une seule table : workshop_workers. Elle contient les colonnes suivantes.

• id - L'ID du travailleur.
• name - Le nom et le prénom du travailleur.
• specialization - La spécialisation du travailleur.
• master_id - L'ID du superviseur du travailleur.
• experience - Les années d'expérience du travailleur.
• project_id - L'ID du projet auquel le travailleur est actuellement affecté.

Voici les données :

id	name	specialization	master_id	experience	project_id
1	Mathew Conn	woodworking	NULL	20	1
2	Kate Brown	woodworking	1	4	1
3	John Doe	incrusting	5	3	1
4	John Kowalsky	watchmaking	7	2	3
5	Suzan Gregowitch	incrusting	NULL	15	4

Exercice 11 : Liste de tous les travailleurs et de leurs superviseurs directs

Exercice : Affichez les noms de tous les travailleurs ainsi que les noms de leurs superviseurs directs. Renommez les colonnes apprentice_name et master_name, respectivement. Ne considérez que les travailleurs qui ont un superviseur (c'est-à-dire un maître).

Solution :

SELECT
  apprentice.name AS apprentice_name,
  master.name AS master_name
FROM workshop_workers apprentice
JOIN workshop_workers master
  ON apprentice.master_id = master.id;

Explication de la solution : Commençons par expliquer l'auto-jonction. Le principe général est le même que pour les jointures ordinaires. Nous référençons la table dans FROM et lui donnons un alias, apprentice. Ensuite, nous utilisons JOIN et y référençons la même table. Cette fois, nous donnons à la table l'alias master. Nous prétendons en fait qu'une table contient les données relatives aux apprentis et l'autre les données relatives aux maîtres d'apprentissage.

Les tables sont jointes sur l'ID du maître d'apprentissage de la table apprentice et l'ID de la table master table.

Cet exemple est une utilisation typique d'une jointure automatique : la table possède une colonne (master_id) qui fait référence à une autre colonne de la même table (id). Les deux colonnes indiquent l'identifiant du travailleur. Lorsqu'il y a NULL dans master_id, cela signifie que le travailleur n'a pas de maître. En d'autres termes, il est le maître.

Après l'auto-jonction, il suffit de sélectionner les colonnes requises et de les renommer.

Résultat de la solution :

Le résultat montre tous les apprentis et leurs superviseurs directs.

apprentice_name	master_name
Kate Brown	Mathew Conn
John Doe	Suzan Gregowitch
John Kowalsky	Joe Darrington
Peter Parker	Joe Darrington
Mary Smith	Mathew Conn
Carlos Bell	Suzan Gregowitch
Dennis Wright	Joe Darrington

Jointures non équitables

Le dernier sujet que nous aborderons dans cette pratique sur les jointures SQL est celui des jointures non égales. Les jointures que nous avons utilisées jusqu'à présent sont appelées jointures équi car elles utilisent le signe d'égalité (=) dans la condition de jointure. Les jointures non équi sont toutes les autres jointures qui utilisent d'autres opérateurs - opérateurs de comparaison (<, >, <=, >=, !=, <>), l'opérateur BETWEEN, ou toute autre condition logique - pour joindre les tables.

Ensemble de données 4

Nous utiliserons l'ensemble de données composé de deux tableaux. La première table est car. Voici ses colonnes :

• id - L'identifiant de la voiture dans la base de données.
• model - Le modèle de la voiture.
• brand - La marque de la voiture.
• original_price - Le prix d'origine de la voiture à l'état neuf.
• mileage - Le kilométrage total de la voiture.
• prod_year - l'année de production de la voiture.

Les données ressemblent à ceci :

id	model	brand	original_price	mileage	prod_year
1	Speedster	Teiko	80,000	150,000	1999
2	Roadmaster	Teiko	110,000	30,000	1980
3	Sundry	Teiko	40,000	25,000	1991
4	Furu	Domus	50,000	10,000	2002
5	Emperor	Domus	65,000	140,000	2005
6	King	Domus	200,000	6,000	1981
7	Empress	Domus	60,000	7,600	1997
8	Fury	Tatsu	150,000	13,000	1993

Le deuxième tableau est charity_auction avec les colonnes suivantes :

• car_id - L'identifiant de la voiture.
• initial_price - Le prix initial de la voiture (c'est-à-dire le prix de départ).
• final_price - Le prix réel lorsque la voiture a été vendue.
• buyer_id - L'identifiant de la personne qui a acheté la voiture.

Voici les données :

car_id	initial_price	final_price	buyer_id
1	65,000	NULL	NULL
3	35,000	50,000	1
5	50,000	120,000	3
6	350,000	410,000	4
7	65,000	NULL	NULL

Exercice 12 : afficher les voitures ayant un kilométrage supérieur à celui d'une voiture spécifique

Exercice : Indiquez le modèle, la marque et le prix final de chaque voiture vendue aux enchères. Ne considérez que les voitures vendues qui ont un kilométrage supérieur à celui de la voiture dont le nom est id = 4.

Solution :

SELECT
  car.model,
  car.brand,
  car.final_price
FROM car
JOIN charity_auction ca
  ON car.id = ca.car_id
JOIN car car2
  ON car.mileage > car2.mileage
WHERE car2.id = 4
  AND final_price IS NOT NULL;

Explication de la solution : Nous sélectionnons le modèle de voiture, la marque et le prix final.

Dans le premier site JOIN, nous joignons le tableau car avec la table charity_auction table. Les tables sont jointes lorsque les identifiants des voitures sont identiques. Il s'agit de notre équi ordinaire JOIN.

Nous ajoutons le deuxième JOIN, qui est une jointure automatique. Il ajoute la table car afin de pouvoir filtrer les données à l'aide de la condition de jointure non équivoque. La condition renverra toutes les voitures de la table car et toutes les voitures de la table car2 avec le kilométrage le plus bas. Il s'agit d'une condition non équivoque car elle utilise l'opérateur "supérieur à" ( > ). La syntaxe est la même, mais il y a > au lieu de = cette fois.

Enfin, nous devons filtrer les données à l'aide de WHERE. Nous ne souhaitons pas comparer le kilométrage de toutes les voitures. Nous voulons afficher les voitures dont le kilométrage est supérieur à celui de la voiture dont l'adresse est id = 4. C'est ce que fait la première condition de filtrage.

Nous ajoutons une autre condition de filtrage qui stipule que le prix final ne doit pas être NULL, c'est-à-dire que la voiture doit avoir été vendue aux enchères.

Résultat de la solution :

Le résultat indique deux voitures :

model	brand	final_price
Sundry	Teiko	50,000
Emperor	Domus	120,000

Les jointures en SQL C'est en forgeant qu'on devient forgeron. Plus d'entraînement ? Encore plus parfait !

Douze exercices de jointure SQL représentent une bonne quantité de pratique. Grâce à ces exercices, vous avez pu apprendre et pratiquer tous les sujets de jointure les plus courants qui posent problème aux utilisateurs débutants et intermédiaires.

Il ne vous reste plus qu'à continuer ! C'est en pratiquant encore plus que l'on devient encore plus parfait. Si vous avez aimé nos exercices, vous pouvez en trouver d'autres dans notre cours sur les jointures SQL ou dans l'article sur les questions d'entretien sur les jointures SQL.

J'espère que vous réussirez tous les exercices qui vous y attendent !