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

- Lecture 12 exercise today.
- Hw #3 is due on monday
- Expect a new homework next monday.


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

Find for each baker, number of tuples and maxepisode in results

select
    b.baker
    , b.fullname
    , count(*) as numtuples
    , max(r.episodeid) as maxepisode
from
    bakers b
    , results r
where
    b.baker = r.baker
group by
   b.baker;

Find for each baker, find the number of times they were star baker.

select
    b.baker
    , b.fullname
    , count(*) as numstarbaker
from
    bakers b
    , results r
where
    b.baker = r.baker
    and r.result = 'star baker'
group by
   b.baker;

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

select
    b.baker
    , b.fullname
    , count(distinct f.episodeid) as numfavs 
    , count(distinct r.episodeid) as numstarbakers
from
    bakers b
    , results r
    , favorites f
where
    b.baker = r.baker
    and b.baker = f.baker
    and r.result = 'star baker'
group by
   b.baker
   , b.fullname
having
   count(distinct f.episodeid) > 1
   and count(distinct r.episodeid) > 1
;

------------------
create table table1 (a int) ;
insert into table1 values (1);
insert into table1 values (2);
insert into table1 values (2);
insert into table1 values (3);
insert into table1 values (4);
insert into table1 values (5);
insert into table1 values (5);


create table table2 (a int) ;
insert into table2 values (2);
insert into table2 values (2);
insert into table2 values (3);
insert into table2 values (3);
insert into table2 values (6);



Set operations: union/except/intersect

Bag operations: union all/except all/intersect all

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

Return name, age of all bakers who won star
baker at least 2 times and never were a favorite.
Order results by baker age asc.

select
    r.baker
    , b.age
from
    results r
    , bakers b
where
    b.baker = r.baker
    and r.result = 'star baker'
group by
    r.baker
    , b.age
having
    count(*) = 1
except
select
    f.baker
    , b.age
from
    favorites f
    , bakers b
where
    f.baker = b.baker ;
    

Return name, age of all bakers who were a favorite
once or won star baker at least 2 times.


select
    f.baker
    , b.age
from
    favorites f
    , bakers b
where
    f.baker = b.baker
UNION    
select
    r.baker
    , b.age
from
    results r
    , bakers b
where
    b.baker = r.baker
    and r.result = 'star baker'
group by
    r.baker
    , b.age
having
    count(*) >= 2;


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

Return name, age of all bakers who were a favorite
once and won star baker at least 2 times.

select
    r.baker
    , b.age
from
    results r
    , bakers b
    , favorites f
where
    b.baker = r.baker
    and b.baker = f.baker
    and r.result = 'star baker'
group by
    r.baker
    , b.age
having
    count(distinct r.episodeid) >= 2;

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














drop table tmp1;
drop table tmp2;
create table tmp1(aval int primary key, bval varchar(10));
insert into tmp1  values(1,NULL) ;
insert into tmp1  values(2,'b') ;
insert into tmp1  values(3,'g') ;
insert into tmp1  values(4,'d') ;
insert into tmp1  values(5,'e') ;


create table tmp2(cval int primary key, bval varchar(10));
insert into tmp2  values(6,'b') ;
insert into tmp2  values(7,'b') ;
insert into tmp2  values(8,NULL) ;
insert into tmp2  values(9,'e') ;
insert into tmp2  values(10,'f') ;


INNER JOIN:

SELECT
    *
FROM
    tmp1 t1
    , tmp2 t2
where
    t1.bval = t2.bval ;


SELECT
    *
FROM
    tmp1 t1 JOIN tmp2 t2
    ON t1.bval = t2.bval ;


LEFT JOIN: INNER JOIN UNION all tuples from left relation that did not join

SELECT
    *
FROM
    tmp1 t1 LEFT JOIN tmp2 t2
    ON t1.bval = t2.bval ;


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

SELECT
    b.baker
    , count(f.baker) as numfavorites
FROM
    bakers b
    LEFT JOIN favorites f
    ON b.baker = f.baker
GROUP BY
    b.baker

Return name, age of all bakers who won star
baker at least 2 times and never were a favorite.
Order results by baker age asc.

select
    r.baker
    , b.age
from
    results r join bakers b
    on b.baker = r.baker
    left join favorites f
    on r.baker = f.baker
WHERE    
    r.result = 'star baker'
    and f.baker is null
group by
    r.baker
    , b.age
having
    count(*) >= 2;


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

SELECT
    b.baker
    , count(distinct f.episodeid) as numfavorites
    , count(distinct r.episodeid) as numstarbakers
FROM
    bakers b LEFT JOIN favorites f
        ON b.baker = f.baker
    LEFT JOIN results r
        ON r.baker = b.baker AND r.result = 'star baker'
GROUP BY
    b.baker;