tblBooks tblAuthors BookID AutoNumber AuthorID AutoNumber AID Number FName Short

Question

tblBooks

tblAuthors

BookID

AutoNumber

AuthorID

AutoNumber

AID

Number

FName

Short Text

BTitle

Short Text

LName

Short Text

PurchaseDate

Date/Time

AComments

Short Text

Genre

Short Text

MediaType

Short Text

BSeries

Short Text

BComments

Short Text

Write the SQL for the following:

1.A listing of all the authors, sort by last name then first name.

2.An alphabetical listing of all the Books.

3.An alphabetical listing of all books by genre.

4.An alphabetical listing of all authors with their books – also alphabetical.

5.An alphabetical listing of the authors that have comments.

6.A listing of books that has the media type of audio.

7.An alphabetical listing of authors where the books are either audio or on an iPad.

8.An alphabetical listing of authors where the books are either on a Kindle or on an iPad.

9.An alphabetical listing of all books that are in a series.

10.An alphabetical listing of books that were purchased yesterday.

BONUS 7 Points: Do #10 asking for the current date.

tblBooks

tblAuthors

BookID

AutoNumber

AuthorID

AutoNumber

AID

Number

FName

Short Text

BTitle

Short Text

LName

Short Text

PurchaseDate

Date/Time

AComments

Short Text

Genre

Short Text

MediaType

Short Text

BSeries

Short Text

BComments

Short Text

Explanation / Answer

Answer:

1.select FName,LName from tblAuthors order by LName;
/*This query will list all the Authors name sorted by Last Name*/

Sample Output:

2.select * from tblBooks order by BTitle;
/*This query will list all the books details in Alphabetical order*/

Sample Output:

3.select * from tblBooks order by BTitle,Genre;
/*This query will list all the books details in Alphabetical order sorted by Genre*/

Sample Output:

4.select b.BTitle,a.FName
from tblBooks b join tblAuthors a on a.AuthorID = b.AID
order by b.BTitle;
/*This query will list all the books Titles and Author First Names in Alphabetical order sorted by BTitle*/

Sample Output:

Let me know if have any doubts in the queries.

FName LName Paul Andrews Symonds Brett Cerezo Christian Koomen Hans

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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