Atelier SQL – Institut de Science des Données

   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.

				
					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

OUTILS & DOCUMENTATION

Base de données

Notes scolaires

Schéma MCD

Modèle relationnel

Crunchy Data

Terminal en ligne

GitHub

Scripts et énoncés

PostgreSQL

SGBD utilisé

Compétences SQL

8 concepts

  // 03 - Mise en pratique

ATELIERS GUIDÉS

1- ENQUÊTE CAPUCHE

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

2- ENQUÊTE FALCONE

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

3- ENQUÊTE POLYBANK

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 :

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 1Somme des notes

La fonction SUM() calcule des totaux. Calculez la somme totale des notes obtenues par chaque élève. Affichez nom, prénom et total des notes.

💡 Indice : Utilisez SUM() avec GROUP BY pour agréger par élève.

				
					SELECT e.nom, e.prenom, SUM(n.note) AS total_notes
FROM eleve e
INNER JOIN notation n ON e.num_eleve = n.num_eleve
GROUP BY e.num_eleve, e.nom, e.prenom;

Étape 2Écart-type

L’écart-type mesure la dispersion des données. Calculez l’écart-type des notes pour chaque matière. Utilisez STDDEV() pour analyser la variabilité.

💡 Indice : STDDEV() calcule l’écart-type, joignez matiere, controle et notation.

				
					SELECT m.nom_matiere, STDDEV(n.note) AS ecart_type
FROM matiere m
INNER JOIN controle c ON m.nom_matiere = c.nom_matiere
INNER JOIN notation n ON c.id_controle = n.id_controle
GROUP BY m.nom_matiere;

Étape 3Médiane

La médiane est la valeur centrale. Calculez la médiane des notes pour chaque contrôle. Utilisez PERCENTILE_CONT(0.5) pour la médiane.

💡 Indice : PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY note) calcule la médiane.

				
					SELECT c.description, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY n.note) AS mediane
FROM controle c
INNER JOIN notation n ON c.id_controle = n.id_controle
GROUP BY c.id_controle, c.description;

Étape 4Analyse comparative

Comparez les performances par niveau scolaire. Calculez la moyenne des notes pour chaque niveau (6ème, 5ème, etc.). Joignez les tables niveau, classe, eleve et notation.

💡 Indice : Commencez par niveau, joignez classe, eleve, notation pour atteindre les notes.

				
					SELECT niveau.nom_niveau, AVG(notation.note) AS moyenne_niveau
FROM niveau
INNER JOIN classe ON niveau.id_niveau = classe.id_niveau
INNER JOIN eleve ON classe.id_classe = eleve.id_classe
INNER JOIN notation ON eleve.num_eleve = notation.num_eleve
GROUP BY niveau.id_niveau, niveau.nom_niveau;

Étape 5Tendances

Analysez l’évolution temporelle. Calculez la moyenne mensuelle des notes sur l’année scolaire. Utilisez EXTRACT(MONTH FROM date) pour grouper par mois.

💡 Indice : EXTRACT(MONTH FROM c.date) permet d’extraire le mois de la date du contrôle.

				
					SELECT EXTRACT(MONTH FROM c.date) AS mois, AVG(n.note) AS moyenne_mensuelle
FROM controle c
INNER JOIN notation n ON c.id_controle = n.id_controle
GROUP BY EXTRACT(MONTH FROM c.date)
ORDER BY mois;

Étape 6Rapport final

Générez un rapport statistique complet de l’établissement. Calculez le nombre total d’élèves, de contrôles, de notes, les moyennes, min/max. Utilisez CROSS JOIN et LEFT JOIN pour une analyse complète.

💡 Indice : CROSS JOIN entre eleve et controle, puis LEFT JOIN avec notation pour inclure tous les cas.

				
					SELECT 
  COUNT(DISTINCT e.num_eleve) AS total_eleves,
  COUNT(DISTINCT c.id_controle) AS total_controles,
  COUNT(*) AS total_notes,
  AVG(n.note) AS moyenne_generale,
  MIN(n.note) AS note_min,
  MAX(n.note) AS note_max
