Oracle DataBase Lesson 10 – Creating Tables in Oracle | Dataplexa

Creating Tables in Oracle

Lesson 8 introduced tables at a structural level. This lesson goes deeper — applying the data types from Lesson 9 to build production-quality table definitions with correct column sizing, appropriate defaults, and well-reasoned nullability decisions. By the end of this lesson every table in both schemas will be fully defined with the right types on every column, ready for constraints to be added in Lesson 11.

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

CREATE TABLE Syntax in Full

The full CREATE TABLE syntax in Oracle covers the table name, column list, and optional table-level clauses. Each column is defined with a name, a data type, an optional DEFAULT value, and an optional NOT NULL constraint. The TABLESPACE clause directs Oracle to store the table in a specific tablespace rather than the default USERS tablespace.

Oracle evaluates column definitions in order — the first column defined is the leftmost column in every row. Column order matters for readability and convention: identifiers first, descriptive columns next, foreign key references after that, status and flag columns near the end, and audit columns like created_date last.

-- TABLESPACE directs storage to a named tablespace rather than the default USERS
CREATE TABLE customers (
    customer_id    NUMBER(10)     NOT NULL,
    full_name      VARCHAR2(100)  NOT NULL,
    email          VARCHAR2(150)  NOT NULL,
    phone          VARCHAR2(20),
    country        VARCHAR2(60)   NOT NULL,
    city           VARCHAR2(60),
    loyalty_tier   VARCHAR2(20)   DEFAULT 'standard' NOT NULL,
    created_date   DATE           DEFAULT SYSDATE    NOT NULL
)
TABLESPACE dataplexa_store_ts;

-- Verify the table landed in the correct tablespace
SELECT table_name,
       tablespace_name,
       num_rows,
       last_analyzed
FROM   user_tables
WHERE  table_name = 'CUSTOMERS';

Table CUSTOMERS created.

TABLE_NAME TABLESPACE_NAME NUM_ROWS LAST_ANALYZED
─────────── ──────────────────── ──────── ─────────────
CUSTOMERS DATAPLEXA_STORE_TS 0 (null)

If TABLESPACE is omitted Oracle places the table in the default tablespace for the connected user — in XE this is USERS; in production always specify it explicitly
num_rows shows 0 immediately after creation because statistics have not been gathered yet — use SELECT COUNT(*) for an accurate live count
Column order is permanent — Oracle does not allow reordering columns after creation without dropping and recreating the table

Designing Column Definitions

Good column definitions are precise — sized to hold the real data, typed to match the real meaning, and constrained to reflect the real business rules. Every column definition answers three questions: what type of data is it, how large can it be, and is it always required?

A common mistake is oversizing columns — using VARCHAR2(4000) for a status code or NUMBER without precision for a price. Oversizing does not increase storage for VARCHAR2 but it communicates nothing about the data and allows invalid values in. Right-sizing is documentation as well as enforcement.

-- DataplexaStore — all five tables with production-quality column definitions
CREATE TABLE customers (
    customer_id    NUMBER(10)     NOT NULL,
    full_name      VARCHAR2(100)  NOT NULL,
    email          VARCHAR2(150)  NOT NULL,
    phone          VARCHAR2(20),              -- optional — international format e.g. +44 7700 900123
    country        VARCHAR2(60)   NOT NULL,
    city           VARCHAR2(60),
    loyalty_tier   VARCHAR2(20)   DEFAULT 'standard' NOT NULL,
    created_date   DATE           DEFAULT SYSDATE    NOT NULL
)
TABLESPACE dataplexa_store_ts;

CREATE TABLE products (
    product_id     NUMBER(10)     NOT NULL,
    product_name   VARCHAR2(150)  NOT NULL,
    category       VARCHAR2(60)   NOT NULL,
    unit_price     NUMBER(10,2)   NOT NULL,
    stock_qty      NUMBER(8)      DEFAULT 0   NOT NULL,
    description    VARCHAR2(500),
    active_flag    CHAR(1)        DEFAULT 'Y' NOT NULL,  -- CHAR(1): always exactly 'Y' or 'N'
    created_date   DATE           DEFAULT SYSDATE NOT NULL
)
TABLESPACE dataplexa_store_ts;

CREATE TABLE orders (
    order_id       NUMBER(10)     NOT NULL,
    customer_id    NUMBER(10)     NOT NULL,
    order_date     DATE           DEFAULT SYSDATE   NOT NULL,
    status         VARCHAR2(20)   DEFAULT 'pending' NOT NULL,
    total_amount   NUMBER(12,2)   DEFAULT 0         NOT NULL  -- NUMBER(12,2): totals can exceed a single item price
)
TABLESPACE dataplexa_store_ts;

CREATE TABLE order_items (
    order_item_id  NUMBER(10)     NOT NULL,
    order_id       NUMBER(10)     NOT NULL,
    product_id     NUMBER(10)     NOT NULL,
    quantity       NUMBER(6)      NOT NULL,
    unit_price     NUMBER(10,2)   NOT NULL  -- price at time of order — stored independently of products.unit_price
)
TABLESPACE dataplexa_store_ts;

