/* TP 23 correction : Langage SQL partie 2
   BCPST1B 2025-2026
   Lycée Hoche, Versailles
   L.-C. LEFÈVRE
*/

/* Partie III : observations de mammifères
   Base : mammiferes.db
*/

/* exercice 2 */
SELECT * FROM observations ORDER BY dateObservation DESC;
SELECT DISTINCT ordre FROM animaux;
SELECT MAX(latitude) FROM observations;
SELECT COUNT(*) FROM observations WHERE annee < 1980;
SELECT annee, COUNT(*) AS "Nombre d'observations" FROM observations GROUP BY annee;
SELECT annee AS "Année", COUNT(*) AS "Nombre d'observations" FROM observations GROUP BY "Année" ORDER BY "Nombre d'observations" DESC;
SELECT AVG(latitude) AS "Latitude moyenne", AVG(longitude) AS "Longitude moyenne", animal FROM observations GROUP BY animal;

/* exemple */
SELECT * FROM observations JOIN animaux ON animal=cdRef;

/* exercice 3 */
SELECT nomVernaculaire, nomScientifiqueRef, dateObservation FROM observations JOIN animaux ON animal=cdRef;
SELECT nomVernaculaire, dateObservation FROM observations JOIN animaux ON animal=cdRef ORDER BY dateObservation ASC;
SELECT famille, COUNT(*) AS "Nombre d'observations" FROM observations JOIN animaux ON animal=cdRef GROUP BY famille;
SELECT nomVernaculaire, nomScientifiqueRef FROM observations JOIN animaux ON animal=cdRef WHERE annee <= 1910;
SELECT MAX(dateObservation), espece FROM observations JOIN animaux ON animal=cdRef;

/* Partie IV : Pokémon
   Base : pokemon.db
*/

/* exercice 5 */
SELECT COUNT(*) AS "Nombre de Pokémon" FROM pokemons;
SELECT nom, niveau FROM pokemons JOIN evolue_en ON pokemons.id=evolue_en.pokemon_base_id;
SELECT pokemons.nom FROM pokemons JOIN dresseurs JOIN detient_pokemons ON dresseurs.id=detient_pokemons.dresseur_id AND pokemons.id=detient_pokemons.pokemon_id WHERE dresseurs.nom='Pierre';
SELECT attaques.libelle AS "Attaque", types.libelle as "Type" FROM attaques JOIN types ON attaques.type_id=types.id;
SELECT dresseurs.nom, pokemons.nom FROM dresseurs JOIN pokemons JOIN detient_pokemons ON dresseurs.id=detient_pokemons.dresseur_id AND pokemons.id=detient_pokemons.pokemon_id;
SELECT pokemons1.nom AS "Base", niveau, pokemons2.nom AS "Évolution" FROM pokemons AS pokemons1 JOIN pokemons AS pokemons2 JOIN evolue_en ON pokemons1.id=evolue_en.pokemon_base_id AND pokemons2.id=evolue_en.pokemon_evol_id;
SELECT dresseurs.nom, COUNT(*) AS "Nombre de Pokémon" FROM dresseurs JOIN pokemons JOIN detient_pokemons ON dresseurs.id=detient_pokemons.dresseur_id AND pokemons.id=detient_pokemons.pokemon_id GROUP BY dresseurs.id;
SELECT dresseurs.nom, COUNT(*) AS "Nombre de Pokémon", AVG(niveau) AS "Niveau moyen" FROM dresseurs JOIN pokemons JOIN detient_pokemons ON dresseurs.id=detient_pokemons.dresseur_id AND pokemons.id=detient_pokemons.pokemon_id GROUP BY dresseurs.id;
SELECT dresseurs.nom, COUNT(*) AS "Nombre de Pokémon" FROM dresseurs JOIN pokemons JOIN detient_pokemons ON dresseurs.id=detient_pokemons.dresseur_id AND pokemons.id=detient_pokemons.pokemon_id GROUP BY dresseurs.id HAVING "Nombre de Pokémon" >= 4;
SELECT dresseurs.nom, COUNT(*) AS "Nombre de Pokémon" FROM dresseurs JOIN pokemons JOIN detient_pokemons ON dresseurs.id=detient_pokemons.dresseur_id AND pokemons.id=detient_pokemons.pokemon_id GROUP BY dresseurs.id HAVING "Nombre de Pokémon" >= 4 AND MIN(niveau) >= 50;
SELECT dresseurs.nom, COUNT(*) AS "Nombre de Pokémon", MAX(niveau) AS "Niveau maximal" FROM dresseurs JOIN pokemons JOIN detient_pokemons ON dresseurs.id=detient_pokemons.dresseur_id AND pokemons.id=detient_pokemons.pokemon_id GROUP BY dresseurs.id HAVING "Nombre de Pokémon" >= 4 AND MIN(niveau) >= 50 ORDER BY "Niveau maximal" DESC;
