Correction Var8 2011 (Base de donnée)

par Mr.Hicham Mer Juil 13, 2011 1:32 pm

Q 1 :

Code:: use master go if exists(select name from sys.databases where name = 'vv') drop database vv create database vv go use vv go if OBJECT_ID('Membre','U') is not null drop table Membre create table Membre(login varchar(20) primary key not null, nom_membre nvarchar(50), prenom_membre nvarchar(50), email varchar(20), passe varchar(15)) go if OBJECT_ID('Theme','U') is not null drop table Theme create table Theme(code_theme int primary key not null, nom_theme nvarchar(50), description_theme nvarchar(max)) go if OBJECT_ID('Document','U') is not null drop table Document create table Document(code_document int primary key not null, description_document nvarchar(max), chemin_document varchar(100), date_envoi datetime, etat_document int, code_theme int foreign key references Theme(code_theme) on delete no action on update no action, login varchar(20) foreign key references Membre(login) on delete no action on update no action) go if OBJECT_ID('Commentaire','U') is not null drop table Commentaire create table Commentaire(code_commentaire int identity(1,1) primary key not null, date_commentaire datetime, text_commentaire text, etat_commentaire int, code_document int foreign key references Document(code_document) on delete no action on update no action, login varchar(20) foreign key references Membre(login) on delete no action on update no action) go if OBJECT_ID('Role','U') is not null drop table Role create table Role(nom_role nvarchar(20)primary key not null) go if OBJECT_ID('Role_utlisateur','U') is not null drop table Role_utlisateur create table Role_utlisateur(login varchar(20) foreign key references Membre(login) on delete cascade on update cascade, nom_role nvarchar(20) foreign key references Role(nom_role) on delete cascade on update cascade, constraint pk_ru primary key(login,nom_role)) go /* Jeu D'essai */ insert into Membre values('tdi2','hicham','karim','ttt@tt.com','azqswx') insert into Membre values('tri2','hanan','souad','ttt@tt.com','azqswx123') insert into Theme values(1,'theme1','description 1') insert into Theme values(2,'theme2','description 2') insert into Document values(1,'description 1','d:\doc1','23/11/2010',1,1,'tdi2') insert into Document values(2,'description 2','d:\doc2','12/09/2010',2,2,'tri2') insert into Commentaire values('23/11/2010','commentaire 1',1,1,'tdi2') insert into Commentaire values('12/09/2010','commentaire 2',2,2,'tri2') insert into Role values('role 1') insert into Role values('role 2') insert into Role_utlisateur values('tdi2','role 1') insert into Role_utlisateur values('tri2','role 1')

Q 2:

Code:: if OBJECT_ID('tr1','TR') is not null drop trigger tr1 go create trigger tr1 ON Membre for insert as begin declare @login varchar(50) set @login = (select login from INSERTED) insert into Role_utilisateur values(@login,'utilisateur') end

Q 3:

Code:: if OBJECT_ID('tr2','TR') is not null drop trigger tr2 go create trigger tr2 ON Membre for delete as declare @login varchar(20) select @login = login from inserted if @login = (select login from inserted where login='admin') begin raiserror('Suppression Impossible',16,1) rollback transaction end

Q 4:

Code:: if OBJECT_ID('fn1','FN') is not null drop function fn1 go create function fn1(@code int) returns int as begin return( select count(D.code_document) As Nombre_Documents from Document D where D.code_theme = @code) end

Q 5:

Code:: if OBJECT_ID('ps1','P') is not null drop proc ps1 go create proc ps1 as select T.nom_theme, 'nombre de commentaire'=count(C.code_commentaire) from Commentaire C inner join Document D on D.code_document = C.code_document inner join Theme T on T.code_theme = D.code_theme group by T.nom_theme

Q 6:

Code:: if OBJECT_ID('ps2','') is not null drop proc ps2 go create proc ps2 as select M.login ,count(D.code_document) as 'nbr_doc' from Document D inner join Membre M on M.login = D.login group by M.login having count(D.code_document) > 5

par Souka_Souka Mer Juil 13, 2011 10:34 pm

Had variente mnhoussa jat fia hhhhhh

par Souka_Souka Mer Juil 13, 2011 10:50 pm

Hadik lkhra madrtch having

par Contenu sponsorisé

Correction Var8 2011 (Base de donnée)

Correction Var8 2011 (Base de donnée)

Re: Correction Var8 2011 (Base de donnée)

Re: Correction Var8 2011 (Base de donnée)

Re: Correction Var8 2011 (Base de donnée)