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

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.

id	first_name	last_name	national_team_id	games_played
1	Gianfranco	Zola	1	35
2	Virgil	van Dijk	2	53
3	Marcus	Rashford	3	51
4	Kylian	Mbappé	5	66
5	Phil	Foden	3	22
6	Frenkie	de Jong	2	22
7	Mario	Balotelli	1	36
8	Erling	Haaland	6	23

La seconde est national_team.

id	country
1	Italy
2	Netherlands
3	England
4	Croatia

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 :

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36

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.

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36

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 :

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
4	Kylian	Mbappé	5	NULL	66
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36
8	Erling	Haaland	6	NULL	23

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 :

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36
NULL	NULL	NULL	NULL	Croatia	NULL

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:

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
4	Kylian	Mbappé	5	NULL	66
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36
8	Erling	Haaland	6	NULL	23
NULL	NULL	NULL	NULL	Croatia	NULL

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.

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Italy	53
3	Marcus	Rashford	3	Italy	51
4	Kylian	Mbappé	5	Italy	66
5	Phil	Foden	3	Italy	22
6	Frenkie	de Jong	2	Italy	22
7	Mario	Balotelli	1	Italy	36
8	Erling	Haaland	6	Italy	23
1	Gianfranco	Zola	1	Netherlands	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	Netherlands	51
4	Kylian	Mbappé	5	Netherlands	66
5	Phil	Foden	3	Netherlands	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Netherlands	36
8	Erling	Haaland	6	Netherlands	23
1	Gianfranco	Zola	1	England	35
2	Virgil	van Dijk	2	England	53
3	Marcus	Rashford	3	England	51
4	Kylian	Mbappé	5	England	66
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	England	22
7	Mario	Balotelli	1	England	36
8	Erling	Haaland	6	England	23
1	Gianfranco	Zola	1	Croatia	35
2	Virgil	van Dijk	2	Croatia	53
3	Marcus	Rashford	3	Croatia	51
4	Kylian	Mbappé	5	Croatia	66
5	Phil	Foden	3	Croatia	22
6	Frenkie	de Jong	2	Croatia	22
7	Mario	Balotelli	1	Croatia	36
8	Erling	Haaland	6	Croatia	23

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 :

id	first_name	last_name	national_team_id	id_2	first_name_2	last_name_2	national_team_id_2
1	Gianfranco	Zola	1	7	Mario	Balotelli	1
2	Virgil	van Dijk	2	6	Frenkie	de Jong	2
3	Marcus	Rashford	3	5	Phil	Foden	3
5	Phil	Foden	3	3	Marcus	Rashford	3
6	Frenkie	de Jong	2	2	Virgil	van Dijk	2
7	Mario	Balotelli	1	1	Gianfranco	Zola	1

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 :

id	first_name	last_name
1	Steve	Bergman
2	Steve	Johnson
3	Steve	King

La seconde table est customerqui contient les données suivantes :

id	first_name	last_name
1	Ann	Coleman
2	Steve	Bergman
3	Steve	Young
4	Donna	Winter
5	Steve	King

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_name	last_name
Steve	Bergman
Steve	King

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 :

id	first_name	last_name	mobile_data_limit
1	Michael	Watson	5,000
2	Nicole	Gomez	10,000
3	Sam	Stone	8,000

Le deuxième tableau est data_usagequi indique la consommation mensuelle réelle de données de l'utilisateur en Mo :

id	mobile_user_id	data_used	period
1	1	4,987	2022_10
2	2	6,875	2022_10
3	3	12,547	2022_10
4	1	5,037	2022_11
5	2	11,111	2022_11
6	3	4,897	2022_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_name	last_name	mobile_data_limit	data_used	period
Sam	Stone	8,000	12,547	2022_10
Michael	Watson	5,000	5,037	2022_11
Nicole	Gomez	10,000	11,111	2022_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_id	loan_duration
100112	60
100205	60
100208	48
100333	120
100357	48
100398	120

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:

id	first_name	last_name	salary	department
1	Nicholas	Poirot	4,798.44	IT
2	Samantha	Wolf	5,419.24	IT
3	Stewart	Johnsons	5,419.24	IT
4	Jackie	Biden	8,474.54	Sales
5	Mark	Hamilton	10,574.84	Sales
6	Mariana	Costa	9,747.54	Sales
7	Paul	Stewart	3,498.12	Accounting
8	Mary	Rutte	4,187.23	Accounting
9	Thomas	Schwarz	3,748.55	Accounting

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 :

department	average_salary
Accounting	3,811.30
Sales	9,598.97
IT	5,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 :

department	average_salary
Accounting	3,811.30
IT	5,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:

