Atelier informatique

Atelier

SQL

Manipulez des données, créez des requêtes et comprenez les bases des bases de données relationnelles avec des ateliers conçus pour l’apprentissage.

voir les ateliers
voir les bases → 
				
					requete.sql

SELECT e.nom, e.prenom, COUNT(n.note) AS nb_notes
FROM eleve e

INNER JOIN notation n ON e.num_eleve = n.num_eleve

GROUP BY e.num_eleve

ORDER BY nb_notes DESC;
				
			
  // 01 - Concepts clés

LES BASES DU SQL

01

SELECT … FROM

SELECT … FROM … est la structure de base de toute requête. SELECT choisit les colonnes à afficher, FROM indique la table source. Exemple : SELECT nom, prenom FROM eleve;

02

WHERE

WHERE filtre les lignes selon une condition. Exemple : WHERE note = note_max filtre uniquement les notes maximales. On peut combiner des conditions avec AND, OR.

03

AS (renommage)

AS permet de renommer une colonne ou une table dans l’affichage. Exemple : COUNT(note) AS nb_notes. On peut aussi renommer une table : FROM eleve e.

04

COUNT · MAX · AVG

COUNT compte le nombre de lignes. MAX retourne la valeur maximale. AVG calcule la moyenne. ALL est utilisé pour comparer une valeur à toutes les autres. Exemple : COUNT(*) AS total, AVG(note) AS moyenne.

05

INNER JOIN · ON

JOIN relie deux tables sur une colonne commune. INNER JOIN … ON ne retourne que les lignes qui ont une correspondance dans les deux tables. Exemple : INNER JOIN notation n ON e.num_eleve = n.num_eleve. On peut aussi faire un produit cartésien (FROM t1, t2 WHERE t1.id = t2.id).

06

GROUP BY

GROUP BY regroupe les lignes ayant la même valeur pour une colonne, afin d’appliquer une fonction d’agrégation par groupe. Exemple : GROUP BY e.num_eleve pour compter les notes par élève.

07

DISTINCT

DISTINCT élimine les doublons dans les résultats. Exemple : SELECT DISTINCT num_eleve FROM notation retourne chaque élève une seule fois, même s’il a plusieurs notes.

08

SCHÉMA BDD

