Défis Pratiques en SQL : Résolution de Requêtes Courantes

  1. Grands Pays (595)

Description

Vous disposez d'une table nommée World avec les colonnes suivantes :

name continent area population gdp
Afghanistan Asia 652230 25500100 20343000
Albania Europe 28748 2831741 12960000
Algeria Africa 2381741 37100000 188681000
Andorra Europe 468 78115 3712000
Angola Africa 1246700 20609294 100990000

L'objectif est de sélectionner le nom (name), la population (population) et la superficie (area) de tous les pays considérés comme "grands". Un pays est grand s'il a une superficie supérieure à 3 000 000 km² OU une population supérieure à 25 000 000 habitants.

Résultat attendu :

name population area
Afghanistan 25500100 652230
Algeria 37100000 2381741

Solution

Pour cette requête, nous filtrons la table World en utilisant une clause WHERE combinant les deux critères avec l'opérateur logique OR.

SELECT
    nom,
    nombre_habitants,
    superficie_km2
FROM
    Pays
WHERE
    superficie_km2 > 3000000
    OR nombre_habitants > 25000000;

Schéma SQL

DROP TABLE IF EXISTS Pays;
CREATE TABLE Pays ( nom VARCHAR(255), continent VARCHAR(255), superficie_km2 INT, nombre_habitants INT, pib INT );
INSERT INTO Pays ( nom, continent, superficie_km2, nombre_habitants, pib )
VALUES
    ( 'Afghanistan', 'Asia', '652230', '25500100', '203430000' ),
    ( 'Albania', 'Europe', '28748', '2831741', '129600000' ),
    ( 'Algeria', 'Africa', '2381741', '37100000', '1886810000' ),
    ( 'Andorra', 'Europe', '468', '78115', '37120000' ),
    ( 'Angola', 'Africa', '1246700', '20609294', '1009900000' );
  1. Inverser les Sexes (627)

Description

On vous donne une table Employes contenant les informations suivantes :

id nom sexe salaire
1 A m 2500
2 B f 1500
3 C m 5500
4 D f 500

Mettez à jour le champ sexe de manière à inverser 'm' et 'f' pour tous les enregistrements en une seule requête SQL.

Résultat attendu :

id nom sexe salaire
1 A f 2500
2 B m 1500
3 C f 5500
4 D m 500

Solution

La manière la plus idiomatique d'inverser des valeurs binaires (comme 'm' et 'f') est d'utiliser une instruction UPDATE avec une expression CASE. Cela permet d'assigner la valeur 'f' si le sexe est 'm', et 'm' si le sexe est 'f' (ou toute autre valeur si non 'm', ce qui est suffisant ici).

UPDATE Employes
SET sexe = CASE
    WHEN sexe = 'm' THEN 'f'
    ELSE 'm'
END;

Schéma SQL

DROP TABLE IF EXISTS Employes;
CREATE TABLE Employes ( id INT, nom VARCHAR(100), sexe CHAR(1), salaire INT );
INSERT INTO Employes ( id, nom, sexe, salaire )
VALUES
    ( '1', 'A', 'm', '2500' ),
    ( '2', 'B', 'f', '1500' ),
    ( '3', 'C', 'm', '5500' ),
    ( '4', 'D', 'f', '500' );
  1. Films Non Ennuyeux (620)

Description

Vous avez une table Films avec les informations suivantes :

id film description evaluation
1 War great 3D 8.9
2 Science fiction 8.5
3 irish boring 6.2
4 Ice song Fantacy 8.6
5 House card Interesting 9.1

Trouvez tous les films ayant un identifiant (id) impair et dont la description n'est PAS 'boring'. Le résultat doit être trié par l'évaluation (evaluation) en ordre décroissant.

Résultat attendu :

id film description evaluation
5 House card Interesting 9.1
1 War great 3D 8.9

Solution

Nous utiliserons une clause WHERE pour appliquer les conditions sur l'id et la description, puis une clause ORDER BY pour trier les résultats. Le modulo % est utilisé pour vérifier si l'id est impair.

SELECT
    identifiant_film AS id,
    titre_film AS movie,
    resume_film AS description,
    note_critique AS rating
FROM
    Films
WHERE
    MOD(identifiant_film, 2) = 1
    AND resume_film != 'boring'
ORDER BY
    note_critique DESC;

Schéma SQL

