Correction Dossier 1 - Partie SQL pour <TDIju2011-EFF-TpV8>

par **Mr.Ayoub** Ven Avr 06, 2012 6:11 pm

-- Question 1
create database v8_tp_2011
go
use v8_tp_2011
go
create table Theme
(code_theme int primary key,
nom_theme varchar(50),
description_theme varchar(100))
go

create table Member
(login varchar(50) primary key,
nom varchar(50),
prenom varchar(50),
email varchar(50),
pass varchar(50))
go

create table role
(nom_role varchar(50) primary key)
go

create table rol_utilisateur
(login varchar(50) foreign key references Member(login),
nom_role varchar(50) foreign key references role(nom_role),
primary key (login,nom_role))
go

create table document
(code_document int primary key identity,
description_document varchar(100),
chemin_document varchar(100),
date_envoi datetime,
etat_document int,
code_theme int foreign key references Theme(code_theme), login varchar(50) foreign key references Member(login))
go

create table commentaire
(code_commentaire int primary key identity,
text_commentaire varchar(255),
date_commentaire datetime,
etat_commentaire int,
code_document int foreign key references document(code_document), login varchar(50) foreign key references Member(login))
Go

insert into Theme values(1,'TDI','INFORMATION TDI')
insert into Member values('mourad277','mourad','vitcho','
vitcho@gmail.com','142536')
insert into role values('Utilisateur')
insert into rol_utilisateur values('mourad277','Utilisateur')
insert into document values('document algorithme','doc/algo.pdf','01/05/2011',1,1,'mourad277')
insert into commentaire values('Merci pour le document','05/05/2011',1,1,'mourad277')

-- Question 2
create trigger qu2 on Member for insert as
begin
declare @login varchar(50)
select @login=login from inserted
insert into rol_utilisateur values(@login,'Utilisateur')
end

-- Question 3
create trigger qu3 on Member for insert as
begin
declare @login varchar(50)
select @login=login from inserted
if(@login='admin')
rollback
end

-- Question 4
create function qu4(@n int) returns int as
begin
declare @count int
select @count=count(*) from document where code_theme=@n
return @count
end

-- Question 5
create proc qu5 as
begin
select t.nom_theme,count(*) as Total from commentaire c,document d, Theme t where
c.login=d.login and t.code_theme=d.code_theme group by t.nom_theme
end

-- Question 6
create proc qu6 as
begin
select m.nom,m.prenom,count(*) from Member m,document d where m.login=d.login group by m.nom,m.prenom having count(*)>5
end