Consider the following schema:
Publisher (name, phone, city), PK:name.
Book (ISBN, title, year, published_by,previous_edition, price), PK: ISBN, FK: published_by refsPublisher, previous_edition refs Book.
Author (SSN, first_name, last_name,address, income), PK: SSN.
Write (aSSN, bISBN), PK: (aSSN,bISBN), FK: aSSN refs Author, bISBN refs Book.
Editor (SSN, first_name, last_name,address, salary, works_for, book_count), PK: SSN, FK: works_forrefs Publisher.
Edit (eSSN, bISBN), PK: (eSSN, bISBN),FK: eSSN refs Editor, bISBN refs Book.
Author_Editor (aeSSN, hours), PK:aeSSN, FK: aeSSN refs Author, aeSSN refs Editor.
Give SQL statements for the following plain English languagequeries based on the above schema.
Hint: You may use views to hold intermediate results.
- Show the number of books published in 2008 versus the number ofbooks published in 2009 for every publisher. The result should havethe following four columns: publisher_name, book_count_08,book_count 09, percentage of increase from 2008 to 2009
2. Provide an SQL UPDATE statementthat updates the book_count field of the Editor table by computingthe number of books edited by each editor using nested queries. (10pts)
3. For each publisher, find the titleof the book that it publishes with the largest number of editors.The output should have two columns - one is the publisher’ name andthe other is the title of the book found.
