Tables, Rows & Columns

A table is the fundamental unit of storage in Oracle. Every piece of data you insert, query, update, or delete lives in a table. Understanding how tables are structured, how Oracle organises rows and columns physically, and how to create well-defined tables is the foundation of everything else in this course. This lesson creates the first tables of the DataplexaStore and DataplexaHR schemas that all subsequent lessons build on.

Tables

A table is a two-dimensional structure that stores data in a fixed, organised format. Think of it like a spreadsheet grid — but with strict rules about what each column can hold, enforced by the database engine rather than left to the person entering data. Every piece of data in Oracle lives in a table — there is no other permanent storage structure for rows of data.

In Oracle, a table belongs to a schema. A schema is a named container owned by a database user — it groups all the objects that user creates. When you connect as the user dataplexa and create a table called products, the fully qualified name is dataplexa.products. Within your own session you refer to it simply as products.

Every table in Oracle has three fundamental properties:

• A name — unique within the schema, stored in uppercase internally
• A fixed set of columns — defined at creation time; every row must conform to this structure
• Zero or more rows — a table with no rows is valid; it simply has structure but no data yet

-- user_tables lists every table in the currently connected user's schema
-- num_rows is not a live count — it reflects the last time statistics were gathered;
-- use SELECT COUNT(*) FROM table_name for the real current count
SELECT table_name,
       num_rows,
       last_analyzed,
       tablespace_name
FROM   user_tables
ORDER BY table_name;

• user_tables shows only the tables owned by the currently connected user — use all_tables to see tables you have been granted access to across all schemas, or dba_tables for every table in the entire database (requires DBA privilege)
• Oracle stores all object names in uppercase internally — CREATE TABLE products is stored as PRODUCTS; this is why user_tables always shows uppercase names regardless of how you typed them
• num_rows in user_tables is not a live count — it reflects the last time statistics were gathered with DBMS_STATS; use SELECT COUNT(*) FROM table_name for the real current count

Columns

A column defines one attribute of the entity a table represents. It has three things: a name, a data type, and a nullability rule. The name identifies the attribute. The data type enforces what kind of value can be stored — text, a number, a date. The nullability rule controls whether the column must always have a value (NOT NULL) or whether it can be left empty (NULL).

Choosing columns carefully at design time matters. A column that is too wide wastes storage. A column that is too narrow causes data to be truncated or rejected. A column that allows NULL when it should not allows incomplete records into the database silently.

Every column in Oracle has these characteristics:

• Name — up to 128 characters, must start with a letter, can contain letters, numbers, and underscores; convention is lowercase with underscores — unit_price, hire_date, customer_id
• Data type — defines the kind and size of value allowed: VARCHAR2(n) for variable-length text, NUMBER(p,s) for numeric values, DATE for date and time. Full coverage is in Lesson 9.
• NULL or NOT NULL — NOT NULL means the column must always have a value; NULL (the default) means the column is optional and can be left empty
• DEFAULT — an optional value Oracle uses automatically when no value is supplied during INSERT

-- DESCRIBE is a SQL Developer / SQL*Plus command, not a SQL statement
-- It prints column names, data types, and nullability in a readable format
DESCRIBE products;

-- user_tab_columns gives the same information but as queryable rows
-- Useful inside scripts where DESCRIBE cannot be embedded
-- nullable: 'N' = NOT NULL enforced, 'Y' = NULL allowed
-- data_default: shows the DEFAULT expression defined on the column e.g. SYSDATE or 0
-- table_name must be uppercase when filtering — Oracle stores all names in uppercase
SELECT column_name,
       data_type,
       data_length,
       data_precision,   -- total significant digits — applies to NUMBER columns only
       data_scale,       -- decimal places — e.g. 2 for NUMBER(10,2) money columns
       nullable,
       data_default
FROM   user_tab_columns
WHERE  table_name = 'PRODUCTS'
ORDER BY column_id;       -- column_id preserves the order columns were defined in

• DESCRIBE (or DESC) is an Oracle SQL*Plus and SQL Developer command — it is not a SQL statement and cannot be embedded inside a query; use user_tab_columns when you need column details inside a script
• nullable = 'N' in user_tab_columns means NOT NULL — the column must always have a value; nullable = 'Y' means the column accepts NULL
• data_default shows the DEFAULT expression assigned to the column — SYSDATE for created_date and 0 for stock_qty match exactly what was defined in the CREATE TABLE statement

Rows