id	first_name	last_name	start_date	termination_date
1	Valeria	Bogdanov	2022-10-11	NULL
2	Nicholas	Bertolucci	2022-04-07	2022-11-11
3	Mathilde	Bauman	2022-05-25	2022-10-01
4	Trevor	Trucks	2022-01-28	NULL
5	Maria	Szabo	2022-03-15	NULL

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_name	last_name	start_date	termination_date
Nicholas	Bertolucci	2022-04-07	2022-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 :

id	artist_name
1	Prince
2	Jimi Hendrix
3	Santana
4	Otis Redding
5	Lou Rawls

Voici les données song données :

id	artist_id	song_title
1	1	Purple Rain
2	2	Purple Haze
3	3	Europa
4	1	Cream
5	1	Bambi
6	1	Why You Wanna Treat Me So Bad?
7	2	Spanish Castle Magic
8	3	Taboo
9	3	Incident at Neshabur
10	3	Flor 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 :

id	artist_name	number_of_songs
1	Prince	4
2	Jimi Hendrix	2
3	Santana	4
4	Otis Redding	1
5	Lou Rawls	1

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 :

id	artist_name	number_of_songs
1	Prince	4
2	Jimi Hendrix	2
3	Santana	4
4	Otis Redding	0
5	Lou Rawls	0

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 :

id	product_name	quantity	product_category
1	Apple MacBook Air (2020) MGN63N/A Space Gray	319	Laptop
2	Fairphone 4 128GB Green 5G	208	Mobile phone
3	Apple iMac 24" (2021) 16GB/512GB Apple M1 with 8 core GPU Silver	157	Desktop
4	HP 17-cp0971nd	487	Laptop
5	Huawei P30 Pro - 128GB - Blue	148	Mobile phone
6	Lenovo Legion T5 - AMD Ryzen 9 - 32 GB - 2TB HDD+SSD - Windows 10 Home PC	514	Desktop
7	Toshiba Dynabook Satellite Pro E10-S-101 Notebook	207	Laptop
8	Samsung Galaxy S23 5G - 256GB - Phantom Black	56	Mobile phone
9	Intel Compleet PC | Intel Core i7-10700	459	Desktop

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_category	product_quantity
Laptop	806
Desktop	973

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:

id	first_name	last_name
1	Nina	Lee
2	Carolina	Green
3	Mick	Johnson

L'autre table est sales:

id	salesperson_id	monthly_sales	period
1	1	1,200.47	2021_10
2	2	5,487.22	2021_10
3	3	700.47	2021_10
4	1	15,747.54	2021_11
5	2	16,700.87	2021_11
5	3	14,322.87	2021_11
6	1	9,745.55	2021_12
7	2	9,600.97	2021_12
8	3	6,749.58	2021_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_name	last_name	monthly_sales	period
Nina	Lee	15,747.54	2021_11
Carolina	Green	16,700.87	2021_11
Mick	Johnson	14,322.87	2021_11
Nina	Lee	9,745.55	2021_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 :

id	artist	album	copies_sold
1	Eagles	Hotel California	42,000,000
2	Led Zeppelin	Led Zeppelin IV	37,000,000
3	Shania Twain	Come On Over	40,000,000
4	Fleetwood Mac	Rumours	40,000,000
5	AC/DC	Back in Black	50,000,000
6	Bee Gees	Saturday Night Fever	40,000,000
7	Michael Jackson	Thriller	70,000,000
8	Pink Floyd	The Dark Side of the Moon	45,000,000
9	Whitney Houston	The Bodyguard	45,000,000
10	Eagles	Their 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 :

rank	artist	album	copies_sold
1	Michael Jackson	Thriller	70,000,000
2	AC/DC	Back in Black	50,000,000
3	Whitney Houston	The Bodyguard	45,000,000
4	Pink Floyd	The Dark Side of the Moon	45,000,000
5	Eagles	Their Greatest Hits (1971-1975)	44,000,000
6	Eagles	Hotel California	42,000,000
7	Shania Twain	Come On Over	40,000,000
8	Fleetwood Mac	Rumours	40,000,000
9	Bee Gees	Saturday Night Fever	40,000,000
10	Led Zeppelin	Led Zeppelin IV	37,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 :

rank	artist	album	copies_sold
1	Michael Jackson	Thriller	70,000,000
2	AC/DC	Back in Black	50,000,000
3	Whitney Houston	The Bodyguard	45,000,000
3	Pink Floyd	The Dark Side of the Moon	45,000,000
5	Eagles	Their Greatest Hits (1971-1975)	44,000,000
6	Eagles	Hotel California	42,000,000
7	Shania Twain	Come On Over	40,000,000
7	Fleetwood Mac	Rumours	40,000,000
7	Bee Gees	Saturday Night Fever	40,000,000
10	Led Zeppelin	Led Zeppelin IV	37,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 :

rank	artist	album	copies_sold
1	Michael Jackson	Thriller	70,000,000
2	AC/DC	Back in Black	50,000,000
3	Whitney Houston	The Bodyguard	45,000,000
3	Pink Floyd	The Dark Side of the Moon	45,000,000
4	Eagles	Their Greatest Hits (1971-1975)	44,000,000
5	Eagles	Hotel California	42,000,000
6	Shania Twain	Come On Over	40,000,000
6	Fleetwood Mac	Rumours	40,000,000
6	Bee Gees	Saturday Night Fever	40,000,000
7	Led Zeppelin	Led Zeppelin IV	37,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 :

id	first_name	last_name	salary	department
1	Tim	Thompson	10,524.74	Sales
2	Martina	Hrabal	7,895.14	Accounting
3	Susan	Truman	15,478.69	Sales
4	Ciro	Conte	8,794.41	Accounting
5	Jorge	De Lucia	7,489.15	Sales
6	Carmen	Lopez	10,479.15	Accounting
7	Catherine	Molnar	8,794.89	Sales
8	Richard	Buchanan	12,487.69	Accounting
9	Mark	Wong	9,784.19	Sales
10	Silvia	Karelias	9,748.64	Accounting

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_name	last_name	salary	department	salary_rank
Richard	Buchanan	12,487.69	Accounting	1
Carmen	Lopez	10,479.15	Accounting	2
Silvia	Karelias	9,748.64	Accounting	3
Susan	Truman	15,478.69	Sales	1
Tim	Thompson	10,524.74	Sales	2
Mark	Wong	9,784.19	Sales	3

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:

id	actual_revenue	period
1	8,748,441.22	2022_07
2	10,487,444.59	2022_08
3	7,481,457.15	2022_09
4	7,497,441.89	2022_10
5	8,697,415.36	2022_11
6	12,497,441.56	2022_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_revenue	monthly_revenue_change	period
8,748,441.22	NULL	2022_07
10,487,444.59	1,739,003.37	2022_08
7,481,457.15	-3,005,987.44	2022_09
7,497,441.89	15,984.74	2022_10
8,697,415.36	1,199,973.47	2022_11
12,497,441.56	3,800,026.20	2022_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_revenue	cumulative_revenue	period
8,748,441.22	8,748,441.22	2022_07
10,487,444.59	19,235,885.81	2022_08
7,481,457.15	26,717,342.96	2022_09
7,497,441.89	34,214,784.85	2022_10
8,697,415.36	42,912,200.21	2022_11
12,497,441.56	55,409,641.77	2022_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 :

id	exchange_rate	date
1	1.0666	2022-12-30
2	1.0683	2023-01-02
3	1.0545	2023-01-03
4	1.0599	2023-01-04
5	1.0601	2023-01-05
6	1.0500	2023-01-06
6	1.0696	2023-01-09
7	1.0723	2023-01-10
8	1.0747	2023-01-11
9	1.0772	2023-01-12
10	1.0814	2023-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_rate	eur_usd_moving_average	date
1.0666	1.0666	2022-12-30
1.0683	1.0675	2023-01-02
1.0545	1.0631	2023-01-03
1.0599	1.0609	2023-01-04
1.0601	1.0582	2023-01-05
1.0500	1.0567	2023-01-06
1.0696	1.0599	2023-01-09
1.0723	1.0640	2023-01-10
1.0747	1.0722	2023-01-11
1.0772	1.0747	2023-01-12
1.0814	1.0778	2023-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_id	first_name	last_name	manager_id
5529	Jack	Simmons	5125
5238	Maria	Popovich	5329
5329	Dan	Jacobsson	5125
5009	Simone	Gudbois	5329
5125	Albert	Koch	NULL
5500	Jackie	Carlin	5529
5118	Steve	Nicks	5952
5012	Bonnie	Presley	5952
5952	Harry	Raitt	5529
5444	Sean	Elsam	5329

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_id	employee_first_name	employee_last_name	direct_manager_id	direct_manager_first_name	direct_manager_last_name	level
5529	Jack	Simmons	5125	Albert	Koch	0
5952	Harry	Raitt	5529	Jack	Simmons	1
5500	Jackie	Carlin	5529	Jack	Simmons	1
5012	Bonnie	Presley	5952	Harry	Raitt	2
5118	Steve	Nicks	5952	Harry	Raitt	2

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.