***** Exercice 1 *****

SELECT Nom, Prenom
FROM Notes
JOIN Etudiants
ON Etudiant = Id
WHERE Note =
            (
              SELECT MAX(Note)
              FROM Notes
              JOIN Etudiants
              ON Etudiant = Id
              WHERE Examen=2 AND Classe = 'MPSI2'
            )
AND Examen=2 AND Classe='MPSI2'

***** Exercice 2 *****

SELECT MAX(Moyenne)
FROM
(
  SELECT SUM(Note*Coeff)/SUM(Coeff) AS Moyenne
  FROM Examens
  JOIN Notes
  ON Id = Examen
  GROUP BY Etudiant
) AS td

***** Exercice 3 *****

SELECT COUNT(*)
FROM
(
  SELECT SUM(Note*Coeff)/SUM(Coeff) AS Moyenne
  FROM Examens
  JOIN Notes
  ON Examens.Id = Examen
  JOIN Etudiants
  ON Etudiants.Id = Etudiant
  WHERE Classe="MPSI1"
  GROUP BY Etudiant
  HAVING Moyenne < 10
) AS td

***** Exercice 4 *****

SELECT Classe
FROM
(
  SELECT Classe, SUM(Note*Coeff)/SUM(Coeff) AS Moyenne
  FROM Examens
  JOIN Notes
  ON Examens.Id = Examen
  JOIN Etudiants
  ON Etudiant = Etudiants.Id
  GROUP BY Etudiant
) AS td
GROUP BY Classe
ORDER BY AVG(Moyenne) DESC LIMIT 1

***** Exercice 5 *****

SELECT Classe
FROM Etudiants
JOIN Notes
ON Id = Etudiant
WHERE Examen=1 AND Note =
                        (
                          SELECT MAX(Note)
                          FROM Notes
                          WHERE Examen=1
                        )

***** Exercice 6 *****

SELECT Classe, COUNT(*) AS Majors
FROM Etudiants
JOIN Notes
ON Etudiant = Etudiants.Id
WHERE (Examen,Note) IN
                      (
                        SELECT Examen, MAX(Note)
                        FROM Notes
                        GROUP BY Examen
                      )
GROUP BY Classe

***** Exercice 7 *****

SELECT Classe FROM
(
  SELECT Classe, SUM(Note*Coeff)/SUM(Coeff) AS Moyenne
  FROM Examens
  JOIN Notes
  ON Examens.Id = Examen
  JOIN Etudiants
  ON Etudiant = Etudiants.Id
  GROUP BY Etudiant
) AS td
GROUP BY Classe
ORDER BY AVG(Moyenne > 10) DESC LIMIT 1

[autre version]

SELECT td.Classe, COUNT(*)/Effectif AS Taux_au_dessus
FROM
(
  SELECT Classe, SUM(Note*Coeff)/SUM(Coeff) AS Moyenne
  FROM Examens
  JOIN Notes
  ON Examens.Id = Examen
  JOIN Etudiants
  ON Etudiants.Id = Etudiant
  GROUP BY Etudiant
) AS td
JOIN
(
  SELECT Classe, COUNT(*) AS Effectif
  FROM Etudiants
  GROUP BY Classe
) AS td2
ON td.Classe = td2.Classe
WHERE Moyenne >= 10
GROUP BY td.Classe
ORDER BY Taux_au_dessus DESC