Creating and Managing Tables

The CREATE TABLE statement is a core DDL command used to define and build new tables in an Oracle database. It specifies the table's name, column names, their data types, constraints, and other properties, laying the foundation for storing data.

Example 1: Basic CREATE TABLE Statement

CREATE TABLE departments (
    department_id NUMBER(4),      -- Unique identifier for the department, up to 4 digits
    department_name VARCHAR2(30)   -- Name of the department, up to 30 characters
);

Explanation This SQL command creates a new table named departments with two columns: department_id (a number) and department_name (a variable-length string). This is a fundamental step in Oracle database design.

Example 2: CREATE TABLE with Primary Key Constraint

CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY, -- Unique identifier for each employee, serves as primary key
    first_name VARCHAR2(20),            -- Employee's first name
    last_name VARCHAR2(25) NOT NULL,    -- Employee's last name, cannot be NULL
    email VARCHAR2(25) UNIQUE,          -- Employee's email, must be unique across all employees
    phone_number VARCHAR2(20),
    hire_date DATE DEFAULT SYSDATE,     -- Date employee was hired, defaults to current date
    job_id VARCHAR2(10) NOT NULL,
    salary NUMBER(8,2) CHECK (salary > 0), -- Employee's salary, must be positive
    commission_pct NUMBER(2,2),
    manager_id NUMBER(6),
    department_id NUMBER(4)             -- Department ID, to be linked to departments table
);

Explanation This code creates the employees table, defining various columns with common Oracle SQL constraints like PRIMARY KEY, NOT NULL, UNIQUE, and CHECK. It also demonstrates DEFAULT SYSDATE for automatic date population.

Example 3: CREATE TABLE with Foreign Key Constraint

CREATE TABLE job_history (
    employee_id NUMBER(6) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    job_id VARCHAR2(10) NOT NULL,
    department_id NUMBER(4),
    CONSTRAINT pk_job_history PRIMARY KEY (employee_id, start_date), -- Composite primary key
    CONSTRAINT fk_employee FOREIGN KEY (employee_id) REFERENCES employees(employee_id), -- Links to employees table
    CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id) -- Links to departments table
);

Explanation This statement creates job_history with a composite primary key and two FOREIGN KEY constraints. These constraints enforce referential integrity, ensuring data consistency between job_history, employees, and departments tables in Oracle SQL.

Example 4: CREATE TABLE AS SELECT (CTAS)

CREATE TABLE old_employees AS
SELECT employee_id, first_name, last_name, hire_date
FROM employees
WHERE hire_date < TO_DATE('2000-01-01', 'YYYY-MM-DD'); -- Creates a new table from existing data

Explanation This CREATE TABLE AS SELECT (CTAS) statement efficiently creates a new table old_employees by copying structure and data from the employees table. It's excellent for creating backup tables or staging areas in Oracle 19c.

Example 5: CREATE TABLE with TABLESPACE and LOGGING Options

CREATE TABLE large_data (
    data_id NUMBER PRIMARY KEY,
    description VARCHAR2(100),
    blob_content BLOB
)
TABLESPACE users_data                      -- Specifies the tablespace for storing table data
LOGGING;                                   -- Enables logging of changes for recovery purposes

Explanation This example demonstrates creating a table and explicitly assigning it to the users_data tablespace, enhancing storage management. The LOGGING clause ensures that DML operations on this table are written to the redo log, crucial for Oracle database recovery.