CREATE TABLE payments (
    payment_id     NUMBER(10)     NOT NULL,
    order_id       NUMBER(10)     NOT NULL,
    payment_date   DATE           DEFAULT SYSDATE NOT NULL,
    amount         NUMBER(12,2)   NOT NULL,
    method         VARCHAR2(30)   NOT NULL
)
TABLESPACE dataplexa_store_ts;

Table CUSTOMERS created.
Table PRODUCTS created.
Table ORDERS created.
Table ORDER_ITEMS created.
Table PAYMENTS created.

unit_price in order_items is separate from products.unit_price by design — product prices change over time, but a historical order line must always reflect the price at the moment of purchase
active_flag CHAR(1) is one of the few legitimate uses of CHAR — the value is always exactly one character, never longer or shorter
total_amount in orders uses NUMBER(12,2) rather than NUMBER(10,2) — order totals aggregate multiple items and can legitimately exceed the range of a single item price

Creating the DataplexaHR Tables

The HR schema introduces one important design consideration: self-referencing columns. The manager_id in employees references the same table — an employee's manager is also an employee. Both manager_id columns are nullable because not every row has a parent — the most senior executive has no manager, and a newly created department may not yet have one assigned.

CREATE TABLE departments (
    department_id    NUMBER(6)      NOT NULL,
    department_name  VARCHAR2(100)  NOT NULL,
    location         VARCHAR2(100),
    manager_id       NUMBER(6)      -- NULL allowed — a new department may not yet have a manager
)
TABLESPACE dataplexa_hr_ts;

CREATE TABLE jobs (
    job_id      VARCHAR2(20)   NOT NULL,  -- readable code e.g. 'IT_PROG', 'HR_MGR'
    job_title   VARCHAR2(100)  NOT NULL,
    min_salary  NUMBER(10,2)   NOT NULL,
    max_salary  NUMBER(10,2)   NOT NULL
)
TABLESPACE dataplexa_hr_ts;

CREATE TABLE employees (
    employee_id    NUMBER(6)      NOT NULL,
    first_name     VARCHAR2(50)   NOT NULL,
    last_name      VARCHAR2(50)   NOT NULL,
    email          VARCHAR2(150)  NOT NULL,
    hire_date      DATE           NOT NULL,
    job_id         VARCHAR2(20)   NOT NULL,
    department_id  NUMBER(6),    -- NULL allowed — new starters may not yet be assigned
    manager_id     NUMBER(6)     -- NULL allowed — the most senior employee has no manager
)
TABLESPACE dataplexa_hr_ts;

CREATE TABLE salaries (
    salary_id       NUMBER(10)    NOT NULL,
    employee_id     NUMBER(6)     NOT NULL,
    amount          NUMBER(10,2)  NOT NULL,
    effective_date  DATE          NOT NULL,
    salary_type     VARCHAR2(20)  DEFAULT 'monthly' NOT NULL
)
TABLESPACE dataplexa_hr_ts;

CREATE TABLE payroll_history (
    payroll_id    NUMBER(10)    NOT NULL,
    employee_id   NUMBER(6)     NOT NULL,
    pay_period    DATE          NOT NULL,
    gross_pay     NUMBER(10,2)  NOT NULL,
    tax_deducted  NUMBER(10,2)  DEFAULT 0 NOT NULL,  -- defaults to 0 before payroll is processed
    net_pay       NUMBER(10,2)  NOT NULL
)
TABLESPACE dataplexa_hr_ts;

Table DEPARTMENTS created.
Table JOBS created.
Table EMPLOYEES created.
Table SALARIES created.
Table PAYROLL_HISTORY created.

job_id is VARCHAR2(20) rather than NUMBER — codes like IT_PROG and HR_MGR are readable in queries and reports; a numeric surrogate key would carry no meaning on its own
hire_date is NOT NULL — there is no valid scenario where an employee exists in a payroll system without a known start date
Creation order matters when foreign keys are added in Lesson 11 — jobs and departments must exist before employees, and employees before salaries and payroll_history

Querying Table Structure

After creating tables, Oracle's data dictionary gives you several ways to inspect and verify the structure. These queries are essential during development and debugging when you need to confirm exactly what was created.

-- List all tables in the current schema with tablespace assignment
SELECT table_name,
       tablespace_name,
       num_rows
FROM   user_tables
ORDER BY tablespace_name, table_name;

-- Full column detail for a specific table — column_id preserves creation order
SELECT column_id,
       column_name,
       data_type,
       data_length,
       data_precision,
       data_scale,
       nullable,
       data_default
FROM   user_tab_columns
WHERE  table_name = 'EMPLOYEES'
ORDER BY column_id;

-- Count columns per table across both schemas
SELECT table_name,
       COUNT(*) AS column_count
FROM   user_tab_columns
WHERE  table_name IN (
    'CUSTOMERS','PRODUCTS','ORDERS','ORDER_ITEMS','PAYMENTS',
    'DEPARTMENTS','JOBS','EMPLOYEES','SALARIES','PAYROLL_HISTORY'
)
GROUP BY table_name
ORDER BY table_name;

