18.5 Write SQL queries for the books database (discussed in Section 18.3) that p
ID: 3769093 • Letter: 1
Question
18.5 Write SQL queries for the books database (discussed in Section 18.3) that perform each of the following tasks:
Select all authors from the Authors table with the columns in the order lastName, firstName and authorID.
Select a specific author and list all books for that author. Include the title, year and ISBN number. Order the information alphabetically by title.
Add a new author to the Authors table.
Add a new title for an author (remember that the book must have an entry in the AuthorISBN table).
Fig. 18.3 Authors table from the books database.
Column Description
AuthorID Author’s ID number in the database. In the books database, this integer column is defined as autoincremented—for each row inserted in this table, the AuthorID value is increased by 1 automatically to ensure that each row has a unique AuthorID. This column represents the table’s primary key.
FirstName Author’s first name (a string).
LastName Author’s last name (a string).
Explanation / Answer
a. Select all authors from the Authors table with the columns in the orderlastName, firstName and authorID.
SELECT lastName, firstName, authorID
FROM authors
b. Select a specific author and list all books for that author. Include the title, year and ISBN number. Order the information alphabetically by title.
SELECT firstName, lastName, title, year, ISBN
FROM authors, authorISBN, titles
WHERE authorID = 1
ORDER BY title ASC
c. Add a new author to the Authors table.
INSERT INTO authors ( authorID, lastName, firstName )
VALUES ( 25, 'lighnin', 'friend' )
d. Add a new title for an author (remember that the book must have an entry in the AuthorISBN table).
INSERT INTO authorISBN, titles( authorISBN.authorID, authorISBN.ISBN, titles.ISBN, titles.title, titles.editionNumber, titles.copyright )
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.