Using the following books.sql table, an example of using a query against the books.sql database is below in the class DisplayAuthors to . Perform the following:

Select a specific author and list all books for that author. Include each books title, year, and ISBN. Order the information chronologically.

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

SQL Tables called books.sql

DROP TABLE authorISBN; DROP TABLE titles; DROP TABLE authors;

CREATE TABLE authors ( authorID INT NOT NULL GENERATED ALWAYS AS IDENTITY, firstName varchar (20) NOT NULL, lastName varchar (30) NOT NULL, PRIMARY KEY (authorID) );

CREATE TABLE titles ( isbn varchar (20) NOT NULL, title varchar (100) NOT NULL, editionNumber INT NOT NULL, copyright varchar (4) NOT NULL, PRIMARY KEY (isbn) ); 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'), ('Harvey','Deitel'), ('Abbey','Deitel'), ('Dan','Quirk'), ('Michael','Morgano');

INSERT INTO titles (isbn,title,editionNumber,copyright) VALUES ('0132151006','Internet & World Wide Web How to Program',5,'2012'), ('0133807800','Java How to Program',10,'2015'), ('0132575655','Java How to Program, Late Objects Version',10,'2015'), ('013299044X','C How to Program',7,'2013'), ('0132990601','Simply Visual Basic 2010',4,'2013'), ('0133406954','Visual Basic 2012 How to Program',6,'2014'), ('0133379337','Visual C# 2012 How to Program',5,'2014'), ('0136151574','Visual C++ How to Program',2,'2008'), ('0133378713','C++ How to Program',9,'2014'), ('0133764036','Android How to Program',2,'2015'), ('0133570924','Android for Programmers: An App-Driven Approach, Volume 1',2,'2014'), ('0132121360','Android for Programmers: An App-Driven Approach',1,'2012');

INSERT INTO authorISBN (authorID,isbn) VALUES (1,'0132151006'), (2,'0132151006'), (3,'0132151006'), (1,'0133807800'), (2,'0133807800'), (1,'0132575655'), (2,'0132575655'), (1,'013299044X'), (2,'013299044X'), (1,'0132990601'), (2,'0132990601'), (3,'0132990601'), (1,'0133406954'), (2,'0133406954'), (3,'0133406954'), (1,'0133379337'), (2,'0133379337'), (1,'0136151574'), (2,'0136151574'), (4,'0136151574'), (1,'0133378713'), (2,'0133378713'), (1,'0133764036'), (2,'0133764036'), (3,'0133764036'), (1,'0133570924'), (2,'0133570924'), (3,'0133570924'), (1,'0132121360'), (2,'0132121360'), (3,'0132121360'), (5,'0132121360');

Create the connection to the following java class

import java.sql.Connection; import java.sql.Statement; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException;

public class DisplayAuthors { public static void main(String args[]) { final String DATABASE_URL = "jdbc:derby://localhost:1527/books"; final String SELECT_QUERY = "SELECT authorID, firstName, lastName FROM authors";

// use try-with-resources to connect to and query the database try ( Connection connection = DriverManager.getConnection( DATABASE_URL, "deitel", "deitel"); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(SELECT_QUERY)) { // get ResultSet's meta data ResultSetMetaData metaData = resultSet.getMetaData(); int numberOfColumns = metaData.getColumnCount(); System.out.printf("Authors Table of Books Database:%n%n");

// display the names of the columns in the ResultSet for (int i = 1; i <= numberOfColumns; i++) System.out.printf("%-8s\t", metaData.getColumnName(i)); System.out.println(); // display query results while (resultSet.next()) { for (int i = 1; i <= numberOfColumns; i++) System.out.printf("%-8s\t", resultSet.getObject(i)); System.out.println(); } } // AutoCloseable objects' close methods are called now catch (SQLException sqlException) { sqlException.printStackTrace(); } } } // end class DisplayAuthors