Consider the following schema:

Publisher (name, phone, city), PK: name.

Book (ISBN, title, year, published_by, previous_edition, price), PK: ISBN, FK: published_by refs Publisher, 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_for refs 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 language queries based on the above schema.

1.Show the number of books published in 2008 versus the number of books published in 2009 for every publisher. (5 pts)The result should have the following four columns: publisher_name, book_count_08, book_count 09, percentage of increase from 2008 to 2009

Ans:

select p.name, b1.year , b2.year from Publisher p, book b1, book b2, Editor a1 where b1.year = 2008 and b2.year = 2009 ORDER BY p.name ;

2.Provide an SQL UPDATE statement that updates the book_count field of the Editor table by computing the number of books edited by each editor using nested queries. (10 pts)

Ans:

3.For each publisher, find the title of the book that it publishes with the largest number of editors. The output should have two columns - one is the publisher name and the other is the title of the book found. (5 pts)

Ans: