NANG ME-MPENG DAVY MARCEL
19M2261
TP INF322: Langage SQL
Le schéma relationnel de la base de données nommé "TP INF322" est défini comme suit:
Etudiant (matricule, nom, prenom, anNaiss, sexe)
cours (nomCours, nbre_credit, #idEnseignant)
suivie(#matricule, #nomCours, note)
salle(nomSalle, capacite)
dispense(#nomCours, #nomSalle, heureDebut, heureFin)
Enseignant(idEnseignant, nom, prénom, grade)
En NOUS servant de ce schéma relationnel, répondONS aux requetes suivantes:
1) Liste des salles ayant des capacités identiques
REQUETE SQL :
SELECT [Link], [Link]
FROM salle s1
INNER JOIN salle s2 ON [Link] = [Link] AND [Link] <> [Link]
GROUP BY [Link], [Link];
2) Le nom et prénom de l'enseignant qui dispense le cours ayant obtenu la note maximale.
REQUETE SQL :
3) Nom et capacité de la salle la plus utilisée.
REQUETE SQL :
SELECT nomSalle, MAX(capacite) AS capacite
FROM salle;
4) Nom du cours qui dure le plus
REQUETE SQL :
SELECT [Link]
FROM cours c
ORDER BY c.nbre_credit DESC
LIMIT 1;
5) Nom de la filière où on retrouve le plus d'étudiants de sexe Masculin.
REQUETE SQL :
SELECT [Link]
FROM cours C
JOIN suivie S ON [Link] = [Link]
JOIN Etudiant E ON [Link] = [Link]
WHERE [Link] = 'M'
GROUP BY [Link]
ORDER BY COUNT([Link]) DESC
LIMIT 1;
6) Noms et prénoms des étudiants qui suivent tous les cours
REQUETE :
SELECT [Link], [Link]
FROM Etudiant E
WHERE NOT EXISTS (
SELECT nomCours
FROM cours
EXCEPT
SELECT nomCours
FROM suivie
WHERE [Link] = [Link]
);
7) Nom et prénom de l'enseignant qui dispense la matière ayant le plus grand crédit.
REQUETE ;
SELECT [Link], [Link]
FROM Enseignant E
JOIN dispense D ON [Link] = [Link]
JOIN cours C ON [Link] = [Link]
ORDER BY C.nbre_credit DESC
LIMIT 1;
8) En considérant l'année 2024, donnez la liste des cours suivis par les étudiants de plus de
20 ans.
REQUETE :
SELECT DISTINCT [Link]
FROM cours C
JOIN suivie S ON [Link] = [Link]
JOIN Etudiant E ON [Link] = [Link]
WHERE (2024 - [Link]) > 20;
9) Ecrire et exécuter une procédure stockée qui retourne le nombre d'étudiants qui suivent chaque
cours.
REQUETE :
CREATE PROCEDURE NombreEtudiantsParCours()
BEGIN
SELECT nomCours, COUNT(matricule) as nombre_etudiants
FROM suivie
GROUP BY nomCours;
END;
-- Pour exécuter la procédure
CALL NombreEtudiantsParCours();
10)Écrire une procédure stockée qui prend en paramètre un grade et retourne la liste des cours
dispensés par les enseignants
de ce grade. Tester votre procédure avec le grade CC.
REQUETE :
CREATE PROCEDURE CoursParGrade(IN grade_param VARCHAR(10))
BEGIN
SELECT DISTINCT [Link]
FROM cours C
JOIN dispense D ON [Link] = [Link]
JOIN Enseignant E ON [Link] = [Link]
WHERE [Link] = grade_param;
END;
-- Pour tester la procédure avec le grade 'CC'
CALL CoursParGrade('CC');
