CMSC4003

Homework5: SQL

Due: See the due date in D2L calendar.

Requirement: Your answers must be typed and printed. You may attach hand-drawndiagrams if needed.

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. Provide the title, year, and publisher name of every book and the first name and last name of the editor of the book. (15 pts)
  2. Provide the title of those books whose author’s last name is neither “Smith” nor “Doe”. (15 pts)
  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.(15 pts)
  4. Provide the title of the most expensive book published by the publisher named “ABC”.(10 pts)
  5. Provide the first name and the last name of those editors who have edited all the books written by John Smith. (5 pts)
  6. Give the title of those books that have more than 3 authors. (15 pts)
  7. Show thenumber 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

  1. Provide an SQL UPDATE statement that updates the book_count field of the Editor table by computing the number of books edited by eacheditorusing nested queries.(10 pts)
  2. 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)
  3. 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. (5 pts)