🦆 👨🏿‍⚕️ 🏍️ 5 questions SQL fréquemment posées par les scientifiques des données pour les entretiens d'embauche 🐆 🐦 🔁

Bien que la compilation de requêtes SQL ne soit pas la chose la plus intéressante pour les Data Scientists, une bonne compréhension de SQL est extrêmement importante pour quiconque souhaite réussir dans toute activité liée au traitement des données. Le point ici est que SQL n'est pas seulement SELECT, FROMet WHERE. Plus un spécialiste connaît de constructions SQL, plus il lui sera facile de créer des requêtes pour obtenir des bases de données tout ce dont il peut avoir besoin. L'auteur de l'article, dont nous publions la traduction aujourd'hui, dit qu'il vise à résoudre deux problèmes:

Explorer des mécanismes qui vont au-delà des connaissances SQL de base.
Prise en compte de plusieurs tâches pratiques pour travailler avec SQL.

Cet article couvre 5 questions SQL de Leetcode. Ils représentent des tâches pratiques souvent rencontrées lors des entretiens.

Question n ° 1: deuxième place en termes de salaire

Écrivez une requête SQL pour obtenir du tableau contenant les informations sur le salaire des employés ( Employee) une entrée contenant le deuxième salaire le plus élevé.

Par exemple, une telle requête exécutée pour le tableau ci-dessous devrait retourner 200. Si la table n'a pas une valeur inférieure au salaire le plus élevé, la demande doit retourner null.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

▍ Décision A: utiliser `IFNULL`et`OFFSET`

Voici les principaux mécanismes qui seront utilisés dans cette solution au problème:

IFNULL(expression, alt): cette fonction renvoie son argument expressions'il n'est pas égal null. Sinon, l'argument est renvoyé alt. Nous utiliserons cette fonction pour retourner nullsi la table ne contient pas la valeur souhaitée.
OFFSET: Cet opérateur est utilisé avec une expression ORDER BYpour supprimer les premières nlignes. Cela nous est utile parce que nous nous intéressons à la deuxième ligne du résultat (c'est-à-dire le deuxième plus gros salaire, dont les données figurent dans le tableau).

Voici une demande toute faite:

SELECT
    IFNULL(
        (SELECT DISTINCT Salary
        FROM Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1
        ), null) as SecondHighestSalary
FROM Employee
LIMIT 1

▍ Solution B: utiliser `MAX`

La requête ci-dessous utilise la fonction MAX. Ici, la valeur de salaire la plus élevée est sélectionnée, non égale au salaire maximum reçu dans l'ensemble du tableau. En conséquence, nous obtenons ce dont nous avons besoin - le deuxième plus gros salaire.

SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary != (SELECT MAX(salary) FROM Employee)

Question # 2: adresses e-mail en double

Écrivez une requête SQL qui détecte Persontoutes les adresses e-mail en double dans le tableau .

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

▍ Décision A: `COUNT`dans la sous-requête

Tout d'abord, nous créons une sous-requête dans laquelle la fréquence d'occurrence de chaque adresse dans le tableau est déterminée. Le résultat renvoyé par la sous-requête est ensuite filtré à l'aide de l'instruction WHERE count > 1. La requête renvoie plusieurs informations sur les adresses trouvées dans la table source.

SELECT Email
FROM (
    SELECT Email, count(Email) AS count
    FROM Person
    GROUP BY Email
) as email_count
WHERE count > 1

▍ Solution B: expression `HAVING`

HAVING: Il s'agit d'une expression qui vous permet d'utiliser une instruction WHEREavec une expression GROUP BY.

SELECT Email
FROM Person
GROUP BY Email
HAVING count(Email) > 1

Question n ° 3: hausse de la température

Écrivez une requête SQL qui trouve dans le tableau Weathertoutes les dates (identificateurs de date) où la température serait supérieure à la température aux dates qui la précèdent. C'est-à-dire que nous nous intéressons aux dates auxquelles la température «d'aujourd'hui» est plus élevée que celle «d'hier».

+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
|       1 |       2015-01-01 |               10 |
|       2 |       2015-01-02 |               25 |
|       3 |       2015-01-03 |               20 |
|       4 |       2015-01-04 |               30 |
+---------+------------------+------------------+

▍Solution: `DATEDIFF`

DATEDIFF: Cette fonction calcule la différence entre deux dates. Il est utilisé pour fournir une comparaison précise des températures «d'aujourd'hui» et «d'hier».

Si nous formulons la requête suivante en langage ordinaire, il s'avère qu'elle exprime l'idée suivante: nous devons choisir des identifiants tels que la température correspondant aux dates qu'ils représentent est supérieure à la température des dates «d'hier» par rapport à eux.

SELECT DISTINCT a.Id
FROM Weather a, Weather b
WHERE a.Temperature > b.Temperature
AND DATEDIFF(a.Recorddate, b.Recorddate) = 1

Question numéro 4: le salaire le plus élevé de l'unité

Le tableau Employeestocke des informations sur les employés de l'entreprise. Chaque enregistrement de ce tableau contient des informations sur l'identifiant ( Id) de l'employé, son nom ( Name), son salaire ( Salary) et la division de l'entreprise où il travaille ( Department).

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

Le tableau Departmentcontient des informations sur les services de l'entreprise.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Écrivez une requête SQL qui trouve dans chacun des départements des employés avec le salaire maximum. Par exemple, pour les tableaux ci-dessus, une requête similaire doit renvoyer les résultats représentés par le tableau suivant (l'ordre des lignes dans le tableau n'a pas d'importance):

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