-- user_tables:
TABLE_NAME TABLESPACE_NAME NUM_ROWS
─────────────── ──────────────────── ────────
DEPARTMENTS DATAPLEXA_HR_TS 0
EMPLOYEES DATAPLEXA_HR_TS 0
JOBS DATAPLEXA_HR_TS 0
ORDER_ITEMS DATAPLEXA_STORE_TS 0
ORDERS DATAPLEXA_STORE_TS 0
CUSTOMERS DATAPLEXA_STORE_TS 0
PAYMENTS DATAPLEXA_STORE_TS 0
PAYROLL_HISTORY DATAPLEXA_HR_TS 0
PRODUCTS DATAPLEXA_STORE_TS 0
SALARIES DATAPLEXA_HR_TS 0

-- user_tab_columns for EMPLOYEES:
COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH PRECISION SCALE NULLABLE DATA_DEFAULT
───────── ───────────── ───────── ─────────── ───────── ───── ──────── ────────────
1 EMPLOYEE_ID NUMBER 22 6 0 N (null)
2 FIRST_NAME VARCHAR2 50 (null) (null) N (null)
3 LAST_NAME VARCHAR2 50 (null) (null) N (null)
4 EMAIL VARCHAR2 150 (null) (null) N (null)
5 HIRE_DATE DATE 7 (null) (null) N (null)
6 JOB_ID VARCHAR2 20 (null) (null) N (null)
7 DEPARTMENT_ID NUMBER 22 6 0 Y (null)
8 MANAGER_ID NUMBER 22 6 0 Y (null)

-- Column counts:
TABLE_NAME COLUMN_COUNT
─────────────── ────────────
CUSTOMERS 8
DEPARTMENTS 4
EMPLOYEES 8
JOBS 4
ORDER_ITEMS 5
ORDERS 5
PAYMENTS 5
PAYROLL_HISTORY 6
PRODUCTS 8
SALARIES 5

nullable = 'Y' for DEPARTMENT_ID and MANAGER_ID confirms the NULL decisions made at design time — a quick way to audit that nullability was applied correctly
data_default shows (null) for columns with no default — this means no default expression was defined, not that the column itself allows NULL
Use user_tab_columns inside scripts rather than DESCRIBE — DESCRIBE is an interactive tool that cannot be embedded in SQL or PL/SQL

Summary

Concept	What It Does	Key Point
CREATE TABLE ... TABLESPACE	Creates a table in a named tablespace	Always specify TABLESPACE in production
Column order	Defines left-to-right order of columns in every row	Permanent — cannot be changed after creation
Right-sizing columns	Matching type and size to the real data	Oversizing allows invalid data — sizing is documentation
NOT NULL	Enforces a column must always have a value	Raises ORA-01400 on violation
DEFAULT SYSDATE	Auto-fills current date and time on insert	Standard pattern for created_date audit columns
CHAR(1) for flags	Fixed single-character column for Y/N values	One of the few correct uses of CHAR over VARCHAR2
user_tables	Lists all tables in the current schema	num_rows reflects last stats gather — not a live count
user_tab_columns	Lists every column with full type and constraint detail	Use in scripts — DESCRIBE cannot be embedded in SQL

Practice Questions

Practice 1. Why is column order in a CREATE TABLE statement considered a permanent design decision?

Practice 2. The order_items table stores unit_price separately from products. Why is this intentional and not a mistake?

Practice 3. Why is hire_date defined as NOT NULL in the employees table?

Practice 4. You need to add a notes column to the customers table for free-form text up to 2000 characters. Write the ALTER TABLE statement.

Practice 5. What is the difference between user_tables and user_tab_columns, and when would you use each?

Quiz

Quiz 1. Which clause in CREATE TABLE directs Oracle to store the table in a specific tablespace?

TABLESPACE
STORAGE
FILEGROUP
PARTITION

Quiz 2. A salary column stores values like 9500.00 and 10500.00. Which data type is most appropriate?

NUMBER(10,2)
NUMBER(6)
VARCHAR2(10)
INTEGER

Quiz 3. Which data dictionary view gives the data type and nullability of every column in a table?

user_tab_columns
user_tables
user_constraints
dba_objects

Quiz 4. Why does manager_id in the employees table allow NULL?

The most senior employee has no manager — NULL correctly represents the top of the hierarchy
Manager data is not always known at insert time
Oracle requires at least one nullable column per table
It improves query performance

Quiz 5. You query user_tables after inserting rows and see num_rows = 0. What is the most likely reason?

num_rows reflects the last statistics gather — not the current live row count
The INSERT statements were not committed
user_tables only shows tables with primary keys
num_rows is always 0 for tables in non-default tablespaces

Next up — Constraints & Keys — Add primary keys, foreign keys, unique constraints, and check constraints to enforce data integrity across both schemas at the database level.

← Previous Course Index Next →

Oracle Database