DROP TABLE IF EXISTS Films;
CREATE TABLE Films ( identifiant_film INT, titre_film VARCHAR(255), resume_film VARCHAR(255), note_critique FLOAT(2,1) );
INSERT INTO Films ( identifiant_film, titre_film, resume_film, note_critique )
VALUES
    ( 1, 'War', 'great 3D', 8.9 ),
    ( 2, 'Science', 'fiction', 8.5 ),
    ( 3, 'irish', 'boring', 6.2 ),
    ( 4, 'Ice song', 'Fantacy', 8.6 ),
    ( 5, 'House card', 'Interesting', 9.1 );
  1. Cours avec Plus de 5 Étudiants (596)

Description

On vous fournit une table Inscriptions qui associe des étudiants à des cours :

etudiant cours
A Math
B English
C Math
D Biology
E Math
F Computer
G Math
H Math
I Math

Votre tâche est de trouver tous les noms de cours (cours) qui comptent au moins cinq étudiants inscrits (distincts).

Résultat attendu :

cours
Math

Solution

Pour regrouper les enregistrements par cours et compter les étudiants, nous utilisons GROUP BY avec la colonne cours. Ensuite, la clause HAVING permet de filtrer ces groupes. Nous utilisons COUNT(DISTINCT nom_etudiant) pour s'assurer de ne compter que les étudiants uniques.

SELECT
    nom_cours
FROM
    Inscriptions
GROUP BY
    nom_cours
HAVING
    COUNT(DISTINCT nom_etudiant) >= 5;

Schéma SQL

DROP TABLE IF EXISTS Inscriptions;
CREATE TABLE Inscriptions ( nom_etudiant VARCHAR(255), nom_cours VARCHAR(255) );
INSERT INTO Inscriptions ( nom_etudiant, nom_cours )
VALUES
    ( 'A', 'Math' ),
    ( 'B', 'English' ),
    ( 'C', 'Math' ),
    ( 'D', 'Biology' ),
    ( 'E', 'Math' ),
    ( 'F', 'Computer' ),
    ( 'G', 'Math' ),
    ( 'H', 'Math' ),
    ( 'I', 'Math' );
  1. E-mails Dupliqués (182)

Description

Voici la table Utilisateurs, qui contient des identifiants et des adresses e-mail :

id email
1 a@b.com
2 c@d.com
3 a@b.com

Identifiez toutes les adresses e-mail qui apparaissent plus d'une fois dans la table.

Résultat attendu :

email
a@b.com

Solution

Comme pour la question précédente, l'approche consiste à regrouper les données par la colonne email et à utiliser la fonction d'agrégation COUNT(*) pour déterminer si un e-mail apparaît plusieurs fois. La clause HAVING filtre ensuite ces groupes.

SELECT
    adresse_email
FROM
    Utilisateurs
GROUP BY
    adresse_email
HAVING
    COUNT(*) >= 2;

Schéma SQL

DROP TABLE IF EXISTS Utilisateurs;
CREATE TABLE Utilisateurs ( id INT, adresse_email VARCHAR(255) );
INSERT INTO Utilisateurs ( id, adresse_email )
VALUES
    ( 1, 'a@b.com' ),
    ( 2, 'c@d.com' ),
    ( 3, 'a@b.com' );
  1. Suppression d'E-mails Dupliqués (196)

Description

En reprennant la table Utilisateurs :

id email
1 john@example.com
2 bob@example.com
3 john@example.com

Supprimez toutes les lignes où les adresses e-mail sont dupliquées, en ne conservant qu'une seule instance pour chaque e-mail unique (celle avec le plus petit id).

Résultat attendu :

id email
1 john@example.com
2 bob@example.com

Solution

Nous pouvons effectuer une suppression en utilisant une auto-jointure. L'idée est de joindre la table Utilisateurs avec elle-même, en cherchant les paires d'enregistrements ayant la même adresse e-mail mais des identifiants différents, puis de supprimer l'enregistrement avec le plus grand id.

DELETE p1
FROM
    Utilisateurs p1
INNER JOIN
    Utilisateurs p2 ON p1.adresse_email = p2.adresse_email AND p1.id_utilisateur > p2.id_utilisateur;

Schéma SQL

Identique à la question 182.

  1. Combiner Deux Tables (175)

Description

On vous donne deux tables, Personnes et Adresses :

Table Personnes :

Nom Colonne Type
PersonneId int
Prenom varchar
NomFamille varchar

PersonneId est la clé primaire.

Table Adresses :

Nom Colonne Type
AdresseId int
PersonneId int
Ville varchar
Etat varchar

AdresseId est la clé primaire.

Sélectionnez Prenom, NomFamille, Ville et Etat pour chaque personne. Le résultat doit inclure toutes les personnes, même si elles n'ont pas d'adresse enregistrée.