FROM eleve e
CROSS JOIN controle c
LEFT JOIN notation n ON e.num_eleve = n.num_eleve AND c.id_controle = n.id_controle;

Solution Étape 1

La fonction SUM() agrège les valeurs numériques. Ici, nous joignons les tables eleve et notation pour calculer le total des notes par élève.

💡 Explication : Le GROUP BY est essentiel pour agréger par élève. Sans lui, SUM() calculerait le total de toutes les notes.

				
					SELECT e.nom, e.prenom, SUM(n.note) AS total_notes
FROM eleve e
INNER JOIN notation n ON e.num_eleve = n.num_eleve
GROUP BY e.num_eleve, e.nom, e.prenom;

Solution Étape 2
STDDEV() mesure la dispersion des valeurs autour de la moyenne. Plus l’écart-type est élevé, plus les notes sont dispersées.
💡 Explication : Les trois tables sont jointes pour relier matière → contrôle → notation. L’écart-type révèle la difficulté relative des matières.

				
					SELECT m.nom_matiere, STDDEV(n.note) AS ecart_type
FROM matiere m
INNER JOIN controle c ON m.nom_matiere = c.nom_matiere
INNER JOIN notation n ON c.id_controle = n.id_controle
GROUP BY m.nom_matiere;

Solution Étape 3

PERCENTILE_CONT(0.5) calcule la médiane en trouvant la valeur qui sépare les 50% inférieurs des 50% supérieurs.

💡 Explication : La syntaxe WITHIN GROUP (ORDER BY) est spécifique aux fonctions de fenêtrage. Elle ordonne les notes avant de calculer le percentile.

				
					SELECT c.description, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY n.note) AS mediane
FROM controle c
INNER JOIN notation n ON c.id_controle = n.id_controle
GROUP BY c.id_controle, c.description;

Solution Étape 4

Cette requête traverse quatre tables pour analyser les performances par niveau scolaire : niveau → classe → eleve → notation.

💡 Explication : Cette chaîne de jointures suit la hiérarchie scolaire. Elle révèle comment les performances évoluent avec le niveau.

				
					SELECT niveau.nom_niveau, AVG(notation.note) AS moyenne_niveau
FROM niveau
INNER JOIN classe ON niveau.id_niveau = classe.id_niveau
INNER JOIN eleve ON classe.id_classe = eleve.id_classe
INNER JOIN notation ON eleve.num_eleve = notation.num_eleve
GROUP BY niveau.id_niveau, niveau.nom_niveau;

Solution Étape 5

EXTRACT(MONTH FROM date) extrait le numéro du mois de la date. Combiné avec AVG(), cela révèle l’évolution mensuelle des performances.

💡 Explication : L’ORDER BY sur le mois assure un affichage chronologique. Cette analyse peut révéler des effets saisonniers sur les performances.

				
					SELECT EXTRACT(MONTH FROM c.date) AS mois, AVG(n.note) AS moyenne_mensuelle
FROM controle c
INNER JOIN notation n ON c.id_controle = n.id_controle
GROUP BY EXTRACT(MONTH FROM c.date)
ORDER BY mois;

Solution Étape 6

Cette requête complexe utilise CROSS JOIN pour créer toutes les combinaisons élève-contrôle possibles, puis LEFT JOIN pour inclure les notes existantes.

				
					SELECT 
  COUNT(DISTINCT e.num_eleve) AS total_eleves,
  COUNT(DISTINCT c.id_controle) AS total_controles,
  COUNT(*) AS total_notes,
  AVG(n.note) AS moyenne_generale,
  MIN(n.note) AS note_min,
  MAX(n.note) AS note_max
FROM eleve e
CROSS JOIN controle c
LEFT JOIN notation n ON e.num_eleve = n.num_eleve AND c.id_controle = n.id_controle;

Réalisé par

Clément Herguezabal
Yakun Chen