How to Create a Table in SQL? | Simple Example (2025 Guide)

Introduction

Creating a table is the first step in structuring a relational database. A table organizes data in rows and columns, allowing efficient storage and retrieval.

If you’re a beginner or an experienced SQL user, understanding how to create tables correctly is essential for database performance and scalability.

In This Guide, You’ll Learn:

✔️ What is an SQL Table?
✔️ How to Create a Table in SQL (With Examples)
✔️ SQL Data Types & Constraints
✔️ Best Practices for Table Creation

By the end of this article, you’ll be able to create optimized, structured, and performance-efficient tables in SQL.

1. What Is an SQL Table?

An SQL Table is a structured collection of related data stored in a relational database.
Each table consists of:
🔹 Columns (Fields) – Defines the type of data stored.
🔹 Rows (Records) – Stores actual data entries.

Example:

A Customers table may contain:

Customer_ID (Unique ID)
Name (Full Name)
Email (Email Address)
City (Customer’s City)

2. How to Create a Table in SQL? (Basic Example)

The CREATE TABLE statement is used to define a new table in SQL.

Basic Syntax:

sqlCopyEditCREATE TABLE table_name (  
    column1 datatype constraints,  
    column2 datatype constraints,  
    column3 datatype constraints,  
    ...
);

Example: Creating a “Customers” Table

sqlCopyEditCREATE TABLE Customers (  
    Customer_ID INT PRIMARY KEY,  
    Name VARCHAR(100) NOT NULL,  
    Email VARCHAR(255) UNIQUE,  
    City VARCHAR(100)  
);

Explanation:

✔️ Customer_ID → Primary Key (Unique ID for each customer)
✔️ Name → Cannot be NULL (Must contain a value)
✔️ Email → Unique Constraint (No duplicate emails allowed)
✔️ City → Regular column

3. SQL Data Types & Constraints (Must-Know Basics)

Common SQL Data Types:

Data Type	Description	Example
`INT`	Integer values	`101, 202`
`VARCHAR(n)`	Text (up to `n` characters)	`"John Doe"`
`DECIMAL(p,s)`	Decimal values	`10.50`
`DATE`	Stores date values	`2025-02-23`
`BOOLEAN`	True/False values	`TRUE / FALSE`

Common SQL Constraints:

Constraint	Description
`PRIMARY KEY`	Uniquely identifies each row
`NOT NULL`	Ensures the column cannot be empty
`UNIQUE`	Ensures all values in the column are distinct
`DEFAULT`	Assigns a default value if none is provided
`FOREIGN KEY`	Links two tables together

4. Creating a Table with Foreign Key (Advanced Example)

Scenario:

We want to create an Orders table linked to the Customers table using a foreign key.

SQL Query:

sqlCopyEditCREATE TABLE Orders (  
    Order_ID INT PRIMARY KEY,  
    Customer_ID INT,  
    Order_Date DATE NOT NULL,  
    Amount DECIMAL(10,2),  
    FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID)  
);

How It Works:

✔️ Order_ID → Unique order identifier (Primary Key).
✔️ Customer_ID → Links each order to a customer (Foreign Key).
✔️ Order_Date → Cannot be NULL (Every order must have a date).
✔️ Amount → Stores the order amount.

5. Inserting Data into the Table (Step-by-Step Example)

Once the table is created, you can insert data using the INSERT INTO statement.

Example: Inserting Data into “Customers” Table

sqlCopyEditINSERT INTO Customers (Customer_ID, Name, Email, City)  
VALUES (101, 'John Doe', '[email protected]', 'New York');

Example: Inserting Data into “Orders” Table

sqlCopyEditINSERT INTO Orders (Order_ID, Customer_ID, Order_Date, Amount)  
VALUES (1, 101, '2025-02-23', 250.00);

6. Viewing Table Data (Retrieving Records)

To retrieve records from a table, use the SELECT statement.

Example:

sqlCopyEditSELECT * FROM Customers;

Example: Get all orders for a specific customer.

sqlCopyEditSELECT Orders.Order_ID, Customers.Name, Orders.Amount  
FROM Orders  
JOIN Customers  
ON Orders.Customer_ID = Customers.Customer_ID  
WHERE Customers.Name = 'John Doe';

7. Altering a Table (Modify an Existing Table)

Adding a New Column:

sqlCopyEditALTER TABLE Customers ADD Phone VARCHAR(15);

Modifying a Column:

sqlCopyEditALTER TABLE Customers MODIFY Name VARCHAR(150);

Deleting a Column:

sqlCopyEditALTER TABLE Customers DROP COLUMN Phone;

8. Deleting a Table (Be Careful!)

Delete All Data But Keep Table Structure:

sqlCopyEditTRUNCATE TABLE Customers;

Delete Table (Permanently Remove It):

sqlCopyEditDROP TABLE Customers;

9. Best Practices for Creating Tables in SQL

✅ Use Proper Data Types – Choose data types that best fit the data.
✅ Always Define Primary Keys – Helps maintain unique records.
✅ Use Foreign Keys for Relationships – Ensures referential integrity.
✅ Apply Constraints – Prevents invalid data from being inserted.
✅ Optimize Indexes – Speed up searches for large tables.

Conclusion

Creating tables in SQL is fundamental to database design. Here’s a quick recap of what we covered:

✅ Basic CREATE TABLE syntax
✅ Common data types & constraints
✅ Creating tables with foreign keys
✅ Inserting, updating, and deleting records
✅ Best practices for database efficiency

🔹 Want to learn more about SQL best practices? Join our SQL Community Forum for discussions and expert insights!

🔹 Further Reading: Check out this SQL Table Design Guide by W3Schools.