SELECT

    E.idEleve,

    E.nom,

    E.prenom,

    COUNT(*) AS nbControle

FROM

    eleves E

    INNER JOIN controle C ON C.idEleve = E.idEleve

    INNER JOIN matiere M ON M.idMatiere = C.idMatiere

WHERE

    M.nomMatiere = 'NSI'

GROUP BY

    E.idEleve,

    E.nom,

    E.prenom

