CISC 7510X Final Exam
NAME: _______________________
<!-- cat final20211214.src.txt|perl -e'$/=undef;$_=<STDIN>;s/<!--.*?-->//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.

<!-- --DDL
create table patron(patronid bigint,fname varchar(64),lname varchar(64));
create table beer(beerid bigint,description varchar(255),listedprice numeric(18,8));
create table transaction(txid bigint,patronid bigint,event_tim timestamp);
create table transactionitem(txid bigint,beerid bigint,qty int,price numeric(18,8));
-->

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'<!-- answer -->

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<!-- answer -->

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<!-- answer -->

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<!-- answer -->

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<!-- answer -->

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<!-- answer -->

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  <!-- answer -->

e. (write your own answer)


8. (5 points) What is the most appropriate index for patron.nickname field?

a. Btree Index<!-- answer -->

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<!-- answer -->

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<!-- answer -->

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<!-- answer -->

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<!-- answer -->

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<!-- answer -->

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<!-- answer -->

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)<!-- 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)<!-- 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)<!-- answer -->


18. (5 points) In general, on limited memory system, no indexes, 
  and huge tables, what join type would perform best?

a. merge join.<!-- answer -->

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.<!-- answer -->

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.<!-- answer -->

d. All of the above.

e. (write your own answer)





<!-- 

1. c
2. b
3. d
4. b
5. a
6. d
7. d
8. a
9. a
10. c
11. d, e
12. c
13. a
14. d
15. e
16. e
17. e
18. a
19. b
20. c

-->