SQL DELETE STATEMENT
--------------------

DELETE FROM <relation> WHERE <condition> ;


 -- Delete from the relation, tuples satisfying the given condition

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') ;


DELETE FROM episodes WHERE firstaired is NULL ;

DELETE FROM eliminated WHERE episodeid = 5;

DELETE FROM eliminated ;



DROP TABLE eliminated ;

-- delete from favorites relation, all tuples that are
-- for a baker who won star baker in that episode



DELETE FROM
    favorites f
WHERE
    EXISTS (SELECT 1
            FROM results r
            WHERE f.episodeid = r.episodeid
                  AND f.baker=r.baker
                  AND r.result = 'star baker');


SQL UPDATE STATEMENT
--------------------


UPDATE <table>  SET <attribute> = <constant value> WHERE <condition>;

   -- Change only the tuples satisfying the condition in WHERE
   -- by changing given attributes to the given constant value

UPDATE results SET result = 'runner up' WHERE episodeid=10 AND baker='Kim-Joy';



ALTER TABLE episodes ADD season int ;
ALTER TABLE episodes ADD year int ;

UPDATE episodes SET season = 9 ;  -- all tuples from season 9

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') ;


UPDATE
    episodes
SET
    year = extract(year from firstaired)
WHERE
    firstaired is not null ; --only for tuples with an airdate value




ALTER TABLE bakers ADD numwins INT ;

UPDATE
    bakers 
SET
    numwins = (SELECT count(*)
                  FROM results r
                  WHERE r.baker = bakers.baker AND r.result='star baker') ;