A row is one complete record — one instance of the entity the table describes. Every row in a table has exactly the same columns as every other row. A row in the customers table represents one customer. A row in the order_items table represents one line on one order. A row in the employees table represents one person employed by the organisation.

Oracle stores rows in data blocks — fixed-size pages of storage, typically 8 KB each, held in data files on disk and cached in the SGA buffer cache in memory. When a row is too wide to fit in a single block, Oracle chains it across multiple blocks. When multiple small rows fit in one block, Oracle packs them together. The application sees only logical rows and columns — the physical block management is handled entirely by Oracle.

Key things to understand about rows in Oracle:

• A row has no guaranteed physical order — Oracle does not store rows in any particular sequence unless you use an Index-Organised Table (IOT). A SELECT without an ORDER BY clause can return rows in any order and that order may change between executions
• Each row has a unique physical address called a ROWID — it encodes the data file number, block number, and row position within the block. ROWID is the fastest possible way to access a single row and is used internally by Oracle indexes.
• NULL in a row means the value is unknown or not applicable — it is not zero, not an empty string, and not a space. NULL is the absence of a value. A phone column that is NULL means the phone number is not known — it does not mean the customer has no phone.

-- ROWID is Oracle's internal physical address for every row
-- It encodes the data file, block number, and row slot — used internally by indexes
-- Never store ROWID in application tables — it changes if a row is moved
SELECT ROWID,
       customer_id,
       full_name,
       country
FROM   customers;

-- IS NULL is the only correct way to test for a missing value
-- WHERE phone = NULL never matches anything — NULL is not equal to anything, including itself
SELECT customer_id,
       full_name,
       phone
FROM   customers
WHERE  phone IS NULL;

-- COUNT(*) counts all rows; COUNT(column) skips NULLs in that column
-- The difference reveals exactly how many rows are missing a phone number
SELECT COUNT(*)                AS total_rows,
       COUNT(phone)            AS rows_with_phone,
       COUNT(*) - COUNT(phone) AS rows_without_phone
FROM   customers;

• ROWID should never be stored in application tables or used as a join condition — it changes when a row is moved (for example during a table reorganisation or partition move), making stored ROWIDs unreliable
• COUNT(*) counts all rows including those with NULL values in every column; COUNT(column_name) counts only rows where that column is NOT NULL — this is why COUNT(*) and COUNT(phone) produce different results when some phone values are NULL
• Oracle treats NULL as not equal to anything — including another NULL. WHERE phone = NULL never returns rows; the correct syntax is always WHERE phone IS NULL

Creating the DataplexaStore Tables

The DataplexaStore schema models an e-commerce platform. It has five tables: Customers, Products, Orders, OrderItems, and Payments. Each table is created with CREATE TABLE, specifying column names, data types, and inline constraints. Data types are covered fully in Lesson 9 — for now, NUMBER, VARCHAR2, and DATE are the three types used here.

-- CUSTOMERS — one row per registered customer
CREATE TABLE customers (
    customer_id    NUMBER(10)      NOT NULL,
    full_name      VARCHAR2(100)   NOT NULL,
    email          VARCHAR2(150)   NOT NULL,
    phone          VARCHAR2(20),              -- optional — not all customers provide a phone number
    country        VARCHAR2(60)    NOT NULL,
    city           VARCHAR2(60),              -- optional — city is not always provided at registration
    created_date   DATE            DEFAULT SYSDATE NOT NULL  -- auto-filled with current date on insert
);

-- PRODUCTS — one row per product available for sale
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,  -- 2 decimal places for currency values e.g. 49.99
    stock_qty      NUMBER(8)       DEFAULT 0 NOT NULL,  -- new products start at 0 stock
    created_date   DATE            DEFAULT SYSDATE NOT NULL
);

-- ORDERS — one row per order placed by a customer
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,  -- all new orders begin as pending
    total_amount   NUMBER(12,2)    DEFAULT 0 NOT NULL           -- recalculated as items are added
);

-- ORDER_ITEMS — one row per product line on an order
-- An order containing 3 different products produces 3 rows in this table
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 captured at order time — independent of product price changes
);

-- PAYMENTS — one row per payment made against an order
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  -- e.g. credit_card, paypal, bank_transfer
);

• NOT NULL is an inline constraint that prevents empty values — any attempt to insert a row without providing a value for a NOT NULL column will fail with ORA-01400
• DEFAULT SYSDATE means Oracle automatically fills in the current date and time if the caller does not supply a value — this is how audit columns like created_date are typically managed without requiring the application to pass a date
• Column names use lowercase with underscores — this is a widely adopted Oracle naming convention that produces readable SQL; Oracle stores them as uppercase internally but you can type them in any case in your queries

