Correction Dossier 1 - Partie SQL TDIju2011-EFF-TpV1

-- Question 1
Create DataBase TDIJu2011_V1
Go
Use TDIJu2011_V1
Go

Create table personne(Matricule int primary key, nom varchar(50), prenom varchar(50), telephone varchar(50), passe varchar(20))
Create table marque(nom_marque varchar(50) primary key)
Create
table autocar(code_car int primary key, capacite int, date_achat
smalldatetime, consomation real, nom_marque varchar(50) foreign key
references marque(nom_marque))
Create table ville(code_ville int primary key,nom_ville varchar(50))
Create
table ligne(code_ligne int primary key, distance real,
code_ville_depart int foreign key references ville(code_ville),
code_ville_arrivee int foreign key references ville(code_ville))
Create
table calendrier(code_calendrier int primary key, jour_voyage int,
heure_depart real, heure_arrivee real, code_ligne int foreign key
references ligne(code_ligne))
Create table Voyage (code_voyage int
identity primary key, dh_depart_voyage smalldatetime, dh_arrive_voyage
smalldatetime, places int, compteur_depart int, compteur_arrivee int,
matricule int foreign key references personne(Matricule), code_car int
foreign key references autocar(code_car), code_calendrier int foreign
key references calendrier(code_calendrier))
Go


insert into personne values(1,'Ali','Ali','0522334455','123')
insert into personne values(2,'Ahmed','Ahmed','0522333365','321')
insert into marque values('Renault')insert into marque values('Mercedes')
insert into autocar values(1,50,'1/1/2000',20,'Renault')
insert into ville values(1,'Casablanca')
insert into ville values(2,'Rabat')
insert into ville values(3,'Tanger')
insert into ligne values(1,160,1,2)
insert into calendrier values(1,1,14.5,15.25,1)
insert
into voyage(dh_depart_voyage , dh_arrive_voyage , places ,
compteur_depart , compteur_arrivee , matricule , code_car,
code_calendrier) values('3/4/2012 15:30','3/4/2012
16:30',45,23452,23543,1,1,1)
insert into voyage(dh_depart_voyage ,
dh_arrive_voyage , places , compteur_depart , compteur_arrivee ,
matricule , code_car, code_calendrier) values('3/4/2012
15:30','3/4/2012 16:30',45,23452,23543,2,1,1)
Go

-- Question 2
alter table calendrier add constraint ctj check(jour_voyage between 1 and 7)
alter table voyage add constraint ctv check(dh_depart_voyage<dh_arrive_voyage)
alter table autocar add constraint cta check(capacite>0)

-- Question 3
alter table personne add np as nom + ' ' + prenom

-- Question 4
Create trigger Question4 on ligne for insert,update
as
begin
if
((select count(*) from ligne,inserted where
ligne.code_ville_depart=inserted.code_ville_depart and
ligne.code_ville_arrivee=inserted.code_ville_arrivee)>1)
begin
raiserror('Ligne Exist déjà',15,1)
rollback
end
end

insert into ligne values(2,360,1,3)

--Question 5
Create Function Question5(@cl int) returns real
as
begin
declare @s real
set @s= (select sum(places) from voyage,calendrier where voyage.code_calendrier=calendrier.code_calendrier and code_ligne=@cl)
return @s
end

select dbo.Question5(1)

-- Question 6
Create proc Question6 @cl int
as
begin
select voyage.* from voyage,calendrier where voyage.code_calendrier=calendrier.code_calendrier and code_ligne=@cl
end

Exec Question6 1

-- Question 7
Create function Question7(@cl int) returns varchar(200)
as
begin
declare @s varchar(200),@np varchar(50)
select @s='',@np=''
declare cr cursor for select np from personne,voyage,calendrier
where personne.matricule=voyage.matricule and voyage.code_calendrier=calendrier.code_calendrier and code_ligne=@cl
open cr
fetch next from cr into @np
while(@@fetch_status=0)
begin
set @s=@s + @np + ', '
fetch next from cr into @np
end
close cr
deallocate cr
return @s
end

select dbo.Question7(1)


Le Formateur Mr Driouch