-- 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?