Creating the DataplexaHR Tables

The DataplexaHR schema models a human resources system. It has five tables: Departments, Jobs, Employees, Salaries, and PayrollHistory. These tables are referenced throughout the HR-focused examples in this course.

-- DEPARTMENTS — one row per department in the organisation
CREATE TABLE departments (
    department_id   NUMBER(6)       NOT NULL,
    department_name VARCHAR2(100)   NOT NULL,
    location        VARCHAR2(100),   -- optional — some departments may be fully remote
    manager_id      NUMBER(6)        -- NULL allowed — a new department may not yet have a manager
);

-- JOBS — one row per job role, holding the salary band for that grade
CREATE TABLE jobs (
    job_id          VARCHAR2(20)    NOT NULL,  -- human-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
);

-- EMPLOYEES — one row per person employed by the organisation
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 — contractors or new starters may not yet be assigned
    manager_id      NUMBER(6)    -- NULL allowed — the most senior executive has no manager
);

-- SALARIES — one row per current salary record per employee
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  -- monthly is the standard pay cycle
);

-- PAYROLL_HISTORY — one row per pay run per employee, grows every pay period
CREATE TABLE payroll_history (
    payroll_id      NUMBER(10)      NOT NULL,
    employee_id     NUMBER(6)       NOT NULL,
    pay_period      DATE            NOT NULL,   -- last day of the pay period e.g. 31-JAN-2024
    gross_pay       NUMBER(10,2)    NOT NULL,
    tax_deducted    NUMBER(10,2)    DEFAULT 0 NOT NULL,  -- defaults to 0 if not yet calculated
    net_pay         NUMBER(10,2)    NOT NULL
);

• The manager_id column in both departments and employees is nullable — not every department has a designated manager yet, and not every employee has a manager (the CEO has no manager); allowing NULL here is intentional and correct
• job_id in the jobs table is a VARCHAR2 rather than a NUMBER — human-readable job codes such as IT_PROG or HR_MGR are more practical as identifiers in an HR system than arbitrary integers
• Primary key and foreign key constraints are not added here — they are the subject of Lesson 11 (Constraints & Keys); the tables are structurally complete but not yet fully constrained

Modifying and Dropping Tables

After a table is created you can modify its structure using ALTER TABLE. You can add new columns, modify existing column definitions, or rename columns. Dropping a table with DROP TABLE removes it permanently along with all its data.

-- ADD a new column — DEFAULT is required because the table already has rows
-- Oracle needs a value to back-fill this column for all existing customers
ALTER TABLE customers
ADD (loyalty_tier VARCHAR2(20) DEFAULT 'standard' NOT NULL);

-- ADD multiple columns in a single statement
ALTER TABLE products
ADD (
    description    VARCHAR2(500),                        -- optional — NULL allowed
    is_active      VARCHAR2(1)   DEFAULT 'Y' NOT NULL    -- Y = on sale, N = delisted
);

-- MODIFY increases the column size — safe with existing data
-- Decreasing size is only allowed if every existing value already fits within the smaller length
ALTER TABLE customers
MODIFY (phone VARCHAR2(30));

-- RENAME COLUMN — changes the name only, data and data type are unaffected
ALTER TABLE products
RENAME COLUMN is_active TO active_flag;

-- RENAME TO renames the entire table — data, constraints, and indexes all move with it
ALTER TABLE order_items
RENAME TO order_lines;

ALTER TABLE order_lines
RENAME TO order_items;

-- DROP TABLE moves the table to Oracle's Recycle Bin — recoverable with FLASHBACK
DROP TABLE payments;

-- DROP TABLE ... PURGE bypasses the Recycle Bin — permanently deleted, no recovery possible
DROP TABLE payments PURGE;

• Oracle's Recycle Bin means a dropped table is not immediately gone — it can be recovered using FLASHBACK TABLE payments TO BEFORE DROP as long as it has not been purged; this has saved many developers and DBAs from accidental data loss
• You can only increase the size of a VARCHAR2 column with MODIFY — decreasing it is only possible if every existing value already fits in the smaller size, otherwise Oracle raises ORA-01441
• ALTER TABLE ... ADD with NOT NULL requires a DEFAULT value when the table already contains rows — without a default, Oracle cannot populate the new column for existing rows and will raise ORA-01758

Summary

Practice Questions

Quiz

Next up — Oracle Data Types — Learn every data type Oracle provides for storing text, numbers, dates, and large objects, and understand which type to choose for each column in your schema.