TP
TP : Révision
Année Universitaire 2022/2023
TP SQL Server.
TP : Révision Requêtes SQL+Transact_SQL+
Correction
Partie II : Programmation BD Transact_SQL.
Reprenez la même Base de Données Gestion Voiture et programmer les Procédures Stockées
et les fonctions suivantes. Partie II : Programmation BD Transact_SQL.
Client(Cin, Nom_Prenom, Profession, Adresse, Ville, Tel)
Opération(Numéro, Date_Operation, Nbre_Jour, Prix_Unitaire, Avance, Réglé, #CIN, #Ma-
tricule)
Voiture(Matricule, Marque, Type, Nbre_Chevaux, Disponible)
Employé(Cin, Nom_Prenom, Adresse, Date_Naissance, Date_Recrutement, Grade, Salaire, #Id-
Service)
Service(Id, Nom, #IdVille)
Ville(Id, Nom)
Travail à faire :
Q1) Écrire un programme/ requête qui permet d'a cher les employé.
Q2)Écrire un programme/ requête qui calcule le nombre des employés dans un service
donné en paramètre.
Q3) Écrire une procédure qui permet d'a cher les employé.
Q4)Écrire une procédure qui calcule le nombre des employés dans un service donné en
paramètre.
EMSI Casablanca A.U 2022/2023.
Q5) Écrire une fonction qui retourne les noms et prénoms des employés avec les noms
de service auxquels ils appartiennent.
Q6) Écrire une fonction qui calcule l'ancienneté d'un employé.
Q7) Écrire une procédure qui augmente le salaire d'un employé selon ancienneté.
plus de 10 ans 20%
moins de 10 ans 15%
Q8) Écrire une fonction qui calcule l'age d'un employé.
Q9) Écrire une procédure qui calcule l'age et l'ancienneté des employés.
Correction complète
3
Partie II : Programmation BD SQL Server
--Q1 :
--Ecrire un programme ou une requête qui affiche les employés
--Réponse 1 :
Begin
Select *
From employé;
End;
--Solution avec curseur
Declare --declaration
C cursor for (select *
From employé);
Begin
Open C;--ouverture
Fetch next from C --fetcher la premiere ligne
While (@@fetch_status=0)--fetch
Begin
Fetch next from C;
End
Close C;--fermer le curseur
Deallocate C;--liberer le curseur
End
--Utiliser un curseur pour afficher les noms prénoms et les salaires des employés.
----Réponse 2 :
Declare --declaration
C cursor for (select [nom prenom], salaire
From employé);
Declare
@Sal money,
@Np varchar (20);
Begin
Open C;--ouverture
Fetch next from C into @Np, @Sal; --fetcher la premiere ligne
While (@@fetch_status=0)--fetch
Begin
Print ‘l’’employé ‘+@Np+ ‘a le salaire ‘+ cast(@Sal as varchar) ;
Fetch next from C into @Np, @Sal;
End
Close C;--fermer le curseur
Deallocate C;--liberer le curseur
End
--Q2
--Ecrire un programme / une requete qui calcule le nombre des employés.
--Reponse 2
Begin
Select count(*) as [nbre employés]
From employé;
End
--Autre reponse
Declare
@nbre int;
Begin
Select @nbre=count(*) as [nbre employés]
From employé;
Print ‘le nombre des employés est :’+ cast(@nbre as varchar) ;
end
--Proposition
--Calculer l’ancienneté d’une opération utiliser la table opération getdate()-dateoper
--faire avec fonction
--fonction table pour calculer l’ancienté a toutes les operations de location
--1—création:
create function calculanc()
returns table
return(select DATEDIFF(year, dateoper, getdate()) as ancienté
from operation)
end
--2—execution :
select * from calculanc();
--fonction scalaire pour calculer l’ancienté a une operation de location
--1—création:
create function calculancienty(@num int)
returns int
as
begin
declare
@anc int
select @anc=DATEDIFF(year, dateoper, getdate())
from opération
where numéro=@num
return @anc
end
--2—execution :
select calculancienty(1)
---------------------------------------------------------------------------------------------------------
---utiliser un curseur + procedure
--1 creation
Create procedure p_anc
as
Declare --declaration
C cursor for (select num
From operation);
Declare
@num int,
@anc int;
Begin
Open C;--ouverture
Fetch next from C into @num; --fetcher la premiere ligne
While (@@fetch_status=0)--fetch
Begin
@anc =select [Link](@num) ;
Print ‘l’’ operation ‘+cast(@num as varchar)+ ‘a depassé‘+ cast(@anc as
varchar) +’ ans d’’ancienté’;
Fetch next from C into @num
End
Close C;--fermer le curseur
Deallocate C;--liberer le curseur
End
--2 execution
Execute p_anc
-- les procedures stockées+ les fonctions+curseurs
--Q3: Ici, on utilise une procédure stockée normale(sans parametres)
--(sans parametres), on va creer cette procedure dans
--le schema dbo
--snytaxe
--1--creation
--create procedure nomprocedure
--as
--begin
--traitement
--end
--2--execution
--execute nomprocedure
--1--creation
create procedure raffichemployé
as
begin
select * from employé;
end
--Autre solution
create procedure raffichemployé
as
declare
C cursor for (select * from employé);
Begin
Open C;
Fetch next from C;
While(@@fetch_status=0)
Begin
Fetch next from C;
End
Close C;
Deallocate C;
end
--2-- execution
execute affichemployé
---------------------------------------------------------------------------------------------------------
--Q4: programme 4
--Procedure stockée avec parametres entrée
--@id int
--snytaxe
--1--creation
--create procedure nomprocedure(@nomparametres type)
--as
--begin
--traitement en utilisant le parametres @nomparametres
--end
--2--execution
--execute nomprocedure valeurdeparametres
--1--creation
create procedure calculemployé(@id int)
as
begin
select COUNT(*) from employé where Idser=@id;
end
--Solution 2:
create procedure calculemployé(@id int)
as
declare
@nbre int;
begin
select @nbre=COUNT(*) from employé where Idser=@id;
print ‘le nombre des employés est: ‘+ cast(@nbre as varchar) ;
end
--2--execution
execute calculemployé 1
--Q5: programme 5
--une fonction table retourne plusieurs valeurs ou le resultat d'une requete select qui va retourner
une table
--syntaxe
/*
--1--creation
create function nomfonction(parameters input)
retruns table
return (requete de selection);
--2-- execution
select * from nomfct(parameters input)
*/
--1 creation
create function affichemployéservice()
returns table
return(select e1.[nom prenom], [Link]
from [Link]é e1, [Link] s
where [Link]=[Link]);
*/
--execution
select *
from affichemployéservice()
--------------------------------------
--Q6: programme 6: une fct scalaire : la fonction scalaire retourne une seule valeur.
--qui calcule l'ancienté d'un employé
--son cin est passé en parametres input
---------------------------------------
--select DATEDIFF(year,date_recrutement,getdate()) as ancienté
--from employé where cin=@cin;
--select * from employé
-----------------------------------------
--1-- syntaxe
/*
create function nomfct(parametres d'entree)
returns type de retour(int, varchar,....)
as
begin
declare
@nomvariableretour type de retour
begin
select @nomvariableretour=........
return @nomvariableretour;
end
--2-- execution
select [Link](parameters)--Obligatoire
print [Link](parameters)
*/
--------------------------------------
--1--creation
create function calculanc(@cin varchar(20))
returns int
as
begin
declare
@anc int
select @anc=datediff(year, date_recrutement, getdate())
from [Link]é
where cin=@cin
return @anc;
end
--2--execution-------------------------
--select [Link]('A123456')
--print [Link]('A123456')
---------------------------------------------------------------------------------------------------------------------------
--Q7 --procedure avec parametres input
---------------------------------------
--La procedure augmentesaliare va appeler
--la fonction calculanc pour calculer
--l'ancienté
--1--creation
create procedure augmentesalaire(@cin varchar(10))
as
declare
@anc int;
begin
select @anc=calculanc(@cin)
if (@anc>10)
begin
update employé set salaire=salaire*1.20
where cin=@cin
end
else
begin
update employé set salaire=salaire*1.15
where cin=@cin
end
end
--2—execution
execute augmentesalaire 'A123456'
-------------------------------------------------------------------------------------------------------------------------------------
--Q8: function scalaire qui retourne l’age d’un employé
--1—création:
create function calculage(@cin varchar(10))
returns int
as
begin
declare
@age int
select @age=DATEDIFF(year, date_naissance, getdate())
from employé
where cin=@cin
return @age
end
--2—execution :
select calculage('A123456')
--------------------------------------------------------------------------------------------------------------------------------------
--Q9
--Procedure normale sans parametres qui calcule l’age et l’ancienté des employés en utilisant un --
curseur
--1--creation
create procedure calculancageemp
as
declare
@anc int
@age int ,
@cin varchar(20),
@nomp varchar(20)
declare c1 cursor for (select [nom prenom], cin from employé)
begin
open c1
fetch next from c1 into @nomp, @cin;
while(@@FETCH_STATUS=0)
begin
select @anc=calculanc(@cin)
select @age=alculage(@cin)
print 'l''employé'+@nomp+'a l''age'+convert(varchar,@age)+'avec ancienté'+cast(@anc as varchar)
fetch next from c1 into @nomp, @cin;
end
close c1
deallocate c1
end
--2--execution
Execute calculancageemp