EFM - SGBD1 - 2013-2014

A. Création de la base de données

//Auteur: CHAOULID
//Copyright:Exelib.net
//**D'autres écritures sont possibles**// 

create database Gestion_comptes
go
use Gestion_comptes

create table Client(Num_cl int primary key identity(1,1),Nom varchar(50), date_naissance datetime,Tel varchar(16))
create table Compte(Num_cpt int primary key identity(1,1), type varchar(50),date_creation datetime,Num_cl int foreign key references Client(Num_cl))
create table Operation(Num_opr int primary key identity(1,1),libelle varchar(50),type char(1) default '+',date_operation datetime,montant decimal(10,2),Num_cpt int foreign key references Compte(Num_cpt))

alter table Client add constraint CK_Client_dateNaissance check (DATEDIFF(DAY,date_naissance,GETDATE())/365.25 >=18)
alter table Client add constraint CK_Client_Tel check(Tel like '+212[56][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
alter table Operation add constraint CK_Operation_type check (type in ('+','-'))
alter table Operation add date_valeur as case type when '+' then DATEADD(DAY,-2,date_operation) when '-' then DATEADD(DAY,2,date_operation) end

insert into Client values('CLIENT1','2/2/1995','+212623334576')
insert into Client values('CLIENT2','2/2/1993','+212523334546')
insert into Client values('CLIENT3','2/2/1993','+212523334045')
insert into Client values('CLIENT4','2/8/1986','+212523334045')
insert into Compte values('TYPE1','14/1/2015',9)
insert into Compte values('TYPE4','14/1/2014',12)
insert into Operation values('OP1','-','14/12/2015',500,4)
insert into Operation values('OP2','-','12/3/2016',500,3)
insert into Operation values('OP6','+','16/1/2016',400,2)
insert into Operation (libelle,date_operation,montant,Num_cpt) values('OP3','4/1/2016',300,2)

select * from Client
select * from Compte
select * from Operation

 B. Créer les requêtes de sélection suivantes :

//1
select Num_cl,Nom,FLOOR(DATEDIFF(DAY,date_naissance,GETDATE())/365.25) as "Age",Tel
from Client
where Tel like'____5%045'
order by Age

//2
select *
from Compte
where Num_cpt not in (select distinct Num_cpt from Operation where MONTH(date_operation)=MONTH(GETDATE()))

//3

select c.Num_cl,ISNULL(Somme_Créditeurs,0) - ISNULL(Somme_Débiteurs,0) as "Solde"

from
( // table virtuelle c qui contient la somme des opérations créditeur de chaque client
select c.Num_cl,SUM(o.montant) as "Somme_Créditeurs"
from Operation o inner join Compte c on o.Num_cpt=c.Num_cpt
where o.type='+'
group by c.Num_cl
) c
left outer join
(   //table virtuelle d qui contient la somme des opérations débiteurs de chaque client
select c.Num_cl,SUM(o.montant) as "Somme_Débiteurs"
from Operation o inner join Compte c on o.Num_cpt=c.Num_cpt
where o.type='-'
group by c.Num_cl
) d
on c.Num_cl=d.Num_cl

//4

select c.*
from
( // table virtuelle cs qui contient le solde de chaque client
select c.Num_cl,ISNULL(Somme_Créditeurs,0) - ISNULL(Somme_Débiteurs,0) as "Solde"
from
(
select c.Num_cl,SUM(o.montant) as "Somme_Créditeurs"
from Operation o inner join Compte c on o.Num_cpt=c.Num_cpt
where o.type='+'
group by c.Num_cl
) c
left outer join
(
select c.Num_cl,SUM(o.montant) as "Somme_Débiteurs"
from Operation o inner join Compte c on o.Num_cpt=c.Num_cpt
where o.type='-'
group by c.Num_cl
) d
on c.Num_cl=d.Num_cl
) cs   // fin de la table virtuelle cs

inner join Client c on cs.Num_cl=c.Num_cl
where cs.Solde>10000

//5

select *
from Compte
where 
Num_cpt
in
( // comptes qu’ont enregistré une opération créditrice ces 3 derniers jours.
select distinct Num_cpt
from Operation
where type='+'and DATEDIFF(DAY,date_operation,GETDATE())<=3
)
and
Num_cpt
in
( // comptes qu’ont enregistré une opération débitrice ces 3 derniers jours.
select distinct Num_cpt
from Operation
where type='-'and DATEDIFF(DAY,date_operation,GETDATE())<=3
)

//5 une autre écriture
select distinct c.*
from Compte c inner join Operation o on c.Num_cpt=o.Num_cpt
where o.type='+'and DATEDIFF(DAY,o.date_operation,GETDATE())<=3
intersect
select distinct c.*
from Compte c inner join Operation o on c.Num_cpt=o.Num_cpt
where o.type='-'and DATEDIFF(DAY,o.date_operation,GETDATE())<=3

 C. Créer les requêtes de mise à jour suivantes :

1//
delete
from Compte
where Num_cpt not in (select distinct Num_cpt from Operation)

2//
update Operation
set montant=case 
when type='+' then montant*1.1
when type='-' then montant*0.9
else montant
end
where CAST(date_operation as DATE)=cast(GETDATE() as DATE) and Num_cpt=100

 D. Gestion de la sécurité:

1//
create login Ahmed with password='12345',default_database=Gestion_comptes
create login Salman with password='12345',default_database=Gestion_comptes

2//
use Gestion_comptes
create user Ahmed for login Ahmed
create user Salman for login Salman

3//
exec sp_addrolemember 'r_agents','Ahmed'
exec sp_addrolemember 'r_agents','Salman'

4//
grant update,select on Object::Operation to Ahmed,Salman