MYSQL Examen Corrigés

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

Affichez les noms et prénoms de tous les étudiants inscrits en 2022.

Réponse:

SELECT Nom, Prenom
FROM Etudiant
JOIN Inscription ON Etudiant.ID_Etudiant = Inscription.ID_Etudiant
WHERE Annee = 2022;

Cette requête utilise une jointure (JOIN) pour lier les tables Etudiant et Inscription via l'ID de l'étudiant. Ensuite, elle utilise une clause WHERE pour filtrer les inscriptions de l'année 2022.

Récupérez le nom et le code de tous les cours enseignés par un enseignant spécifique, par exemple, "Dr. Smith".

Réponse:

SELECT Cours.NomCours, Cours.CodeCours
FROM Cours
JOIN Enseignant ON Cours.ID_Enseignant = Enseignant.ID_Enseignant
WHERE Enseignant.Nom = 'Dr. Smith';

Cette requête utilise une jointure entre les tables Cours et Enseignant sur l'ID de l'enseignant. Ensuite, elle filtre les résultats en fonction du nom de l'enseignant.

Ajoutez un nouvel étudiant nommé "John Doe" avec l'adresse "123 Main Street".

Réponse:

INSERT INTO Etudiant (Nom, Prenom, Adresse)
VALUES ('John', 'Doe', '123 Main Street');

Cette instruction SQL utilise la commande INSERT pour ajouter une nouvelle entrée dans la table Etudiant avec le nom, le prénom et l'adresse spécifiés.

Mettez à jour le titre de l'enseignant avec l'ID 3 en "Professeur".

Réponse:

UPDATE Enseignant
SET Titre = 'Professeur'
WHERE ID_Enseignant = 3;

Cette requête utilise la commande UPDATE pour modifier le champ Titre de la table Enseignant pour l'enseignant avec l'ID 3.

Supprimez tous les étudiants qui n'ont pas été inscrits à un cours.

Réponse:

DELETE FROM Etudiant
WHERE ID_Etudiant NOT IN (SELECT DISTINCT ID_Etudiant FROM Inscription);

Cette requête utilise la commande DELETE pour supprimer les étudiants dont l'ID n'est pas présent dans la table Inscription. La sous-requête (SELECT DISTINCT ID_Etudiant FROM Inscription) récupère tous les étudiants inscrits à au moins un cours.

Affichez le nombre total d'étudiants inscrits à chaque cours.

Réponse:

SELECT ID_Cours, COUNT(ID_Etudiant) AS NombreEtudiants
FROM Inscription
GROUP BY ID_Cours;

Cette requête utilise la commande COUNT avec GROUP BY pour compter le nombre d'étudiants inscrits à chaque cours.

Récupérez le nom de tous les cours auxquels l'étudiant avec l'ID 1 est inscrit.

Réponse:

SELECT Cours.NomCours
FROM Cours
JOIN Inscription ON Cours.ID_Cours = Inscription.ID_Cours
WHERE Inscription.ID_Etudiant = 1;

Cette requête utilise une jointure pour obtenir les cours auxquels l'étudiant avec l'ID 1 est inscrit.

Créez une procédure stockée qui prend en entrée le nom d'un cours et affiche le nombre d'étudiants inscrits à ce cours.

Réponse:

DELIMITER //
CREATE PROCEDURE AfficherNombreEtudiantsParCours (IN coursNom VARCHAR(255))
BEGIN
    SELECT COUNT(I.ID_Etudiant) AS NombreEtudiants
    FROM Inscription I
    JOIN Cours C ON I.ID_Cours = C.ID_Cours
    WHERE C.NomCours = coursNom;
END //
DELIMITER ;

Cette procédure stockée prend en entrée le nom d'un cours, puis utilise une jointure avec la table Inscription pour compter le nombre d'étudiants inscrits à ce cours.

Écrivez une requête SQL pour calculer la moyenne des notes pour chaque cours.

Réponse:

SELECT ID_Cours, AVG(Note) AS MoyenneNotes
FROM Notes
GROUP BY ID_Cours;

