The CREATE TABLE statement defines a brand-new table — its name, its columns, each column's data type, and any constraints those columns should enforce. It's the statement that lays the foundation everything else in this course builds on, including the employees table used in most of the other examples.

Basic Syntax

CREATE TABLE employees (
    id          INT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    department  VARCHAR(50),
    salary      DECIMAL(10, 2),
    hire_date   DATE
);

Result:

Query OK, table 'employees' created

This is the exact table referenced throughout this SQL course. Reading it column by column: id is an integer and the table's primary key, name is a required (NOT NULL) text field capped at 100 characters, department and salary are optional, and hire_date stores a calendar date.

Common Data Types

Every column needs a type. The names vary slightly between databases, but the categories are consistent:

INT / INTEGER       -- whole numbers
DECIMAL(p, s)        -- exact decimal numbers (p = total digits, s = after the point)
VARCHAR(n)           -- variable-length text, up to n characters
TEXT                 -- long, unbounded text
DATE / DATETIME      -- calendar dates, with or without a time
BOOLEAN              -- true/false

DECIMAL is worth calling out specifically for anything involving money: unlike FLOAT/DOUBLE, it stores exact values with no rounding error, which is exactly what you want for a salary column.

Adding Table-Level Constraints

Constraints can also be declared after the column list, which is required for anything spanning multiple columns, like a composite key or a foreign key reference:

CREATE TABLE orders (
    id            INT PRIMARY KEY,
    employee_id   INT,
    order_total   DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

Once a table exists, you're not locked into its original structure — the ALTER TABLE statement lets you add, remove, or change columns on a table that already has data in it.