Forêts

Question 1:

SELECT DISTINCT  localite FROM lieux;

Question 2:

SELECT DISTINCT  localite FROM lieux WHERE type="foret";

Animaux protégés

Question 1:

SELECT DISTINCT * FROM animaux A WHERE protege="True";

Question 2:

SELECT DISTINCT D.* FROM observations O, details D, animaux A WHERE O.id_observation=D.id_observation AND D.id_animal=A.id_animal AND A.protege="True";

Question 3:

SELECT DISTINCT L.nom FROM lieux L, observations O, details D, animaux A WHERE L.id_lieu = O.id_lieu AND O.id_observation=D.id_observation AND D.id_animal=A.id_animal AND A.protege="True";

Meilleur Observateur

Question 1:

SELECT O.id_personne, SUM(D.quantite) FROM observations O, details D, animaux A WHERE A.protege="True" AND D.id_observation=O.id_observation AND A.id_animal=D.id_animal  GROUP BY id_personne;

Question 2:

SELECT O.id_personne, SUM(D.quantite) FROM observations O, details D, animaux A WHERE A.protege="True" AND D.id_observation=O.id_observation AND A.id_animal=D.id_animal GROUP BY id_personne ORDER BY SUM(D.quantite) DESC LIMIT 1;

Question 3:

SELECT B.prenom, B.nom FROM observations O, details D, animaux A, observateurs B WHERE A.protege="True" AND D.id_observation=O.id_observation AND A.id_animal=D.id_animal AND B.id_personne=O.id_personne GROUP BY O.id_personne ORDER BY SUM(D.quantite) DESC LIMIT 1

FamillesM

Question 1:

SELECT nom FROM familles WHERE nom LIKE "M%";

ObsNonDoc

Question 1:

SELECT email FROM observateurs WHERE fonction<>"Doctorant" AND email IS NOT null;

IdObsTemps

Question 1:

SELECT id_personne, SUM(duree) AS temps_passe FROM observations GROUP BY id_personne;

PersonneTemps

Question 1:

SELECT prenom, nom, temps_passe
            FROM observateurs O,
                       (SELECT id_personne, SUM(duree) as temps_passe FROM observations GROUP BY id_personne) as B
            WHERE O.id_personne = B.id_personne;

AnimauxNonObserves

Question 1:

SELECT nom FROM animaux
            WHERE id_animal NOT IN (SELECT id_animal FROM details);

LieuxProfesseurs

Question 1:

SELECT nom FROM familles WHERE nom LIKE "M%";

LieuxPlusVisites

Question 1:

SELECT nom, localite FROM lieux
            WHERE id_lieu IN
                (
                 SELECT id_lieu
                 FROM observations O, observateurs P
                 WHERE O.id_personne = P.id_personne AND P.fonction="professeur"
                );