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