19 exercices d'entraînement PostgreSQL avec des solutions détaillées

Vous avez probablement entendu le dicton "c'est en forgeant qu'on devient forgeron". Découvrez si cette phrase est vraie en essayant l'ensemble complet d'exercices d'entraînement PostgreSQL que vous trouverez dans cet article !

Pour maîtriser une langue, il est essentiel de la pratiquer régulièrement afin de ne pas perdre ses compétences. C'est aussi vrai pour la communication verbale que pour la programmation. Et la programmation SQL ne fait pas exception. Même le programmeur PostgreSQL le plus expérimenté a besoin de pratiquer PostgreSQL quotidiennement pour perfectionner (et maintenir) ses compétences.

Les débutants en PostgreSQL devraient développer leurs compétences par le biais d'exercices simples qui leur permettent d'intégrer progressivement de nouvelles connaissances. Dans cet article, vous trouverez une série d'exercices d'entraînement PostgreSQL qui vous aideront à apprendre rapidement comment interroger les bases de données PostgreSQL. Vous trouverez d'autres exercices d'entraînement SQL pour débutants dans cet article.

Pour travailler sur ces exercices, vous aurez besoin d'un serveur PostgreSQL où vous pourrez créer une base de données, y ajouter des tables et la remplir de données. Vous aurez également besoin d'une application cliente qui vous permettra d'exécuter des commandes SQL sur le serveur PostgreSQL. Avec ces éléments, vous pouvez créer les tables d'exemple qui vous permettront de faire tous les exercices pratiques de PostgreSQL tout au long de cet article. Si vous avez besoin d'aide, vous pouvez lire cet article sur la création de votre propre base de données d'entraînement SQL.

Il existe de nombreuses façons de s'entraîner au langage SQL en ligne. Celle que je recommande spécifiquement aux utilisateurs de PostgreSQL est notre site interactif Exercices Pratiques de SQL in PostgreSQL. Vous y trouverez 88 exercices qui vont des concepts simples aux concepts avancés. Les tables d'exemple utilisées dans cet article sont déjà créées et peuplées ; vous pouvez écrire et exécuter tous les exercices de requête que vous voulez et voir comment ils fonctionnent. Les exercices de cet article sont directement tirés de ce cours - si vous les trouvez utiles, vous pouvez être sûr que le reste du cours le sera également. Ne vous inquiétez pas : vous avez le droit de faire toutes sortes d'erreurs - il n'y a rien que vous puissiez casser et personne ne se mettra en colère contre vous si vous exécutez des requêtes bizarres.

Enfin, gardez à portée de main notre aide-mémoire gratuit sur PostgreSQL pendant que vous travaillez sur cet article. Ce guide rapide résume les principales commandes et fonctionnalités de PostgreSQL.

Maintenant, commençons à pratiquer PostgreSQL !

Ensemble de données PostgreLa pratique du SQL

La table principale que nous utiliserons pour les exercices PostgreSQL de cet article contient des informations sur les chats - leur nom, leur race, leur âge, etc. Le nom de la table est simplement cat, et sa structure est la suivante :

• id: Une valeur numérique qui identifie chaque chat représenté dans la table.
• name: Le nom du chat.
• breed
• coloration
• age
• Fav_toy: Le type de jouet préféré du chat.

Voici un exemple de données :

id	name	breed	coloration	age	fav_toy
1	Micky	Maine Coon	tortoiseshell	3	ball - red
2	Nine	Ragamuffin	cream	2	ball - green
3	Carmen	Persian	brown	2	toy mouse
4	Luna	Abyssinian	tortoiseshell	12	teaser
5	Bella	Siamese	red	15	teaser

Exercice 1 : Connaître les données

Exercice : Obtenez toutes les informations du tableau cat.

Solution :

SELECT *
FROM cat;

Explication : La commande SELECT * FROM cat demande à PostgreSQL de récupérer toutes les colonnes et toutes les lignes de la table. cat table. La clé pour obtenir toutes les colonnes est SELECT *. Le "*" fonctionne comme un joker : si vous ne connaissez pas les noms des colonnes, SELECT * les retournera toutes.

Avec la clause FROM cat vous indiquez à PostgreSQL que vous voulez obtenir des informations de la table cat . En ne posant aucune condition supplémentaire, vous obtiendrez toutes les lignes. Le point-virgule à la fin de la commande est simplement une indication que la commande se termine ici.

Exercice 2 : Sélectionner des colonnes spécifiques d'une table

Exercice : Sélectionner uniquement les colonnes name et age du tableau cat.

Solution :

SELECT name, age
FROM cat;

