Question

IN MY SQL:

- Display each Author’s ID, first and last names and the total number of pages for all of the Books they have written.

- Display each Book genre as well as the number of Books written in that genre with the column header “Number Of Books”

- Display the Author’s first and last name, as well as their ID, and the Book title and number of pages for all of the books they have written that have more than the average number of pages for all of the books that have been written, listed by author’s first and last name along with the book title, and the book’s number of pages.

- Display the Author’s or Authors’ ID, first and last name of the Author who has the most Books in the Coastal Publishing database.

LINK TO WHAT THE SETUP AND INSERTS LOOK LIKE:

https://docs.google.com/document/d/1Qb30rS-g03pUBBFGBRwq-f9L8CsHY0Dz_HKpt_lt9uM/edit?usp=sharing

Answer 1

1.

Select AuthorID,AuthorFirstName,AuthorLastName, SUM(BookNumberOfPages) from CoastalPublishing.Author inner join CoastalPublishing.Book on CoastalPublishing.Author.AuthorID = CoastalPublishing.Book.Author_authorID group by AuthorID,AuthorFirstName,AuthorLastName;

2.

Select BookGenre , Count(BookTitle) as 'Number Of Books' from CoastalPublishing.Book group by BookGenre;

3.

Select AuthorID,AuthorFirstName,AuthorLastName, SUM(BookNumberOfPages) from CoastalPublishing.Author inner join CoastalPublishing.Book on CoastalPublishing.Author.AuthorID = CoastalPublishing.Book.Author_authorID group by AuthorID,AuthorFirstName,AuthorLastName having SUM(BookNumberOfPages) > AVG(BookNumberOfPages) order by AuthorFirstName,AuthorLastName, BookTitle, SUM(BookNumberOfPages);

4.

Select AuthorID,AuthorFirstName,AuthorLastName, SUM(BookNumberOfPages) from CoastalPublishing.Author inner join CoastalPublishing.Book on CoastalPublishing.Author.AuthorID = CoastalPublishing.Book.Author_authorID group by AuthorID,AuthorFirstName,AuthorLastName having Count(BookTitle) = Max(BookTitle);

Do ask if any doubt. Please upvote.