SET/BAG OPERATIONS// ORDER BY // LIMIT
=========================================



SELECT
   episodeid
FROM
   showstoppers
WHERE
   lower(make) like '%chocolate%'
ORDER BY
   episodeid DESC
;


SELECT
   episodeid
FROM
   showstoppers
WHERE
   lower(make) like '%chocolate%'
ORDER BY
   episodeid DESC
LIMIT
   5
;

------

Set operators

Unique tuples in each
SELECT FROM WHERE
UNION
SELECT FROM WHERE

Unique common tuples

SELECT FROM WHERE
INTERSECT
SELECT FROM WHERE

Unique tuples only in the top query

SELECT FROM WHERE
EXCEPT            -- set difference
SELECT FROM WHERE


Bag operators

SELECT FROM WHERE
UNION ALL
SELECT FROM WHERE

SELECT FROM WHERE
INTERSECT ALL
SELECT FROM WHERE

SELECT FROM WHERE
EXCEPT ALL
SELECT FROM WHERE

R UNION ALL S = {m+n copies of t |there are m copies of t in R and n
                copies of t is S}

R INTERSECT ALL S = {min(m,n) copies of t| there are m copies of t in R and n
                copies of t is S}

R EXCEPT ALL S = {max(0,m-n) copies of t| there are m copies of t in R and n
                copies of t is S}




SELECT   --q2
   episodeid
FROM
   signatures
WHERE
   lower(make) like '%cardamom%'
   or lower(make) like '%ginger%'
EXCEPT ALL
SELECT    --q1
   episodeid
FROM
   showstoppers
WHERE
   lower(make) like '%chocolate%'
ORDER BY
   episodeid
;

SELECT    --q1
   episodeid
FROM
   showstoppers
WHERE
   lower(make) like '%chocolate%'
EXCEPT ALL
SELECT   --q2
   episodeid
FROM
   signatures
WHERE
   lower(make) like '%cardamom%'
   or lower(make) like '%ginger%'
ORDER BY
   episodeid
;

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

Finds all bakers who were never star bakers.


SELECT  --- all bakers
   baker
   , fullname
FROM
   bakers 
EXCEPT
SELECT    -- bakers whowon once at least
   b.baker
   , b.fullname
FROM
   results r
   , bakers b
WHERE
   b.baker = r.baker
   and r.result = 'star baker'
ORDER BY
   baker
   , fullname
;