This is the original bookssql DROP DATABASE IF EXISTS books

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).

Solution

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\')   ;