Create Database FEM_SGBDII
use FEM_SGBDII
/* Partie I : Programmation TSQL : (4 pts) */
--1
select char(65+32)
declare @i int
declare @t table (AlphaMaG varchar,ASCIIMaG int, AlphaMin varchar,ASCIIMin int)
set @i = 65
while(@i < 91)
begin
insert into @t values (CHAR(@i), @i,CHAR(@i+32), @i+32)
set @i = @i+1
end
select * from @t
--2
create table Livre(
IdL int primary key,
Titre varchar (4)
)
delete from Livre
declare @i int
set @i=0
while @i < 100
begin
insert into Livre values (@i+1,'T'+cast(@i as varchar(25)))
set @i = @i + 1
end
select * from Livre
--3
create table Livre_OLD(
IdL int primary key,
Titre varchar (4)
)
declare old cursor for (select * from Livre)
open old
declare @id int, @titre varchar(255)
fetch next from old into @id ,@titre
while @@FETCH_STATUS = 0
begin
if not exists(select * from Livre_OLD where IdL=@id and Titre = @titre)
begin
begin tran a
insert into Livre_OLD values (@id,@titre)
delete from Livre where IdL=@id and Titre = @titre
commit a
end
fetch next from old int @id,@titre
end
xs
/* Partie II : Procédures stockées et Triggers */
create table Cinéma (
nomCinéma varchar(255) primary key,
numéro int,
rue varchar(255),
ville varchar(255)
)
create table Salle (
nomCinéma varchar(255),
noSalle int,
capacité int,
climatisée int
primary key (noSalle)
)
insert into Salle values('s',1,1,1)
create table Horaire (
idHoraire int primary key,
heureDébut time,
heureFin time,
Durée float
)
insert into Séance values(1,'alhillal',1,1,100000)
insert into Séance values(101,'alhillal',1,1,100000)
insert into Séance values(100,'alhillal',1,1,100)
create table Séance (
idFilm int foreign key references Film(idFilm),
nomCinéma varchar(255) foreign key references Cinéma(nomCinéma),
noSalle int foreign key references Salle(noSalle),
idHoraire int foreign key references Horaire(idHoraire),
tarif int,
primary key (idFilm,nomCinéma,noSalle,idHoraire)
)
create table Film (
idFilm int primary key,
titre varchar(255),
année int,
genre varchar(255),
résumé varchar(255)
)
insert into Film values (1,'Planet des signe',1995,'Science fiction','Monde des singes')
insert into Film values (2,'Avatar',2010,'Science fiction','autre vie')
insert into Film values (3,'Le petit monde de borweur',2012,'comique','monde de petit')
insert into Film values (4,'La rélité',2005,'drama','la fin de monde')
create table Acteur (
idActeur int primary key,
nom varchar(255),
prénom varchar(255),
DateNaissance date
)
create table Rôle (
idActeur int foreign key references Acteur(idActeur),
idFilm int foreign key references Film(idFilm),
nomRôle varchar(255)
primary key(idActeur,idFilm)
)
insert into Cinéma values('MegaRama',1,'Agdal','Casa')
insert into Cinéma values('Arif',2,'Dawdiyat','Marrakech')
insert into Cinéma values('Alhillal',3,'Sou9 Arabi3','Rabat')
insert into Cinéma values('Mabrouka',4,'Prince','marrakech')
insert into Rôle values (1,1,'Pressonage pricipal')
insert into Rôle values (2,1,'Scéane comique')
insert into Rôle values (3,1,'Combat')
insert into Rôle values (2,3,'Police')
insert into Salle values ('MegaRama',12,100,0)
insert into Salle values ('MegaRama',5,250,1)
insert into Salle values ('Arif',6,85,0)
insert into Salle values ('Alhillal',10,150,0)
insert into Horaire values (1,'2:0','4:0',NULL)
insert into Horaire values (2,'8:0','1:0',NULL)
insert into Horaire values (3,'1:0','6:0',NULL)
insert into Horaire values (4,'9:0','10:0',NULL)
insert into Séance values (1,'Alhillal',1,1,100)
insert into Séance values (1,'Arif',2,2,150)
insert into Séance values (2,'Alhillal',2,2,80)
insert into Séance values (2,'MegaRama',1,2,200)
insert into Séance values (3,'MegaRama',2,2,600)
insert into Séance values (3,'Alhillal',3,1,100)
insert into Séance values (1,'Mabrouka',3,3,250)
select * from Acteur,Séance,Horaire,Salle,Cinéma
insert into Acteur values (12,'hillal',7,'03/11/2010')
insert into Acteur values (2,'shari','anas','15/10/1989')
insert into Acteur values (3,'Zaid','badir','2/2/1991')
insert into Acteur values (4,'Dahamne','brahim','1/6/1198')
delete from Acteur where idActeur=1
/* 3)
Triggers */
-- a
create trigger a on Cinéma after delete
As
begin
print 'Imposible de supprimer'
rollback
end
-- b
create trigger bb on Acteur after insert
As
begin
declare @nom varchar(255),@prenom varchar(255),@id int
Select @nom = (select nom from inserted), @prenom = (select prénom from inserted)
select @id = (select idActeur from inserted)
update Acteur set nom = upper(@nom), prénom = upper(left(@prenom,1))+lower(right(@prenom,len(@prenom-1))) where idActeur= @id
commit
end
-- cdelete f
delete from Cinéma where
create trigger c on Cinéma after delete
As
begin
declare @nomCinéma varchar(255)
set @nomCinéma = (select nomCinéma from deleted)
delete from Salle where nomCinéma = @nomCinéma
delete from Séance where nomCinéma = @nomCinéma
commit
end
-- d
create trigger d on Horaire after insert, update
As
begin
declare @heureDébut time, @heureFin time, @Durée float,@idHoraire int
set @heureDébut = (select heureDébut from inserted)
set @heureFin = (select heureFin from inserted)
set @idHoraire = (select idHoraire from inserted)
update Horaire set Durée = (datediff(second,@heureDébut,@heureFin))/3600 where idHoraire = @idHoraire
commit
end
-- e
create trigger ee on Séance after insert, update
As
begin
if (select tarif from inserted) > 1000
begin
print 'Le Tarif ne doit pas dépasser 1000 DH.'
rollback
end
end
/*
Procédures : */
--a
alter proc PS_1(@code int, @nbr int output)
As
begin
select @nbr = count(r.idFilm)
from Acteur a, Rôle r
where a.idActeur = @code and r.idActeur = a.idActeur
group by a.idActeur
end
declare @a int
exec PS_1 2, @a output
select @a
--b
create proc PS_2
As
begin
declare c cursor for (select * from Horaire)
declare @idHoraire int, @heureDébut time, @heureFin time,@Durée float, @test int
set @test = 1
declare @t table (idHoraire int, heureDébut time, heureFin time,Durée float)
open c
fetch next from c into @idHoraire, @heureDébut, @heureFin,@Durée
while @@fetch_status = 0
Begin
if (datediff(second,@heureDébut, @heureFin)) < 0 or ((datediff(second,@heureDébut, @heureFin)) / 3600) > 5
set @test = @idHoraire
fetch next from c into @idHoraire, @heureDébut, @heureFin,@Durée
end
if @test = 0
print 'les conndtion est faux dans lenregistrement est : '+ cast(@idHoraire as varchar)
else
begin
delete from Horaire
insert into Horaire select * from @t
end
close c
deallocate c
end
--c
create proc PS_3
As
begin
declare c1 cursor for (select c.ville, COUNT(*),COUNT(s.noSalle) from Cinéma c, Salle s where c.nomCinéma = s.nomCinéma group by c.ville order by c.ville asc)
declare @v varchar(255), @nbrcénima int, @nbrsalle int
open c1
fetch next from c1 into @v, @nbrcénima, @nbrsalle
while @@FETCH_STATUS = 0
begin
print 'la ville est : '+ cast(@v as varchar(255))+'Nbr Cénima '+cast(@nbrcénima as varchar(255))+'Nbr Salle '+cast(@nbrsalle as varchar(255))
fetch next from c1 into @v, @nbrcénima, @nbrsalle
end
close c1
deallocate c1
end
-- d
create table Grp_Cin (code int , descriptions varchar(255))
alter proc PS_4
As
begin
declare @i int
set @i = 1
while (@i < 5)
begin
insert into Grp_Cin values (@i,'Groupe'+CAST(@i as varchar))
set @i = @i +1
end
end
exec PS_4
select * from Grp_Cin
--e
alter table Cinéma add CodeG int
alter proc PS_4
As
begin
declare c2 cursor for (select c.nomCinéma,COUNT(s.noSalle) from Cinéma c, Salle s where c.nomCinéma = s.nomCinéma group by c.nomCinéma)
declare @NomC varchar(255), @compt int
open c2
fetch next from c2 into @NomC,@compt
while @@FETCH_STATUS = 0
begin
if @compt <= 1
update Cinéma set CodeG = 1 where nomCinéma = @NomC
if @compt between 1 and 3
update Cinéma set CodeG = 2 where nomCinéma = @NomC
if @compt between 3 and 5
update Cinéma set CodeG = 3 where nomCinéma = @NomC
if @compt > 5
update Cinéma set CodeG = 4 where nomCinéma = @NomC
fetch next from c2 into @NomC,@compt
end
end
exec PS_4
--f
create table T (
nomCinéma varchar(255),
numéro int,
rue varchar(255),
ville varchar(255),
noSalle int,
capacité int,
climatisée int
)
select * from t
create proc PS_5
As
begin
insert into T select Cinéma.nomCinéma, numéro, rue, ville,noSalle, capacité, climatisée from Cinéma , salle
end
exec PS_5
