CISC 7510X Final Exam

employee(eid, name, email, phone, position, mid)
client(cid, name, email, phone, street, city, state, zip)
presentation(pid, cid, timstamp, description)
pteam(pid, eid)
sale(sid, cid, pid, amnt, timstamp)
plog(lid, pid, notes, timstamp)

1. b
2. a 
3. d
4. c
5. d 
6. b
7. c 
8. b 
9. c 
10. a
11. c 
12. c 
13. d
14. b
15. 

select a.*
from client a
  left outer join presentation b
  on a.cid=b.cid and b.timstamp >= (now() - interval '6 months')
where b.cid is null

16. 

with stat as (
  select a.eid,sum(b.amnt) tot, max(sum(b.amnt)) over () maxtot
  from pteam a
    inner join sale b
    on a.pid=b.pid
  group by a.eid
)
select eid
from stat
where tot = maxtot

17. 

with stat as (
  select a.eid,sum(coalesce(c.amnt,0)) tot,    --note coalesce
     min(sum(coalesce(c.amnt,0))) over () mintot
  from employee a
    left outer join pteam b    -- note left joins.
    on a.eid=b.eid
    left outer join sale c
    on b.pid=c.pid  
  group by a.eid
)
select eid
from stat
where tot = mintot --for employees who did not sell anything, this will be 0.

18. 

with emp_presentations as (
  -- setup start-time of each presentation for each employee.
  select b.eid, a.pid, extract(epoch from a.timstamp) tim
  from presentation a 
    inner join pteam b
    on a.pid=b.pid
),
start_end as (
 -- synthesize "start" and "end" records for each presentation.
 select 'S' evt, eid, pid, tim from emp_presentations 
 union all
 select 'E' evt, eid, pid, tim+3600 from emp_presentations  --3600 seconds is 1 hour.
),
cnts as (
  select a.*,
    -- sequentially count how many presentation each employee is in.
    -- when presentation starts, we add 1, when it ends we subtract 1.
    sum(case when evt='S' then 1 when evt='E' then -1 else 0 end) 
      over (partition by eid order by tim 
            rows between unbounded preceding and current row) cnt
  from start_end a
)
select * 
from cnts 
where cnt>1   --only keep records when employee is in more than 1 presentation

19. a
20. b