Retour à la liste des articles Articles
36 minutes de lecture

27 questions d'entretien pour SQL avancé avec réponses

Table des matiè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.