Ce document de Travaux Dirigés est spécifiquement conçu pour les étudiants de niveau Licence. Il a pour objectif de développer et de consolider leurs compétences en manipulation de données en utilisant le langage SQL, dans le cadre d'un système de gestion de la scolarité universitaire.
Il aborde, au travers d'exercices pratiques, les concepts clés de SQL, notamment :
- La sélection et le filtrage de données.
- Les jointures entre tables et les fonctions d'agrégation.
- Le regroupement de données et les sous-requêtes.
Modélisation Merise : TD avec solution Manipulation de données Le langage SQL
Télécharger PDFGestion de la Scolarité Universitaire : Exercices Pratiques en SQL
Ce document présente une série d'exercices pratiques visant à manipuler des données universitaires à l'aide du langage SQL. Le niveau de difficulté correspond à celui d'une licence, idéal pour renforcer la compréhension des concepts fondamentaux des bases de données relationnelles et des requêtes SQL.
Contexte : Schéma Relationnel de la Scolarité
Nous travaillons avec le schéma relationnel suivant, représentant une base de données simplifiée pour la gestion de la scolarité universitaire :
- Etudiant (CNE, Nom, DateNaiss, Niveau, Adresse, Ville)
Clé primaire : CNE (Code National de l'Étudiant)
- Enseignant (NomEns, Fonction)
Clé primaire : NomEns
- Module (CodeMod, Titre, NomEns_Responsable)
Clé primaire : CodeMod. NomEns_Responsable est une clé étrangère vers Enseignant(NomEns).
- FiliereModule (Filiere, CodeMod)
Clé primaire composée : (Filiere, CodeMod). CodeMod est une clé étrangère vers Module(CodeMod).
- Intervient (CodeMod, NomEns)
Clé primaire composée : (CodeMod, NomEns). Indique qu'un enseignant enseigne un module. CodeMod est une clé étrangère vers Module(CodeMod). NomEns est une clé étrangère vers Enseignant(NomEns).
- Exam (CNE, CodeMod, Note1)
Clé primaire composée : (CNE, CodeMod). Enregistre les notes d'examen pour les étudiants. CNE est une clé étrangère vers Etudiant(CNE). CodeMod est une clé étrangère vers Module(CodeMod).
- Oral (CNE, CodeMod, Note2)
Clé primaire composée : (CNE, CodeMod). Enregistre les notes d'oral pour les étudiants. CNE est une clé étrangère vers Etudiant(CNE). CodeMod est une clé étrangère vers Module(CodeMod).
- Seance (CodeMod, Jour, Heure, Salle)
Clé primaire composée : (CodeMod, Jour, Heure, Salle). Décrit les séances de cours. CodeMod est une clé étrangère vers Module(CodeMod).
- OccupationSalle (NomEns, Jour, Heure, Salle)
Clé primaire composée : (NomEns, Jour, Heure, Salle). Décrit les occupations de salles par les enseignants. NomEns est une clé étrangère vers Enseignant(NomEns).
Travaux Pratiques : Requêtes SQL
Répondez aux questions suivantes en écrivant les requêtes SQL appropriées. Les solutions sont fournies à titre indicatif et expliquées pour faciliter la compréhension.
Question 1 : Étudiants de niveau 3
Donnez la liste de tous les étudiants inscrits au niveau 3.
SELECT *
FROM Etudiant
WHERE Niveau = 3;Question 2 : Étudiants résidant dans une ville spécifique
Donnez le nom des étudiants qui habitent à Casablanca.
SELECT Nom
FROM Etudiant
WHERE Ville = "Casablanca";Note : Remplacez "Casablanca" par la ville de votre choix.
Question 3 : Enseignants occupant une salle un jour donné
Donnez le nom des enseignants distincts qui occupent la salle 4 le lundi.
SELECT DISTINCT NomEns
FROM OccupationSalle
WHERE (Salle = 4) AND (Jour = "Lundi");Question 4 : Salles avec cours les lundis ou jeudis
Donnez les salles où il y a des cours le lundi ou le jeudi.
SELECT Salle
FROM Seance
WHERE (Jour = "Lundi") OR (Jour = "Jeudi");Question 5 : Étudiants ayant passé un examen spécifique
Donnez le nom des étudiants ayant passé l'examen en "Programmation" ou dans le module de code 6.
SELECT E.Nom
FROM Etudiant E, Exam X, Module M
WHERE (E.CNE = X.CNE)
AND (X.CodeMod = M.CodeMod)
AND ((M.CodeMod = 6) OR (M.Titre = "Programmation"));Note : Cette requête sélectionne les étudiants ayant passé un examen dans le module identifié par le code 6 OU dans un module dont le titre est "Programmation".
Question 6 : Filière associée à un module spécifique
Donnez le nom de la filière comportant le module de code 8.
SELECT Filiere
FROM FiliereModule
WHERE CodeMod = 8;Correction : Le code module 8 est utilisé ici, conformément à la question.
Question 7 : Filière associée à un module par titre
Donnez le nom de la filière comportant le module de titre "Informatique de gestion".
SELECT FM.Filiere
FROM FiliereModule FM, Module M
WHERE FM.CodeMod = M.CodeMod
AND M.Titre = "Informatique de gestion";Correction : Les alias de table ont été utilisés pour plus de clarté et les guillemets manquants ajoutés.
Question 8 : Étudiants avec une bonne note dans un module
Donnez le nom des étudiants ayant obtenu une note supérieure à 12 dans le module de titre "Informatique de gestion".
SELECT E.Nom
FROM Etudiant E, Exam X, Module M
WHERE (E.CNE = X.CNE)
AND (X.CodeMod = M.CodeMod)
AND (X.Note1 > 12)
AND (M.Titre = "Informatique de gestion");Correction : L'opérateur de comparaison `>` a été rétabli et les guillemets manquants autour du titre du module ont été ajoutés.
Question 9 : Nombre d'étudiants ayant passé un oral
Donnez le nombre d'étudiants distincts qui ont passé un oral.
SELECT COUNT(DISTINCT CNE)
FROM Oral;Question 10 : Meilleure note à un examen de module
Donnez la meilleure note de l'examen du module 2.
SELECT MAX(Note1)
FROM Exam
WHERE CodeMod = 2;Question 11 : Étudiants ayant la meilleure note à un examen de module
Donnez le nom des étudiants qui ont obtenu la meilleure note à l'examen du module 2.
SELECT E.Nom
FROM Etudiant E, Exam X
WHERE (E.CNE = X.CNE)
AND (X.CodeMod = 2)
AND X.Note1 = (SELECT MAX(Note1)
FROM Exam
WHERE CodeMod = 2);Note : Cette requête utilise une sous-requête pour d'abord trouver la note maximale du module 2, puis sélectionne les étudiants dont la note correspond à cette valeur.
Question 12 : Filières avec plus de 4 modules
Donnez la liste des filières comprenant plus de 4 modules.
SELECT Filiere
FROM FiliereModule
GROUP BY Filiere
HAVING COUNT(*) > 4;Note : La clause `GROUP BY` regroupe les modules par filière, et `HAVING` filtre ces groupes pour ne retenir que ceux qui contiennent plus de 4 modules.
Question 13 : Nombre de salles avec plus de 4 séances
Donnez le nombre de salles comportant plus de 4 séances.
-- Étape 1 : Identifier les salles ayant plus de 4 séances
-- (Ceci peut impliquer une table temporaire ou une vue selon le SGBD)
/* CREATE TEMPORARY TABLE SallesPlusDe4Seances AS
SELECT Salle
FROM Seance
GROUP BY Salle
HAVING COUNT(*) > 4; */
-- Étape 2 : Compter le nombre de ces salles
SELECT COUNT(DISTINCT Salle)
FROM Seance
GROUP BY Salle
HAVING COUNT(*) > 4;Note : L'approche originale en deux étapes est condensée ici en une seule requête pour obtenir le nombre de salles directement, ce qui est généralement plus efficace. Si une table temporaire était requise par l'environnement d'origine, la syntaxe pourrait varier.
Question 14 : Moyenne générale des étudiants aux examens
Donnez la moyenne générale de chaque étudiant à l'examen.
SELECT E.CNE, E.Nom, AVG(X.Note1) AS MoyenneGenerale
FROM Etudiant E, Exam X
WHERE E.CNE = X.CNE
GROUP BY E.CNE, E.Nom;Correction : Le mot clé `SELECT` a été corrigé. La clause `GROUP BY` est essentielle ici pour calculer la moyenne par étudiant.
FAQ sur la Manipulation de Données SQL
Q1 : Pourquoi utiliser DISTINCT dans une requête SQL ?
R1 : Le mot-clé DISTINCT est utilisé pour éliminer les lignes dupliquées d'un ensemble de résultats. Par exemple, si plusieurs enseignants occupent la même salle le même jour, SELECT DISTINCT NomEns ne retournera le nom de l'enseignant qu'une seule fois, assurant l'unicité des résultats.
Q2 : Quelle est la différence entre WHERE et HAVING ?
R2 : La clause WHERE est utilisée pour filtrer les lignes individuelles d'une table avant que toute agrégation (comme COUNT ou AVG) ne soit effectuée et avant le regroupement par GROUP BY. La clause HAVING, en revanche, est utilisée pour filtrer des groupes de lignes après qu'ils ont été créés par GROUP BY et que les fonctions d'agrégat ont été calculées. Elle agit comme un WHERE pour les groupes.
Q3 : Quand doit-on utiliser les alias de table (par exemple, E pour Etudiant) ?
R3 : Les alias de table sont recommandés lorsque vous travaillez avec plusieurs tables dans une requête (lors de jointures) ou lorsque le nom de la table est long. Ils améliorent la lisibilité de la requête et sont essentiels pour distinguer les colonnes de même nom provenant de différentes tables (par exemple, E.CNE vs X.CNE) pour éviter toute ambiguïté.