Pratique en ligne des requêtes SQL de base : 20 exercices pour les débutants

Ces 20 exercices sont exactement ce dont les débutants ont besoin pour s'entraîner aux requêtes SQL. Essayez de résoudre chacun d'entre eux, puis regardez les solutions. Si quelque chose doit être clarifié, il y a des explications pour chaque solution.

Dans cet article, vous parlerez moins que d'habitude. Au lieu de cela, vous allez écrire les réponses à des requêtes d'entraînement SQL. (Ne vous inquiétez pas, nous avons inclus les solutions si vous êtes bloqué.) L'objectif est de vous donner, en tant que débutant, de nombreuses occasions de pratiquer les requêtes SQL.

J'ai sélectionné vingt exemples à partir de notre site Pratique SQL de base : Des courses aux requêtes ! Si, à la fin de l'article, vous sentez que vous avez besoin de vous entraîner davantage - je vous le recommande vivement -, vous trouverez près de 100 exercices SQL interactifs supplémentaires dans ce cours. Ils couvrent des sujets tels que l'interrogation d'une table, en utilisant JOINs, le tri de données avec ORDER BY, l'agrégation de données et l'utilisation de GROUP BY, le traitement de NULLs, les opérations mathématiques et l'écriture de sous-requêtes.

Ce sont des sujets que tout débutant en SQL doit bien connaître avant de passer à des sujets plus complexes. La meilleure façon d'apprendre quoi que ce soit en SQL est d'écrire systématiquement votre propre code. Ainsi, vous maîtriserez la syntaxe SQL et ses fonctionnalités de base, et vous comprendrez la résolution de problèmes. Après tout, l'intérêt de connaître le langage SQL est de savoir comment utiliser les données pour résoudre des problèmes.

Et vous pourriez aller encore plus loin ! Nous avons le La pratique du SQL et le cours mensuel La pratique du SQL pour une pratique encore plus poussée des requêtes SQL.

Ceci étant dit, plongeons directement dans la pratique du SQL, en commençant par l'ensemble de données.

• Jeu de données
• Exercice 1 : Afficher les dates finales de tous les événements et les points de vent
• Exercice n°2 : afficher toutes les finales pour lesquelles le vent était supérieur à 0,5 point
• Exercice n° 3 : afficher toutes les données pour tous les marathons
• Exercice n°4 : afficher tous les résultats finaux pour les coureurs qui n'ont pas participé aux courses
• Exercice n°5 : afficher toutes les données des résultats pour les coureurs non partants
• Exercice n°6 : afficher les noms des coureurs des disciplines masculines de moins de 500 mètres
• Exercice n°7 : trier les noms et abréviations des pays
• Exercice n°8 : trier les noms et prénoms des athlètes
• Exercice n°9 : trier les résultats finaux sur trois heures
• Exercice n°10 : afficher les noms et places des 3 meilleurs athlètes
• Exercice n°11 : afficher tous les marathons avec le nom de la compétition, l'année de la compétition et le nom de la discipline
• Exercice n°12 : afficher les scores de Mo Farah pour toutes les disciplines
• Exercice n°13 : afficher les noms des compétitions et le nombre d'épreuves
• Exercice n°14 : afficher les noms des athlètes les plus populaires
• Exercice #15 : Montrer chaque pays et le nombre d'athlètes qui ont terminé sans place
• Exercice n°16 : calculer l'allure moyenne de chaque course à pied
• Exemple n° 17 : Trouver tous les temps supérieurs à la moyenne pour les courses de 1 500 mètres
• Exercice n°18 : Trouver tous les athlètes qui ont couru dans au moins deux épreuves d'une compétition
• Exercice n°19 : Afficher les coureurs qui n'ont terminé qu'en première position
• Exercice n°20 : Trouver tous les athlètes qui n'ont pas pris le départ et qui ont gagné au moins une fois

Pratique de la requête SQL

Ensemble de données

Le jeu de données contient des données sur les finales des compétitions de course sur piste dans les championnats d'athlétisme : Jeux olympiques de Rio de Janeiro en 2016, Championnats du monde d'athlétisme de l'IAAF de Londres en 2017 et Championnats du monde d'athlétisme de l'IAAF de Doha en 2019.

Les données sont stockées dans six tables : competition, event, discipline, final_result, athleteet nationality. Le schéma est illustré ci-dessous :

Les informations relatives au concours sont stockées dans le tableau competition. Elle comporte les colonnes suivantes :

• id - L'ID du concours et la clé primaire de la table.
• name - Le nom de la compétition.
• start_date - Le premier jour de la compétition.
• end_date - Le dernier jour de la compétition.
• year - L'année au cours de laquelle la compétition a eu lieu.
• location - Le lieu de la compétition.

Voici les données du tableau.

id	name	start_date	end_date	year	location
7093747	Rio de Janeiro Olympic Games	2016-08-12	2016-08-21	2016	Estádio Olímpico, Rio de Janeiro (BRA)
7093740	London IAAF World Championships in Athletics	2017-08-04	2017-08-13	2017	Olympic Stadium, London (GBR)
7125365	IAAF World Championships in Athletics	2019-09-27	2019-10-06	2019	Khalifa International Stadium, Doha (QAT)

