drop table if exists tmp ;
drop table if exists results ;
drop table if exists bakers;
drop table if exists episodes;

create table bakers(
      baker        varchar(10) primary key
      , fullname   varchar(100)
      , age        int    not null
      , occupation varchar(100)
      , hometown   varchar(100)
);

create table episodes (
    id               int  primary key
    , title          varchar(100)
    , firstaired     date
    , viewers7day  float   check(viewers7day > 0)
    , signature       varchar(100)
    , technical       varchar(100)
    , showstopper     varchar(100)
) ;


create table results (
      episodeid int
     , baker   varchar(100)
     , result   varchar(20)	  
     , primary key (episodeid, baker)
     , foreign key (episodeid) references episodes(id)
       on delete cascade
       on update cascade
     , foreign key (baker) references bakers(baker)
       on delete cascade
       on update cascade
);

create table tmp (
     id int primary key
     , episodeid int
     , baker   varchar(100)
     , foreign key (episodeid, baker) references results(episodeid, baker)
       on delete cascade
       on update set null
) ;


insert into bakers values('Antony','Antony Amourdoux',30,'Banker','London');
insert into bakers values('Briony','Briony Williams',33 ,'Full-time parent','Bristol');
insert into bakers values('Dan','Dan Beasley-Harling',36 ,'Full-time parent','London');
insert into bakers values('Imelda','Imelda McCarron',33 ,'Countryside recreation officer','County Tyrone');
insert into bakers values('Jon','Jon Jenkins',47 ,'Blood courier','Newport');
insert into bakers values('Karen','Karen Wright',60 ,'In-store sampling assistant','Wakefield');
insert into bakers values('Kim-Joy','Kim-Joy Hewlett',27 ,'Mental health specialist','Leeds');
insert into bakers values('Luke','Luke Thompson',30 ,'Civil servant/house and techno DJ','Sheffield');
insert into bakers values('Manon','Manon Lagrève',26 ,'Software project manager','London');
insert into bakers values('Rahul','Rahul Mandal',30 ,'Research scientist','Rotherham');
insert into bakers values('Ruby','Ruby Bhogal',29 ,'Project manager','London');
insert into bakers values('Terry','Terry Hartill',56 ,'Retired air steward','West Midlands');


insert into episodes values(1,'Biscuits', date '2018-8-28', 9.55,'24 Regional Biscuits','8 Wagon Wheels','3D Biscuit Self-Portrait');
insert into episodes values(2,'Cakes', date '2018-9-4', 9.31,'16 Traybakes','Le Gâteau Vert','Chocolate Collar Cake');
insert into episodes values(3,'Bread', date '2018-9-11', 8.91,'12 Chelsea Buns','8 Non-Yeasted Garlic Naan Breads','Korovai');
insert into episodes values(4,'Desserts', date '2018-9-18', 8.88,'Meringue Roulade','Raspberry Blancmange with 12 Langues du Chat biscuits','Melting Chocolate Ball Dessert');
insert into episodes values(5,'Spice', date '2018-9-25', 8.67,'Ginger Cake','12 Ma''amoul','Spiced Biscuit Chandelier');
insert into episodes values(6,'Pastry', date '2018-10-2', 9.30,'12 Samosas','6 Puits D''amour','Shaped Banquet Pie');
insert into episodes values(7,'Vegan', date '2018-10-9', 9.54,'8 Savoury Vegan Tartlets','Vegan Tropical Fruit Pavlova','Vegan Celebratory Cake');
insert into episodes values(8,'Danish', date '2018-10-16', 9.69,'2 Smørrebrød','14 Æbleskiver','Kagemand/Kagekone');
insert into episodes values(9,'Pâtisserie (Semi-final)', date '2018-10-23', 9.50,'24 Madeleines','Torta Setteveli','Parisian Pâtisserie Window');
insert into episodes values(10,'Final', date '2018-10-30', 10.34,'12 Iced Doughnuts','6 Campfire Pita Breads','Landscape Dessert');

insert into results values(1,'Manon','star baker') ;
insert into results values(1,'Imelda','eliminated') ;
insert into results values(2,'Rahul','star baker') ;
insert into results values(2,'Luke','eliminated') ;
insert into results values(3,'Rahul','star baker') ;
insert into results values(3,'Antony','eliminated') ;
insert into results values(4,'Dan','star baker') ;
insert into results values(5,'Terry','eliminated') ;
insert into results values(5,'Kim-Joy','star baker') ;
insert into results values(5,'Karen','eliminated') ;
insert into results values(6,'Briony','star baker') ;
insert into results values(6,'Dan','eliminated') ;
insert into results values(7,'Kim-Joy','star baker') ;
insert into results values(7,'Jon','eliminated') ;
insert into results values(8,'Ruby','star baker') ;
insert into results values(8,'Manon','eliminated') ;
insert into results values(9,'Ruby','star baker') ;
insert into results values(9,'Briony','eliminated') ;

insert into tmp values(1, 5, 'Kim-Joy');
insert into tmp values(2, 5, 'Kim-Joy');
insert into tmp values(3, 8, 'Manon');