Create Table in SQL Explained with Syntax, Data Types, and Real-World Examples

When you work with a database, data cannot exist on its own. You first need a table. That is why learning how to create table in SQL is one of the first things every beginner learns.

A table stores data in rows and columns. Each column has a fixed type of data, and each row stores one complete record.

Let’s understand this slowly with real examples.

What Does Create Table in SQL Mean?

Creating a table in SQL means you are telling the database:

• What kind of data you want to store.
• How that data should be organized.
• What rules should the data follow.

Think of a table like a form. First, you design the form, then people fill it.

Basic CREATE TABLE Syntax

CREATE TABLE table_name (

    column_name data_type

);

We write:

1. Table name
2. Column names
3. Data type for each column

That’s all SQL needs to create an empty table.

Example 1 – Creating a Simple Users Table

Let’s say you are building a website and want to store users.

CREATE TABLE users (

    id INT,

    name VARCHAR(50),

    email VARCHAR(100)

);

➤ Table Structure (How it will look)

Right now, the table is empty. But the structure is ready.

Understanding Data Types Using This Example

Let’s break it down:

• INT → stores numbers (used for ID)
• VARCHAR(50) → stores text up to 50 characters
• VARCHAR(100) → stores longer text like email

We use different data types so SQL knows how to store and validate data.

Example 2 – Adding a Date Column

Now, suppose you also want to store the date when the user joined.

CREATE TABLE users (

    id INT,

    name VARCHAR(50),

    email VARCHAR(100),

    join_date DATE

);

➤ Updated Table Structure will look Like This

Here:

• DATE stores only date (year, month, day)

What are Constraints, and When Do We Need Constraints?

Constraints are rules in SQL that control what kind of data can be stored in a table. Constraints are not mandatory everywhere. We use them only when rules are required.

Let’s add them one by one, where they actually make sense.

Example 3 – Using PRIMARY KEY (Very Important)

Every table should have a unique identifier.

CREATE TABLE users (

    id INT PRIMARY KEY,

    name VARCHAR(50),

    email VARCHAR(100),

    join_date DATE

);

Why is it needed?

• Every user must be unique.
• No duplicate IDs allowed.

Example 4 – Using NOT NULL (Only Where Required)

A user without a name or email is useless. So we enforce this rule.

CREATE TABLE users (

    id INT PRIMARY KEY,

    name VARCHAR(50) NOT NULL,

    email VARCHAR(100) NOT NULL,

    join_date DATE

);

Now:

• Name cannot be empty.
• Email cannot be empty.

This improves data quality.

Example 5 – Using UNIQUE for Email

Two users should not have the same email.

CREATE TABLE users (

    id INT PRIMARY KEY,

    name VARCHAR(50) NOT NULL,

    email VARCHAR(100) NOT NULL UNIQUE,

    join_date DATE

);

This ensures:

• No duplicate email entries.
• Cleaner and safer data.

Final Users Table (Complete & Clean)

➤ SQL Code

CREATE TABLE users (

    id INT PRIMARY KEY,

    name VARCHAR(50) NOT NULL,

    email VARCHAR(100) NOT NULL UNIQUE,

    join_date DATE

);

➤ Final Table Structure will Look Like This

Example 6 – Auto Increment ID (Real Project Use)

Manually entering IDs is risky. So we let SQL handle it.

CREATE TABLE users (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(50) NOT NULL,

    email VARCHAR(100) NOT NULL UNIQUE,

    join_date DATE

);

Now:

• SQL automatically generates IDs.
• No manual mistakes.

Real-World Example – Orders Table

Now let’s create an orders table.

CREATE TABLE orders (

    order_id INT PRIMARY KEY,

    user_id INT,

    order_date DATE,

    amount DECIMAL(8,2)

);

➤ Orders Table Structure

This is how real applications store order data.

Checking the Table After Creation

To see table structure:

DESCRIBE users;

This command shows:

• Column names
• Data types
• Constraints

Final Thoughts

Learning how to create a table in SQL is not about memorizing syntax. It is about understanding your data. When you plan your table properly, everything else becomes easier – inserts, updates, queries, and reports.

Start simple. Use real examples. Improve your table design as your project grows. That’s how real developers work.

Read Also –

1. UPDATE Query in SQL
2. Delete Query in SQL