How Do I Create The Airports table

 Creating the US_Airports table is a fundamental step for managing airport data efficiently in Oracle databases. This table captures key details about airports across the United States, including the city, state, airport code, and geographic coordinates. The table design ensures data integrity by enforcing constraints such as a three-character airport code and a primary key on this code, which uniquely identifies each airport. Setting up this table correctly allows you to store, query, and manipulate airport information for various applications like travel systems, mapping services, or logistics management.

Using Arial font, size 14px, plain text:

To create the US_Airports table in Oracle, you use the CREATE TABLE statement. This defines the structure of your table and the data types of each column. Here's how you do it step-by-step:

1. Define the table and columns:

CREATE TABLE US_Airports (
    City VARCHAR2(100),
    State VARCHAR2(100),
    Airport_Code VARCHAR2(10) CONSTRAINT chk_airport_code CHECK (LENGTH(Airport_Code) = 3),
    Latitude NUMBER(9,6),
    Longitude NUMBER(9,6),
    CONSTRAINT pk_airport PRIMARY KEY (Airport_Code)
);

• City and State are text columns with max length 100 characters.

• Airport_Code is a 3-letter airport code; the check constraint chk_airport_code ensures its length is exactly 3.

• Latitude and Longitude are numeric columns that hold coordinates with precision up to 6 decimal places.

• The PRIMARY KEY constraint on Airport_Code makes sure each airport code is unique and indexed for fast lookups.

2. Add data into the table

To populate the table with airports, you use INSERT INTO statements specifying the column values in the correct order. Each insert must be followed by a COMMIT; to save changes permanently.

Example:

INSERT INTO US_AIRPORTS (City, State, Airport_Code, Latitude, Longitude)
VALUES ('Atlanta', 'Georgia', 'ATL', 33.6407, -84.4277);
COMMIT;

3. Notes on the insert statements

• Make sure the column names in the insert statement exactly match those in the table (State not ST as in some examples).

• The Airport_Code must be 3 characters; otherwise, the check constraint will reject the insert.

• Latitude and Longitude must be numeric values with up to 6 decimal places.

• You can insert multiple rows one by one as above, or use batch inserts if preferred.

4. Full insert example

Here is a corrected and expanded version of the insert statements with proper column names (State instead of ST):

INSERT INTO US_AIRPORTS (City, State, Airport_Code, Latitude, Longitude) VALUES ('Atlanta', 'Georgia', 'ATL', 33.6407, -84.4277); COMMIT;
INSERT INTO US_AIRPORTS (City, State, Airport_Code, Latitude, Longitude) VALUES ('Los Angeles', 'California', 'LAX', 33.9416, -118.4085); COMMIT;
INSERT INTO US_AIRPORTS (City, State, Airport_Code, Latitude, Longitude) VALUES ('Chicago', 'Illinois', 'ORD', 41.9742, -87.9073); COMMIT;
INSERT INTO US_AIRPORTS (City, State, Airport_Code, Latitude, Longitude) VALUES ('Dallas', 'Texas', 'DFW', 32.8998, -97.0403); COMMIT;
-- Continue adding rows in the same format

5. Creating and populating in Oracle APEX SQL Workshop

• Log in to your Oracle APEX workspace.

• Navigate to SQL Workshop > SQL Commands.

• Paste the CREATE TABLE statement and run it.

• Paste the INSERT statements one by one or in batches and run them.

• Verify your data by running:

SELECT * FROM US_AIRPORTS;

6. Additional Tips

• Use uppercase for SQL keywords (CREATE TABLE, INSERT INTO, etc.) for readability.

• Use consistent column names.

• If inserting many rows, you may want to disable COMMIT after each insert and commit once at the end for better performance.

• Consider adding an index on frequently searched columns if needed (besides the primary key).

To create the US_Airports table, you use the CREATE TABLE statement, specifying columns for City, State, Airport_Code, Latitude, and Longitude. A check constraint ensures airport codes are exactly three characters long, and the primary key constraint guarantees uniqueness. After the table is created, you populate it with data using multiple INSERT INTO statements, each adding a specific airport's details. Committing after each insert ensures the data is saved reliably. This setup forms a solid foundation for any system needing accurate airport location data and supports efficient querying and reporting.