CREATE VIEW mavue AS

    SELECT 

        E.nom , 

        E.prenom ,

        M.nom ,

        M.coef ,

        AVG(C.note) AS moyenne

    FROM 

        controles C 

    INNER JOIN 

        eleves E ON C.idEleve = E.idEleve 

    INNER JOIN 

        matieres M ON C.idMatiere = M.idMatiere

    GROUP BY  

        E.nom ,

        E.prenom ,

        M.nom ,

        M.coef