-------------------------------------------------------------------------------
-- ex_independance.sql
-- 26 mars 2020
-------------------------------------------------------------------------------

drop table billet cascade constraints;
create table billet(client varchar2(10), dest varchar2(10), prix integer);
insert into billet values('Riton', 'Rio', 100);
insert into billet values('Rita', 'Saint Trop', 50);
insert into billet values('Riton', 'Saint Trop', 50);
select * from billet;
select client from billet where dest = 'Saint Trop';

drop table voyage;
drop table train;
create table voyage(client varchar2(10), idt integer); 
create table train(idt integer, dest varchar2(10), prix integer); 
insert into voyage values('Riton', 1);
insert into voyage values('Riton', 2);
insert into voyage values('Rita', 2);
insert into train values(1, 'Rio', 100);
insert into train values(2, 'Saint Trop', 50);
drop table billet;
select * from voyage;
select * from train;

-------------------------------------------------------------------------------
-- la bonne maniere :

drop table billet cascade constraints;
create table billet(client varchar2(10), dest varchar2(10), prix integer);
insert into billet values('Riton', 'Rio', 100);
insert into billet values('Rita', 'Saint Trop', 50);
insert into billet values('Riton', 'Saint Trop', 50);
select * from billet;

-- NON : select client from billet where dest = 'Saint Trop';

create or replace view vbillet as
        select * from billet;

select * from vbillet; -- OUI !

select client from vbillet where dest = 'Saint Trop';

drop table voyage;
drop table train;
create table voyage(client varchar2(10), idt integer); 
create table train(idt integer, dest varchar2(10), prix integer); 
insert into voyage values('Riton', 1);
insert into voyage values('Riton', 2);
insert into voyage values('Rita', 2);
insert into train values(1, 'Rio', 100);
insert into train values(2, 'Saint Trop', 50);
drop table billet;
select * from voyage;
select * from train;

create or replace view vbillet as
        select client, dest, prix
	    from voyage, train
	    where voyage.idt = train.idt;

select client from vbillet where dest = 'Saint Trop';

-------------------------------------------------------------------------------