xavpd

2)
select  distinct nom_etud, prenom_etud as liste
from etudiant as et, enseignant as e, jury as j, soutenance as s, stage as st
where nom_ens="marchetti" or nom_ens="zertal"
and e.id_ens = j.id_ens_pres
and j.no_jury = s.no_jury
and et.no_etud = st.no_etud
and st.no_stage = s.no_stage

4)
select distinct(nom_entrep)
from entreprise as en, promotion p1, promotion p2, etudiant e1, etudiant e2, stage s1, stage s2
where p1.annee_sortie = "2018"
and p2.annee_sortie = "2018"
and p1.no_promo = e1.no_promo
and p2.no_promo = e2.no_promo
and s1.no_etud = e1.no_etud
and s2.no_etud = e2.no_etud
and s1.no_entrep = s2.no_entrep
and s1.no_stage <> s2.no_stage
and s1.no_entrep = en.no_entrep



6)
create temporary table t1
select count(no_stage) s1
from promotion as p, etudiant as e, stage s1
where p.annee_sortie = "2018"
and e.no_promo = p.no_promo
and e.no_etud = s1.no_etud
;
create temporary table t2
select count(no_stage) s2
from promotion as p, etudiant as e, stage s2, jury as j
where p.annee_sortie ="2018"
and p.no_promo = e.no_promo
and e.no_etud = s2.no_etud
and j.id_ens_pres = s2.no_ens_tuteur
;
select(s2/s1*100)
from t1,t2
;
drop table t1
;
drop table t2

8)
select nom_etud, e.no_etud
from etudiant as e, promotion as p, stage as s
where p.annee_sortie ="2018"
and p.no_promo = e.no_promo
and e.no_etud = s.no_etud
and s.no_stage not in(
    select so.no_stage
    from soutenance  as so, stage as s
    where so.no_stage = s.no_stage
    )


10)
create temporary table t1
select count(no_stage) s1
from stage s1
;
create temporary table t2
select count(no_stage) s2
from stage s2, pays as p, entreprise as e
where e.no_entrep = s2.no_stage
and p.nom_pays = "France"
and p.no_pays = e.no_pays
;
create temporary table t3
select count(no_stage) s3
from entreprise as e, stage s3, pays as p
where e.no_entrep = s3.no_stage
and p.nom_pays <> "France"
and p.no_pays = e.no_pays
;
select s2/s1*100 as statfr , s3/s1*100 as statet
from t1, t2, t3
;
drop table t1
;
drop table t2
;
drop table t3



12)
select distinct s1.no_etud
from stage s1, stage s2, promotion as p, etudiant as e
where p.annee_sortie="2018"
and p.no_promo = e.no_promo
and s1.no_etud = s2.no_etud
and s1.no_stage <> s2.no_stage
and s1.no_etud = e.no_etud
and s2.no_etud = e.no_etud