-----------------  Q02 --------------------------------------
-- les employes avec les salaires les plus eleves dans chaque departement

SELECT nom, prenom, dnom, salaire
FROM employe NATURAL JOIN departement X
WHERE   salaire = (select MAX(salaire) 
                   FROM   employe E
                   WHERE  E.dnumero=X.dnumero);


-- trouver les departements ou travaillent des femmes
SELECT dnom
FROM departement X
WHERE 'F' IN (SELECT sexe 
              FROM   employe, departement D
              WHERE X.dnumero = D.dnumero );

--les depertements ou il y a plus d'employes femmes que hommes
SELECT dnom
FROM   departement D
WHERE  (SELECT COUNT(*) 
        FROM employe E 
        WHERE E.dnumero = D.dnumero AND sexe = 'F') 
	>   
	(SELECT COUNT(*) 	
        FROM employe E 
        WHERE E.dnumero = D.dnumero AND sexe = 'H') 
;   


-- qui gagne entre +- 25% de salaire moyen?

SELECT nom, prenom, salaire
FROM   employe WHERE salaire BETWEEN 0.75*(SELECT avg(salaire) 
                                      FROM employe) AND
                                     1.25*(SELECT avg(salaire)
                                           FROM employe)
;


-- Trouver tous les employes qui travaillent dans 2 projets
-- au moins
SELECT nom, prenom
FROM employe E
WHERE (SELECT count(*) 
       FROM emp_proj P
       WHERE E.ssn = E.ssn) >= 2 ;


-- les employes qui travaillent dans tous les projets controles par le 
-- le departement Research
SELECT nom, prenom
FROM employe E
WHERE NOT EXISTS
     (
	(SELECT pnumero          --- tous les projets controles par Research
      	 FROM   departement D, projet P
      	 WHERE  lower(dnom) = 'research')
     EXCEPT
	 ( SELECT W.pnumero
         FROM   emp_proj W
         WHERE  W.ssn = E.ssn)
     ) ;


-- tous les employes qui ont un ayantdroit du meme sexe que lui/elle
SELECT nom, prenom, sexe
from  employe E
WHERE EXISTS (SELECT * 
             FROM   ayantdroit A
             where A.ssn = E.ssn AND E.sexe = A.sexe );

-- tous les employes qui n'ont pas d'ayantdroit
SELECT nom, prenom
FROM   employe E
WHERE  NOT EXISTS (SELECT *
                      FROM ayantdroit A
                      WHERE A.ssn=E.ssn);

-- les femmes qui travaillent dans un departement ou travaillent 
-- aussi des hommes
SELECT nom, prenom, dnom
FROM   departement NATURAL JOIN employe AS FF
WHERE  sexe = 'F'  AND EXISTS (SELECT *
                               FROM   employe E
                               WHERE  sexe='M' AND E.dnumero=FF.dnumero);

-- les plus jeunes employes dans chaque departement
select nom, prenom, dnom
from   employe natural join  departement
where  (date_naiss,dnumero) =ANY (select min(date_naiss),dnumero
                               from employe 
                               group by dnumero);

-- les chefs de  departements qui gagnent plus que tous les employes
-- de leur departement
select nom, prenom, dnom
from   employe X, departement D
where ssn = dir_ssn and
      salaire >ALL (select salaire 
                    from employe E 
                    where D.dnumero = E.dnumero AND E.ssn <> X.ssn);

 
-- les employes qui travaillent dans un projet ou
-- travaille  Smith
select nom, prenom 
from employe E
WHERE EXISTS (
     (select pnumero
      from emp_proj P
      where P.ssn = E.ssn) 
    INTERSECT
      (select pnumero
      from employe natural join emp_proj 
      where lower(nom)='smith' and lower(prenom)='john' )
);

select nom, prenom 
from 
   (SELECT nom, prenom, pnumero
    FROM  employe NATURAL JOIN emp_proj
    ) AS A 
   NATURAL JOIN 
      (select pnumero
      from employe natural join emp_proj 
      where lower(nom)='smith' and lower(prenom)='john' ) AS B;