1. Construct SQL queries to create tables for the below 2 entities using CREATE TABLE statement:

BOOK

Book_Id CHAR

Book_Name CHAR

Author_id CHAR

Total_Copies NUMBER

Available_Copies NUMBER

AUTHOR

Autor_Id CHAR

Author_Name CHAR

Author_Address CHAR

Author_Email CHAR

2. Insert the below values to tables BOOK and AUTHOR using INSERT SQL statements.

BOOK

Book_id Book_name Author_id Total_copies Available_copies Category

BF0001 A human life in the moon A001 5 5 Fantasy

BM0001 The ghost town A002 10 5 Mystery

BM0002 A shadow follows a man A003 8 8 Mystery

BT0001 A murder in the dark A004 10 4 Thriller

BF0002 Travel by a saint A001 7 5 Fantasy

AUTHOR

Author_Id Author_Name Author_Address Author_Email

A001 White, Douglas 2837 Greenway, Fillmore FL 33336 douglas.white@westcoast.com

A002 McElroy, David 3827 Devon, Grove FL 33321 david.mcelroy@eastwest.com

A003 Drennan, Becky 382 Wildwood, Northfield FL 33146 becky.drennan@redwine.com

A004 Wallace-Morgan, Diana 1828 Raven, Crystal FL 33503 diana.wallace@selfhelp.com

A005 Lucht, Tedd 3829 Central, Grove FL 33321 tedd.lucht@queenbee.com

A006 Elliott, Andrew 838 Ridgeland, Fillmore FL 33321 andrew.elliott@ngo.com

3. List all the books which have not been checked out.

4. List Book_id, Book_name, No_of_books_checked_out = (Total_copies Available_copies) for all the books.

5. List author_name, author_address of authors whose ids are A003, A005 and A002; use IN operator.

6. List author_id, author_name, author_address and author_email of authors whose address includes zip-code 33321; use LIKE operator.

7. Two copies of book BT0001 has been returned; update Total_copies & Available_copies columns in BOOK table; use UPDATE statement.

8. One copy each of the books BF0001 and BM0002 is checked out; update Total_copies & Available_copies columns in BOOK table; use UPDATE statement.

9. Create a new table called FANTASY_BOOK which should contain all the Fantasy books from BOOK table. Use SELECT . INTO . WHERE Let the FANTASY_BOOK table contain only Book_id, Book_name, Total_copies & Available_copies columns.

10. List all the books with book_id and book_name written by author White, Douglas; join tables BOOK & AUTHOR.

11. List author_id, author_name, number_of_books for all authors; use COUNT function and GROUP BY clause.

12. Delete all the authors from AUTHOR table that their books are not found in BOOK table; use DELETE statement with NOT IN operator and Sub-query in WHERE clause.