/* TP 22 correction : Langage SQL partie 2
   BCPST1B 2023-2024
   Lycée Hoche, Versailles
   L.-C. LEFÈVRE
*/

/* Cas 1 : observation des 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 COUNT(*), annee FROM observations GROUP BY annee;
SELECT COUNT(*) AS "Nombre d'observations", annee FROM observations GROUP BY annee ORDER BY "Nombre d'observations" DESC;
SELECT AVG(latitude), AVG(longitude), animal FROM observations GROUP BY animal;

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

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

/* Cas 2 : suivi du paludisme
   Base : paludisme.db
*/

/* exemple */
SELECT * FROM palu JOIN demographie ON iso=pays AND annee=periode;

/* exercice 5 */
SELECT * FROM palu JOIN demographie ON iso=pays AND annee=periode WHERE annee=2010 AND deces >= 1000;
SELECT SUM(deces) FROM palu;
SELECT annee, SUM(deces) AS "Nombre de décès" FROM palu GROUP BY annee;
SELECT annee, nom, SUM(deces) FROM palu GROUP BY nom ORDER BY SUM(deces) DESC;
SELECT 100.0 * deces / cas, nom, annee FROM palu;
SELECT 100.0 * deces / cas AS "Taux de mortalité", nom, annee FROM palu ORDER BY "Taux de mortalité" DESC;
SELECT 100.0 * SUM(deces) / SUM(cas) AS "Taux de mortalité", nom FROM palu GROUP BY nom ORDER BY "Taux de mortalité" DESC;
SELECT 100000.0 * cas / pop, nom, annee FROM palu JOIN demographie ON iso=pays AND annee=periode;
SELECT 100000.0 * cas / pop AS "Taux d'incidence", nom, annee FROM palu JOIN demographie ON iso=pays AND annee=periode ORDER BY "Taux d'incidence" DESC;
SELECT AVG(100000.0 * cas / pop) AS "Taux d'incidence moyen", nom FROM palu JOIN demographie ON iso=pays AND annee=periode GROUP BY nom ORDER BY "Taux d'incidence moyen" DESC;
