SQL et PostgreSQL : de la première requête à l'optimisation

Les étapes

1. Étape 1
   Débutant

   SQL pour débutants : explorer une base PostgreSQL

   Mama Ngo tient sa boutique à Nkoldongo, Yaoundé, depuis vingt ans. Provisions, savon, sardines, cubes Maggi. Et un cahier, où tout était écrit. Au printemps, son neveu lui a installé une caisse sur un vieux portable ; depuis, tout est saisi et plus personne ne sait rien. « Le cahier, je l'ouvrais et je voyais. Là, je vois un écran noir. » Cet atelier est le premier contact avec une base de données, et il est conçu pour quelqu'un qui n'en a jamais ouvert une. Vous n'écrirez pas une ligne de SQL et vous n'ouvrirez aucun terminal : vous travaillez dans une interface web, sur un lien, et vous regardez. Apprendre en même temps la ligne de commande et la notion de table, c'est empiler deux difficultés qui n'ont rien à voir. Le guide ne se lit pas, il se pratique. À six endroits il vous demande de répondre avant de continuer, et la réponse commentée attend sous un ruban que vous ne tirez qu'après avoir décidé. Chercher une réponse, même en se trompant, fixe une notion bien mieux que la lire. En une heure et quart, vous saurez ce qu'est une table, une colonne, une ligne et un type ; pourquoi un prix stocké en texte casse les calculs six mois plus tard ; et pourquoi une case vide et un zéro ne veulent pas dire la même chose, la confusion qui fera disparaître les deux tiers des ventes de quelqu'un d'autre, plus tard dans le parcours. Le SQL est la compétence technique la plus demandée aux postes d'entrée, et la plus indépendante des choix techniques de l'employeur : elle sert autant dans une PME sur ERP que dans le rapprochement d'un compte mobile money. C'est par là qu'on commence.

   • 4,0 sur 5, (1 avis)
   • Débutant
   • 1 h 15 min
   • 250 crédits
   • 1er atelier offert
2. Étape 2
   Débutant

   SQL : filtrer et trier avec WHERE et ORDER BY

   « Avant, j'ouvrais le cahier le dimanche et je voyais ce qui allait manquer. Là je ne vois plus rien venir. Qu'est-ce qui est bientôt fini sur les étagères ? » Vous savez ce qu'est une table. Cet atelier vous apprend à lui poser une question : votre première requête SQL, dans la même interface web, toujours sans terminal. SELECT choisit les colonnes, WHERE choisit les lignes, ORDER BY range, LIMIT coupe. Chaque chapitre se termine par un exercice que vous écrivez vous-même, avec la réponse commentée sous un ruban. En une heure et demie vous écrivez la requête qui rend à Mama Ngo sa page du dimanche. Et vous rencontrez les deux erreurs qui ne lèvent aucun message. La première : chercher une case vide avec « = NULL » renvoie zéro ligne alors qu'il y en a trois, et rien ne vous avertit. La seconde : AND passe avant OR, silencieusement ; votre requête vous répond, et elle a répondu à une autre question que la vôtre. Vous le verrez sur un résultat qui change sous vos yeux selon l'endroit où vous placez deux parenthèses. L'atelier se termine par un tableau de dépannage construit sur les vrais messages de PostgreSQL, y compris le plus dangereux de tous, celui qui n'existe pas : un résultat vide qui a l'air d'une réponse.

   • 5,0 sur 5, (1 avis)
   • Débutant
   • 1 h 30 min
   • 250 crédits
   • 1er atelier offert
