Exercice (6 points) — dans l'esprit des sessions 2023–2025, section Sciences de l'informatique
L'agence Patrimoine Vivant organise des visites guidées de groupes dans les musées tunisiens. Elle utilise la base de données Gestion_Musees :
- Musee ( codeMusee , nomMusee , ville )
- Guide ( codeGuide , nomGuide , langue )
- Visite ( numVisite , dateVisite , nbVisiteurs , codeMusee# , codeGuide# )
| Champ | Description |
|---|---|
| nomMusee | Nom d'un musée, chaîne de 40 caractères. |
| langue | Langue dans laquelle un guide commente les visites, chaîne de 15 caractères. |
| dateVisite | Date d'une visite. |
| nbVisiteurs | Nombre de personnes d'un groupe, entier. |
Extraits des tables :
Table Musee
| codeMusee | nomMusee | ville |
|---|---|---|
| M01 | Musée du Bardo | Tunis |
| M02 | Musée de Carthage | Carthage |
| M03 | Musée d'El Jem | El Jem |
| M04 | Musée de Sousse | Sousse |
Table Guide
| codeGuide | nomGuide | langue |
|---|---|---|
| G1 | Amri Sami | Anglais |
| G2 | Chaouch Leila | Français |
| G3 | Ferjani Olfa | Italien |
| G4 | Sahli Youssef | Allemand |
Table Visite
| numVisite | dateVisite | nbVisiteurs | codeMusee | codeGuide |
|---|---|---|---|---|
| 1 | 2025-03-02 | 25 | M01 | G2 |
| 2 | 2025-03-02 | 40 | M03 | G1 |
| 3 | 2025-03-05 | 18 | M01 | G3 |
| 4 | 2025-03-09 | 32 | M02 | G2 |
| 5 | 2025-03-12 | 30 | M01 | G1 |
| 6 | 2025-03-15 | 22 | M03 | G1 |
| 7 | 2025-03-16 | 15 | M03 | G3 |
Requête R :
SELECT nomMusee,
COUNT(numVisite) AS nbGroupes,
SUM(nbVisiteurs) AS total
FROM Musee M, Visite V
WHERE M.codeMusee = V.codeMusee
GROUP BY nomMusee;Requête R2 :
SELECT codeGuide, nomGuide
FROM Guide
WHERE codeGuide IN (SELECT codeGuide
FROM Visite
WHERE codeMusee = 'M03');Liste des rôles (question 5) :
- a. Modifier la langue de tous les guides.
- b. Afficher la date et le nombre de visiteurs des visites du musée dont le nom est « Tunis ».
- c. Ajouter un nouveau guide avec des valeurs spécifiques.
- d. Supprimer les guides dont le nom contient la lettre « S ».
- e. Modifier le type du champ « nbVisiteurs » de la table « Visite ».
- f. Afficher la date et le nombre de visiteurs des visites effectuées dans les musées situés à Tunis.
- g. Modifier la valeur par défaut du champ « nbVisiteurs » de la table « Visite ».
- h. Supprimer les guides dont le nom commence par la lettre « S ».
- i. Ajouter une contrainte de domaine sur un champ de la table « Visite ».
- j. Modifier la langue du guide dont le code est spécifié.
Requêtes de la question 5 :
-- Q1
INSERT INTO Guide
VALUES ('G5', 'Hamdi Sonia', 'Espagnol');
-- Q2
SELECT dateVisite, nbVisiteurs
FROM Visite V, Musee M
WHERE V.codeMusee = M.codeMusee
AND ville = 'Tunis';
-- Q3
UPDATE Guide SET langue = 'Anglais'
WHERE codeGuide = 'G3';
-- Q4
DELETE FROM Guide
WHERE nomGuide LIKE 'S%';
-- Q5
ALTER TABLE Visite
ADD CHECK (nbVisiteurs BETWEEN 1 AND 50);
-- Q6
ALTER TABLE Visite
MODIFY nbVisiteurs INT DEFAULT 20;- En se basant sur les extraits fournis, donner le résultat de l'exécution de la requête R, en indiquant les noms des colonnes affichées.
- En déduire le rôle de la requête R.
- Réécrire la requête R en apportant les modifications nécessaires pour n'afficher que les musées ayant accueilli, au total, au moins 70 visiteurs.
- On s'intéresse à la requête R2.
- Donner le résultat de son exécution sur les extraits fournis.
- En déduire son rôle.
- Donner une requête SQL équivalente à R2, sans utiliser l'opérateur IN.
- Pour chacune des requêtes Q1 à Q6, donner la lettre correspondant à son rôle dans la liste proposée.
Voir la correction commentéeAprès avoir posé votre démarche
1. Idée : la jointure ne garde que les visites appariées à un musée ; GROUP BY nomMusee forme un groupe par musée visité, COUNT compte ses visites et SUM additionne ses visiteurs.
| nomMusee | nbGroupes | total |
|---|---|---|
| Musée du Bardo | 3 | 73 (25 + 18 + 30) |
| Musée d'El Jem | 3 | 77 (40 + 22 + 15) |
| Musée de Carthage | 1 | 32 |
Le Musée de Sousse n'apparaît pas : aucune visite ne le référence dans l'extrait. Sans ORDER BY, l'ordre des lignes n'est pas imposé.
2. La requête R affiche, pour chaque musée ayant reçu au moins une visite, son nom, le nombre de groupes accueillis et le nombre total de visiteurs.
3. Idée : la condition porte sur un total calculé par groupe : elle se place dans HAVING, après GROUP BY, jamais dans WHERE.
SELECT nomMusee,
COUNT(numVisite) AS nbGroupes,
SUM(nbVisiteurs) AS total
FROM Musee M, Visite V
WHERE M.codeMusee = V.codeMusee
GROUP BY nomMusee
HAVING SUM(nbVisiteurs) >= 70;Sur l'extrait, restent le Musée du Bardo (73) et le Musée d'El Jem (77) ; le Musée de Carthage (32) est éliminé.
4.a Idée : exécuter d'abord la sous-requête : elle renvoie les guides des visites du musée M03, soit G1, G1 et G3 (visites 2, 6 et 7).
| codeGuide | nomGuide |
|---|---|
| G1 | Amri Sami |
| G3 | Ferjani Olfa |
4.b R2 affiche le code et le nom des guides ayant encadré au moins une visite au musée de code « M03 ».
4.c Idée : IN se remplace par une jointure Guide–Visite ; G1 ayant encadré deux visites à M03, la jointure le produirait deux fois : DISTINCT rétablit le résultat de R2.
SELECT DISTINCT G.codeGuide, nomGuide
FROM Guide G, Visite V
WHERE G.codeGuide = V.codeGuide
AND codeMusee = 'M03';5. Idée : pour chaque requête, vérifier la commande, la colonne sur laquelle porte le critère et la forme exacte du motif LIKE.
| Requête | Réponse | Justification |
|---|---|---|
| Q1 | c | insertion de valeurs données dans Guide |
| Q2 | f | le critère porte sur la ville du musée ; b supposerait nomMusee = 'Tunis' |
| Q3 | j | le WHERE vise un seul guide ; a correspondrait à un UPDATE sans WHERE |
| Q4 | h | 'S%' : le nom commence par S ; « contient » (d) s'écrirait '%S%' |
| Q5 | i | CHECK ajoute une contrainte de domaine |
| Q6 | g | nbVisiteurs reste de type INT (e ne convient pas) : seule la valeur par défaut change |