For the below questions, use the following schema definition.

book$($isbn$,$title,subject$)$

person$($pid$,$fname,lname$,$dob$)$

author$($isbn$,$pid$)$

purchase$($id$,$ts$,$isbn$)$

destroy$($id$,$ts$,$isbn$)$

borrow$($id$,$ts$,$isbn,pid$)$

return$($id$,$ts$,$isbn,pid$)$

This is a schema for a library. The book table info on books. The person table has all the individuals,

such as book borrowers and book authrors. The author table links person authors to book records.

The purchase, and destroy tables record when a library purchases or dstroys a copy of a particular

book. The borrow and return tables record when someone borrows and subseuqently returns the

book.

Note that the book table does not tell you if the book is available in the library$$we need to

ensure that the book has been purchased and not destroyed $($we are recording events, not states$)$

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. There are at most $2$ questions where writing your own answer

may be appropriate.

$1.(5$ points$)$ Find ISBN number of a book Catch $22.$

$($a$)$ select title from book where upper$($title$)=$CATCH $22$;

$($b$)$ select isbn from book where upper$($title$)=$CATCH $22$;

$($c$)$ select isbn from author where upper$($title$)=$CATCH $22$;

$($d$)$ select isbn from borrow where upper$($title$)=$CATCH $22$;

$($e$)$ Other:

$2.(5$ points$)$ How many books have $$CATCH$$ in their title?

$($a$)$ select group by count$(*)$ from book where upper$($title$)$ like $\%$CATCH$\%$;

$($b$)$ select title,count$(*)$ from book where upper$($title$)$ like $\%$CATCH$\%$

group by title;

$($c$)$ select count$(*)$ from book where upper$($title$)$ like $\%$CATCH$\%$;

$($d$)$ select sum$(1)/$count$(*)$ from book where upper$($title$)$ like $\%$CATCH$\%$;

$($e$)$ Other:

$3.(5$ points$)$ Find author of $$Catch $22$

$($a$)$ select fname,lname from book where upper$($a$.$title$)=$CATCH $22$

$($b$)$ select b$.$fname,b$.$lname from book a inner join author b

where upper$($a$.$title$)=$CATCH $22$

$($c$)$ select c$.$fname,c$.$lname from book a inner join author b

on a$.$pid $=$ b$.$pid inner join person c on b$.$isbn$=$c$.$isbn

where upper$($a$.$title$)=$CATCH $22$

$1$

$($d$)$ select c$.$fname,c$.$lname from book a inner join author b

on a$.$isbn $=$ b$.$isbn inner join person c on b$.$pid$=$c$.$pid

where upper$($a$.$title$)=$CATCH $22$

$($e$)$ Other: