-- Question 1
SELECT nom, COUNT(idPokemon)
FROM Pokemon, Apparition
WHERE id = idPokemon (+)
GROUP BY nom, id
ORDER BY COUNT(idPokemon);

-- Question 2
SELECT nom, COUNT(idPokemon)
FROM Pokemon, Apparition
WHERE id = idPokemon (+)
GROUP BY nom, id
HAVING COUNT(idPokemon) >
    (SELECT AVG(COUNT(idPokemon))
    FROM Pokemon, Apparition
    WHERE id = idPokemon (+)
    GROUP BY nom, id);

-- Question 3	
SELECT pseudonyme, sexe, niveau, COUNT(id)
FROM Joueur, Pokemon
WHERE pseudonyme = idJoueur (+)
GROUP BY pseudonyme, sexe, niveau
ORDER BY COUNT(id);

-- Question 4
SELECT nom
FROM Pokemon, Apparition
WHERE Pokemon.id = idPokemon
GROUP BY nom, Pokemon.id
HAVING COUNT(DISTINCT idEmplacement) =
    (SELECT COUNT(*)
    FROM Emplacement);

-- Question 5
SELECT pseudonyme
FROM Joueur, Pokemon
WHERE pseudonyme = idJoueur
GROUP BY pseudonyme
HAVING COUNT(DISTINCT espece) =
    (SELECT COUNT(DISTINCT espece)
    FROM Pokemon);

-- Question 6
SELECT COUNT(idJoueur)
FROM Pokemon, Apparition, Emplacement
WHERE Pokemon.id = idPokemon AND idEmplacement = Emplacement.id AND latitude =
    (SELECT MAX(latitude)
    FROM Emplacement);

-- Question 7
SELECT Equipe.nom
FROM Equipe, Defense
WHERE Equipe.id = idEquipe
GROUP BY Equipe.nom, Equipe.id
HAVING COUNT(idArene) =
    (SELECT MAX(COUNT(idArene))
    FROM Defense
    GROUP BY idEquipe);

-- Question 8
SELECT horaire
FROM Apparition, Pokemon, Joueur, Defense
WHERE idPokemon = id AND idJoueur = pseudonyme AND Joueur.idEquipe = Defense.idEquipe
GROUP BY horaire, Defense.idEquipe
HAVING COUNT(idArene) =
    (SELECT MAX(COUNT(idArene))
    FROM Defense
    GROUP BY idEquipe);