15th Sep 2023 26 minutes de lecture Pratique avancée de SQL : 10 exercices de pratique de SQL avec solutions Martyna Sławińska sql pratique sql La maîtrise du langage SQL étant de plus en plus recherchée par les professionnels des données et les développeurs, on ne saurait trop insister sur l'importance de la pratique. Poursuivez votre lecture pour plonger dans le monde du langage SQL avancé et vous lancer dans des exercices pratiques pour améliorer vos compétences. Cet article vous propose une collection de dix exercices pratiques SQL difficiles, spécialement conçus pour ceux qui cherchent à améliorer leurs compétences en SQL avancé. Les exercices couvrent une sélection de concepts SQL et vous aideront à rafraîchir vos connaissances en SQL avancé. Chaque exercice est accompagné d'une solution détaillée, vous permettant de tester vos connaissances et d'acquérir une compréhension plus profonde des concepts SQL complexes. Les exercices proviennent de nos cours de pratique SQL avancée. Si vous voulez voir plus d'exercices comme celui-ci, consultez ces cours : Fonctions de fenêtrage Practice Set 2021 Mensuel Exercices Pratiques de SQLs - Avancé 2022 Mensuel Exercices Pratiques de SQLs - Avancé Commençons. S'entraîner pour maîtriser le langage SQL La pratique fait partie intégrante de la maîtrise du langage SQL ; on ne saurait trop insister sur son importance. Le chemin vers la maîtrise du langage SQL avancé exige de l'implication, de la persévérance et un engagement fort dans la pratique continue. En pratiquant régulièrement le langage SQL avancé, les individus peuvent affiner leurs compétences, élargir leurs connaissances et développer une compréhension approfondie des subtilités de la gestion et de la manipulation des données. SQL avancé Les exercices servent d'outils inestimables, incitant les apprenants à appliquer leurs connaissances théoriques dans des scénarios pratiques et renforçant leur compréhension de concepts complexes. Avec chaque session de pratique SQL dédiée, vous pouvez découvrir des techniques efficaces et gagner la confiance nécessaire pour relever les défis du monde réel en matière de données. Passons en revue les exercices et leurs solutions. SQL avancé Exercices pratiques Nous allons présenter plusieurs exercices SQL avancés qui couvrent les fonctions de fenêtre, les JOIN, les GROUP BY, les expressions de table commune (CTE), et bien plus encore. Section 1 : SQL avancé Exercices sur les JOIN Dans les exercices SQL avancés suivants, nous utiliserons une base de données de vêtements de sport qui stocke des informations sur les vêtements, les catégories de vêtements, les couleurs, les clients et les commandes. Elle contient cinq tables : color, customer, category, clothing, et clothing_order. Examinons les données de cette base. La table color contient les colonnes suivantes : idstocke l'identifiant unique de chaque couleur. name stocke le nom de la couleur. extra_fee stocke les frais supplémentaires (le cas échéant) ajoutés pour les vêtements commandés dans cette couleur. Dans la table customer, vous trouverez les colonnes suivantes : id stocke les identifiants des clients. first_name stocke le prénom du client. last_name stocke le nom de famille du client. favorite_color_idstocke l'ID de la couleur préférée du client (référence à la table des couleurs). La table category contient les colonnes suivantes id stocke l'identifiant unique de chaque catégorie. name stocke le nom de la catégorie. parent_id stocke l'ID de la catégorie principale pour cette catégorie (s'il s'agit d'une sous-catégorie). Si cette valeur est NULL, cela signifie que cette catégorie est une catégorie principale. Remarque : les valeurs sont liées à celles de la colonne id de ce tableau. La table clothing stocke des données dans les colonnes suivantes : id stocke l'identifiant unique de chaque élément. name stocke le nom de cet article. size stocke la taille de ce vêtement : S, M, L, XL, 2XL ou 3XL. price stocke le prix de l'article. color_id enregistre le site color de l'article (référence au tableau des couleurs). category_id stocke la catégorie de l'article (renvoie au tableau des catégories). La table clothing_order contient les colonnes suivantes id stocke l'identifiant unique de la commande. customer_id stocke l'identifiant du client qui a commandé les vêtements (renvoie à la table customer ). clothing_id enregistre l'ID de l'article commandé (renvoie à la table clothing ). items stocke le nombre de vêtements commandés par le client. order_date stocke la date de la commande. Faisons quelques exercices SQL avancés qui se concentrent sur JOINs. Exercice 1 : Liste de tous les articles de vêtements Exercice : Afficher le nom des vêtements (nommer la colonne clothes), leur couleur (nommer la colonne color), ainsi que le nom et le prénom du ou des clients qui ont acheté ces vêtements dans leur couleur préférée. Triez les lignes en fonction de la couleur, par ordre croissant. Solution : SELECT cl.name AS clothes, col.name AS color, cus.last_name, cus.first_name FROM clothing_order co JOIN clothing cl ON cl.id = co.clothing_id JOIN color col ON col.id = cl.color_id JOIN customer c ON cus.id = co.customer_id WHERE cus.favorite_color_id = cl.color_id ORDER BY col.name; Explication de la solution : Nous voulons afficher les valeurs des colonnes de trois tables différentes (clothing, color, et customer), y compris des informations sur le client qui a commandé un certain article (à partir de la table clothing_order ). Nous devons donc joindre ces quatre tables sur leurs colonnes communes. Tout d'abord, nous effectuons une sélection dans la table clothing_order (alias co) et la joignons à la table clothing (alias cl). Nous joignons les tables en utilisant la colonne de clé primaire de la table clothing (id) et la colonne de clé étrangère de la table clothing_order (clothing_id) ; cette colonne de clé étrangère relie les tables clothing et clothing_order. Ensuite, nous joignons la table color (alias col) à la table clothing (alias cl). Nous utilisons ici la colonne de clé primaire de la table color (id) et la colonne de clé étrangère de la table clothing (color_id). Enfin, nous joignons la table customer (alias cus) à la table clothing_order (alias co). La clé étrangère de la table clothing_order (customer_id) est liée à la clé primaire de la table customer (id). La clause ON stocke la condition de l'instruction JOIN. Par exemple, un article de la table clothing dont la valeur id est 23 est associé à une commande de la table clothing_order dont la valeur clothing_id est égale à 23. Suivez cet article pour voir d'autres exemples de jonction de trois tables (ou plus). Et voici comment joindre à gauche plusieurs tables. Exercice 2 : Obtenir tous les clients qui n'achètent pas Exercice : Sélectionner le nom et le prénom des clients ainsi que le nom de leur couleur préférée pour les clients qui n'ont pas acheté. Solution : SELECT cus.last_name, cus.first_name, col.name FROM customer cus JOIN color col ON col.id = cus.favorite_color_id LEFT JOIN clothing_order o ON o.customer_id = cus.id WHERE o.customer_id IS NULL; Explication de la solution : Ici, nous devons afficher le nom et le prénom des clients à partir de la table customer et le nom de leur couleur préférée color à partir de la table des couleurs. Nous devons le faire uniquement pour les clients qui n'ont pas encore passé de commande ; par conséquent, nous avons besoin des informations de la table clothing_order. L'étape suivante consiste donc à relier ces trois tables. Tout d'abord, nous joignons la table customer (alias cus) à la table color (alias col). Pour ce faire, nous utilisons la condition suivante : la colonne de clé primaire de la table color (id) doit être égale à la colonne de clé étrangère de la table customer (favorite_color_id). Cela nous permet de sélectionner le nom de la couleur préférée au lieu de son ID. Voici comment s'assurer que nous ne listons que les clients qui n'ont pas encore passé de commande : Nous LEFT JOIN la table clothing_order (alias o) avec la table customer (alias cus) pour nous assurer que toutes les lignes de la table customer (même celles qui n'ont pas de correspondance) sont listées. Dans la clause WHERE, nous définissons une condition pour n'afficher que les lignes dont la colonne customer_id de la table clothing_order est égale à NULL (ce qui signifie que seuls les clients dont les identifiants ne figurent pas dans la table clothing_order seront renvoyés). Il existe différents types de JOINs, notamment INNER JOIN, LEFT JOIN, RIGHT JOIN et FULL JOIN. Vous pouvez en apprendre davantage en suivant les articles liés. Exercice 3 : Sélectionner toutes les catégories principales et leurs sous-catégories Exercice : Sélectionnez le nom des catégories principales (qui ont un NULL dans la colonne parent_id) et le nom de leur sous-catégorie directe (si elle existe). Nommez la catégorie de la première colonne et la sous-catégorie de la deuxième colonne. La solution : SELECT c1.name AS category, c2.name AS subcategory FROM category c1 JOIN category c2 ON c2.parent_id = c1.id WHERE c1.parent_id IS NULL; Explication de la solution : Chaque catégorie répertoriée dans la table category possède son propre identifiant (stocké dans la colonne id ) ; certaines possèdent également l'identifiant de leur catégorie mère (stocké dans la colonne parent_id ). Nous pouvons donc relier la table category à elle-même pour répertorier les catégories principales et leurs sous-catégories. Le type de JOIN où l'on relie une table à elle-même est appelé familièrement " jointure automatique". Lorsque vous reliez une table à elle-même, vous devez donner des noms d'alias différents à chaque copie de la table. Ici, nous avons une table category appelée c1 et une autre table category appelée c2. Nous sélectionnons la table name à partir de la table category (alias c1) et nous nous assurons que nous n'énumérons que les catégories principales en faisant en sorte que la colonne parent_id soit égale à NULL dans la clause WHERE. Ensuite, nous joignons la table category (alias c1) à la table category (alias c2). Cette dernière fournit des sous-catégories pour les catégories principales. Par conséquent, dans la clause ON, nous définissons que la colonne parent_id de c2 doit être égale à la colonne id de c1. Lisez cet article pour en savoir plus sur les jointures automatiques. Les exercices de cette section sont tirés de notre cours 2021 Monthly Exercices Pratiques de SQL s - Advanced. Chaque mois, nous publions un nouveau cours de pratique SQL dans notre piste mensuelle La pratique du SQL; chaque mois impair, le cours est d'un niveau avancé. Les cours de pratique SQL avancés de 2021 ont été rassemblés dans notre cours 2021 Monthly Exercices Pratiques de SQL s - Advanced. Consultez-le pour trouver plus d'exercices JOIN et d'autres défis SQL avancés. Section 2 : Exercices avancés GROUP BY Dans les exercices SQL avancés suivants, nous utiliserons une base de données de club sportif qui stocke des informations sur les coureurs et les événements de course à pied. Elle contient trois tables : runner, event, et runner_event. Examinons les données de cette base. La table runner contient les colonnes suivantes id stocke l'identifiant unique du coureur. name stocke le nom du coureur. main_distance stocke la distance (en mètres) parcourue par le coureur lors des épreuves. age stocke l'âge du coureur. is_female indique si le coureur est un homme ou une femme. Le tableau event contient les colonnes suivantes id stocke l'identifiant unique de l'événement. name enregistre le nom de l'événement (par exemple, le marathon de Londres, les courses de Varsovie ou la course du Nouvel An). start_date La date de l'événement est enregistrée. city stocke la ville où se déroule l'événement. Le tableau runner_event contient les colonnes suivantes runner_id stocke l'ID du coureur. event_id stocke l'ID de l'événement. Nous allons maintenant faire quelques exercices SQL avancés qui se concentrent sur GROUP BY. Exercice 4 : Organiser les coureurs en groupes Exercice: Sélectionnez la distance principale et le nombre de coureurs qui ont couru la distance donnée (runners_number). N'affichez que les lignes où le nombre de coureurs est supérieur à 3. Solution: SELECT main_distance, COUNT(*) AS runners_number FROM runner GROUP BY main_distance HAVING COUNT(*) > 3; Explication de la solution: Nous voulons obtenir le nombre de coureurs pour chaque distance parcourue. Pour ce faire, nous devons regrouper tous les coureurs par distance et utiliser la fonction d'agrégation COUNT() pour calculer le nombre de coureurs dans chaque groupe de distance. Nous sélectionnons la colonne main_distance et GROUP BY cette colonne. Lorsque nous utilisons la fonction d'agrégation COUNT(), elle nous donne le nombre de coureurs qui correspondent à chaque valeur main_distance. La clause GROUP BY est utilisée pour regrouper les lignes d'une table en fonction d'une ou plusieurs colonnes. Elle divise l'ensemble des résultats en sous-ensembles ou groupes, où chaque groupe partage les mêmes valeurs dans la ou les colonnes spécifiées. Cela nous permet d'exécuter des fonctions d'agrégation (telles que SUM(), COUNT(), AVG(), etc.) sur chaque groupe séparément. Voici les questions d'entretien les plus courantes sur le GROUP BY. Pour n'afficher que les groupes comptant plus de trois coureurs, nous utilisons une clause HAVING qui filtre les valeurs renvoyées par la fonction d'agrégation COUNT(). La clause HAVING est souvent utilisée avec la clause GROUP BY pour filtrer les données groupées en fonction de conditions spécifiques. Son fonctionnement est similaire à celui de la clause WHERE, mais elle opère sur les données groupées plutôt que sur les lignes individuelles. Consultez cet article pour en savoir plus sur la clause HAVING. Exercice 5 : Combien de coureurs participent à chaque événement ? Exercice: Affichez le nom de l'événement et le nombre de membres du club qui participent à cet événement (appelez cette colonne runner_count). Notez qu'il peut y avoir des événements auxquels aucun membre du club ne participe. Pour ces événements, la colonne runner_count doit être égale à 0. Solution: SELECT event.name, COUNT(runner.id) AS runner_count FROM event LEFT JOIN runner_event ON runner_event.event_id = event.id LEFT JOIN runner ON runner_event.runner_id = runner.id GROUP BY event.name; Explication de la solution: Nous voulons afficher le nom de l'événement dans la table event et le nombre de participants dans la table runner. Les tables event et runner sont liées par une relation de plusieurs à plusieurs ; pour joindre ces tables, nous avons également besoin de la table runner_event qui relie les événements et les coureurs. Tout d'abord, nous effectuons une sélection dans la table event. Ensuite, nous la sélectionnons dans la table LEFT JOIN avec la table runner_event, qui est à son tour reliée à la table LEFT JOINed avec la table runner. Pourquoi utiliser ici le tableau LEFT JOIN? Parce que nous voulons nous assurer que tous les événements (même ceux qui n'ont pas de participants) sont affichés. Nous sélectionnons le nom de l'événement et le nombre de tous les participants ; par conséquent, nous devons GROUP BY le nom de l'événement pour obtenir le nombre de participants par événement. Veuillez noter que nous utilisons COUNT(runner_id) au lieu de COUNT(*) afin de nous assurer que nous affichons zéro pour les événements sans participants (c'est-à-dire pour les événements qui ne sont liés à aucun runner_id). Pour en savoir plus sur les différentes variantes de la fonction COUNT(), cliquez ici. Exercice 6 : Regrouper les coureurs par distance principale et par âge Exercice: Affichez la distance et le nombre de coureurs pour les catégories d'âge suivantes : moins de 20 ans, 20-29 ans, 30-39 ans, 40-49 ans et plus de 50 ans. Utilisez les alias de colonne suivants : under_20, age_20_29, age_30_39, age_40_49, et over_50. Solution: SELECT main_distance, COUNT(CASE WHEN age < 20 THEN id END) AS under_20, COUNT(CASE WHEN age >= 20 AND age < 30 THEN id END) AS age_20_29, COUNT(CASE WHEN age >= 30 AND age < 40 THEN id END) AS age_30_39, COUNT(CASE WHEN age >= 40 AND age < 50 THEN id END) AS age_40_49, COUNT(CASE WHEN age >= 50 THEN id END) AS over_50 FROM runner GROUP BY main_distance; Explication de la solution: Cet exercice est similaire à l'exercice 4 - nous voulons connaître le nombre de coureurs par valeur de distance. Nous sélectionnons donc la colonne main_distance et GROUP BY cette colonne. Ensuite, nous utilisons plusieurs fonctions d'agrégation COUNT() pour obtenir le nombre de coureurs par distance. Cependant, nous devons ici diviser les coureurs en fonction de leur âge. L'instruction CASE WHEN est très utile ici, car elle peut être utilisée pour évaluer des conditions et renvoyer différentes valeurs en fonction des résultats de ces conditions. Nous pouvons la passer comme argument à la fonction d'agrégation COUNT() pour obtenir le nombre de coureurs remplissant une condition donnée. Voyons comment cela fonctionne. CASE WHEN age >= 20 AND age < 30 THEN id END L'instruction CASE WHEN ne renvoie l'identifiant que si l'âge du coureur est supérieur ou égal à 20 et inférieur à 30. Dans le cas contraire, elle renvoie NULL. Lorsqu'elle est intégrée à la fonction d'agrégation COUNT(), elle renvoie le nombre de coureurs remplissant la condition définie dans l'instruction CASE WHEN. Pour obtenir le nombre de coureurs pour chacun des cinq groupes d'âge, nous devons utiliser autant de fonctions COUNT() et d'instructions CASE WHEN que de groupes d'âge. Pour en savoir plus sur le comptage des lignes en combinant CASE WHEN et GROUP BY, cliquez ici. Section 3 : Exercices avancés Fonctions de fenêtrage Dans les exercices SQL avancés suivants, nous utiliserons une base de données Northwind pour une boutique en ligne avec de nombreux produits alimentaires. Elle contient six tables : customers, orders, products, categories, order_items, et channels. Examinons les données de cette base. La table customers comporte 15 colonnes : customer_id stocke l'ID du client. email stocke l'adresse électronique du client. full_name stocke le nom complet du client. address stocke le numéro de rue et de maison du client. city stocke la ville où le client habite. region enregistre la région du client (pas toujours applicable). postal_code enregistre le code postal du client. country enregistre le pays du client. phone enregistre le numéro de téléphone du client. registration_date enregistre la date à laquelle le client s'est inscrit. channel_id enregistre l'ID du canal par lequel le client a trouvé le magasin. first_order_id enregistre l'identifiant de la première commande passée par le client. first_order_date enregistre la date de la première commande du client. last_order_id Enregistre l'identifiant de la dernière commande du client. last_order_date enregistre la date de la dernière commande du client. La table orders comporte les colonnes suivantes order_id stocke l'ID de la commande. customer_id stocke l'ID du client qui a passé la commande. order_date Enregistre la date à laquelle la commande a été passée. total_amount enregistre le montant total payé pour la commande. ship_name stores le nom de la personne à qui la commande a été envoyée. ship_address enregistre l'adresse (numéro de rue et rue) à laquelle la commande a été envoyée. ship_city enregistre la ville où la commande a été envoyée. ship_region enregistre la région dans laquelle se trouve la ville. ship_postalcode enregistre le code postal de la destination. ship_country indique le pays de destination. shipped_date enregistre la date à laquelle la commande a été expédiée. Le tableau products comporte les colonnes suivantes : product_id stocke l'ID du produit. product_name enregistre le nom du produit. category_id enregistre la catégorie à laquelle le produit appartient. unit_price enregistre le prix d'une unité du produit (par exemple, par bouteille, par paquet, etc.). discontinued indique si le produit n'est plus vendu. La table categories comporte les colonnes suivantes category_id stocke l'ID de la catégorie. category_name Enregistre le nom de la catégorie. description contient une brève description de la catégorie. La table order_items comporte les colonnes suivantes order_id ID de la commande dans laquelle le produit a été acheté. product_id Enregistre l'ID du produit acheté dans la commande. unit_price stocke le prix unitaire du produit. (Ce prix peut être différent du prix indiqué dans la catégorie du produit ; le prix peut changer au fil du temps et des remises peuvent être appliquées). quantity enregistre le nombre d'unités achetées dans la commande. discount stocke la remise appliquée au produit donné. La table channels comporte les colonnes suivantes : id stores the ID of the channel. channel_name stores the name of the channel through which the customer found the shop. Faisons quelques exercices SQL avancés qui se concentrent sur les fonctions de la fenêtre. Exercice 7 : Lister les 3 commandes les plus chères Exercice: Créez un classement dense des commandes en fonction de leur total_amount. Plus le montant est élevé, plus la commande doit être élevée. Si deux commandes ont le même total_amount, la commande la plus ancienne doit être la plus élevée (vous devrez ajouter la colonne order_date au classement). Nommez la colonne de classement rank. Ensuite, ne sélectionnez que les ordres ayant les trois classements denses les plus élevés. Affichez le rang, order_id et total_amount. Solution: WITH orders_with_ranking AS ( SELECT DENSE_RANK() OVER(ORDER BY total_amount DESC, order_date) AS rank, order_id, total_amount FROM orders ) SELECT * FROM orders_with_ranking WHERE rank <= 3; Explication de la solution: Commençons par la première partie de l'instruction. Nous voulons créer un classement dense des commandes sur la base de leur total_amount (plus la valeur est grande, plus le rang est élevé) et de leur order_date (plus la date est ancienne, plus le rang est élevé). Veuillez noter que la valeur du rang ne peut être dupliquée que lorsque les colonnes total_amount et order_date sont toutes deux égales pour plus d'une ligne. Pour ce faire, nous utilisons la fonction de fenêtre DENSE_RANK(). Dans sa clause OVER(), nous spécifions l'ordre : descendant pour les valeurs total_amount et ascendant pour les valeurs order_date. Nous affichons également les colonnes order_id et total_amount du tableau orders. Jusqu'à présent, nous avons listé tous les ordres avec leurs valeurs de classement dense. Mais nous ne voulons voir que les trois premiers ordres (dont la colonne de rang est inférieure ou égale à 3). Analysons les étapes que nous suivons à partir d'ici : Nous définissons une expression de table commune (ETC) à l'aide de l'instruction SELECT - c'est-à-dire que nous utilisons la clause WITH suivie du nom de l'ETC, puis nous plaçons l'instruction SELECT entre parenthèses. Nous effectuons ensuite une sélection à partir de cet ETC, en fournissant la condition pour la colonne "rank" dans la clause WHERE. Vous vous demandez peut-être pourquoi nous avons besoin d'une syntaxe aussi complexe pour définir un ETC et l'interroger ensuite. Vous pouvez dire que nous pourrions définir la condition pour la colonne rank dans la clause WHERE de la première requête SELECT. Ce n'est pas possible en raison de l'ordre d'exécution des requêtes SQL. Nous devons utiliser l'expression de table commune ici parce que vous ne pouvez pas utiliser les fonctions de fenêtre dans la clause WHERE. L'ordre des opérations en SQL est le suivant : FROM, JOIN WHERE GROUP BY Aggregate functions HAVING Window functions SELECT ORDER BY Vous ne pouvez utiliser les fonctions de fenêtre que dans les clauses SELECT et ORDER BY. Si vous souhaitez faire référence aux fonctions de fenêtre dans la clause WHERE, vous devez placer le calcul de la fonction de fenêtre dans un CTE (comme nous l'avons fait dans notre exemple) ou dans une sous-requête et faire référence à la fonction de fenêtre dans la requête externe. Suivez cet article pour en savoir plus sur les CTE et les CTE récursifs. Pour vous donner un aperçu des fonctions de classement disponibles, il existe trois fonctions qui vous permettent de classer vos données : RANK(), DENSE_RANK(), et ROW_NUMBER(). Voyons-les en action. Values to be ranked RANK() DENSE_RANK() ROW_NUMBER() 1 1 1 1 1 1 1 2 1 1 1 3 2 4 2 4 3 5 3 5 3 5 3 6 4 7 4 7 5 8 5 8 La fonction RANK() attribue le même rang si plusieurs lignes consécutives ont la même valeur. Ensuite, la ligne suivante obtient le rang suivant comme si les lignes précédentes avaient des valeurs distinctes. Ici, les rangs 1,1,1 sont suivis de 4 (comme s'il s'agissait de 1,2,3 au lieu de 1,1,1). La fonction DENSE_RANK() attribue également le même rang si plusieurs lignes consécutives ont la même valeur. Dans ce cas, la ligne suivante obtient le rang supérieur d'une unité à celui de la ligne précédente. Ici, 1,1,1 est suivi de 2. La fonction ROW_NUMBER() attribue des nombres consécutifs à chaque ligne suivante sans tenir compte des valeurs des lignes. Voici un article sur la manière de classer les données. Vous pouvez également en savoir plus sur les différences entre les fonctions de classement de SQL. Exercice 8 : Calculer les deltas entre les ordres consécutifs Exercice: Dans cet exercice, nous allons calculer la différence entre deux commandes consécutives du même client. Montrez l'ID de la commande (order_id), l'ID du client (customer_id), le total_amount de la commande, le total_amount de la commande précédente basé sur le order_date (nommez la colonne previous_value), et la différence entre le total_amount de la commande actuelle et la commande précédente (nommez la colonne delta). Solution: SELECT order_id, customer_id, total_amount, LAG(total_amount) OVER(PARTITION BY customer_id ORDER BY order_date) AS previous_value, total_amount - LAG(total_amount) OVER(PARTITION BY customer_id ORDER BY order_date) AS delta FROM orders; Explication de la solution: Nous sélectionnons ici l'ID de la commande, l'ID du client et le montant total dans la table orders. La fonction LAG() récupère la valeur précédente total_amount. Dans la clause OVER(), nous définissons la fonction LAG() séparément pour chaque client et nous classons les résultats en fonction de la date de la commande. Enfin, nous soustrayons la valeur renvoyée par la fonction LAG() de la valeur total_amount pour chaque ligne afin d'obtenir le delta. La colonne previous_value enregistre une valeur nulle pour la première ligne, car il n'y a pas de valeurs précédentes. Par conséquent, la colonne delta est également nulle pour la première ligne. Les valeurs suivantes de la colonne delta enregistrent les différences entre les commandes consécutives passées par le même client. Il convient de préciser qu'un delta représente la différence entre deux valeurs. En calculant le delta entre les montants quotidiens des ventes, nous pouvons déterminer la direction de la croissance/décroissance des ventes au jour le jour. Suivez cet article pour en savoir plus sur le calcul des différences entre deux lignes. Et voici comment calculer les différences d'une année sur l'autre. Exercice 9 : Calculer le total courant des achats par client Exercice: Pour chaque client et ses commandes, indiquez les éléments suivants : customer_id - l'ID du client. full_name - le nom complet du client. order_id - l'identifiant de la commande. order_date - la date de la commande total_amount - le montant total dépensé pour cette commande running_total - le montant total dépensé par le client en question. Triez les lignes en fonction de l'identifiant du client et de la date de la commande. Lasolution: SELECT orders.customer_id, customers.full_name, orders.order_id, orders.order_date, orders.total_amount, SUM(orders.total_amount) OVER(PARTITION BY orders.customer_id ORDER BY orders.order_date) AS running_total FROM orders JOIN customers ON orders.customer_id = customers.customer_id ORDER BY orders.customer_id, orders.order_date; Explication de la solution: Un total courant fait référence au calcul qui accumule les valeurs d'une colonne ou d'une expression spécifique au fur et à mesure que les lignes sont traitées dans un ensemble de résultats. Il fournit une somme courante des valeurs rencontrées jusqu'à la ligne actuelle. Un total courant est calculé en ajoutant la valeur courante à la somme de toutes les valeurs précédentes. Cela peut s'avérer particulièrement utile dans divers scénarios, tels que le suivi des ventes cumulées, le calcul des soldes courants ou l'analyse des progrès cumulés au fil du temps. Suivez cet article pour en savoir plus sur le calcul d'un total courant. Et voici un article sur le calcul des moyennes mobiles. Nous sélectionnons l'ID du client, l'ID de la commande, la date de la commande et le total de la commande dans la table orders. Ensuite, nous joignons la table orders à la table customers sur leurs colonnes customer_id respectives afin de pouvoir afficher le nom complet du client. Nous utilisons la fonction de fenêtre SUM() pour calculer le total courant pour chaque client séparément (PARTITION BY orders.customer_id), puis nous l'ordonnons par ordre croissant de date (ORDER BY orders.order_date). Enfin, nous classons les résultats de cette requête par numéro de client et par date de commande. Section 4 : Exercices avancés de requêtes récursives Dans les exercices SQL avancés suivants, nous utiliserons la base de données d'un site web qui stocke des informations sur les étudiants et les cours. Elle contient trois tables : student, course, et student_course. Examinons les données de cette base. La table student contient les colonnes suivantes id stocke le numéro d'identification unique de chaque étudiant. name stocke le nom de l'étudiant. email stocke l'adresse électronique de l'étudiant. invited_by_id stocke l'ID de l'élève qui a invité cet élève sur le site web. Si l'étudiant s'est inscrit sans invitation, cette colonne sera NULL. La table course comprend les colonnes suivantes : id stocke le numéro d'identification unique de chaque cours. name stocke le nom du cours. La table student_course contient les colonnes suivantes id stocke l'identifiant unique de chaque ligne. student_id stocke l'identifiant de l'étudiant. course_id stocke l'identifiant du cours. minutes_spent stocke le nombre de minutes que l'étudiant a consacrées au cours. is_completed est mis à True lorsque l'étudiant termine le cours. Les exercices de cette section sont tirés de notre jeu d'exercicesFonctions de fenêtrage . Dans cet ensemble, vous trouverez plus d'exercices de fonctions de fenêtre sur les bases de données qui stockent les ventes au détail, suivent les compétitions et le trafic des sites Web. Faisons quelques exercices SQL avancés qui se concentrent sur les requêtes récursives. Exercice 10 : Trouver le chemin d'invitation pour chaque étudiant Exercice: Montrez le chemin des invitations pour chaque étudiant (nommez cette colonne path). Par exemple, si Marie a été invitée par Alice et qu'Alice n'a été invitée par personne, le chemin d'accès de Marie devrait ressembler à ceci : Alice->Mary. Incluez les adresses id, name et invited_by_id de chaque étudiant dans les résultats. Solution: WITH RECURSIVE hierarchy AS ( SELECT id, name, invited_by_id, CAST(name AS text) AS path FROM student WHERE invited_by_id IS NULL UNION ALL SELECT student.id, student.name, student.invited_by_id, hierarchy.path || '->' || student.name FROM student, hierarchy WHERE student.invited_by_id = hierarchy.id ) SELECT * FROM hierarchy; Explication de la solution: Cet exercice nécessite la création d'une valeur personnalisée pour la colonne chemin qui contient le chemin d'invitation de chaque client. Par exemple, Ann Smith a été invité par Veronica Knight, qui à son tour a été invité par Karli Roberson; par conséquent, nous obtenons la colonne chemin comme Karli Roberson->Veronica Knight->Ann Smith pour le nom Ann Smith. Comme vous pouvez le constater, nous avons besoin d'un mécanisme de récursivité pour creuser le chemin d'invitation. Nous pouvons écrire une requête récursive en la définissant avec l'instruction WITH RECURSIVE, suivie du nom de la requête. Le contenu de la requête récursive hierarchy est le suivant : Nous sélectionnons les colonnes id, name et invited_by_id dans la table student. Ensuite, nous utilisons la fonction CAST() pour convertir le type de colonne name en type de données TEXT, ce qui garantit une concaténation fluide (avec -> et les noms suivants) dans la requête principale. La condition de la clause WHERE garantit que seuls les étudiants qui n'ont pas été invités sont répertoriés par cette requête. L'opérateur UNION ALL combine les ensembles de résultats de deux ou plusieurs instructions SELECT sans supprimer les doublons. Ici, les requêtes sur lesquelles UNION ALL est exécuté ont les mêmes ensembles de quatre colonnes ; l'ensemble de résultats de l'une est ajouté à l'ensemble de résultats de l'autre. Dans l'instruction SELECT suivante, nous sélectionnons à nouveau les colonnes id, name et invited_by_id de la table student. Ensuite, nous concaténons la colonne path (qui provient de la requête récursive hiérarchique définie dans la première instruction SELECT ) avec le signe -> et le nom de l'étudiant. Pour réaliser cette concaténation, nous sélectionnons à la fois dans la table des étudiants et dans la requête récursive hiérarchique (c'est là que le mécanisme récursif entre en jeu). Dans la clause WHERE, nous définissons que la colonne invited_by_id de la table student est égale à la colonne id de la requête récursive hiérarchique, ce qui nous permet d'obtenir le nom de l'étudiant qui a invité l'étudiant actuel ; à l'itération suivante, nous obtenons le nom de l'étudiant qui a invité cet étudiant, et ainsi de suite. Il s'agit d'une requête récursive, car elle s'interroge elle-même pour remonter le chemin des invitations. Progresser une requête à la fois Les exercices SQL avancés présentés dans cet article constituent une plate-forme complète pour améliorer vos compétences SQL, une requête à la fois. En explorant les fonctions de fenêtre, JOINs, GROUP BY, et bien d'autres, vous avez approfondi votre compréhension des concepts SQL complexes et acquis une expérience pratique dans la résolution de problèmes liés aux données du monde réel. La pratique est la clé de la maîtrise des compétences SQL. Grâce à une pratique régulière, vous pouvez améliorer vos compétences et transformer vos connaissances théoriques en expertise pratique. Cet article présente des exercices tirés de nos cours ; vous pouvez découvrir d'autres exercices de ce type en vous inscrivant à nos cours : Fonctions de fenêtrage Ensemble d'exercices 2021 Mensuel Exercices Pratiques de SQLs - Avancé 2022 Mensuel Exercices Pratiques de SQLs - Avancé Inscrivez-vous maintenant et commencez gratuitement ! Nous vous souhaitons bonne chance ! Tags: sql pratique sql