Lecture 17
--------------

Please do not create tables in the "BAKING" database
Use your own database

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

Exam #2 locations:

Last names: Akinyemi - Smith: Classroom (Academy Hall Aud)

Last names: Stadtler - Zhao: JROWL (Science Center) 1C13

Accommodations: you have been notified, please acknowledge

Exam #2 open book and open notes

IPAD/Computers are allowed, but no typing or taking notes, working on
draft solutions. Open all relevant tabs beforehand.

You are responsible for everything we did in class, including triggers.

Other material I cover today (other than review) will not be in the exam.



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

Triggers
------------

- Triggered when an insert/update/delete happens

     INSERT OR UPDATE ON results

- Additional conditions using WHEN clause

- Trigger can be before or after the update/insert/delete

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

- After you enroll students, update the count of students in class
- Before inserting a class, check if instructor is in the database
  if not, assign "STAFF" to the instructor


OLD/NEW values of tuples

- Insert only has new values
- Delete only has old values
- Update has an old value and a new value


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

CREATE TRIGGER fix_values BEFORE UPDATE ON bakers
    FOR EACH ROW EXECUTE FUNCTION fix_values ();


CREATE FUNCTION fix_values2() RETURNS trigger AS $$
    BEGIN
        IF NEW.age < 18 THEN
            NEW.age = 18;
	END IF ;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fix_values2 BEFORE INSERT ON bakers
    FOR EACH ROW EXECUTE FUNCTION fix_values2 ();

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

System Tables

Case statement


select cval,
       case when cval < 9 then 5 else 10 end  as summarized
from tmp2 ;


Group by grouping sets ((),(),())
         rollup (...)
	 cube(...)


select day, avg(price)
from events
group by day;

select time, avg(price)
from events
group by time;

select day, time, avg(price)
from events
group by grouping sets ((day), (time));

select day, time, name, avg(price)
from events
group by rollup(day,time,name) ;

select day, time, avg(price)
from events
group by cube(day,time) ;


Group by filter

SELECT day
       , sum(price) as total
       , sum(price) filter (where price>10) as totalfiltered
FROM events
GROUP BY day;

Window - group by without grouping by:

SELECT name, day, time, sum(price) OVER (partition by day)
FROM   events
ORDER BY day;


Recursion:

   anc(X,Y) :- parent(X,Y).
   anc(X,Z) :- anc(X,Y), parent(Y,Z).



WITH RECURSIVE ancestor AS
   (SELECT parent as anc, child as child, 1 as degree  FROM parents
    UNION ALL
    SELECT a.anc, p.child, a.degree+1 as degree
    FROM  ancestor a, parents p
    WHERE a.child = p.parent 
    )
SELECT
   * 
FROM
   ancestor a
WHERE
   a.child = 'Emma';

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


Delete users(1, Rick)

Delete posts(11,1) posts(12,1)

Delete likes(13,1)
       likes(11,2),  likes(11,3)




UPDATE posts SET postid = 24 WHERE postid = 12;

change (12,1) - (24,1)

UPDATE users SET userid = 9 WHERE name = ’Morty’;

users(2,Morty) will be (9,Morty)

change posts(13,2) to (13, null)
but cannot update likes (11,2) because no rule, restrict
the whole transaction fails and no change


SELECT dostuff(5);

insert users(5,null);

UPDATE likes SET userid = 5 WHERE postid<12;

Change (11,2) (11,3) to (11,5) (11,5)
but this violates primary key, so the whole thing fails
and no change.

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


SELECT *
FROM (SELECT baker FROM bakers) x;

WITH x AS (SELECT baker from bakers)
SELECT * FROM x ;


SELECT *
FROM X
WHERE A not in (select B from Y) ; 


-- People who hve been star baker more than dan

SELECT b.baker, b.age
FROM bakers b, results r
WHERE  b.baker = r.baker AND r.result = 'star baker'
GROUP BY b.baker
HAVING count(*) >
    (select count(*) from results r
     where r.baker = 'Dan' and r.result = 'star baker' );

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

Be careful, correlated subqueries!


Bakers who never won a technical challenge

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


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


Find people who never competed in a technical

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


count:

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


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