1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53
| drop table demcontrat cascade constraints ;
drop table demcontyp cascade constraints ;
drop table domdem cascade constraints ;
drop table demcon cascade constraints ;
-- Contrats
create table demcontrat (
cont_id int primary key,
cont_libelle varchar(20),
cont_type int
) ;
-- Types de Contrats
/*
create table demcontyp (
type_id int primary key,
type_libelle varchar(30)
) ;
*/
-- Réservation
create table domdem (
resa_id int primary key,
resa_date date
) ;
-- Correspondance contrat / réservation
create table demcon (
cont_id int,
resa_id int,
constraint demcon_pk primary key ( cont_id,resa_id),
constraint demcon_cont_id_fk foreign key (cont_id) references demcontrat (cont_id),
constraint demcon_resa_id_fk foreign key (resa_id) references domdem (resa_id)
) ;
insert into demcontrat values (1,'Contrat 1',1) ;
insert into demcontrat values (2,'Contrat 2',2) ;
insert into demcontrat values (3,'Contrat 3',1) ;
insert into domdem values (4,to_date('30/01/2003','dd/mm/yyyy')) ;
insert into domdem values (3,to_date('15/01/2004','dd/mm/yyyy')) ;
insert into domdem values (5,to_date('15/01/2004','dd/mm/yyyy')) ;
insert into domdem values (1,to_date('01/01/2003','dd/mm/yyyy')) ;
insert into domdem values (2,to_date('10/01/2003','dd/mm/yyyy')) ;
insert into demcon values (1,1) ;
insert into demcon values (1,2) ;
insert into demcon values (2,3) ;
insert into demcon values (2,4) ;
insert into demcon values (3,5) ;
select cont_id , cont_type, max(resa_date) as date_fin from demcontrat natural join demcon natural join domdem
group by cont_id, cont_type
order by cont_type, date_fin; |
Partager