Consider the following schema: Publisher (name, phone, city), PK: name. Book (IS
ID: 3605899 • Letter: C
Question
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. Hint: You may use views to hold intermediate results.
7. Show the number of books published in 2008 versus the number of books published in 2009 for every publisher. The result should have the following four columns: publisher_name, book_count_08, book_count 09, percentage of increase from 2008 to 2009
Explanation / Answer
select p.name, count(*) b1.year , count(*) b2.year , - 100.0 * (1 - LEAD(count(*)b1.year) OVER (count(*) b2.year) / count(*) a1.book_count)
from Publisher p, book b1, book b2, Editor a1 where b1.year = 2008 and b2.year = 2009 ORDER BY p.name ;