DATABASE:

library $($library$\_$name$[$pk$],$ City, Phone, manager$\_$SSN$[$pk$,$fk$])$

book $($book$\_$name$[$pk$],$ Genre, Country$)$

borrower $($SSN$[$pk$],$ Name, Birthyear, Fav$\_$book$[$fk$],$ Gender$)$

likes $($SSN$[$pk$,$fk$],$ book$[$pk$,$fk$])$

bookLoans $($library$[$fk$],$ books$[$fk$])$

manager $($SSN$[$pk$],$ Name, City, Address, Phone$)$

$2$

For this database, all the tables are created and populated in a dump$/$backup file that you

can download from here. You need to import the dump$/$backup file to your MySQL as a

new database $($instructions on how to import a dump file can be found at the end part in

Appendix A$)$ and then perform the following queries:

$1)$ List name, gender, and age of all borrowers. The age of borrower column should

be named as $$age$$ in the output.

$2)$ List name, gender, and age of all female borrowers older than $40$ years. The age

of those female borrower column should be named as $$age$$ in the output.

$3)$ List the name, phone, and address of all managers that have the word $$Martial$$

anywhere in their address.

$4)$ List the Name of borrowers who declared the book they like. $($Even if a borrower

likes several books, his$/$her name must appear just once$).$

$5)$ List library name, manager name of that library, and phone number where phone

number is recorded both for the library and the manager phone $($you cannot use

JOIN for this question. Use subquery$).$

$6)$ List the name and ssn of all borrowers who like their favorite book. $($you cannot use

JOIN for this question. Use subquery$).$

$7)$ List the name and gender of all borrowers who DO NOT like their favorite book.

$($you cannot use JOIN for this question. Use subquery$).$

$8)$ List the name of libraries that loan book genre $$science fiction$.$

$9)$ List of managers who live in the same city that $$Robinson Baumbach$($name of

one of the managers$)$ lives.

$10)$ List the book names loaned by at least one library and liked by at least one

borrower.

$11)$ List the name and phone number of all managers who do not have $$The Little

Prince$$ in their libraries. $($The Little Prince$$ is a book name$)$

$12)$ List Name of the borrowers who like the same book. The list should be in form of

pairs of Names. That is$,$ the Names of two persons who like the same book.

$13)$ Find total number of libraries that have $$The Lord of the Rings$.$

$14)$ Find the total number of books that are liked by borrowers. Note that if a book is

repeatedly liked by several people, it is just counted as once.