3. Étape 3
   Débutant

   SQL : compter et regrouper avec COUNT et GROUP BY

   « Combien j'ai vendu ce mois ? Et surtout : qu'est-ce qui rapporte vraiment ? Parce que le riz, j'en vends beaucoup, mais le sac est cher aussi. » Jusqu'ici vous réduisiez et rangiez des lignes. Ici elles disparaissent : trois cent vingt ventes se replient en une poignée de chiffres. C'est le deuxième mur du SQL après la jointure, et il a son atelier à lui seul plutôt qu'un sous-chapitre. COUNT, SUM, DISTINCT, GROUP BY, HAVING, et le message d'erreur le plus fréquent du SQL, celui qui reproche une colonne absente du GROUP BY. Vous le déclencherez volontairement, et vous comprendrez pourquoi PostgreSQL refuse plutôt que d'inventer une réponse là où d'autres bases choisissaient une ligne au hasard sans rien dire. Vous apprendrez aussi à filtrer les groupes eux-mêmes, ce que WHERE ne sait pas faire, et vous verrez pourquoi : au moment où WHERE travaille, les groupes n'existent pas encore. L'atelier se termine par un challenge sans solution donnée : trouver le produit qui a rapporté le plus d'argent en août. La réponse est dans le guide, repliée sous un ruban que vous ne tirez qu'après avoir cherché. Et une surprise vous attend dans les chiffres : une catégorie arrive deuxième alors qu'elle ne devrait pas. Vous en connaissez déjà la cause. Une donnée fausse ne se voit pas dans la donnée, elle se voit dans le résultat qui étonne, et un résultat qui étonne se vérifie avant d'être annoncé. C'est le réflexe professionnel que cet atelier installe.

   • 5,0 sur 5, (1 avis)
   • Débutant
   • 1 h 30 min
   • 250 crédits
   • 1er atelier offert
4. Étape 4
   Débutant

   SQL : relier deux tables avec JOIN

   « Le cahier disait : trois savons à Mme Essomba. L'écran me dit : produit 5, client 1. Je dois tenir une boutique, pas déchiffrer des numéros. » C'est le mur du SQL, et il a un atelier pour lui seul. Pas de fonctions, pas d'alias décoratifs : relier deux tables, et comprendre ce que l'opération garde et ce qu'elle jette. Vous commencerez par répondre vous-même à la question que l'atelier 1 avait laissée ouverte : pourquoi la caisse enregistre un numéro plutôt qu'un nom. La réponse tient en une phrase qui fait tenir toutes les bases de données du monde : une information n'est écrite qu'à un seul endroit. Puis vous verrez 218 ventes disparaître d'un seul coup. Une jointure stricte sur les clients fait tomber la boutique de 320 ventes à 102, sans le moindre message d'erreur, parce que les deux tiers des gens paient comptant sans laisser leur nom. C'est la différence entre JOIN et LEFT JOIN, et elle ne se choisit jamais par habitude : elle se choisit par la question qu'on pose. Un chapitre entier est consacré au réflexe qui vous évitera un jour d'annoncer un chiffre faux : compter les lignes après chaque jointure et comparer au total de départ. Une jointure ne perd pas seulement des lignes, elle peut aussi en inventer, et aucun message ne vous préviendra. Vous apprendrez enfin l'anti-jointure, la figure qui répond à toutes les questions en « qui n'a jamais » : clients sans commande, factures sans paiement, produits que personne ne prend au cahier. L'atelier se termine par un challenge, les cinq plus gros débiteurs de Mama Ngo, dont la réponse est repliée sous un ruban que vous ne tirez qu'après avoir cherché.

   • 5,0 sur 5, (1 avis)
   • Débutant
   • 1 h 30 min
   • 400 crédits
   • 1er atelier offert
5. Étape 5
   Débutant

   SQL : INSERT, UPDATE, DELETE et premiers pas avec psql

   « Une dame a demandé le savon liquide. La caisse a dit douze mille cinq cents. Elle est partie. Ludovic a tapé un zéro de trop. Corrige, mais ne casse rien, c'est toute ma boutique qui est là-dedans. » Jusqu'ici, une requête fausse rendait un résultat faux. À partir de cet atelier, une requête fausse détruit. C'est le premier atelier du parcours où l'interface autorise l'écriture. Vous ouvrirez d'abord votre premier terminal, et ce n'est pas un ajout décoratif : ce qui suit exige une session, c'est-à-dire une conversation continue avec la base. Une interface web ne vous montre pas la session ; psql vous la montre, et l'invite elle-même change sous vos yeux pour vous dire à chaque instant ce que vous risquez. Vous déclencherez ensuite volontairement la catastrophe : un UPDATE sans clause WHERE, qui met les vingt-cinq produits de la boutique au même prix en une seconde. Puis vous la défaites d'un mot. C'est toute la leçon : BEGIN avant, vérification au milieu, COMMIT seulement quand on a compris ce qu'on touche. Vous découvrirez aussi que le compteur de lignes ment par omission. « DELETE 1 » prouve que vous avez supprimé une seule ligne, jamais que c'était la bonne ; un faux doublon vous le montrera. Et « UPDATE 0 » prouve que votre correction n'a jamais eu lieu, ce dont rien d'autre ne vous avertira. L'atelier ajoute enfin l'écriture d'une ligne neuve avec INSERT, la clé primaire qui refuse physiquement un doublon, et la clé étrangère qui vous empêche de rendre une vente orpheline. Une base bien construite n'est pas un obstacle à contourner : c'est un collègue qui relit votre travail et vous arrête quand vous vous trompez. C'est aussi le pont vers le parcours sauvegardes, dont l'incident fondateur est exactement l'UPDATE sans WHERE que vous venez de déclencher en sécurité.

   • Aucun avis
   • Débutant
   • 1 h 30 min
   • 400 crédits
   • 1er atelier offert
6. Étape 6
   Général

   PostgreSQL : contraintes, types et droits d'accès

   Mama Ngo est rentrée du village. Pendant son absence, sa nièce Nadia a tenu la caisse une semaine entière. « Il y a des ventes à zéro, un moyen de paiement que personne ne connaît, et le riz en double. La machine ne pouvait pas refuser toute seule ? » À l'atelier précédent, vous avez appris à écrire prudemment. Mais la prudence ne protège que ce que vous tapez vous-même. La seule protection qui vaut pour tout le monde, c'est la base qui refuse. Vous commencez par retrouver les dégâts avec les requêtes que vous connaissez, puis vous tentez de poser la règle qui les aurait empêchés, et la base refuse de la créer : des lignes existantes la violent déjà. Ce refus devient votre plan de nettoyage. Vous nettoyez sans rien perdre, après avoir demandé à Nadia ce qui s'est vraiment passé, car une donnée étrange n'est pas forcément fausse. Puis vous posez les règles que la base fera respecter à tout le monde : CHECK, UNIQUE, DEFAULT. Vous fusionnez le riz en double malgré la clé étrangère qui s'y oppose, vous choisissez le type de chaque information d'un bon de livraison, et vous créez la table des livraisons, avec sa numérotation automatique et son lien vers les produits. Enfin, vous découvrez qu'une table neuve est fermée à tous, et vous donnez le droit de la lire, et seulement de la lire. Tout se passe dans le terminal, avec le compte du propriétaire de la base. À la fin, les règles posées et la table créée sont vérifiées dans la base, et le résultat compte pour votre classement.

   • Aucun avis
   • Intermédiaire
   • 2 h
   • 750 crédits
   • 1er atelier offert
7. Étape 7
   Général

   SQL avancé : sous-requêtes, WITH (CTE) et fonctions de fenêtrage

   La boutique connaît enfin ce qu'elle paie au grossiste. Mama Ngo a deux questions : « Est-ce que septembre a été meilleur qu'août ? Et sur quoi je gagne vraiment, une fois payé le grossiste ? » Aucune des deux ne tient dans une requête simple. La première compare un mois au précédent, la seconde croise des tables regroupées différemment. C'est le nouveau mur du SQL : la question qui demande plusieurs étapes. Vous apprenez à l'écrire comme on la pense, une étape nommée après l'autre, chacune vérifiable seule. En chemin, trois pièges qui ne lèvent aucun message. Une division entre entiers qui affiche 0 % au lieu de 58 %. Un NOT IN qui rend zéro ligne parce qu'un seul vide traîne dans la liste. Et une jointure directe entre deux tables qui multiplie les montants sans prévenir. Chacun est provoqué sur des chiffres que vous connaissez, pour que vous voyiez qu'ils sont faux au lieu de les croire. Vous regroupez par mois et par jour de la semaine, vous calculez la marge de chaque produit avec WITH, vous classez avec CASE, et vous découvrez les fonctions de fenêtre : lag pour comparer chaque mois au précédent, rank pour classer sans regrouper. Le challenge : le produit le plus rentable de chaque rayon. Et une surprise dans les chiffres : ce qui remplit la caisse n'est pas ce qui fait vivre la boutique.

   • Aucun avis
   • Intermédiaire
   • 2 h
   • 750 crédits
   • 1er atelier offert
8. Étape 8
   Général

   PostgreSQL : accélérer une requête avec EXPLAIN et les index

   La boutique a rejoint la coopérative des commerçants de Nkoldongo : quarante boutiques, une seule base, un million de ventes. Le président appelle Mama Ngo : « Quand un client fidèle passe, l'écran met un temps fou à sortir son historique. Et le samedi, c'est pire. » Aucune requête n'est fausse : elles sont lentes. Et devant une requête lente, la tentation est d'essayer des choses au hasard. Cet atelier fait l'inverse : avant d'accélérer une requête, on lit comment PostgreSQL l'exécute. Vous mesurez, puis vous lisez le plan avec EXPLAIN et EXPLAIN ANALYZE : un million de lignes lues pour en rendre quatorze, par trois processus en parallèle. Vous créez l'index qui change le plan, et vous le prouvez par le plan lui-même, en apprenant à y chercher un seul mot. Puis les trois cas où un index existe et ne sert à rien : une fonction sur la colonne, une condition trop peu sélective, une recherche au milieu d'un texte. Vous mesurez aussi ce qu'un index coûte à chaque écriture, et vous supprimez celui qui ne sert pas. Enfin, la recherche plein texte : chercher des mots plutôt que des lettres, au pluriel comme au singulier, en majuscules ou non, avec l'index qui la fait passer de plus d'une seconde à quelques dizaines de millisecondes. Le challenge : le tableau de bord du samedi, et l'index sur deux colonnes qui le rend instantané.

   • Aucun avis
   • Intermédiaire
   • 2 h 15 min
   • 1 000 crédits
   • 1er atelier offert
9. Étape 9
   Général

   PostgreSQL : transactions, verrous et interblocages

   Mama Ngo a acheté une seconde caisse. Samedi dernier, il ne restait qu'une bouteille d'huile végétale : « Une dame l'a payée chez Nadia pendant qu'un monsieur la payait chez moi. Deux tickets, une seule bouteille. La machine n'a pas vu qu'on vendait la même chose en même temps ? » Tant qu'il n'y avait qu'une caisse, les ventes passaient l'une après l'autre. Avec deux, elles se croisent. Cet atelier ouvre deux terminaux, un par caisse, et rejoue le samedi dans l'ordre exact où les deux caisses agissent. Vous voyez d'abord ce qu'une session voit du travail d'une autre, avant et après COMMIT, puis vous reproduisez la vente fantôme : aucune erreur, deux ventes validées, une bouteille. Vous rencontrez votre premier verrou, un terminal qui ne rend pas la main parce que l'autre caisse tient la ligne. Puis vous écrivez la vente qui ne peut pas survendre, en laissant la base compter, et vous voyez PostgreSQL relire la ligne après l'attente. Vous réservez une ligne avant de décider avec SELECT FOR UPDATE, vous travaillez dans un cliché figé en REPEATABLE READ jusqu'à l'erreur de sérialisation, et vous provoquez un interblocage que la base dénoue en sacrifiant l'une des deux caisses. Et une dernière scène : la transaction oubliée qui fige tout le monde. La retrouver et la couper, c'est le métier d'administrateur, et c'est le défi qui clôt le parcours.

   • Aucun avis
   • Intermédiaire
   • 2 h 15 min
   • 1 000 crédits
   • 1er atelier offert
10. Étape 10
    Général

    Défi PostgreSQL : débloquer une session qui fige la caisse

    Samedi, 10 h. La boutique est pleine, et la caisse de Nadia tourne dans le vide. Elle ne plante pas, elle n'affiche rien : elle attend. Ludovic ne répond pas au téléphone. Mama Ngo attend avec une cliente, un paquet de sardines à la main. Ce défi n'a pas de guide pas à pas. Il a une mission, des règles, et la documentation officielle de PostgreSQL. Vous êtes pour la première fois de l'autre côté du serveur : superutilisateur, avec la vue sur toutes les sessions ouvertes, ce que chacune fait, et ce que chacune attend. À vous de remonter de la caisse bloquée jusqu'à la session qui la bloque, de comprendre ce qu'elle tient et depuis quand, puis de la couper, elle seule, sans redémarrer le serveur ni arrêter la caisse. Il faudra aussi que cela ne se reproduise pas : régler le serveur pour qu'une transaction oubliée ne puisse plus bloquer la boutique plus de trente secondes, et laisser un mot à Ludovic pour qu'il comprenne ce qui s'est passé. C'est exactement le travail d'un administrateur de bases de données : observer, comprendre, intervenir sans interrompre le service, et s'appuyer sur la documentation. La suite naturelle de ce parcours.

    • Aucun avis
    • Intermédiaire
    • 1 h 30 min
    • 1 000 crédits
    • 1er atelier offert