drop database mediators;
create database mediators;
\c mediators;

create table R1(a int, b varchar);
insert into R1 values (1, 'Andy');
insert into R1 values (2, 'Beth');
insert into R1 values (3, 'Carole');
insert into R1 values (4, 'Cecil');
insert into R1 values (5, 'Cynthia');
select * from R1;

create table S1(a int, c varchar);
insert into S1 values(10, 'Paris');
insert into S1 values(11, 'Chicago');
select * from S1;

create table R2(a int, b varchar);
insert into R2 values(1, 'London');
insert into R2 values(2, 'Berlin');
select * from R2;

create table S2(a int, c varchar);
insert into S2 values(12, 'Oxford');
insert into S2 values (13, 'Beijing'); 
select * from S2;

create view S3 as
   select * from S1
   union all
   select * from S2
   union all
   (select r1.a as a, r2.b as c from R1 r1, R2 r2 where r1.a=r2.a);

select * from S3;
-- see also plan using https://explain.dalibo.com/
drop view S3; 

create view S3 as
   select * from S1
   union all
   select * from S2
   union all
   (select r1.a as a, r2.b as c from R1 r1, R2 r2 where r1.a=r2.a);

explain select * from S3 where a=3;

create view S4 as
   select * from R1 where b like 'C%';

explain select b from S4;
