Question

Population Database Make sure you have downloaded the book's source code from the companion Web site at www.pearson.com/gaddis. In this chapter's source code folder named CreateCityDB.java. Compile and run the program. The program will create a Java you will find a program 1108 Chapter 16 Databases DB or Apache Derby database named CityDB. The CityDB database will have a table named City, with the following columns: Data Type CHAR (50) Column Name CityName Primary key Population DOUBLE The CityName column stores the name of a city and the Population column stores the population of that city. After you run the CreateCityDB.java program, the City table will contain 20 rows with various cities and their populations. Next, write a program that connects to the CityDB database, and allows the user to select any of the following operations: • Sort the list of cities by population, in ascending order. • Sort the list of cities by population, in descending order. • Sort the list of cities by name. • Get the total population of all the cities. • Get the average population of all the cities. • Get the highest population. • Get the lowest population.

Answer 1

To connect to the CityDB database and perform the requested operations, you can use the Java Database Connectivity (JDBC) API. Here is an example of how you can implement the program.

Step-by-step explanation:

To connect to the CityDB database and perform the requested operations, you can use the Java Database Connectivity (JDBC) API. Here is an example of how you can implement the program:

1. Import the necessary JDBC classes: import java.sql.*;
2. Establish a connection to the CityDB database: Connection conn = DriverManager.getConnection("jdbc:derby:CityDB");
3. Create a statement object: Statement stmt = conn.createStatement();
4. Display the menu options to the user and prompt for their selection.
5. Based on the user's selection, execute the corresponding SQL query:

• To sort the list of cities by population in ascending order:
• String query = "SELECT * FROM City ORDER BY Population ASC";

You can refer to the JDBC documentation for more information on executing SQL queries and retrieving results. Remember to handle exceptions and close the database connection properly.