Question
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,
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.
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. (15 pts)
Provide the title of those books whose authors last name is neither Smith nor Doe. (15 pts)
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)
Provide the title of the most expensive book published by the publisher named ABC. (10 pts)
Provide the first name and the last name of those editors who have edited all the books written by John Smith. (5 pts)
Give the title of those books that have more than 3 authors. (15 pts)
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
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)
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)
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)
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started