ISTA
Sidi Youssef
Ben Ali
Marrakech
SGBD 2
Série N° 2
Formateur : LAMOURI Najib
Exercice 1: Soit le modèle relationnel suivant :
CLIENT (codeclt, nomclt, prenomclt, adresse, cp, ville)
PRODUIT (référence, désignation, prix)
TECHNICIEN (codetec, nomtec, prenomtec, tauxhoraire)
INTERVENTION (numero, date, raison, codeclt, référence, codetec)
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
2) Créer une fonction qui prend une ville en paramètre et retourne le nombre de clients qui
habitent cette ville.
3) Créer une fonction qui retourne le nombre d’interventions effectuées par le technicien
dont le nom est passé en paramètre.
4) Créer une fonction qui retourne la somme des prix des produits qui ont subi une
intervention entre deux dates passées en paramètre et qui sont à la propriété du client
auquel le nom est passé en paramètre.
5) Créer une fonction qui retourne la liste des interventions (numéro, date, nomclt,
désignation) du technicien auquel le nom est passé en paramètre.
6) Créer une fonction qui retourne la liste des clients qui n’ont pas demandé d’interventions
entre deux dates passées en paramètre.
Exercice 2: Soit le modèle relationnel suivant :
CLIENT (codeclt, nomclt, prenomclt, adresseclt, CPclt, villeclt)
APPARTEMENT (ref, superficie, pxvente, secteur, coderep, codeclt)
REPRESENTANT (coderep, nomrep, prenomrep)
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
2) Créer une fonction qui retourne la liste des clients (nom, prénom) qui habitent la ville
passée en paramètre.
3) Créer une fonction qui retourne le nombre d’appartements vendus par le représentant
passé en paramètre (coderep).
4) Créer un curseur Cur_Apprt qui permet de parcourir tous les appartements (ref, pxvente,
secteur) et qui permet de modifier le pxvente seulement.
5) Créer une fonction qui retourne le nouveau prix de vente selon l’ancienne valeur qui est
passée en paramètre :
a. Le prix est augmenté de 10% s’il est inferieur à 250000
b. Le prix est augmenté de 7% s’il est entre 250000 et 380000
c. Le prix est augmenté de 5% s’il est supérieur à 380000
6) Modifier les prix (pxvente) des appartements en utilisant la fonction définie dans la question 5)
et le curseur déclaré à la question 4).
Exercice 3: Soit le modèle relationnel suivant :
STAGIAIRE (code, nom, groupe, dateNaissance)
MODULE (code, nom, Coef)
EVALUER (codeStg, codeMod, Date, note)
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu d’enregistrements.
2) Créer une fonction qui retourne la liste des modules dans lesquels le stagiaire (nom) passé
en paramètre a passé des évaluations.
3) Créer une fonction qui retourne les dates et les notes obtenues pour chaque date dans le
module (code) passé en paramètre par le stagiaire (code) passé en paramètre.
4) Créer une fonction qui retourne la moyenne obtenue par le stagiaire (code) passé en
paramètre dans le module (code) passé en paramètre (utiliser la fonction définie dans 3).
5) Créer une fonction qui retourne la moyenne générale obtenue par le stagiaire (code) passé
en paramètre (Indications : utiliser un curseur qui parcourt les modules et la fonction
définie dans 4).
6) Créer une fonction qui retourne la mention obtenue selon la moyenne passée en
paramètre :
a. Ajourné : si la moyenne est inférieure à 10
b. Passable : si la moyenne appartient à [10, 12[
c. A. Bien : si la moyenne appartient à [12, 14[
d. Bien : si la moyenne appartient à [14, 16[
e. T. Bien : si la moyenne appartient à [16, 20]
7) Créer un curseur qui permet de parcourir les stagiaires (Code, Nom, Groupe) et d’afficher
pour chaque stagiaire la moyenne générale obtenue et la décision correspondante.
create database ex1_Serie2
use ex1_serie2
create table CLIENT (
codeclt int primary key ,
nomclt varchar(50),
prenomclt varchar(50),
adresse varchar(50),
cp int,
ville varchar(50)
)
insert into CLIENT values(1,'oudad','widad','mhamid',40000,'Marrakech')
insert into CLIENT values(2,'sidda','youssef','al qods',40000,'El jadida')
insert into CLIENT values(3,'akhana','Mohmed','douar lasker',40000,'Marrakech')
create table PRODUIT (
référence int primary key ,
désignation varchar(50),
prix money )
insert into PRODUIT values (1,'Produit1',20000)
insert into PRODUIT values (2,'Produit2',30000)
insert into PRODUIT values (3,'Produit3',40000)
create table TECHNICIEN (
codetec int primary key ,
nomtec varchar(50),
prenomtec varchar(50),
tauxhoraire float
)
insert into TECHNICIEN values (1,'SALHI','Samir',10)
insert into TECHNICIEN values (2,'alaoui','khalid',8)
insert into TECHNICIEN values (3,'nassiri','Fouad',9)
create table INTERVENTION (
numero int primary key ,
dateI datetime ,
raison varchar(50),
codeclt int foreign key references CLIENT(codeclt),
référence int foreign key references PRODUIT(référence) ,
codetec int foreign key references TECHNICIEN(codetec))
insert into INTERVENTION values (1,'20/08/2011','raison1',1,1,1)
insert into INTERVENTION values (2,'13/09/2011','raison2',2,2,2)
insert into INTERVENTION values (3,'27/08/2011','raison3',3,3,3)
--2
create function NbrClientParVille( @Ville varchar(50))
returns int
as
begin
declare @NB int
select @NB = count(codeclt) from CLIENT where Ville = @Ville
return @NB
end
select ville ,dbo.NbrClientParVille(ville) as [nbr client]from CLIENT group by ville
--3
create function Nbrinervention(@Nomtec varchar(50))
returns int
as
begin
declare @NB int
select @NB = count(INTERVENTION.codetec) from TECHNICIEN inner join INTERVENTION on TECHNICIEN.codetec = TECHNICIEN.codetec where TECHNICIEN.nomtec = @Nomtec
return @NB
end
select codetec , nomtec ,dbo.Nbrinervention('SALHI') from TECHNICIEN
--4
create function Sommeprix (@Nomclt varchar(50),@Date1 datetime , @Date2 datetime)
returns float
as
begin
declare @Somme float
select @Somme = Sum (prix) from PRODUIT P inner join INTERVENTION I on I.référence = P.référence inner join CLIENT C on C.codeclt = I.codeclt where C.nomclt = @Nomclt and I.dateI between @Date1 and @Date2
return @Somme
end
--5
create function Lite_inter(@Nomtec varchar(50))
returns table
as
return (select INTERVENTION.numero, INTERVENTION.dateI, CLIENT.nomclt, PRODUIT.désignation from TECHNICIEN t inner join INTERVENTION I on I.codeTEC= t.codeTEC inner join PRODUIT P on P.référence = I.référence where t.nomtec = @Nomtec)
--6
create function ListeClt (@date1 datetime,@date2 datetime)
returns table
as
return (select * from ClIENT inner join INTERVENTION on CLIENT.Codeclt = INTERVENTION.Codeclt where dateI not between @date1 and @date2)
select * from ClIENT inner join INTERVENTION on CLIENT.Codeclt = INTERVENTION.Codeclt where dateI not between '12/09/2011' and '14/09/2011'
------------------------------------------------------------------------------------------------
create database Ex2
use Ex2
create table CLIENT (
codeclt int primary key,
nomclt varchar(50),
prenomclt varchar(50),
adresse varchar(50),
cp int ,
ville varchar(50))
create table APPARTEMENT (
ref int primary key ,
superficie int ,
pxvente money,
secteur varchar(50),
coderep int foreign key references REPRESENTANT(coderep),
codeclt int foreign key references CLIENT(codeclt),
)
create table REPRESENTANT (
coderep int primary key,
nomrep varchar(50),
prenomrep varchar(50))
insert into CLIENT values(1,'oudad','widad','mhamid',40000,'Marrakech')
insert into CLIENT values(2,'sidda','youssef','al qods',40000,'El jadida')
insert into CLIENT values(3,'akhana','Mohmed','douar lasker',40000,'Marrakech')
--3
declare cur_app cursor
global
scroll
dynamic
for
select
--------------------------------------------------------------------------------------------
create database ex3
use ex3
create table STAGIAIRE (
code int primary key,
nom varchar(50),
groupe varchar(50),
dateNaissance datetime)
create table MODULE (
code int primary key,
nom varchar(50),
Coef int )
create table EVALUER (
codeStg int foreign key references STAGIAIRE (code),
codeMod int foreign key references MODULE(code),
DateE datetime,
note float
primary key (codeStg,codeMod,DateE)
)
insert into STAGIAIRE values (1,'widad','GA','20/05/1990')
insert into STAGIAIRE values (2,'ali','GB','30/06/1990')
insert into STAGIAIRE values (3,'Khalid','GA','09/12/1991')
insert into MODULE values (1,'MOD1',2)
insert into MODULE values (2,'MOD2',1)
insert into MODULE values (3,'MOD3',7)
insert into EVALUER values (1,1,'13/06/2011',15)
insert into EVALUER values (2,1,'16/06/2011',14.06)
insert into EVALUER values (3,2,'16/06/2011',12)
insert into EVALUER values (1,3,'15/11/2011',17)
--2
create function liste_mod (@nom varchar(50))
returns table
as
return (select M.* from MODULE M inner join EVALUER E ON M.code = E.codeMod inner join STAGIAIRE S on S.code = E.codeStg where S.nom = @nom)
--3
create function liste_moy(@codeS int , @codeM int)
returns table
as
return(select DateE,note from EVALUER where codestg =@codeS and codeMod = @codeM)
--4
create function Moyenne(@codeM varchar(50), @codeS varchar(50))
returns float
as
begin
declare @M float
select @M = AVG(note) from liste_moy(@codeS,@codeM)
return @M
end
---5
create function moygen (@codeS int)
returns float as
begin
declare cur_module cursor for select code,dbo.Moyenne(@codeS,codeM) ,coef from MODULE
declare @moy float
---6
declare @code int , @nom varchar(50),@groupe varchar(50),@
declare cur cursor for select code,nom,groupe from STAGIAIRE
open cur
fetch next from cur into @code,@nom,@groupe
while @@fetch_status = 0
begin
print 'Code :'+ convert(varchar,@code)+'NOM :'+@nom +'groupe :'+@groupe
fetch next from cur into @code,@nom,@groupe
end
close cur
deallocate cur
Sidi Youssef
Ben Ali
Marrakech
SGBD 2
Série N° 2
Formateur : LAMOURI Najib
Exercice 1: Soit le modèle relationnel suivant :
CLIENT (codeclt, nomclt, prenomclt, adresse, cp, ville)
PRODUIT (référence, désignation, prix)
TECHNICIEN (codetec, nomtec, prenomtec, tauxhoraire)
INTERVENTION (numero, date, raison, codeclt, référence, codetec)
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
2) Créer une fonction qui prend une ville en paramètre et retourne le nombre de clients qui
habitent cette ville.
3) Créer une fonction qui retourne le nombre d’interventions effectuées par le technicien
dont le nom est passé en paramètre.
4) Créer une fonction qui retourne la somme des prix des produits qui ont subi une
intervention entre deux dates passées en paramètre et qui sont à la propriété du client
auquel le nom est passé en paramètre.
5) Créer une fonction qui retourne la liste des interventions (numéro, date, nomclt,
désignation) du technicien auquel le nom est passé en paramètre.
6) Créer une fonction qui retourne la liste des clients qui n’ont pas demandé d’interventions
entre deux dates passées en paramètre.
Exercice 2: Soit le modèle relationnel suivant :
CLIENT (codeclt, nomclt, prenomclt, adresseclt, CPclt, villeclt)
APPARTEMENT (ref, superficie, pxvente, secteur, coderep, codeclt)
REPRESENTANT (coderep, nomrep, prenomrep)
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
2) Créer une fonction qui retourne la liste des clients (nom, prénom) qui habitent la ville
passée en paramètre.
3) Créer une fonction qui retourne le nombre d’appartements vendus par le représentant
passé en paramètre (coderep).
4) Créer un curseur Cur_Apprt qui permet de parcourir tous les appartements (ref, pxvente,
secteur) et qui permet de modifier le pxvente seulement.
5) Créer une fonction qui retourne le nouveau prix de vente selon l’ancienne valeur qui est
passée en paramètre :
a. Le prix est augmenté de 10% s’il est inferieur à 250000
b. Le prix est augmenté de 7% s’il est entre 250000 et 380000
c. Le prix est augmenté de 5% s’il est supérieur à 380000
6) Modifier les prix (pxvente) des appartements en utilisant la fonction définie dans la question 5)
et le curseur déclaré à la question 4).
Exercice 3: Soit le modèle relationnel suivant :
STAGIAIRE (code, nom, groupe, dateNaissance)
MODULE (code, nom, Coef)
EVALUER (codeStg, codeMod, Date, note)
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu d’enregistrements.
2) Créer une fonction qui retourne la liste des modules dans lesquels le stagiaire (nom) passé
en paramètre a passé des évaluations.
3) Créer une fonction qui retourne les dates et les notes obtenues pour chaque date dans le
module (code) passé en paramètre par le stagiaire (code) passé en paramètre.
4) Créer une fonction qui retourne la moyenne obtenue par le stagiaire (code) passé en
paramètre dans le module (code) passé en paramètre (utiliser la fonction définie dans 3).
5) Créer une fonction qui retourne la moyenne générale obtenue par le stagiaire (code) passé
en paramètre (Indications : utiliser un curseur qui parcourt les modules et la fonction
définie dans 4).
6) Créer une fonction qui retourne la mention obtenue selon la moyenne passée en
paramètre :
a. Ajourné : si la moyenne est inférieure à 10
b. Passable : si la moyenne appartient à [10, 12[
c. A. Bien : si la moyenne appartient à [12, 14[
d. Bien : si la moyenne appartient à [14, 16[
e. T. Bien : si la moyenne appartient à [16, 20]
7) Créer un curseur qui permet de parcourir les stagiaires (Code, Nom, Groupe) et d’afficher
pour chaque stagiaire la moyenne générale obtenue et la décision correspondante.
create database ex1_Serie2
use ex1_serie2
create table CLIENT (
codeclt int primary key ,
nomclt varchar(50),
prenomclt varchar(50),
adresse varchar(50),
cp int,
ville varchar(50)
)
insert into CLIENT values(1,'oudad','widad','mhamid',40000,'Marrakech')
insert into CLIENT values(2,'sidda','youssef','al qods',40000,'El jadida')
insert into CLIENT values(3,'akhana','Mohmed','douar lasker',40000,'Marrakech')
create table PRODUIT (
référence int primary key ,
désignation varchar(50),
prix money )
insert into PRODUIT values (1,'Produit1',20000)
insert into PRODUIT values (2,'Produit2',30000)
insert into PRODUIT values (3,'Produit3',40000)
create table TECHNICIEN (
codetec int primary key ,
nomtec varchar(50),
prenomtec varchar(50),
tauxhoraire float
)
insert into TECHNICIEN values (1,'SALHI','Samir',10)
insert into TECHNICIEN values (2,'alaoui','khalid',8)
insert into TECHNICIEN values (3,'nassiri','Fouad',9)
create table INTERVENTION (
numero int primary key ,
dateI datetime ,
raison varchar(50),
codeclt int foreign key references CLIENT(codeclt),
référence int foreign key references PRODUIT(référence) ,
codetec int foreign key references TECHNICIEN(codetec))
insert into INTERVENTION values (1,'20/08/2011','raison1',1,1,1)
insert into INTERVENTION values (2,'13/09/2011','raison2',2,2,2)
insert into INTERVENTION values (3,'27/08/2011','raison3',3,3,3)
--2
create function NbrClientParVille( @Ville varchar(50))
returns int
as
begin
declare @NB int
select @NB = count(codeclt) from CLIENT where Ville = @Ville
return @NB
end
select ville ,dbo.NbrClientParVille(ville) as [nbr client]from CLIENT group by ville
--3
create function Nbrinervention(@Nomtec varchar(50))
returns int
as
begin
declare @NB int
select @NB = count(INTERVENTION.codetec) from TECHNICIEN inner join INTERVENTION on TECHNICIEN.codetec = TECHNICIEN.codetec where TECHNICIEN.nomtec = @Nomtec
return @NB
end
select codetec , nomtec ,dbo.Nbrinervention('SALHI') from TECHNICIEN
--4
create function Sommeprix (@Nomclt varchar(50),@Date1 datetime , @Date2 datetime)
returns float
as
begin
declare @Somme float
select @Somme = Sum (prix) from PRODUIT P inner join INTERVENTION I on I.référence = P.référence inner join CLIENT C on C.codeclt = I.codeclt where C.nomclt = @Nomclt and I.dateI between @Date1 and @Date2
return @Somme
end
--5
create function Lite_inter(@Nomtec varchar(50))
returns table
as
return (select INTERVENTION.numero, INTERVENTION.dateI, CLIENT.nomclt, PRODUIT.désignation from TECHNICIEN t inner join INTERVENTION I on I.codeTEC= t.codeTEC inner join PRODUIT P on P.référence = I.référence where t.nomtec = @Nomtec)
--6
create function ListeClt (@date1 datetime,@date2 datetime)
returns table
as
return (select * from ClIENT inner join INTERVENTION on CLIENT.Codeclt = INTERVENTION.Codeclt where dateI not between @date1 and @date2)
select * from ClIENT inner join INTERVENTION on CLIENT.Codeclt = INTERVENTION.Codeclt where dateI not between '12/09/2011' and '14/09/2011'
------------------------------------------------------------------------------------------------
create database Ex2
use Ex2
create table CLIENT (
codeclt int primary key,
nomclt varchar(50),
prenomclt varchar(50),
adresse varchar(50),
cp int ,
ville varchar(50))
create table APPARTEMENT (
ref int primary key ,
superficie int ,
pxvente money,
secteur varchar(50),
coderep int foreign key references REPRESENTANT(coderep),
codeclt int foreign key references CLIENT(codeclt),
)
create table REPRESENTANT (
coderep int primary key,
nomrep varchar(50),
prenomrep varchar(50))
insert into CLIENT values(1,'oudad','widad','mhamid',40000,'Marrakech')
insert into CLIENT values(2,'sidda','youssef','al qods',40000,'El jadida')
insert into CLIENT values(3,'akhana','Mohmed','douar lasker',40000,'Marrakech')
--3
declare cur_app cursor
global
scroll
dynamic
for
select
--------------------------------------------------------------------------------------------
create database ex3
use ex3
create table STAGIAIRE (
code int primary key,
nom varchar(50),
groupe varchar(50),
dateNaissance datetime)
create table MODULE (
code int primary key,
nom varchar(50),
Coef int )
create table EVALUER (
codeStg int foreign key references STAGIAIRE (code),
codeMod int foreign key references MODULE(code),
DateE datetime,
note float
primary key (codeStg,codeMod,DateE)
)
insert into STAGIAIRE values (1,'widad','GA','20/05/1990')
insert into STAGIAIRE values (2,'ali','GB','30/06/1990')
insert into STAGIAIRE values (3,'Khalid','GA','09/12/1991')
insert into MODULE values (1,'MOD1',2)
insert into MODULE values (2,'MOD2',1)
insert into MODULE values (3,'MOD3',7)
insert into EVALUER values (1,1,'13/06/2011',15)
insert into EVALUER values (2,1,'16/06/2011',14.06)
insert into EVALUER values (3,2,'16/06/2011',12)
insert into EVALUER values (1,3,'15/11/2011',17)
--2
create function liste_mod (@nom varchar(50))
returns table
as
return (select M.* from MODULE M inner join EVALUER E ON M.code = E.codeMod inner join STAGIAIRE S on S.code = E.codeStg where S.nom = @nom)
--3
create function liste_moy(@codeS int , @codeM int)
returns table
as
return(select DateE,note from EVALUER where codestg =@codeS and codeMod = @codeM)
--4
create function Moyenne(@codeM varchar(50), @codeS varchar(50))
returns float
as
begin
declare @M float
select @M = AVG(note) from liste_moy(@codeS,@codeM)
return @M
end
---5
create function moygen (@codeS int)
returns float as
begin
declare cur_module cursor for select code,dbo.Moyenne(@codeS,codeM) ,coef from MODULE
declare @moy float
---6
declare @code int , @nom varchar(50),@groupe varchar(50),@
declare cur cursor for select code,nom,groupe from STAGIAIRE
open cur
fetch next from cur into @code,@nom,@groupe
while @@fetch_status = 0
begin
print 'Code :'+ convert(varchar,@code)+'NOM :'+@nom +'groupe :'+@groupe
fetch next from cur into @code,@nom,@groupe
end
close cur
deallocate cur