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