Explication : Dans l'exercice précédent, vous avez utilisé le joker "*" pour sélectionner toutes les colonnes d'un tableau. Dans cet exercice, vous voulez seulement voir le nom et l'âge de chaque chat. Au lieu d'utiliser le caractère générique "*", vous spécifiez simplement les noms des colonnes name et age après la commande SELECT. Séparez les colonnes par une virgule.

Exercice 3 : Collecte d'informations sur les chats siamois

Exercice : Obtenir toutes les informations de la table cat sur les chats siamois.

Solution :

SELECT *
FROM cat
WHERE breed = 'Siamese';

Explication : Pour appliquer un filtre aux lignes sélectionnées, utilisez la clause WHERE. Celle-ci vous permet de spécifier une condition à laquelle les lignes doivent satisfaire pour être incluses dans l'ensemble de résultats. Dans cet exercice, la condition WHERE demande à la base de données de ne renvoyer que des informations sur les chats siamois.

La condition spécifiée dans la clause WHERE est (en général) une condition logique qui peut être évaluée comme vraie ou fausse. Dans sa forme la plus simple, elle indique le nom de l'une des colonnes de la table (breed), puis un opérateur de comparaison (=) et une valeur à comparer ('Siamese'). Notez que toutes les chaînes littérales doivent être placées entre guillemets.

Exercice 4 : recherche des chats les plus jeunes

Exercice : Obtenez les lignes du tableau cat qui correspondent à des individus âgés de moins de 8 ans.

Solution :

SELECT *
FROM cat
WHERE age < 8;

Explication : Dans l'exercice précédent, nous avons vu comment filtrer les résultats d'une requête en comparant une colonne de texte (breed) à une valeur littérale ('Siamese'). Seuls les enregistrements dont la valeur dans cette colonne correspond à la chaîne littérale spécifiée ont été pris en compte.

Dans cet exercice, vous devez également spécifier une condition dans la clause WHERE. Cependant, vous appliquez maintenant la condition à une valeur numérique et vous utilisez l'opérateur de comparaison de non-égalité < (moins que).

Pour obtenir une liste de toutes les lignes de la table cat pour les chats de moins de 8 ans, vous devez utiliser la colonne âge dans la clause WHERE. Vous la combinez avec l'opérateur de comparaison < et la valeur numérique 8.

De la même manière, vous pouvez utiliser différents critères pour comparer des valeurs numériques. Pour voir les chats âgés de plus de 8 ans, par exemple, il suffit de remplacer la condition de la clause WHERE par age > 8.

Exercice 5 : Recherche de jeunes chats siamois

Exercice : Obtenir les lignes correspondant aux chats siamois âgés de moins de 8 ans.

Solution :

SELECT *
FROM cat
WHERE breed = 'Siamese'
AND age < 8;

Explication : Vous aurez souvent besoin d'obtenir des lignes qui répondent à plus d'une condition. Pour ce faire, toutes les conditions doivent être spécifiées dans la clause WHERE et jointes par la conjonction AND. À la suite de cette requête, PostgreSQL renverra un ensemble d'enregistrements pour les chats siamois âgés de moins de 8 ans.

En plus de la conjonction AND, il existe d'autres façons de combiner des conditions dans des déclarations logiques qui évaluent à vrai ou faux.

Exercice 6 : Utilisation de conditions alternatives (l'une ou l'autre)

Exercice : Obtenez une liste de chats persans ou siamois dans le tableau. cat tableau.

Solution :

SELECT id, name
FROM cat
WHERE breed = 'Siamese'
OR breed = 'Persian';

Explication : Les conditions combinées par l'opérateur logique OR retournent True si au moins une des expressions est True. Dans cet exercice, nous avons utilisé l'opérateur OR pour obtenir les données des chats siamois ou persans.

Exercice 7 : Combinaison de différentes conditions logiques

Exercice : Obtenir la liste des chats siamois ou persans du tableau cat qui ont moins de 5 ans ou plus de 10 ans.

Solution :

SELECT id, name
FROM cat
WHERE (breed = 'Siamese' OR breed = 'Persian')
AND (age < 5 OR age > 10);

Explication : Pour obtenir des données d'une table qui répond à une combinaison de conditions alternatives ou obligatoires, vous devez combiner les opérateurs logiques OR et AND. Nous le faisons avec des parenthèses, afin que PostgreSQL comprenne correctement la priorité des conditions.

Il faut faire attention lorsque les opérateurs OR et AND sont placés dans la même clause WHERE. PostgreSQL combinera les conditions logiques dans l'ordre où elles apparaissent , sauf si des parenthèses sont utilisées. Si des parenthèses sont utilisées (comme dans l'exemple ci-dessus), le résultat de ce qui est entre les parenthèses est évalué en premier; ensuite, ces résultats sont combinés avec l'opérateur qui les relie.

