CISC 7512 Final Exam

1. b
2. c
3. a
4. c
5. b
6. b
7. c
8. c
9. b
10. a
11. c
12. c
13. d
14. a
15. b
16. Write query to identify all the free time (time when there are no events). 

with e0 as (
  -- just in case there are no events, we want to start our free time with now()  
  select now() as tim, 0 c  
  union all 
  --start of an event increments a counter
  select starttim tim, 1 c from event where endtim >= now()  -- only events that end after "now"
  union all 
  --end of the event decrements it.
  select endtim tim, -1 c from event where endtim >= now()
),
e1 as (
  select a.*, 
    -- running total, whenever cnt=0, that is free time.  
    sum(c) over (order by tim) cnt, 
    -- the "end" time of current slice
    coalesce( lead(tim) over (order by tim), cast('2100-01-01' as timestamp)) as nexttim   
  from e0 a
)
select *
from e1 
where cnt = 0  --pick out only free time slices. 
 and tim != nexttim  -- end there's some duration to the event.

17. Write a query to insert into events table all the "free time events" identified in 
previous query (note that after this, there should be no more free time in the calendar).

TODO: we need to assume there's an event_seq, since we need to generate eids 
e.g.: create sequence event_seq;

insert into event
with e0 as (
  -- just in case there are no events, we want to start our free time with now()  
  select now() as tim, 0 c  
  union all 
  --start of an event increments a counter
  select starttim tim, 1 c from event where endtim >= now()  -- only events that end after "now"
  union all 
  --end of the event decrements it.
  select endtim tim, -1 c from event where endtim >= now()
),
e1 as (
  select a.*, 
    -- running total, whenever cnt=0, that is free time.  
    sum(c) over (order by tim) cnt, 
    -- the "end" time of current slice
    coalesce( lead(tim) over (order by tim), cast('2100-01-01' as timestamp)) as nexttim   
  from e0 a
)
select nextval('event_seq') as eid, 
  'free time' as title, 
  tim as starttim, 
  nexttim as endtim, 
  null as location, null as phone
from e1 
where cnt = 0  --pick out only free time slices.
 and tim != nexttim  -- end there's some duration to the event.
;

18. d
19. a
20. b