Publisher (name, phone, city), PK: name. Book (ISBN, title, year, published_by,

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, 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 author’s 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)

Explanation / Answer

--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)
SELECT b.title,b.year,e.first_name,e.last_name from Book b INNER JOIN Edit it ON b.ISBN=it.bISBN
INNER JOIN Editor e ON it.eSSN=e.SSN;

--Provide the title of those books whose author’s last name is neither “Smith” nor “Doe”. (15 pts)
SELECT b.title from Book b INNER JOIN Write it ON b.ISBN=it.bISBN
INNER JOIN Author e ON it.aSSN=e.SSN
WHERE e.last_name NOT in ('Smith','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. (15 pts)
SELECT a.first_name, a.last_name from Author a INNER JOIN Author_Editor ae ON a.SSN=ae.aeSSN;

--Provide the title of the most expensive book published by the publisher named “ABC”. (10 pts)
SELECT title from Book where price= (SELECT MAX(price) from Book where published_by='ABC') and published_by='ABC';

--Provide the first name and the last name of those editors who have edited all the books written by John Smith. (5 pts)
SELECT e.first_name, e.last_name from Editor WHERE SSN IN (SELECT aSSN from Author WHERE first_name='John' and last_name='Smith');

--Give the title of those books that have more than 3 authors. (15 pts)
SELECT title from Book WHERE ISBN IN (SELECT aSSN from Write WHERE COUNT(aSSN)>3)

NOte: AS per chegg rules we can solve only 1st 5 sub question. please post other question in another post. Thanks

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.