Hw #4 due this friday Lecture exercise 12, due on sunday ------------------------ For each baker, how many times they were in position 1, only return bakers who won it at least two times. SELECT baker , count(*) as numepisodes FROM technicals WHERE rank = 1 GROUP BY baker HAVING count(*) > 1 ORDER BY numepisodes desc ; For each baker and each technical positions 1-4, return how many times each baker had that position, only if they held that rank more than once. SELECT baker , rank , count(*) as numepisodes , min(episodeid) , max(episodeid) FROM technicals WHERE rank <= 4 GROUP BY baker , rank HAVING count(distinct episodeid) > 1 ORDER BY baker , rank ; ----------- For each baker, find how many times they were a judge favorite. SELECT b.baker , b.fullname , count(*) as numtimes FROM favorites f , bakers b WHERE f.baker = b.baker GROUP BY b.baker UNION ( SELECT baker, fullname, 0 FROM bakers EXCEPT SELECT b.baker , b.fullname , 0 FROM bakers b , favorites f WHERE b.baker = f.baker ) ; INNER JOIN vs OUTER JOIN ----------------------------- Inner join SELECT b.baker , b.fullname , count(*) as numtimes FROM favorites f , bakers b WHERE f.baker = b.baker GROUP BY b.baker SELECT b.baker , b.fullname , count(*) as numtimes FROM favorites f inner join bakers b on f.baker = b.baker GROUP BY b.baker ; SELECT b.baker , b.fullname , count(f.episodeid) as numtimes FROM bakers b left outer join favorites f on f.baker = b.baker GROUP BY b.baker ; Find bakers who were never favorites: SELECT baker FROM bakers EXCEPT SELECT baker FROM favorites ; SELECT baker, fullname FROM bakers EXCEPT SELECT b.baker , b.fullname FROM bakers b , favorites f WHERE b.baker = f.baker; SELECT b.baker , b.fullname FROM bakers b left outer join favorites f on f.baker = b.baker WHERE f.baker is null ; For each baker, find total number of times they won star baker. SELECT b.baker , b.fullname , count(DISTINCT r.episodeid) + count(DISTINCT f.episodeid) as correctcount , count(r.episodeid) + count(f.episodeid) as incorrect FROM bakers b left join results r on b.baker = r.baker and r.result = 'star baker' left join favorites f on b.baker = f.baker GROUP BY b.baker ; LIMIT statement ------------------- SELECT FROM WHERE GROUP BY HAVING UNION SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT K; ----------------------------- Anonymous queries: For each baker, find the number of times they were #1 in technicals SELECT baker , count(*) as numepisodes FROM technicals WHERE rank = 1 GROUP BY baker HAVING count(*) > 1 Return maximum number of times a baker has been number 1 in technicals. SELECT max(numepisodes) FROM ( SELECT baker , count(*) as numepisodes FROM technicals WHERE rank = 1 GROUP BY baker HAVING count(*) > 1 ) as rank1 For each baker, return total number of times they were star baker and number 1 in technicals. --- Not necessary to use anonymous relations in this case. SELECT r.baker , count(*)+rank1.numepisodes as numstarbakerwinsandrank1 FROM ( SELECT baker , count(*) as numepisodes FROM technicals WHERE rank = 1 GROUP BY baker HAVING count(*) > 1 ) as rank1 , results r WHERE rank1.baker = r.baker and r.result = 'star baker' GROUP BY r.baker , rank1.numepisodes For each baker, find the average viewership of episodes in which they won star baker and the average viewership of episodes in which they placed first in a technical challenge. Only return bakers who won star baker. SELECT r.baker , avg(e.viewers7day)::numeric(5,2) as avgviewership , avg(e2.viewers7day)::numeric(5,2) as avgviewership2 , count(distinct e.id) as e1 , count(distinct e2.id) as e2 FROM episodes e join results r on e.id = r.episodeid and r.result = 'star baker' left join technicals t on r.baker = t.baker and t.rank =1 left join episodes e2 on t.episodeid = e2.id GROUP BY r.baker -- HW: Write this using anonymous queries -------------- Scalar Queries: that returns 1 tuple with 1 attribute select count(*) from results where result = 'star baker' and baker = 'Briony' ; Find people who won star baker more times than Briony SELECT baker FROM results WHERE result = 'star baker' GROUP BY baker HAVING count(*) > (select count(*) from results where result = 'star baker' and baker = 'Briony'); Find the air date of episodes that aired after the first episode to reach 9 million in viewers7day. SELECT id , firstaired FROM episodes WHERE firstaired > (SELECT min(firstaired) FROM episodes WHERE viewers7day >= 9); Set operations: Value in (SET) Value not in (SET) Value >=ALL (SET) Value <=ALL (SET) Value <=ANY (SET) Value >ANY (SET) select id from episodes where viewers7day in (select viewers7day From episodes where viewers7day>9) ; SELECT id , firstaired FROM episodes WHERE firstaired <=ALL (SELECT firstaired FROM episodes WHERE viewers7day >= 9);