Exercice corrigé procedure stocke (Gestion des stagiaires)

1) La procédure permettant de lister les stagiaires d'une filière donnée

create proc ex1 @fil char(30)

as

begin

select stagiaire.* from stagiaire, filière

where stagiaire.nfilière=filière.nfilière and intituléfil=@fil

end

2) La procédure permettant d'afficher les stagiaires ayant l'âge dans la tranche précisé par l'utilisateur

create proc ex2 @d1 int,@d2 int

as

begin

select *, DATEDIFF(YEAR,datenaiss,GETDATE()) as age from stagiaire

where DATEDIFF (YEAR,datenaiss,GETDATE()) between (@d1 and @d2)

end

3) Augmenter d'un point les notes des stagiaires dans le module -métier et formation-

create proc ex3

as

begin

Update notation

Set note=note+1

where n_module in (select n_module from module where intitulémod='métier et formation')

end

4) La liste des stagiaires dont le nom commence par une lettre spécifiée par l'utilisateur

create proc ex4 @l char(1)

as

begin

select * from stagiare where nom like @l+'%'

end

5) Le bulletin de notes d'un stagiaire donné

create proc ex5 @num int as

begin

select * from stagiaire where stagiaire.N_stagiaire=@num

select note,intmod

from notation,stagiaire,module where stagiaire.N_stagiaire=notation.N_stagiaire

and stagiaire.N_stagiaire=@num and module.N_module=Notation.N_module

compute avg (note)

end

6) Liste des stagiaires inscrits entre deux dates

create proc ex6 @d1 datetime,@d2 datetime

as

begin

select * from stagiaire where dateinscrit between (@d1 and @d2)

end

7) Liste des stagiaires non notés pour le module -SGBDI-

create proc ex7

as

begin

select * from stagiaire where n_stagiaire not in (select n_stagiaire from notation where n_module in (select n_module from module where intmod='SGBDI'))

end

8) Avant de supprimer un stagiaire, vérifier s'il existe et vérifier s'il a des notes

create proc ex8 @num int

as

begin

if exists (select * from stagiaire where n_stagiaire=@num)

begin

if exists (select * from notation where n_stagiaire=@num)

begin

delete from notation where n_stagiaire=@num

print 'les notes du stagiaire numéro '+ convert(varchar(5),@num)+ 'ont été supprimés')

end

print 'stagiaire numéro' + convert (varchar(5),@num) + 'est supprimé'

delete from stagiaire where n_stagiaire=@num

end

else

begin

print 'le stagiaire numéro' + convert (varchar(5),@num) + 'n"existe pas'

end

end

9) Procédure qui supprime une filière avec l'ensemble des stagiaires affectés à cette filière

create proc ex9 @nf char(3)

as

begin

if exists (select * from filière where nomfil=@nf)

begin

if (select COUNT(*) from stagiaire where n_fil in (select n_fil from filiere where nomfil=@nf))>0

begin

delete from stagiaire where n_fil in (select n_fil from filiere where nomfil=@nf)

print 'les stagiaires de la filière'+@nf+'ont été supprimés'

end

delete from filiere where nomfil=@nf

print 'la filiere'+@nf+'a été supprimée'

end

else

begin

print 'la filière'+@nf+'n"existe pas'

end

end

10) Affecter une note pour un stagiaire : vérifier l'existance du stagiaire et du module. Vérifier si le stagiaire est déjà noté pour ce module

create proc ex10 @ns int, @m char(3), @not float

as

begin

if exists (select * from stagiaire where n_stagiaire=@ns)

begin

if exists (select * from module where n_module=@m)

begin

if exists (select * from notation where n_stagiaire=@ns and n_module=@m)

begin

print 'le stagiaire n°' + convert(varchar(3),@ns) + 'est déjà noté pour le Module' + @m

end

else

begin

insert into notation values (@ns,@m,@not)

print'la note est affecté au stagiaire'

end

end

else

begin

print'le module n"existe pas'

end

else

begin

print 'le stagiaire n"existe pas'

end

end

11) Supprimer les stagiaires inscrits l'année dernière et stocker les dans la table archive

if exists (select * from stagiaire where DATEDIFF(YEAR,dateinscrip,GETDATE())=1)

begin

select * into archive from stagiaire where DATEDIFF(YEAR,dateinscrip,GETDATE())=1

delete from stagiaire where DATEDIFF(YEAR,dateinscrip,GETDATE())=1

end

else

begin

print 'aucun stagiaire'

end

12) Afficher les informations des stagiaires qui ont plus de deux notes

select * from stagiaire

where n_stagiaire in (select n_stagiaire from notation group by n_stagiaire having COUNT (n_stagiaire)>=2)

13) Supprimer les 3 premiers stagiaires

delete from stagiaire

where n_stagiaire in (select top 10 n_stagiaire from notation group by n_stagiaire order by AVG(note) desc)