Exercice 5 BDD SQLSERVER

use exercice_5
create table Ville(codePostal varchar(5) primary key check( codePostal like'[0-9][0-9][0-9][0-9][0-9]'), nomVille varchar(10))

create table Cinema( numCinema int primary key, nomCinema varchar(10), rueCinema varchar(10), codePostal varchar(5) foreign key references Ville(codePostal))

create table Salle( numSalle int primary key, capacite int check(capacite between 30 and 100), numCinema int foreign key references Cinema(numCinema))

create table Film( numFilm int primary key, titre varchar(10), duree int, producteur varchar(10))

create table Projection( numFilm int foreign key references Film(numFilm), numSalle int foreign key references Salle(numSalle),
dateP date, nbrEntree int, primary key (numFilm, numSalle, dateP))
go
--

--Insertion du donnees--Q2
insert into Ville values(11111, 'CasaBlanca'),(22222, 'Rabat'),(33333, 'Tanger'),(84000, 'Tata')

insert into Cinema values(1, 'amal', 'rue_amal',11111),(2, 'nour', 'rue_nour',11111),(3, 'houda', 'rue_houda',11111),
(4, 'salam', 'rue_salam',22222),(5, 'irfan', 'rue_irfan',22222),(6, 'arabi', 'rue_arabi',33333)

--11 Salles: (1 amal, 2 nour, 3 houda)=6 Casablanca, (4 salam, 1 irfan)=5 rabat
insert into salle values(11,30,1),(21,40,2),(22,50,2),(31,60,3),(32,70,3),(33,80,3),
(41,30,4),(42,30,4),(43,30,4),(44,60,4),(51,40,5)

insert into Film values(1, 'film1',1,'pro_film1'),(2, 'film2',2,'dev_pro'),(3, 'film3',3,'code_pro'),(4, 'film4',3,'code_pro'),(5, 'film5',3,'dev_pro')

insert into Projection values(1,31,'01/02/2020',1000),(1,11,'01/01/2020',100),(1,21,'02/01/2020',100),(1,22,'03/01/2020',100),
(2,31,'01/02/2020',1000),(2,32,'01/02/2020',10000),(2,33,'01/02/2020',10000),
(3,41,'01/03/2020',100),(3,31,'01/02/2020',1000),
(4,42,'01/04/2020',350),(4,31,'01/02/2020',1000),
(5,43,'01/05/2020',250)
insert into Projection values(5,51,'01/01/2021',1)
go
--

--Afficher la liste des projections où le nombre d’entrées a dépassé 80% de la capacité de la salle de projection.--Q3
/*select P.*
from Projection as P, Salle as S
where P.nbrEntree > 0.8*S.capacite
go*/
select p.*
from Projection p inner join Salle s on p.numSalle=s.numSalle
where p.nbrEntree>=0.8*s.capacite 
go
--

--Afficher le nombre de salles de cinéma par ville (nom ville).--Q4
select nomVille,Count(numSalle) as 'nombre de salle'
from Cinema as C,Salle as S, Ville as V
where S.numCinema = C.numCinema and C.codePostal=V.codePostal
group by nomVille
go
--autre methode
select nomVille, nomCinema, Count(numSalle) as 'nombre des salle'
from Ville, Cinema, Salle
where Ville.codePostal=Cinema.codePostal and Cinema.numCinema=Salle.numCinema
group by nomCinema,nomVille
go
--autre methode
select COUNT(s.numSalle) as "Nombre de salles de cinéma",v.nomVille
from Cinema c inner join Salle s on c.numCinema=s.numCinema inner join Ville v on v.codePostal=c.codePostal
group by v.nomVille
go
--

--Afficher la capacité totale de chaque cinéma (nom du cinéma).--Q5
select nomCinema, SUM(capacite) as 'capacite total'
from Cinema, Salle
where Salle.numCinema= Cinema.numCinema
group by nomCinema
order by 'capacite total' asc
go
--autre methode
select c.nomCinema,sum(s.capacite) as "Capacité totale"
from Salle s inner join Cinema c on s.numCinema=c.numCinema
group by c.nomCinema
go
--

--Afficher le nombre de films projeté le 01/0/2011 par producteur.--Q6
select producteur, Count(Film.numFilm) as 'nombre de film projete'
from Film, Projection
where Film.numFilm = Projection.numFilm and Projection.dateP = cast('2020/01/02' as date)
group by producteur
go
--autre methode
select f.producteur,COUNT(f.numFilm) as "Nombre de films"
from Projection p inner join Film f on p.numFilm=f.numFilm 
where DateP='2020/01/02'
group by f.Producteur
go
--

--Afficher pour chaque film (titre du film) le nombre de projections entre le 20/10/2011 et 25/10/2011.--Q7
select titre, count(dateP) as 'nombre de projections'
from Film, Projection
where (Film.numFilm = Projection.numFilm )and ( Projection.dateP between cast('2020/01/02' as date) and cast('2020/01/04' as date))
group by titre
go
--autre methode
select f.numFilm, f.titre, COUNT(*) as "Nombre de projections"
from Projection p inner join Film f on p.numFilm=f.numFilm 
where p.dateP between  cast('2020/01/02' as date) and cast('2020/01/04' as date)
group by f.numFilm,f.titre
go
--

--Afficher pour chaque cinéma (nom du cinéma) le nombre de projections dont le nombre total d’entrées dépasse 150.--Q8
select nomCinema, count(Projection.numFilm) as 'le nombre de projection', sum(nbrEntree) as 'le nombre total entree'
from Cinema, Salle, Projection
where (Cinema.numCinema= Salle.numCinema and Salle.numSalle=Projection.numSalle) 
group by nomCinema
having sum(nbrEntree)>150
go
--autre methode
select c.numCinema,c.nomCinema,COUNT(*) as "Nombre de Projections"
from Projection p inner join Salle s on p.numSalle=s.numSalle inner join Cinema c on c.numCinema=s.numCinema
group by c.numCinema,c.nomCinema
having sum(p.nbrEntree)>150
go
--

--Supprimer les films qui ne sont pas projetés depuis 3 ans.--

Q9--------------------
delete from Film
where numFilm in
( 
select numFilm
from Projection
group by numFilm 
having MAX(dateP)<=Cast((DATEADD(YEAR,-3,GETDATE())) As DATE)
)
go
------------------------------------------------------
 
--Supprimer les cinémas qui contiennent au moins une salle non utilisée depuis 10 mois-

-Q10
delete from Cinema
where numCinema in
(
select s.numCinema
from Projection p inner join Salle s on p.numSalle=s.numSalle
group by s.numCinema,s.numSalle
having MAX(p.Datep)<=CAST((DATEADD(MONTH,-10,GETDATE())) As DATE)
)
go

===> Exercice 6 Corrigé