CREATE TABLE in SQL

CREATE TABLE defines a new table — its columns, their data types, and the rules (constraints) each column must follow.

Syntax

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

Example

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

This creates an empty table — no rows yet, just structure. You'd add data with INSERT.

Auto-Incrementing Primary Keys — Dialect Differences

Avoiding Errors on Re-Run

CREATE TABLE IF NOT EXISTS employees ( ... );

Practical Use Case

This is the first statement in almost every real project setup — defining the schema before any application code touches the database. Getting data types and constraints right here saves painful migrations later.

Common Mistakes

• Forgetting a PRIMARY KEY — every table should have one to uniquely identify rows
• Making every column VARCHAR(255) without thinking about the actual data (see Data Types)
• Not adding NOT NULL on columns that should never be empty (e.g. an order's customer_id)

Interview Relevance

You'll frequently be asked to design a table from a description ("design a table to store orders for an e-commerce store") — practice reading requirements and mapping them to columns, types, and constraints.

Practice Question

Design a students table with: an auto-incrementing ID, a required name, an email that must be unique, and an enrollment date that defaults to today.

Related SQL Notes

• ALTER TABLE
• Constraints
• Primary Key