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.

• Provide the title, year, and publisher name of every book and the first name and last name of the editor of the book.
• Provide the title of those books whose author’s last name is neither “Smith” nor “Doe”.
• Provide the first name and last name of every author-editor who edits at least one book that is written by him or herself.
• Provide the title of the most expensive book published by the publisher named “ABC”.
• Provide the first name and the last name of those editors who have edited all the books written by John Smith.
• Give the title of those books that have more than 3 authors.
• 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

• 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.
• 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.
• Provide the name of each publisher that is located in ‘OKC’ and is one of the top three publishers in terms of the total amount of salary it pays its editors.

Answer 1

1. Provide the title, year, and publisher name of every book and the first name and last name of the editor of the book.

SELECT title, year, Published_by from Books
UNION
SELECT FirstName, LastName from Editor;

2.Provide the title of those books whose author’s last name is neither “Smith” nor “Doe”.

SELECT ISBN from Author

WHERE LastName != 'Smith', 'Deo' IN ( SELECT title from Book

WHERE ISBN = '1' ,'2' ,'3');

3.Provide the first name and last name of every author-editor who edits at least one book that is written by him or herself.

SELECT FirstName, LastName from Editor
WHERE ( aeSSN = eSSN);

4.Provide the title of the most expensive book published by the publisher named “ABC”.

SELECT MAX(price) as max_price from

WHERE (Published_by = 'ABC');

5.Provide the first name and the last name of those editors who have edited all the books written by John Smith.

SELECT FirstName, LastName from (
SELECT ISBN from Books
UNION ALL
SELECT bISBN from Editor)
WHERE ( ISBN = bISBN);

6.Give the title of those books that have more than 3 authors.

SELECT
FirstName,
ISBN,

COUNT (*) occurrences
from Author

GROUP BY
FirstName,
ISBN,

Select Book.title
from Book;

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

SELECT COUNT(year) from Book
WHERE year = 2008
UNION
SELECT COUNT(year) from Book
WHERE year = 2009
SELECT year, count() * 100.0 / (SELECT count () from Book);

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

SELECT Bookcount from Editor
WHERE Bookcount INENTITY (1,1);

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

SELECT
published_by,
Works for,

COUNT(*) occurrences
from Book

GROUP BY
published_by,
Works for,

SELECT Book.title,
Book.published_by
From Book;

11.Provide the name of each publisher that is located in ‘OKC’ and is one of the top three publishers in terms of the total amount of salary it pays its editors.

SELECT name from publisher WHERE ( city = 'OKC')

UNION

SELECT name from editor
WHERE IN ( SELECT eSSN, MAX(salary) as max_salary from Editor )
GROUP BY salary;