The CityDB database will have a table named City, with the following columns: Co

ID: 3683166 • Letter: T

Question

The CityDB database will have a table named City,
with the following columns:
Column Name Data Type
CityName CHAR (50)
Primary key
Population DOUBLE

The CityName column stores the name of a city and the Population column stores rhe
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 bv 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.

/** code for the CityDB */

import java.sql.*;

/**
This program creates the CityDB database. *
*/

public class CreateCityDB
{
public static void main(String[] args) throws Exception
{
       String sql;
final String DB_URL =
"jdbc:derby:CityDB;create=true";

try
{
// Create a connection to the database.
Connection conn =
DriverManager.getConnection(DB_URL);

// Create a Statement object.
Statement stmt = conn.createStatement();

// Create the Dvd table.
System.out.println("Creating the City table...");
stmt.execute("CREATE TABLE City (" +
"CityName CHAR(25) NOT NULL PRIMARY KEY, " +
"Population DOUBLE)");

// Add some rows to the new table.
sql = "INSERT INTO City VALUES" +
"('Beijing', 12500000)";
stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" +
"('Buenos Aires', 13170000)";
stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" +
"('Cairo', 14450000)";
stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" +
"('Calcutta', 15100000)";
stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" +
"('Delhi', 18680000)";
stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" +
"('Jakarta', 18900000)";
stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" +
"('Karachi', 11800000)";
stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" +
"('Lagos', 13488000)";
stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" +
"('London', 12875000)";
stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" +
"('Los Angeles', 15250000)";
stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" +
"('Manila', 16300000)";
stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" +
"('Mexico City', 20450000)";
stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" +
"('Moscow', 15000000)";
stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" +
"('Mumbai', 19200000)";
stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" +
"('New York City', 19750000)";
stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" +
"('Osaka', 17350000)";
stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" +
"('Sao Paulo', 18850000)";
stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" +
"('Seoul', 20550000)";
stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" +
"('Shanghai', 16650000)";
stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" +
"('Tokyo', 32450000)";
stmt.executeUpdate(sql);

// Close the resources.
stmt.close();
conn.close();
System.out.println("Done");
}
catch(Exception ex)
{
System.out.println("ERROR: " + ex.getMessage());
}
}
}

Explanation / Answer

Program code: (given in quesion)

import java.sql.*;

/**

This program creates the CityDB database. *

public class CreateCityDB {

public static void main(String[] args) throws Exception {

String sql;

final String DB_URL = "jdbc:derby:CityDB;create=true";

try {

// Create a connection to the database.

Connection conn = DriverManager.getConnection(DB_URL);

// Create a Statement object.

Statement stmt = conn.createStatement();

// Create the Dvd table.

System.out.println("Creating the City table...");

stmt.execute("CREATE TABLE City (" +

"CityName CHAR(25) NOT NULL PRIMARY KEY, " +

"Population DOUBLE)");

// Add some rows to the new table.