SQL Advanced Features: Left Join
----------------------------------

Inner join -> join

select *
from a,b
where a.id1 = b.id2;

select *
from
   a join b on a.id1=b.id2;

Outer join ->

Left outer join -> inner join tuples union tuples from A that did not
participate in the join

select *
from
   a left join b on a.id1=b.id2;

Right outer join -> inner join tuples union tuples from B that did not
participate in the join


select *
from
   a right join b on a.id1=b.id2;

Full outer join -> inner join tuples union tuples from B that did not
participate in the join union tuples from A that did not
participate in the join

Full outer join:

select *
from
   a full join b on a.id1=b.id2;

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


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


List of bakers who were never a favorite

select
  b.baker
from
   bakers b
   left join favorites f
    on b.baker = f.baker
where
   f.baker is null;

equal to:

select
  b.baker
from
   bakers b
except
select
   baker
from
   favorites ;

---------

Return for all bakers how many times they were rank 1 in technicals.

select
   b.baker
from
   bakers b
   left join technicals t
     on b.baker = t.baker
        and t.rank=1
where
   t.baker is null;



----- 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





SQL - SET COMPARISONS IN QUERY
---------------------------------


SELECT
   attributes, expressions over attributes, constants
FROM
   relations r1, r2, ...
WHERE
   expression over attributes of r1,r2,...: compare to each
   other or to a constant
GROUP BY
   attributes
HAVING
   aggregate = value;

SELECT
   baker
FROM
   bakers
WHERE
   hometown = 'London'
   or hometown = 'Bristol'
   or hometown = 'Wakefield';


SELECT
   baker
FROM
   bakers
WHERE
   hometown IN ('London','Bristol','Wakefield');


SELECT
   baker
FROM
   bakers
WHERE
   hometown NOT IN ('London','Bristol','Wakefield');



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

value IN (QUERY)   --> can be easily written by a join query
value NOT IN (QUERY) --> similar to except!


SELECT baker FROM results WHERE result = 'star baker';

SELECT * FROM bakers
WHERE baker IN (SELECT baker FROM results WHERE result = 'star baker') ;

SELECT DISTINCT
   b.*
FROM
   bakers b
   , results r
WHERE
   b.baker = r.baker
   and r.result = 'star baker' ;


-- Bakers who never were given star baker award!

SELECT * FROM bakers
WHERE baker NOT IN
      (SELECT baker FROM results WHERE result = 'star baker') ;


value > ANY (QUERY)
value = ANY (QUERY)   --> same as IN
value <> ANY (QUERY)   

value <> ALL (QUERY)  --> same as NOT IN
value >= ALL (QUERY) 
value > ALL (QUERY)


Which baker won the star baker highest number of times?

SELECT
  baker
  , count(*) as numtimes
FROM
  results
WHERE
  result = 'star baker'
GROUP BY
  baker ;


SELECT
  baker
  , count(*) as numtimes
FROM
  results
WHERE
  result = 'star baker'
GROUP BY
  baker
HAVING
  count(*) >=ALL (SELECT count(*) FROM results WHERE result = 'star baker'
                 GROUP BY baker);




Which episode had the highest ratings?

SELECT
   *
FROM
   episodes
WHERE
   viewers7day >=All (SELECT viewers7day FROM episodes) ;


Who was the youngest baker(s)?

SELECT
    *
FROM
    bakers
WHERE
    age <=ALL (SELECT age FROM bakers);


SELECT
    *
FROM
    bakers
WHERE
    age = (SELECT min(age) FROM bakers);












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'
;




DROP TABLE IF EXISTS a;
CREATE TABLE a (id1 int, vala varchar(10));
INSERT INTO a VALUES (1, 'abc');
INSERT INTO a VALUES (2, 'def');
INSERT INTO a VALUES (3, 'ghi');

DROP TABLE IF EXISTS b;
CREATE TABLE b (id2 int, valb varchar(10));
INSERT INTO b VALUES (2, 'qwe');
INSERT INTO b VALUES (2, 'tyu');
INSERT INTO b VALUES (3, 'ery');
INSERT INTO b VALUES (4, 'kjh');
INSERT INTO b VALUES (5, 'pwe');

SELECT * FROM a;
SELECT * FROM b;