How Do I Create The Cities table

 Introduction

Creating the Cities table in Oracle APEX is a straightforward task that allows you to store geographic and location-based data, such as city names and their corresponding latitude and longitude coordinates. This type of table is especially useful for applications involving maps, search filters, and location analytics. Using SQL Workshop in Oracle APEX, you can run a SQL script to define the table structure, apply constraints, and populate the table with a predefined list of major U.S. cities.

Creating the Cities table in Oracle APEX involves defining the table structure and populating it with initial data. This process uses SQL commands executed within the SQL Workshop environment.

First, the table is created using the CREATE TABLE statement. The Cities table includes four columns:

• city_id: A numeric column generated automatically as an identity (auto-increment) and set as the primary key.

• city_name: A VARCHAR2 column (up to 50 characters) to store the name of the city; this column is mandatory (NOT NULL).

• latitude and longitude: Numeric columns that store geographic coordinates with precision to six decimal places, also mandatory.

After defining the table, a PL/SQL block inserts multiple rows of city data. Each INSERT statement adds a city with its name, state, latitude, and longitude. The COMMIT statement finalizes these changes in the database.

Steps to Create the Cities Table in Oracle APEX:

1. Open SQL Workshop in your Oracle APEX workspace.

2. Navigate to SQL Commands.

3. Copy and paste the following SQL to create the table:

CREATE TABLE Cities (
    city_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    city_name VARCHAR2(50) NOT NULL,
    latitude NUMBER(9,6) NOT NULL,
    longitude NUMBER(9,6) NOT NULL
);

4. Run the above command to create the table.

5. Next, copy and paste the PL/SQL block with multiple INSERT statements to populate the table:

BEGIN
    INSERT INTO Cities (city_name, latitude, longitude) VALUES ('New York', 40.712776, -74.005974);
    INSERT INTO Cities (city_name, latitude, longitude) VALUES ('Los Angeles', 34.052235, -118.243683);
    INSERT INTO Cities (city_name, latitude, longitude) VALUES ('Chicago', 41.878113, -87.629799);
    -- (repeat for other cities as needed)
    COMMIT;
END;

6. Execute this block to insert all city records.

7. Verify the data by querying the table:

SELECT * FROM Cities;

Note: The example you provided included a state column in the inserts but not in the table definition. To avoid errors, ensure the state column is either added to the table or omitted from the inserts.

Using this approach in Oracle APEX, you create a structured table with geographic data ready for your applications. This enables location-based features such as mapping, filtering by city, or spatial analysis, all managed within the APEX environment without needing external tools.

Conclusion
By defining and populating the Cities table using SQL scripts in Oracle APEX, developers can establish a reusable, reliable source of location data for use across various applications. Whether you're building dashboards, forms, or geographic visualizations, having this structured city data ready in your database allows for more interactive and data-driven user experiences. With the flexibility of APEX and the power of SQL, managing and leveraging this type of reference data becomes both efficient and scalable.