24.2 (Query Application for the books Database) Using the techniques shown in th

Question

24.2 (Query Application for the books Database) Using the techniques shown in this chapter, define a complete query application for the books database. Provide the following predefined queries:

Select all authors from the Authors table.

Select a specific author and list all books for that author. Include each book’s title, year and ISBN. Order the information alphabetically by the author’s last name then by first name.

Select a specific title and list all authors for that title. Order the authors alphabetically by last name then by first name.

Provide any other queries you feel are appropriate.

Explanation / Answer

is this u r looking for

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 ('Paul','Deitel') ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Harvey','Deitel') ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Abbey','Deitel') ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Michael','Morgano') ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Eric','Kern') ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132152134','How to Program',5,'2011') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132152134') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132152134') ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132151421','Visual C# 2010 How to Program',4,'2011') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132151421') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132151421') ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132575663','Java How to Program',9,'2012') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132575663') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132575663') ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132662361','C++ How to Program',8,'2012') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132662361') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132662361') ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132404168','C How to Program',6,'2010') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132404168') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132404168') ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('013705842X','iPhone for Programmers: An App-Driven Approach',1,'2010') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'013705842X') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'013705842X') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (3,'013705842X') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (4,'013705842X') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (5,'013705842X') ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132121360','Android for Programmers: An App-Driven Approach',1,'2012') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132121360') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132121360') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (3,'0132121360') ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (4,'0132121360')

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

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