Exercices Corrigés:Fonctions,Procédure,Cursors,...

Soit le schéma relationnel suivant:

-- Table Cours
CREATE TABLE Cours (
    ID_Cours INT PRIMARY KEY,
    NomCours VARCHAR(255),
    CodeCours VARCHAR(20)
);

-- Table Enseignant
CREATE TABLE Enseignant (
    ID_Enseignant INT PRIMARY KEY,
    Nom VARCHAR(255),
    Prenom VARCHAR(255),
    Titre VARCHAR(50)
);

-- Table Inscription
CREATE TABLE Inscription (
    ID_Inscription INT PRIMARY KEY,
    ID_Etudiant INT,
    ID_Cours INT,
    Annee INT,
    FOREIGN KEY (ID_Etudiant) REFERENCES Etudiant(ID_Etudiant),
    FOREIGN KEY (ID_Cours) REFERENCES Cours(ID_Cours)
);

Questions & Réponse

Création d'une procédure pour insérer un nouvel étudiant :

DELIMITER //
CREATE PROCEDURE InsertStudent(IN p_Nom VARCHAR(255), IN p_Prenom VARCHAR(255), IN p_Adresse VARCHAR(255))
BEGIN
    INSERT INTO Etudiant(Nom, Prenom, Adresse) VALUES (p_Nom, p_Prenom, p_Adresse);
END //
DELIMITER ;

Création d'une fonction pour récupérer le nombre d'étudiants inscrits à un cours :

DELIMITER //
CREATE FUNCTION GetStudentCountInCourse(p_CourseID INT) RETURNS INT
BEGIN
    DECLARE student_count INT;
    SELECT COUNT(*) INTO student_count FROM Inscription WHERE ID_Cours = p_CourseID;
    RETURN student_count;
END //
DELIMITER ;

Création d'une procédure pour mettre à jour les informations d'un enseignant :

DELIMITER //
CREATE PROCEDURE UpdateTeacher(IN p_TeacherID INT, IN p_NewNom VARCHAR(255), IN p_NewPrenom VARCHAR(255), IN p_NewTitre VARCHAR(255))
BEGIN
    UPDATE Enseignant SET Nom = p_NewNom, Prenom = p_NewPrenom, Titre = p_NewTitre WHERE ID_Enseignant = p_TeacherID;
END //
DELIMITER ;

Création d'une procédure pour supprimer un cours avec des étudiants inscrits :

DELIMITER //
CREATE PROCEDURE DeleteCourseWithEnrollments(IN p_CourseID INT)
BEGIN
    DELETE FROM Inscription WHERE ID_Cours = p_CourseID;
    DELETE FROM Cours WHERE ID_Cours = p_CourseID;
END //
DELIMITER ;

Création d'une procédure utilisant une boucle WHILE pour mettre à jour plusieurs étudiants :

DELIMITER //
CREATE PROCEDURE UpdateStudentAddresses()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE studentID INT;
    DECLARE cur CURSOR FOR SELECT ID_Etudiant FROM Etudiant;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO studentID;
        IF done THEN
            LEAVE read_loop;
        END IF;

        UPDATE Etudiant SET Adresse = CONCAT(Adresse, ', France') WHERE ID_Etudiant = studentID;
    END LOOP;

    CLOSE cur;
END //
DELIMITER ;

Création d'une procédure utilisant une condition IF pour afficher le titre de l'enseignant en fonction de son ID :

DELIMITER //
CREATE PROCEDURE GetTeacherTitleByID(IN p_TeacherID INT)
BEGIN
    DECLARE teacherTitle VARCHAR(255);

    SELECT Titre INTO teacherTitle FROM Enseignant WHERE ID_Enseignant = p_TeacherID;

    IF teacherTitle IS NOT NULL THEN
        SELECT CONCAT('Le titre de l enseignant est : ', teacherTitle) AS Result;
    ELSE
        SELECT 'Aucun enseignant trouvé avec cet ID' AS Result;
    END IF;
