customer(custid,username,fname,lname)

product(prodid,description,listedprice)

purchase(purchid,custid,timstamp)

purchaseitem(purchid,prodid,qty,price)

11. (5 points) The below code (tip: write out the first few output numbers): with recursive n(n) as ( select 2 n union all select n+1 from n where n<1000 ) select a.n from n a left join n b on b.n < sqrt(a.n) group by a.n having a.n=2 or min(a.n % b.n) > 0 (a) Is invalid (b) Will generate a list of numbers 1 to 1000 (c) Will create a table with all dates between 19000101 and 21000101 (d) Will output list of all prime numbers between 1 and 1000 (e) Other:

12. (5 points) Find average number of items per purchase. (a) select avg(purchase) from customer a natural inner join purchase b (b) select avg(*) from customer a natural inner join purchase b where custid > 0 (c) select avg(cnt) from (select purchid,count(*) cnt from purchase a natural inner join purchaseitem b group by purchid) a (d) select avg( sum(1.0) ) over () from customer a (e) Other:

13. (5 points) Find items that were bought on sale (listed price is higher than purchase price). (a) select * from product a natural inner join purchaseitem b where listedprice > price 3 (b) select * from product a natural inner join purchaseitem b group by purchid having listedprice > price (c) select count(*) from product a natural inner join purchaseitem b group by purchid having listedprice > price (d) select * from purchaseitem b where listedprice > price (e) Other:

14. (5 points) Find the last sale price for each item. (a) select prodid,max(price) ls from purchaseitem order by timstamp (b) select prodid,max(timstamp) over (partition by prodid order by price) ls from purchaseitem (c) select prodid,last value(price) over (partition by prodid order by timstamp) ls from purchaseitem (d) select prodid,last value(price) over (partition by prodid order by timstamp) ls from purchase p natural inner join purchaseitem pi (e) Other:

15. (5 points) Find percentage of purchases with above average amount. (a) select row number() over () / count(*) from purchase a inner join purchaseitem b where qty*price > avg(qty*price) (b) select purchid,sum(qty*price) px, avg( sum(qty*price) ) over () avgpx purchase a inner join purchaseitem b where px > avgpx (c) select percentage(qty*price) from purchaseitem where qty*price > avg(qty*price) (d) select sum(case when qty*price>avg() then 1.0 else NULL end) / sum(1.0) from purchase inner join purchaseitem (e) Other: