customer(custid,username,fname,lname)

product(prodid,description,listedprice)

purchase(purchid,custid,timstamp)

purchaseitem(purchid,prodid,qty,price)

6. (5 points) Find customers who have never purchased anything. (a) select a.* from customer a natural inner join purchase b where b.purchid is null (b) select a.* from customer a left join purchaseitem b on a.custid=b.custid where b.purchid=0 (c) select a.* from customer a inner join purchase b on a.custid=b.custid where b.purchid > 0 (d) select a.* from customer a natural left outer join purchase b where b.purchid is null (e) Other: 7. (5 points) Find top 10 customers who spent the most in 2013. (a) select top 10 custid from purchase a natural inner join purchaseitem b where timstamp >=20130101 and timstamp < 20140101 (b) select custid from purchase a natural inner join purchaseitem b where timstamp >=20130101 and timstamp < 20140101 order by sum(qty*price) desc (c) select custid,row number() over (order by sum(qty*price) desc) rn from purchase a natural inner join purchaseitem b where timstamp >=20130101 and timstamp 20140101 and rn <= 10 (d) select custid,sum(qty*price) v from purchase a natural inner join purchaseitem b where timstamp >=20130101 and timstamp < 20140101 group by custid order by 2 desc limit 10 (e) Other: 8. (5 points) What is the most appropriate index for customer.username field? (a) Btree Index (b) Bitmap Index (c) Clustered Index (d) Bitmap Clustered Index (e) Other: 2 9. (5 points) What is the most appropriate index for product.description field? (a) Btree Index (b) Bitmap Index (c) Clustered Index (d) Bitmap Clustered Index (e) Other: 10. (5 points) What is the most appropriate index for product.prodid field? (a) Btree Index (b) Bitmap Index (c) Clustered Index (d) Bitmap Clustered Index (e) Other: