'>

SQL Server Training : Creat and Use Fonctions

 Consider the following relational model:
 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)

Create Tables then insert some records:

 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)



Create a function that takes a parameter and returns city in the number of clients inhabit this city:
 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

Create a function that returns the number of procedures performed by the technician
whose name is passed as a parameter.


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

Create a function that returns the sum of the prices of products which have undergone actions between two dates passed as parameters and are the property of the customer which name is passed as a parameter:

 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

Create a function that returns the list of interventions (number, date, nomclt, designation) the technician to whom the name is passed as a parameter:

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)

Create a function that returns a list of customers who have not requested interventions passed as parameters between two dates:
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'

By Drupal Study