Subject: Relational Database With MySQL, I need help creating queries for the fo
ID: 3698652 • Letter: S
Question
Subject: Relational Database
With MySQL, I need help creating queries for the following:
6. Display the Author’s first and last name and their Agent’s first and last name for all Author’s whose last name starts with the same first letter of your last name (if no match try the first letter of your first name but could result in no match).
7. Display the Book title and Author’ first and last name of the Books whose genre is either Biography or History.
8. Display the Author’s first and last name, Book’s title and genre, Editor’s first and last name for all Books that are in the science fiction genre.
9. Display each Editor’s first and last name along with their mentor’s first and last name with column headers MentorFirstName and MentorLastName respectively.
10. Display the Editors’ first and last name, as well as the Book’s title sorted by Book’s title first and the Editor’s last name second for all Books that were published on September 26, 2006.
11. Display the Editor’s first and last name along with their ID for the Editor that has edited the most Books for Coastal Publishing.
12. Display each Author’s ID, first and last names and the total number of pages for all of the Books they have written.
13. Display the Author’s ID, first and last name of the Author who has the most Books in the Coastal Publishing database.
14. Display each Book genre as well as the number of Books written in that genre with the column header “Number Of Books”
15. 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.
___________________________________________
Here are the downloads for the setup and inserts files (If you need them)
MySQL Setup File: https://www.dropbox.com/s/2yyx1t2ntfa02xg/CoastalPublishingSetup.sql?dl=0
MySQL Inserts File: https://www.dropbox.com/s/zldaunxis6j5rf5/CoastalPublishingInserts.sql?dl=0
Explanation / Answer
6.select AuthorFirstName, AuthorLastName,AgentFirstName, AgentLastName
from Author A
left join Agent AG
on A.Agent_agent_id=AG.agentID
where AuthorLastName like 'S%'
7.select BookTitle,AuthorFirstName, AuthorLastName
from Author A
left join Book B
on A.authorID=B.Author_authorID
where B.BookGenre in ('Biography','History')
8.select AuthorFirstName,AuthorLastName,BookTitle,BookGenre,
EditorFirstName,EditorLastName
from Author A
left join Books B
on A.authorID=B.Author_authorID
left join Editor E
on E.editorID=B.Editor_editorID
where B.BookGenre='Science Fiction'
9.select EditorFirstName,EditorLastName,EditorFirstName as MentorFirstName
,EditorLastName as MentorLastName
from Editor E
where E.EditorID=E.mentorID
10.select EditorFirstName,EditorLastName,BookTitle,EditorLastName
from Books B
left join Editor E
on E.editorID=B.Editor_editorID
where BookPublishDate='09/26/2006'
order by BookTitle asc,EditorLastName asc