Solution

Pour inclure toutes les personnes, y compris celles sans correspondance dans la table Adresses, une jointure externe gauche (LEFT JOIN) est nécessaire. La table Personnes doit être placée à gauche de la jointure.

SELECT
    P.prenom_personne,
    P.nom_famille_personne,
    A.ville_adresse,
    A.etat_adresse
FROM
    Personnes P
LEFT JOIN
    Adresses A ON P.id_personne = A.id_personne;

Schéma SQL

DROP TABLE IF EXISTS Personnes;
CREATE TABLE Personnes ( id_personne INT, prenom_personne VARCHAR(255), nom_famille_personne VARCHAR(255) );
DROP TABLE IF EXISTS Adresses;
CREATE TABLE Adresses ( id_adresse INT, id_personne INT, ville_adresse VARCHAR(255), etat_adresse VARCHAR(255) );
INSERT INTO Personnes ( id_personne, nom_famille_personne, prenom_personne )
VALUES
    ( 1, 'Wang', 'Allen' );
INSERT INTO Adresses ( id_adresse, id_personne, ville_adresse, etat_adresse )
VALUES
    ( 1, 2, 'New York City', 'New York' );
  1. Employés Gagnant Plus que leurs Managers (181)

Description

Voici la table Personnel :

Id Nom Salaire ManagerId
1 Joe 70000 3
2 Henry 80000 4
3 Sam 60000 NULL
4 Max 90000 NULL

Votre objectif est de trouver le nom de tous les employés dont le salaire est strictement supérieur à celui de leur manager.

Solution

Nous utilisnos une auto-jointure sur la table Personnel, en joignant les employés (e) à leurs managers (m) via la colonne manager_id. Ensuite, nous appliquons une condition pour comparer leurs salaires.

SELECT
    e.nom_employe AS Employe
FROM
    Personnel e
INNER JOIN
    Personnel m ON e.manager_id = m.id_employe
WHERE
    e.salaire_employe > m.salaire_employe;

Schéma SQL

DROP TABLE IF EXISTS Personnel;
CREATE TABLE Personnel ( id_employe INT, nom_employe VARCHAR(255), salaire_employe INT, manager_id INT );
INSERT INTO Personnel ( id_employe, nom_employe, salaire_employe, manager_id )
VALUES
    ( 1, 'Joe', 70000, 3 ),
    ( 2, 'Henry', 80000, 4 ),
    ( 3, 'Sam', 60000, NULL ),
    ( 4, 'Max', 90000, NULL );
  1. Clients Sans Commandes (183)

Description

Vous disposez de deux tables : Clients et Commandes.

Table Clients :

Id Nom
1 Joe
2 Henry
3 Sam
4 Max

Table Commandes :

Id ClientId
1 3
2 1

Identifiez les noms des clients qui n'ont jamais passé de commande.

Résultat attendu :

Clients
Henry
Max

Solution

Une méthode efficace pour trouver les enregistrements qui n'ont pas de correspondance dans une autre table est d'utiliser une sous-requête avec NOT EXISTS. Cela vérifie l'absence de commandes pour chaque client.

SELECT
    C.nom_client AS Clients
FROM
    Clients C
WHERE NOT EXISTS (
    SELECT 1
    FROM Commandes O
    WHERE O.id_client = C.id_client
);

Schéma SQL

DROP TABLE IF EXISTS Clients;
CREATE TABLE Clients ( id_client INT, nom_client VARCHAR(255) );
DROP TABLE IF EXISTS Commandes;
CREATE TABLE Commandes ( id_commande INT, id_client INT );
INSERT INTO Clients ( id_client, nom_client )
VALUES
    ( 1, 'Joe' ),
    ( 2, 'Henry' ),
    ( 3, 'Sam' ),
    ( 4, 'Max' );
INSERT INTO Commandes ( id_commande, id_client )
VALUES
    ( 1, 3 ),
    ( 2, 1 );
  1. Salaire le Plus Élevé par Département (184)

Description

Vous avez deux tables, Employes et Departements.

Table Employes :

Id Nom Salaire DepartementId
1 Joe 70000 1
2 Henry 80000 2
3 Sam 60000 2
4 Max 90000 1

Table Departements :

Id Nom
1 IT
2 Sales

Pour chaque département, trouvez le ou les employés qui ont le salaire le plus élevé. Le résultat doit afficher le nom du département, le nom de l'employé et son salaire.

Résultat attendu :

Departement Employe Salaire
IT Max 90000
Sales Henry 80000

Solution

