This is the original books.sql DROP DATABASE IF EXISTS books; CREATE DATABASE bo
ID: 3794996 • Letter: T
Question
This is the original books.sql
DROP DATABASE IF EXISTS books;
CREATE DATABASE books;
USE books;
CREATE TABLE Authors
(
AuthorID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL
) ;
CREATE TABLE Titles
(
ISBN varchar(20) NOT NULL PRIMARY KEY,
Title varchar(100) NOT NULL,
EditionNumber int NOT NULL,
Copyright varchar(4) NOT NULL
) ;
CREATE TABLE AuthorISBN
(
AuthorID int NOT NULL,
ISBN varchar(20) NOT NULL,
FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID),
FOREIGN KEY (ISBN) References Titles(ISBN)
) ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Harvey','Deitel') ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Paul','Deitel') ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Andrew','Goldberg') ;
INSERT INTO Authors (FirstName,LastName) VALUES ('David','Choffnes') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0131869000','Visual Basic 2005 How to Program',3,'2006') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0131869000') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0131869000') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0131525239','Visual C# 2005 How to Program',2,'2006') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0131525239') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0131525239') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132222205','Java How to Program',7,'2007') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132222205') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132222205') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0131857576','C++ How to Program',5,'2005') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0131857576') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0131857576') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132404168','C How to Program',5,'2007') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132404168') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132404168') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0131450913','Internet & World Wide Web How to Program',3,'2004') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0131450913') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0131450913') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (3,'0131450913') ;
INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0131828274','Operating Systems',3,'2004') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0131828274') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0131828274') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (4,'0131828274') ;
I need these questions answered in order to inplement them in this SQL
Write SQL queries for the books database 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).
Explanation / Answer
a)Select all authors from the Authors table with the columns in the order lastName,
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 Title,Copyright,ISBN FROM Titles,AuthorISBN,Authors WHERE Titles.ISBN=AuthorISBN.ISBN and AuthorISBN.AuthorID=Authors.AuthorID
c) Add a new author to the Authors table.
=> INSERT INTO Authors (FirstName,LastName) VALUES ('George','Martin');
d) Add a new title for an author (remember that the book must have an entry in the
AuthorISBN table).
=> INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0131828274','Database',6,'2003') ;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.