Dans cet exercice, la race du chat est d'abord évaluée. Si elle remplit l'une des deux conditions WHERE, la base de données évalue alors la série de conditions suivante pour voir si le chat remplit les conditions d'âge. Seuls les chats qui remplissent une condition de race et une condition d'âge sont inclus dans le résultat.

Exercice 8 : Liste de chats de différentes races

Exercice : Obtenir une liste de chats persans, siamois ou ragdolls sans utiliser l'opérateur OR.

Solution :

SELECT id, name
FROM cat
WHERE breed IN ('Persian', 'Siamese', 'Ragdoll');

Explication : Il est parfois nécessaire de filtrer les résultats d'une requête par un ensemble de valeurs. On peut y parvenir en spécifiant plusieurs conditions reliées par des opérateurs OR, comme ceci :

SELECT name
FROM cat
WHERE breed = 'Persian' OR breed = 'Siamese' OR breed = 'Ragdoll';

Mais pour simplifier une requête comportant de nombreuses énumérations, il est préférable d'utiliser l'opérateur logique IN, qui évalue l'appartenance d'une valeur à un ensemble. Dans ce cas, la valeur serait la colonne race et l'ensemble serait une liste de valeurs ('Persian', 'Siamese', 'Ragdoll').

L'opérateur IN facilite la lecture et la compréhension des requêtes complexes.

Exercice 9 : recherche de correspondances partielles

Exercice : Obtenir toutes les lignes de la table cat correspondant aux chats dont le jouet préféré est une balle de n'importe quelle couleur.

Solution :

SELECT *
FROM cat
WHERE fav_toy LIKE '%ball%';

Explication : Lorsque vous essayez de rechercher des correspondances dans des colonnes contenant des descriptions, vous ne savez souvent pas exactement comment les descriptions sont stockées. Vous devez parfois rechercher un mot particulier susceptible d'apparaître dans une description. Dans ce cas, l'opérateur de comparaison = n'est d'aucune utilité, car il ne renvoie que les lignes où la correspondance est exacte.

Lorsque vous avez besoin de trouver des correspondances partielles, PostgreSQL vous permet de rechercher des fragments de texte dans les colonnes varchar. L'astuce consiste à remplacer l'opérateur = par LIKE, en ajoutant des caractères génériques pour représenter les parties du texte que vous ne connaissez pas.

Dans la table cat la colonne fav_toy contient le mot "balle" ainsi que sa couleur - par exemple "balle rouge" ou "balle verte". Avec l'opérateur LIKE, vous pouvez demander à PostgreSQL de retourner les lignes où fav_toy contient le mot "balle".

Le symbole % placé de part et d'autre du mot "balle" agit comme un joker, indiquant qu'il peut y avoir n'importe quel texte autour de "balle". Les lignes obtenues sont celles où le mot "balle" apparaît dans le texte de la colonne fav_toy, quel que soit le reste du texte qu'il contient.

Exercice 10 : recherche de valeurs NULL dans certaines colonnes

Exercice : Obtenez toutes les lignes du tableau cat correspondant aux chats qui n'ont pas de jouet préféré.

Solution :

SELECT name
FROM cat
WHERE fav_toy IS NULL;

Explication : Dans certaines situations, il est nécessaire d'obtenir les lignes d'une table pour lesquelles il n'y a pas d'informations dans une certaine colonne. En d'autres termes, vous devez demander à la base de données de ne récupérer que les lignes pour lesquelles une colonne donnée a une valeur NULL. Vous ne pouvez pas utiliser les opérateurs de comparaison pour cela ; en SQL, toute comparaison impliquant des valeurs NULL produit toujours la valeur logique False.

La seule façon de demander à la base de données de récupérer de telles lignes est de spécifier que la valeur de la colonne IS NULL: fav_toy IS NULL.

N'oubliez pas que le mot NULL en SQL fait référence à une valeur inconnue. Ce n'est pas la même chose que la valeur 0 dans une colonne numérique ou qu'une chaîne de longueur nulle dans une colonne varchar. Toute opération numérique, de date ou de chaîne de caractères impliquant une valeur NULL aboutit également à une valeur NULL.

Exercice 11 : éviter les lignes sans données dans certaines colonnes

Exercice : Obtenez toutes les lignes de la table cat correspondant aux chats qui ont un jouet préféré.

Solution :

SELECT name
FROM cat
WHERE fav_toy IS NOT NULL;