L'utilisation des fonctions de fenêtre SQL (spécifiquement DENSE_RANK()) simplifie grandement ce type de requête. Nous pouvons classer les employés par salaire au sein de chaque département, puis filtrer pour ne garder que ceux ayant le rang 1.

WITH SalairesClasses AS (
    SELECT
        e.id_departement,
        e.nom_employe,
        e.salaire_employe,
        DENSE_RANK() OVER (PARTITION BY e.id_departement ORDER BY e.salaire_employe DESC) AS rang_salaire
    FROM
        Employes e
)
SELECT
    d.nom_departement AS Departement,
    sc.nom_employe AS Employe,
    sc.salaire_employe AS Salaire
FROM
    SalairesClasses sc
JOIN
    Departements d ON sc.id_departement = d.id_departement
WHERE
    sc.rang_salaire = 1;

Schéma SQL

DROP TABLE IF EXISTS Employes;
CREATE TABLE Employes ( id_employe INT, nom_employe VARCHAR(255), salaire_employe INT, id_departement INT );
DROP TABLE IF EXISTS Departements;
CREATE TABLE Departements ( id_departement INT, nom_departement VARCHAR(255) );
INSERT INTO Employes ( id_employe, nom_employe, salaire_employe, id_departement )
VALUES
    ( 1, 'Joe', 70000, 1 ),
    ( 2, 'Henry', 80000, 2 ),
    ( 3, 'Sam', 60000, 2 ),
    ( 4, 'Max', 90000, 1 );
INSERT INTO Departements ( id_departement, nom_departement )
VALUES
    ( 1, 'IT' ),
    ( 2, 'Sales' );
  1. Deuxième Salaire le Plus Élevé (176)

Description

Table Salaires :

Id Salaire
1 100
2 200
3 300

Trouvez le deuxième salaire le plus élevé. Si aucun deuxième salaire n'existe (par exemple, un seul employé), la requête doit retourner NULL.

Résultat attendu :

DeuxiemeSalaireLePlusEleve
200

Solution

Nous pouvons utiliser les fonctions de fenêtre, spécifiquement DENSE_RANK(), pour classer les salaires de manière distincte. Ensuite, nous sélectionnons le salaire correspondant au rang 2. L'utilisation de MAX() sur le résultat garantit que si aucun deuxième salaire n'est trouvé, NULL est retourné.

WITH ClassementSalaires AS (
    SELECT
        salaire_emp AS Salaire,
        DENSE_RANK() OVER (ORDER BY salaire_emp DESC) AS rang_salaire
    FROM
        Employes
)
SELECT
    MAX(CASE WHEN rang_salaire = 2 THEN Salaire ELSE NULL END) AS DeuxiemeSalaireLePlusEleve
FROM
    ClassementSalaires;

Schéma SQL

DROP TABLE IF EXISTS Employes;
CREATE TABLE Employes ( id_employe INT, salaire_emp INT );
INSERT INTO Employes ( id_employe, salaire_emp )
VALUES
    ( 1, 100 ),
    ( 2, 200 ),
    ( 3, 300 );
  1. N-ième Salaire le Plus Élevé (177)

Description

Écrivez une fonction SQL pour récupérer le N-ième salaire le plus élevé. La table est la même que celle de la question précédente (Employes).

Solution

Nous allons créer une fonction qui prend un entier N en paramètre. À l'intérieur de la fonction, nous utiliserons la fonction de fenêtre DENSE_RANK() pour classer les salaires et sélectionner le salaire correspondant au rang N.

CREATE FUNCTION ObtenirNiemeSalaireLePlusEleve (rang_souhaite INT) RETURNS INT
BEGIN
    DECLARE resultat_salaire INT;

    WITH SalairesClasses AS (
        SELECT
            salaire_emp,
            DENSE_RANK() OVER (ORDER BY salaire_emp DESC) AS rang_effectif
        FROM
            Employes
    )
    SELECT DISTINCT salaire_emp
    INTO resultat_salaire
    FROM SalairesClasses
    WHERE rang_effectif = rang_souhaite;

    RETURN resultat_salaire;
END;

Schéma SQL

Identique à la question 176.

  1. Classement des Scores (178)

Description

Voici la table ResultatsExamens :

Id Score
1 3.50
2 3.65
3 4.00
4 3.85
5 4.00
6 3.65

