18.5 Write SQL queries for the books database (discussed in Section 18.3 ) that
ID: 3534203 • Letter: 1
Question
18.5 Write SQL queries for the books database (discussed in Section 18.3) that perform each of the following tasks:
a. Select all authors from the Authors table with the columns in the order lastName, firstName and authorID.
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.
c. Add a new author to the Authors table.
d. Add a new title for an author (remember that the book must have an entry in the AuthorISBN table).
18.3
Author ID Authors ID number in the database. In the books database, this integer column is defined as autoincremented-for each row inserted in this table, the author ID value is increased by 1 automatically to ensure that each row has a unique author ID. This column represents the tables primary Key.
FirstName Authors first name (a string)
LastName Authors Last Name (a String)
Author ID First Name Last Name
1 Paul Deitel
2 Harvey Deitel
3 Abbey Deitel
4 Michael Morgano
5 Eric Kern
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 )
VALUES ( 5, XX-XXXXX, XX-XXXXX, 'How to Be Awesome', 23, 2013)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.