Modèle de concours : Ingénieurs d'État — 1er grade — Haut-Commissariat au Plan 2021

Ce modèle correspond au concours de recrutement des ingénieurs d'État du 1er grade du Haut-Commissariat au Plan (HCP), session du dimanche 2 mai 2021, pour les spécialités génie logiciel et systèmes d'information, ingénierie de développement des services numériques, ingénierie des données et informatique décisionnelle. Documents et appareils électroniques interdits ; la durée est [illisible dans le scan]. La partie visible du scan comporte deux exercices de QCM (3 points chacun, une seule réponse valide) : le premier sur les entrepôts de données et OLAP, le second sur des requêtes SQL. Voici une correction méthodologique proposée.

💡 Avant de commencer : pour les questions SQL, ne survolez pas les requêtes. Vérifiez d'abord la validité syntaxique (un NOT IN (SELECT *) est faux dès que la sous-requête renvoie plusieurs colonnes, et NOT EXIST sans S n'existe pas), puis la logique. Les questions du type « tous les livres » ou « tous les adhérents » relèvent toujours de la division relationnelle, avec un double NOT EXISTS. Pour la question sur GROUP BY, testez la formule sur chaque tuple proposé.

Exercice 1 : entrepôts de données et OLAP

Q1 — Source des métadonnées du cube OLAP

• Réponse : d) les réponses a et b. Le cube s'appuie sur un modèle multidimensionnel, en étoile comme en flocon, d'où il tire ses dimensions et ses mesures. Une base de données « standard » (normalisée, transactionnelle) n'est pas conçue pour cela.

Q2 — Opération OLAP qui calcule toutes les relations de données pour une ou plusieurs dimensions

• Réponse : c) roll-up, c'est-à-dire l'agrégation vers un niveau supérieur de la hiérarchie (jour, puis mois, puis année) avec calcul des totaux. Slice fixe la valeur d'une dimension, Dice extrait un sous-cube et Pivot ne fait que réorienter les axes d'affichage.

Q3 — « Une base multidimensionnelle est normalisée »

• Réponse : b) Faux. Le modèle multidimensionnel est volontairement dénormalisé, notamment les dimensions du schéma en étoile, pour accélérer les requêtes d'analyse.

Q4 — « Les tables de dimensions servent d'axes d'analyse »

• Réponse : a) Vrai. Les dimensions (temps, produit, région) fournissent les axes d'analyse, et la table de faits porte les mesures (ventes).

Q5 — Nombre de lignes avec GROUP BY, WITH CUBE et WITH ROLLUP

• Q1 (GROUP BY simple) : n1 × n2 × n3.
• Q2 (WITH CUBE) : toutes les combinaisons, plus une valeur « ALL » par dimension : (n1+1)(n2+1)(n3+1).
• Q3 (WITH ROLLUP) : sous-totaux hiérarchiques : n1n2n3 + n1n2 + n1 + 1.
• Test de (c) = (5, 10, 10, 500, 1000, 550) : Q1 = 500 est correct, mais CUBE = 6 × 11 × 11 = 726 ≠ 1000, donc (c) est rejetée.
• Test de (d) = (4, 7, 3, 84, 160, 117) : Q1 = 4 × 7 × 3 = 84 ✓ ; CUBE = 5 × 8 × 4 = 160 ✓ ; ROLLUP = 84 + 28 + 4 + 1 = 117 ✓.
• Réponse : d. Les propositions a et b sont [illisible dans le scan], mais d vérifie les trois formules.

Exercice 2 : requêtes SQL (base d'une bibliothèque)

Tables : Emprunt(Adherent, Livre, DateEmprunt, DateRetourPrevue, DateRetourEffective) et Retard(Adherent, Livre, DateEmprunt, PénalitéRetard).

Q1 — Adhérents n'ayant jamais rendu de livre en retard

• Réponse : b) SELECT Adherent FROM Emprunt WHERE Adherent NOT IN (SELECT Adherent FROM Retard).
• (a) est fausse, car NOT IN (SELECT * …) compare une colonne à quatre. (c) est syntaxiquement fausse : NOT EXIST n'existe pas, et NOT EXISTS ne se place pas après un nom de colonne. Remarque : un DISTINCT serait bienvenu pour éviter les doublons.

Q2 — Adhérents ayant emprunté tous les livres

• Réponse : b), la forme en double NOT EXISTS : il n'existe aucun livre u pour lequel il n'existe pas d'emprunt v par l'adhérent t (v.Adherent = t.Adherent AND v.Livre = u.Livre).
• (a) et (c) utilisent NOT IN (SELECT * …), ce qui n'est pas valide ; (a) n'indique même pas de colonne avant NOT IN.

Q3 — Livres empruntés par tous les adhérents

• Requête correcte en théorie : SELECT t.Livre FROM Emprunt t WHERE NOT EXISTS (SELECT * FROM Emprunt u WHERE NOT EXISTS (SELECT * FROM Emprunt v WHERE v.Livre = t.Livre AND v.Adherent = u.Adherent)).
• Seule la première proposition (a) a la structure de cette division (double NOT EXISTS). Mais la condition telle qu'elle est transcrite (u.Livre = t.Livre au lieu de v.Livre = t.Livre) est [illisible dans le scan] : vérifiez-la sur l'original. La proposition b (double EXISTS) renvoie pratiquement tous les livres et la proposition c ne renvoie aucune ligne.

💡 Synthèse : Exercice 1 : Q1 = d, Q2 = c (roll-up), Q3 = b (faux), Q4 = a (vrai), Q5 = d. Exercice 2 : Q1 = b, Q2 = b, Q3 = a (division par double NOT EXISTS). Pour préparer le concours d'ingénieur d'État du HCP, révisez la modélisation multidimensionnelle (étoile, flocon, faits et dimensions), les opérations OLAP et GROUP BY CUBE/ROLLUP/GROUPING SETS, le SQL avancé (sous-requêtes corrélées, division relationnelle, piège de NOT IN face aux NULL) ainsi que l'ETL et la qualité des données, qui sont au cœur du métier de la statistique publique.