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

 - Hw#5 due next week! Out by tomorrow. Expect a bigger DB.


Homework Questions:
---------------------

- Email from: rpidbsprof@gmail.com
  titled: Welcome to the CSCI 4380 Fall 2020 Database Server
  (check in respite.rpi.edu)
 
- Tour of DBS server

- Simpler queries are better, more portable, are harder to write at first

- Debugging:

  -> Start small/remove relations from the from statement
  -> explain query ;


Old Material
---------------


SELECT
FROM
WHERE
GROUP BY
HAVING

<set operations>

SELECT
FROM
WHERE
GROUP BY
HAVING

ORDER BY
LIMIT




SELECT  attributes, aggregates
FROM    relations
WHERE   conditions over attributes
GROUP BY  attributes from the from relations
HAVING   aggregate compared to a constant
ORDER BY attributes constructed in select


New Material
---------------

Outer join
Anonymous queries
Scalar queries
Set operations


select id1, vala, id2, valb
from a,b
where a.id1 = b.id2 ;


select id1, vala, id2, valb
from a join b on a.id1 = b.id2 ;

select id1, vala, id2, valb
from a left join b on a.id1 = b.id2 ;


select
    b.baker
    , count(*) as numtuples
    , count(f.episodeid) as numfavorites
from
    bakers b
    left join favorites f
       on b.baker = f.baker
group by
    b.baker;


(left relation)   left join  (right relation)  on condition

-- For each baker, find how many times they were ranked 3 or lower in
   technicals.


(left relation=all bakers)   left join  (right relation= bakers with at least one technical with rank<=3)  on condition

SELECT
   b.baker
   , count(t.episodeid)
FROM
   bakers b
   left join technicals t
     on b.baker = t.baker and t.rank <= 3
GROUP BY
   b.baker;

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

Anonymous

SELECT
   s.baker
   , s.make
   , tr1.numwins
FROM
   (select baker,count(*) as numwins
      from technicals
      where rank = 1
      group by baker
      having count(*)>=1) as tr1,
     signatures s
WHERE
    s.baker = tr1.baker;

-- same!

SELECT
   s.baker
   , s.make
   , count(DISTINCT t.episodeid) as numwins
FROM
   signatures s
   , technicals t
WHERE
   s.baker = t.baker
   and t.rank =1
GROUP BY
   s.baker
   , s.make
HAVING
   count(DISTINCT t.episodeid)>=1;


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

SELECT max(viewers7day) from episodes;

SELECT
   *
FROM
   episodes
WHERE
   viewers7day = 10.34;


SELECT
   *
FROM
   episodes
WHERE
   viewers7day = (SELECT max(viewers7day) from episodes);


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

1. Return the fullname and all showstoppers that they made for all bakers
who won exactly two technicals in odd numbered episodes that aired
before October. Order by fullname ASC.


SELECT
   b.fullname
   , s.make
FROM
   bakers b
   , showstoppers s
   , (SELECT
       t.baker
     FROM
       episodes e
      , technicals t
    WHERE
      e.firstaired < date '10/1/2018'
      and e.id%2 = 1
      and t.episodeid = e.id
      and t.rank = 1
GROUP BY
   t.baker
HAVING
   count(*)=2) as twotimes 
WHERE
   b.baker = s.baker
   and twotimes.baker = b.baker;

   
2. Return the name and age of the bakers who were not 30 years of age,
who were the star baker more than once, also return the name of their
showstopper dessert in an episode that they are a star baker, but for
episodes that do not have the lowest two viewers7day count.


SELECT
   b.fullname
   , b.age
   , s.make
FROM
   bakers b
   , showstoppers s
   , (SELECT baker FROM results WHERE result = 'star baker'
      GROUP BY baker HAVING count(*)>1) as stm1
   , results r      
WHERE
   b.age<>30
   and b.baker = s.baker
   and s.episodeid NOT IN
          (SELECT id FROM  episodes
	   ORDER BY viewers7day asc LIMIT 2) 
   and b.baker = stm1.baker
   and r.episodeid = s.episodeid
   and r.baker = s.baker
   and r.result = 'star baker'
;