NTIC DEV WWW.NTICRIAD.NET GESTION DES STAGIAIRES Soit la base de données suivante : Stagiaire (N°stagiaire, Nom, Prénom, datenaiss, , dateinscri, Adresse, tel, #Nfilière) Filière(Nfilière, Intituléfil, Capacité, nbreannées) Notation(N°notation ,#N°stagaire, #N°module, note) Module (N°module, intitulémod, masse horaire) Créer les procédures stockées 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 , filiere Where stagiaire.nfilè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()) beetween (@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 formation’) END
WWW.NTICRIAD.NET NTIC DEV
where intitulémod=’métier et
NTIC DEV WWW.NTICRIAD.NET 4) La liste des stagiaires dont le nom commence par une lettre spécifiée par l’utilisateur alter proc ex4 @l char(1) As Begin Select * from stagiaire 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 « SGBD I » 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 = 'sgbd I'))
WWW.NTICRIAD.NET NTIC DEV
NTIC DEV WWW.NTICRIAD.NET 8) Avant de supprimer un stagiaire, vérifier s’il existe et vérifier s’il a des notes. alter PROCEDURE liste44 @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 filiere 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 filière '+@nf+ ' a été supprimée' End Else begin Print 'la filière '+@nf+ ' n''existe pas'
WWW.NTICRIAD.NET NTIC DEV
NTIC DEV WWW.NTICRIAD.NET End end 10) La procédure qui permet de vérifier l’existence du stagiaire et de modifier par la suite la note affectée au module « SGBD II » create proc ex10 @ns int, @not float As Begin If exists (select * from stagiaire where n_stagiaire=@ns) Begin If exists(select * from notation where n_stagiaire=@ns And n_module in select n_module from module where intmod=’sgbd II’) begin Update notation set note =@not Where n_stagiaire=@n_stagiaire And n_mod in (select n_mod from module where nommod=’sgbd II’) Print ‘la nouvelle note ‘ + @not+ ‘est affectée pour le stagiaire ‘+@ns+’dans le module sgbd II’ End Else Begin print ‘ce stagiaire n’’a pas de note dans le module sgbdII’ end end Else Begin Print ‘le stagiaire n’’existe pas’ End end 11) Affecter une note pour un stagiaire ; vérifier l’existence du stagiaire et du module. Vérifier si le stagiaire est déjà noté pour ce module. create proc ex11 @ns int, @m car(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)
WWW.NTICRIAD.NET NTIC DEV
NTIC DEV WWW.NTICRIAD.NET 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ée au stagiaire’ End
end Else Begin Print ‘le module n’’existe pas’ End Else Begin Print ‘le stagiaire n’’existe pas’ End end
12) 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 13) 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)
14) Supprimer les 3 premiers stagiaires DELETE from stagiaire
WWW.NTICRIAD.NET NTIC DEV
NTIC DEV WWW.NTICRIAD.NET Where n_stagiaire in (SELECT TOP 10 n_stagiaire FROM notation Group by n_stagiaire Order by avg(note) desc) Delete from stagiaire Where n_stagiaire in (SELECT TOP 2 n_stagiaire FROM stagiaire)
WWW.NTICRIAD.NET NTIC DEV
NTIC DEV WWW.NTICRIAD.NET 15) Supprimer les stagiaires non notés dans le module ‘conception et modélisation’ DELETE stagiaire FROM (SELECT n_stagiaire FROM stagiaire where n_stagiaire not in (select n_stagiaire from notation where n_module in select n_module from module where nommod=‘conception et modélisation’) as t1 WHERE stagiaire.n_stagiaire = t1.n_stagiaire
Delete from stagiaire where n_stg not in (select n_stg from notation where n_module in (select n_module from module where intmod=’conception et modelisation’)) 16) afficher dans une colonne nommée « observation » la valeur ‘Echec’ ou ‘racheté’ ou ‘admis’ en fonction de la moyenne obtenue par le stagiaire create proc observation as begin SELECT 'Observation' = CASE WHEN avg(note) < 9 THEN 'Echec' WHEN avg(note)>= 9 and avg(note)< 10 THEN 'Racheté' WHEN avg(note) >= 10 THEN 'Admis' ELSE 'Erreur de formule!' END, avg(note) AS 'moyenne', n_stagiaire as 'numéro stagiaire' FROM notation group by n_stagiaire ORDER BY avg(note) desc end 17) Créer une procédure « compter » qui permet afficher le nombre de stagiaires inscrits dans une filière donnée CREATE PROCEDURE compter @fil int , @x int output AS begin select @x = count(*) from stagiaire where n_fil=@fil print ‘le nombre de stagiaires inscrits est :’+ convert(varchar(3),@x)
WWW.NTICRIAD.NET NTIC DEV
NTIC DEV WWW.NTICRIAD.NET end GO
17) Créer une procédure nommée « affecter » qui permet de modifier la valeur du champ capacité en lui affectant le nombre de stagiaires inscrits Utiliser la procédure compter pour calculer le nombre de stagiaires inscrits dans la filière CREATE proc affecter @fil int as declare @nb int begin set @nb=0 exec liste @fil , @nb output update filiere set capacite=@nb where n_fil=@fil end GO 18) effectuer la même opération pour l’ensemble des filières CREATE PROCEDURE iteratif1 AS declare @c int declare @n int begin select @n=select count(*) from filiere set @c=1 while @c <= @n begin exec affecter @c print ‘la filière n°’+@c+’ a été modifiée’ set @c=@c+1 end end
WWW.NTICRIAD.NET NTIC DEV