SQL - SET COMPARISONS IN QUERY
---------------------------------


SELECT
   attributes, expressions over attributes, constants
FROM
   relations r1, r2, ...
WHERE
   expression over attributes of r1,r2,...: compare to each
   other or to a constant
GROUP BY
   attributes
HAVING
   aggregate = value;

SELECT
   baker
FROM
   bakers
WHERE
   hometown = 'London'
   or hometown = 'Bristol'
   or hometown = 'Wakefield';


SELECT
   baker
FROM
   bakers
WHERE
   hometown IN ('London','Bristol','Wakefield');


SELECT
   baker
FROM
   bakers
WHERE
   hometown NOT IN ('London','Bristol','Wakefield');



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

value IN (QUERY)   --> can be easily written by a join query
value NOT IN (QUERY) --> similar to except!


SELECT baker FROM results WHERE result = 'star baker';

SELECT * FROM bakers
WHERE baker IN (SELECT baker FROM results WHERE result = 'star baker') ;

SELECT DISTINCT
   b.*
FROM
   bakers b
   , results r
WHERE
   b.baker = r.baker
   and r.result = 'star baker' ;


-- Bakers who never were given star baker award!

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


value > ANY (QUERY)
value = ANY (QUERY)   --> same as IN
value <> ANY (QUERY)   

value <> ALL (QUERY)  --> same as NOT IN
value >= ALL (QUERY) 
value > ALL (QUERY)


Which baker won the star baker highest number of times?

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


SELECT
  baker
  , count(*) as numtimes
FROM
  results
WHERE
  result = 'star baker'
GROUP BY
  baker
HAVING
  count(*) >=ALL (SELECT count(*) FROM results WHERE result = 'star baker'
                 GROUP BY baker);




Which episode had the highest ratings?

SELECT
   *
FROM
   episodes
WHERE
   viewers7day >=All (SELECT viewers7day FROM episodes) ;


Who was the youngest baker(s)?

SELECT
    *
FROM
    bakers
WHERE
    age <=ALL (SELECT age FROM bakers);


SELECT
    *
FROM
    bakers
WHERE
    age = (SELECT min(age) FROM bakers);