Cette requête utilise la commande AVG avec GROUP BY pour calculer la moyenne des notes pour chaque cours.

Créez une fonction stockée qui prend en entrée l'ID d'un enseignant et renvoie la liste des cours qu'il enseigne.

Réponse:

DELIMITER //
CREATE FUNCTION GetCoursEnseignes (enseignantID INT)
RETURNS VARCHAR(255)
BEGIN
    DECLARE coursListe VARCHAR(255);
    SELECT GROUP_CONCAT(NomCours SEPARATOR ', ')
    INTO coursListe
    FROM Cours
    WHERE ID_Enseignant = enseignantID;
    RETURN coursListe;
END //
DELIMITER ;

Cette fonction stockée prend en entrée l'ID d'un enseignant et utilise GROUP_CONCAT pour renvoyer la liste des cours qu'il enseigne.

Écrivez une requête SQL pour trouver les étudiants avec la meilleure note dans chaque cours.

Réponse:

SELECT I.ID_Cours, E.ID_Etudiant, E.Nom, E.Prenom, MAX(N.Note) AS MeilleureNote
FROM Inscription I
JOIN Etudiant E ON I.ID_Etudiant = E.ID_Etudiant
JOIN Notes N ON I.ID_Etudiant = N.ID_Etudiant AND I.ID_Cours = N.ID_Cours
GROUP BY I.ID_Cours;

Cette requête utilise une jointure entre Inscription, Etudiant, et Notes pour récupérer les étudiants avec la meilleure note dans chaque cours, en utilisant la fonction MAX pour obtenir la meilleure note.

Créez une procédure stockée qui augmente la note de tous les étudiants d'un cours spécifique de 5 points.

Réponse:

DELIMITER //
CREATE PROCEDURE AugmenterNotes (IN coursID INT)
BEGIN
    UPDATE Notes
    SET Note = Note + 5
    WHERE ID_Cours = coursID;
END //
DELIMITER ;

Cette procédure stockée utilise la commande UPDATE pour augmenter la note de tous les étudiants dans un cours spécifique de 5 points.

Écrivez une requête SQL pour afficher les cours qui ont été enseignés par plus de deux enseignants différents.

Réponse:

SELECT ID_Cours, COUNT(DISTINCT ID_Enseignant) AS NombreEnseignants
FROM Cours
GROUP BY ID_Cours
HAVING NombreEnseignants > 2;

Cette requête utilise la commande COUNT(DISTINCT ID_Enseignant) avec GROUP BY et HAVING pour afficher les cours enseignés par plus de deux enseignants différents.

Créez une procédure stockée qui prend en entrée l'ID d'un étudiant et renvoie son statut de réussite (réussi/échoué) pour l'année en cours.

Réponse:

DELIMITER //
CREATE PROCEDURE VerifierReussite (IN etudiantID INT)
BEGIN
    DECLARE totalNotes DECIMAL(5, 2);
    DECLARE moyenne DECIMAL(5, 2);
    
    SELECT SUM(Note), AVG(Note)
    INTO totalNotes, moyenne
    FROM Notes
    WHERE ID_Etudiant = etudiantID;
    
    IF totalNotes >= 100 AND moyenne >= 10 THEN
        SELECT 'Réussi' AS Statut;
    ELSE
        SELECT 'Échoué' AS Statut;
    END IF;
END //
DELIMITER ;

Cette procédure stockée prend l'ID d'un étudiant en entrée, calcule la somme des notes et la moyenne, puis renvoie le statut de réussite en fonction de certaines conditions.

Écrivez une requête SQL pour trouver le nombre total d'inscriptions par année.

Réponse:

SELECT Annee, COUNT(*) AS NombreInscriptions
FROM Inscription
GROUP BY Annee;

Cette requête utilise GROUP BY pour compter le nombre total d'inscriptions par année.

Créez une procédure stockée qui ajoute un nouvel enseignant avec un titre spécifique.

Réponse:

