Définition et mise à jour des données - Base de données CINEMAS

//Auteur: CHAOULID
//Copyright:Exelib.net
//***Autres Écritures sont possibles*** 
//Q1

create database CINEMAS
go
use CINEMAS

create table Ville(CodePostal int primary key check(CodePostal like '_____'),NomVille varchar(30))
create table Cinema(NumCinema int primary key,NomCinema varchar(30),RueCinema varchar(30),CodePostal int foreign key references Ville(CodePostal) on delete cascade)
create table Salle(NumSalle int primary key,Capacite int check(Capacite between 30 and 100),NumCinema int foreign key references Cinema(NumCinema) on delete cascade)
create table Film(NumFilm int primary key,Titre varchar(50),Duree int,Producteur varchar(30))
create table Projection(NumFilm int foreign key references Film(NumFilm) on delete cascade,NumSalle int foreign key references Salle(NumSalle) on delete cascade,DateP date,NbreEntree int,primary key(NumFilm,NumSalle,DateP))

//Q2

insert into Ville values(11111,'VILLE1')
insert into Ville values(22222,'VILLE2')
insert into Ville values(33333,'VILLE3')

select * from Ville

insert into Cinema values(1,'CINEMA1','RUE1',11111)
insert into Cinema values(2,'CINEMA2','RUE2',22222)
insert into Cinema values(3,'CINEMA3','RUE3',33333)

select * from Cinema

insert into Salle values(1,40,1)
insert into Salle values(2,35,1)
insert into Salle values(3,60,2)
insert into Salle values(4,90,3)
insert into Salle values(5,40,2)


select * from Salle

insert into Film values(1,'FILM1',90,'PROD1')
insert into Film values(2,'FILM2',90,'PROD2')
insert into Film values(3,'FILM3',80,'PROD3')
insert into Film values(4,'FILM4',70,'PROD4')

select * from Film

insert into Projection values(1,2,'21/10/2011',30)
insert into Projection values(2,1,'24/10/2011',80)
insert into Projection values(3,1,'25/10/2011',50)
insert into Projection values(4,4,'16/11/2011',70)
insert into Projection values(1,3,'25/08/2011',60)
insert into Projection values(2,2,'23/10/2011',20)
insert into Projection values(1,4,'25/08/2011',50)
insert into Projection values(1,4,'27/08/2011',50)
insert into Projection values(1,4,'24/10/2014',50)
insert into Projection values(2,1,'24/10/2015',50)

select * from Projection

//Q3
select p.*
from Projection p inner join Salle s on p.NumSalle=s.NumSalle
where p.NbreEntree>=0.8*s.Capacite 

//Q4

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

//Q5

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

//Q6
select f.Producteur,COUNT(f.NumFilm) as "Nombre de films"
from Projection p inner join Film f on p.NumFilm=f.NumFilm 
where DateP='25/08/2011'
group by f.Producteur
 
//Q7
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 '20/10/2011' and '25/10/2011'
group by f.NumFilm,f.Titre

//Q8
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.NbreEntree)>150


//Q9

delete from Film
where NumFilm in
( 
select NumFilm
from Projection
group by NumFilm 
having MAX(DateP)<=Cast((DATEADD(YEAR,-3,GETDATE())) As DATE)
)

//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)
)