-- Find baker who won star baker in the lowest rated episode

SELECT
   r.baker
   , e.id
FROM
   results r
   , episodes e
WHERE
   r.episodeid = e.id
   and r.result = 'star baker'
   and e.viewers7day = (SELECT min(viewers7day) FROM episodes);

SELECT
   r.baker
   , e.id
FROM
   results r
   , episodes e
WHERE
   r.episodeid = e.id
   and r.result = 'star baker'
   and e.viewers7day <=ALL (SELECT viewers7day FROM episodes);


-- Find the episode with the highest number of eliminated people

SELECT
   r.episodeid
   , count(*)
FROM
   results r
WHERE
   r.result = 'eliminated'
GROUP BY
   r.episodeid
HAVING
   count(*) >=ALL (SELECT count(*) FROM results WHERE result='eliminated'
                   GROUP BY episodeid);
   


-- Find bakers who won a star baker but never won the technical challenge

SELECT
   baker
FROM
   results
WHERE
   result = 'star baker'
   and baker NOT IN (SELECT baker FROM technicals WHERE rank =1 );


SELECT
   baker
FROM
   results
WHERE
   result = 'star baker'
EXCEPT
SELECT
   baker
FROM
   technicals
WHERE   
    rank =1;


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

-- Find bakers who won a star baker in an episode but did not win the
   technical challenge in that episode

SELECT
   r.baker
   , r.episodeid
FROM
   results r
WHERE
   r.result = 'star baker'
   and r.baker NOT IN (SELECT
                           t.baker
		       FROM
		           technicals t
                       WHERE
		           t.rank =1
			   and t.episodeid = r.episodeid );


EXISTS  (QUERY)     <-----  return true if there are any tuples in the inner query

NOT EXISTS  (QUERY)  <--- returns true, if there are no tuples in the inner



SELECT
   r.baker
   , r.episodeid
FROM
   results r
WHERE
   r.result = 'star baker'
   and
      NOT EXISTS
        (SELECT *
        FROM
           technicals t
        WHERE
           t.rank =1
	   and t.episodeid = r.episodeid
	   and t.baker = r.baker);


SELECT
   r.baker
   , r.episodeid
FROM
   results r
WHERE
   r.result = 'star baker'
   and
      NOT EXISTS
        (SELECT 1
        FROM
           technicals t
        WHERE
           t.rank =1
	   and t.episodeid = r.episodeid
	   and t.baker = r.baker);


--- Find bakers who participated in every episode in the database
--- check participation with a tuple in signatures table.


SELECT
   b.baker
FROM
   bakers b
WHERE
   NOT EXISTS
      (SELECT 1 FROM episodes e
       WHERE NOT EXISTS
           (SELECT 1 FROM signatures s
	    WHERE s.episodeid = e.id
	          AND s.baker = b.baker)) ;

---> How can we rewrite this without NOT EXISTS?  




















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












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






























WITH statement
Correlated subqueries


For each baker find the number of favorites and number of star bakers
that they won.

SELECT
   baker
   , count(*) as numfavorites
FROM
   favorites
GROUP BY
   baker;

SELECT
   baker
   , count(*) as numstar
FROM
   results
WHERE
   result = 'star baker'
GROUP BY
   baker;


SELECT  *
FROM (SELECT
        baker
      , count(*) as numfavorites
     FROM
       favorites
    GROUP BY
       baker ) as numf;

WITH numf AS (
SELECT
   baker
   , count(*) as numfavorites
FROM
   favorites
GROUP BY
   baker),
nums AS (
SELECT
   baker
   , count(*) as numstar
FROM
   results
WHERE
   result = 'star baker'
GROUP BY
   baker)
SELECT
   numf.baker
   , nums.baker
   , numf.numfavorites
   , nums.numstar
FROM
   numf
   full join nums 
     on numf.baker = nums.baker ;


Find the bakers with the highest number of favorites and star bakers.


WITH
numf AS (
   SELECT
      baker
      , count(*) as numfavorites
   FROM
     favorites
   GROUP BY
     baker),
nums AS (
   SELECT
     baker
     , count(*) as numstar
   FROM
     results
  WHERE
     result = 'star baker'
  GROUP BY
     baker),
totalnum AS (     
   SELECT
     numf.baker
     , numf.numfavorites + nums.numstar as totalwins
   FROM
     numf join nums 
       on numf.baker = nums.baker)
SELECT
   baker
FROM
   totalnum
WHERE
   totalwins = (SELECT max(totalwins) FROM totalnum)
;


Find bakers who never won star baker and were never a favorite.

(SELECT
   baker
FROM
   bakers
EXCEPT
SELECT
   baker
FROM
   favorites)
EXCEPT
SELECT
   baker
FROM
   results
WHERE
   result = 'star baker';


SELECT
   baker
FROM
   bakers
WHERE
   baker NOT IN (SELECT baker FROM favorites)
   and baker NOT IN (SELECT baker FROM results
                     WHERE result = 'star baker');

WITH winners AS
((SELECT   baker FROM   favorites)
UNION
(SELECT   baker FROM    results WHERE    result = 'star baker'))
SELECT
   baker
FROM
   bakers
WHERE
   baker NOT IN (SELECT baker FROM winners);


SELECT
   b.baker
FROM
   bakers b
WHERE
   NOT EXISTS (SELECT 1 FROM favorites f WHERE f.baker=b.baker)
   and NOT EXISTS (SELECT 1 FROM results r
                     WHERE r.result = 'star baker' and
		           r.baker = b.baker);



Find bakers who did not compete in an episode, before they were
eliminated.

SELECT
   b.baker
   , e.id
FROM
   bakers b
   , results r
   , episodes e
WHERE
   b.baker = r.baker
   and r.result = 'eliminated'
   and e.id < r.episodeid
EXCEPT   
SELECT
   baker
   , episodeid
FROM
   signatures;

-- Rahul was never eliminated
baking=> delete from signatures where episodeid = 8 and baker = 'Rahul';
DELETE 1

--- Above query will never return Rahul
--- Alternate approach:

SELECT DISTINCT
    b.baker
FROM
    bakers b
    , episodes e
WHERE
    NOT EXISTS (SELECT 1
                FROM signatures s
		WHERE s.episodeid = e.id AND s.baker = b.baker)
    AND NOT EXISTS (SELECT 1 FROM results r
                WHERE r.baker = b.baker AND r.result='eliminated'
		      AND r.episodeid < e.id ) ;


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





Transactions

Insert/Update/Delete


Insert into episodes, episode 11
Create new table for least favorites and insert tuples:

Terry, 1 2
Karen, 4
Jon, 5
Manon, 6
Briony, 2, 3, 4, 5
Ruby, 1 7
Kim-Joy, 8
Rahul, 8 9

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



Add new attribute to bakers, proportion favorite and populate (update)


update bakers b
set prop = (select count(*)::float from favorites f
            where f.baker = b.baker )/
	   (select count(*)::float from signatures s
	    where s.baker = b.baker)


Add tuples to results least/most favorite (insert)

Delete from least favorites people who won star baker next episode (delete)


DELETE FROM leastfavorites l
WHERE l.baker IN (SELECT r.baker FROM results r
                  WHERE r.result = 'star baker'
		        and r.episodeid = l.episodeid + 1);

DELETE FROM leastfavorites l
WHERE EXISTS (SELECT 1 FROM results r
                  WHERE r.result = 'star baker'
		        and r.episodeid = l.episodeid + 1
			and r.baker = l.baker );