Le tableau discipline contient des informations pour toutes les disciplines de course à pied. Elle comporte les colonnes suivantes :

• id - L'ID de la discipline et la clé primaire de la table.
• name - Le nom de la discipline.
• is_men - TRUE s'il s'agit d'une discipline masculine, FALSE s'il s'agit d'une discipline féminine.
• distance - La distance de la discipline, en mètres.

Il s'agit d'un aperçu des cinq premières lignes de données :

id	name	is_men	distance
1	Men's 100m	TRUE	100
2	Men's 200m	TRUE	200
3	Men's 400m	TRUE	400
4	Men's 800m	TRUE	800
5	Men's 1500m	TRUE	1,500

Le tableau suivant est celui des événements, qui contient des informations sur chaque événement particulier :

• id - L'ID de l'événement et la clé primaire du tableau.
• competition_id - Relie l'événement à une compétition.
• discipline_id - Relie l'événement à une discipline.
• final_date - La date de la finale de l'événement.
• wind - Les points de vent pendant la finale.

Voici les cinq premières lignes de ce tableau :

id	competition_id	discipline_id	final_date	wind
1	7093747	1	2016-08-14	0.2
2	7093747	2	2016-08-18	-0.5
3	7093747	3	2016-08-14	0
4	7093747	4	2016-08-15	0
5	7093747	5	2016-08-20	0

Les données relatives à chaque athlète se trouvent dans le tableau athlète:

• id - L'ID de l'athlète et la clé primaire du tableau.
• first_name - Le prénom de l'athlète.
• last_name - Le nom de famille de l'athlète.
• nationality_id - La nationalité de l'athlète.
• birth_date - La date de naissance de l'athlète.

Il s'agit des cinq premières lignes :

id	first_name	last_name	nationality_id	birth_date
14201847	Usain	BOLT	1	1986-08-21
14238562	Justin	GATLIN	2	1982-02-10
14535607	André	DE GRASSE	3	1994-11-10
14201842	Yohan	BLAKE	1	1989-12-26

Le tableau nationality contient des informations sur le pays :

• id - L'ID du pays et la clé primaire du tableau.
• country_name - Le nom du pays.
• country_abbr - L'abréviation à trois lettres du pays.

Voici un aperçu de cette table sur cinq lignes :

id	country_name	country_abbr
1	Jamaica	JAM
2	United States	USA
3	Canada	CAN
4	South Africa	RSA
5	Côte d’Ivoire	CIV

La dernière table est final_result. Elle contient des informations sur les participants et leurs résultats à un événement particulier :

• event_id - L'ID de l'épreuve.
• athlete_id - Le nom de l'athlète
• result - Le temps/le score de l'athlète (peut être NULL).
• place - La place obtenue par l'athlète (peut être NULL).
• is_dsq - TRUE en cas dedisqualification.
• is_dnf - TRUE si l'athlète n'a pas terminé lacourse.
• is_dns - TRUE si l'athlète n'a pas commencé lacourse.

Voici l'instantané :

event_id	athlete_id	result	place	is_dsq	is_dns	is_dnf
1	14201847	0:00:10	1	FALSE	FALSE	FALSE
1	14238562	0:00:10	2	FALSE	FALSE	FALSE
1	14535607	0:00:10	3	FALSE	FALSE	FALSE
1	14201842	0:00:10	4	FALSE	FALSE	FALSE
1	14417763	0:00:10	5	FALSE	FALSE	FALSE

Maintenant que vous avez jeté un coup d'œil à l'ensemble de données, commençons à nous entraîner aux requêtes SQL de base ! Tous les exercices requièrent des connaissances en SQL, alors assurez-vous de connaître tous les éléments de base d'une requête SQL.

Exercice 1 : Afficher les dates finales de tous les événements et les points de vent

Exercice : Trouver les dates finales de tous les événements et les points de vent.

Solution :

SELECT
  final_date,
  wind
FROM event;

Explication : Les données dont vous avez besoin se trouvent dans le tableau événement. Vous devez sélectionner deux colonnes dans ce tableau : final_date et wind. Pour ce faire, vous écrivez la première colonne dans l'instruction SELECT. Ensuite, vous écrivez le nom de la deuxième colonne et séparez les noms de colonnes par une virgule.

Enfin, vous faites référence à la table dans la clause FROM.

Résultat :

final_date	wind
2016-08-14	0.2
2016-08-18	-0.5
2016-08-14	0
2016-08-15	0
2016-08-20	0

Exercice #2 : Afficher toutes les finales où le vent était supérieur à 0,5 points

Exercice : Afficher toutes les dates des finales où le vent était supérieur à 0,5 point.

Solution :

SELECT final_date
FROM event
WHERE wind > 0.5;