END //
DELIMITER ;

Création d'une procédure utilisant une clause CASE pour déterminer le statut d'un étudiant en fonction de son ID :

DELIMITER //
CREATE PROCEDURE GetStudentStatusByID(IN p_StudentID INT)
BEGIN
    DECLARE studentStatus VARCHAR(255);

    SELECT
        CASE
            WHEN Annee > YEAR(CURDATE()) THEN 'Étudiant actif'
            WHEN Annee = YEAR(CURDATE()) THEN 'Nouvel étudiant cette année'
            ELSE 'Ancien étudiant'
        END AS Status
    INTO studentStatus
    FROM Inscription
    WHERE ID_Etudiant = p_StudentID;

    SELECT CONCAT('Le statut de l étudiant est : ', studentStatus) AS Result;
END //
DELIMITER ;

Création d'une procédure utilisant une jointure pour récupérer les cours auxquels un étudiant est inscrit :

DELIMITER //
CREATE PROCEDURE GetCoursesByStudentID(IN p_StudentID INT)
BEGIN
    SELECT Cours.NomCours
    FROM Inscription
    JOIN Cours ON Inscription.ID_Cours = Cours.ID_Cours
    WHERE Inscription.ID_Etudiant = p_StudentID;
END //
DELIMITER ;

Création d'une procédure utilisant une clause GROUP BY pour obtenir le nombre d'étudiants inscrits par année :

DELIMITER //
CREATE PROCEDURE GetStudentCountByYear()
BEGIN
    SELECT Annee, COUNT(*) AS NombreEtudiants
    FROM Inscription
    GROUP BY Annee;
END //
DELIMITER ;

Création d'une procédure utilisant une clause HAVING pour filtrer les cours ayant plus de 10 inscriptions :

DELIMITER //
CREATE PROCEDURE GetCoursesWithMoreThan10Students()
BEGIN
    SELECT Cours.NomCours, COUNT(*) AS NombreInscriptions
    FROM Inscription
    JOIN Cours ON Inscription.ID_Cours = Cours.ID_Cours
    GROUP BY Cours.NomCours
    HAVING NombreInscriptions > 10;
END //
DELIMITER ;

Création d'une procédure utilisant une clause ORDER BY pour obtenir la liste des étudiants triés par nom :

DELIMITER //
CREATE PROCEDURE GetStudentsOrderedByName()
BEGIN
    SELECT *
    FROM Etudiant
    ORDER BY Nom, Prenom;
END //
DELIMITER ;

Création d'une procédure utilisant une clause WHERE pour récupérer les enseignants dont le titre est 'Professeur' :

DELIMITER //
CREATE PROCEDURE GetProfessors()
BEGIN
    SELECT *
    FROM Enseignant
    WHERE Titre = 'Professeur';
END //
DELIMITER ;

Création d'une procédure utilisant une clause JOIN et une clause WHERE pour récupérer les cours enseignés par un enseignant spécifique :

DELIMITER //
CREATE PROCEDURE GetCoursesByTeacherID(IN p_TeacherID INT)
BEGIN
    SELECT Cours.NomCours
    FROM Cours
    JOIN Enseignant ON Cours.ID_Enseignant = Enseignant.ID_Enseignant
    WHERE Enseignant.ID_Enseignant = p_TeacherID;
END //
DELIMITER ;

Création d'une procédure utilisant une boucle FOR pour afficher les n premiers étudiants :

