----- PROCEED WITH EXTREME CAUTION!!!! ------
select
b.baker
, count(f.episodeid) as numfavorites
, 4
from
bakers b
, favorites f
where
b.baker = f.baker
and f.episodeid >= 4
group by
b.baker;
SQL - Scalar queries
----------------------
Who was the youngest baker(s)?
select min(age) from bakers;
select baker from bakers where age = 26;
select
b.baker
from
bakers b
left join bakers b2
on b.age > b2.age
where
b2.baker is null ;
select
baker
, (select min(age) from bakers) as minage
from
bakers
where
age = (select min(age) from bakers) ;
Which episode had the highest ratings?
select
id
from
episodes
where
viewers7day = (select max(viewers7day) from episodes);
SQL - Anonymous queries
----------------------
Relation = bag of tuples
find the drop in ratings for each episode
select
e2.id
, e2.viewers7day - e1.viewers7day as drop
from
episodes e1
, episodes e2
where
e2.id = e1.id+1 ;
select
d.e1
, r.baker
, d.drop
from
(select
e1.id as e1
, e2.id as e2
, e2.viewers7day - e1.viewers7day as drop
from
episodes e1
, episodes e2
where
e2.id = e1.id+1) as d
, results r
where
d.drop < 0
and r.episodeid = d.e1
and r.result = 'eliminated';
Which episode had the highest drop in ratings?
select
e1.id
, e2.id
from
(select
min(e2.viewers7day - e1.viewers7day) as mindrop
from
episodes e1
, episodes e2
where
e2.id = e1.id+1) as d
, episodes e1
, episodes e2
where
e2.id = e1.id+1
and e2.viewers7day - e1.viewers7day = d.mindrop;
select
e1.id
, e2.id
from
episodes e1
, episodes e2
where
e2.id = e1.id+1
and e2.viewers7day - e1.viewers7day =
(select
min(e2.viewers7day - e1.viewers7day)
from
episodes e1
, episodes e2
where
e2.id = e1.id+1);
Which baker won the star baker highest number of times?
-> a query that returns number of times a baker has won star baker
-> find max times!
-> finding the baker who had max times