CREATE TABLE AND INSERT STATEMENTS
------------------------------------

INSERT INTO results VALUES(10, 'Rahul', 'winner');

INSERT INTO results(baker, episodeid, result) VALUES('Ruby', 10, 'runner up');

INSERT INTO results(baker, episodeid) VALUES('Kim-Joy', 10);

INSERT INTO episodes(id, title, signature, technical, showstopper)
VALUES(11,'The Great Christmas Bake Off','12 Iced Biscuits','6 Laufabrauð','Hidden Design Christmas Present Cake') ;



CREATE TABLE eliminated (
    episodeid  int
    , baker    character varying(100)
    , PRIMARY KEY (episodeid, baker)
    , FOREIGN KEY (episodeid) REFERENCES episodes(id)
    , FOREIGN KEY (baker) REFERENCES bakers(baker)
) ;



INSERT INTO eliminated (baker, episodeid)
SELECT
   baker, episodeid
FROM
   results
WHERE
   result = 'eliminated';



CREATE TABLE eliminated as
      SELECT episodeid, baker FROM results WHERE result = 'eliminated';

ALTER TABLE eliminated ADD PRIMARY KEY(episodeid, baker) ;
ALTER TABLE eliminated ADD FOREIGN KEY (episodeid) REFERENCES episodes(id);
ALTER TABLE eliminated ADD FOREIGN KEY (baker) REFERENCES bakers(baker);


TRANSACTIONS
-------------------

A set of operations that is executed as a single unit, zero, one or
more operations in a transaction may change data.

- Atomicity: all or nothing: either the transaction completely
  succeeds and makes all changes, or it fails and has no effect.

- Concurrency: each operation executes as if it is the only program
executing.

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

BEGIN; --- starts a transaction

CREATE TABLE tmp2(id int) ;
INSERT INTO tmp2 VALUES(1) ;

COMMIT; -- successfull end of transaction
ROLLBACK; -- unsuccessfull end of transaction