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)