▍Solution: équipe `IN`

La commande INvous permet de définir des WHEREconditions dans l'instruction qui correspondent à l'utilisation de plusieurs commandes OR. Par exemple, les deux constructions suivantes sont identiques:

WHERE country = ‘Canada’ OR country = ‘USA’
WHERE country IN (‘Canada’, ’USA’).

Ici, nous voulons obtenir un tableau contenant le nom du département ( Department), le nom de l'employé ( Employee) et son salaire ( Salary). Pour ce faire, nous créons une table qui contient des informations sur l'identifiant d'unité ( DepartmentID) et le salaire maximum pour cette unité. Ensuite, nous combinons les deux sur une table de conditions selon laquelle l'entrée dans la table résultante ne tombe que si DepartmentIDet Salarys'est déjà formée dans la table.

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM Employee
INNER JOIN Department ON Employee.DepartmentId = Department.Id
WHERE (DepartmentId , Salary) 
    IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
 )

Question n ° 5: transplanter des étudiants

Mary est enseignante au secondaire. Elle a une table seatqui stocke les noms des élèves et des informations sur leur place dans la classe. La valeur idde ce tableau est en constante augmentation. Mary veut échanger des étudiants voisins.

Voici un tableau du placement initial des étudiants:

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+

Voici ce qui devrait arriver après la transplantation d'étudiants voisins:

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+

Écrivez une demande qui permettra à l'enseignant de résoudre le problème ci-dessus.

Veuillez noter que si le nombre d'étudiants est impair, vous n'avez pas besoin de transférer le dernier étudiant n'importe où.

▍Solution: à l'aide d'un opérateur `WHEN`

Une construction SQL CASE WHEN THENpeut être considérée comme un opérateur ifdans la programmation.

Dans notre cas, le premier opérateur est WHENutilisé pour vérifier si l'identifiant impair est affecté à la dernière ligne du tableau. Si c'est le cas, la ligne n'est pas sujette à changement. Le deuxième opérateur WHENest responsable d'ajouter 1 à chaque identifiant impair (par exemple, 1, 3, 5 tours en 2, 4, 6) et de soustraire 1 de chaque identifiant pair (2, 4, 6 devient 1, 3, 5).

SELECT 
    CASE 
        WHEN((SELECT MAX(id) FROM seat)%2 = 1) AND id = (SELECT MAX(id) FROM seat) THEN id
        WHEN id%2 = 1 THEN id + 1
        ELSE id - 1
    END AS id, student
FROM seat
ORDER BY id

Sommaire

Nous avons examiné plusieurs tâches SQL, discutant en cours de route quelques outils avancés pouvant être utilisés pour compiler des requêtes SQL. Nous espérons que ce que vous avez appris aujourd'hui vous sera utile lors des entretiens avec SQL et se révélera utile dans le travail quotidien.

PS Sur notre marché, il y a une image Docker avec SQL Server Express, qui est installée en un clic. Vous pouvez vérifier le fonctionnement des conteneurs sur VPS. Tous les nouveaux clients bénéficient de 3 jours gratuits pour les tests.

Chers lecteurs! Que pouvez-vous conseiller à ceux qui veulent maîtriser l'art de créer des requêtes SQL?

5 questions SQL fréquemment posées par les scientifiques des données pour les entretiens d'embauche

Question n ° 1: deuxième place en termes de salaire

▍ Décision A: utiliser IFNULLetOFFSET

▍ Solution B: utiliser MAX

Question # 2: adresses e-mail en double

▍ Décision A: COUNTdans la sous-requête

▍ Solution B: expression HAVING