Correction EFF 2012 v5 TDI partie sql

--1
create database Marocmec
use Marocmec

create table etat(idetat int primary key,libetat varchar(20))
insert into etat values(1,'x')
select * from rechange
create table appareil (idapp int primary key,decapp varchar(50),nserie int,idmod int foreign key references model(idmod),prixapp int ,idlot int foreign key references lot(idlot),idetat int foreign key references etat(idetat) )
insert into appareil values(1,'blabla',1,1,200,1,1)

create table  model(idmod int primary key , libmod varchar(20))
insert into model values(1,'mod1')

create table  lot(idlot int primary key,liblot varchar(20),dateach date,prixach int)
insert into lot values(1,'lot1','22/06/2012',200)

create table  rechange(idrech int primary key,idpie int foreign key references piece(idpie),idapp int foreign key references appareil(idapp),datenvrech date,datretrech date,montant int,decrech varchar(20))
insert into rechange values(1,1,1,'22/04/2010','30/06/2010',3000,'descrech')

create table  piece(idpie int primary key ,libpie varchar(20),idmod int foreign key references model(idmod),prixpie int,idlot int foreign key references lot(idlot))
insert into piece  values(1,'piece1',1,300,1)
--2
alter table rechange add constraint ct check (datretrech > datenvrech)
alter table rechange add constraint ct2 check (montant >=0)

--3
create proc p (@mod int)
as
select montant from rechange where idapp in (select idapp from appareil where idmod = @mod )
exec p 1
--4
alter trigger tr on rechange instead of insert
as begin
declare @x varchar(50) ,@l varchar(20),@p int,@idp int,@m int,@da date ,@dr date ,@idap int,@idre int,@pa int
select @x=decrech,@idp=idpie ,@m=montant ,@da=datenvrech,@dr=datretrech,@idap=idapp,@idre=idrech from inserted
select @l=libpie,@p=prixpie from piece where idpie = @idp
select @pa=prixapp from appareil where idapp = @idap
if(@m >= @pa)
begin
rollback
end
else if(@x = '')
begin

insert into rechange values (@idre,@idp,@idap,@da,@dr,@m,@l +''+ @p)
end
else insert into rechange values (@idre,@idp,@idap,@da,@dr,@m,@x)

end

 --5
alter function f (@idap int)
 returns int
 as begin
 declare @c int
 select @c= montant from rechange where idapp = @idap
 return @c
 end
 exec dbo.f 1

 --6
 select idapp,decapp,dbo.f(idapp) as Cout_de_reparation from appareil
 --ps liste
 create proc pslistpiece (@d1 date,@d2 date)
 as
 select * from rechange where datenvrech between   @d1 and @d2

Posted in Examen Fin Formation