Review Session
---------------------

Transactions:

levels of isolation
begin/commit/rollback



-- T1
begin ;
create table a (id int primary key);
insert into a values (1) ;
insert into a values (2) ;
insert into a values (3) ;
commit ;


-- T2
begin ;
create table a (id int primary key);
insert into a values (1) ;
insert into a values (2) ;
insert into a values (2) ;
commit ;


----------

isolation


SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

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



SELECT
FROM
WHERE
GROUP BY   a,b
HAVING 

UNION/UNION ALL/INTERSECT/...

SELECT
FROM
WHERE
GROUP BY
HAVING

ORDER BY
LIMIT

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

-- Bakers who won either star baker or technical
SELECT
   t.baker
FROM
   technicals t
WHERE
   t.rank = 1
UNION
SELECT
   baker
FROM
   results
WHERE
   result = 'star baker' ;


-- For each baker that won either a technical or star baker, find how
--  many times they won star baker or technical

WITH wins AS (
SELECT
   t.baker
FROM
   technicals t
WHERE
   t.rank = 1
UNION ALL
SELECT
   baker
FROM
   results
WHERE
   result = 'star baker'
)
SELECT
    baker
    , count(*) as numwins
FROM
    wins
GROUP BY
    baker
ORDER BY
    numwins desc
;


-- For each baker, find how many times they won star baker or
-- technical

WITH wins AS (
SELECT
   t.baker
FROM
   technicals t
WHERE
   t.rank = 1
UNION ALL
SELECT
   baker
FROM
   results
WHERE
   result = 'star baker'
)
SELECT
    b.baker
    , count(w.baker) as numwins
FROM
    bakers b left join wins w
    on b.baker = w.baker
GROUP BY
    b.baker
ORDER BY
    numwins desc
;



SELECT
    b.baker
    , count(distinct r.episodeid) + count(distinct t.episodeid) as numwins
FROM
    bakers b 
       left join technicals t
       on b.baker = t.baker and t.rank = 1
       left join results r
       on b.baker = r.baker and r.result = 'star baker'
GROUP BY
    b.baker
ORDER BY
    numwins desc
;



SELECT
    b.baker
    , count(w.baker) as numwins
FROM
    bakers b left join
    (
    SELECT
    t.baker
   FROM
   technicals t
  WHERE
   t.rank = 1
   UNION ALL
   SELECT
   baker
   FROM
   results
   WHERE
   result = 'star baker'
    ) as w
    on b.baker = w.baker
GROUP BY
    b.baker
ORDER BY
    numwins desc
;

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

CREATE VIEW wins(baker) AS
SELECT
   t.baker
FROM
   technicals t
WHERE
   t.rank = 1
UNION ALL
SELECT
   baker
FROM
   results
WHERE
   result = 'star baker';




SELECT
    baker
    , count(*) as numwins
FROM
    wins
GROUP BY
    baker
ORDER BY
    numwins desc
;

-------------------------------------------------------------
Find bakers who never won the technical challenge.

SELECT baker FROM bakers
EXCEPT
SELECT baker FROM technicals WHERE rank = 1;


SELECT baker
FROM bakers
WHERE baker NOT IN (SELECT baker FROM technicals WHERE rank = 1) ;

SELECT
   b.baker
FROM
   bakers b
WHERE
   NOT EXISTS (SELECT 1 FROM technicals t
               WHERE t.rank = 1 and t.baker = b.baker) ;


SELECT
   b.baker
FROM
   bakers b
   left join technicals t
   on b.baker = t.baker and t.rank=1
WHERE
   t.baker is null ;


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

-- bakers who won a technical challenge

SELECT baker
FROM bakers
WHERE baker IN (SELECT baker FROM technicals WHERE rank = 1) ;

SELECT
   b.baker
FROM
   bakers b
WHERE
   EXISTS (SELECT 1 FROM technicals t
           WHERE t.rank = 1 and t.baker = b.baker) ;


SELECT DISTINCT
   b.baker
FROM
   bakers b
   , techicals t
WHERE
   b.baker = t.baker
   and rank = 1 ;


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

CREATE FUNCTION atrg_f() RETURNS trigger AS $$
DECLARE
sumval int ;
    BEGIN
        SELECT sum(id) INTO sumval FROM a ;
	INSERT INTO b VALUES(sumval) ;
	NEW.id = sumval ;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER atrg BEFORE INSERT ON a
    FOR EACH ROW EXECUTE FUNCTION atrg_f();


CREATE FUNCTION atrg_f() RETURNS trigger AS $$
    BEGIN
        IF NEW.id - OLD.id > 10 THEN
	  ROLLBACK ;
	END IF ;   
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER atrg BEFORE UPDATE ON a
    FOR EACH ROW EXECUTE FUNCTION atrg_f();


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

- select bakers who never won technical and star baker in the same
- episode, includes people who never won either one

select baker from bakers
except
select t.baker from technicals t, results r
where r.baker = t.baker and r.result = 'star baker' and t.rank=1
      and r.episodeid = t.episodeid;

-- select bakers, episodeid who won technical in an episode but not
-- star baker in that episode


select
   t.baker
from
   technicals t
where
   t.rank=1
   and not exists (select 1 from results r
                   where r.baker = t.baker
		         and r.episodeid = t.episodeid
			 and r.result = 'star baker'
			 ) ;


select
   t.baker
from
   technicals t
where
   t.rank=1
   and (t.baker, t.episodeid) not in
           (select r.baker,r.episodeid from results r
            where  r.result = 'star baker'
            ) ;


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

select
   b.baker
   , count(t.baker) as numwins
from
   bakers b
   left join technicals t
      on b.baker = t.baker    
         and t.rank = 1
group by
  b.baker ;


select
   b.baker
   , t.baker
from
   bakers b
   left join technicals t
      on b.baker = t.baker    
         and t.rank = 1

order by b.baker