Lecture 13
================


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

- Lecture 12 exercise deadline is extended till tomorrow at 4pm
- Lecture 13 exercise today: due on wednesday as usual
  (please complete 12 before attempting 13)

- Hw#4 in the works, not sure when it will be released

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

Queries:

- Queries that return a set/bag of tuples

-> Among these some queries return a single number

   => scalar queries

select min(viewers7day) from episodes ; -- scalar query

select age from bakers where baker = 'Rahul'; -- scalar query, selection on the primary key

select max(viewers7day), min(firstaired) from episodes ; -- not a scalar query

----------

Find all episodes where the viewership is more than 1.1 times min (8.67).

select
   viewers7day/(select min(viewers7day) from episodes)
from
   episodes
where
   viewers7day >= 1.1 * (select min(viewers7day) from episodes) 
;

select
   e1.viewers7day/(select min(e3.viewers7day) from episodes e3)
from
   episodes e1
where
   e1.viewers7day >= 1.1 * (select min(e2.viewers7day) from episodes e2) 
;

Find the viewership for the episode in which the youngest person in the
database was eliminated.

-- will return all people who had the lowest age
select
   e.viewers7day
   , b.baker
   , b.age
from
   episodes e
   , results r
   , bakers b
where
   e.id = r.episodeid
   and r.result = 'eliminated'
   and b.baker = r.baker
   and b.age = (select min(b2.age) from bakers b2);


-- will return only one baker who had the lowest age

select
   e.viewers7day
   , b.baker
   , b.age
from
   episodes e
   , results r
   , bakers b
where
   e.id = r.episodeid
   and r.result = 'eliminated'
   and b.baker = r.baker
order by
   b.age asc
limit 1;   

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

Anonymous relations: any query can be treated as a (virtual) relation
and can be used in the from clause




select
   e.viewers7day
   , b.baker
   , b.age
from
   episodes e
   , results r
   , bakers b
   , (select min(b2.age) as m from bakers b2) as minage
where
   e.id = r.episodeid
   and r.result = 'eliminated'
   and b.baker = r.baker
   and b.age = minage.m;

For each baker, find the number of times they won star
baker and the number of times they were a favorite

select
   b.baker
   , count(*) as numstar
   , favcnt.numf as numfavorite
from
   bakers b
   , (select f.baker, count(*) as numf
      from favorites f group by f.baker) as favcnt
   , results r
where
   b.baker = favcnt.baker
   and b.baker = r.baker
   and r.result = 'star baker'
group by
   b.baker
   , favcnt.numf ;

select
   b.baker
   , count(distinct r.episodeid) as numstar
   , count(distinct f.episodeid) as numfavorite
from
   bakers b
   , favorites f
   , results r
where
   b.baker = f.baker
   and b.baker = r.baker
   and r.result = 'star baker'
   and f.baker = r.baker
group by
   b.baker ;


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

Set operations


SELECT
   *
FROM
   bakers
WHERE
   hometown in ('London', 'Bristol') ;

SELECT
   *
FROM
   bakers
WHERE
   hometown = 'London'
   or hometown = 'Bristol' ;



Find bakers who placed in top 2 of technicals in the first 4 episodes.

select 
    *
from
    bakers
where
    baker in (select baker from technicals where episodeid<=4 and rank<=2) ;


select distinct
   b.*
from
   bakers b
   , technicals t
where
   t.baker = r.baker
   and t.rank <= 2
   and t.episodeid <= 4


attr IN (subquery)
attr NOT IN (subquery)
attr >= ALL (subquery)
attr < ALL (subquery)
attr >= ANY (subquery)
attr >= ALL (subquery)
attr = ANY (subquery)   -> IN
attr = ALL (subquery)
attr <> ALL (subquery)  -> NOT IN
attr <> ANY (subquery)


select
   e.viewers7day
   , b.baker
   , b.age
from
   episodes e
   , results r
   , bakers b
where
   e.id = r.episodeid
   and r.result = 'eliminated'
   and b.baker = r.baker
   and b.age >= ALL (select b2.age from bakers b2);

Find the baker(s) who were not eliminated


SELECT
   *
FROM
   bakers
WHERE   
   baker not in
      (select baker from results where result = 'eliminated') ;

SELECT
   baker
FROM
   bakers
EXCEPT
select
   baker
from
   results
where result = 'eliminated' ;


SELECT
   b.baker
FROM
   bakers b
   left join results r
   on b.baker = r.baker and r.result = 'eliminated'
WHERE
   r.baker is null ;

Find the episode in which no one was a favorite

SELECT
   *
FROM
   episodes
WHERE
   id not in (set of episodes in which someone was a favorite)

SELECT
   *
FROM
   episodes
WHERE
   id not in (SELECT episodeid from favorites) ;

Find the baker(s) who won technical (rank 1) the most.

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


SELECT
   t.baker
   , count(*) as numtimes
FROM
   technicals t
WHERE
   t.rank=1
GROUP BY
   t.baker
HAVING
   count(*) = (SELECT count(*) as num
               FROM technicals t2
	       WHERE t2.rank=1
	       GROUP BY t2.baker
	       ORDER BY num DESC
	       LIMIT 1)
;



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


----------

EXISTS (subquery)
NOT EXISTS (subquery)




Find the baker(s) who were not eliminated

-- uncorrelated subquery

SELECT
   *
FROM
   bakers
WHERE   
   baker not in
      (select baker from results where result = 'eliminated') ;


- correlated subquery

SELECT
   *
FROM
   bakers b
WHERE   
   NOT EXISTS (SELECT * FROM results r
               WHERE r.result = 'eliminated' and r.baker = b.baker);
      


Find episodes in which no one was eliminated

SELECT
   *
FROM
   episodes e
WHERe
   not exists (select 1 from results r
               where r.episodeid = e.id and r.result = 'eliminated') ;

Find who was eliminated after an episode in which no one was eliminated.

SELECT
   r2.baker
   , e.id
FROM
   episodes e
   , results r2
WHERe
   not exists (select 1 from results r
               where r.episodeid = e.id and r.result = 'eliminated')
   and r2.result = 'eliminated'
   and r2.episodeid = e.id+1
;


Find bakers who competed in EVERY episode (in every signature challenge)

return all bakers b
    if for this baker there does not exist an episode e such that
        b did not compete in episode e.

return all bakers b
    such that there does not exist an episode e such that
        there does not exist a tuple in signatures for b and e.

SELECT
   b.baker
   , b.fullname
FROM
   bakers b
WHERE
   NOT EXISTS (SELECT * FROM episodes e
               WHERE NOT EXISTS (SELECT * FROM signatures s
	                         WHERE s.episodeid = e.id
				       AND s.baker = b.baker)) ;
   

return all bakers where the number of episodes they competed
in is equal to total number of episodes.

select
   b.baker
   , b.fullname
from
   bakers b
   , signatures s
where
   b.baker = s.baker
group by
   b.baker
having
   count(*) = (select count(*) from episodes) ;