-------------------------------------- Q04.sql -------------------------------
--------------------  sous-requetes dans FROM

-- tous les employes dont l'age depasse l'age moyen dans leur departement

SELECT nom, prenom, dnom
FROM   employe NATURAL JOIN
           (SELECT avg(EXTRACT(YEAR FROM CURRENT_DATE) -
                       EXTRACT(YEAR FROM date_naisS)) AS average, dnumero
            FROM employe
            GROUP BY dnumero) AS agem NATURAL JOIN 
       departement 
WHERE  EXTRACT(YEAR FROM current_date) - EXTRACT(YEAR FROM date_naiss) 
       > average;


-- tous les employes qui travaillent dans un departement ou travaillent
-- au moins trois personnes
SELECT nom, prenom, dnom
from employe natural join departement
where dnumero IN
     (select  dnumero 
      from employe
      group by dnumero
      having count(*) > 3)
ORDER BY dnumero, dnom;
 
-- toutes les femmes qui travaillent dans un departement ou 
-- travaillent au moins trois femmes
SELECT nom, prenom, dnom
from (SELECT *
      FROM employe
      WHERE sexe='F') AS FF 
      natural join 
      departement
where dnumero IN
     (select  dnumero 
      from employe
      where sexe='F' 
      group by dnumero
      having count(*) > 3)
ORDER BY dnumero, dnom;