TD6 Base de données
                        
1-  SELECT * FROM basetd.TRAVAIL
    SELECT * FROM basetd.CONCERNE
    SELECT * FROM basetd.PROJET
    SELECT * FROM basetd.SERVICE
    SELECT * FROM basetd.EMPLOYE
    
2-  SELECT nomserv, nomempl 
    FROM basetd.service, basetd.employe 
    WHERE basetd.service.chef = basetd.employe.nuempl
    
3-  SELECT nomempl,nomproj 
    FROM basetd.employe E,basetd.travail T,basetd.projet P
    WHERE T.nuproj=P.nuproj and T.nuempl=E.nuempl
    
4-  SELECT nomserv,nomproj,nomempl
    FROM basetd.PROJET P , basetd.SERVICE S , basetd.CONCERNE C , basetd.EMPLOYE E
    WHERE C.nuproj= P.nuproj and P.resp=E.nuempl and C.nuserv=S.nuserv 
    ORDER BY nomserv
    
5-  SELECT nomserv,nomempl ,
    (SELECT count(nuempl) FROM basetd.employe E, basetd.Service S 
    WHERE E.affect=S.nuserv and S.nomserv='achat')
    FROM  basetd.SERVICE S , basetd.EMPLOYE E
    WHERE S.nomserv='achat' and S.chef=E.nuempl
    
6-  SELECT nomprj,nomempl
    FROM basetd.projet,basetd.employe
    WHERE resp=nuempl
    
7-  SELECT nomproj,nomempl, 
    (SELECT count(*) from basetd.travail T, basetd.projet P 
    WHERE T.nuproj=P.nuproj and P.nomproj='esprit' )
    FROM basetd.employe, basetd.projet P
    WHERE resp=nuempl AND P.nomproj='esprit'

8-  SELECT nomempl FROM basetd.EMPLOYE 
    WHERE NOT EXISTS 
    5SELECT * FROM basetd.TRAVAIL WHERE nuempl=EMPLOYE.nuempl)

9-  (select nomempl
    FROM basetd.employe E, basetd.travail T
    WHERE   E.nuempl = T.nuempl
    AND T.nuproj NOT IN (
    select nuproj from basetd.concerne C where  
    C.nuserv = E.affect )
    )

10-	select nomserv, nomempl "chef",
    (select COUNT(NUEMPL) 
    from basetd.employe E
    where
    S.nuserv = E.affect)"nb employe"
    FROM  basetd.service S, basetd.employe E
    WHERE S.chef = E.nuempl

11- select nomproj, nomempl "chef",	
    select COUNT(NOMEMPL) 
    from basetd.employe E, basetd.travail T
    where P.nuproj = T.nuproj AND
    E.nuempl =  T.nuempl )"nb employe"
    FROM  basetd.projet P, basetd.employe E
    WHERE P.resp = E.nuempl

12- SELECT nomproj,sum(duree)
    FROM basetd.PROJET P, basetd.TRAVAIL T
    WHERE P.nuproj=T.nuproj
    GROUP BY nomproj

13- select nomproj,nomserv,	
    (select COUNT(nomempl)
    FROM basetd.employe, basetd.travail
    WHERE basetd.employe.affect = basetd.service.nuserv
    AND basetd.employe.nuempl = basetd.travail.nuempl
    AND basetd.travail.nuproj = basetd.projet.nuproj
    )"nb empl serv"	
    FROM  basetd.projet, basetd.concerne, basetd.service
    WHERE basetd.projet.nuproj = basetd.concerne.nuproj
    AND basetd.concerne.nuserv = basetd.service.nuserv

14- SELECT nomempl
    FROM basetd.employe E, basetd.TRAVAIL T,basetd.PROJET P
    WHERE E.nuempl   IN  (SELECT ALL NUPROJ FROM basetd.projet ) 
    
    ou
    
    select nomempl from basetd.employe where nuempl 
    IN (SELECT nuempl from basetd.travail 
    group by nuempl having count(*)=(select count(*) from basetd.projet))

15- select nomserv,	nomproj,
    (select sum(duree)
    FROM basetd.employe , basetd.travail ,basetd.service
    WHERE basetd.travail.nuproj = basetd.projet.nuproj  
    AND basetd.employe.affect = basetd.service.nuserv
    AND basetd.employe.nuempl = basetd.travail.nuempl
    
    AND basetd.travail.nuproj = basetd.projet.nuproj
    )"duree"	
    
    FROM  basetd.projet, basetd.concerne, basetd.service
    WHERE basetd.projet.nuproj = basetd.concerne.nuproj
    AND basetd.concerne.nuserv = basetd.service.nuserv
    order by nomserv
    
    ou
    
    select nomserv,NOMPROJ,
    ( select sum(DUREE) 
    FROM basetd.Employe,basetd.SERVICE,basetd.travail
    WHERE basetd.employe.AFFECT=basetd.SERVICE.NUSERV
    AND basetd.employe.nuempl=basetd.travail.nuempl
    AND basetd.projet.nuproj=basetd.travail.nuproj
    )"nb d heure"
    FROM  basetd.projet, basetd.concerne, basetd.service
    WHERE basetd.projet.nuproj = basetd.concerne.nuproj
    AND basetd.concerne.nuserv = basetd.service.nuserv
    ORDER BY nomserv

    

16- SELECT nomempl
    FROM basetd.employe E, basetd.TRAVAIL T,basetd.projet P
    WHERE E.nuempl   IN  (SELECT nuproj FROM basetd.employe E,basetd.concerne C where E.affect=C.nuserv  ) 


17- select distinct nomempl
    from basetd.travail T,basetd.employe E
    where T.nuproj IN (select nuproj from basetd.travail T,basetd.employe E
    where nomempl='simone'
    and E.nuempl=T.nuempl)
    
    
18- SELECT distinct nomempl
    FROM basetd.employe E, basetd.TRAVAIL T,basetd.projet P
    WHERE E.nuempl   NOT IN  (SELECT nuproj FROM basetd.employe E,basetd.concerne C where E.affect=C.nuserv  )