Classez les scores du plus élevé au moins élevé. Les scores égaux doivent avoir le même rang, et il ne doit pas y avoir de "trous" dans la séquence des rangs (c'est-à-dire un classement de type "dense").

Résultat attendu :

Score Rang
4.00 1
4.00 1
3.85 2
3.65 3
3.65 3
3.50 4

Solution

La fonction de fenêtre DENSE_RANK() est parfaitement adaptée à ce problème. Elle attribue un rang à chaque ligne en fonction de son ordre dans un groupe partitionné, sans sauter de rangs en cas d'égalité.

SELECT
    score_etudiant AS Score,
    DENSE_RANK() OVER (ORDER BY score_etudiant DESC) AS Rang
FROM
    ResultatsExamens
ORDER BY
    Score DESC;

Schéma SQL

DROP TABLE IF EXISTS ResultatsExamens;
CREATE TABLE ResultatsExamens ( id_enregistrement INT, score_etudiant DECIMAL(3,2) );
INSERT INTO ResultatsExamens ( id_enregistrement, score_etudiant )
VALUES
    ( 1, 4.1 ),
    ( 2, 4.1 ),
    ( 3, 4.2 ),
    ( 4, 4.2 ),
    ( 5, 4.3 ),
    ( 6, 4.3 );
  1. Nombres Consécutifs (180)

Description

Vous avez une table HistoriqueNombres :

Id Num
1 1
2 1
3 1
4 2
5 1
6 2
7 2

Trouvez tous les nombres qui apparaissent au moins trois fois consécutivement.

Résultat attendu :

NombresConsecutifs
1

Solution

Pour identifier les nombres consécutifs, nous pouvons utiliser la fonction de fenêtre LAG() pour accéder aux valeurs des lignes précédentes. Nous vérifions ensuite si le nombre actuel est le même que les deux précédents.

WITH NumerosSequences AS (
    SELECT
        numero_valeur,
        LAG(numero_valeur, 1) OVER (ORDER BY id_enregistrement) AS precedent_un,
        LAG(numero_valeur, 2) OVER (ORDER BY id_enregistrement) AS precedent_deux
    FROM
        HistoriqueNombres
)
SELECT DISTINCT
    numero_valeur AS NombresConsecutifs
FROM
    NumerosSequences
WHERE
    numero_valeur = precedent_un
    AND numero_valeur = precedent_deux;

Schéma SQL

DROP TABLE IF EXISTS HistoriqueNombres;
CREATE TABLE HistoriqueNombres ( id_enregistrement INT, numero_valeur INT );
INSERT INTO HistoriqueNombres ( id_enregistrement, numero_valeur )
VALUES
    ( 1, 1 ),
    ( 2, 1 ),
    ( 3, 1 ),
    ( 4, 2 ),
    ( 5, 1 ),
    ( 6, 2 ),
    ( 7, 2 );
  1. Échanger les Places (626)

Description

La table Sieges contient un identifiant de siège (id) et le nom de l'étudiant (student) qui l'occupe :

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

Échangez les étudiants des sièges adjacents. Si le nombre total de sièges est impair, le dernier siège (avec l'id le plus élevé) doit rester inchangé. Le résultat doit être ordonné par id.

Résultat attendu :

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

Solution

Une instruction SELECT avec une clause CASE permet de gérer les différentes logiques d'échange. Pour un id impair qui n'est pas le dernier, on ajoute 1. Pour un id pair, on soustrait 1. Le dernier id impair reste tel quel.

SELECT
    CASE
        -- Si l'id est impair et c'est le dernier siège, il ne bouge pas
        WHEN MOD(s.id_siege, 2) = 1 AND s.id_siege = (SELECT MAX(sub.id_siege) FROM Sieges sub) THEN s.id_siege
        -- Si l'id est impair (et non le dernier), il prend l'id suivant
        WHEN MOD(s.id_siege, 2) = 1 THEN s.id_siege + 1
        -- Si l'id est pair, il prend l'id précédent
        ELSE s.id_siege - 1
    END AS id,
    s.nom_etudiant AS student
FROM
    Sieges s
ORDER BY
    id ASC;

Schéma SQL

DROP TABLE IF EXISTS Sieges;
CREATE TABLE Sieges ( id_siege INT, nom_etudiant VARCHAR(255) );
INSERT INTO Sieges ( id_siege, nom_etudiant )
VALUES
    ( '1', 'Abbot' ),
    ( '2', 'Doris' ),
    ( '3', 'Emerson' ),
    ( '4', 'Green' ),
    ( '5', 'Jeames' );

Étiquettes: SQL Fonctions Fenêtre jointures SQL Sous-requêtes manipulation de données

Publié le 25 août à 03h54

Étiquettes Populaires

©2026 L'Atelier Monstre