Requêtes SQL
ℹ️ Cette page documente les deux requêtes SQL nécessaires (principale & secondaire) et leur usage dans le script creation_BDD.R.
Script R – création BDD Voir le dictionnaire des variables
Deux requêtes sont nécessaires pour disposer (1) des données principales sur les notes et (2) des points de jury et de bonus.
Le script creation_BDD.R compile ensuite les deux extractions.
✅ Bonnes pratiques
- Lancer la requête année par année (2015–2016 → 2024–2025) et stocker dans un même répertoire.
- Vérifier que la base active correspond bien à l’année cible avant exécution.
1 Requête principale
La requête se nomme « Scola - Extraction notes - Requête principale - Pour Base_eleves ».
Elle fonctionne pour toutes les années 2015–2016 à 2024–2025.
SELECT RIGHT(DATABASE(),4) AS annee_courante,
voie.voie_lib,
aide.situation, -- libellé(s) de voie(s) “10 juin”
/* — identité de l’étudiant — */
e.nom,
e.prenom,
e.libelle_etat_civil,
e.id_etudiant,
/* — module / matière — */
mo.libelle AS ue,
m.id_type_matiere,
m.intitule AS matiere,
m.code_matiere,
ROUND(moy.moyenne,2) AS moyenne_matiere,
ROUND(moymo.moyenne,2) AS moyenne_ue,
/* — variables « profil » (requête 1) — */
e.paysnai,
e.id_ref_paysnai,
e.id_nationalite,
e.libelle_statut_etudiant,
e.concours_origine,
e.concours_annee,
e.etab_origine_formation,
e.bac_annee,
e.bac_mention,
e.certif_anglais_org,
e.certif_anglais_score,
e.toeic,
/* — indicateurs issus de la requête “10 juin” — */
aide.date_naissance,
aide.sexe,
aide.double_diplome,
aide.autre_diplome,
aide.redoublement,
aide.att_ing,
aide.bac_spe1,
aide.bac_spe2,
aide.cpge,
aide.bourse,
aide.bourse_type,
aide.bourse_montant,
aide.id_pcs_pere,
aide.id_pcs_mere,
aide.MES1, aide.MHS1, aide.MIS1, aide.MSS1,
aide.MES2, aide.MHS2, aide.MIS2, aide.MSS2,
aide.MGS1, aide.MGS2,
aide.RES1, aide.RHS1, aide.RIS1, aide.RSS1,
aide.RES2, aide.RHS2, aide.RIS2, aide.RSS2,
aide.moyenne_generale,
aide.total_coeff,
aide.total_ects,
aide.rattrapage_max,
aide.AV -- année validée (0/1)FROM moyenne AS moy LEFT JOIN etudiant AS e ON e.id_etudiant = moy.id_etudiant LEFT JOIN matiere AS m ON m.id_matiere = moy.id_matiere LEFT JOIN module AS mo ON mo.id_module = m.id_module LEFT JOIN moyenne_module AS moymo ON moymo.id_module = m.id_module AND moymo.id_etudiant = e.id_etudiant
/* ======================= sous-requête « voies » ======================= */ LEFT JOIN ( SELECT ve.id_etudiant, GROUP_CONCAT(DISTINCT v.lib ORDER BY v.lib) AS voie_lib FROM voie_etudiant ve INNER JOIN voie v ON v.id_voie = ve.id_voie GROUP BY ve.id_etudiant ) AS voie ON voie.id_etudiant = e.id_etudiant
/* ==================== sous-requête « agrégats annuels » =============== / LEFT JOIN ( / ——– sélection agrégée par étudiant (version 10 juin) ——– */ SELECT aide_int.id_etudiant,
MAX(aide_int.annee) AS annee,
MAX(aide_int.date_naissance) AS date_naissance,
MAX(aide_int.situation) AS situation,
MAX(aide_int.sexe) AS sexe,
MAX(aide_int.double_diplome) AS double_diplome,
MAX(aide_int.autre_diplome) AS autre_diplome,
MAX(aide_int.redoublement) AS redoublement,
MAX(aide_int.att_ing) AS att_ing,
MAX(aide_int.bac_annee) AS bac_annee,
MAX(aide_int.bac_spe1) AS bac_spe1,
MAX(aide_int.bac_spe2) AS bac_spe2,
MAX(aide_int.bac_mention) AS bac_mention,
MAX(aide_int.concours_origine) AS concours_origine,
MAX(aide_int.situation_avant) AS situation_avant,
MAX(aide_int.cpge) AS cpge,
MAX(aide_int.bourse) AS bourse,
MAX(aide_int.bourse_type) AS bourse_type,
MAX(aide_int.bourse_montant) AS bourse_montant,
MAX(aide_int.id_pcs_pere) AS id_pcs_pere,
MAX(aide_int.id_pcs_mere) AS id_pcs_mere,
/* — moyennes de groupes — */
GROUP_CONCAT(DISTINCT IF(aide_int.groupe_cat_note='MES1',ROUND(aide_int.groupe_moyenne,2),NULL)) AS MES1,
GROUP_CONCAT(DISTINCT IF(aide_int.groupe_cat_note='MHS1',ROUND(aide_int.groupe_moyenne,2),NULL)) AS MHS1,
GROUP_CONCAT(DISTINCT IF(aide_int.groupe_cat_note='MIS1',ROUND(aide_int.groupe_moyenne,2),NULL)) AS MIS1,
GROUP_CONCAT(DISTINCT IF(aide_int.groupe_cat_note='MSS1',ROUND(aide_int.groupe_moyenne,2),NULL)) AS MSS1,
GROUP_CONCAT(DISTINCT IF(aide_int.groupe_cat_note='MES2',ROUND(aide_int.groupe_moyenne,2),NULL)) AS MES2,
GROUP_CONCAT(DISTINCT IF(aide_int.groupe_cat_note='MHS2',ROUND(aide_int.groupe_moyenne,2),NULL)) AS MHS2,
GROUP_CONCAT(DISTINCT IF(aide_int.groupe_cat_note='MIS2',ROUND(aide_int.groupe_moyenne,2),NULL)) AS MIS2,
GROUP_CONCAT(DISTINCT IF(aide_int.groupe_cat_note='MSS2',ROUND(aide_int.groupe_moyenne,2),NULL)) AS MSS2,
/* — moyennes semestrielles — */
GROUP_CONCAT(DISTINCT IF(aide_int.semestre_cat_note='MGS1',ROUND(aide_int.semestre_moyenne,2),NULL)) AS MGS1,
GROUP_CONCAT(DISTINCT IF(aide_int.semestre_cat_note='MGS2',ROUND(aide_int.semestre_moyenne,2),NULL)) AS MGS2,
/* — rattrapages — */
GROUP_CONCAT(DISTINCT IF(aide_int.groupe_cat_ratt='RES1',aide_int.groupe_ratt,NULL)) AS RES1,
GROUP_CONCAT(DISTINCT IF(aide_int.groupe_cat_ratt='RHS1',aide_int.groupe_ratt,NULL)) AS RHS1,
GROUP_CONCAT(DISTINCT IF(aide_int.groupe_cat_ratt='RIS1',aide_int.groupe_ratt,NULL)) AS RIS1,
GROUP_CONCAT(DISTINCT IF(aide_int.groupe_cat_ratt='RSS1',aide_int.groupe_ratt,NULL)) AS RSS1,
GROUP_CONCAT(DISTINCT IF(aide_int.groupe_cat_ratt='RES2',aide_int.groupe_ratt,NULL)) AS RES2,
GROUP_CONCAT(DISTINCT IF(aide_int.groupe_cat_ratt='RHS2',aide_int.groupe_ratt,NULL)) AS RHS2,
GROUP_CONCAT(DISTINCT IF(aide_int.groupe_cat_ratt='RIS2',aide_int.groupe_ratt,NULL)) AS RIS2,
GROUP_CONCAT(DISTINCT IF(aide_int.groupe_cat_ratt='RSS2',aide_int.groupe_ratt,NULL)) AS RSS2,
MAX(aide_int.moyenne_generale) AS moyenne_generale,
MAX(aide_int.total_coeff) AS total_coeff,
MAX(aide_int.total_ects) AS total_ects,
MAX(aide_int.rattrapage_max) AS rattrapage_max,
MAX(aide_int.AV) AS AV
FROM (
/* ------------------- bloc interne (version 10 juin) ------------------- */
SELECT
e.id_etudiant,
DATE_FORMAT(e.datenai,'%d/%m/%Y') AS date_naissance,
RIGHT(DATABASE(),4) AS annee,
CONCAT(e.nom,' ',e.prenom) AS nom_complet,
CASE
WHEN e.libelle_etat_civil IN ('Monsieur','M.') THEN '1'
WHEN e.libelle_etat_civil IN ('Madame','Mme','Mademoiselle') THEN '2'
ELSE '?'
END AS sexe,
GROUP_CONCAT(DISTINCT v.lib ORDER BY v.ordre) AS situation,
e.diplome_double1 AS double_diplome,
e.diplome_autre1 AS autre_diplome,
IF(MAX(IFNULL(v.niveau,0)) = MAX(IFNULL(v_avant.niveau,0)),1,0) AS redoublement,
IF(GROUP_CONCAT(DISTINCT v.libelle ORDER BY v.ordre) LIKE '%Attaché%','attaché','ingénieur') AS att_ing,
e.bac_annee,
e.bac_serie AS bac_spe1,
NULL AS bac_spe2,
bac_mention.libelle AS bac_mention,
e.concours_origine,
rsp.libelle AS situation_avant,
cpge.libelle AS cpge,
IF(IFNULL(e.bourse_type,'')='',0,1) AS bourse,
e.bourse_type AS bourse_type,
e.bourse_montant,
e.id_pcs_pere,
e.id_pcs_mere,
/* — résultats de groupe (MESx, …) — */
grp.id_periode,
grp.cat_note AS groupe_cat_note,
grp.cat_ratt AS groupe_cat_ratt,
grp.moyenne AS groupe_moyenne,
grp.ratt AS groupe_ratt,
/* — résultats semestriels — */
sem.cat_note AS semestre_cat_note,
sem.moyenne AS semestre_moyenne,
/* — moyennes générales annuelles — */
moy_gen.moyenne_generale,
moy_gen.total_coeff,
moy_gen.total_ects,
moy_gen.rattrapage_max,
/* — validation annuelle — */
IF(com.commentaire LIKE '%année validée%',1,0) AS AV
FROM etudiant e
INNER JOIN voie_etudiant ve ON ve.id_etudiant = e.id_etudiant
INNER JOIN voie v ON v.id_voie = ve.id_voie
AND v.cle_voie = 1
AND v.voie_option = 0
LEFT JOIN commentaire com ON com.id_etudiant = e.id_etudiant
LEFT JOIN ref_cpge cpge ON cpge.id_ref_cpge = e.id_ref_cpge
LEFT JOIN ref_bac_mention bac_mention
ON bac_mention.id_ref_bac_mention = e.id_ref_bac_mention
LEFT JOIN ref_situation_pre rsp ON rsp.id_ref_situation_pre = e.id_ref_situation_pre
/* — année N-1 pour détecter redoublement — */
LEFT JOIN ensai_2024.voie_etudiant ve_avant
ON ve_avant.id_etudiant = e.id_etudiant
LEFT JOIN ensai_2024.voie v_avant ON v_avant.id_voie = ve_avant.id_voie
AND v_avant.cle_voie = 1
/* ---- sous-total par groupe (MESx, …) ---- */
LEFT JOIN (
SELECT
moy.id_etudiant,
CONCAT('M',LEFT(rtm.lib,1),'S',modu.id_periode) AS cat_note,
CONCAT('R',LEFT(rtm.lib,1),'S',modu.id_periode) AS cat_ratt,
modu.id_periode,
SUM(moy.moyenne*m.coefficient)/SUM(m.coefficient) AS moyenne,
SUM(moy.rattrapage) AS ratt
FROM matiere m
INNER JOIN ref_type_matiere rtm ON rtm.id_ref_type_matiere = m.id_type_matiere
INNER JOIN module modu ON modu.id_module = m.id_module
INNER JOIN moyenne moy ON moy.id_matiere = m.id_matiere
WHERE m.bonus = 0
GROUP BY
moy.id_etudiant,
modu.id_periode,
m.id_type_matiere
) AS grp ON grp.id_etudiant = e.id_etudiant
/* ---- sous-total semestriel ---- */
LEFT JOIN (
SELECT
CONCAT('MGS',modu.id_periode) AS cat_note,
modu.id_periode,
mmoy.id_etudiant,
SUM(mmoy.moyenne*mmoy.coefficient)/SUM(mmoy.coefficient) AS moyenne
FROM moyenne_module mmoy
INNER JOIN module modu ON modu.id_module = mmoy.id_module
WHERE IFNULL(modu.id_periode,0) > 0
AND mmoy.moyenne IS NOT NULL
GROUP BY
modu.id_periode,
mmoy.id_etudiant
) AS sem ON sem.id_etudiant = e.id_etudiant
AND sem.id_periode = grp.id_periode
/* ---- moyenne générale annuelle ---- */
LEFT JOIN (
SELECT
id_etudiant,
ROUND(SUM(moyenne*coefficient)/SUM(coefficient),2) AS moyenne_generale,
SUM(coefficient) AS total_coeff,
SUM(credit_ects) AS total_ects,
MAX(rattrapage) AS rattrapage_max
FROM (
SELECT
mm.id_etudiant,
SUM(mm.coefficient*mm.moyenne)/SUM(mm.coefficient)
+ IFNULL(bonus.somme_bonus,0) AS moyenne,
modu.id_periode,
SUM(mm.coefficient) AS coefficient,
SUM(mm.credit_ects_valide) AS credit_ects,
MAX(mm.rattrapage) AS rattrapage
FROM moyenne_module mm
INNER JOIN module modu ON modu.id_module = mm.id_module
LEFT JOIN (
SELECT
m2.id_etudiant,
m.bonus AS id_periode,
SUM(m2.note) AS somme_bonus
FROM moyenne m2
INNER JOIN matiere m ON m.id_matiere = m2.id_matiere
WHERE m.bonus > 0 AND m.id_module = 0
GROUP BY m2.id_etudiant, m.bonus
) AS bonus ON bonus.id_etudiant = mm.id_etudiant
AND modu.id_periode = bonus.id_periode
GROUP BY mm.id_etudiant, modu.id_periode
) AS t
GROUP BY id_etudiant
) AS moy_gen ON moy_gen.id_etudiant = e.id_etudiant
GROUP BY
e.id_etudiant,
grp.cat_note
) AS aide_int
GROUP BY aide_int.id_etudiant
HAVING MGS1 IS NOT NULL OR MGS2 IS NOT NULL) AS aide ON aide.id_etudiant = e.id_etudiant
/* ======================= regroupement final & tri ===================== */ GROUP BY e.id_etudiant, m.id_module, m.id_matiere
ORDER BY voie.voie_lib, e.id_etudiant DESC, m.id_module, m.id_matiere;
2 Requête secondaire
La requête « Scola - Extraction notes - Points de bonus et de jury - Pour Base_eleves »
récupère les points de jury et les bonus.
WITH voie_etud AS ( SELECT ve.id_etudiant FROM voie_etudiant ve INNER JOIN voie v ON v.id_voie = ve.id_voie GROUP BY ve.id_etudiant ),
aide_reduit AS ( SELECT e.id_etudiant FROM etudiant e INNER JOIN voie_etudiant ve ON ve.id_etudiant = e.id_etudiant INNER JOIN voie v ON v.id_voie = ve.id_voie GROUP BY e.id_etudiant ),
moyenne_matiere AS ( SELECT moy.id_etudiant, moy.id_matiere, ROUND(moy.moyenne, 2) AS moyenne_matiere, moy.note, moy.rang, moy.rang_max FROM moyenne moy ),
moyenne_module AS ( SELECT mmoy.id_etudiant, m.id_module, ROUND(mmoy.moyenne, 2) AS moyenne_ue FROM moyenne_module mmoy INNER JOIN matiere m ON m.id_module = mmoy.id_module WHERE mmoy.moyenne IS NOT NULL ),
matiere_module AS ( SELECT m.id_matiere, m.id_module, m.intitule AS matiere, m.code_matiere, m.id_type_matiere, m.bonus AS bonus_type, mo.libelle AS ue, CASE WHEN m.objectifs IS NULL OR m.objectifs LIKE ‘%mso-%’ THEN ’’ ELSE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( m.objectifs, ‘ ’, ’ ‘), ’é’, ‘é’), ‘è’, ‘è’), ‘à’, ‘à’), ‘œ’, ‘œ’), ‘ç’, ‘ç’), ‘“‘,’”’), ’’‘,’’‘),’–‘,’–‘),’&‘,’&‘),’<‘,’<‘),’>‘,’>’) END AS objectifs_matiere FROM matiere m LEFT JOIN module mo ON mo.id_module = m.id_module )
SELECT RIGHT(DATABASE(), 4) AS annee_courante,
/* Identité */
e.id_etudiant,
/* Module / matière */
matiere.ue,
matiere.matiere,
matiere.code_matiere,
matiere.objectifs_matiere,
matiere.bonus_type,
matiere.id_type_matiere,
/* Bonus ou points de jury */
CASE
WHEN matiere.bonus_type > 0 OR matiere.matiere LIKE '%points de jury%' THEN mm.note
ELSE NULL
END AS point_jury,
/* Point bonus s’il y a lieu */
CASE
WHEN matiere.bonus_type IN (1, 2) THEN mm.moyenne_matiere
ELSE 0
END AS point_bonusFROM moyenne_matiere mm JOIN etudiant e ON e.id_etudiant = mm.id_etudiant JOIN matiere_module matiere ON matiere.id_matiere = mm.id_matiere LEFT JOIN moyenne_module mmodule ON mmodule.id_etudiant = e.id_etudiant AND mmodule.id_module = matiere.id_module LEFT JOIN voie_etud voie ON voie.id_etudiant = e.id_etudiant LEFT JOIN aide_reduit aide ON aide.id_etudiant = e.id_etudiant
GROUP BY e.id_etudiant, matiere.id_module, matiere.id_matiere
ORDER BY e.id_etudiant DESC, matiere.id_module, matiere.id_matiere;