----------------------------- Q03.sql -----------------------------------
--  sous-requetes dans FROM

-- les employes qui travaillent dans un projet ou
-- travaille  Smith
select DISTINCT 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;



-- Pour chaque departement les femmes avec les salaires les
-- le plus eleves (les plus eleves parmi les femmes, on ignore les hommes)

SELECT nom, prenom, E.salaire, dnom
FROM   employe E, departement D, (SELECT MAX(salaire), dnumero FROM employe
                                  WHERE sexe ='F'
                                  GROUP BY dnumero
                                 ) AS F(salaire,dnumero)
WHERE E.sexe='F' AND E.dnumero = D.dnumero AND 
      (E.salaire, E.dnumero) = (F.salaire,F.dnumero);


SELECT nom, prenom, salaire, dnom
FROM     departement D NATURAL JOIN employe E
         NATURAL JOIN  (SELECT MAX(salaire), dnumero FROM employe
                                  WHERE sexe ='F'
                                  GROUP BY dnumero
                                 ) AS F(salaire,dnumero);


SELECT nom, prenom, salaire, dnom
FROM     departement D NATURAL JOIN employe E
         NATURAL JOIN  (SELECT MAX(salaire) AS salaire, dnumero FROM employe
                                  WHERE sexe ='F'
                                  GROUP BY dnumero
                                 ) AS F;

-- les moyens des salaires par departement avec le nom des departements
SELECT CAST(salaire AS INT),  dnom
FROM   (SELECT AVG(salaire) AS salaire, dnumero
        FROM employe
        GROUP BY dnumero) AS moy,
       departement D
WHERE  moy.dnumero = D.dnumero;

SELECT CAST(salaire AS DEC(8,2)),  dnom
FROM   (SELECT AVG(salaire) AS salaire, dnumero
        FROM employe
        GROUP BY dnumero) AS X NATURAL JOIN
       departement; 

-- afficher nom, prenom, age et tranche d'age pour chaque employe
SELECT nom, prenom, age, 
       CASE
           WHEN age >= 52 THEN 'trop vieux'
           WHEN age >= 42 THEN 'meilleur'
           WHEN age >= 32 THEN  'jeune'
           ELSE 'trop jeune'
      END 
FROM   (SELECT nom, prenom, EXTRACT(YEAR FROM CURRENT_DATE)
                            - EXTRACT(YEAR FROM date_naiss) 
                              AS age
        FROM   employe) AS toto;

-- Noter que 
-- la requete suivante ne marchera pas.
-- Message d'erreur:
-- column "age" does not exist
--SELECT nom, prenom, (EXTRACT(YEAR FROM CURRENT_DATE)
--                    - EXTRACT(YEAR FROM date_naiss)) AS age,
--       CASE
--           WHEN age >= 52 THEN 'trop vieux'
--           WHEN age >= 42 THEN 'meilleur'
--           WHEN age >= 32 THEN  'jeune'
--           ELSE 'trop jeune'
--      END 
--FROM   employe;