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
|
create table PRESTATIONS (
ID_PRESTATION int not null,
LIBELLE char(32) not null,
constraint PK_PRESTATIONS primary key (ID_PRESTATION)
)
go
create table TYPEDETARIF (
ID_TYPEDETARIF int not null,
LIBELLE char(32) not null,
constraint PK_TYPEDETARIF primary key (ID_TYPEDETARIF)
)
go
create table TARIF (
ID_PRESTATION int not null,
IDTARIF int not null,
ID_TYPEDETARIF int not null,
VALEURTARIF int not null,
constraint PK_TARIF primary key (ID_PRESTATION, IDTARIF),
constraint FK_TYPEDETARIF foreign key (ID_TYPEDETARIF)
references TYPEDETARIF (ID_TYPEDETARIF)
on update cascade,
constraint FK_PRESTATION foreign key (ID_PRESTATION)
references PRESTATIONS (ID_PRESTATION)
on update cascade on delete cascade
)
go
insert into TYPEDETARIF values (1, 'Tarif 1 Année') ;
insert into TYPEDETARIF values (2, 'Tarif 1 Trimestre') ;
insert into TYPEDETARIF values (3, 'Tarif 1 Semaine') ;
insert into TYPEDETARIF values (7, 'Tarif 2 Année') ;
insert into PRESTATIONS values (1, 'Prestation Machin') ;
insert into PRESTATIONS values (2, 'Prestation Truc') ;
insert into TARIF values (1, 1, 1, 1000) ;
insert into TARIF values (1, 2, 2, 5000) ;
insert into TARIF values (2, 1, 1, 2000) ;
Select p.LIBELLE As Prestation, y.LIBELLE as 'Type tarif', t.VALEURTARIF as 'Valeur tarif'
From TARIF t, TYPEDETARIF y, prestations p
Where t.ID_TYPEDETARIF = y.ID_TYPEDETARIF
And t.ID_PRESTATION = p.ID_PRESTATION
;
Prestation Type tarif Valeur tarif
---------- ---------- ------------
Prestation Machin Tarif 1 Année 1000
Prestation Machin Tarif 1 Trimestre 5000
Prestation Truc Tarif 1 Année 2000 |
Partager