Le schéma de la base de données de l’enquête : PERSONNE → ELEVE (num_eleve, est_dyslexique) et PROFESSEUR (num_prof, est_agrege). CLASSE (id_classe, nom_classe, #niveau). MATIERE (nom_matiere). CONTROLE (id_controle, #prof, #matiere, #classe, note_max, coefficient, date). NOTATION (#eleve, #controle, note). Chaque élève est noté aux contrôles de sa classe.

  // 02 - Rssources utiles

Instructions crunchy data playground

  // 03 - Mise en pratique

ATELIERS GUIDÉS

Une base de données scolaire a été piratée : des fausses notes ont été insérées.
Retrouvez le coupable en résolvant 7 étapes de requêtes SQL.
 
Voici le lien Crunchy Data Playground permettant de faire des requêtes SQL sur la base de données décrite par le schéma suivant : 
 
  • Étape 1Liste des élèves

Votre professeur vous confie une mission : retrouver le coupable. Commencez par afficher la liste des élèves, puis comptez-les.

💡 Indice : utilisez COUNT(*) pour compter les enregistrements.

				
					-- Liste des élèves
SELECT * FROM eleve;

-- Nombre total d'élèves
SELECT COUNT(*) AS nb_eleves FROM eleve;
  • Étape 2Vérifier les notes

L’informaticien vous informe qu’avant l’intrusion il y avait 13 096 notes en tout, dont 908 notes maximales (note = note_max du contrôle). Vérifiez si c’est toujours le cas. Au moins deux méthodes sont attendues.

💡 Indice : produit cartésien FROM t1, t2 WHERE … ou INNER JOIN … ON ….

				
					-- Total des notes
SELECT COUNT(*) FROM notation;

-- Méthode 1 : produit cartésien
SELECT COUNT(*) FROM notation, controle
WHERE notation.controle = controle.id_controle
AND notation.note = controle.note_max;

-- Méthode 2 : INNER JOIN
SELECT COUNT(*) FROM notation
INNER JOIN controle ON notation.controle = controle.id_controle
WHERE notation.note = controle.note_max;
  • Étape 3Moyenne de notes par élève

50 notes maximales ont l’air d’avoir été rajoutées cette nuit. Cherchons si un élève a un nombre anormal de notes. Trouvez d’abord le nombre moyen de notes par élève. Au moins deux méthodes sont attendues.

💡 Méthode 1 : AVG. Méthode 2 : moy = nb_notes / nb_eleves avec DISTINCT.

				
					-- Méthode 1 : AVG
SELECT AVG(nb) AS moy_notes
FROM (SELECT COUNT(*) AS nb
      FROM notation GROUP BY eleve) t;

-- Méthode 2 : nb_notes / nb_eleves
SELECT COUNT(*) / COUNT(DISTINCT eleve) AS moy_notes
FROM notation;
  • Étape 4L’élève suspect

Trouvez l’élève ayant le plus grand nombre de notes. Affichez son numéro élève, son nom, son prénom et sa classe. Au moins deux méthodes sont attendues.

💡 Indice : ALL ou MAX permettent de vérifier si un nombre est supérieur ou égal à tous les autres.

				
					-- Méthode 1 : avec ALL
SELECT e.num_eleve, e.nom, e.prenom, c.nom_classe
FROM eleve e
INNER JOIN notation n ON e.num_eleve = n.eleve
INNER JOIN classe c ON e.classe = c.id_classe
GROUP BY e.num_eleve, e.nom, e.prenom, c.nom_classe
HAVING COUNT(*) >= ALL (
  SELECT COUNT(*) FROM notation GROUP BY eleve
);

-- Méthode 2 : avec MAX
SELECT e.num_eleve, e.nom, e.prenom, c.nom_classe
FROM eleve e
INNER JOIN notation n ON e.num_eleve = n.eleve
INNER JOIN classe c ON e.classe = c.id_classe
GROUP BY e.num_eleve, e.nom, e.prenom, c.nom_classe
HAVING COUNT(*) = (
  SELECT MAX(cnt) FROM
    (SELECT COUNT(*) AS cnt FROM notation GROUP BY eleve) t
);
  • Étape 5Combien de notes ?

Combien de fois cet élève a-t-il été noté au total ?

				
					SELECT COUNT(*) AS nb_notations
FROM notation
WHERE eleve = /* num_eleve trouvé à l'étape 4 */;
  • Étape 6Notes par contrôle

Affichez la note obtenue et la note maximale pour chaque contrôle de cet élève.

				
					SELECT n.note, c.note_max
FROM notation n
INNER JOIN controle c ON n.controle = c.id_controle
WHERE n.eleve = /* num_suspect */;
  • Étape 7Résolution de l’enquête

En regardant les dernières notations de cet élève, il semble extrêmement suspect. Affichez ses notes maximales avec le nom de la matière, le nom et prénom du professeur, la date et la description du contrôle. Y trouvez-vous une information intéressante ? Si oui, communiquez-la à votre professeur !

				
					SELECT m.nom_matiere, p.nom, p.prenom,
       c.date, c.description
FROM notation n
INNER JOIN controle c  ON n.controle  = c.id_controle
INNER JOIN matiere  m  ON c.matiere  = m.nom_matiere
INNER JOIN professeur p ON c.prof     = p.num_prof
WHERE n.eleve = /* num_suspect */
AND   n.note      = c.note_max;
  • Solution Étape 1

Il y a 273 élèves dans l’école.

				
					-- Nombre total d'élèves
SELECT COUNT(*) AS nb_eleves FROM eleve;
-- Résultat : 273
  • Solution Étape 2
  • Il y a maintenant 13 154 notes(au lieu de 13096) , et 958 notes maximales (au lieu de 908). 50 notes maximales ont été ajoutées.
				
					-- Total des notes
SELECT COUNT(*) FROM notation;
-- Résultat : 13154

-- Notes maximales (méthode INNER JOIN)
SELECT COUNT(*) FROM notation
INNER JOIN controle ON notation.controle = controle.id_controle
WHERE notation.note = controle.note_max;
-- Résultat : 958
  • Solution Étape 3

La moyenne est d’environ 48.18 notes par élève.

				
					-- Moyenne avec AVG
SELECT AVG(nb) AS moy_notes
FROM (SELECT COUNT(*) AS nb
      FROM notation GROUP BY eleve) t;
-- Résultat : 48.18
  • Solution Étape 4

L’élève suspect est le numéro 99597461, LECOMTE Francisco, en classe de sixième.

				
					-- Élève avec le plus de notes (méthode MAX)
SELECT e.num_eleve, e.nom, e.prenom, c.nom_classe
FROM eleve e
INNER JOIN notation n ON e.num_eleve = n.eleve
INNER JOIN classe c ON e.classe = c.id_classe
GROUP BY e.num_eleve, e.nom, e.prenom, c.nom_classe
HAVING COUNT(*) = (
  SELECT MAX(cnt) FROM
    (SELECT COUNT(*) AS cnt FROM notation GROUP BY eleve) t
);
-- Résultat : 99597461 | LECOMTE | Francisco | 6
  • Solution Étape 5

Cet élève a 76 notations.

				
					SELECT COUNT(*) AS nb_notations
FROM notation
WHERE eleve = 99597461;
-- Résultat : 98
  • Solution Étape 6

Voici les notes de l’élève pour chaque contrôle.

				
					SELECT n.note, c.note_max
FROM notation n
INNER JOIN controle c ON n.controle = c.id_controle
WHERE n.eleve = 99597461;
-- Résultats : liste des notes et max
  • Solution Étape 7

Le coupable est la professeure Marie VERMINE, car elle a noté plusieurs fois le même contrôle du 30 juin.

				
					SELECT m.nom_matiere, p.nom, p.prenom,
       c.date, c.description
FROM notation n
INNER JOIN controle c  ON n.controle  = c.id_controle
INNER JOIN matiere  m  ON c.matiere  = m.nom_matiere
INNER JOIN professeur p ON c.prof     = p.num_prof
WHERE n.eleve = 99597461
AND   n.note      = c.note_max;
-- Résultats : liste des contrôles avec notes max
Approfondissez vos connaissances en SQL avec des jointures avancées et des requêtes complexes.
    
Contexte : 

Vous êtes une jeune recrue ingénieur(e) informaticien(ne) de la police scientifique Française. Votre supérieur, ingénieur en chef, vous attribue une première mission : vous avez la charge d’enquêter sur l’explosion d’une voiture dans laquelle deux personnes sont décédées.
Les enquêteurs qui ont inspecté la scène ont rapportés qu’il y avait des traces d’explosifs ont été relevés sous la voiture. La piste la plus probable à ce jour est que cela soit un homicide. À noter que Giovanni Falcone était un juge italien engagé dans la lutte anti-mafia.

Voici le lien Crunchy Data Playground permettant de faire des requêtes SQL sur la base de données décrite par le schéma suivant :

  • Étape 1Existence du maire dans la base

Avant de fouiller dans la base de données pour obtenir plus d’informations, vérifiez que le maire de la ville, M. Sop Alain, existe bien. 

💡 Indice : Utiliser SELECT FROM WHERE AND

				
					SELECT *
FROM /* TODO */
WHERE /* TODO */
  AND /* TODO */ ;
  • Étape 2Numéro du maire

Trouvez le ou les numéros de téléphone du maire de la ville

💡 Indice : Joindre la table carte_sim

				
					SELECT c.numero
FROM personne p
JOIN /* TODO */
ON /* TODO */
WHERE /* TODO */
AND /* TODO */
  • Étape 3Recherche d’antenne

Cherchez l’antenne ou le groupe d’antennes le plus proches du lieu du meurtre (latitude : 43.60189922045401 , longitude : 3.8950539964978104). Affichez aussi la distance. Pour cela, vous avez à votre disposition la fonction distance(latitude1, longitude1, latitude2, longitude2) créée spécialement pour l’occasion qui calcule le nombre de km entre deux coordonnées géographiques. Vous devrez utiliser deux types de requête différente. 

💡 Indice : Utiliser deux requêtes avec MIN et ALL 

				
					/* Méthode 1 */
SELECT id_antenne,
       distance(latitude, longitude,
                43.60189922045401,
                3.8950539964978104) AS distance
FROM antenne
WHERE distance(latitude, longitude,
               43.60189922045401,
               3.8950539964978104)
=
(
SELECT MIN(distance(latitude, longitude,
                    43.60189922045401,
                    3.8950539964978104))
FROM antenne
);

/* Méthode 2 */
SELECT id_antenne,
       distance(latitude, longitude,
                43.60189922045401,
                3.8950539964978104) AS distance
FROM antenne
WHERE distance(latitude, longitude,
               43.60189922045401,
               3.8950539964978104)
<= ALL
(
SELECT distance(latitude, longitude,
                43.60189922045401,
                3.8950539964978104)
FROM antenne
);
  • Étape 4Liste d’appels

Avant de chercher les informations sur une potentielle personne ayant passé un appel avec le maire sur les lieux du meurtre, cherchez la liste des appels passés entre deux sessions (envoyeur ou receveur) où au moins l’une d’entre elle utilise une des antennes proches du crime. 

💡 Indice : Utiliser la requête précédente en tant que sous-requête.

				
					SELECT *
FROM appel a
JOIN session /* TODO */
JOIN session /* TODO */
WHERE/* TODO */ IN (
    SELECT id_antenne
    FROM antenne
    WHERE distance(latitude, longitude,
                   43.60189922045401,
                   3.8950539964978104)
    <= ALL (
        SELECT distance(latitude, longitude,
                        43.60189922045401,
                        3.8950539964978104)
        FROM antenne
    )
)
OR /* TODO */ IN (
    SELECT id_antenne
    FROM antenne
    WHERE distance(latitude, longitude,
                   43.60189922045401,
                   3.8950539964978104)
    <= ALL (
        SELECT distance(latitude, longitude,
                        43.60189922045401,
                        3.8950539964978104)
        FROM antenne
    )
);
  • Étape 5Appels avec le maire

Maintenant, trouvez les appels passés avec le maire la date du meurtre. 

💡 Indice : Cherchez dans les appels les numéros appartenant au maire, que ce soit receveur ou envoyeur. Vous devrez utiliser la fonction EXTRACT(part FROM date) avec « part » la partie à extraire de la date (year, month, day, etc.). 

				
					SELECT *
FROM appel a
JOIN session s1 ON a.envoyeur = s1.id_session
JOIN session s2 ON a.receveur = s2.id_session
WHERE
(
s1.sim IN
(
SELECT numero
FROM carte_sim
WHERE proprietaire =
(
SELECT id_personne
FROM personne
WHERE nom='Sop'
AND prenom='Alain'
)
)
OR
s2.sim IN
(
SELECT numero
FROM carte_sim
WHERE proprietaire =
(
SELECT id_personne
FROM personne
WHERE nom='Sop'
AND prenom='Alain'
)
)
)
AND EXTRACT(/* TODO */)=2022
AND EXTRACT(/* TODO */)=6
AND EXTRACT(/* TODO */)=27;
  • Étape 6Correspondant du maire

À qui appartient ce numéro de téléphone ? 

💡 Indice : Joindre carte_sim et utiliser le numéro trouvé à l’étape 5.

				
					SELECT *
FROM personne p
JOIN carte_sim c
ON /* TODO */
WHERE c.numero=/* TODO */;
  • Étape 7Recherche de session

Vous informez votre supérieur de votre découverte : il y a bien eu un appel avec le maire le matin avant l’explosion ! 

Le lendemain, il revient vers vous avec quelques informations. Il a fait interroger cette personne et les résultats sont intéressant : il se trouve qu’elle a appelé le maire dans le but de prendre des nouvelles, car c’est en fait sa petite-fille. Le maire a pu confirmer ses propos. 

Elle est en ce moment employé par une société des eaux de la ville. Et lors d’un de ses déplacements, elle avait remarqué une étrange personne qui avait l’air de trifouiller le dessous d’une voiture vers 6h du matin. Elle a eu la bonne idée de prendre cette étrange personne en photo. Cette photo était de mauvaise qualité, mais néanmoins on peut y reconnaître clairement la voiture de M. Falcone et Mme Morvillo ! De plus, cette personne avait un pull vert foncé. 

Camille Honnête a communiqué une dernière information : elle avait vu la personne envoyer de nombreux messages textuels sur son smartphone entre 6h et 6h30 (au moins d’une dizaine). 

Trouvez une session (si elle existe) dans laquelle au moins 10 SMS ont été envoyés entre 6h et 6h30 du matin au lieu du crime. 

💡 Indice : Utiliser COUNT, la requête de l’étape 3 ainsi que EXTRACT.

				
					SELECT s.id_session, /* TODO */
FROM sms sm
JOIN session s ON sm.envoyeur = s.id_session
WHERE s.antenne IN (
    SELECT id_antenne
    FROM antenne
    WHERE distance(latitude, longitude, 43.60189922045401, 3.8950539964978104) <= ALL (
        SELECT distance(latitude, longitude, 43.60189922045401, 3.8950539964978104)
        FROM antenne
    )
)
AND EXTRACT(/* TODO */) = 6
AND EXTRACT(/* TODO */) <= 30
GROUP BY s.id_session
HAVING /* TODO */;
  • Étape 8Appartenance du numéro

Le numéro de téléphone lié à cette session appartient-il a quelqu’un ? 

💡 Indice : Joindre session et utiliser l’id_session trouvé à l’étape précédente.

				
					SELECT p.*
FROM personne p
JOIN carte_sim c
ON p.id_personne = c.proprietaire
JOIN session s
ON /* TODO */
WHERE s.id_session = /* TODO */;
  • Étape 9Conversation SMS

Afficher les conversation SMS liée à cette session. Afficher le numéro de téléphone envoyeur et receveur pour chaque SMS. 

💡 Indice : Utiliser la table sms et l’id_session précédent.

				
					SELECT se.sim AS numero_envoyeur,
       sr.sim AS numero_receveur,
       sms.date,
       sms.contenu
FROM sms
JOIN session se
ON /* TODO */
JOIN session sr
ON /* TODO */
WHERE sms.envoyeur=/* TODO */
   OR sms.receveur=/* TODO */
ORDER BY sms.date;
  • Étape 10Appartenance second du numéro

L’autre numéro de téléphone appartient-il à quelqu’un ? 

💡 Indice : Utiliser le numéro du premier receveur apparaissant dans la conversation.

				
					SELECT *
FROM personne p
JOIN carte_sim c
ON /* TODO */
WHERE c.numero=/* TODO */;
  • Étape 11Trouver le coupable

Trouvez l’identité du poseur de bombe. Nous avons besoin de son numéro d’identification, son nom, son prénom, sa date de naissance et le numéro de téléphone utilisé. Pour cela, effectuez une seule requête SQL en utilisant les informations de la précédente conversation. 

Les coordonnées géographiques de Palavas sont : 43.5288, 3.9308 

💡 Indice : Dans cette conversation, on peut y trouver une information importante : le poseur de bombe a l’air d’avoir gardé sa carte SIM personnelle avec lui. Il va être possible de le traquer sachant que nous connaissons maintenant 2 positions du suspect à des horaires différentes.

				
					SELECT p.id_personne,
       p.nom,
       p.prenom,
       p.date_naissance,
       c.numero
FROM personne p
JOIN /* TODO */
WHERE c.numero IN (
    SELECT DISTINCT /* TODO */
    FROM /* TODO */
    WHERE antenne IN (
        /* TODO */
    )
    /* TODO */
    SELECT DISTINCT /* TODO */
    FROM /* TODO */
    WHERE antenne IN (
        SELECT id_antenne
        FROM antenne
        WHERE distance(latitude, longitude, 43.60189922045401, 3.8950539964978104)
              = (
                  SELECT MIN(distance(latitude, longitude, 43.60189922045401, 3.8950539964978104))
                  FROM antenne
              )
    )
);
  • Solution Étape 1
				
					SELECT *
FROM personne
WHERE nom = 'Sop'
  AND prenom = 'Alain';
  -- Résultats : id_personne = 145545455466
  • Solution Étape 2
				
					SELECT c.numero
FROM personne p
JOIN carte_sim c
ON p.id_personne = c.proprietaire
WHERE p.nom = 'Sop'
AND p.prenom = 'Alain';
-- Résultats : 778985226 
 --658457855
  • Solution Étape 3
				
					/* Méthode 1 */
SELECT id_antenne,
       distance(latitude, longitude,
                43.60189922045401,
                3.8950539964978104) AS distance
FROM antenne
WHERE distance(latitude, longitude,
               43.60189922045401,
               3.8950539964978104)
=
(
SELECT MIN(distance(latitude, longitude,
                    43.60189922045401,
                    3.8950539964978104))
FROM antenne
);

/* Méthode 2 */
SELECT id_antenne,
       distance(latitude, longitude,
                43.60189922045401,
                3.8950539964978104) AS distance
FROM antenne
WHERE distance(latitude, longitude,
               43.60189922045401,
               3.8950539964978104)
<= ALL
(
SELECT distance(latitude, longitude,
                43.60189922045401,
                3.8950539964978104)
FROM antenne
);
-- Résultats : antennes 2329 à 2357
  • Solution Étape 4
				
					SELECT *
FROM appel a
JOIN session s1 ON a.envoyeur = s1.id_session
JOIN session s2 ON a.receveur = s2.id_session
WHERE s1.antenne IN (
    SELECT id_antenne
    FROM antenne
    WHERE distance(latitude, longitude,
                   43.60189922045401,
                   3.8950539964978104)
    <= ALL (
        SELECT distance(latitude, longitude,
                        43.60189922045401,
                        3.8950539964978104)
        FROM antenne
    )
)
OR s2.antenne IN (
    SELECT id_antenne
    FROM antenne
    WHERE distance(latitude, longitude,
                   43.60189922045401,
                   3.8950539964978104)
    <= ALL (
        SELECT distance(latitude, longitude,
                        43.60189922045401,
                        3.8950539964978104)
        FROM antenne
    )
);
-- Résultats : On trouve 14 appels
  • Solution Étape 5
				
					SELECT *
FROM appel a
JOIN session s1 ON a.envoyeur = s1.id_session
JOIN session s2 ON a.receveur = s2.id_session
WHERE
(
s1.sim IN
(
SELECT numero
FROM carte_sim
WHERE proprietaire =
(
SELECT id_personne
FROM personne
WHERE nom='Sop'
AND prenom='Alain'
)
)
OR
s2.sim IN
(
SELECT numero
FROM carte_sim
WHERE proprietaire =
(
SELECT id_personne
FROM personne
WHERE nom='Sop'
AND prenom='Alain'
)
)
)
AND EXTRACT(YEAR FROM a.date_debut)=2022
AND EXTRACT(MONTH FROM a.date_debut)=6
AND EXTRACT(DAY FROM a.date_debut)=27;
-- Résultats : 1 appel avec 645236585
  • Solution Étape 6
				
					SELECT *
FROM personne p
JOIN carte_sim c
ON p.id_personne = c.proprietaire
WHERE c.numero='645236585';
--Résultats : Camille HONNETE
  • Solution Étape 7
				
					SELECT s.id_session, COUNT(*) AS nb_sms
FROM sms sm
JOIN session s ON sm.envoyeur = s.id_session
WHERE s.antenne IN (
    SELECT id_antenne
    FROM antenne
    WHERE distance(latitude, longitude, 43.60189922045401, 3.8950539964978104) <= ALL (
        SELECT distance(latitude, longitude, 43.60189922045401, 3.8950539964978104)
        FROM antenne
    )
)
AND EXTRACT(HOUR FROM sm.date) = 6
AND EXTRACT(MINUTE FROM sm.date) <= 30
GROUP BY s.id_session
HAVING COUNT(*) >= 10;
--Résultats : 1 session avec l'id 53734
  • Solution Étape 8
				
					SELECT p.*
FROM personne p
JOIN carte_sim c
ON p.id_personne = c.proprietaire
JOIN session s
ON c.numero = s.sim
WHERE s.id_session = 53734;
--Résultats : Pas de numéro
  • Solution Étape 9
				
					SELECT se.sim AS numero_envoyeur,
       sr.sim AS numero_receveur,
       sms.date,
       sms.contenu
FROM sms
JOIN session se
ON sms.envoyeur=se.id_session
JOIN session sr
ON sms.receveur=sr.id_session
WHERE sms.envoyeur=53734
   OR sms.receveur=53734
ORDER BY sms.date;
--Résultats : Une conversation plutôt suspecte
  • Solution Étape 10
				
					SELECT *
FROM personne p
JOIN carte_sim c
ON p.id_personne=c.proprietaire
WHERE c.numero='623534522';
--Résultats : Pas de correspondance
  • Solution Étape 11
				
					SELECT p.id_personne,
       p.nom,
       p.prenom,
       p.date_naissance,
       c.numero
FROM personne p
JOIN carte_sim c ON p.id_personne = c.proprietaire
WHERE c.numero IN (
    SELECT DISTINCT sim
    FROM session
    WHERE antenne IN (
        SELECT id_antenne
        FROM antenne
        WHERE distance(latitude, longitude, 43.5288, 3.9308)
              <= ALL (
                  SELECT distance(latitude, longitude, 43.5288, 3.9308)
                  FROM antenne
              )
    )
    INTERSECT
    SELECT DISTINCT sim
    FROM session
    WHERE antenne IN (
        SELECT id_antenne
        FROM antenne
        WHERE distance(latitude, longitude, 43.60189922045401, 3.8950539964978104)
              = (
                  SELECT MIN(distance(latitude, longitude, 43.60189922045401, 3.8950539964978104))
                  FROM antenne
              )
    )
);
--Résultats : Lerbizard Richard
Approfondissez vos connaissances en SQL avec des fonctions d’agrégation.
 

Contexte : vous êtes le nouveau responsable de la sécurité informatique de la banque française PolyBank. Il vous est confié une première mission : un client s’est plaint auprès de son agence à Montpellier, la responsable de l’agence a rédigé une réclamation :

Voici le lien Crunchy Data Playground permettant de faire des requêtes SQL sur la base de données décrite par le schéma suivant :

Schéma relationnel de la base

Le schéma ci-dessous résume la structure de la base de données. Les attributs soulignés représentent les clés primaires de chaque table. Ceux précédés d’un signe # sont des clés étrangères, c’est-à-dire qu’ils font référence à la clé primaire d’une autre table.

CLIENT                         (id_client, nom, prenom, sexe_client, date_naissance, date_inscription, num_tel, email, #domicile, #ville_naissance)

PAYS                             (code_pays, libelle)

VILLE                            (id_ville, libelle, code_postal, #pays)

ADRESSE                     (id_adresse, voie, numero, #ville)

TITULAIRE                   (#compte, #client)

COMPTE                      (id_compte, #type_compte, solde, iban, #agence)

TYPE_COMPTE            (nom_type)

OPERATION                 (id_operation, montant, date_heure, #compte, #carte_bancaire)

– RETRAIT_DEPOT       (id_operation, montant, date_heure, #compte, #carte_bancaire, #distributeur)

– OPERATION_TIERS   (id_operation, montant, date_heure, #compte, #carte_bancaire, #type_operation, iban_tiers, bic_tiers)

DISTRIBUTEUR            (id_distributeur, #adresse)

AGENCE                       (code_agence, #adresse)

TYPE_OPERATION      (nom_type_operation)

CARTE_BANCAIRE      (num_carte, #compte, date_expiration)

  • Étape 1Retrouver la client lésé

À vous de trouver le compte du client avec les informations que vous avez à votre disposition.

  • Étape 2Retrouver les opérations suspectes

Quels sont les types d’opération sur ce compte ? (Opération tiers ou retrait/dépôt)

Aide : Les opérations tierces sont considérées comme potentiellement suspectes.

💡 Indice : il est possible d’utiliser l’expression conditionnelle CASE

  • Étape 3Retrouver le numéro des comptes bénéficiaires

Trouver la liste des comptes tiers bénéficiaires de ces débits, et le type de ces opérations tierces.

  • Étape 4En déduire le nom des clients brigands

À présent, cherchez les clients reliés aux comptes ayant ces identifiants de comptes bancaires.

  • Étape 5Nouvelle requête

Une seule et même personne semble être reliée à ces comptes suspects. De plus, 2 autres clients, Mme Boutonnet et M. Saint Roch, sont venus déclarer des vols similaires de leur argent, tout comme M. Mercier. Après quelques recherches, vos collègues ont trouvé que les vols étaient eux aussi reliés aux mêmes comptes suspects. Cela laisse penser que ce n’était pas une erreur. Vous avez communiqué toutes les informations que vous avez trouvés à la police.

En attendant leur réponse, quelques jours plus tard, un collègue vous aidant sur l’enquête est venu vous voir et à besoin de votre aide. Il dit ne pas parvenir à faire certaines requêtes importantes. Il a besoin de trouver la liste des clients de PolyBank ayant au moins un compte de chaque type.

  • Étape 6

Après avoir trouvé et interrogé la personne suspecte, la police a pu lui soutirer des informations intéressantes. Cette personne, une femme d’affaire anglaise, prétend avoir été payée très généreusement par un inconnu pour effectuer des faux achats (voler l’argent) sur une plateforme d’e-commerce dans laquelle elle a des droits d’accès privilégiés. Le problème, c’est que celle-ci prétend ne détenir que peu d’informations sur cet inconnu, ils se seraient vus à visage couvert.

Comme vous aviez pu aider votre collègue à faire ses requêtes, il vous communique une information intéressante : les 3 comptes de la suspecte ont tous été vidés de 100% de leur solde par retrait en liquide dans des distributeurs. En tout, 128.542.26€ ont été retirés. À vous de tenter de retrouver dans quel compte s’est retrouvé cet argent.

  • Étape 7Retrouver le coupable

Retrouvez à qui appartient ce compte.

  • Solution Étape 1
				
					SELECT * FROM client WHERE nom='Mercier';

Résultats :

id_client                | 852741963
nom                       | Mercier
prenom                 | Marc
sexe_client            | Homme
date_naissance    | 1995-03-16
date_inscription   | 2010-03-16 00:00:00
num_tel                 | 652418973
email                      |
ville_naissance      | 1
domicile                 | 1

  •  
  • Solution Étape 2
				
					SELECT 
    O.id_operation,
    O.montant,
    O.date_heure,
    CASE 
        WHEN RD.id_operation IS NOT NULL THEN 'RETRAIT_DEPOT'
        WHEN OT.id_operation IS NOT NULL THEN 'OPERATION_TIERS'
        ELSE 'INCONNU'
    END AS type_operation
FROM titulaire T
JOIN compte C ON T.compte = C.id_compte
JOIN operation O ON C.id_compte = O.compte
LEFT JOIN retrait_depot RD ON O.id_operation = RD.id_operation
LEFT JOIN operation_tiers OT ON O.id_operation = OT.id_operation
WHERE T.client = '852741963';

Résultats :

  •  
  • Solution Étape 3
				
					SELECT 
    C.id_compte,
    O.id_operation,
    O.montant,
    O.date_heure,
    CASE 
        WHEN OT.id_operation IS NOT NULL THEN 'OPERATION_TIERS'
        WHEN RD.id_operation IS NOT NULL THEN 'RETRAIT_DEPOT'
        ELSE 'AUTRE'
    END AS type_operation,
    OT.iban_tiers,
    OT.bic_tiers,
    RD.distributeur
FROM titulaire T
JOIN compte C ON T.compte = C.id_compte
JOIN operation O ON C.id_compte = O.compte
LEFT JOIN operation_tiers OT ON O.id_operation = OT.id_operation
LEFT JOIN retrait_depot RD ON O.id_operation = RD.id_operation
WHERE T.client = '852741963';
  •  
  • Solution Étape 4
				
					SELECT 
    CL.nom,
    CL.prenom,
    T.client AS id_client,
    C.id_compte,
    C.iban
FROM compte C
JOIN titulaire T ON C.id_compte = T.compte
JOIN client CL ON T.client = CL.id_client
WHERE C.iban IN ('FR1420041010050500013M02606', 'FR7614410000011234567890163', 'FR8642559000011234567890121'); 
/*ces numéros d'iban correspondent aux trois trouvés à la quesion précédente*/
  •  
  • Solution Étape 5
				
					SELECT 
    CL.id_client,
    CL.nom,
    CL.prenom,
    COUNT(DISTINCT C.type_compte) AS nombre_types_possedes
FROM client CL
JOIN titulaire T ON CL.id_client = T.client
JOIN compte C ON T.compte = C.id_compte
GROUP BY CL.id_client, CL.nom, CL.prenom
HAVING COUNT(DISTINCT C.type_compte) = 3;

-- la solution est la client Chloé Jones
  •  
  • Solution Étape 6
				
					SELECT DISTINCT T_BENEF.compte AS id_compte_beneficiaire
FROM retrait_depot RD
JOIN operation AS O ON RD.id_operation = O.id_operation
JOIN compte AS C ON O.compte = C.id_compte
--
-- On filtre les retraits faits SUR les comptes de la suspecte
JOIN titulaire AS T_SUSPECTE ON C.id_compte = T_SUSPECTE.compte
--
-- On récupère la carte utilisée pour le retrait
JOIN carte_bancaire AS CB ON RD.carte_bancaire = CB.num_carte
--
-- On trouve le compte associé à cette carte (le bénéficiaire)
JOIN titulaire AS T_BENEF ON CB.compte = T_BENEF.compte
WHERE T_SUSPECTE.client = '100589675'
AND RD.montant IS NOT NULL;
  •     
  • Solution Étape 7
				
					SELECT *
FROM client cli, compte co, titulaire ti
WHERE cli.id_client = ti.client
AND co.id_compte = ti.compte
AND ti.compte = '20003';

Réalisé par

  • Clément Herguezabal
  • Yakun Chen