DELIMITER //
CREATE PROCEDURE AjouterEnseignant (IN nomEnseignant VARCHAR(255), IN prenomEnseignant VARCHAR(255), IN titre VARCHAR(255))
BEGIN
    INSERT INTO Enseignant (Nom, Prenom, Titre)
    VALUES (nomEnseignant, prenomEnseignant, titre);
END //
DELIMITER ;

Cette procédure stockée utilise la commande INSERT pour ajouter un nouvel enseignant avec un nom, prénom et titre spécifiés.

Écrivez une requête SQL pour obtenir la liste distincte des adresses des étudiants.

Réponse:

SELECT DISTINCT Adresse
FROM Etudiant;

Cette requête utilise DISTINCT pour obtenir une liste distincte des adresses des étudiants dans la table Etudiant.

Créez une fonction stockée qui prend en entrée l'ID d'un étudiant et renvoie le nombre total de cours auxquels il est inscrit.

Réponse:

DELIMITER //
CREATE FUNCTION NombreCoursInscrits (etudiantID INT)
RETURNS INT
BEGIN
    DECLARE nombreCours INT;
    SELECT COUNT(*) INTO nombreCours
    FROM Inscription
    WHERE ID_Etudiant = etudiantID;
    RETURN nombreCours;
END //
DELIMITER ;

Cette fonction stockée prend l'ID d'un étudiant en entrée et utilise COUNT pour renvoyer le nombre total de cours auxquels il est inscrit.

