ÉTUDE DE CAS – GESTION D’UNE UNIVERSITÉ
Base de données UNIVERSITE :
• ETUDIANT (Num_Etu, Nom, Prenom, Ville, Tel)
• DEPARTEMENT (Code_Dep, NomDep, VilleDep, Budget)
• INSCRIPTION (Num_Inscr, #Num_Etu, #Code_Dep, Annee,
Moyenne)
• STAGE (Num_Stage, #Num_Etu, Sujet, Duree, Date_Stage,
Organisme)
- Clé primaire : soulignée
- Clé étrangère : précédée d’un astérisque (#)
Requêtes SQL
1. Par département, donner la moyenne générale des moyennes des
étudiants, à condition qu’elle soit supérieure à 12.
Résultat : Code_Dep, Moy_Globale
************Correction ***********************
SELECT Code_Dep, AVG(Moyenne) AS Moy_Globale
FROM INSCRIPTION
GROUP BY Code_Dep
HAVING AVG(Moyenne) > 12;
*********************Explication ************************
- SELECT Code_Dep, AVG(Moyenne) AS Moy_Globale
• On veut afficher deux colonnes :
o Code_Dep : le code du département (ou filière,
spécialité, etc.).
o AVG(Moyenne) : la moyenne des moyennes des
étudiants (agrégée par département).
o AS Moy_Globale : on donne un nom d’alias plus clair
à la colonne calculée (AVG(Moyenne) devient
Moy_Globale).
- FROM INSCRIPTION
• On travaille à partir de la table INSCRIPTION, qui contient
probablement les colonnes Code_Dep, Moyenne, etc.
- GROUP BY Code_Dep
• Cela signifie qu’on veut regrouper les lignes par
département (Code_Dep) pour effectuer un calcul agrégé (ici
une moyenne).
- HAVING AVG(Moyenne) > 12
• on garde uniquement les départements dont la moyenne des
moyennes des étudiants est strictement supérieure à 12.
• Toujours Having avec group by
2. Lister les noms des étudiants ayant fait un stage dans la ville de
“Sfax”.
***************************Correction ***********************
SELECT DISTINCT E.Nom
FROM ETUDIANT E, STAGE S
Where E.Num_Etu = S.Num_Etu
And S.Organisme LIKE '%Sfax%';
***************************Explication***********************
- SELECT DISTINCT E.Nom
o On sélectionne les noms des étudiants (E.Nom).
o Le mot-clé DISTINCT permet d’éviter les doublons : chaque
nom apparaîtra une seule fois, même si l’étudiant a plusieurs
stages à Sfax.
- FROM ETUDIANT E, STAGE S
o On travaille à partir de la table ETUDIANT, qu'on appelle ici
E (alias, pour simplifier la suite).
o De même pour le tableau stage
- JOINTURE avec : E.Num_Etu = S.Num_Etu
o On fait une jointure avec la table STAGE.
o Cela permet de relier chaque étudiant à ses stages.
- WHERE S.Organisme LIKE '%Sfax%'
o On filtre les lignes pour ne garder que les stages dont le nom
de l’organisme contient "Sfax".
o Le symbole % est un joker dans SQL :
▪ '%Sfax%' signifie : contient le mot "Sfax" n’importe où
dans le texte (au début, au milieu ou à la fin).
3. Trouver les étudiants inscrits dans un département différent de la
ville où ils habitent.
***************************Correction ***********************
SELECT E.Num_Etu, E.Ville AS Ville_Etu, D.VilleDep AS Ville_Dep
FROM ETUDIANT E, INSCRIPTION I, DEPARTEMENT D
WHERE E.Num_Etu = I.Num_Etu
AND I.Code_Dep = D.Code_Dep
AND E.Ville <> D.VilleDep;
***************************Explication***********************
- SELECT E.Num_Etu, E.Ville AS Ville_Etu, D.VilleDep AS
Ville_Dep
o On sélectionne :
▪ le numéro de l'étudiant (E.Num_Etu),
▪ la ville de résidence de l'étudiant (E.Ville, renommée
en Ville_Etu),
▪ la ville du département (D.VilleDep, renommée en
Ville_Dep).
- FROM ETUDIANT E, INSCRIPTION I, DEPARTEMENT D
o On utilise trois tables : ETUDIANT, INSCRIPTION et
DEPARTEMENT.
- WHERE E.Num_Etu = I.Num_Etu
o Jointure entre l’étudiant et ses inscriptions.
- AND I.Code_Dep = D.Code_Dep
o Jointure entre l’inscription et le département correspondant.
- AND E.Ville <> D.VilleDep
o On sélectionne uniquement les étudiants dont la ville de
résidence est différente de la ville du département où ils sont
inscrits.
4. Afficher pour chaque département le nombre d'étudiants inscrits.
***************************Correction ***********************
SELECT Code_Dep, COUNT(Num_Etu) AS Nb_Etudiants
FROM INSCRIPTION
GROUP BY Code_Dep;
5. . Trouver les étudiants n'ayant jamais fait de stage.
***************************Correction ***********************
SELECT E.Num_Etu, E.Nom, E.Prenom
FROM ETUDIANT E
WHERE NOT EXISTS (
SELECT E.Num_Etu
FROM STAGE S
WHERE S.Num_Etu = E.Num_Etu);
***************************Explication***********************
- WHERE NOT EXISTS (...)
• On applique un filtre : on ne garde que les étudiants pour lesquels
il n'existe aucune ligne dans la table STAGE qui leur est liée.
*************Solution 2 avec Not in***********************
SELECT E.Num_Etu, E.Nom, E.Prenom
FROM ETUDIANT E
WHERE E.Num_Etu NOT IN (
SELECT S.Num_Etu
FROM STAGE S
WHERE S.Num_Etu IS NOT NULL);
6. Afficher les étudiants ayant effectué plus d’un stage.
Champ résultat : Num_Etu, Nom, Nb_Stages
***************************Correction ***********************
SELECT E.Num_Etu, E.Nom, COUNT(*) AS Nb_Stages
FROM ETUDIANT E, STAGE S
WHERE E.Num_Etu = S.Num_Etu
GROUP BY E.Num_Etu, E.Nom
HAVING COUNT(*) > 1;
***************************Explication***********************
- SELECT E.Num_Etu, E.Nom, COUNT(*) AS Nb_Stages
o On sélectionne :
▪ le numéro d’étudiant (E.Num_Etu),
▪ son nom (E.Nom),
▪ et le nombre de stages qu’il a effectués (COUNT(*)),
qu'on renomme en Nb_Stages.
- FROM ETUDIANT E, STAGE S
o On utilise une jointure implicite entre les tables ETUDIANT
et STAGE.
o Cette syntaxe est ancienne ; la condition de jointure doit être
précisée dans le WHERE.
- GROUP BY E.Num_Etu, E.Nom
o On regroupe les lignes par étudiant (identifié par son
numéro et son nom).
o Cela permet d’appliquer des fonctions d’agrégation (comme
COUNT) pour chaque étudiant.
- HAVING COUNT(*) > 1
o On filtre après le regroupement pour ne garder que les
étudiants ayant effectué plus d’un stage.
o HAVING s’utilise toujours après un GROUP BY pour filtrer
sur les résultats agrégés.
7. Utiliser l’opérateur INTERSECT pour extraire les étudiants
présents à la fois parmi ceux ayant un stage et ceux inscrits dans
un département localisé à Mahdia.
***************************Correction***********************
SELECT Num_Etu
FROM STAGE
WHERE Organisme LIKE '%Mahdia%'
INTERSECT
SELECT I.Num_Etu
FROM INSCRIPTION I, DEPARTEMENT D
Where I.Code_Dep = D.Code_Dep
And D.VilleDep = 'Mahdia';
***************************Explication***********************
Partie 1 — Avant le INTERSECT
SELECT Num_Etu
FROM STAGE
WHERE Organisme LIKE '%Mahdia%'
• Sélectionne les numéros d’étudiants (Num_Etu) ayant effectué un
stage dans un organisme dont le nom contient "Mahdia".
• Le LIKE '%Mahdia%' signifie que "Mahdia" peut être n’importe où
dans le nom de l’organisme (début, milieu ou fin).
Partie 2 — Après le INTERSECT
SELECT I.Num_Etu
FROM INSCRIPTION I, DEPARTEMENT D
WHERE I.Code_Dep = D.Code_Dep
AND D.VilleDep = 'Mahdia';
• Sélectionne les étudiants inscrits dans un département dont la ville
(VilleDep) est Mahdia.
• Cela se fait en reliant les tables INSCRIPTION et DEPARTEMENT
grâce à Code_Dep.
INTERSECT
• L'opérateur INTERSECT retourne uniquement les valeurs
communes aux deux sous-requêtes.
• Résultat : les étudiants présents dans les deux listes.
8. Utiliser UNION pour trouver les étudiants ayant un stage OU une
inscription à Bizerte.
**************************Correction***********************
-- Étudiants ayant un stage
SELECT S.Num_Etu
FROM STAGE S
UNION
-- Étudiants inscrits dans un département situé à Bizerte
SELECT I.Num_Etu
FROM INSCRIPTION I, DEPARTEMENT D
Where I.Code_Dep = D.Code_Dep
And D.VilleDep = 'Bizerte';
9. Donner la durée moyenne des stages par étudiant.
Champ résultat : Num_Etu, Moy_Duree
**************************Correction***********************
SELECT Num_Etu, AVG(Duree) AS Moy_Duree
FROM STAGE
GROUP BY Num_Etu;
10.Trouver les départements dans lesquels aucun étudiant n’est
inscrit.
Champ résultat : Code_Dep, NomDep
**************************Correction***********************
SELECT D.Code_Dep, D.NomDep
FROM DEPARTEMENT D
WHERE NOT EXISTS (
SELECT *
FROM INSCRIPTION I
WHERE I.Code_Dep = D.Code_Dep
);
11. Trouver les étudiants qui ont fait un stage dans un organisme
contenant le mot “Informatique”.
Champ résultat : Num_Etu, Nom, Organisme
**************************Correction***********************
SELECT E.Num_Etu, E.Nom, S.Organisme
FROM ETUDIANT E, STAGE S
WHERE E.Num_Etu = S.Num_Etu
AND S.Organisme LIKE '%Informatique%';
12.Trouver les étudiants qui ne sont pas inscrits dans le même
département que "Ahmed ALI".
Pensez à NOT IN + IN ou NOT EXISTS.
***************Correction avec NOT IN ***********************
SELECT Num_Etu
FROM INSCRIPTION
WHERE Code_Dep
NOT IN (
SELECT Code_Dep
FROM ETUDIANT E, INSCRIPTION I
WHERE E.Num_Etu = I.Num_Etu
AND E.Nom = 'ALI' AND E.Prenom = 'Ahmed');
***********************Correction avec NOT EXISTS*****************
SELECT I1.Num_Etu
FROM INSCRIPTION I1
WHERE NOT EXISTS (
SELECT *
FROM INSCRIPTION I2, ETUDIANT E
WHERE I2.Num_Etu = E.Num_Etu
AND E.Nom = 'ALI' AND E.Prenom = 'Ahmed'
AND I1.Code_Dep = I2.Code_Dep);
13.Départements ayant un budget supérieur à tous les départements
de “Sfax”.
***********************Correction ***********************************
SELECT Code_Dep
FROM DEPARTEMENT
WHERE Budget > ALL (
SELECT Budget
FROM DEPARTEMENT
WHERE VilleDep = 'Sfax');
*********************************Explication *****************************
- SELECT Code_Dep
On veut afficher les codes des départements (Code_Dep).
- 2. FROM DEPARTEMENT
On travaille sur la table DEPARTEMENT, qui contient les
informations sur chaque département (y compris leur budget et
leur ville).
- WHERE Budget > ALL (...)
On garde **uniquement les départements dont le budget est
strictement supérieur à tous les budgets des départements situés à
Sfax.
- Sous-requête :
SELECT Budget
FROM DEPARTEMENT
WHERE VilleDep = 'Sfax'
• Cette sous-requête retourne tous les budgets des départements
situés à Sfax.
14.Afficher la liste des étudiants et leur moyenne la plus élevée (s’ils
ont plusieurs inscriptions).
Champ résultat : Num_Etu, Nom, Max_Moyenne
*********************Correction******************
SELECT E.Num_Etu, E.Nom, MAX(I.Moyenne) AS Max_Moyenne
FROM ETUDIANT E, INSCRIPTION I
WHERE E.Num_Etu = I.Num_Etu
GROUP BY E.Num_Etu, E.Nom;
15.Nombre d’étudiants inscrits dans le département « Informatique »
dont la ville n’est pas renseignée.
************************************Correction******************
SELECT COUNT(*) AS Nombre
FROM INSCRIPTION I, DEPARTEMENT D, ETUDIANT E
WHERE I.Code_Dep = D.Code_Dep
And E.Num_Etu = I.Num_Etu
WHERE D.NomDep = 'Informatique' AND E.Ville IS NULL;
16.Étudiants ayant une moyenne > à la somme des budgets de tous
les départements de Tunis.
************************************Correction******************
SELECT DISTINCT I.Num_Etu
FROM INSCRIPTION I
WHERE I.Moyenne > (
SELECT SUM(Budget)
FROM DEPARTEMENT
WHERE VilleDep = 'Tunis');
17.Étudiants dont la moyenne totale est < au budget minimum de
tous les départements.
************************************Correction******************
SELECT Num_Etu
FROM INSCRIPTION
GROUP BY Num_Etu
HAVING SUM(Moyenne) < (
SELECT MIN(Budget)
FROM DEPARTEMENT);
18.Supprimer les stages de l’étudiant« Ahmed Ali ».
************************************Correction******************
DELETE FROM STAGE
WHERE Num_Etu IN (
SELECT Num_Etu FROM ETUDIANT
WHERE Nom = 'Ali' AND Prenom = 'Ahmed');
19.Réduire de 10% la durée des stages pour les étudiants de « Kef ».
************************************Correction******************
UPDATE STAGE
SET Duree = Duree * 0.9
WHERE Num_Etu IN (
SELECT Num_Etu FROM ETUDIANT
WHERE Ville = 'Kef');
20.Trouver les étudiants dont le nom commence par "B" et qui ont une
moyenne supérieure à 12.
************************************Correction******************
SELECT DISTINCT E.Num_Etu, E.Nom, I.Moyenne
FROM ETUDIANT E, INSCRIPTION I
WHERE E.Num_Etu = I.Num_Etu
WHERE E.Nom LIKE 'B%' AND I.Moyenne > 12;
21.Lister les départements qui ont un budget supérieur à la
moyenne des budgets.
************************************Correction******************
SELECT Code_Dep, NomDep, Budget
FROM DEPARTEMENT
WHERE Budget > (
SELECT AVG(Budget) FROM DEPARTEMENT);
22.Trouver les étudiants ayant effectué un stage de plus de 3 mois
(90 jours).
************************************Correction******************
SELECT E.Num_Etu, E.Nom, S.Sujet, S.Duree
FROM ETUDIANT E , STAGE S
WHERE E.Num_Etu = S.Num_Etu
WHERE S.Duree > 90;
23.Trouver le ou les départements ayant le budget maximal
************************************Correction******************
SELECT Code_Dep, NomDep, Budget
FROM DEPARTEMENT
WHERE Budget = (
SELECT MAX(Budget) FROM DEPARTEMENT);
24.Donner le nombre d’inscriptions par année.
************************************Correction******************
SELECT Annee, COUNT(*) AS Nb_Inscrits
FROM INSCRIPTION
GROUP BY Annee
ORDER BY Annee;
25.Trouver les étudiants ayant une inscription mais aucun stage
************************************Correction******************
SELECT DISTINCT E.Num_Etu, E.Nom
FROM ETUDIANT E, INSCRIPTION I
Where E.Num_Etu = I.Num_Etu
WHERE E.Num_Etu NOT IN (
SELECT Num_Etu FROM STAGE);
26.Trouver les étudiants qui ont fait un stage et dont la moyenne est
supérieure à 15.
SELECT DISTINCT E.Num_Etu, E.Nom, I.Moyenne
FROM ETUDIANT E, INSCRIPTION I, STAGE S
WHERE E.Num_Etu = I.Num_Etu
and E.Num_Etu = S.Num_Etu
and I.Moyenne > 15;
27.Donner les étudiants qui ont effectué un stage en 2024
SELECT E.Num_Etu, E.Nom, S.Date_Stage
FROM ETUDIANT E, STAGE S
Where E.Num_Etu = S.Num_Etu
And EXTRACT(YEAR FROM S.Date_Stage) = 2024;
