Kindly assist with this assignment:

INTRODUCTION$/$OVERVIEW $-$ STRUCTURED QUERY LANGUAGE $($SQL$)$

SQL is a standard programming language used for managing and manipulating relational databases. It allows users to query, insert, update, and delete data within a database.

INSTRUCTIONS

Provide the SQL code for the questions below $($This should be done using MySQLOneUnit Number$($s$)03$ Structured Query Language

Assignment Title Structured Query Language Academic Year $20242025$

Compiler. Show the results for each question$).$

SCENARIOS$/$QUESTIONS

Given the LIBRARY relational database schema shown below, which is usedtokeeptrackof books, borrowers, and book loans. Referential integrity constraints are shownasdirected arcs in the diagram. Provide the SQL code for the questions below$($This shouldbedone using MySQL One Compiler. Show the results for each question$).$

BOOK

BookId Title PublisherName

$0130895725$ C How to Program Prentice Hall

$0130895717$ C$++$ How to Program Prentice Hall

$0139163050$ The Complete C$++$ Training Course Prentice Hall PTG

$0130161438$ Internet and World Wide Web How to Program Prentice Hall

$0130856118$ Computer Networking Prentice Hall

$0130852473$ The Complete Java $2$ Training Course Prentice Hall PTG

$0130125075$ Software Engineering Addison Wesley

$0138993947$ Using Z Prentice Hall

BOOK$\_$AUTHORS

BookId AuthorName

$0130895725$ Paul Deitel

$0130895717$ Harvey Deitel

$0139163050$ Deitel & Deitel

$0130161438$ Carey Hamil

$0130856118$ Rowe Schuch

$0130852473$ Deitel & Deitel

$0130125075$ Sommerville

$0138993947$ Wood cock Davis

PUBLISHER

PublisherName Address Phone

Prentice Hall $20$ Carolina Street California USA $1607689256$

Prentice Hall

PTG $10$ Hendricks Street Florida USA $1407654478$

Addison Wesley $5$ Caper Street Los Angeles USA $1645997658$

BOOK$\_$COPIES

BookId BranchId NoOfCopies

$013089572514$

$013089571713$

$013916305012$

$013016143825$

$013085611826$

$013085247333$

$013012507536$

$013899394727$

BOOK$\_$LOANS

BookId BranchId CardNo DateOut DueDate

$0130895725110569874325\mathrm{.}05\mathrm{.}202101\mathrm{.}07\mathrm{.}2021$

$0130895717110469964825\mathrm{.}04\mathrm{.}202101\mathrm{.}06\mathrm{.}2021$

$0139163050110654321020\mathrm{.}04\mathrm{.}202120\mathrm{.}06\mathrm{.}2021$

$0130161438210754321923\mathrm{.}03\mathrm{.}202120\mathrm{.}05\mathrm{.}2021$

$0130856118210687621424\mathrm{.}04\mathrm{.}202120\mathrm{.}05\mathrm{.}2021$

$0130852473310345673225\mathrm{.}03\mathrm{.}202120\mathrm{.}06\mathrm{.}2021$

$0130125075310476598725\mathrm{.}04\mathrm{.}202120\mathrm{.}06\mathrm{.}2021$

$0138993947210234567825\mathrm{.}05\mathrm{.}202120\mathrm{.}06\mathrm{.}2021$

LIBRARY$\_$BRANCH

BranchId BranchName Address

$1$ Tom Redcam Kingston

$2$ National Library of Jamaica Clarendon

$3$ Si$.$ Catherine Parish Library St$,$ Catherine

BORROWER

CardNo Name Address Phone

$105698743$ Adrian Hendrikson $15$ Cauper Drive Kingston $198763827578$

$104699648$ Jack Daniels $60$ Harbour View Drive Kingston $208765764832$

$106543210$ Jim Sandricks $12$ Hagley P$\{$ark Road Kingston $408765674323$

$107543219$ Carol Drakes $17$ Clarendon Park Road Clarendon $8765432145$

$106876214$ Jeliza Phillips $14$ Maud Road, Clarendon $8763456782$

$103456732$ Carey Satchell $5$ Melbourne Road, St Catherine $8765678906$

$104765987$ Geraldine Hakes $3$ Spanish Town Road, St$.$ Catherine $8764326798$

$102345678$ Yetzi Cornwall $23$ Harper Road, Clarendon $8763765643$

$107545878$ Kaye Francis $56$ Lepert Avenue $8765432310$

For each of the following provide the SQL code $($This should be done using MYSQL One Compiler$)$ display theresultsfor each question below.

$1.$ Create each table $(7)$ clearly identifying the primary key and the Foreign Keys. $($N$.$B$.$ A table can have more than one foreign keys.$)$

$2.$ Populate each table with the data given. Show the script used to populate the tables

$3.$ How many copies of the book titled $$C How to program$$ are owned by the Library branch whose nameisTomRedcam.

$4.$ Retrieve the names of all borrowers who do not have any books checked out.

$5.$ For each library branch, retrieve the branch name and the total number of books loaned out from that branch.

$6.$ Delete the Borrower tuple where the name is Kaye Francis.

$7.$ Modify the Telephone attribute of the Publisher table from $1407654478$ to $1507675532.$

$8.$ Retrieve all Borrowers whose card numbers begins with $0130$

$9.$ Find the names of each borrower who borrowed books from $25\mathrm{.}03\mathrm{.}2021$ to $25\mathrm{.}04\mathrm{.}2021$

$10.$ Add an attribute to the Borrower table for keeping track of the number of borrowers

$11.$ Find the total number of books on loan.