Page 936 - IT2

Dossier 2 :
1. Create table Etab_Art ( codeEtab int not null foreing key referenses Etablissement (codeEtab),
codeArt int not null foreing key referenses Article (codeArt),
QteStock int check QteStock int >=0 , constraint Etab_pk Primary key (codeEtab,codeArt) )

2. Select nomEtab , sum(Qte) From SortieArt S inner join Etab_Art EA ON S.codeArt=EA.codeArt inner
join Etablissement E ON EA.codeEtab=E.codeEtab Group by nomEtab

3. Create function question3 (@codeEtab int, @dd , @df)
Returns money
Begin
declare @tot money
Select @tot=sum(Qte*PU) From SortieArt S inner join Etab_Art EA ON S.codeArt=EA.codeArt
Where codeEtab=@codeEtab AND (DateS between @dd and @df)
return @tot

4. Create procedure Transfer @EtabSource int , @ EtabDest int , @codeArt int , @Qte int
As
declare @QteStock int
If @EtabSource = @ EtabDest
Return 3
Select @QteStock = QteStock From Etab_Art Where codeArt = @EtabSource
If @Qte > @QteStock
Return 2
Update Etab_Art set QteStock = QteStock + @Qte Where codeEtab = @EtabDest AND
codeArt=@codeArt
Update Etab_Art set QteStock = QteStock - @Qte Where codeEtab = @EtabSource AND
codeArt=@codeArt
Return 0

5. Create Trigger question5 ON SortieArt
After Insert
As

Filière Epreuve Session 5/8
DI Théorique FF (Elément de correction) Juillet 2016
www.itlearning-settat.com

Email: admission@itlearning-settat.com

Tél.:0661077812