Écrivez une requête SQL pour afficher les trois cours les plus populaires (ceux avec le plus grand nombre d'inscriptions).

Réponse:

SELECT ID_Cours, COUNT(*) AS NombreInscriptions
FROM Inscription
GROUP BY ID_Cours
ORDER BY NombreInscriptions DESC
LIMIT 3;

Cette requête utilise GROUP BY pour compter le nombre d'inscriptions par cours, ORDER BY pour les trier par ordre décroissant, et LIMIT 3 pour obtenir les trois cours les plus populaires.

Créez une procédure stockée qui prend en entrée le nom d'un étudiant et affiche la mention obtenue dans chaque cours.

Réponse:

DELIMITER //
CREATE PROCEDURE AfficherMentions (IN nomEtudiant VARCHAR(255))
BEGIN
    SELECT C.NomCours, I.Mention
    FROM Inscription I
    JOIN Cours C ON I.ID_Cours = C.ID_Cours
    JOIN Etudiant E ON I.ID_Etudiant = E.ID_Etudiant
    WHERE E.Nom = nomEtudiant;
END //
DELIMITER ;

Cette procédure stockée prend le nom d'un étudiant en entrée, utilise une jointure pour récupérer les cours et les mentions correspondantes, puis les affiche.

Écrivez une requête SQL pour mettre à jour la mention d'un étudiant lors de la suppression d'une note.

Réponse:

DELIMITER //
CREATE TRIGGER UpdateStudentMentionOnNoteDelete
AFTER DELETE ON Notes
FOR EACH ROW
BEGIN
    DECLARE avgGrade DECIMAL(5,2);

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

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

Ce déclencheur (trigger) est exécuté après la suppression d'une note. Il recalcule la moyenne des notes de l'étudiant concerné et met à jour la mention dans la table Inscription en fonction de cette moyenne.

Écrivez une requête SQL pour mettre à jour la moyenne générale d'un étudiant lors de la suppression d'une note.

Réponse:

DELIMITER //
CREATE TRIGGER UpdateAverageGradeOnNoteDelete
AFTER DELETE ON Notes
FOR EACH ROW
BEGIN
    DECLARE avgGrade DECIMAL(5,2);

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

    UPDATE Etudiant
    SET MoyenneGenerale = avgGrade
    WHERE ID_Etudiant = OLD.ID_Etudiant;
END;
//
DELIMITER ;

Ce déclencheur recalcule la moyenne générale d'un étudiant après la suppression d'une note et met à jour cette moyenne dans la table Etudiant.

Écrivez une requête SQL pour mettre à jour le nombre d'étudiants inscrits lors de la suppression d'une inscription.

Réponse:

DELIMITER //
CREATE TRIGGER UpdateEnrollmentCountOnInscriptionDelete
AFTER DELETE ON Inscription
FOR EACH ROW
BEGIN
    UPDATE Cours
    SET NombreEtudiantsInscrits = (SELECT COUNT(*) FROM Inscription WHERE ID_Cours = OLD.ID_Cours)
    WHERE ID_Cours = OLD.ID_Cours;
END;
//
DELIMITER ;

Ce déclencheur est déclenché après la suppression d'une inscription et met à jour le nombre d'étudiants inscrits dans un cours spécifique.

Écrivez une procédure stockée qui met à jour le nombre d'inscriptions d'un étudiant lors de la suppression de l'étudiant.

Réponse:

DELIMITER //
CREATE PROCEDURE UpdateStudentEnrollmentCountOnStudentDelete (IN etudiantID INT)
BEGIN
    UPDATE Etudiant
    SET NombreInscriptions = 0
    WHERE ID_Etudiant = etudiantID;
END;
//
DELIMITER ;

Cette procédure stockée est appelée lors de la suppression d'un étudiant et met à jour le nombre d'inscriptions de cet étudiant.

Écrivez une requête SQL pour afficher la liste des cours qui ont été enseignés par plus de deux enseignants différents.

Réponse:

SELECT ID_Cours, COUNT(DISTINCT ID_Enseignant) AS NombreEnseignants
FROM Cours
GROUP BY ID_Cours
HAVING NombreEnseignants > 2;

Cette requête utilise GROUP BY et HAVING pour afficher les cours enseignés par plus de deux enseignants différents, en comptant les enseignants distincts pour chaque cours.

Créez une procédure stockée qui prend en entrée le nom d'un cours et supprime tous les étudiants inscrits à ce cours.

Réponse:

DELIMITER //
CREATE PROCEDURE SupprimerEtudiantsDuCours (IN coursNom VARCHAR(255))
BEGIN
    DELETE FROM Inscription
    WHERE ID_Cours = (SELECT ID_Cours FROM Cours WHERE NomCours = coursNom);
END //
DELIMITER ;

Cette procédure stockée prend le nom d'un cours en entrée, trouve l'ID correspondant à ce cours, puis supprime toutes les inscriptions liées à ce cours.

Écrivez une requête SQL pour afficher les cours auxquels aucun étudiant n'est inscrit.

Réponse:

SELECT C.NomCours
FROM Cours C
LEFT JOIN Inscription I ON C.ID_Cours = I.ID_Cours
WHERE I.ID_Etudiant IS NULL;

Cette requête utilise une jointure LEFT JOIN pour obtenir tous les cours, puis utilise WHERE pour filtrer ceux auxquels aucun étudiant n'est inscrit.

Créez une procédure stockée qui met à jour le titre de tous les enseignants en ajoutant "Senior" devant leur titre actuel.

Réponse:

DELIMITER //
CREATE PROCEDURE MettreAJourTitresEnseignants()
BEGIN
    UPDATE Enseignant
    SET Titre = CONCAT('Senior ', Titre);
END //
DELIMITER ;

Cette procédure stockée utilise la fonction CONCAT pour ajouter "Senior" devant le titre actuel de tous les enseignants.

Écrivez une requête SQL pour obtenir la liste des étudiants qui sont inscrits à tous les cours.

Réponse:

SELECT E.Nom, E.Prenom
FROM Etudiant E
WHERE NOT EXISTS (
    SELECT DISTINCT C.ID_Cours
    FROM Cours C
    WHERE NOT EXISTS (
        SELECT I.ID_Etudiant
        FROM Inscription I
        WHERE I.ID_Etudiant = E.ID_Etudiant AND I.ID_Cours = C.ID_Cours
    )
);

Cette requête utilise une clause NOT EXISTS pour sélectionner les étudiants qui sont inscrits à tous les cours.

Créez une fonction stockée qui prend en entrée l'ID d'un étudiant et retourne le nombre de cours pour lesquels il a obtenu une note supérieure à la moyenne de ce cours.

Réponse:

DELIMITER //
CREATE FUNCTION NombreCoursAvecNoteSupMoyenne (etudiantID INT)
RETURNS INT
BEGIN
    DECLARE countCours INT;
    SELECT COUNT(DISTINCT N.ID_Cours)
    INTO countCours
    FROM Notes N
    JOIN Inscription I ON N.ID_Etudiant = I.ID_Etudiant AND N.ID_Cours = I.ID_Cours
    WHERE N.Note > (SELECT AVG(Note) FROM Notes WHERE ID_Cours = N.ID_Cours)
    AND I.ID_Etudiant = etudiantID;
    RETURN countCours;
END //
DELIMITER ;

Cette fonction stockée prend l'ID d'un étudiant en entrée, utilise une jointure avec la table Notes et Inscription pour compter les cours où l'étudiant a obtenu une note supérieure à la moyenne de ce cours.

Créez une procédure stockée qui attribue une mention d'honneur ("Distinction") à tous les étudiants ayant une moyenne générale supérieure à 16.

Réponse:

DELIMITER //
CREATE PROCEDURE AttribuerMentionDistinction()
BEGIN
    UPDATE Etudiant
    SET Mention = 'Distinction'
    WHERE MoyenneGenerale > 16;
END //
DELIMITER ;

Cette procédure stockée utilise la commande UPDATE pour attribuer la mention "Distinction" à tous les étudiants ayant une moyenne générale supérieure à 16.

Écrivez une requête SQL pour afficher les cours qui ont été enseignés par le même enseignant pendant deux années consécutives.

Réponse:

SELECT DISTINCT C1.NomCours
FROM Cours C1
JOIN Cours C2 ON C1.ID_Enseignant = C2.ID_Enseignant
WHERE YEAR(C1.Annee) = YEAR(C2.Annee) - 1;

Cette requête utilise une jointure pour lier les cours enseignés par le même enseignant pendant deux années consécutives.

Créez une fonction stockée qui prend en entrée le nom d'un enseignant et retourne le nombre total de cours qu'il a enseignés.

Réponse:

DELIMITER //
CREATE FUNCTION NombreCoursEnseignes (enseignantNom VARCHAR(255))
RETURNS INT
BEGIN
    DECLARE countCours INT;
    SELECT COUNT(*)
    INTO countCours
    FROM Cours C
    JOIN Enseignant E ON C.ID_Enseignant = E.ID_Enseignant
    WHERE E.Nom = enseignantNom;
    RETURN countCours;
END //
DELIMITER ;

Cette fonction stockée prend le nom d'un enseignant en entrée, utilise une jointure avec la table Cours et Enseignant pour compter le nombre total de cours qu'il a enseignés.

Écrivez une requête SQL pour afficher le pourcentage d'étudiants ayant réussi chaque cours.

Réponse:

SELECT C.NomCours, COUNT(DISTINCT I.ID_Etudiant) * 100 / COUNT(DISTINCT E.ID_Etudiant) AS PourcentageReussite
FROM Cours C
LEFT JOIN Inscription I ON C.ID_Cours = I.ID_Cours
LEFT JOIN Etudiant E ON I.ID_Etudiant = E.ID_Etudiant
WHERE I.Mention = 'Réussi'
GROUP BY C.NomCours;

Cette requête utilise une jointure LEFT JOIN pour obtenir le nombre d'étudiants ayant réussi chaque cours et calcule le pourcentage de réussite.

Créez une procédure stockée qui supprime tous les enseignants n'ayant enseigné aucun cours.

Réponse:

DELIMITER //
CREATE PROCEDURE SupprimerEnseignantsSansCours()
BEGIN
    DELETE FROM Enseignant
    WHERE ID_Enseignant NOT IN (SELECT DISTINCT ID_Enseignant FROM Cours);
END //
DELIMITER ;

Cette procédure stockée utilise la commande DELETE pour supprimer tous les enseignants qui n'ont enseigné aucun cours.

Fonctions et procédures

Les triggers

la Sécurité de données

Exercices

Mysql

SELECT

DataTypes

UPDATE

DELETE

CREATE

DROP

RENAME

ALTER

Tables

MYSQL Examen Corrigés