15th Mar 2023 36 minutes de lecture 27 questions d'entretien pour SQL avancé avec réponses Tihomir Babic sql emplois et carrières Où un professionnel SQL peut-il trouver un guide complet des questions d'entretien SQL avancées ? La réponse la plus courte est : ici ! Nous avons sélectionné les 27 questions SQL les plus importantes et y avons répondu pour vous. Se préparer à un entretien SQL n'est pas facile, surtout si votre poste requiert des connaissances avancées en SQL. Cet article contient les 27 questions les plus fréquemment posées lors d'un entretien SQL avancé et fournit des réponses détaillées ainsi que des ressources pour une lecture plus approfondie. Nous allons passer en revue ces quatre concepts principaux et quelques autres encore : JOINs GROUP BY, WHERE, et HAVING CTEs (Common Table Expressions) et les requêtes récursives Fonctions de fenêtrage La meilleure façon de rafraîchir vos connaissances en SQL avancé est de suivre notre cours interactif. SQL avancé interactive. Elle comporte plus de 300 exercices pratiques sur les fonctions de fenêtre, les expressions de table commune, les fonctions récursives, etc. Sans plus attendre, attaquons ces questions de front ! 1) Qu'est-ce qu'un JOIN en SQL ? JOIN Le JOIN est une commande SQL qui permet de combiner deux tables ou plus. Cela se fait par l'intermédiaire d'une colonne commune (c'est-à-dire une colonne qui a les mêmes valeurs dans les deux tables), ce qui permet d'utiliser les données de deux tables ou plus en même temps. La jonction de tables en SQL est essentielle en raison de la nature des bases de données relationnelles : les données sont atomisées en tables, chaque table ne contenant qu'une partie des données disponibles dans la base de données. Nous utiliserons deux tables pour montrer comment cela fonctionne. La première table est football_players. idfirst_namelast_namenational_team_idgames_played 1GianfrancoZola135 2Virgilvan Dijk253 3MarcusRashford351 4KylianMbappé566 5PhilFoden322 6Frenkiede Jong222 7MarioBalotelli136 8ErlingHaaland623 La seconde est national_team. idcountry 1Italy 2Netherlands 3England 4Croatia Voici une requête qui joint deux tables : SELECT fp.id, first_name, last_name, national_team_id, country, games_played FROM football_players fp JOIN national_team nt ON fp.national_team_id = nt.id ORDER BY fp.id; Elle sélectionne des colonnes dans les deux tables. Pour les joindre, nous référençons d'abord une table dans la clause FROM. Cette clause est suivie de JOIN, puis de la deuxième table. Nous utilisons la clause ON pour spécifier la condition avec laquelle les tables seront jointes : le national_team_id dans la table football_players doit être égale à la colonne id de la table national_team dans la table. Le résultat de la requête est le suivant : idfirst_namelast_namenational_team_idcountrygames_played 1GianfrancoZola1Italy35 2Virgilvan Dijk2Netherlands53 3MarcusRashford3England51 5PhilFoden3England22 6Frenkiede Jong2Netherlands22 7MarioBalotelli1Italy36 INNER JOIN est l'une des nombreuses jointures distinctes en SQL. Sa caractéristique est qu'elle ne renvoie que les données des tables jointes pour lesquelles la condition de jointure est vraie. Voici plus de détails sur le fonctionnement de la jointure INNER JOIN en SQL. 2) Quelle est la différence entre INNER JOIN, LEFT JOIN, RIGHT JOIN et FULL JOIN ? Il existe différents types de jointures en SQL. Les jointures les plus couramment utilisées sont INNER JOIN, LEFT JOIN, RIGHT JOIN, et FULL JOIN. LEFT JOIN, RIGHT JOIN, et FULL JOIN sont ce que l'on appelle des jointures externes. JOIN (alias INNER JOIN) est une jointure interne. Dans ce cas, "interne" signifie qu'elle renvoie uniquement les lignes des deux tables qui satisfont à la condition de jointure ; les jointures externes renvoient toutes les lignes d'une table, plus les lignes correspondantes de l'autre (des autres) table(s). L'exception est FULL JOIN, qui renvoie toutes les lignes des deux tables. Voici le résultat de la requête INNER JOIN de l'exemple précédent. Reprenons-le ici. Ainsi, il sera plus facile de voir la différence entre les différentes jointures. idfirst_namelast_namenational_team_idcountrygames_played 1GianfrancoZola1Italy35 2Virgilvan Dijk2Netherlands53 3MarcusRashford3England51 5PhilFoden3England22 6Frenkiede Jong2Netherlands22 7MarioBalotelli1Italy36 LEFT JOIN renvoie toutes les données de la table de gauche (c'est-à-dire la première table, qui est listée avant le mot-clé JOIN ) et seulement les lignes correspondantes de la table de droite (la deuxième table, listée après le mot-clé JOIN ). S'il n'y a pas de données correspondantes dans le tableau de droite, les valeurs manquantes sont affichées sous la forme NULLs. Voici la même requête avec LEFT JOIN remplaçant INNER JOIN: SELECT fp.id, first_name, last_name, national_team_id, country, games_played FROM football_players fp LEFT JOIN national_team nt ON fp.national_team_id = nt.id ORDER BY fp.id; Le tableau de gauche est football_playerset le tableau de droite est national_team. Comme on peut s'y attendre, le résultat est différent : idfirst_namelast_namenational_team_idcountrygames_played 1GianfrancoZola1Italy35 2Virgilvan Dijk2Netherlands53 3MarcusRashford3England51 4KylianMbappé5NULL66 5PhilFoden3England22 6Frenkiede Jong2Netherlands22 7MarioBalotelli1Italy36 8ErlingHaaland6NULL23 Tous les joueurs de football de la table de gauche sont ici. Cependant, Kylian Mbappé et Erling Haaland n'ont pas de pays correspondant dans le tableau de droite, et il y a donc NULLs dans la colonne country pour ces joueurs. Ces lignes n'étaient pas présentes dans le résultat de INNER JOIN. Elles ont été ajoutées par LEFT JOIN. RIGHT JOIN fait l'inverse : il renvoie toutes les données de la table de droite et uniquement les données correspondantes de la table de gauche. Lorsqu'il n'y a pas de données correspondantes dans le tableau de gauche, les valeurs manquantes sont affichées sous la forme NULLs. Voici le code : SELECT fp.id, first_name, last_name, national_team_id, country, games_played FROM football_players fp RIGHT JOIN national_team nt ON fp.national_team_id = nt.id ORDER BY fp.id; Tout reste identique, sauf que nous utilisons RIGHT JOIN au lieu de LEFT JOIN. Voici le résultat : idfirst_namelast_namenational_team_idcountrygames_played 1GianfrancoZola1Italy35 2Virgilvan Dijk2Netherlands53 3MarcusRashford3England51 5PhilFoden3England22 6Frenkiede Jong2Netherlands22 7MarioBalotelli1Italy36 NULLNULLNULLNULLCroatiaNULL Nous avons maintenant toutes les équipes nationales et leurs joueurs. Mais vous pouvez voir qu'un pays (la Croatie) n'a aucun joueur dans le tableau de gauche. Les colonnes des joueurs pour la Croatie sont remplies avec des NULL. FULL JOIN sort toutes les données de toutes les tables jointes. Encore une fois, s'il n'y a pas de données correspondantes dans la table correspondante, les valeurs manquantes apparaîtront comme NULL. Une fois de plus, nous modifions le type de jointure dans la requête : SELECT fp.id, first_name, last_name, national_team_id, country, games_played FROM football_players fp FULL JOIN national_team nt ON fp.national_team_id = nt.id ORDER BY fp.id; Elle renverra toutes les données des deux tables. Toute donnée non correspondante est remplacée par NULLs. Tous les joueurs figurent dans le résultat, même s'ils n'ont pas de pays correspondant dans l'autre table. Tous les pays figurent dans le résultat, même s'ils n'ont pas de joueurs dans l'autre table. football_player tableau. Le résultat FULL JOIN est l'union de LEFT JOIN et RIGHT JOIN: idfirst_namelast_namenational_team_idcountrygames_played 1GianfrancoZola1Italy35 2Virgilvan Dijk2Netherlands53 3MarcusRashford3England51 4KylianMbappé5NULL66 5PhilFoden3England22 6Frenkiede Jong2Netherlands22 7MarioBalotelli1Italy36 8ErlingHaaland6NULL23 NULLNULLNULLNULLCroatiaNULL Vous trouverez plus d'informations dans l'article sur les différents types de JOIN. Vous pouvez également consulter notre Les jointures en SQL Cheat Sheet pour une mise à jour rapide. 3. qu'est-ce qu'une jointure croisée ? Un CROSS JOIN est un autre type de jointure disponible en SQL. Il renvoie un produit cartésien. Cela signifie que la jointure croisée renvoie chaque ligne de la première table combinée à chaque ligne de la seconde table. Ce type de jointure n'est pas utilisé très souvent. Mais si vous êtes tenté de l'utiliser, réfléchissez-y à deux fois. L'affichage de toutes les combinaisons de lignes peut prendre un certain temps - si tant est que la requête se termine ! À titre d'exemple, utilisons les tableaux présentés dans les deux questions précédentes. Pour écrire la requête, utilisez le mot-clé CROSS JOIN. Comme il s'agit d'un type de jointure qui renvoie toutes les combinaisons de lignes de toutes les tables, il n'y a pas de clause ON. Jetez un coup d'œil : SELECT fp.id, first_name, last_name, national_team_id, country, games_played FROM football_players fp CROSS JOIN national_team nt; Voici le résultat. Tous les joueurs de la table football_players sont répertoriés avec tous les pays du tableau national_team table. idfirst_namelast_namenational_team_idcountrygames_played 1GianfrancoZola1Italy35 2Virgilvan Dijk2Italy53 3MarcusRashford3Italy51 4KylianMbappé5Italy66 5PhilFoden3Italy22 6Frenkiede Jong2Italy22 7MarioBalotelli1Italy36 8ErlingHaaland6Italy23 1GianfrancoZola1Netherlands35 2Virgilvan Dijk2Netherlands53 3MarcusRashford3Netherlands51 4KylianMbappé5Netherlands66 5PhilFoden3Netherlands22 6Frenkiede Jong2Netherlands22 7MarioBalotelli1Netherlands36 8ErlingHaaland6Netherlands23 1GianfrancoZola1England35 2Virgilvan Dijk2England53 3MarcusRashford3England51 4KylianMbappé5England66 5PhilFoden3England22 6Frenkiede Jong2England22 7MarioBalotelli1England36 8ErlingHaaland6England23 1GianfrancoZola1Croatia35 2Virgilvan Dijk2Croatia53 3MarcusRashford3Croatia51 4KylianMbappé5Croatia66 5PhilFoden3Croatia22 6Frenkiede Jong2Croatia22 7MarioBalotelli1Croatia36 8ErlingHaaland6Croatia23 Pour en savoir plus sur le site CROSS JOIN, consultez ce guide illustré de la jointure croisée SQL. 4) Qu'est-ce qu'une jointure automatique en SQL ? Comme vous vous en doutez probablement, une jointure automatique se produit lorsque la table est jointe à elle-même. Il est important de noter qu'il ne s'agit pas d'une commande distincte en SQL : n'importe quel type JOIN peut être utilisé pour joindre une table à elle-même. La jointure s'effectue comme n'importe quelle autre JOIN, mais cette fois-ci, vous ferez référence à la même table de part et d'autre du mot-clé JOIN. L'auto-jointure est particulièrement utile lorsqu'une table possède une clé étrangère qui fait référence à sa clé primaire. Cela vous permet d'interroger des données hiérarchiques, telles que des arbres généalogiques ou la hiérarchie organisationnelle d'une entreprise. Elle est également utile lorsque vous souhaitez trouver des paires de valeurs. Dans l'exemple ci-dessous, nous recherchons des joueurs de la même équipe nationale : SELECT fp1.id, fp1.first_name, fp1.last_name, fp1.national_team_id, fp2.id AS id_2, fp2.first_name AS first_name_2, fp2.last_name AS last_name_2, fp2.national_team_id as national_team_id_2 FROM football_players fp1 JOIN football_players fp2 ON fp1.id <> fp2.id AND fp1.national_team_id = fp2.national_team_id; L'auto-jonction signifie qu'au lieu de deux tables, vous spécifiez la même table deux fois : une fois dans la clause FROM et une fois après la clause JOIN. Comme vous utilisez deux fois la même table, vous devez utiliser des alias pour les tables. Chaque occurrence de la table doit recevoir un alias distinct (fp1, fp2 dans notre requête) afin qu'il soit clair à quelle occurrence de la table nous faisons référence. Nous joignons des joueurs de la même équipe nationale (leurs valeurs national_team_id sont égales). Cependant, nous ne voulons pas lister un joueur avec lui-même, donc nous excluons le cas où fp1.id et fp2.id sont égaux. Le résultat de la requête est le suivant : idfirst_namelast_namenational_team_idid_2first_name_2last_name_2national_team_id_2 1GianfrancoZola17MarioBalotelli1 2Virgilvan Dijk26Frenkiede Jong2 3MarcusRashford35PhilFoden3 5PhilFoden33MarcusRashford3 6Frenkiede Jong22Virgilvan Dijk2 7MarioBalotelli11GianfrancoZola1 Vous pouvez utiliser les colonnes national_team_id des deux tables pour confirmer que Gianfranco Zola et Mario Balotelli ont bien joué pour la même équipe. Pour en savoir plus, consultez notre article sur les exemples d'auto-jointures. 5. joindre deux tables à l'aide d'une jointure à deux colonnes Vous disposez de deux tables. La première est employeequi contient les données suivantes : idfirst_namelast_name 1SteveBergman 2SteveJohnson 3SteveKing La seconde table est customerqui contient les données suivantes : idfirst_namelast_name 1AnnColeman 2SteveBergman 3SteveYoung 4DonnaWinter 5SteveKing Votre tâche consiste à rechercher tous les employés qui sont également clients de l'entreprise. Malheureusement, vous ne pouvez pas utiliser la colonne id car il s'agit de l'ID de l'employé dans une table et de l'ID du client dans une autre. En d'autres termes, il n'existe pas de colonne unique dans une table qui fasse référence à l'autre. La solution consiste à joindre les tables sur le nom et le prénom, c'est-à-dire à utiliser deux colonnes JOIN. Le code ci-dessous joint d'abord les tables sur le prénom. Ensuite, le mot-clé AND définit la deuxième condition de jointure, qui est le nom de famille. De cette manière, vous obtiendrez les données des deux tables pour lesquelles la combinaison prénom/nom est identique. Si nous n'avions utilisé qu'une seule de ces colonnes, nous aurions pu obtenir des données erronées, car les employés et les clients peuvent avoir le même prénom mais des noms de famille différents (ou vice versa). Voici la requête : SELECT e.first_name, e.last_name FROM employee e JOIN customer c ON e.first_name = c.first_name AND e.last_name = c.last_name; Voici le code de sortie.. : first_namelast_name SteveBergman SteveKing Le résultat montre que Steve Bergman et Steve King sont à la fois des employés et des clients de l'entreprise. 6. joindre deux tables à l'aide d'une jointure non équivoque Jusqu'à présent, nous avons utilisé des jointures égales : des jointures où il y a un signe d'égalité dans la condition ON. Inversement, la jointure non-équi est une jointure dont la clause ON contient une condition de non-égalité. Cette fois-ci, nous disposons de données sur les utilisateurs de téléphones mobiles et leur consommation de données. La première table est mobile_userqui indique les utilisateurs mobiles et leur limite mensuelle de données mobiles en Mo : idfirst_namelast_namemobile_data_limit 1MichaelWatson5,000 2NicoleGomez10,000 3SamStone8,000 Le deuxième tableau est data_usagequi indique la consommation mensuelle réelle de données de l'utilisateur en Mo : idmobile_user_iddata_usedperiod 114,9872022_10 226,8752022_10 3312,5472022_10 415,0372022_11 5211,1112022_11 634,8972022_11 La tâche consiste à trouver toutes les données pour lesquelles l'utilisation réelle est supérieure à la limite mensuelle. Nous voulons voir le nom et le prénom de l'utilisateur, la limite mensuelle, les données réellement utilisées et la période de temps. La solution consiste à utiliser la jointure non équivoque, comme indiqué ci-dessous : SELECT first_name, last_name, mobile_data_limit, data_used, period FROM mobile_user mu JOIN data_usage du ON mu.id = du.mobile_user_id AND mobile_data_limit < data_used; La requête sélectionne toutes les informations requises dans deux tables. Les tables sont jointes à l'aide d'un INNER JOIN. Nous les joignons d'abord lorsque l'ID de l'utilisateur est le même. Ensuite, nous ajoutons la deuxième condition après le mot-clé AND. Nous avons ici une condition de non-égalité qui nous permettra d'obtenir des données dont la limite est inférieure à la consommation mensuelle. Vous pouvez voir le résultat ci-dessous : first_namelast_namemobile_data_limitdata_usedperiod SamStone8,00012,5472022_10 MichaelWatson5,0005,0372022_11 NicoleGomez10,00011,1112022_11 Si vous êtes intéressé, voici d'autres exemples de jointures non égales. 7) Que fait DISTINCT ? DISTINCTL'objectif de DISTINCT, en général, est de supprimer les valeurs en double. Ou, en d'autres termes, d'afficher les valeurs uniques dans le résultat de votre requête. Imaginez que vous travaillez avec la table suivante loans qui présente les identifiants des prêts et leurs durées en mois. loan_idloan_duration 10011260 10020560 10020848 100333120 10035748 100398120 Plusieurs prêts peuvent avoir la même durée, et vous souhaitez donc extraire la liste des durées possibles. Vous pouvez le faire en utilisant DISTINCT: SELECT DISTINCT loan_duration FROM loans ORDER BY loan_duration; Le résultat montre qu'il existe des prêts d'une durée de 48, 60 et 120 mois : loan_duration 48 60 120 DISTINCT peut être utilisé dans SELECT avec une colonne pour n'afficher que les valeurs uniques de cette colonne, comme dans l'exemple ci-dessus. S'il est utilisé dans SELECT mais avec plusieurs colonnes, le résultat affichera les combinaisons uniques de toutes ces colonnes. Vous pouvez également utiliser DISTINCT avec des fonctions d'agrégation. Dans ce cas, votre requête éliminera les résultats d'agrégation en double. Vous pouvez voir des exemples de ces utilisations dans notre article sur le rôle de DISTINCT en SQL. 8) Que fait GROUP BY en SQL ? GROUP BY GROUP BY est une clause SQL utilisée pour organiser les données en groupes basés sur une ou plusieurs valeurs communes. Elle est le plus souvent utilisée avec les fonctions d'agrégation ; cette combinaison renvoie des données agrégées pour chaque groupe. Cependant, il est important de savoir que l'utilisation de fonctions d'agrégation dans la clause GROUP BY n'est pas autorisée. La syntaxe générale de GROUP BY est la suivante : SELECT column_1, column_2, …, FROM table_name WHERE … GROUP BY column_1, column_2 HAVING … ORDER BY column_1, column_2; Supposons que la table salaries: idfirst_namelast_namesalarydepartment 1NicholasPoirot4,798.44IT 2SamanthaWolf5,419.24IT 3StewartJohnsons5,419.24IT 4JackieBiden8,474.54Sales 5MarkHamilton10,574.84Sales 6MarianaCosta9,747.54Sales 7PaulStewart3,498.12Accounting 8MaryRutte4,187.23Accounting 9ThomasSchwarz3,748.55Accounting Nous utiliserons GROUP BY et AVG() pour trouver le salaire moyen par département : SELECT department, AVG(salary) AS average_salary FROM salaries GROUP BY department; Nous voulons voir les départements, nous sélectionnons donc cette colonne. Pour calculer le salaire moyen, nous appliquons la fonction AVG() à la colonne salary. Toutes les colonnes énumérées dans GROUP BY définissent les groupes de données. Dans notre exemple, les groupes sont définis par la colonne des départements : nous calculons le salaire moyen pour chaque département. Le regroupement et l'agrégation des données se présentent comme suit : departmentaverage_salary Accounting3,811.30 Sales9,598.97 IT5,212.31 Le salaire moyen dans le département Comptabilité est de 3.811,30. Les salaires moyens dans les deux autres départements sont respectivement de 9.598,97 et 5.212,31. Lors de la rédaction d'une requête, GROUP BY doit toujours être placé après WHERE mais avant la clause HAVING. Vous pouvez en apprendre davantage à ce sujet dans cet article sur le GROUP BY en SQL. 9. comment filtrer les groupes GROUP BY ? Une fois que vous avez obtenu les groupes que vous avez spécifiés dans GROUP BY, vous voudrez parfois les filtrer. L'indice pour y parvenir se trouve dans la syntaxe de la question précédente. La clause qui vous permet de filtrer les groupes est HAVING. Une fois les critères de filtrage spécifiés dans HAVING, la requête renvoie toutes les données qui satisfont aux critères. Toutes les autres données seront filtrées. Voici comment cela fonctionne sur les données de la question précédente si nous devions afficher uniquement les départements dont le salaire moyen est inférieur à 5 500 dollars. SELECT department, AVG(salary) AS average_salary FROM salaries GROUP BY department HAVING AVG(salary) < 5500; Le code est très similaire à celui de la question précédente. La différence réside dans la clause HAVING. Nous l'utilisons pour filtrer les résultats et n'afficher que les départements dont les salaires sont inférieurs à 5 500 dollars. Voici ce que le code renvoie : departmentaverage_salary Accounting3,811.30 IT5,212.31 Le département qui ne figure pas dans le résultat est celui des ventes, car son salaire moyen est de 9 598,97 euros. Vous trouverez d'autres exemples pratiques de cette clause dans cet article expliquant HAVING en SQL. 10) Quelle est la différence entre WHERE et HAVING ? Si vous connaissez les réponses aux deux questions précédentes, vous connaissez probablement la réponse à cette question. La principale différence est que WHERE est utilisé pour filtrer les données avant qu'elles ne soient regroupées. Sa position dans la déclaration SELECT le montre : il vient avant GROUP BY. En raison de son objectif, aucune fonction d'agrégation n'est autorisée dans WHERE. HAVINGLa fonction d'agrégation, au contraire, est utilisée pour filtrer les données après leur regroupement ; c'est pourquoi elle est utilisée après GROUP BY. De même, HAVING autorise les conditions qui incluent des fonctions d'agrégation. La meilleure façon d'apprendre la distinction est de lire cet article sur WHERE vs. HAVING en SQL. 11) Quel sera le résultat de la requête suivante qui tente de filtrer les NULL ? Vous aurez souvent ce type de question lors de votre entretien SQL avancé : on vous donnera un code et vous devrez décrire le résultat de la requête. Bien que l'écriture et la lecture de code SQL aillent de pair, il est toujours différent de devoir analyser le code que quelqu'un d'autre a écrit. Vous avez des données dans la table contributors: idfirst_namelast_namestart_datetermination_date 1ValeriaBogdanov2022-10-11NULL 2NicholasBertolucci2022-04-072022-11-11 3MathildeBauman2022-05-252022-10-01 4TrevorTrucks2022-01-28NULL 5MariaSzabo2022-03-15NULL Que va retourner ce code ? SELECT first_name, last_name, start_date, termination_date FROM contributors WHERE termination_date != '2022-10-01'; Si vous répondez qu'il retournera toutes les lignes sauf ID = 3, vous vous trompez ! Il s'agit d'une question piège. En lisant la condition WHERE, vous pourriez la lire comme suit : retourner toutes les données dont la date de fin est différente de 2022-10-01. En regardant le tableau, on pourrait penser qu'il s'agit de toutes les lignes sauf une. C'est le cas, mais pas pour SQL ! Comme vous pouvez le voir, il y a trois lignes avec des valeurs NULL. Pour SQL, NULL n'est pas une valeur, c'est une non-valeur. Ainsi, lorsque vous définissez la condition dans WHERE de cette manière, vous excluez toutes les dates qui ne sont pas égales à 2022-10-01 et les valeurs NULL. En voici la preuve : first_namelast_namestart_datetermination_date NicholasBertolucci2022-04-072022-11-11 Vous pouvez en savoir plus sur cet opérateur et sur d'autres opérateurs de comparaison utilisés avec NULL. 12. écrivez une requête qui trouve le nombre de chansons par artiste. Utilisez LEFT JOIN et COUNT(). Supposons que l'on vous donne deux tables : artist et song. Voici les artist données : idartist_name 1Prince 2Jimi Hendrix 3Santana 4Otis Redding 5Lou Rawls Voici les données song données : idartist_idsong_title 11Purple Rain 22Purple Haze 33Europa 41Cream 51Bambi 61Why You Wanna Treat Me So Bad? 72Spanish Castle Magic 83Taboo 93Incident at Neshabur 103Flor D' Luna Vous devez utiliser LEFT JOIN et COUNT() pour trouver tous les artistes, leurs identifiants et le nombre de leurs chansons dans la base de données. Vous pourriez être tenté de suggérer cette solution : SELECT a.id, artist_name, COUNT(*) AS number_of_songs FROM artist a LEFT JOIN song s ON a.id = s.artist_id GROUP BY a.id, artist_name ORDER BY a.id; Jetons un coup d'œil à la sortie : idartist_namenumber_of_songs 1Prince4 2Jimi Hendrix2 3Santana4 4Otis Redding1 5Lou Rawls1 Le résultat montre tous les artistes ; c'est correct. Cependant, le nombre de chansons pour Otis Redding et Lou Rawls est de un, ce qui est faux ! Jetez un coup d'œil au tableau songet vous verrez qu'il n'y a pas d'ID d'artiste égal à 4 ou 5. Qu'est-ce qui ne va pas ? Lorsque l'on utilise COUNT(*) avec LEFT JOIN, la fonction d'agrégation compte toutes les valeurs non appariées (NULLs). C'est pourquoi le résultat montre une chanson pour Otis Redding et Lou Rawls, même s'ils n'ont aucune chanson dans le tableau. COUNT(*) est utilisé pour compter toutes les lignes. Pour obtenir une réponse correcte, vous devez utiliser COUNT(song_title) à la place. SELECT a.id, artist_name, COUNT(song_title) AS number_of_songs FROM artist a LEFT JOIN song s ON a.id = s.artist_id GROUP BY a.id, artist_name ORDER BY a.id; Ce code vous donnera la bonne réponse : idartist_namenumber_of_songs 1Prince4 2Jimi Hendrix2 3Santana4 4Otis Redding0 5Lou Rawls0 Le nombre de chansons de Prince, Jimi Hendrix et Santana est resté le même que dans le résultat précédent. Cependant, le nombre de chansons des deux autres artistes est maintenant égal à zéro, et c'est le bon résultat. 13) Quelle est la différence entre JOIN et UNION ? JOIN L'union est une clause SQL utilisée pour joindre deux tables ou plus. Elle permet d'utiliser les données de toutes les tables jointes. En d'autres termes, les colonnes de toutes les tables sont affichées les unes à côté des autres, ce qui signifie que les données sont empilées horizontalement. UNION est un opérateur d'ensemble utilisé pour combiner les résultats de deux ou plusieurs instructions SELECT. Les données sont empilées verticalement. L'une des conditions d'utilisation de UNION est qu'il doit y avoir un nombre égal de colonnes dans toutes les instructions SELECT syndiquées. En outre, toutes les colonnes sélectionnées doivent être du même type de données. 14) Quelle est la différence entre UNION et UNION ALL ? Ce qu'ils ont en commun, c'est qu'il s'agit d'opérateurs d'ensemble. De plus, les deux opérateurs sont utilisés dans le même but : fusionner des données provenant de deux ou plusieurs instructions SELECT. Les exigences relatives au nombre de colonnes et à leur type de données sont également les mêmes. La différence réside dans le fait que UNION ne renvoie que des enregistrements uniques. En revanche, UNION ALL renvoie tous les enregistrements, y compris les doublons. En général, UNION ALL est plus rapide car il ne trie pas le résultat pour supprimer les doublons. La règle générale est d'utiliser UNION ALL par défaut. N'utilisez UNION que si vous avez besoin de résultats uniques ou si vous êtes absolument certain que votre requête ne renverra pas de données dupliquées. Vous pouvez en savoir plus sur leur syntaxe et leur utilisation dans cet article sur les différences entre UNION et UNION ALL. 15) Qu'est-ce qu'une sous-requête en SQL ? Une sous-requête est une requête écrite à l'intérieur d'une autre requête SQL. La "autre" requête est appelée requête principale, tandis qu'une sous-requête est parfois aussi appelée requête imbriquée. Les sous-requêtes peuvent être utilisées dans les instructions SELECT, INSERT, UPDATE et DELETE. Elles peuvent également être utilisées dans des clauses telles que FROM ou WHERE, ce qui est l'utilisation la plus courante. Voici un exemple. La table est productsElle contient des informations sur les noms, les quantités et les catégories de produits : idproduct_namequantityproduct_category 1Apple MacBook Air (2020) MGN63N/A Space Gray319Laptop 2Fairphone 4 128GB Green 5G208Mobile phone 3Apple iMac 24" (2021) 16GB/512GB Apple M1 with 8 core GPU Silver157Desktop 4HP 17-cp0971nd487Laptop 5Huawei P30 Pro - 128GB - Blue148Mobile phone 6Lenovo Legion T5 - AMD Ryzen 9 - 32 GB - 2TB HDD+SSD - Windows 10 Home PC514Desktop 7Toshiba Dynabook Satellite Pro E10-S-101 Notebook207Laptop 8Samsung Galaxy S23 5G - 256GB - Phantom Black56Mobile phone 9Intel Compleet PC | Intel Core i7-10700459Desktop Nous allons utiliser une sous-requête et afficher la quantité totale par catégorie de produit, mais uniquement pour les produits individuels dont la quantité est supérieure à la quantité moyenne de tous les produits. Voici la solution : SELECT product_category, SUM(quantity) AS product_quantity FROM products WHERE quantity > (SELECT AVG(quantity) FROM products) GROUP BY product_category; La requête sélectionne la catégorie de produits et additionne la quantité à l'aide de la fonction d'agrégation SUM(). Une condition dans WHERE stipule que seuls les produits individuels dont la quantité est supérieure à la moyenne seront inclus dans la somme. Nous utilisons la sous-requête et la fonction AVG() pour obtenir cette moyenne. La requête renvoie deux lignes : product_categoryproduct_quantity Laptop806 Desktop973 Une catégorie est manquante parce qu'elle ne répond pas aux critères de filtrage - les téléphones portables. Il existe différents types de sous-requêtes, telles que les requêtes scalaires, les requêtes à lignes multiples et les requêtes corrélées. Pour en savoir plus, consultez notre article sur les types de sous-requêtes. 16. écrire une requête pour retourner les vendeurs et leurs données de vente mensuelles au-dessus de leur moyenne de vente personnelle. Utilisez une sous-requête corrélée. Une sous-requête corrélée est un type de sous-requête qui utilise les valeurs de la requête externe. Elle est vérifiée une fois pour chaque ligne renvoyée par la requête externe, ce qui peut ralentir les performances. Cependant, la question insiste pour l'utiliser, alors voyons les données. La première table est salesperson: idfirst_namelast_name 1NinaLee 2CarolinaGreen 3MickJohnson L'autre table est sales: idsalesperson_idmonthly_salesperiod 111,200.472021_10 225,487.222021_10 33700.472021_10 4115,747.542021_11 5216,700.872021_11 5314,322.872021_11 619,745.552021_12 729,600.972021_12 836,749.582021_12 Votre tâche consiste à utiliser une sous-requête corrélée et à renvoyer le nom complet du vendeur, ses ventes mensuelles et les périodes au cours desquelles ses ventes sont supérieures à sa moyenne personnelle. Voici la solution : SELECT first_name, last_name, monthly_sales, period FROM salesperson sp JOIN sales s ON sp.id = s.salesperson_id WHERE monthly_sales > (SELECT AVG(monthly_sales) FROM sales WHERE salesperson_id = sp.id); La requête sélectionne toutes les colonnes nécessaires. Ces données proviennent des deux tables, nous les avons donc jointes. Vient maintenant la partie cruciale. Pour filtrer les données, nous utilisons la clause WHERE. Cette condition indique que la requête doit renvoyer toutes les données dont le chiffre d'affaires mensuel est supérieur au chiffre d'affaires moyen de chaque vendeur. Comment calculer ces ventes moyennes individuelles ? En utilisant la fonction AVG() dans la sous-requête que nous écrivons dans la clause WHERE. Voici le résultat : first_namelast_namemonthly_salesperiod NinaLee15,747.542021_11 CarolinaGreen16,700.872021_11 MickJohnson14,322.872021_11 NinaLee9,745.552021_12 Vous trouverez d'autres exemples dans cet article sur les sous-requêtes corrélées. 17 Qu'est-ce que Fonctions de fenêtrage en SQL ? Les fonctions de fenêtre SQL tirent leur nom du fait qu'elles s'appliquent à une fenêtre de données. Cette fenêtre est simplement un ensemble de lignes liées à la ligne courante. Les fonctions de fenêtre sont lancées par la clause OVER(). Une autre clause importante est PARTITION BY, qui définit les partitions de données dans un cadre de fenêtre. Lorsque cette clause est omise, la partition est l'ensemble de la table de résultats. Lorsque la clause PARTITION BY est utilisée, vous pouvez définir une ou plusieurs colonnes en fonction desquelles les données seront partitionnées. Vous pouvez considérer cette clause comme GROUP BY pour les fonctions de fenêtre. Une autre clause importante est ORDER BY, qui trie les données à l'intérieur de la fenêtre. Dans le contexte des fonctions de fenêtre, cette clause donne des instructions sur l'ordre dans lequel la fonction sera exécutée. Pour en savoir plus, consultez cet article sur les fonctions de fenêtre. 18. quelle est la différence entre Fonctions de fenêtrage et GROUP BY ? La seule similitude qu'elles partagent est que les fonctions GROUP BY et window peuvent être - et sont très souvent - utilisées avec les fonctions d'agrégation, et qu'elles travaillent toutes deux sur un ensemble de lignes. Toutefois, lorsque vous utilisez GROUP BY, les résultats sont affichés sous forme de groupes et vous ne pouvez pas voir les lignes individuelles qui forment le groupe. Les fonctions de fenêtre ne présentent pas ce type de problème. L'une de leurs caractéristiques est qu'elles ne réduisent pas les lignes individuelles lorsqu'elles affichent des données agrégées. Cela signifie qu'il est possible d'afficher simultanément des données agrégées et non agrégées. Les fonctions de fenêtre ne sont pas seulement utilisées pour l'agrégation de données, comme vous le verrez dans la question suivante. Mais si vous voulez en savoir plus sur le sujet actuel, nous avons un article qui explique les fonctions de fenêtre par rapport à GROUP BY. 19. quelle Fonctions de fenêtrage connaissez-vous ? Les fonctions de fenêtre SQL peuvent être généralement divisées en quatre catégories : Fonctions de classement Fonctions de distribution Fonctions analytiques Fonctions d'agrégation Les fonctions de classement sont les suivantes ROW_NUMBER() - Renvoie un numéro unique pour chaque ligne d'une partition ; les valeurs ex æquo ont des numéros de ligne différents. RANK() - Classe les données à l'intérieur d'une partition ; les valeurs liées ont le même rang et il y a un écart entre les valeurs liées (par exemple, 1, 2, 3, 3, 5). DENSE_RANK() - Classe les données à l'intérieur d'une partition ; les valeurs liées ont le même rang et il n'y a pas d'écart de classement (par exemple, 1, 2, 3, 3, 4). Les fonctions de distribution sont les suivantes PERCENT_RANK() - renvoie le rang relatif à l'intérieur d'une partition. CUME_DIST() - Renvoie la distribution cumulative au sein d'une partition. Les fonctions analytiques sont les suivantes LEAD() - Permet d'accéder aux valeurs d'une ligne suivante par rapport à la ligne actuelle. LAG() - Permet d'accéder aux valeurs d'une ligne précédente par rapport à la ligne actuelle. NTILE() - Divise les lignes d'une partition en groupes approximativement égaux. FIRST_VALUE() - Permet d'accéder aux valeurs de la première ligne d'une partition. LAST_VALUE() - Permet d'accéder aux valeurs de la dernière ligne d'une partition. NTH_VALUE() - Permet d'accéder à la n-ième ligne d'une partition. Enfin, les fonctions d'agrégation sont les suivantes AVG() - Renvoie une valeur moyenne pour les lignes d'une partition. COUNT() - Renvoie le nombre de valeurs dans les lignes d'une partition. MAX() - Renvoie la valeur maximale des lignes d'une partition. MIN() - Renvoie la valeur minimale des lignes d'une partition. SUM() - Renvoie la valeur de la somme des lignes d'une partition. Notre Cheat Sheet Fonctions de fenêtrage vous fournira de plus amples informations sur toutes ces fonctions. 20) Comment créer un classement en SQL ? La façon la plus simple de classer des données en SQL est d'utiliser l'une des trois fonctions de fenêtre de classement : ROW_NUMBER() RANK() DENSE_RANK() On vous donne cet ensemble de données nommé album_sales avec les données suivantes : idartistalbumcopies_sold 1EaglesHotel California42,000,000 2Led ZeppelinLed Zeppelin IV37,000,000 3Shania TwainCome On Over40,000,000 4Fleetwood MacRumours40,000,000 5AC/DCBack in Black50,000,000 6Bee GeesSaturday Night Fever40,000,000 7Michael JacksonThriller70,000,000 8Pink FloydThe Dark Side of the Moon45,000,000 9Whitney HoustonThe Bodyguard45,000,000 10EaglesTheir Greatest Hits (1971-1975)44,000,000 Il s'agit des ventes des dix albums les plus vendus de l'histoire. Comme vous pouvez le constater, les albums ne sont pas classés. C'est ce que nous allons faire ici : les classer du plus vendu au moins vendu à l'aide de fonctions de fenêtre. Si vous utilisez ROW_NUMBER(), la requête ressemblera à ceci : SELECT ROW_NUMBER() OVER (ORDER BY copies_sold DESC) AS rank, artist, album, copies_sold FROM album_sales; La syntaxe est simple. Tout d'abord, vous choisissez la fonction de fenêtre. Ensuite, vous utilisez la clause obligatoire OVER() qui signale qu'il s'agit d'une fonction de fenêtre. Dans ORDER BY, vous triez les données par ordre décroissant. Cela signifie que les numéros de ligne seront attribués en fonction du nombre d'exemplaires vendus, du plus élevé au plus bas. Bien entendu, listez toutes les autres colonnes dont vous avez besoin et faites référence au tableau pour obtenir le même résultat : rankartistalbumcopies_sold 1Michael JacksonThriller70,000,000 2AC/DCBack in Black50,000,000 3Whitney HoustonThe Bodyguard45,000,000 4Pink FloydThe Dark Side of the Moon45,000,000 5EaglesTheir Greatest Hits (1971-1975)44,000,000 6EaglesHotel California42,000,000 7Shania TwainCome On Over40,000,000 8Fleetwood MacRumours40,000,000 9Bee GeesSaturday Night Fever40,000,000 10Led ZeppelinLed Zeppelin IV37,000,000 Comme vous pouvez le voir, les albums sont classés de un à dix. Deux albums se sont vendus à 45 millions d'exemplaires. Cependant, ils sont classés différemment (troisième et quatrième) selon des critères aléatoires. Il en va de même pour trois albums vendus à 40 millions d'exemplaires. Si vous utilisez RANK(), la syntaxe est la même, sauf que vous utilisez une fonction de fenêtre différente : SELECT RANK() OVER (ORDER BY copies_sold DESC) AS rank, artist, album, copies_sold FROM album_sales; Cependant, le résultat est différent : rankartistalbumcopies_sold 1Michael JacksonThriller70,000,000 2AC/DCBack in Black50,000,000 3Whitney HoustonThe Bodyguard45,000,000 3Pink FloydThe Dark Side of the Moon45,000,000 5EaglesTheir Greatest Hits (1971-1975)44,000,000 6EaglesHotel California42,000,000 7Shania TwainCome On Over40,000,000 7Fleetwood MacRumours40,000,000 7Bee GeesSaturday Night Fever40,000,000 10Led ZeppelinLed Zeppelin IV37,000,000 Vous pouvez voir que les albums ex-aequo sont classés troisièmes (deux fois). L'album suivant qui n'est pas à égalité est classé cinquième. Il en va de même pour les albums classés en septième position. Voyons ce qui se passe si nous utilisons DENSE_RANK(): SELECT DENSE_RANK() OVER (ORDER BY copies_sold DESC) AS rank, artist, album, copies_sold FROM album_sales; Voici le résultat : rankartistalbumcopies_sold 1Michael JacksonThriller70,000,000 2AC/DCBack in Black50,000,000 3Whitney HoustonThe Bodyguard45,000,000 3Pink FloydThe Dark Side of the Moon45,000,000 4EaglesTheir Greatest Hits (1971-1975)44,000,000 5EaglesHotel California42,000,000 6Shania TwainCome On Over40,000,000 6Fleetwood MacRumours40,000,000 6Bee GeesSaturday Night Fever40,000,000 7Led ZeppelinLed Zeppelin IV37,000,000 Les premiers albums ex aequo sont classés troisièmes, ce qui est identique au résultat précédent. Mais la différence est que le prochain album non ex æquo est classé quatrième - ce qui signifie que le classement n'est pas sauté. Les trois autres albums ex aequo sont maintenant classés sixième, et non septième comme auparavant. De même, le rang le plus élevé est le septième et non le dixième. Comme vous pouvez le constater, chaque méthode donne des résultats différents. Vous devez utiliser celle qui convient le mieux à vos données et à ce que vous souhaitez obtenir avec le classement. Pour en savoir plus, lisez l'article sur le classement des lignes en SQL. 21) Quelle est la différence entre RANK() et DENSE_RANK() ? Nous avons déjà abordé la différence dans la question précédente. Vous l'avez vue dans un exemple pratique, et maintenant formulons-la pour répondre à cette question. RANK() attribue le même rang aux lignes ayant les mêmes valeurs. Lorsqu'il arrive à la ligne suivante non liée, il saute le rang du nombre de rangs liés. DENSE_RANK() attribue également le même rang aux valeurs liées. Cependant, le rang n'est pas ignoré lorsqu'il atteint la ligne suivante non liée. En d'autres termes, DENSE_RANK() classe les données de manière séquentielle. Plus de détails sont expliqués dans cet article sur les différences entre RANK() et DENSE_RANK(). 22. trouver les n premières lignes en SQL à l'aide d'une fonction Window et d'un CTE. Il s'agit d'une question courante qui peut être résolue de plusieurs façons. Nous utiliserons la fonction window dans un CTE pour obtenir le résultat souhaité. Les données disponibles sont stockées dans la table salary table : idfirst_namelast_namesalarydepartment 1TimThompson10,524.74Sales 2MartinaHrabal7,895.14Accounting 3SusanTruman15,478.69Sales 4CiroConte8,794.41Accounting 5JorgeDe Lucia7,489.15Sales 6CarmenLopez10,479.15Accounting 7CatherineMolnar8,794.89Sales 8RichardBuchanan12,487.69Accounting 9MarkWong9,784.19Sales 10SilviaKarelias9,748.64Accounting Il s'agit ici de retourner les trois employés les mieux payés dans chaque département, avec leur salaire et leur département. L'approche est la suivante : WITH ranking AS ( SELECT first_name, last_name, salary, department, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank FROM salary ) SELECT * FROM ranking WHERE salary_rank <= 3 ORDER BY department, salary_rank; La première partie du code est une expression de table commune, ou CTE. Elle est lancée à l'aide du mot-clé WITH. L'ETC s'appelle ranking. Après le mot-clé AS, nous écrivons la définition de l'ETC sous la forme d'une déclaration SELECT entre parenthèses. Après avoir sélectionné toutes les colonnes nécessaires vient ranking, nous utilisons la fonction DENSE_RANK(). Vous pouvez utiliser n'importe quelle autre fonction de fenêtre de classement si vous le souhaitez. La syntaxe est familière. Pour obtenir les classements par département, nous devons partitionner les données en fonction de cette colonne. Nous voulons également classer les salaires du plus élevé au plus bas. En d'autres termes, les données de la partition doivent être classées par salaire dans l'ordre décroissant. La deuxième instruction SELECT (c'est-à-dire la requête externe) sélectionne toutes les colonnes de l'ETC et définit la condition dans la clause WHERE pour filtrer uniquement les trois premiers salaires par département. Enfin, le résultat est trié par département et par rang de salaire. Voici le résultat : first_namelast_namesalarydepartmentsalary_rank RichardBuchanan12,487.69Accounting1 CarmenLopez10,479.15Accounting2 SilviaKarelias9,748.64Accounting3 SusanTruman15,478.69Sales1 TimThompson10,524.74Sales2 MarkWong9,784.19Sales3 23. calculer la différence entre deux lignes (Delta) à l'aide d'un CTE. Fonctions de fenêtrage Ce problème est résolu de la manière la plus élégante en utilisant la fonction de fenêtre LAG(). N'oubliez pas qu'il s'agit d'une fonction qui accède à la valeur de la ligne précédente. Les données de l'exemple se trouvent dans le tableau revenue: idactual_revenueperiod 18,748,441.222022_07 210,487,444.592022_08 37,481,457.152022_09 47,497,441.892022_10 58,697,415.362022_11 612,497,441.562022_12 Vous devez indiquer les recettes réelles, la période et la différence mensuelle (delta) entre les recettes réelles et celles du mois précédent. Voici comment procéder. SELECT actual_revenue, actual_revenue - LAG(actual_revenue) OVER (ORDER BY period ASC) AS monthly_revenue_change, period FROM revenue ORDER BY period; Un delta est calculé en soustrayant le mois précédent du mois réel. C'est exactement ce que fait cette requête ! Pour obtenir les revenus du mois précédent, la fonction LAG() est très utile. La colonne actual_revenue est l'argument de la fonction, puisque nous voulons accéder aux données de revenus de la ligne précédente. Comme pour toute fonction window, il existe une clause OVER(). Dans cette clause, nous avons trié les données par période de manière ascendante, car il est logique de calculer le delta de manière chronologique. Voici le résultat de la requête : actual_revenuemonthly_revenue_changeperiod 8,748,441.22NULL2022_07 10,487,444.591,739,003.372022_08 7,481,457.15-3,005,987.442022_09 7,497,441.8915,984.742022_10 8,697,415.361,199,973.472022_11 12,497,441.563,800,026.202022_12 Le premier résultat indique que la variation des revenus est NULL. C'est normal car il n'y a pas de mois antérieur à partir duquel on peut faire des déductions. En 2022_08, il y a eu une augmentation des recettes de 1 739 003,37 = recettes du mois en cours - recettes du mois précédent = 10 487 444,59 - 8 748 441,22. La même logique s'applique à tous les autres résultats. Vous trouverez des exemples similaires dans l'article sur le calcul de la différence entre deux lignes en SQL. 24. utiliser Fonctions de fenêtrage pour calculer un total courant Un total courant ou cumulatif est la somme d'une séquence de nombres. Le total courant est mis à jour chaque fois qu'une nouvelle valeur est ajoutée à la séquence. Pensez aux revenus mensuels : le revenu total du mois en cours comprendra la somme des revenus du mois en cours et de tous les mois précédents. La fonction de fenêtre qui convient parfaitement au calcul d'un total courant (somme cumulative) est SUM(). Montrons l'approche sur les mêmes données que dans la question précédente. L'objectif est de calculer le revenu cumulé pour tous les mois disponibles en 2022. Voici la solution : SELECT actual_revenue, SUM(actual_revenue) OVER (ORDER BY period ASC) AS cumulative_revenue, period FROM revenue; La somme cumulée est la somme des revenus du mois en cours et la somme des revenus de tous les mois précédents. La fonction de fenêtre SUM() applique cette logique. L'argument de la fonction est le revenu réel, car c'est ce que nous additionnons. Pour qu'elle additionne tous les revenus précédents et le revenu actuel, il faut trier les données par ordre croissant de période. Là encore, il est logique de calculer un total cumulé du mois le plus ancien au mois le plus récent. Voici ce que le code renvoie : actual_revenuecumulative_revenueperiod 8,748,441.228,748,441.222022_07 10,487,444.5919,235,885.812022_08 7,481,457.1526,717,342.962022_09 7,497,441.8934,214,784.852022_10 8,697,415.3642,912,200.212022_11 12,497,441.5655,409,641.772022_12 Le revenu cumulé de la première ligne est identique au revenu réel. Pour la deuxième ligne, le cumul est de 19 235 885,81 = 8 748 441,22 + 10 487 444,59. En septembre, le cumul est de 26 717 342,96 = 8 748 441,22 + 10 487 444,59 + 7 481 457,15. La même logique s'applique au reste du tableau. Pour en savoir plus sur le total courant et son calcul, cliquez ici. 25. trouver une moyenne mobile en utilisant Fonctions de fenêtrage Une moyenne mobile est utilisée lors de l'analyse d'une série. Vous pouvez la trouver sous d'autres noms, tels que moyenne mobile, moyenne mobile ou moyenne mobile. Il s'agit d'une moyenne de la valeur actuelle et du nombre défini de valeurs précédentes. Par exemple, une moyenne mobile sur 7 jours est la moyenne du jour en cours et des six jours précédents. Pour vous montrer comment la calculer, nous utiliserons la fonction eur_usd_rate tableau : idexchange_ratedate 11.06662022-12-30 21.06832023-01-02 31.05452023-01-03 41.05992023-01-04 51.06012023-01-05 61.05002023-01-06 61.06962023-01-09 71.07232023-01-10 81.07472023-01-11 91.07722023-01-12 101.08142023-01-13 Nous calculons la moyenne mobile à 3 jours de la manière suivante : SELECT exchange_rate, AVG(exchange_rate) OVER (ORDER BY date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS eur_usd_moving_average, date FROM eur_usd_rate; Nous utilisons la fonction de fenêtre AVG() sur la colonne exchange_rate. Dans la clause OVER(), les données sont triées par date et par ordre croissant. Voici maintenant la partie la plus importante ! Rappelez-vous que nous avons besoin d'une moyenne mobile sur 3 jours impliquant la ligne actuelle et les deux lignes précédentes. Nous le spécifions dans la clause BETWEEN: nous demandons à la fonction d'inclure les deux lignes précédentes et la ligne actuelle. Jetons un coup d'œil au résultat : exchange_rateeur_usd_moving_averagedate 1.06661.06662022-12-30 1.06831.06752023-01-02 1.05451.06312023-01-03 1.05991.06092023-01-04 1.06011.05822023-01-05 1.05001.05672023-01-06 1.06961.05992023-01-09 1.07231.06402023-01-10 1.07471.07222023-01-11 1.07721.07472023-01-12 1.08141.07782023-01-13 La moyenne mobile de la première date est identique au taux de change car : 1.0666/1 = 1.0666. Pour le 2023-01-02, elle se calcule ainsi : (1,0666 + 1,0683)/2 = 1,0675. En 2023-01-03, nous aurons enfin trois dates : (1,0666 + 1,0683 + 1,0545)/3 = 1,0631. Cette logique s'applique à toutes les autres dates. D'autres exemples peuvent être trouvés dans cet article sur le calcul des moyennes mobiles en SQL. 26) Quelle est la différence entre ROWS et RANGE ? ROWS et RANGE sont des clauses utilisées pour définir un cadre de fenêtre. Elles limitent la plage de données utilisée dans une fonction de fenêtre à l'intérieur d'une partition. La clause ROWS limite les lignes. Elle est utilisée pour spécifier un nombre fixe de lignes précédant et suivant la ligne actuelle. La valeur des lignes n'est pas prise en compte. La clause RANGE limite la plage de données de manière logique. En d'autres termes, elle limite les données en examinant les valeurs des lignes précédentes et suivantes par rapport à la ligne actuelle. Elle ne tient pas compte du nombre de lignes. Comment les utiliser en pratique ? Lisez notre article sur ROWS et RANGE pour plus de détails. 27) Utiliser une requête récursive pour trouver tous les employés d'un responsable donné. Une requête récursive est un type spécial d'ETC qui se réfère à lui-même jusqu'à ce qu'il atteigne la fin de la récursion. Elle est idéale pour interroger des données graphiques ou des structures hiérarchiques. L'organigramme de l'entreprise, présenté dans le tableau suivant, est un exemple de ce type de structure. company_organization tableau : employee_idfirst_namelast_namemanager_id 5529JackSimmons5125 5238MariaPopovich5329 5329DanJacobsson5125 5009SimoneGudbois5329 5125AlbertKochNULL 5500JackieCarlin5529 5118SteveNicks5952 5012BonniePresley5952 5952HarryRaitt5529 5444SeanElsam5329 Ce tableau présente tous les employés et l'identifiant de leur supérieur direct. Il s'agit ici d'utiliser la récursivité et de retourner tous les subordonnés directs et indirects de Jack Simmons. Nous ajouterons également une colonne qui pourra être utilisée pour distinguer les différents niveaux d'organisation. Voici le code : WITH RECURSIVE subordinates AS ( SELECT employee_id, first_name, last_name, manager_id, 0 AS level FROM company_organization WHERE employee_id= 5529 UNION ALL SELECT co.employee_id, co.first_name, co.last_name, co.manager_id, level + 1 FROM company_organization co JOIN subordinates s ON co.manager_id = s.employee_id ) SELECT s.employee_id, s.first_name AS employee_first_name, s.last_name AS employee_last_name, co.employee_id AS direct_manager_id, co.first_name AS direct_manager_first_name, co.last_name AS direct_manager_last_name, s.level FROM subordinates s JOIN company_organization co ON s.manager_id = co.employee_id ORDER BY level; Nous démarrons la récursivité en utilisant WITH RECURSIVE(si vous travaillez avec MS SQL Server, utilisez uniquement WITH). Le premier SELECT d'un CTE est appelé anchor member (membre d'ancrage). Nous y référençons l'ensemble de données et sélectionnons toutes les colonnes nécessaires. Nous créons également une nouvelle colonne avec la valeur zéro et filtrons les données dans la clause WHERE. Pourquoi utiliser cette condition exacte dans WHERE? Parce que l'ID de l'employé Jack Simmons est 5529 et que nous voulons l'afficher ainsi que ses subordonnés. Vient ensuite la clause UNION ALL, qui combine les résultats de la requête d'ancrage et de la requête récursive, c'est-à-dire la deuxième déclaration SELECT. Nous voulons que la récursivité aille jusqu'au bout de l'organigramme. Dans la requête récursive, nous joignons l'ETC à la table company_organization table. Nous énumérons à nouveau toutes les colonnes nécessaires de cette dernière table. De plus, nous voulons ajouter un niveau organisationnel à chaque récursion. Enfin, nous en arrivons à la requête qui utilise l'ETC. Cette requête sert à obtenir des données à la fois de l'ETC et de la table. company_organization table. Nous utilisons le CTE pour afficher les données relatives aux employés. L'autre table est utilisée pour afficher les informations relatives au responsable direct. L'exécution du code permet d'obtenir ce résultat : employee_idemployee_first_nameemployee_last_namedirect_manager_iddirect_manager_first_namedirect_manager_last_namelevel 5529JackSimmons5125AlbertKoch0 5952HarryRaitt5529JackSimmons1 5500JackieCarlin5529JackSimmons1 5012BonniePresley5952HarryRaitt2 5118SteveNicks5952HarryRaitt2 Le tableau ci-dessus indique que le responsable direct de Jack Simmons est Albert Koch. Les subordonnés directs de Simmons sont Harry Raitt et Jackie Carlin. Les subordonnés indirects sont Bonnie Presley et Steve Nicks. Leur supérieur direct est Harry Raitt. Vous trouverez d'autres variantes de cette tâche dans l'article sur les CTE récursifs. Vous pouvez en apprendre davantage sur les fonctions de fenêtre dans cet article consacré aux questions d'entretien sur les fonctions de fenêtre SQL. Êtes-vous prêt à répondre aux questions d'entretien d'embauche en SQL ? La rédaction de ce guide n'a pas été facile. Mais cela s'avère payant lorsque l'on pense à faciliter votre entretien d'embauche en SQL avancé. Bien entendu, il ne s'agit pas de toutes les questions qui pourraient vous être posées lors de l'entretien. Cependant, nous pensons que cette sélection vous fournira une base solide des concepts SQL avancés les plus importants. Ce guide est également suffisamment court pour que vous puissiez le parcourir rapidement avant l'entretien et vous rafraîchir la mémoire. Pour plus de rafraîchissements sur les sujets SQL avancés, essayez notre cours Fonctions de fenêtrage cours ou SQL avancé piste. Tags: sql emplois et carrières