Explication : Tout d'abord, sélectionnez la colonne final_date dans le tableau event. Vous obtiendrez ainsi une liste de toutes les finales. Cependant, vous n'avez pas besoin de toute la liste, mais seulement des finales où le vent était supérieur à 0,5.

Vous devez donc filtrer les données à l'aide de la clause WHERE. Dans cette clause, vous écrivez le nom de la colonne que vous souhaitez filtrer ; dans ce cas, il s'agit de la colonne wind. Pour obtenir le vent supérieur à 0,5, utilisez l'opérateur logique "supérieur à" (>).

Résultat :

final_date
2017-08-11
2019-09-28
2019-10-02

Exercice #3 : Afficher toutes les données pour tous les marathons

Exercice : Affichez les données relatives à la discipline pour tous les marathons.

Solution :

SELECT *
FROM discipline	
WHERE name LIKE '%Marathon%';

Explication : Pour sélectionner toutes les colonnes, il n'est pas nécessaire d'écrire leur nom explicitement. Il existe une abréviation pour "toutes les colonnes" appelée astérisque (*). Au lieu des noms des colonnes, mettez simplement un astérisque dans SELECT.

Ensuite, lorsque vous souhaitez obtenir des données de la table disciplinevous y faites référence dans FROM.

Enfin, vous devez filtrer les données. Utilisez WHERE et l' opérateur LIKE. Cet opérateur examine les données textuelles de la colonne et renvoie toutes les lignes contenant le texte de la condition WHERE. En d'autres termes, la condition recherchera le mot "Marathon". Vous devez mettre le mot entre guillemets simples.

Cependant, vous ne connaissez pas le nom exact de la discipline ; vous savez simplement qu'elle doit contenir ce mot. Il peut se trouver n'importe où dans le nom de la discipline : au début, au milieu ou à la fin. Pour chercher n'importe où dans la chaîne, placez l'opérateur modulo (%) avant et après le mot que vous recherchez.

Résultat :

id	name	is_men	distance
8	Men's Marathon	TRUE	42,195
16	Women's Marathon	FALSE	42,195

Exercice #4 : Afficher tous les résultats finaux pour les coureurs qui ne participent pas aux courses

Exercice : Afficher toutes les données des résultats finaux des coureurs qui ne se sont pas classés.

Solution :

SELECT *
FROM final_result
WHERE place IS NULL;

Explication : Vous avez besoin de toutes les colonnes, donc utilisez un astérisque dans SELECT et faites référence à la table final_result dans FROM.

Vous ne devez afficher que les résultats pour lesquels les coureurs n'ont pas obtenu de place. Cette fois encore, vous utiliserez WHERE et filtrerez sur la colonne place. Si un coureur termine sans place, la colonne place sera vide (c'est-à-dire NULL). Vous avez besoin de l'opérateur IS NULL après le nom de la colonne pour renvoyer toutes ces lignes.

Il serait bon de savoir ce qu'est un NULL en SQL avant d'utiliser l'opérateur IS NULL.

Sortie :

event_id	athlete_id	result	place	is_dsq	is_dns	is_dnf
6	14464221	NULL	NULL	TRUE	FALSE	FALSE
7	14530623	NULL	NULL	FALSE	FALSE	TRUE
7	14573513	NULL	NULL	FALSE	FALSE	TRUE
8	14167397	NULL	NULL	FALSE	FALSE	TRUE
8	14177784	NULL	NULL	FALSE	FALSE	TRUE

Exercice #5 : Afficher toutes les données de résultats pour les coureurs non-partants

Exercice : Afficher toutes les données de résultats pour les coureurs qui n'ont pas commencé la course.

Solution :

SELECT *
FROM final_result
WHERE is_dns IS TRUE;

Explication : Sélectionnez toutes les colonnes du tableau final_result en utilisant un astérisque et en faisant référence au tableau dans FROM.

Ensuite, vous voulez utiliser WHERE et filtrer la colonne par is_dns. Si le coureur n'a pas pris le départ, cette colonne aura la valeur TRUE. Vous devez donc utiliser l'opérateur IS TRUE après le nom de la colonne.

Résultat : Voici la sortie complète :

event_id	athlete_id	result	place	is_dsq	is_dns	is_dnf
14	14451797	NULL	NULL	FALSE	TRUE	FALSE
16	14296979	NULL	NULL	FALSE	TRUE	FALSE
19	14176330	NULL	NULL	FALSE	TRUE	FALSE
22	14367867	NULL	NULL	FALSE	TRUE	FALSE
24	14219653	NULL	NULL	FALSE	TRUE	FALSE
24	14225279	NULL	NULL	FALSE	TRUE	FALSE
32	14751813	NULL	NULL	FALSE	TRUE	FALSE
41	14291986	NULL	NULL	FALSE	TRUE	FALSE

Exercice #6 : Afficher les noms des coureurs de moins de 500 mètres dans les disciplines masculines

Exercice : Afficher uniquement les noms des disciplines masculines où la distance à courir est inférieure à 500 mètres.

Solution :

