TDI 2A - Corrigé

DésignationPr PrixUnitaire Client(NumClient, NomClient, AdresseClient) Commande(NumCommande, DateCommande, #NumClient) Produit(CodeProduit, DesignationPr, PrixUnitaire) LigneCommande(#NumCommande , #CodeProduit, QuantiteCommande) 2) create database GesCommande

create table Client (NumClient int Primary key, NomClient varchar(200) not null, AdresseClient varchar(200) not null

create table Produit (CodeProduit varchar(100) Primary key, DesignationPr varchar(200) not null, PrixUnitaire float not null

create table LigneCommande (NumCommande int foreign key references Commande(NumCommande), CodeProduit varchar(100) foreign key references Produit(CodeProduit), QuantiteCommande int not null, constraint pk_LigneCmd primary key (NumCommande,CodeProduit)

insert into client values(1,'Alami', 'Rabat') insert into commande values(1,'01/02/2017',1) insert into Produit values(1,'Clavier USB', 50) insert into LigneCommande values(1,1,2) Exercice 2 (10Pts)

SELECT dbo.Operation.NumOp, dbo.Operation.MtOp, dbo.Operation.NumCpt, dbo.Operation.DateOp FROM dbo.Operation INNER JOIN dbo.Compte ON dbo.Operation.NumCpt = dbo.Compte.NumCpt INNER JOIN dbo.Client ON dbo.Compte.NumCli = dbo.Client.NumCli where dbo.Client.VilleCli='Rabat' and DateDiff(day,Dateop,GetDate())<=2

SELECT dbo.Operation.NumOp, dbo.Operation.MtOp, dbo.Operation.NumCpt, dbo.Operation.DateOp FROM dbo.Operation INNER JOIN dbo.Compte ON dbo.Operation.NumCpt = dbo.Compte.NumCpt INNER JOIN dbo.Client ON dbo.Compte.NumCli = dbo.Client.NumCli where dbo.Client.NumCli=@Id

begin return (SELECT count(dbo.Operation.NumOp) FROM dbo.Operation INNER JOIN dbo.Compte ON dbo.Operation.NumCpt = dbo.Compte.NumCpt INNER JOIN dbo.Client ON dbo.Compte.NumCli = dbo.Client.NumCli where dbo.Client.NumCli=@Id) end

declare @v varchar(50),@n int declare NbreClient cursor for select VilleCli From Client group by VilleCli open NbreClient fetch NbreClient into @v

while(@@FETCH_STATUS=0) begin set @n= (select count(*) from client where VilleCli=@v) print @v + ' '+convert(varchar(50),@n) fetch NbreClient into @v end close NbreClient deallocate NbreClient