How to Create a Table in SQL? | Simple Example

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_IDPrimary Key (Unique ID for each customer)
✔️ Name → Cannot be NULL (Must contain a value)
✔️ EmailUnique Constraint (No duplicate emails allowed)
✔️ City → Regular column


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

Common SQL Data Types:

Data TypeDescriptionExample
INTInteger values101, 202
VARCHAR(n)Text (up to n characters)"John Doe"
DECIMAL(p,s)Decimal values10.50
DATEStores date values2025-02-23
BOOLEANTrue/False valuesTRUE / FALSE

Common SQL Constraints:

ConstraintDescription
PRIMARY KEYUniquely identifies each row
NOT NULLEnsures the column cannot be empty
UNIQUEEnsures all values in the column are distinct
DEFAULTAssigns a default value if none is provided
FOREIGN KEYLinks 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.