Explication : Nous savons comment obtenir des lignes lorsqu'une colonne a une valeur NULL. Mais que se passe-t-il si vous avez besoin de l'inverse, c'est-à-dire d'obtenir toutes les lignes avec une valeur quelconque dans cette colonne ? La condition IS NOT NULL fait exactement le contraire de IS NULL: elle renvoie toutes les lignes qui contiennent des données (n'importe lesquelles) dans la colonne fav_toy.

Exercice 12 : Obtenir une liste de chats triés par âge

Exercice : sélectionnez le nom et l'âge dans le tableau cat en s'assurant que les résultats sont classés par âge.

Solution :

SELECT name, age
FROM cat
ORDER BY age;

Explication : Lors de l'exécution d'une requête SELECT dans une base de données SQL, il est impossible de prédire l'ordre dans lequel les résultats apparaîtront - à moins que vous ne spécifiiez un critère d'ordre. Si vous souhaitez obtenir les résultats d'une requête dans un ordre particulier, il est nécessaire de spécifier les colonnes qui détermineront l'ordre à l'aide d'une clause ORDER BY.

Exercice 13 : trier les chats par race et par âge

Exercice : Sélectionnez la race, le nom et l'âge de toutes les lignes du tableau catLa solution est d'obtenir les résultats triés d'abord par race et ensuite par âge.

Solution :

SELECT breed, name, age
FROM cat
ORDER BY breed, age;

Explication : Vous pouvez utiliser la clause ORDER BY pour indiquer une ou plusieurs colonnes comme critères de tri. La requête de cet exercice trie les résultats par race ; dans chaque race, les lignes sont triées par âge.

Exercice 14 : Utilisation de l'ordre croissant et décroissant

Exercice : Sélectionnez la race, le nom et l'âge de tous les chats, puis triez les résultats d'abord par race dans l'ordre alphabétique, puis par âge, du plus vieux au plus jeune.

Solution :

SELECT breed, name, age
FROM cat
ORDER BY breed ASC, age DESC;

Explication : La clause ORDER BY vous permet également de spécifier l'ordre croissant ou décroissant pour chacune des colonnes de tri. Le mot ASC indique un ordre ascendant (A à Z, 1 à 10) ; DESC indique un ordre descendant (Z à A, 10 à 1). Si l'ordre de tri n'est pas spécifié, l'ordre croissant est supposé. Pour les données alphanumériques, l'ordre croissant est identique à l'ordre alphabétique.

La requête de cet exercice trie les lignes par ordre alphabétique de race (en ordre croissant), puis par ordre décroissant d'âge.

Exercice 15 : regroupement et agrégation de données

Exercice : Sélectionnez les races de chats dans le tableau cat et le nombre de chats de chaque race.

Solution :

SELECT breed, COUNT(*)
FROM cat
GROUP BY breed;

Explication : La clause GROUP BY regroupe les lignes en fonction de la valeur d'une colonne (GROUP BY breed) ; une ligne est renvoyée pour chaque valeur de groupe (par exemple, le tableau cats aura une ligne pour les Siamois, une pour les Ragdolls, etc.)

Pour obtenir le nombre de chats de chaque race, nous utilisons la fonction d'agrégation COUNT(*), qui renvoie le nombre de lignes de chaque groupe.

Lorsque vous utilisez la clause GROUP BY, il est important de noter que les colonnes de la liste SELECT qui ne sont pas utilisées dans une fonction agrégée doivent également figurer dans la liste des colonnes de la clause GROUP BY.

Si vous pensez avoir besoin de plus de pratique pour bien comprendre le regroupement en SQL, essayez ces 10 exercices d'entraînement GROUP BY.

Exercice 16 : Obtenir l'âge du plus jeune et du plus vieux chat de chaque race

Exercice : Sélectionnez une liste de races de chats dans le tableau, ainsi que les âges maximum et minimum. cat ainsi que les âges maximum et minimum pour chaque race.

Solution :

SELECT breed, MIN(age), MAX(age)
FROM cat
GROUP BY breed;

Explication : Le regroupement permet d'obtenir des données résumées. En plus d'obtenir le nombre d'éléments dans chaque groupe (comme nous l'avons fait dans l'exercice précédent), nous pouvons utiliser des fonctions pour calculer les totaux, les moyennes et les valeurs maximales/minimales pour chaque groupe.

Dans cet exercice, les fonctions MIN() et MAX() sont utilisées pour obtenir les âges maximum et minimum pour chaque race.

Exercice 17 : regroupement par colonnes multiples

Exercice : Sélectionnez une liste de races de chats et de couleurs différentes dans le tableau, en comptant le nombre de chats de chaque race. cat et comptez le nombre de chats de chaque combinaison de race et de coloration.

Solution :

SELECT 
  breed, 
  coloration, 
  COUNT(*)
FROM cat
GROUP BY breed, coloration;

Explication : Il se peut que vous souhaitiez regrouper les données en fonction de plusieurs colonnes, en créant une hiérarchie de plusieurs critères de regroupement imbriqués. Pour ce faire, vous pouvez détailler tous les champs de regroupement dans la clause GROUP BY.

L'ordre dans lequel ces champs sont écrits déterminera l'aspect de la hiérarchie de regroupement. Dans cet exercice, les données sont regroupées par race, puis par couleur au sein de chaque race. Et la fonction COUNT(*) montre combien de chats il y a dans le tableau pour chaque combinaison de race et de coloration.

Exercice 18 : définition de conditions sur des données agrégées

Exercice : Obtenez une liste des races qui sont représentées par plus d'un enregistrement dans le tableau. cat tableau.

Solution :

SELECT breed, COUNT(*)
FROM cat
GROUP BY breed
HAVING COUNT(*) > 1;

Explication : Nous avons vu que WHERE vous permet de définir des conditions sur les lignes renvoyées par la requête. Mais il se peut que vous deviez filtrer les lignes groupées en définissant des conditions sur les résultats des fonctions d'agrégation.

Pour ce faire, définissez la condition selon laquelle le résultat de la fonction COUNT(*) doit être supérieur à 1. Mais vous ne pouvez pas spécifier cette condition dans la clause WHERE; elle est calculée sur des lignes individuelles avant que GROUP BY ne soit traitée par la base de données. Vous devez donc utiliser la clause HAVING, comme nous l'avons fait dans cet exercice.

Exercice 19 : Obtenir des données connexes à partir de différentes tables

Exercice : Sélectionnez l'origine de la race de chaque chat dans le tableau cat en l'obtenant dans la table connexe breed_origin dans la table connexe. Voici un aperçu des données du tableau breed_origin tableau :

breed	origin
Ragdoll	California
Persian	Unknown
Ragamuffin	California
Maine Coon	Maine
Siamese	Thailand
Abyssinian	Southeast Asia

Solution :

SELECT cat.name, cat.breed, breed_origin.origin
FROM cat JOIN breed_origin
ON cat.breed = breed_origin.breed;

Explication : Si vous avez besoin d'une requête qui renvoie l'origine de la race pour chacun des chats, vous devez joindre la table cat avec la table breed_origin table. Pour ce faire, vous devez spécifier les deux tables dans la clause FROM - séparées par le mot JOIN.

La condition de jonction est spécifiée après les noms des tables et précédée du mot ON. Il est nécessaire de définir cette condition pour que la base de données sache comment faire correspondre correctement les lignes. Dans ce cas, la condition est que la colonne "race" de cat soit égale à la colonne "race" de breed_origin.

Étant donné qu'il y a plus d'une table dans la clause FROM, il est préférable de placer le nom de la table avant le nom de la colonne, en les séparant par un point (par exemple cat.breed). Cela évite toute ambiguïté, tant pour la base de données que pour le lecteur. Il n'est pas obligatoire de procéder ainsi pour toutes les colonnes, mais c'est essentiel pour les colonnes qui partagent le même nom dans les deux tables.

Dans l'exemple ci-dessus, la colonne race figure à la fois dans les tables cat et breed_originIl est donc obligatoire de préfixer le nom de la table chaque fois que vous faites référence à l'une ou l'autre colonne.

Une fois que vous aurez compris comment joindre des tables en SQL, vous pourrez renforcer vos connaissances en réalisant ces 12 exercices d'entraînement JOIN.

Prêt pour les exercices avancés de PostgreLa pratique du SQL?

Qu'avez-vous pensé de cet ensemble d'exercices d'entraînement PostgreSQL pour débutants ? Rappelez-vous que vous pouvez continuer à vous entraîner avec les 88 exercices du cours interactif Exercices Pratiques de SQL in PostgreSQL, qui n'est qu'un des 65 cours SQL pratiques pour les débutants et les experts que vous pouvez trouver dans LearnSQL.fr.

Si vous avez trouvé les exercices PostgreSQL suggérés très faciles, vous êtes peut-être prêt à commencer l'apprentissage avancé de PostgreSQL. Gardez à l'esprit que tous les efforts que vous consacrerez à l'apprentissage de PostgreSQL seront bien investis, car ce dialecte du langage SQL se développe rapidement et vous ouvrira la porte à de nombreuses opportunités professionnelles intéressantes. Continuez à pratiquer et à apprendre, et vous maintiendrez vos compétences SQL à jour.