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.

1. 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

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)

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.

Answer 1

Answer to Question 1

We are using correlated sub queries to find the solution. There are two sub queries and both will be joined.

SELECT sub8.Publisher_Name, sub8.book_count_08, sub9.book_count_09, ( (sub9.book_count_09-sub8.book_count_08)/sub9.book_count_09) * 100 AS [Percentage_Increase from 08 to 09] FROM

(SELECT published_by AS Publisher_Name,COUNT(ISBN) AS book_count_08 FROM Book

WHERE year=2008 GROUP BY published_by) As Sub8

LEFT JOIN

(SELECT published_by AS Publisher_Name,COUNT(ISBN) AS book_count_09 FROM Book

WHERE year=2009 GROUP BY published_by) AS sub9

ON sub8.Publisher_Name=sub9.Publisher_Name

Answer to Question 2

The nested query to update the table is as follows:

UPDATE editor SET book_count=sub.book_count (

SELECT eSSN,count(bISBN) AS Book_Count

) SUB

WHERE SSN=sub.eSSN;

Answer to Question 3

SELECT published_by,title FROM

SELECT p.published_by,p.title,MAX(count(e.eSSN)) AS Count FROM book b INNER JOIN edit e ON b.ISBN=e.bISBN

GROUP BY p.published_by,p.title)