DELIMITER //
CREATE PROCEDURE DisplayTopNEtudents(IN p_N INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    
    FOR i IN 1..p_N DO
        SELECT *
        FROM Etudiant
        WHERE ID_Etudiant = i;
    END FOR;
END //
DELIMITER ;

Création d'une procédure utilisant une transaction pour insérer un nouvel étudiant et un nouvel enregistrement d'inscription :

DELIMITER //
CREATE PROCEDURE InsertStudentAndEnrollment(IN p_Nom VARCHAR(255), IN p_Prenom VARCHAR(255), IN p_Adresse VARCHAR(255), IN p_CourseID INT, IN p_Annee INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Erreur lors de l insertion de l etudiant et de l inscription.';
    END;

    START TRANSACTION;

    INSERT INTO Etudiant(Nom, Prenom, Adresse) VALUES (p_Nom, p_Prenom, p_Adresse);
    SET @last_student_id = LAST_INSERT_ID();

    INSERT INTO Inscription(ID_Etudiant, ID_Cours, Annee) VALUES (@last_student_id, p_CourseID, p_Annee);

    COMMIT;
END //
DELIMITER ;

Création d'une procédure utilisant une instruction IF et une boucle WHILE pour mettre à jour les adresses des étudiants selon une condition spécifique :

DELIMITER //
CREATE PROCEDURE UpdateStudentAddressesBasedOnCondition(IN p_Condition VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE studentID INT;
    DECLARE cur CURSOR FOR SELECT ID_Etudiant FROM Etudiant WHERE Nom LIKE p_Condition;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO studentID;
        IF done THEN
            LEAVE read_loop;
        END IF;

        IF LENGTH(Adresse) > 0 THEN
            UPDATE Etudiant SET Adresse = CONCAT(Adresse, ', France') WHERE ID_Etudiant = studentID;
        END IF;
    END LOOP;

    CLOSE cur;
END //
DELIMITER ;

Création d'une fonction pour calculer la moyenne des notes d'un étudiant dans un cours :

DELIMITER //
CREATE FUNCTION GetAverageGradeForStudent(IN p_StudentID INT, IN p_CourseID INT) RETURNS DECIMAL(5,2)
BEGIN
    DECLARE avgGrade DECIMAL(5,2);

    SELECT AVG(Note) INTO avgGrade
    FROM Notes
    WHERE ID_Etudiant = p_StudentID AND ID_Cours = p_CourseID;

    RETURN avgGrade;
END //
DELIMITER ;

Création d'une procédure utilisant une clause CASE pour attribuer une mention à un étudiant en fonction de sa moyenne :

DELIMITER //
CREATE PROCEDURE AssignGradeToStudent(IN p_StudentID INT, IN p_CourseID INT)
BEGIN
    DECLARE avgGrade DECIMAL(5,2);

    SELECT AVG(Note) INTO avgGrade
    FROM Notes
    WHERE ID_Etudiant = p_StudentID AND ID_Cours = p_CourseID;

    CASE
        WHEN avgGrade >= 16 THEN
            UPDATE Inscription SET Mention = 'Très Bien' WHERE ID_Etudiant = p_StudentID AND ID_Cours = p_CourseID;
        WHEN avgGrade >= 14 THEN
            UPDATE Inscription SET Mention = 'Bien' WHERE ID_Etudiant = p_StudentID AND ID_Cours = p_CourseID;
        WHEN avgGrade >= 12 THEN
            UPDATE Inscription SET Mention = 'Assez Bien' WHERE ID_Etudiant = p_StudentID AND ID_Cours = p_CourseID;
        ELSE
            UPDATE Inscription SET Mention = 'Non Classé' WHERE ID_Etudiant = p_StudentID AND ID_Cours = p_CourseID;
    END CASE;
END //
DELIMITER ;

Création d'une procédure utilisant une instruction LOOP pour récupérer les étudiants inscrits à plus d'un cours :

DELIMITER //
CREATE PROCEDURE GetStudentsEnrolledInMultipleCourses()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE studentID INT;
    DECLARE cur CURSOR FOR
        SELECT ID_Etudiant
        FROM Inscription
        GROUP BY ID_Etudiant
        HAVING COUNT(ID_Cours) > 1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO studentID;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Faites quelque chose avec l'ID de l'étudiant (affichage, mise à jour, etc.).
        SELECT * FROM Etudiant WHERE ID_Etudiant = studentID;
    END LOOP;

    CLOSE cur;
END //
DELIMITER ;

Création d'une fonction utilisant une clause JOIN pour obtenir la liste des cours auxquels un étudiant est inscrit :

DELIMITER //
CREATE FUNCTION GetCoursesByStudentName(IN p_Nom VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
    DECLARE courseList VARCHAR(255);

    SELECT GROUP_CONCAT(NomCours SEPARATOR ', ') INTO courseList
    FROM Inscription
    JOIN Etudiant ON Inscription.ID_Etudiant = Etudiant.ID_Etudiant
    JOIN Cours ON Inscription.ID_Cours = Cours.ID_Cours
    WHERE Etudiant.Nom = p_Nom;

    RETURN courseList;
END //
DELIMITER ;

Création d'une procédure utilisant une instruction IF et une boucle REPEAT pour mettre à jour des données jusqu'à ce qu'une condition soit remplie :

DELIMITER //
CREATE PROCEDURE UpdateStudentDataUntilCondition(IN p_Condition INT)
BEGIN
    REPEAT
        -- Faites quelque chose (par exemple, une mise à jour) avec les étudiants selon une condition.
        UPDATE Etudiant SET Adresse = CONCAT(Adresse, ', France') WHERE ID_Etudiant < p_Condition;
    UNTIL p_Condition = 0
    END REPEAT;
END //
DELIMITER ;

Création d'une procédure utilisant une instruction SIGNAL pour gérer une erreur personnalisée :

DELIMITER //
CREATE PROCEDURE InsertStudentWithCheck(IN p_Nom VARCHAR(255), IN p_Prenom VARCHAR(255), IN p_Adresse VARCHAR(255))
BEGIN
    DECLARE studentCount INT;
    
    SELECT COUNT(*) INTO studentCount FROM Etudiant WHERE Nom = p_Nom AND Prenom = p_Prenom;
    
    IF studentCount > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Un étudiant avec le même nom et prénom existe déjà.';
    ELSE
        INSERT INTO Etudiant(Nom, Prenom, Adresse) VALUES (p_Nom, p_Prenom, p_Adresse);
    END IF;
END //
DELIMITER ;

Création d'une procédure utilisant une boucle FOR pour afficher les cours avec le nombre d'étudiants inscrits :

DELIMITER //
CREATE PROCEDURE DisplayCoursesWithEnrollmentCount()
BEGIN
    DECLARE courseID INT;
    
    FOR courseID IN (SELECT ID_Cours FROM Cours) DO
        SELECT Cours.NomCours, COUNT(*) AS NombreEtudiants
        FROM Inscription
        JOIN Cours ON Inscription.ID_Cours = Cours.ID_Cours
        WHERE Cours.ID_Cours = courseID;
    END FOR;
END //
DELIMITER ;

Création d'une fonction pour calculer la somme des notes d'un étudiant dans tous les cours :

DELIMITER //
CREATE FUNCTION GetTotalGradeForStudent(IN p_StudentID INT) RETURNS DECIMAL(5,2)
BEGIN
    DECLARE totalGrade DECIMAL(5,2);

    SELECT SUM(Note) INTO totalGrade
    FROM Notes
    WHERE ID_Etudiant = p_StudentID;

    RETURN totalGrade;
END //
DELIMITER ;

Création d'une procédure utilisant une clause DISTINCT pour afficher les différents cours auxquels au moins un étudiant est inscrit :

DELIMITER //
CREATE PROCEDURE DisplayDistinctCoursesWithEnrollment()
BEGIN
    SELECT DISTINCT Cours.NomCours
    FROM Inscription
    JOIN Cours ON Inscription.ID_Cours = Cours.ID_Cours;
END //
DELIMITER ;

Création d'une procédure utilisant une clause LIMIT pour récupérer les dix premiers étudiants inscrits :

DELIMITER //
CREATE PROCEDURE GetTopTenStudents()
BEGIN
    SELECT *
    FROM Etudiant
    ORDER BY ID_Etudiant
    LIMIT 10;
END //
DELIMITER ;

Création d'une fonction pour calculer l'âge moyen des étudiants :

DELIMITER //
CREATE FUNCTION GetAverageAge() RETURNS DECIMAL(5,2)
BEGIN
    DECLARE avgAge DECIMAL(5,2);

    SELECT AVG(YEAR(CURDATE()) - YEAR(DateNaissance)) INTO avgAge
    FROM Etudiant;

    RETURN avgAge;
END //
DELIMITER ;

Création d'une procédure utilisant une clause IN pour récupérer les étudiants inscrits à certains cours spécifiques :

DELIMITER //
CREATE PROCEDURE GetStudentsInSpecificCourses()
BEGIN
    SELECT *
    FROM Etudiant
    WHERE ID_Etudiant IN (SELECT DISTINCT ID_Etudiant FROM Inscription WHERE ID_Cours IN (1, 2, 3));
END //
DELIMITER ;

Création d'une procédure utilisant une clause UNION pour récupérer les noms des étudiants et des enseignants :

DELIMITER //
CREATE PROCEDURE GetStudentAndTeacherNames()
BEGIN
    SELECT Nom AS NomEntite, Prenom AS PrenomEntite, 'Etudiant' AS TypeEntite
    FROM Etudiant
    UNION
    SELECT Nom, Prenom, 'Enseignant'
    FROM Enseignant;
END //
DELIMITER ;

Création d'une fonction utilisant une clause LEFT JOIN pour obtenir les cours auxquels un étudiant est inscrit, avec une valeur par défaut pour les étudiants sans inscription :

DELIMITER //
CREATE FUNCTION GetCoursesForStudentWithDefault(IN p_StudentID INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE courseList VARCHAR(255);

    SELECT GROUP_CONCAT(COALESCE(Cours.NomCours, 'Aucun cours') SEPARATOR ', ') INTO courseList
    FROM Etudiant
    LEFT JOIN Inscription ON Etudiant.ID_Etudiant = Inscription.ID_Etudiant
    LEFT JOIN Cours ON Inscription.ID_Cours = Cours.ID_Cours
    WHERE Etudiant.ID_Etudiant = p_StudentID;

    RETURN courseList;
END //
DELIMITER ;

Création d'une procédure utilisant une clause GROUP_CONCAT pour obtenir les cours auxquels un étudiant est inscrit sous forme de chaîne de caractères :

DELIMITER //
CREATE PROCEDURE GetCoursesAsStringForStudent(IN p_StudentID INT)
BEGIN
    DECLARE courseList VARCHAR(255);

    SELECT GROUP_CONCAT(Cours.NomCours SEPARATOR ', ') INTO courseList
    FROM Inscription
    JOIN Cours ON Inscription.ID_Cours = Cours.ID_Cours
    WHERE Inscription.ID_Etudiant = p_StudentID;

    SELECT courseList AS CoursesForStudent;
END //
DELIMITER ;

Création d'une procédure utilisant une clause ORDER BY et LIMIT pour récupérer les trois meilleurs étudiants :

DELIMITER //
CREATE PROCEDURE GetTopThreeStudents()
BEGIN
    SELECT *
    FROM Etudiant
    ORDER BY MoyenneGenerale DESC
    LIMIT 3;
END //
DELIMITER ;

Création d'une fonction pour calculer la médiane des notes d'un étudiant dans un cours :

DELIMITER //
CREATE FUNCTION GetMedianGradeForStudent(IN p_StudentID INT, IN p_CourseID INT) RETURNS DECIMAL(5,2)
BEGIN
    DECLARE medianGrade DECIMAL(5,2);

    SELECT
        IF(COUNT(*) % 2 = 0,
            (MAX(Note) + MIN(Note)) / 2,
            MAX(Note)
        ) INTO medianGrade
    FROM Notes
    WHERE ID_Etudiant = p_StudentID AND ID_Cours = p_CourseID;

    RETURN medianGrade;
END //
DELIMITER ;

Création d'une procédure utilisant une clause CASCADE pour supprimer un enseignant et tous les cours associés :

DELIMITER //
CREATE PROCEDURE DeleteTeacherAndCourses(IN p_TeacherID INT)
BEGIN
    DELETE FROM Enseignant WHERE ID_Enseignant = p_TeacherID;
END //
DELIMITER ;

Création d'une procédure utilisant une clause TRIGGER pour mettre à jour la date de modification d'un étudiant lors de chaque mise à jour :

DELIMITER //
CREATE TRIGGER UpdateStudentModified
BEFORE UPDATE ON Etudiant
FOR EACH ROW
SET NEW.DateModification = NOW();

Création d'une procédure utilisant une clause ENUM pour définir le statut des étudiants :

DELIMITER //
CREATE PROCEDURE UpdateStudentStatus(IN p_StudentID INT, IN p_Status ENUM('Actif', 'Inactif'))
BEGIN
    UPDATE Etudiant SET Statut = p_Status WHERE ID_Etudiant = p_StudentID;
END //
DELIMITER ;

Création d'une procédure utilisant une clause WHILE pour supprimer tous les étudiants inactifs :

DELIMITER //
CREATE PROCEDURE DeleteInactiveStudents()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE studentID INT;
    DECLARE cur CURSOR FOR SELECT ID_Etudiant FROM Etudiant WHERE Statut = 'Inactif';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO studentID;
        IF done THEN
            LEAVE read_loop;
        END IF;

        DELETE FROM Etudiant WHERE ID_Etudiant = studentID;
    END LOOP;

    CLOSE cur;
END //
DELIMITER ;

Création d'une procédure utilisant une clause REPEAT pour incrémenter les notes de tous les étudiants d'un certain pourcentage :

DELIMITER //
CREATE PROCEDURE IncreaseGrades(IN p_Percentage INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE studentID INT;
    DECLARE cur CURSOR FOR SELECT ID_Etudiant FROM Etudiant;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO studentID;
        IF done THEN
            LEAVE read_loop;
        END IF;

        UPDATE Notes SET Note = Note + (Note * p_Percentage / 100) WHERE ID_Etudiant = studentID;
    END LOOP;

    CLOSE cur;
END //
DELIMITER ;

Création d'une fonction utilisant une clause CASE pour obtenir la mention d'un étudiant en fonction de ses notes :

DELIMITER //
CREATE FUNCTION GetStudentMention(IN p_StudentID INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE avgGrade DECIMAL(5,2);

    SELECT AVG(Note) INTO avgGrade
    FROM Notes
    WHERE ID_Etudiant = p_StudentID;

    RETURN
        CASE
            WHEN avgGrade >= 16 THEN 'Très Bien'
            WHEN avgGrade >= 14 THEN 'Bien'
            WHEN avgGrade >= 12 THEN 'Assez Bien'
            ELSE 'Non Classé'
        END;
END //
DELIMITER ;

Création d'une procédure utilisant une clause GROUP BY avec une agrégation pour obtenir le nombre d'étudiants par adresse :

DELIMITER //
CREATE PROCEDURE GetStudentCountByAddress()
BEGIN
    SELECT Adresse, COUNT(*) AS NombreEtudiants
    FROM Etudiant
    GROUP BY Adresse;
END //
DELIMITER ;

Création d'une procédure utilisant une clause FULL OUTER JOIN pour récupérer tous les étudiants, même ceux sans inscription :

DELIMITER //
CREATE PROCEDURE GetStudentsWithOrWithoutEnrollment()
BEGIN
    SELECT Etudiant.*, Inscription.ID_Cours
    FROM Etudiant
    LEFT JOIN Inscription ON Etudiant.ID_Etudiant = Inscription.ID_Etudiant
    UNION
    SELECT Etudiant.*, NULL AS ID_Cours
    FROM Etudiant
    WHERE ID_Etudiant NOT IN (SELECT ID_Etudiant FROM Inscription);
END //
DELIMITER ;

Création d'une procédure utilisant une clause RAND() pour sélectionner aléatoirement un étudiant :

DELIMITER //
CREATE PROCEDURE GetRandomStudent()
BEGIN
    SELECT * FROM Etudiant ORDER BY RAND() LIMIT 1;
END //
DELIMITER ;

Création d'une fonction utilisant une clause AVG() pour calculer la moyenne des notes par cours :

DELIMITER //
CREATE FUNCTION GetAverageGradeByCourse(IN p_CourseID INT) RETURNS DECIMAL(5,2)
BEGIN
    DECLARE avgGrade DECIMAL(5,2);

    SELECT AVG(Note) INTO avgGrade
    FROM Notes
    WHERE ID_Cours = p_CourseID;

    RETURN avgGrade;
END //
DELIMITER ;

Création d'une procédure utilisant une clause WHERE avec LIKE pour rechercher des étudiants par nom :

DELIMITER //
CREATE PROCEDURE SearchStudentsByName(IN p_SearchTerm VARCHAR(255))
BEGIN
    SELECT * FROM Etudiant
    WHERE Nom LIKE CONCAT('%', p_SearchTerm, '%') OR Prenom LIKE CONCAT('%', p_SearchTerm, '%');
END //
DELIMITER ;

Création d'une procédure utilisant une clause CASE pour déterminer si un étudiant est en situation d'échec :

DELIMITER //
CREATE PROCEDURE CheckStudentFailure(IN p_StudentID INT)
BEGIN
    DECLARE avgGrade DECIMAL(5,2);

    SELECT AVG(Note) INTO avgGrade
    FROM Notes
    WHERE ID_Etudiant = p_StudentID;

    CASE
        WHEN avgGrade < 10 THEN
            SELECT 'Étudiant en situation d échec' AS Result;
        ELSE
            SELECT 'Étudiant réussite' AS Result;
    END CASE;
END //
DELIMITER ;

Création d'une fonction utilisant une clause GROUP_CONCAT pour obtenir la liste des cours sous forme de chaîne de caractères :

DELIMITER //
CREATE FUNCTION GetCourseListAsString() RETURNS VARCHAR(255)
BEGIN
    DECLARE courseList VARCHAR(255);

    SELECT GROUP_CONCAT(NomCours SEPARATOR ', ') INTO courseList
    FROM Cours;

    RETURN courseList;
END //
DELIMITER ;

Création d'une procédure utilisant une clause DISTINCT pour récupérer les différentes adresses des étudiants :

DELIMITER //
CREATE PROCEDURE GetDistinctAddresses()
BEGIN
    SELECT DISTINCT Adresse FROM Etudiant;
END //
DELIMITER ;

Création d'une procédure utilisant une clause ORDER BY et LIMIT pour obtenir les trois cours les plus populaires :

DELIMITER //
CREATE PROCEDURE GetTopThreePopularCourses()
BEGIN
    SELECT Cours.NomCours, COUNT(*) AS NombreInscriptions
    FROM Inscription
    JOIN Cours ON Inscription.ID_Cours = Cours.ID_Cours
    GROUP BY Cours.NomCours
    ORDER BY NombreInscriptions DESC
    LIMIT 3;
END //
DELIMITER ;

Création d'une procédure utilisant une clause CASE pour déterminer le semestre d'inscription d'un étudiant en fonction de l'année :

DELIMITER //
CREATE PROCEDURE GetSemesterForStudent(IN p_StudentID INT, IN p_Year INT)
BEGIN
    DECLARE semester VARCHAR(255);

    SELECT
        CASE
            WHEN Annee = p_Year THEN 'Semestre 1'
            WHEN Annee = p_Year - 1 THEN 'Semestre 2'
            ELSE 'Non défini'
        END AS Semester
    INTO semester
    FROM Inscription
    WHERE ID_Etudiant = p_StudentID
    ORDER BY Annee DESC
    LIMIT 1;

    SELECT semester AS SemesterForStudent;
END //
DELIMITER ;

Création d'une fonction utilisant une clause DISTINCT avec une sous-requête pour obtenir la liste des enseignants ayant enseigné au moins un cours :

DELIMITER //
CREATE FUNCTION GetDistinctTeachersWithCourses() RETURNS VARCHAR(255)
BEGIN
    DECLARE teacherList VARCHAR(255);

    SELECT GROUP_CONCAT(Nom, ' ', Prenom SEPARATOR ', ') INTO teacherList
    FROM Enseignant
    WHERE ID_Enseignant IN (SELECT DISTINCT ID_Enseignant FROM Cours);

    RETURN teacherList;
END //
DELIMITER ;

Création d'une procédure utilisant une clause JOIN et une clause WHERE pour récupérer les étudiants inscrits à des cours enseignés par un enseignant spécifique :

DELIMITER //
CREATE PROCEDURE GetStudentsByTeacher(IN p_TeacherID INT)
BEGIN
    SELECT DISTINCT Etudiant.*
    FROM Etudiant
    JOIN Inscription ON Etudiant.ID_Etudiant = Inscription.ID_Etudiant
    JOIN Cours ON Inscription.ID_Cours = Cours.ID_Cours
    WHERE Cours.ID_Enseignant = p_TeacherID;
END //
DELIMITER ;

Création d'une procédure utilisant une clause GROUP BY et une clause HAVING pour obtenir les enseignants ayant enseigné à un nombre spécifique de cours :

DELIMITER //
CREATE PROCEDURE GetTeachersWithCourseCount(IN p_CourseCount INT)
BEGIN
    SELECT Enseignant.Nom, Enseignant.Prenom, COUNT(*) AS NombreCoursEnseignes
    FROM Enseignant
    JOIN Cours ON Enseignant.ID_Enseignant = Cours.ID_Enseignant
    GROUP BY Enseignant.ID_Enseignant
    HAVING NombreCoursEnseignes = p_CourseCount;
END //
DELIMITER ;

Création d'une procédure utilisant une clause UPDATE avec ORDER BY et LIMIT pour mettre à jour les notes des trois meilleurs étudiants d'un cours :

DELIMITER //
CREATE PROCEDURE UpdateTopThreeStudentsGrades(IN p_CourseID INT)
BEGIN
    UPDATE Notes
    SET Note = Note + 5
    WHERE ID_Etudiant IN (
        SELECT ID_Etudiant
        FROM Notes
        WHERE ID_Cours = p_CourseID
        ORDER BY Note DESC
        LIMIT 3
    );
END //
DELIMITER ;

Création d'une procédure utilisant une clause IF pour vérifier l'existence d'un étudiant avant de l'inscrire à un cours :

DELIMITER //
CREATE PROCEDURE EnrollStudentToCourse(IN p_StudentID INT, IN p_CourseID INT, IN p_Annee INT)
BEGIN
    IF EXISTS (SELECT 1 FROM Etudiant WHERE ID_Etudiant = p_StudentID) THEN
        INSERT INTO Inscription(ID_Etudiant, ID_Cours, Annee) VALUES (p_StudentID, p_CourseID, p_Annee);
    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Étudiant non trouvé.';
    END IF;
END //
DELIMITER ;

Création d'une fonction utilisant une clause GROUP_CONCAT avec une sous-requête pour obtenir la liste des cours auxquels un étudiant est inscrit :

DELIMITER //
CREATE FUNCTION GetStudentCoursesAsString(IN p_StudentID INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE courseList VARCHAR(255);

    SELECT GROUP_CONCAT(NomCours SEPARATOR ', ') INTO courseList
    FROM Cours
    WHERE ID_Cours IN (SELECT ID_Cours FROM Inscription WHERE ID_Etudiant = p_StudentID);

    RETURN courseList;
END //
DELIMITER ;

Fonctions et procédures

Les triggers

la Sécurité de données

Exercices

Mysql

SELECT

DataTypes

UPDATE

DELETE

CREATE

DROP

RENAME

ALTER

Tables

Exercices Corrigés:Fonctions,Procédure,Cursors,...