Correction de la Série Complète T-SQL

Reda Sbai
30 mai 2026

Exercice 1
1 DECLARE @AgeMax INT ;
2 SELECT @AgeMax = MAX ( DATEDIFF ( year , dateN , GETDATE () ) ) FROM Stagiaire ;
3 PRINT ’L ’ ’ ge maximal est : ’ + CAST ( @AgeMax AS VARCHAR ) ;

Exercice 2
1 SELECT
2 f . NomFiliere ,
3 COUNT ( s . NumStg ) AS NombreStagiaires ,
4 CASE
5 WHEN COUNT ( s . NumStg ) = 0 THEN ’ Aucun stagiaire pour cette f i l i r e ’
6 WHEN COUNT ( s . NumStg ) < 10 THEN ’ Moins de 10 stagiaires pour cette
fili re ’
7 ELSE ’ Plus de 10 stagiaires pour cette f i l i r e ’
8 END AS Observation
9 FROM Filiere f
10 LEFT JOIN Stagiaire s ON f . NumFiliere = s . NumF
11 GROUP BY f . NomFiliere ;

Exercice 3
1 CREATE PROCEDURE p s _ S t a g i a i r e s S a n s N o t e s
2 AS
3 BEGIN
4 SELECT s . NumStg , s . NomStg
5 FROM Stagiaire s
6 LEFT JOIN Notation n ON s . NumStg = n . Numstg
7 WHERE n . Note IS NULL ;
8 END ;
9 GO

Exercice 4
1 CREATE PROCEDURE p s _ F i l i e r e s P l u s 3 M o d u l e s
2 AS
3 BEGIN
4 SELECT f . NomFiliere
5 FROM Filiere f

1
 6 JOIN Programme p ON f . NumFiliere = p . NumF
7 GROUP BY f . NumFiliere , f . NomFiliere
8 HAVING COUNT ( p . NumM ) > 3;
9 END ;
10 GO

Exercice 5
1 CREATE FUNCTION f n _ C o u n t S t g No t e S u p 1 2 ( @NumModule INT )
2 RETURNS INT
3 AS
4 BEGIN
5 DECLARE @Resultat INT ;
6 SELECT @Resultat = COUNT ( Numstg )
7 FROM Notation
8 WHERE NumM = @NumModule AND Note >= 12;
9 RETURN @Resultat ;
10 END ;
11 GO

Exercice 6
1 CREATE FUNCTION f n_ Ca lcu le rM oye nn e ( @NumStg INT )
2 RETURNS FLOAT
3 AS
4 BEGIN
5 DECLARE @Moyenne FLOAT ;
6 SELECT @Moyenne = SUM ( n . Note * p . Coeff ) / SUM ( p . Coeff )
7 FROM Notation n
8 JOIN Programme p ON n . NumM = p . NumM
9 JOIN Stagiaire s ON s . NumStg = n . Numstg AND s . NumF = p . NumF
10 WHERE n . Numstg = @NumStg ;
11
12 RETURN @Moyenne ;
13 END ;
14 GO

Exercice 7
1 CREATE PROCEDURE p s _ T r a i t e m e n t S t a g i a i r e s
2 AS
3 BEGIN
4 DECLARE @NumStg INT , @NomStg VARCHAR (50) , @PrenomStg VARCHAR (50) ,
@NomFiliere VARCHAR (50) ;
5 DECLARE @Moy FLOAT ;
6
7 DECLARE curseur_stg CURSOR LOCAL FOR
8 SELECT s . NumStg , s . NomStg , s . PrenomStg , f . NomFiliere
9 FROM Stagiaire s
10 JOIN Filiere f ON s . NumF = f . NumFiliere ;
11
12 OPEN curseur_stg ;
13 FETCH NEXT FROM curseur_stg INTO @NumStg , @NomStg , @PrenomStg ,
@NomFiliere ;

2
14
15 WHILE @@FETCH_STATUS = 0
16 BEGIN
17 PRINT @NomStg + ’ ’ + @PrenomStg ;
18 PRINT ’ F i l i r e : ’ + @NomFiliere ;
19
20 IF EXISTS ( SELECT 1 FROM Notation WHERE Numstg = @NumStg AND Note IS
NULL )
21 BEGIN
22 PRINT ’ En cours de traitement ’;
23 SELECT m . NomModule
24 FROM Notation n
25 JOIN Module m ON n . NumM = m . NumModule
26 WHERE n . Numstg = @NumStg AND n . Note IS NULL ;
27 END
28 ELSE IF (( SELECT COUNT (*) FROM Notation WHERE Numstg = @NumStg AND
Note < 10) > 2)
29 BEGIN
30 PRINT ’ Travail insuffisant ’;
31 SELECT m . NomModule
32 FROM Notation n
33 JOIN Module m ON n . NumM = m . NumModule
34 WHERE n . Numstg = @NumStg AND n . Note < 10;
35 END
36 ELSE
37 BEGIN
38 SELECT m . NomModule , p . Coeff , n . Note
39 FROM Notation n
40 JOIN Module m ON n . NumM = m . NumModule
41 JOIN Programme p ON p . NumM = m . NumModule
42 JOIN Stagiaire s ON s . NumStg = n . Numstg AND s . NumF = p . NumF
43 WHERE n . Numstg = @NumStg ;
44
45 SET @Moy = dbo . fn _Ca lc ul erM oy en ne ( @NumStg ) ;
46 PRINT ’ Moyenne : ’ + CAST ( ROUND ( @Moy , 2) AS VARCHAR ) ;
47 END ;
48

49 FETCH NEXT FROM curseur_stg INTO @NumStg , @NomStg , @PrenomStg ,