# L2BD_S4 TD6 12-04-2021+19-04-2021

-- Requete 1
SELECT titre FROM film
-- Requete 2
SELECT nom FROM personne WHERE prenom='Robert'
-- Requete 3
SELECT * FROM film WHERE duree=105
-- Requete 4
SELECT * FROM film WHERE an=2017
-- Requete 5
SELECT pid FROM personne WHERE prenom='Robert' AND nom='Redford'
-- Requete 6
SELECT pid FROM personne WHERE prenom='Robert' OR nom='Robert'


Select Pid From Personne Where Prenom = 'Robert' 
Union
Select Pid From Personne Where Nom = 'Robert' ;




-- req 07

select personne.nom from personne, role1
where personne.nom = role1.nom;


Select Nom From Personne Intersect Select Nom From Role1 ;



select role1.pid from role1,mes where role1.pid=mes.pid;


Select Pid From MES      Intersect        Select Pid From Role1 ;

-- req 09


-- req 15

Select Distinct(Personne.Nom, personne.prenom)
From Personne, Role1 as R1, Role1 as R2
Where R1.Pid=R2.Pid and R1.Fid != R2.Fid
and R1.Pid = Personne.Pid ;

Select Personne.Nom, Personne.Prenom
From Personne, Role1
Where Personne.Pid = Role1.Pid
Group BY Personne.Nom,personne.prenom
Having Count (Distinct fid)>=2 ;


-- req 16

Sélect Personne.Nom, personne.prenom  From Personne, Role1
Where Personne.Pid = Role1.Pid
Group BY Personne.Nom
Having Count (Role1.Pid) >=2 ;

-- req 17


select distinct personne.nom, personne.prenom
from personne, role1
where personne.pid=role1.pid
group BY (personne.nom, personne.prenom,fid)
having count(role1.nom)>=2;

-- req 19


Select Personne.Nom, prenom
From Film, Role1, Personne
Where Personne.Pid = Role1.Pid
and Role1.Fid = Film. Fid
and Rang <= All (Select rang from film) ;

-- req 20

select distinct(personne.nom)
from personne, role1
where role1.pid=personne.pid

EXCEPT

select distinct(p2.nom)
from mes, personne as P1, personne as P2, role1
where p1.prenom='Woody' and p1.nom='Allen'
and p1.pid=mes.pid
and mes.fid=role1.fid
and role1.pid=p2.pid
;

-- req 21

-- les noms des acteurs qui ont joué Mary
SELECT DISTINCT (personne.nom, personne.prenom) FROM personne WHERE personne.pid IN (SELECT pid FROM role1 WHERE nom='Mary')
EXCEPT
-- on retire les autres roles
SELECT DISTINCT (personne.nom, personne.prenom) FROM personne WHERE personne.pid IN (SELECT pid FROM role1 WHERE nom!='Mary'); 



SELECT personne.nom, personne.prenom from personne 
EXCEPT 
(SELECT pid FROM personne WHERE personne.pid IN (SELECT pid FROM role1 WHERE nom != 'Mary'));


-- req 22

-- nombre de films
select count( distinct fid) from personne as P, MES where p.prenom='Woody' and p.nom='Allen' and p.pid=mes.pid;


select distinct(p2.nom,p2.prenom) from mes, personne as p, personne as p2, role1
-- P : les films de WA
where p.nom='Allen' and p.pid=mes.pid
-- P2 les acteurs ayant joue dans un film de WA
and mes.fid=role1.fid and role1.pid = p2.pid
-- groupes ar p2.nom
group by p2.nom,p2.prenom
having count(distinct role1.fid) = 
(select count( distinct fid) from personne as P, MES where p.prenom='Woody' and p.nom='Allen' and p.pid=mes.pid);


-- req 23

select distinct(p.prenom,p.nom) from personne as p, role1 where role1.pid=p.pid
and role1.fid in (

select distinct(role1.fid) from role1
group by role1.fid
having count(role1.nom)>1 and count(distinct role1.pid)=1
);