----------------------
Announcements:
----------------------

- There could be chainsaw noise!
- Unavailable this weekend and monday!

- New DB server  (whitelist: rpidbsprof@gmail.com)
- New Submitty exercise format
- Hw#4 is coming.... (may be by the end of day, though not guaranteed)
- Exam #1 is being graded
  --> some checks will be done after grades are released.

---------------
     SQL
---------------

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

SELECT
   attributes to return
FROM
   relations
WHERE
   join conditions and other conditions

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


bakers(baker,fullname,age,occupation,hometown)
episodes(id,title,firstaired,viewers7day,signature,technical,showstopper)
results(episodeid,baker,result)
technicals(episodeid,baker,rank)
signatures(episodeid,baker,make)
showstoppers(episodeid,baker,make)

Find pair of bakers A,B who are from the same town and have placed in
ranks one place apart (eg 2nd 3rd, or 4tg 5th) in the technical
challenge of the same episode.

SELECT DISTINCT
    b1.fullname
    , b2.fullname
FROM
    bakers b1
    , bakers b2
    , technicals t1
    , technicals t2
WHERE
    b1.baker > b2.baker
    and t1.baker = b1.baker
    and t2.baker = b2.baker
    and t1.episodeid = t2.episodeid
    and b1.hometown = b2.hometown
    and abs(t1.rank-t2.rank) = 1
ORDER BY
    b1.fullname asc
    , b2.fullname
;

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


SELECT
   baker as bakershortname
   , fullname as bakerfullname
FROM
   bakers
WHERE
   age >= 30
EXCEPT
SELECT
   b.baker
   , b.fullname
FROM
   bakers b
   , results r
WHERE
   b.baker = r.baker
   and r.result = 'eliminated'
ORDER BY
   bakershortname
;


Find bakers who
   (are either at least 30 years old or have won technical 2 times)
     and were never eliminated.

((30 years old) union (bakers who won 2 times)) - (bakers eliminated)

(SELECT
   baker as baker1
   , fullname as f1
FROM
   bakers
WHERE
   age >= 30
UNION 
SELECT
   b.baker
   , b.fullname
FROM
   bakers b
   , technicals t1
   , technicals t2
WHERE
   b.baker = t1.baker
   and b.baker = t2.baker
   and t1.episodeid <> t2.episodeid
   and t1.rank = 1
   and t2.rank = 1
)
EXCEPT
SELECT
   b.baker
   , b.fullname
FROM
   bakers b
   , results r
WHERE
   b.baker = r.baker
   and r.result = 'eliminated'
ORDER BY
   baker1
   , f1
;


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



SELECT
   baker as bakershortname
   , fullname as bakerfullname
FROM
   bakers
WHERE
   age >= 30
EXCEPT
SELECT
   b.baker
   , b.fullname
FROM
   bakers b
   , results r
WHERE
   b.baker = r.baker
   and r.result = 'eliminated';


Find bakers who
   (are either at least 30 years old or have won technical 2 times)
     and were never eliminated.

---------

set operations

SELECT FROM WHERE
UNION/INTERSECT/EXCEPT
SELECT FROM WHERE

bag operations!
SELECT FROM WHERE
UNION ALL/INTERSECT ALL/EXCEPT ALL
SELECT FROM WHERE


    - or are at least 30 years old.


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

Aggregates:
min,max,sum,avg,stdev

SELECT
   age
   , hometown
FROM
   bakers;


SELECT
   min(age)
   , max(age)
   , max(age) - min(age) as agedifference
   , min(hometown)
FROM
   bakers;

baking=> select * from foobar ;
 id | name 
----+------
  1 | abc
  2 | def
    | ghi
    | 
  2 | klm
(5 rows)

baking=> select count(*) from foobar ;
 count 
-------
     5
(1 row)

baking=> select count(*), count(id) from foobar ;
 count | count 
-------+-------
     5 |     3
(1 row)

baking=> select count(*), count(id), count(distinct id) from foobar ;
 count | count | count 
-------+-------+-------
     5 |     3 |     2
(1 row)

------------------------------------------------------------
   
SELECT
   b.baker
   , b.hometown
   , count(*)
FROM
   bakers b
   , technicals t1
   , technicals t2
WHERE
   b.baker = t1.baker
   and b.baker = t2.baker
   and t1.episodeid <> t2.episodeid
   and t1.rank = 1
   and t2.rank = 1
GROUP BY
   b.baker
   , b.hometown
;

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

For each bakers, find how many times they won technical.

SELECT
   t.baker
   , count(*) as numtechnicalwon
FROM
   technicals t
WHERE
   t.rank = 1
GROUP BY
   t.baker;


Find bakers who have won technical 2 times.

SELECT
   t.baker
   , count(*) as numtechnicalwon
FROM
   technicals t
WHERE
   t.rank = 1
GROUP BY
   t.baker
HAVING
   count(*) > 1
;

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

For each baker and rank, find how many times they got that rank in
technicals.

SELECT
   b.baker
   , t.rank
   , count(*) as numtimes
FROM
   technicals t
   , bakers b
WHERE
   b.baker = t.baker
GROUP BY
   b.baker
   , t.rank;


For each baker and rank, find how many times they got that rank in
technicals.
   - but only for ranks less than 5
   

SELECT
   b.baker
   , t.rank
   , count(*) as numtimes
FROM
   technicals t
   , bakers b
WHERE
   b.baker = t.baker
   and t.rank < 5
GROUP BY
   b.baker
   , t.rank
;

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

Bakers who competed in more than 4 episodes

SELECT    --- who competed in more than 4 episodes
   t.baker
FROM
   technicals t
GROUP BY
   t.baker
HAVING
   count(*)>4


For each baker and rank, find how many times they got that rank in
technicals.
    - but only for ranks less than 5
--- who competed in more than 4 episodes


SELECT
   b.baker
   , t.rank
   , count(DISTINCT t.episodeid) as numtimes
FROM
   technicals t
   , bakers b
   , signatures s
WHERE
   b.baker = t.baker
   and t.rank < 5
   and s.baker = b.baker
GROUP BY
   b.baker
   , t.rank
HAVING
   count(DISTINCT s.episodeid)>4
;



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

Find bakers who were never eliminated and have won technical at least 2 times.

SELECT    
   t.baker
FROM
   technicals t
WHERE
   t.rank = 1
GROUP BY
   t.baker
HAVING
   count(*)>=2
EXCEPT
SELECT
   baker
FROM
   results
WHERE
   result = 'eliminated';

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


Find the last episode in the series.

Find the baker who won technical in the last episode 

Find the biggest drop in the viewers from one episode to the
next. (Who was eliminated?)

For each month, find the average viewers.

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