CISC 7510X Final Exam NAME: _______________________ //sgi;print'>final20211214.txt --> 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 address: alex@theparticle.com Email must have your full name and "CISC 7510X" at the top, and subject. 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 1:00AM on 2021-12-15. For questions below, use the following schema definition. patron(patronid,nickname,fname,lname) beer(beerid,description,listedprice) transaction(txid,patronid,event_tim) transactionitem(txid,beerid,qty,price) It is a schema for a craft beer brewery, with patrons, beers, and transactions that link patrons to beers. Each transaction can have multiple items, which are in transactionitem table. 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. 1. (5 points) Find patron id of John Doe. a. select lname,fname from patron where fname='John' and lname='Doe' b. select patronid from transaction where fname='John' and lname='Doe' c. select patronid from patron where fname='John' and lname='Doe' d. select patronid from patron inner join transactionitem using(patronid) where fname='John' and lname='Doe' e. (write your own answer) 2. (5 points) Find the average price of a beer. a. select avg(price) from transactionitem b. select avg(listedprice) from beer c. select avg(qty*listedprice) from beer d. select avg(price) from beer e. (write your own answer) 3. (5 points) Find number of transactions by patron. a. select patronid,count(*) from transaction natural inner join transactionitem group by patronid b. select beerid,count(*) from transactionitem group by beerid c. select txid,count(*) from transactionitem group by txid d. select patronid,count(*) from transaction group by patronid e. (write your own answer) 4. (5 points) Find names of all beers ever bought by 'John Doe'. a. select count(*) from patron a natural inner join transaction b natural inner join transactionitem c where a.lname='Doe' and a.fname='John' b. select description from patron a natural inner join transaction b natural inner join transactionitem c natural inner join beer d where a.lname='Doe' and a.fname='John' group by description c. select description from patron a natural inner join beer d where a.lname='Doe' and a.fname='John' group by description d. select distinct description from patron a natural inner join beer d where a.lname='Doe' and a.fname='John' e. (write your own answer) 5. (5 points) Find all transactions that total more than $1000. a. select txid from transaction a natural inner join transactionitem b group by txid having sum(qty*price) > 1000 b. select txid from transaction a natural inner join transactionitem b where qty*price > 1000 group by txid c. select txid from patron a inner join transaction a natural inner join transactionitem b where qty*price > 1000 group by txid d. select txid from transactionitem b where qty*price > 1000 e. (write your own answer) 6. (5 points) Find patrons who have never transactiond anything. a. select a.* from patron a natural inner join transaction b where b.txid is null b. select a.* from patron a left join transactionitem b on a.patronid=b.patronid where b.txid=0 c. select a.* from patron a inner join transaction b on a.patronid=b.patronid where b.txid > 0 d. select a.* from patron a natural left outer join transaction b where b.txid is null e. (write your own answer) 7. (5 points) Find top 10 patrons who spent the most in 2019 (assume every patron spent a different amount) a. select top 10 patronid from transaction a natural inner join transactionitem b where event_tim >= '20190101' and event_tim < '20200101' b. select patronid from transaction a natural inner join transactionitem b where event_tim >= '20190101' and event_tim < '20200101' order by sum(qty*price) desc c. select patronid, row_number() over (order by sum(qty*price) desc) rn from transaction a natural inner join transactionitem b where event_tim >= '20190101' and event_tim < '20200101' and rn <= 10 d. select patronid,sum(qty*price) v from transaction a natural inner join transactionitem b where event_tim >= '20190101' and event_tim < '20200101' group by patronid order by 2 desc limit 10 e. (write your own answer) 8. (5 points) What is the most appropriate index for patron.nickname field? a. Btree Index b. Bitmap Index c. Clustered Index d. Bitmap Clustered Index e. (write your own answer) 9. (5 points) What is the most appropriate index for beer.description field? a. Btree Index b. Bitmap Index c. Clustered Index d. Bitmap Clustered Index e. (write your own answer) final20211214.txt 10. (5 points) What is the most appropriate index for beer.beerid field? a. Btree Index b. Bitmap Index c. Clustered Index d. Bitmap Clustered Index e. (write your own answer) 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. (write your own answer) 12. (5 points) Find average number of items per transaction. a. select avg(transaction) from patron a natural inner join transaction b b. select avg(*) from patron a natural inner join transaction b where patronid > 0 c. select avg(cnt) from ( select txid,count(*) cnt from transaction a natural inner join transactionitem b group by txid ) a d. select avg( sum(1.0) ) over () from patron a e. (write your own answer) 13. (5 points) Find items that were bought on sale (listed price is higher than transaction price). a. select * from beer a natural inner join transactionitem b where listedprice > price b. select * from beer a natural inner join transactionitem b group by txid having listedprice > price c. select count(*) from beer a natural inner join transactionitem b group by txid having listedprice > price d. select * from transactionitem b where listedprice > price e. (write your own answer) 14. (5 points) Find the last sale price for each item. a. select beerid,max(price) ls from transactionitem order by event_tim b. select beerid, max(event_tim) over (partition by beerid order by price) ls from transactionitem c. select beerid, last_value(price) over (partition by beerid order by event_tim) ls from transactionitem d. select beerid, last_value(price) over (partition by beerid order by event_tim) ls from transaction p natural inner join transactionitem pi e. (write your own answer) 15. (5 points) Find percentage of transactions with above average amount. a. select row_number() over () / count(*) from transaction a inner join transactionitem b where qty*price > avg(qty*price) b. select txid,sum(qty*price) px, avg( sum(qty*price) ) over () avgpx from transaction a inner join transactionitem b where px > avgpx c. select percentage(qty*price) from transactionitem where qty*price > avg(qty*price) d. select sum(case when qty*price > avg() then 1.0 else NULL end) / sum(1.0) from transaction inner join transactionitem e. (write your own answer) 16. (5 points) Find all patrons who bought 'BestBeer 6000' during the first month of 2019. a. select * from patron where transaction = 'BestBeer 6000' b. select * from patron inner join transaction inner join transactionitem where item='BestBeer 6000' c. select * from transaction inner join transactionitem where description='BestBeer 6000' d. select distinct * from transactionitem inner join patron using(patronid) having description='BestBeer 6000' e. (write your own answer) 17. (5 points) Find patrons who bought 'BestBeer 6000' and also 'BestBeer 9000'. a. select * from patron where transactiond in ('BestBeer 6000', 'BestBeer 9000') b. select * from patron inner join transactionitem on patronid and description in ('BestBeer 6000', 'BestBeer 9000') c. select patronid from transaction where description in ('BestBeer 6000', 'BestBeer 9000') d. select patronid from transaction a inner join transactionitem inner join transaction b inner join transactionitem where a.description='BestBeer 6000' and b.description='BestBeer 9000' e. (write your own answer) 18. (5 points) In general, on limited memory system, no indexes, and huge tables, what join type would perform best? a. merge join. b. hash join. c. indexed lookup join. d. inner loop join. e. (write your own answer) 19. (5 points) For ``patron inner join transaction'', and no indexes, most modern databases will perform: a. merge join. b. hash join. c. indexed lookup join. d. inner loop join. e. (write your own answer) 20. (5 points) Partitions: a. Are similar to views. b. Are similar to temporary tables. c. Allow for physical clustering of logically similar data. d. All of the above. e. (write your own answer)