Announcements
-----------------

1. You all have your own databases in the class server

2. Go through Hw#4 answers

3. Mediumstreaming DB - use only if you feel confident of your answers

4. Debugging SQL

--------

WITH statement
Correlated subqueries


For each baker find the number of favorites and number of star bakers
that they won.

SELECT
   baker
   , count(*) as numfavorites
FROM
   favorites
GROUP BY
   baker;

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


SELECT  *
FROM (SELECT
        baker
      , count(*) as numfavorites
     FROM
       favorites
    GROUP BY
       baker ) as numf;

WITH numf AS (
SELECT
   baker
   , count(*) as numfavorites
FROM
   favorites
GROUP BY
   baker),
nums AS (
SELECT
   baker
   , count(*) as numstar
FROM
   results
WHERE
   result = 'star baker'
GROUP BY
   baker)
SELECT
   numf.baker
   , nums.baker
   , numf.numfavorites
   , nums.numstar
FROM
   numf
   full join nums 
     on numf.baker = nums.baker ;


Find the bakers with the highest number of favorites and star bakers.


WITH
numf AS (
   SELECT
      baker
      , count(*) as numfavorites
   FROM
     favorites
   GROUP BY
     baker),
nums AS (
   SELECT
     baker
     , count(*) as numstar
   FROM
     results
  WHERE
     result = 'star baker'
  GROUP BY
     baker),
totalnum AS (     
   SELECT
     numf.baker
     , numf.numfavorites + nums.numstar as totalwins
   FROM
     numf join nums 
       on numf.baker = nums.baker)
SELECT
   baker
FROM
   totalnum
WHERE
   totalwins = (SELECT max(totalwins) FROM totalnum)
;


Find bakers who never won star baker and were never a favorite.

(SELECT
   baker
FROM
   bakers
EXCEPT
SELECT
   baker
FROM
   favorites)
EXCEPT
SELECT
   baker
FROM
   results
WHERE
   result = 'star baker';


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

WITH winners AS
((SELECT   baker FROM   favorites)
UNION
(SELECT   baker FROM    results WHERE    result = 'star baker'))
SELECT
   baker
FROM
   bakers
WHERE
   baker NOT IN (SELECT baker FROM winners);


SELECT
   b.baker
FROM
   bakers b
WHERE
   NOT EXISTS (SELECT 1 FROM favorites f WHERE f.baker=b.baker)
   and NOT EXISTS (SELECT 1 FROM results r
                     WHERE r.result = 'star baker' and
		           r.baker = b.baker);



Find bakers who did not compete in an episode, before they were
eliminated.

SELECT
   b.baker
   , e.id
FROM
   bakers b
   , results r
   , episodes e
WHERE
   b.baker = r.baker
   and r.result = 'eliminated'
   and e.id < r.episodeid
EXCEPT   
SELECT
   baker
   , episodeid
FROM
   signatures;

-- Rahul was never eliminated
baking=> delete from signatures where episodeid = 8 and baker = 'Rahul';
DELETE 1

--- Above query will never return Rahul
--- Alternate approach:

SELECT DISTINCT
    b.baker
FROM
    bakers b
    , episodes e
WHERE
    NOT EXISTS (SELECT 1
                FROM signatures s
		WHERE s.episodeid = e.id AND s.baker = b.baker)
    AND NOT EXISTS (SELECT 1 FROM results r
                WHERE r.baker = b.baker AND r.result='eliminated'
		      AND r.episodeid < e.id ) ;


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





Transactions

Insert/Update/Delete


Insert into episodes, episode 11
Create new table for least favorites and insert tuples:

Terry, 1 2
Karen, 4
Jon, 5
Manon, 6
Briony, 2, 3, 4, 5
Ruby, 1 7
Kim-Joy, 8
Rahul, 8 9

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



Add new attribute to bakers, proportion favorite and populate (update)


update bakers b
set prop = (select count(*)::float from favorites f
            where f.baker = b.baker )/
	   (select count(*)::float from signatures s
	    where s.baker = b.baker)


Add tuples to results least/most favorite (insert)

Delete from least favorites people who won star baker next episode (delete)


DELETE FROM leastfavorites l
WHERE l.baker IN (SELECT r.baker FROM results r
                  WHERE r.result = 'star baker'
		        and r.episodeid = l.episodeid + 1);

DELETE FROM leastfavorites l
WHERE EXISTS (SELECT 1 FROM results r
                  WHERE r.result = 'star baker'
		        and r.episodeid = l.episodeid + 1
			and r.baker = l.baker );