CISC 7512X Midterm Exam NAME: _______________________ Pick the best answer that fits the question. Not all of the answers may be correct. If none of the answers fit, write your own answer. Answers must be emailed in plain text (no formatting, no attachments). Email must have your full name at the top. Answers to questions must be clearly marked (question number before each answer), and be in sequence (question 1 should come before question 2, etc.). Email must arrive by midnight on 2021-03-22. For questions below, use the following schema definition. restaurant(rid, name, phone, street, city, state, zip) customer(cid, fname, lname, phone, street, city, state, zip) carrier(crid, fname, lname, lp) delivery(did, rid, cid, tim, size, weight) pickup(did, tim, crid) dropoff(did, tim, crid) It's a schema for a food delivery business that employs food carriers (carrier table). Customers (customer table) order food from restaurants (restaurant table). The restaurants order a delivery (delivery table); to deliver food from restaurant to customer. The pickup table records when carrier picks up food at restaurant. The dropoff table records when carrier drops off food at customer. 1. (5 points) Find phone number of customer with cid 235235. a. select phone from restaurant where rid=235235; b. select phone from customer where cid=235235; c. select * from customer where cid=236235; d. select * from customer left outer join delivery using (cid) where cid=235235; e. (write your own answer) 2. (5 points) How many restaurants placed delivery orders in January 2021? a. select count(*) as cnt from restaurant natural inner join delivery where tim>=cast('2021-01-01' as date) and tim=cast('2021-01-01' as date) and tim=cast('2021-01-01' as date) and tim=cast('2021-01-01' as date) and tim e. (write your own answer) 3. (5 points) What percentage of deliveries cross state lines? a. select 100.0*sum(case when a.state != c.state then 1.0 else 0.0 end)/sum(1.0) prcnt from restaurant a natural inner join delivery b natural inner join customer c; b. select count(*)/100.0 prcnt from restaurant a inner join delivery b using(rid) inner join customer c using(cid) where a.state != c.state; c. select 100.0*sum(case when a.state != c.state then 1.0 else 0.0 end)/sum(1.0) prcnt from restaurant a inner join delivery b on a.rid=b.rid inner join customer c on b.cid=c.cid; d. select 100.0*sum(case when a.state != c.state then 1.0 else 0.0 end)/sum(1.0) prcnt from restaurant a inner join delivery b on a.rid=b.rid inner join customer c on b.cid=c.cid where (a.zip != c.zip and a.state = c.state) or (a.zip = c.zip and a.state != c.state); e. (write your own answer) 4. (5 points) Find all ongoing deliveries (food has been picked up, but not dropped off). a. select a.* from delivery a inner join pickup b using (did) inner join dropoff c using (did) where c.did is null; b. select a.* from delivery a left outer join pickup b using (did) inner join dropoff c using (did) where c.did is null; c. select a.* from delivery a inner join pickup b using (did) left outer join dropoff c using (did) where c.did is null; d. select a.* from delivery a left outer join pickup b using (did) left outer join dropoff c using (did) where c.did is null; e. (write your own answer) 5. (5 points) Find average time (in seconds) between a delivery order and food pickup. a. select avg( extract( epoch from b.tim - a.tim) ) from delivery a inner join pickup b on a.did = b.did; b. with diff as ( select extract(epoch from b.tim - a.tim) d from delivery a natural inner join pickup b ) select avg(d) from diff; c. with evts as ( select did, tim from delivery union all select did, tim from pickup ), diff as ( select extract(epoch from lag(tim) over (partition by did order by tim) - tim) d from evts ) select avg(d) from diff; d. select distinct avg( extract(epoch from b.tim - a.tim) ) over () avg from delivery a natural inner join pickup b; e. (write your own answer) 6. (5 points) Identify restaurant with most deliveries. a. with stats as ( select a.rid,count(b.rid) cnt, max( count(b.rid) ) over () maxcnt from restaurant a natural left outer join delivery b natural left outer join pickup c natural left outer join dropoff d natural left outer join carrier e group by a.rid ) select * from stats where cnt = maxcnt; b. with stats as ( select rid, count(*) cnt, max( count(*) ) over () maxcnt from delivery group by rid ) select * from stats where cnt = maxcnt; c. with stats as ( select rid, sum(1) cnt from delivery group by rid ), stats2 as ( select rid, rank() over (order by cnt) rnk from stats ) select * from stats2 where rnk=1; d. with stats as ( select rid, sum(1) cnt from delivery group by rid ), stats2 as ( select rid, dense_rank() over (order by cnt) rnk from stats ) select * from stats2 where rnk=1; e. (write your own answer) 7. (5 points) Identify restaurant with most customers. a. with stats as ( select rid, rank() over ( order by count(distinct cid) desc) r from delivery group by rid ) select * from stats where r=1; b. with stats as ( select a.rid, count(distinct c.cid) cnt from restaurant a natural inner join delivery b natural inner join customer c group by a.rid ), mx as ( select max(cnt) m from stats ) select * from stats cross join mx where m = cnt; c. with stats as ( select rid, count(*) cnt, max( count(*) ) over () mx from delivery group by rid ) select * from stats where cnt = mx; d. select rid from delivery group by rid having count(cid) >= all(select count(cid) from delivery group by rid); e. (write your own answer) 8. (5 points) Carriers may deliver for one or more restaurants. What is the average number of restaurants that a carrier picks up from? a. with stats as ( select count(*) cnt from delivery a natural inner join pickup b group by b.crid ) select avg(cnt) from stats; b. with stats as ( select count(distinct a.rid) cnt from delivery a inner join pickup b on a.did=b.did group by b.crid ) select avg(cnt) from stats; c. with stats as ( select distinct crid, rid from pickup natural left outer join delivery ), cnts as ( select count(*) cnt from stats group by crid having count(*) is not null ) select avg(cnt) from cnts; d. with stats as ( select count(distinct b.crid) cnt from delivery a inner join pickup b on a.did=b.did group by a.rid ) select avg(cnt) from stats; e. (write your own answer) 9. (5 points) What percentage of deliveries are picked up and dropped off by different carriers? a. select count(*) / 100.0 prcnt from pickup a inner join dropoff b on a.did=b.did where a.crid != b.crid; b. with evnts as ( select * from pickup union all select * from dropoff ), fl as ( select case when min(crid)!=max(crid) then 1 else 0 end val from evnts group by did ) select 100.0*sum(val)/count(*) from fl; c. select 100.0*sum(case when a.crid!=b.crid then 1.0 else 0.0 end)/sum(1.0) prcnt from pickup a cross join dropoff b where a.did=b.did; d. select 100.0*sum(case when a.crid != b.crid then 1.0 else 0.0 end)/sum(1.0) prcnt from pickup a inner join dropoff b on a.did=b.did; e. (write your own answer) 10. (5 points) Find unusually slow deliveries, between pickup and dropoff, those that are 2 standard deviations higher than the average time. a. select a.did from pickup a natural inner join dropoff b group by a.did, extract(epoch from b.tim - a.tim) having extract(epoch from b.tim - a.tim) >= avg( extract(epoch from b.tim - a.tim) )+ 2*stddev( extract(epoch from b.tim - a.tim) ); b. with delta as ( select a.did, extract(epoch from b.tim - a.tim) d from pickup a inner join dropoff b on a.did=b.did ), stats as ( select did, d, avg(d) over () a, stddev(d) over () sd from delta ) select * from stats where d >= a+2*sd; c. with dlta as ( select a.did, extract(epoch from c.tim - b.tim) d from delivery a left outer join pickup b on a.did=b.did left outer join dropoff c on a.did=c.did ), stats as ( select did, d, avg(d) a, stddev(d) sd from dlta group by did, d ) select did from stats where d >= a+2*sd; d. with dlta as ( select a.did, extract(epoch from c.tim - b.tim) d from delivery a left outer join pickup b on a.did=b.did left outer join dropoff c on a.did=c.did ), stats as ( select did, d, avg(d) over () a, stddev(d) over () sd from dlta ) select did from stats where d >= a+2*sd; e. (write your own answer) 11. (5 points) Find instances when a customer has 100lb (weight) or more on the way to them (aggregate all shipments picked up, but not dropped off yet). a. with evnts as ( select a.cid,b.tim,a.weight from delivery a inner join pickup b using (did) union all select a.cid,b.tim,-a.weight from delivery a inner join dropoff b using (did) ), stats as ( select cid, tim, sum(weight) over (partition by cid order by tim rows between unbounded preceding and current row) weight from evnts ) select * from stats where weight >= 100; b. with evnts as ( select a.cid,a.tim,a.weight from delivery a union all select a.cid,b.tim,-a.weight from delivery a left outer join dropoff b using (did) ), stats as ( select cid, tim, sum(weight) over (partition by cid order by tim range between unbounded preceding and current row) weight from evnts ) select * from stats where weight >= 100; c. select * from delivery where weight >= 100; d. select a.cid from delivery a inner join pickup b using (did) left outer join dropoff c using (did) where c.did is null group by a.cid having sum(a.weight) >= 100; e. (write your own answer) 12. (5 points) Identify carriers who are overworked (longest interval between pickup/dropoff is less than 8 hours---meaning they don't get to sleep for 8 hours). a. select a.crid from pickup a inner join dropoff b on a.did=b.did group by a.crid having max( extract(epoch from b.tim - a.tim) ) < 8*60*60; b. with stats as ( select a.crid, max( extract(epoch from b.tim - a.tim) ) over (order by a.tim, b.tim) mxdif from pickup a inner join dropoff b on a.did=b.did ) select * from stats where mxdif <= 8*60*60; c. with evts as ( select * from pickup union all select * from dropoff ), dlta as ( select crid, extract(epoch from tim - lag(tim) over (partition by crid order by tim) ) d from evts ), mx as ( select crid, max(d) maxd from dlta group by crid ) select * from mx where maxd < 8*60*60; d. with evts as ( select * from pickup union all select * from dropoff ), dlta as ( select crid, extract(epoch from tim - lag(tim) over (partition by crid order by tim) ) d from evts ), mx as ( select crid, max(d) over (partition by crid) maxd from dlta ) select * from mx where maxd < 8*60*60; e. (write your own answer) 13. (5 points) What percentage of customers only order from 1 restaurant? a. with stats as ( select case when min(rid)=max(rid) then 1.0 else 0.0 end fl from delivery group by cid ) select 100.0*sum(fl)/sum(1.0) from stats; b. select distinct 100.0*sum( case when min(rid)=max(rid) then 1.0 else 0.0 end ) over () / count(*) over () from delivery group by cid; c. select cid, count(distinct rid)/100.0 cnt from delivery group by cid having count(distinct rid) > 1; d. with stats as ( select cid, count(distinct rid) cnt from delivery group by cid ) select cid, 100.0*sum(case when cnt=1 then 1.0 else 0.0 end)/sum(1.0) from stats group by cid; e. (write your own answer) 14. (5 points) Find all deliveries where the carrier name is the same as customer (at the point of dropoff). a. select b.* from customer a natural inner join delivery b natural inner join dropoff c natural inner join carrier d where (a.fname,a.lname) = (d.fname,d.lname); b. select a.* from customer a inner join carrier b using (fname,lname) inner join dropoff c using (crid) inner join delivery d using (did); c. select d.* from customer a inner join delivery b on a.cid=b.cid inner join dropoff c on b.did=c.did inner join carrier d on c.crid=d.crid and a.fname=d.fname and a.lname=d.lname; d. select b.* from customer a inner join delivery b on a.cid=b.cid inner join dropoff c on b.did=c.did inner join carrier d on c.crid=d.crid where a.fname=d.fname and a.lname=d.lname; e. (write your own answer) 15. (5 points) Find customers who never had a delivery. a. select a.* from customer a left outer join delivery b on a.cid=b.rid where b.did is null; b. select a.* from customer a left outer join delivery using (cid) where a.cid is null; c. select a.* from customer a natural left outer join delivery where did is null; d. select a.* from customer a natural inner join delivery where did is null; e. (write your own answer) 16. (5 points) Find customers who did not have any deliveries for 3 months. a. select a.cid from customer a left outer join delivery b on a.cid=b.cid where b.tim >= (now()-interval '3 months') and b.did is null; b. select a.cid from customer a natural left outer join delivery b where b.tim >= (now()-interval '3 months') and b.did is null; c. select a.cid from customer a left outer join delivery b using(cid) and b.tim >= (now()-interval '3 months') where b.did is not null; d. select a.cid from customer a left outer join delivery b on a.cid=b.cid and b.tim >= (now()-interval '3 months') where b.did is null; e. (write your own answer) 17. (5 points) Below query is identical to: select a.*,b.val c from T1 a left outer join T2 b on a.key=b.key and a.val!=b.val a. select a.*,b.val c from T1 a inner join T2 b on a.key=b.key and a.val!=b.val; b. with TMP as ( select a.*,b.val c from T1 a inner join T2 b on a.key=b.key where a.val!=b.val ) select a.*,b.c from T1 a left outer join TMP b on a.key=b.key; c. with TMP as ( select a.*,b.val c from T1 a left outer join T2 b on a.key=b.key where a.val!=b.val ) select a.* from TMP where a.val!=b.val; d. All of the above queries are identical. e. None of the queries are identical to the question. 18. (5 points) Find customers who have more than 10 deliveries? a. select cid,count(*) from delivery group by cid having count(*) > 10; b. select a.cid,count(*) from customer a inner join delivery b using(cid) group by a.cid where count(*) > 10; c. select b.cid,count(*) from customer a left outer join delivery b on a.cid=b.cid group by b.cid having count(*) > 10; d. select cid,sum(case when b.cid is not null then 1 else 0 end) from customer a left outer join delivery b using (cid) group by cid having sum(case when b.cid is not null then 1 else 0 end) >= 10; e. (write your own answer) 19. (5 points) Find customers who have less than 5 deliveries. a. select cid,count(*) from delivery group by cid having count(*) < 5; b. select a.cid,count(*) from customer a inner join delivery b using(cid) group by a.cid having count(*) < 5; c. select a.cid,count(*) from customer a left outer join delivery b on a.cid=b.cid group by a.cid having count(*) < 5; d. select cid,sum(case when b.cid is not null then 1 else 0 end) from customer a left outer join delivery b using (cid) group by cid having sum(case when b.cid is not null then 1 else 0 end) < 5; e. (write your own answer) 20. (5 points) Count of customers by state? a. select state,count(*) from customer group by state; b. select zip,count(*) from customer group by zip; c. select state,count(*) from customer natural inner join delivery where state='NY' group by state; d. with cust_state as ( select cid, case when state='NY' then 1 else 0 end as in_state from delivery natural inner join customer ) select b.in_state,count(*) from customer a left outer join cust_state b on a.cid=b.cid group by b.in_state; e. (write your own answer)