Correction EFF 2011 v1 TDI (sql)

--1
create database transport
use transport
create table Voyage(code_v int identity primary key , dh_deppart_v datetime , dh_arrive_v datetime , place int , cpt_depart int , cpt_arrive int , mtr int foreign key references Personnel(mtr), code_car int foreign key references Autocar(code_cat), code_c int foreign key references Calendrier(code_c) )
create table Personnel(mtr int primary key , nom varchar(20) , prenom varchar(20) , tel int , passe varchar(20))
create table Autocar(code_cat int primary key , capacité int , date_achat date , consommation float , nom_m varchar(20))
create table Calendrier(code_c int primary key , jour_v date , heure_dep datetime , heure_arr datetime , code_ligne int)
create table Ligne(code_ligne int primary key , distance float , code_v_dep int foreign key references Ville(code_ville) , code_v_arr int foreign key references Ville(code_ville))
create table Ville(code_ville int primary key, nom_ville varchar(20))
create table Marque(Nom_marque varchar(20) primary key)


select * from autocar
insert into Voyage values('17/12/2001 17:00:00','17/12/2001 19:00:00',41,40000,40500,10,20,30)
insert into Calendrier values(30,'17/12/2001','17:00:00','19:00:00',59)
insert into Ligne values(59,500,7,8)
insert into Ligne values(65,500,7,8)
insert into Ville values(7,'casa')
insert into Ville values(8,'rabat')
insert into Marque values('volvo')
insert into Marque values('audi')
insert into Marque values('mercedes')
insert into Autocar values(20,41,'11/12/2000',7,'volvo')
insert into Autocar values(22,25,'11/12/2000',7,'audi')
insert into Personnel values(10,'ahmed','abdo',0522021254,555)

--2
alter table Calendrier
add constraint c check(jour_v like '01/%d/%d')

alter table Voyage
add constraint cc check(dh_deppart_v < dh_arrive_v)

alter table Autocar
add check(capacité >0)

--3
alter table Personnel
add NP as nom +''+ prenom

select * from Personnel

--4
create trigger t1 on Ligne
for insert
as
begin
begin tran
declare @code_v_dep int, @code_v_arr int
select @code_v_dep = @code_v_dep from inserted
select @code_v_arr = @code_v_arr from inserted
if(select COUNT(*) from Ligne where @code_v_dep = @code_v_dep and @code_v_arr = @code_v_arr)>1
begin
print('ligne éxiste dèja')
rollback transaction
end
else
commit tran
end

Posted in Examen Fin Formation