Contrôle continue N°2 SGBD II Durée : 1h30
Sur le schéma relationnel suivant :
Emp (num_emp, nom, prenom, salaire, prime, num_deparatement)
Dept (num_dept, libelle, chef) NB : chef est un employé
Questions :
Question 1 (4 pts) :
Procédure 1 : Ajoutez une nouvelle colonne STARS varchar(100) par code , dans la table EMP qui permet de stocker des étoiles « * », Ecrire un programme qui récompense les employés en leur attribuant une étoile dans la colonne STARS par tranche de salaire de 1000DHs.
Question 2 (4 pts):
Procédure 2 : lister les employés qui sont sous la direction d’un chef (dont le num du chef est
donnée par paramètre)
Question 3 (4 pts):
Ecrire une procédure stocké qui affiche le nombre d’employé dans un département donnée (en
paramètre) :
- s’il manque le paramètre, la procédure retourne 0
- si le département n’existe pas, la procédure stocké retourne 1
- si le département existe, la procédure stocké retourne 2 et affiche le nombre d’employé
Dkhal parametre code
Question 4 (4 pts) :
Ecrire une fonction qui retourne les employés subordonné direct d’un employé donnée en
paramètre s’il est chef, sinon retourne -1
Question 5 (4 pts) :
Créer une fonction qui retourne une table qui prend en paramètre le numéro de département et produit une table qui énumère les employés de ce département .
· Le nom des employés est écrit en lettres majuscules
· Les prénoms des employés commencent par une lettre majuscule
CCorrection :
s
create database CCN2_SGBDII
use CCN2_SGBDII
create table Emp(
num_emp int primary key,
nom varchar(255),
prenom varchar(255),
salaire float,
prime int,
num_deparatement int foreign key references Dept(num_dept)
)
create table Dept(
num_dept int primary key,
libelle varchar(255),
chef int
)
insert into Dept values(1,'Departement I',1)
insert into Emp values(1,'Hillal','Abdessamad',7000,2,1)
alter table Dept add constraint FK_Dept_Chef foreign key (chef) references Emp(num_emp)
insert into Dept values(2,'Departement II',1)
insert into Emp values(2,'Shari','Anas',8000,5,2)
insert into Emp values(3,'Sheriff','Abdelhaq',8000,4,2)
insert into Dept values(3,'Departement III',3)
insert into Emp values (4,'Dahmane','Brahime',6700,7,3,NULL)
insert into Emp values (5,'barik','Mohamed',4800,4,3,NULL)
insert into Dept values(4,'Departement IV',2)
insert into Emp values (6,'Badir','Zaid',7300,10,4,NULL)
insert into Dept values(5,'Departement V',4)
select * from Dept
select * from Emp
alter table Emp add STARS varchar(100)
alter proc PS_1
As
begin
declare C1 cursor for (select num_emp,salaire from emp)
declare @n int, @s float
open C1
fetch next from C1 into @n, @s
while(@@FETCH_STATUS = 0)
begin
update Emp set STARS = REPLICATE('* ',cast(@s/1000 as int )) where num_emp = @n
fetch next from C1 into @n, @s
end
close C1
deallocate C1
end
exec PS_1
select * from emp
create proc PS_2(@chef int)
as
Begin
if exists(select * from Dept where chef = @chef)
begin
select * from Emp where num_deparatement in (select num_dept from Dept where chef = @chef)
end
else
print 'N extist pas'
end
exec PS_2 1
create proc PS_3(@num_dept int = NULL)
As
Begin
Declare @res int
if (@num_dept = NULL)
set @res = 0
if not exists(select * from Dept where num_dept = @num_dept)
set @res = 1
else
begin
set @res = 2
select COUNT(*) from Emp where num_deparatement = @num_dept
end
return @res
end
exec PS_3 4
create function Fn_1(@chef int) returns int
As
begin
declare @nbr int
set @nbr = -1
if exists(select * from Dept where chef = @chef)
begin
select @nbr = count(*) from Emp where num_deparatement in (select num_dept from Dept where chef = @chef)
end
return @nbr
end
select dbo.Fn_1(1)
alter function Fn_2(@num_deparatement int) returns @t table(nom varchar(255),prenom varchar(255))
As
begin
declare C2 cursor for (select nom,prenom from Emp where num_deparatement = @num_deparatement)
declare @n varchar(255), @p varchar(255),@temp varchar(255),@i int
open C2
fetch next from C2 into @n,@p
while(@@fetch_status = 0)
begin
select @temp = '',@i = 1
while(@i <= len(@p))
begin
if(@i = 1)
set @temp = @temp + upper(substring(@p,@i,1))
else
set @temp = @temp + substring(@p,@i,1)
set @i = @i + 1
end
insert into @t values(upper(@n),@temp)
fetch next from C2 into @n,@p
end
close C2
deallocate C2
return
end
select * from dbo.Fn_2(1)