## Announcements

- Exam #2 this monday (November 10)
- Covers everything since Exam #1

- Optional Lecture Exercise 2 due on friday
  - Use it as a study guide

- Today: complete advanced SQL features and do a course review


---------


POSTGIS



drop table if exists points ;
create table points(
      name varchar(10)
      , point geometry
      , pg geography(POINT) ) ;

insert into points
    values('p1'
           ,'SRID=4326;POINT(40.70 -74.01)'::geometry
           ,'SRID=4326;POINT(40.70 -74.01)'); 

insert into points
    values('p2'
           ,'SRID=4326;POINT(42.73 -73.68)'::geometry
           ,'SRID=4326;POINT(42.73 -73.68)'); 


select
     p1.name
     , p2.name
     , ST_AsText(p1.pg)
     , ST_AsText(p2.pg)
     , st_distance(p1.pg,p2.pg)/1000
     , st_distance(p1.point,p2.point)
     , st_distance(st_transform(p1.point,3857), st_transform(p2.point,3857))/1000
from points p1, points p2
where p1.name <> p2.name;



-----


select
     p1.name
     , st_distance(p1.point, <query point>::geometry) as distance
from points p1
order by distance asc
limit 10 ;

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

SQL Review

WITH
    q1 AS ()
    , q2 AS ()
SELECT
FROM
WHERE
GROUP BY
HAVING
EXCEPT/INTERSECT/UNION/EXCEPT ALL/INTERSECT ALL/UNION ALL
...
EXCEPT/INTERSECT/UNION/EXCEPT ALL/INTERSECT ALL/UNION ALL
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT ;




SELECT
FROM
WHERE
  EXCEPT
SELECT
FROM
WHERE

SELECT DISTINCT
FROM
WHERE
  EXCEPT
SELECT DISTINCT
FROM
WHERE


SELECT  count(*), avg(R2.B), max(R3.C)
FROM R1, R2, R3
WHERE  R1.A=R2.A AND R3.B=R2.C
       AND R1.A<5



SELECT count(*)
FROM R1, R2, R3
WHERE  R1.A=R2.A AND R3.B=R2.C
       AND R1.A<5
GROUP BY R2.C
HAVING count(*)> (select count(*) from R4) ;

SELECT count(*)
FROM R1, R2, R3
WHERE  R1.A=R2.A AND R3.B=R2.C
       AND R1.A<5
GROUP BY R2.C
HAVING count(*)> (select count(*) from R4 WHERE R4.A=R2.C) ;


SELECT count(*),(SELECT count(*) FROM R6 WHERE R1.C=R6.A)
FROM R1, R2, R3
WHERE  R1.A=R2.A AND R3.B=R2.C
       AND R1.A NOT IN (SELECT C FROM R4)
       AND NOT EXISTS (SELECT * FROM R4 WHERE R4.C=R1.A)
       AND R1.A >ALL (SELECT C FROM R4)
       AND R1.C = (SELECT count(*) FROM R5)
       AND R1.C = (SELECT D FROM R5 WHERE R5.primarykey =5)


SELECT
   *
FROM
   R1,R2
WHERE
   R1.A=R2.B

SELECT
   *
FROM
   R1 join R2 on R1.A=R2.B;


SELECT
   *
FROM
   R1 left join R2 on R1.A=R2.B;


1. Return the full name of bakers who won the technical challenge once
but never became star baker. Order results by fullname ascending.


select distinct
   b.fullname
from
   technicals t
   , bakers b
where
   t.rank = 1
   and b.baker = t.baker
   and b.baker not in (select baker from results where result='star baker')
order by
   b.fullname asc ;


select distinct
   b.fullname
from
   technicals t
   , bakers b left join results r
         on b.baker = r.baker and r.result = 'star baker'
where
   t.rank = 1
   and b.baker = t.baker
   and r.baker is null
order by
   b.fullname asc ;

select distinct
   b.fullname
from
   technicals t
   , bakers b
where
   t.rank = 1
   and b.baker = t.baker
   and not exists (select * from results r
             where r.result='star baker' and r.baker = b.baker)
order by
   b.fullname asc ;


2. For each baker, return the baker and the id of the last episode
they competed in (episodeid). Order results by episodeid descending.

select s.baker, s.episodeid
from showstoppers s
where s.episodeid = (select max(episodeid) from showstoppers s2
                     where s2.baker = s.baker) 
order by s.episodeid desc, s.baker asc;


select s.baker, s.episodeid
from showstoppers s
where s.episodeid >=ALL (select episodeid from showstoppers s2
                     where s2.baker = s.baker) 
order by s.episodeid desc, s.baker asc;


select s.baker, max(s.episodeid) as episodeid
from showstoppers s
group by s.baker
order by episodeid desc, s.baker asc;

3. For each baker, return the number of times they won star baker
after making a showstopper make with the word 'chocolate' in its name
(attribute named numtimes). You should only return bakers who won star
baker at least once. Return the baker attribute, numtimes and order
the results by baker ascending.


select
    s.baker
    , count(distinct r.episodeid)
from
    showstoppers s left join  results r
       on lower(s.make) like '%chocolate%'
          and s.baker = r.baker and r.result = 'star baker'
          and r.episodeid > s.episodeid
    , results r2
where
    s.baker = r2.baker
    and r2.result = 'star baker'
group by
    s.baker
order by
    s.baker asc ;

4. Return all bakers who continuously improved in the technicals. In
other words, their rank always improved (got lower) in each episode
that they competed in. Return the baker in ascending order. Note that
to return a baker, they must have at least competed in two episodes.


select
   t1.baker
from
   technicals t1
   , technicals t2
where
   t1.baker = t2.baker
   and t1.episodeid = t2.episodeid+1
   and t1.rank < t2.rank
group by
   t1.baker
having
   count(*) = (select count(*)-1 from technicals t3
               where t3.baker = t1.baker)
order by
    t1.baker asc
;


-- Return bakers, if for whom there are no two technical
-- tuples from back to back episodes in which they did not improve!

select distinct
   b.baker
from
   technicals b
   , technicals c
where
   not exists (
          select 1 from technicals t1, technicals t2
	  where t1.baker = t2.baker and t1.baker = b.baker
	        and t1.episodeid > t2.episodeid
		and t1.rank >= t2.rank
   )
   and b.baker = c.baker
   and b.episodeid <> c.episodeid
order by
   baker asc;


5. Find bakers who have been in the top 3 of technicals in the highest
number of episodes. Return fullname of the baker in ascending order.


SELECT
   b.fullname
   , count(*)
FROM
   technicals t
   , bakers b
WHERE
   t.rank <= 3
   and b.baker = t.baker
GROUP BY
   b.baker, b.fullname
HAVING
   count(*) >=ALL
       (select count(*) from technicals where rank<=3
        group by baker) ;


SELECT
   b.fullname
   , count(*)
FROM
   technicals t
   , bakers b
WHERE
   t.rank <= 3
   and b.baker = t.baker
GROUP BY
   b.baker, b.fullname
HAVING
   count(*) = 
       (select count(*) as numtimes from technicals where rank<=3
        group by baker order by numtimes desc limit 1) ;




WITH counts AS
   (SELECT baker, count(*) as numtimes
    FROM technicals
    WHERE rank<=3
    GROUP BY baker)
SELECT
   b.fullname
   , c.numtimes
FROM
   bakers b
   , counts c
WHERE
   b.baker = c.baker
   and c.numtimes = (select max(numtimes) from counts) ;