Population Database Compile and run CreateCityDB.java which will create a Java DB database named CityDB. The CityDB database will have a table named City, with the following columns: CityName & Population. Their Data Types: CHAR(50) Primary Key & 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, create an application 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.
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
Population
Compile and run CreateCityDB.java which will create a Java DB database named CityDB. The CityDB database will have a table named City, with the following columns: CityName & Population. Their Data Types: CHAR(50) Primary Key & 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
- 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.
CreateCityDB.java
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";
// Create a connection to the database.
Connection conn = DriverManager.getConnection(DB_URL);
// Create a Statement object.
Statement stmt = conn.createStatement();
try
{
// Create the City 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);
}
catch(Exception ex)
{
System.out.println("ERROR: " + ex.getMessage());
}
finally
{
// Close the resources.
stmt.close();
conn.close();
System.out.println("Done");
}
}
}
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
