Instruction:

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.

**NOTE** the program below is the CityDB database that the above program needs to connect to.

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());
}
}
}

Academic Honesty!
It is not our intention to break the school's academic policy. Projects posted are only used as a reference and should not be submitted as is. We are not held liable for any misuse of the solutions. Please see the frequently asked questions page for further questions and inquiries.
Kindly fill out the form. Please provide a valid email address and we'll get back to you in less than 24 hours. We will be sending an invoice through PayPal upon confirmation. We are a non profit organization however we need an amount to keep this organization running, and to be able to complete our research and development.