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

SELECT COUNT(*)
FROM Goetz_Inscription
WHERE Id=4

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

SELECT COUNT(DISTINCT Nom)
FROM Goetz_Inscription

[autre version]

SELECT COUNT(*)
FROM
(
  SELECT 1
  FROM Goetz_Inscription
  GROUP BY Nom
) AS td

[autre version]

SELECT COUNT(*)
FROM Goetz_Joueurs
WHERE Identite IN
                (
                  SELECT Nom
                  FROM Goetz_Inscription
                )

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

SELECT COUNT(*)
FROM
(
  SELECT 1
  FROM Goetz_Serie
  GROUP BY Id, Numero
  HAVING COUNT(Num_table) = 3 AND MAX(Num_table) = MIN(Num_table)
) AS td

[autre version]

SELECT COUNT(*)
FROM
(
  SELECT COUNT(DISTINCT Num_table) AS nb_tables
  FROM Goetz_Serie
  GROUP BY Id, Numero
  HAVING COUNT(Num_table) = 3
) AS td
WHERE nb_tables = 1

[autre version]

SELECT COUNT(*)
FROM
(
  SELECT COUNT(*) AS nb_fois
  FROM Goetz_Serie
  GROUP BY Id, Numero, Num_table
) AS td
WHERE nb_fois = 3 
[ou HAVING dans la sous-requête, plus efficace mais moins populaire]

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

SELECT Nom, SUM(Resultat)
FROM Goetz_Serie
JOIN Goetz_Inscription
ON Goetz_Serie.Id = Goetz_Inscription.Id
AND Goetz_Serie.Numero = Goetz_Inscription.Numero
GROUP BY Nom
ORDER BY SUM(Resultat) DESC LIMIT 1

[autre version, tenant compte des égalités]

SELECT Nom, SUM(Resultat)
FROM Goetz_Serie
JOIN Goetz_Inscription
ON Goetz_Serie.Id = Goetz_Inscription.Id AND Goetz_Serie.Numero = Goetz_Inscription.Numero
GROUP BY Nom
HAVING SUM(Resultat) =
                      (
                        SELECT SUM(Resultat)
                        FROM Goetz_Serie
                        JOIN Goetz_Inscription
                        ON Goetz_Serie.Id = Goetz_Inscription.Id AND Goetz_Serie.Numero = Goetz_Inscription.Numero
                        GROUP BY Nom
                        ORDER BY SUM(Resultat) DESC LIMIT 1
                      )

[avec les trois critères pour départager]

SELECT Nom, SUM(Resultat)
FROM Goetz_Serie
JOIN Goetz_Inscription
ON Goetz_Serie.Id = Goetz_Inscription.Id
AND Goetz_Serie.Numero = Goetz_Inscription.Numero
GROUP BY Nom
ORDER BY SUM(Resultat) DESC, SUM(Gagnes) DESC, SUM(Perdus) LIMIT 1

[et donc en tenant compte des égalités]

SELECT Nom, SUM(Resultat)
FROM Goetz_Serie
JOIN Goetz_Inscription
ON Goetz_Serie.Id = Goetz_Inscription.Id AND Goetz_Serie.Numero = Goetz_Inscription.Numero
GROUP BY Nom
HAVING (SUM(Resultat), SUM(Gagnes), SUM(Perdus)) =
                      (
                        SELECT SUM(Resultat), SUM(Gagnes), SUM(Perdus)
                        FROM Goetz_Serie
                        JOIN Goetz_Inscription
                        ON Goetz_Serie.Id = Goetz_Inscription.Id AND Goetz_Serie.Numero = Goetz_Inscription.Numero
                        GROUP BY Nom
                        ORDER BY SUM(Resultat) DESC, SUM(Gagnes) DESC, SUM(Perdus) LIMIT 1
                      )

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

SELECT Id, COUNT(*)
FROM Goetz_Inscription
GROUP BY Id
ORDER BY COUNT(*) DESC LIMIT 1

[autre version, tenant compte des égalités]

SELECT Id, COUNT(*)
FROM Goetz_Inscription
GROUP BY Id
HAVING COUNT(*) =
                (
                  SELECT COUNT(*)
                  FROM Goetz_Inscription
                  GROUP BY Id
                  ORDER BY COUNT(*) DESC LIMIT 1
                )

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

SELECT Id, SUM(Resultat)
FROM Goetz_Serie
GROUP BY Id, Numero
ORDER BY SUM(Resultat) DESC LIMIT 1

[autre version, tenant compte des égalités]

SELECT Id, SUM(Resultat)
FROM Goetz_Serie
GROUP BY Id, Numero
HAVING SUM(Resultat) =
                      (
                        SELECT SUM(Resultat)
                        FROM Goetz_Serie
                        GROUP BY Id, Numero
                        ORDER BY SUM(Resultat) DESC LIMIT 1
                      )

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

SELECT COUNT(*)
FROM
(
  SELECT 1
  FROM Goetz_Serie
  GROUP BY Id, Numero
  HAVING COUNT(Place) = 3 AND COUNT(DISTINCT Place MOD 2) = 1
) AS td

[même principe que l'exercice 3 pour les variantes, dont une présentée tout de même ici :]

SELECT COUNT(*)
FROM
(
  SELECT 1
  FROM Goetz_Serie
  GROUP BY Id, Numero
  HAVING COUNT(Place) = 3 AND SUM(Place) MOD 2 = MIN(Place) MOD 2 AND SUM(Place) MOD 2 = MAX(Place) MOD 2
) AS td

***** Exercice 8 *****

SELECT GI1.Nom, GI2.Nom, COUNT(*) AS nb_fois_ensemble
FROM Goetz_Serie AS GS1
JOIN Goetz_Serie AS GS2
ON GS1.Id = GS2.Id AND GS1.Serie = GS2.Serie
JOIN Goetz_Inscription AS GI1
ON GS1.Id = GI1.Id AND GS1.Numero = GI1.Numero
JOIN Goetz_Inscription AS GI2
ON GS2.Id = GI2.Id AND GS2.Numero = GI2.Numero
WHERE GS1.Num_table = GS2.Num_table AND GS1.Numero < GS2.Numero
GROUP BY GS1.Id, GS1.Numero, GS2.Numero
HAVING COUNT(*) > 1

[deuxième requête]

SELECT COUNT(*)
FROM
(
  SELECT COUNT(*) AS nb_fois_ensemble
  FROM Goetz_Serie AS GS1
  JOIN Goetz_Serie AS GS2
  ON GS1.Id = GS2.Id AND GS1.Serie = GS2.Serie
  WHERE GS1.Num_table = GS2.Num_table AND GS1.Numero < GS2.Numero
  GROUP BY GS1.Id, GS1.Numero, GS2.Numero
  HAVING COUNT(*) > 1
) AS td