SELECT name
FROM discipline
WHERE is_men IS TRUE
  AND distance < 500;

Explication : Tout d'abord, sélectionnez le nom de la colonne dans le tableau discipline.

Vous devez à nouveau filtrer les données - cette fois-ci, en mettant deux conditions dans WHERE.

La première condition est qu'il s'agit d'une discipline masculine. Vous devez donc filtrer la colonne is_men à l'aide de l'opérateur IS TRUE. Ensuite, vous ajoutez la deuxième condition : les valeurs de la colonne distance doivent être inférieures à 500. Cette condition utilise l'opérateur moins que (<). Comme les deux conditions doivent être satisfaites, séparez les conditions à l'aide de l'opérateur AND.

Résultat :

name
Men's 100m
Men's 200m
Men's 400m

Exercice #7 : Trier les noms et abréviations de pays

Exercice : Affichez tous les noms et abréviations des pays. Triez les résultats par ordre alphabétique des noms de pays.

Solution :

SELECT
  country_name,
  country_abbr
FROM nationality
ORDER BY country_name ASC;

Explication : Sélectionnez le nom du pays et son abréviation dans le tableau nationality.

Pour trier les résultats, utilisez la clause ORDER BY. Vous voulez trier par nom de pays, alors écrivez country_name dans ORDER BY. La sortie doit être triée par ordre alphabétique, utilisez donc le mot-clé ASC (ascendant) après le nom de la colonne.

Résultat : Voici les cinq premières lignes du résultat :

country_name	country_abbr
Afghanistan	AFG
Algeria	ALG
American Samoa	ASA
Andorra	AND
Angola	ANG

Exercice n°8 : trier les noms et prénoms des athlètes

Exercice : Affichez le nom et le prénom de chaque athlète. Triez les résultats par ordre décroissant du prénom de l'athlète. Si plusieurs athlètes portent le même nom, affichez leurs noms de famille triés par ordre décroissant.

Solution :

SELECT
  first_name,
  last_name
FROM athlete
ORDER BY first_name DESC, last_name DESC;

Explication : Sélectionnez le nom et le prénom dans le tableau athlete.

Ajoutez ensuite la clause ORDER BY. La première condition de tri est le prénom, de manière décroissante, en ajoutant DESC après le nom de la colonne. La deuxième condition de tri consiste à trier par le nom de famille, également de manière décroissante. Inscrivez à nouveau le nom de la colonne et ajoutez DESC. Les conditions doivent être séparées par une virgule.

Résultat : Voici les cinq premières lignes du résultat :

first_name	last_name
Zsófia	ERDÉLYI
Zouhair	AWAD
Zoey	CLARK
Zoe	HOBBS
Zoe	BUCKMAN

Exercice n° 9 : trier les résultats finaux sur trois heures

Exercice : Pour tous les résultats finaux, affichez les heures qui durent au moins trois heures. Triez les lignes en fonction du résultat, par ordre décroissant.

Solution :

SELECT result
FROM final_result
WHERE result >= INTERVAL '3 hours'
ORDER BY result DESC;

Explication : Sélectionnez la colonne result dans le tableau final_result.

Ensuite, utilisez WHERE pour trouver les résultats inférieurs à trois heures. Vous pouvez utiliser les opérateurs "supérieur ou égal" (>=) et INTERVAL.

Les données de la colonne des résultats sont formatées sous forme d'heure. Vous devez donc utiliser INTERVAL pour obtenir la partie spécifique (intervalle) de ces données. Dans ce cas, il s'agit de trois heures. Il suffit d'écrire "3 hours" après INTERVAL.

Enfin, triez les résultats par ordre décroissant.

Résultat : Voici les cinq premières lignes de la sortie :

result
3:20:20
3:16:11
3:15:18
3:11:31
3:11:05

Exercice n° 10 : Afficher les noms et lieux des 3 meilleurs athlètes

