requetes_enoncé

Requetes SQL:

R1:
SELECT Nom, Prenom, Numero
FROM Acteur a
WHERE (SELECT count(distinct AnneeSortie)
	   FROM (
			SELECT AnneeSortie, Nom, Prenom, Numero
			FROM Oeuvre o INNER JOIN Role r
			ON ID = OID
			WHERE AnneeSortie BETWEEN 2003 AND 2007) t
	   WHERE t.Prenom = a.Prenom and t.Nom = a.Nom and t.Numero = a.Numero) = 5;

R1 bis:
SELECT Nom, Prenom, Numero
FROM (
	SELECT AnneeSortie, Nom, Prenom, Numero
	FROM Oeuvre o INNER JOIN Role r
	ON ID = OID
	WHERE AnneeSortie BETWEEN 2003 AND 2007) t
group by Nom, Prenom, Numero
having count(distinct AnneeSortie) = 5


R2 Alex:
Select  a.Nom, a.Prenom, a.Numero
From EcritPar a, Film f
where a.OID = f.FilmID
group by a.Nom, a.Prenom, a.Numero
having count(a.OID) >= 2;

R2 Benj:
SELECT distinct Nom, Prenom, Numero
FROM(
	SELECT AnneeSortie, Nom,Prenom, Numero
	FROM Oeuvre o
	INNER JOIN Film f ON o.ID = f.Film  ID
	INNER JOIN EcritPar ON ID = OID
	group by AnneeSortie, Nom, Prenom, Numero
	having count(*) >=2 )t

R3 dans les oeuvres:
SELECT distinct Nom, Prenom, Numero
FROM(
	SELECT R2.OID
    FROM(
			SELECT R1.Prenom, R1.Nom, R1.Numero, R1.OID
			FROM (
				SELECT *
				FROM Role
				WHERE Nom = 'Willis' and Prenom = 'Bruce' and Numero = 'NA') as T
            JOIN Role R1 ON T.OID = R1.OID) as T2
    JOIN Role R2 ON T2.Prenom = R2.Prenom AND T2.Nom = R2.Nom AND T2.Numero = R2.Numero) as T3
JOIN Role R3 ON T3.OID = R3.OID

R3 que dans les films:
SELECT distinct Nom, Prenom, Numero
FROM(
	SELECT OID
	FROM(
		SELECT 	R2.OID
		FROM(
			SELECT T2.Nom, T2.Prenom, T2.Numero, T2.OID
			FROM(
					SELECT R1.Prenom, R1.Nom, R1.Numero, R1.OID
					FROM (
						SELECT *
						FROM Role
						WHERE Nom = 'Willis' and Prenom = 'Bruce' and Numero = 'NA') as T
						JOIN Role R1 ON T.OID = R1.OID) as T2
			INNER JOIN Film F on F.FilmID = T2.OID) as T3
		JOIN Role R2 ON T3.Prenom = R2.Prenom AND T3.Nom = R2.Nom AND T3.Numero = R2.Numero) as T4
	INNER JOIN Film F2 on F2.FilmID = T4.OID) as T5
JOIN Role R3 ON T5.OID = R3.OID



R4 Alex:
	select distinct e.EpisodeID, e.SID From Episode e where not exists( Select a.OID from Role a, Personne p where p.Numero = a.Numero and a.Nom = p.Nom and a.Prenom = p.Prenom and p.Genre = 'm' and a.OID = e.EpisodeID);

R4 Benj:
SELECT distinct EpisodeID
FROM Episode e
WHERE NOT EXISTS(
	SELECT Genre, EpisodeID
	FROM Role INNER Join Personne on  Personne.Nom = Role.Nom AND Personne.Prenom = Role.Prenom AND Personne.Numero  = Role.Numero
	WHERE genre = 'm' AND OID = e.EpisodeID
    )

R5:
select Prenom, Nom, Numero, count(*)nb
FROM(
	select SID, Prenom, Nom, Numero
	from Episode
	inner join Role on OID = EpisodeID
	group by  SID, Prenom, Nom, Numero)t
group by  Prenom, Nom, Numero
order by nb desc
LIMIT 1


R6:
SELECT T3.SID, ep_num, avg_ep_by_year, avg_actor_by_season
FROM (
	(SELECT ID, count(*) as ep_num
	FROM(
		SELECT ID, EpisodeID
		FROM(
		 SELECT ID
		 FROM Serie INNER Join Oeuvre on Oeuvre.ID = Serie.SerieID
		 WHERE note >(
			 SELECT AVG(note)
			 FROM(
			 SELECT note
			 FROM Serie INNER Join Oeuvre on Oeuvre.ID = Serie.SerieID
			 WHERE note != -1 and AnneeSortie !=0) as t)) as Series_OK
		 INNER JOIN Episode ON Series_OK.ID = Episode.SID) as t
		 GROUP BY ID)T1

	inner join

	 (
		 SELECT SID, avg(num) as avg_actor_by_season
		 FROM(
			SELECT SID, count(*) as num
			FROM(
				SELECT Nom, Prenom, Numero, SID, Saison
					FROM(
					SELECT SID, EpisodeID, Saison
					FROM Episode inner join Serie on SerieID = SID
					WHERE Saison != -1)t
				INNER Join Role on EpisodeID = OID
				Group by Nom, Prenom, Numero, SID, Saison)t2
			GROUP BY SID, Saison)t3
		Group By SID)T2

	on T1.ID = T2.SID

	inner join

	(
		SELECT t5.SID, AVG(num) as avg_ep_by_year
		FROM(
		 SELECT t6.SID, count(*) as num
		 FROM(
			 SELECT SID, ID, AnneeSortie
			 FROM Oeuvre
			 INNER Join Episode on EpisodeID = ID)t6
		 WHERE AnneeSortie != 0
		 GROUP BY t6.SID, AnneeSortie) as t5
		Group by t5.SID)T3

	on T1.ID = T3.SID
)