Exercice : Pour chaque athlète qui est monté sur le podium (c'est-à-dire qui a terminé dans les 3 premiers), indiquez son nom, son prénom et sa place.

Solution :

SELECT
  a.last_name,
  a.first_name,
  fin.place
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place <= 3;

Explication : Dans cet exercice, vous devez utiliser des données provenant de deux tableaux : athlete et final_result. Commençons donc l'explication par la clause FROM.

Vous faites référence à la table athlete et lui donnez un alias "a", de sorte que vous n'aurez pas besoin d'écrire le nom complet de la table ailleurs dans la requête. Pour obtenir des données d'une autre table, vous devez également joindre les tables. Dans ce cas, utilisez JOIN, qui renverra uniquement les lignes correspondantes des deux tables. Pour ce faire, il suffit de faire référence à la table final_result dans JOIN et en ajoutant l'alias "fin".

Ensuite, vous devez spécifier la condition de jointure en utilisant le mot-clé ON. Les tables sont jointes sur des colonnes partagées : id from athlete et athlete_id de final_result. Vous recherchez des lignes où les valeurs de ces deux colonnes sont égales, donc mettez un signe égal (=) entre elles. Devant chaque nom de colonne, mettez l'alias de la table suivi d'un point pour que la base de données sache dans quelle table se trouve cette colonne.

Maintenant que vous avez joint les tables, vous pouvez sélectionner les colonnes. Devant chaque nom de colonne, mettez l'alias de la table pour la même raison que celle expliquée précédemment. Vous avez maintenant les noms et prénoms des athlètes ainsi que leurs lieux de résidence.

Pour terminer, il suffit de filtrer les données à l'aide de WHERE et de la colonne place. Vous recherchez les podiums, les valeurs doivent donc être inférieures ou égales à trois. Utilisez l'opérateur "inférieur ou égal" (<=).

Cette pratique de requête SQL nécessite que vous connaissiez Les jointures en SQL. Si vous n'êtes toujours pas sûr de leur fonctionnement, consultez ces questions d'entraînement Les jointures en SQL avant de passer à d'autres exercices.

Résultat : Voici les cinq premières lignes de la sortie :

last_name	first_name	place
BOLT	Usain	3
BOLT	Usain	1
BOLT	Usain	1
GATLIN	Justin	2
GATLIN	Justin	1

Exercice #11 : Afficher tous les marathons avec leur nom de compétition, l'année de la compétition et le nom de la discipline

Exercice : Afficher tous les marathons, le nom (renommer cette colonne competition_name) et l'année de la compétition, ainsi que le nom de la discipline (renommer cette colonne discipline_name).

Solution :

SELECT
  c.name AS competition_name,
  c.year,
  d.name AS discipline_name
FROM competition c
JOIN event e
  ON e.competition_id = c.id
JOIN discipline d
  ON e.discipline_id = d.id
WHERE d.name LIKE '%Marathon%';

Explication : Cet exercice montre comment joindre plusieurs tables. Le principe est le même qu'avec deux tables. Vous ajoutez simplement plus de JOINs et les conditions de jointure.

Dans ce cas, vous joignez les tables competition et event où e.competition_id est égal à c.id column.

Ensuite, vous devez ajouter la table discipline à la chaîne de jonction. Réécrivez JOIN et faites référence à la table discipline. Ajoutez la condition de jonction : la colonne discipline_id de la table event doit être égale à la colonne id de la table discipline de la table.

Sélectionnez maintenant les colonnes requises, en n'oubliant pas de placer l'alias de la table devant chaque colonne. Alias competition_name et discipline_name en utilisant le mot-clé AS pour leur donner les noms de colonnes décrits dans les instructions.

Enfin, filtrez les résultats pour n'afficher que les disciplines marathoniennes.

Résultat :

competition_name	year	discipline_name
Rio de Janeiro Olympic Games	2016	Men's Marathon
Rio de Janeiro Olympic Games	2016	Women's Marathon
London IAAF World Championships in Athletics	2017	Men's Marathon
London IAAF World Championships in Athletics	2017	Women's Marathon
IAAF World Championships in Athletics	2019	Men's Marathon

Exercice #12 : Afficher les résultats de Mo Farah pour toutes les disciplines

Exercice : Affichez les résultats de Mo Farah (ID d'athlète de 14189197) pour toutes les disciplines. Afficher NULL s'il n'a jamais participé à une discipline donnée. Affichez les noms, dates, lieux et résultats de toutes les disciplines masculines.

Solution :

SELECT
  d.name AS discipline_name,
  e.final_date,
  fin.place,
  fin.result
FROM discipline d
LEFT JOIN event e
  ON e.discipline_id = d.id
LEFT JOIN final_result fin
  ON fin.event_id = e.id
 AND athlete_id = 14189197
WHERE is_men IS TRUE;

Explication : Joignez les tableaux discipline et event sur les colonnes discipline_id et id. Vous devez utiliser LEFT JOIN. Ce type de jointure renvoie toutes les lignes de la première table (gauche) et uniquement les lignes correspondantes de la deuxième table (droite). S'il n'y a pas de lignes correspondantes, les valeurs seront NULL. C'est idéal pour cet exercice, car vous devez afficher toutes les disciplines et utiliser NULLs si Mo Farah n'a jamais participé à la discipline.

La jointure suivante est également une jointure LEFT JOIN. Elle joint la table event avec la table final_result. La première condition de jointure ici joint les tables sur les colonnes event_id et id. Vous devez également inclure la deuxième condition en ajoutant le mot-clé AND. Cette deuxième condition ne recherchera que les données de Mo Farah, c'est-à-dire l'athlète dont l'ID est 14189197.

Enfin, utilisez WHERE pour ne rechercher que les disciplines masculines.

Résultat :

discipline_name	final_date	place	result
Men's 5000m	2016-08-20	1	0:13:03
Men's 10,000m	2016-08-13	1	0:27:05
Men's 5000m	2017-08-12	2	0:13:33
Men's 10,000m	2017-08-04	1	0:26:50
Men's 800m	2017-08-08	NULL	NULL
Men's Marathon	2019-10-05	NULL	NULL
Men's 100m	2017-08-05	NULL	NULL

Exercice #13 : Afficher les noms des compétitions et le nombre d'épreuves

Exercice : Afficher les noms de toutes les compétitions et le nombre d'épreuves pour chaque compétition.

Solution :

SELECT
  c.name AS competition_name,
  COUNT(*) AS events_held
FROM competition c
JOIN event e
  ON e.competition_id = c.id
GROUP BY c.name;

Explication : Tout d'abord, affichez le nom de la colonne du tableau competition et renommez la colonne en competition_name.

Ensuite, utilisez la fonction d'agrégation COUNT(*) pour compter le nombre d'événements qui ont eu lieu. La fonction COUNT() avec un astérisque comptera toutes les lignes de la sortie, y compris les NULL. Pour une meilleure lisibilité, nous donnons à la colonne résultante le nom d'alias events_held.

Les tables que nous rejoignons sont competition et event. Enfin, pour obtenir le nombre d'épreuves par compétition, vous devez GROUP BY le nom de la compétition.

Résultat :

competition_name	events_held
IAAF World Championships in Athletics	15
Rio de Janeiro Olympic Games	16
London IAAF World Championships in Athletics	16

Exercice #14 : Afficher les noms d'athlètes les plus populaires

Exercice : Affichez les noms d'athlètes les plus populaires. Les noms sont populaires si au moins cinq athlètes les partagent. À côté du nom, indiquez également le nombre d'athlètes portant ce nom. Triez les résultats de manière à ce que les noms les plus populaires apparaissent en premier.

Solution :

SELECT
  first_name,
  COUNT(*) AS name_count
FROM athlete
GROUP BY first_name
HAVING COUNT(*) >= 5
ORDER BY COUNT(*) DESC;

Explication : Sélectionnez d'abord les prénoms et comptez-les à l'aide de COUNT(*). Ensuite, regroupez les résultats en fonction du prénom de l'athlète. Vous disposez à présent de tous les noms et de leur nombre.

Mais vous devez afficher uniquement les noms dont le nombre est supérieur à cinq. Vous y parviendrez en utilisant la clause HAVING. Elle a la même utilité que WHERE, mais HAVING est utilisée pour filtrer des données agrégées.

Enfin, triez les résultats en fonction du nombre de noms, du plus élevé au plus bas. Vous ne pouvez pas simplement écrire le nom de la colonne name_count dans ORDER BY car le tri est effectué avant l'agrégation ; SQL ne reconnaîtra pas le nom de la colonne. Au lieu de cela, copiez COUNT(*) et triez par ordre décroissant.

Cet exercice montre un problème SQL typique qui nécessite de filtrer des données avec une fonction d'agrégation.

Sortie :

first_name	name_count
David	9
Daniel	7
Michael	7
Jessica	6
Alex	6
Sarah	5
Diana	5
Jonathan	5
Emmanuel	5
Isaac	5
Julian	5
Anna	5

Exercice #15 : Montrer chaque pays et le nombre d'athlètes qui ont terminé sans place

Exercice : Affichez tous les pays avec le nombre de leurs athlètes qui ont terminé sans place. Afficher 0 s'il n'y en a pas. Triez les résultats par ordre décroissant du nombre d'athlètes et par ordre croissant du nom du pays.

Solution :

SELECT
  n.country_name,
  COUNT(fin.athlete_id) AS athletes_no
FROM nationality n
LEFT JOIN athlete a
  ON n.id = a.nationality_id
LEFT JOIN final_result fin
  ON a.id = fin.athlete_id
 AND fin.place IS NULL
GROUP BY n.country_name
ORDER BY
  COUNT(fin.athlete_id) DESC,
  n.country_name ASC;

Explication : Vous devez conserver toutes les lignes du tableau nationality vous devez donc le LEFT JOIN avec le tableau athlete table. C'est ce que vous faites lorsque id est égal à nationality_id. Ensuite, LEFT JOIN une autre table où id de la table est égal à de la table. athlete table est égal à athlete_id de la final_result tableau.

Comme vous avez besoin de toutes les lignes de nationalité, vous ne pouvez pas utiliser la condition IS NULL dans WHERE. Il y a une solution : déplacez-la dans la clause ON, et vous obtiendrez toutes les valeurs dont l'emplacement est NULL.

Vous pouvez maintenant sélectionner la colonne country_name. Utilisez également la fonction COUNT() sur la colonne athlete_id pour obtenir le nombre d'athlètes qui ont terminé sans place. Vous ne pouvez pas utiliser COUNT(*) ici car cela aurait compté f, et vous avez besoin du nombre d'athlètes concrets.

Pour obtenir la valeur de comptage par pays, regroupez les résultats par nom de pays.

Enfin, triez les résultats par nombre d'athlètes en ordre décroissant et par nom de pays en ordre croissant.

Résultat : Voici les cinq premières lignes du résultat :

country_name	athletes_no
Bahrain	8
Ethiopia	6
Turkey	6
Kenya	5
South Africa	5

Exercice n°16 : Calculer l'allure moyenne de chaque course à pied

Exercice : Calculez l'allure moyenne pour chaque course et affichez-la dans la colonne nommée average_pace.

Solution :

SELECT fin.result / (d.distance * 1.0 / 1000) AS average_pace
FROM event e
JOIN discipline d
  ON e.discipline_id = d.id
JOIN final_result fin
  ON fin.event_id = e.id;

Explication : Pour obtenir l'allure moyenne par course, il faut diviser le résultat par la distance. C'est ce que fait la requête ci-dessus, mais avec deux modifications.

Tout d'abord, vous devez multiplier la distance par 1,0. Cette opération permet de convertir la distance en nombre décimal. Sans cela, la division pourrait donner un résultat différent, car le résultat sera divisé par le nombre entier. La deuxième modification consiste à diviser la distance par 1 000. Ce faisant, vous convertissez la distance de mètres en kilomètres.

Maintenant que vous avez le calcul, donnez à cette colonne l'alias average_pace.

Le reste de la requête correspond à ce que vous avez déjà vu dans les exemples précédents : vous joignez la table event avec la table discipline puis avec la table final_result.

Résultat : Voici les cinq premières lignes de la sortie :

average_pace
0:01:38
0:01:39
0:01:39
0:01:39
0:01:39

Exemple n° 17 : Trouver tous les temps supérieurs à la moyenne pour les courses de 1 500 mètres

Exercice : Produisez les temps de toutes les courses de 1 500 mètres. N'affichez que les temps qui sont plus rapides que le temps moyen pour cette course.

Solution :

SELECT fin.result
FROM final_result fin
JOIN event e
  ON fin.event_id = e.id
JOIN discipline d
  ON e.discipline_id = d.id
WHERE distance = 1500
  AND fin.result < (
    SELECT AVG(fin.result)
    FROM final_result fin
    JOIN event e
      ON fin.event_id = e.id
    JOIN discipline d
      ON e.discipline_id = d.id
    WHERE distance = 1500
  );

Explication : Vous devez connaître les sous-requêtes SQL pour résoudre cet exercice. Leur définition de base est qu'il s'agit de requêtes à l'intérieur d'une requête principale. Voyons comment cela fonctionne !

Sélectionnez la colonne de résultats dans le tableau final_result. Ensuite, JOIN la table avec event puis avec la table discipline tableau.

Ensuite, vous devez définir deux conditions dans WHERE. La première sélectionne uniquement les distances égales à 1 500 mètres.

La seconde recherche les données dont le résultat est inférieur à la moyenne totale des courses de 1 500 mètres. Pour calculer la moyenne, utilisez une sous-requête de la manière suivante.

Entre les parenthèses qui suivent l'opérateur de comparaison, écrivez une autre instruction SELECT (c'est-à-dire une sous-requête). Utilisez la fonction d'agrégation AVG() pour calculer la moyenne des résultats. Le reste de la requête est identique à la requête principale ; vous joignez les mêmes tables et utilisez la même condition de filtrage dans WHERE.

Résultat : Voici les premières lignes du résultat :

result
0:03:51
0:03:51
0:03:51
0:03:51
0:03:51
0:03:50
0:03:50
0:03:51

Exercice #18 : Trouver tous les athlètes qui ont participé à au moins deux épreuves lors d'une compétition

Exercice : Produire une liste d'athlètes qui ont participé à au moins deux épreuves dans une compétition. N'affichez que leurs nom et prénom.

Solution :

SELECT
  first_name,
  last_name
FROM athlete
WHERE id IN (
  SELECT fin.athlete_id
  FROM event e
  JOIN final_result fin
    ON fin.event_id = e.id
  GROUP BY e.competition_id, fin.athlete_id
  HAVING COUNT(*) >= 2
);

Explication : Commencez par sélectionner le prénom et le nom dans le tableau athlete.

Ensuite, utilisez WHERE pour définir une condition. Nous utilisons à nouveau une sous-requête pour renvoyer les données que nous voulons comparer, cette fois avec la colonne id. Cependant, dans l'exemple précédent, nous avons utilisé l'opérateur "moins que" (<) parce que la sous-requête ne renvoyait qu'une seule valeur. Cette fois, nous utilisons l'opérateur IN, qui passera en revue toutes les valeurs renvoyées par la sous-requête et renverra celles qui satisfont à la condition.

La condition est que les athlètes participent à au moins deux épreuves au cours d'une même compétition. Pour trouver ces athlètes, sélectionnez la colonne athlete_id et joignez les tables event et final_result. Regroupez ensuite les résultats en fonction des ID de compétition et d'athlète. Cet exemple montre que vous pouvez regrouper les résultats par la colonne qui n'est pas dans SELECT. Cependant, toutes les colonnes qui apparaissent dans SELECT doivent également apparaître dans GROUP BY.

Enfin, utilisez HAVING pour filtrer les données. Comptez le nombre de lignes à l'aide de COUNT(*). De cette façon, vous comptez combien de fois chaque athlète apparaît. Définissez la condition de façon à ce que seuls les athlètes dont le nombre est supérieur ou égal à deux soient pris en compte.

Résultat : Voici l'aperçu de la sortie.

first_name	last_name
Usain	BOLT
André	DE GRASSE
Aaron	BROWN
LaShawn	MERRITT
Wayde	VAN NIEKERK

Exercice n° 19 : Afficher les coureurs qui n'ont terminé qu'en première position

Exercice : Montrer tous les coureurs qui n'ont jamais terminé à une autre place que la première ; la place n'a jamais manqué pour eux. Affichez trois colonnes : id, first_name, et last_name.

Solution :

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place = 1

EXCEPT

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place != 1 OR fin.place IS NULL;

Explication : Pour cette solution, vous devez utiliser l'opérateur set EXCEPT. Les opérateurs set sont utilisés pour renvoyer les valeurs de deux ou plusieurs requêtes. EXCEPT renvoie tous les enregistrements uniques de la première requête , à l'exception de ceux renvoyés par la deuxième requête.

La première requête de la solution recherche les athlètes qui ont terminé premiers. Pour obtenir ces valeurs, sélectionnez les colonnes requises dans le tableau athlete. Ensuite, joignez la table à la table final_result. Ensuite, définissez la condition dans WHERE pour trouver uniquement les premières places.

Maintenant, écrivez le mot-clé EXCEPT et faites-le suivre de la deuxième requête.

La deuxième requête est presque identique à la première. La seule différence est la présence de deux conditions dans WHERE.

La première condition renvoie toutes les places qui ne sont pas les premières en utilisant l'opérateur "not equal" (!=). La deuxième condition recherche les places nonNULL, c'est-à-dire que la place n'a jamais été manquante pour cet athlète. Les conditions sont reliées à l'aide de l'opérateur OU car l'une d'entre elles doit être vraie ; l'athlète ne peut pas terminer en dessous de la première place et ne pas terminer du tout.

Notez que pour que les opérateurs d'ensemble fonctionnent, il doit y avoir le même nombre de colonnes du même type de données dans les deux requêtes.

Résultat :

id	first_name	last_name
14590785	Elijah Motonei	MANANGOI
14208194	Eliud	KIPCHOGE
14603138	Donavan	BRAZIER
14289014	Jemima Jelagat	SUMGONG
14536762	Noah	LYLES
14377814	Lelisa	DESISA
14209691	David	RUDISHA
14431159	Halimah	NAKAAYI

Exercice n° 20 : Trouver tous les athlètes qui n'ont pas pris le départ et qui ont gagné au moins une fois

Exercice : Affichez les athlètes qui n'ont pas pris le départ d'au moins une course et qui ont gagné au moins une course. Affichez trois colonnes : id, first_name, et last_name.

Solution :

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.is_dns IS TRUE

INTERSECT

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place = 1;

Explication : Cet exercice utilise un autre ensemble d'opérateurs. Cette fois, il s'agit de INTERSECT, qui renvoie toutes les valeurs identiques dans les deux requêtes.

La première requête de la solution répertorie les identifiants des athlètes ainsi que leurs noms et prénoms. Les tables athlete et final_result sont jointes sur les colonnes id et athlete_id des tables.

La condition dans WHERE recherche des lignes avec TRUE comme valeur dans la colonne is_dns, c'est-à-dire la colonne qui indique si l'athlète a commencé la course.

Comme dans l'exemple précédent, écrivez l'opérateur set puis la deuxième requête.

La deuxième requête est identique à la première, à l'exception de WHERE. La condition de filtrage permet de trouver les athlètes qui ont terminé en premier.

Ensemble, ces deux requêtes donnent les athlètes qui n'ont pas pris le départ de la course au moins une fois mais qui ont également terminé premiers au moins une fois.

Résultat :

id	first_name	last_name
14291986	Dafne	SCHIPPERS

De la pratique des requêtes SQL de base à la maîtrise du langage SQL

Il faut bien commencer quelque part. Ces 20 pratiques de base en matière de requêtes SQL sont idéales pour jeter les bases avant d'apprendre des concepts plus avancés.

Vous avez beaucoup appris en vous exerçant à écrire des requêtes utilisant WHERE, ORDER BY, JOINs, GROUP BY et HAVING. Je vous ai également montré plusieurs exemples de traitement des NULL, de calculs, d'écriture de sous-requêtes et d'utilisation d'opérateurs d'ensemble. Les requêtes de cet article sont extraites de notre site Pratique SQL de base : Des courses aux requêtes ! Vous y trouverez d'autres exercices SQL de base. Et si vous voulez plus de pratique, consultez notre La pratique du SQL qui contient 9 cours de pratique SQL pour débutants.

Ajoutez-y 20 exemples de requêtes SQL de base et 10 exercices d'entraînement SQL pour débutants, et vous serez équipé d'un niveau de compétence SQL intermédiaire.