SQL

CREATE TABLE in SQL

🏗️ The CREATE TABLE statement is a Data Definition Language (DDL) command used to create a new table in a database. A table stores data in the form of rows (records) and columns (fields), where each column has a specific data type and optional constraints.

📖 What is CREATE TABLE?

Before storing any data, you must create a table that defines how the data will be organized. The CREATE TABLE statement specifies the table name, column names, data types, and constraints such as PRIMARY KEY, NOT NULL, and UNIQUE.

Information

A table is one of the most important database objects. Every record inserted into a database is stored inside one or more tables.

🎯 Why Use CREATE TABLE?

Tables provide a structured way to organize and store related information.

📌 Store application data in a structured format.
📌 Define column names and data types.
📌 Enforce data integrity using constraints.
📌 Build relationships between tables.
📌 Create the foundation for SQL queries and reports.

📝 Basic Syntax

CREATE TABLE Syntax

CREATE TABLE table_name
(
    column1 data_type,
    column2 data_type,
    column3 data_type
);

💡 Create a Simple Table

Create a table named Students with four columns.

Create Students Table

CREATE TABLE Students
(
    StudentID INT,
    Name VARCHAR(100),
    Department VARCHAR(100),
    Age INT
);

The table is now ready to store student records.

🔑 Create a Table with Constraints

Constraints help enforce data integrity by restricting the type of data that can be stored.

Table with Constraints

CREATE TABLE Students
(
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Department VARCHAR(100),
    Age INT,
    Email VARCHAR(150) UNIQUE
);

Important

Constraints improve data quality by preventing invalid or duplicate values from being stored.

📊 Common SQL Data Types

Data Type	Description	Example
INT	Whole numbers.	101
VARCHAR(n)	Variable-length text.	Alice
CHAR(n)	Fixed-length text.	IN
DATE	Date values.	2026-07-02
DECIMAL(p,s)	Exact numeric values.	1250.50
BOOLEAN	True/False values (where supported).	TRUE

📋 Create a Table with Default Values

Default values are automatically assigned when no value is provided during an INSERT operation.

Default Values

CREATE TABLE Employees
(
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Department VARCHAR(50),
    Salary DECIMAL(10,2),
    Status VARCHAR(20) DEFAULT 'Active'
);

🔗 Create a Table with a Foreign Key

Foreign keys establish relationships between tables and help maintain referential integrity.

Foreign Key Example

CREATE TABLE Enrollments
(
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    FOREIGN KEY (StudentID)
        REFERENCES Students(StudentID)
);

📦 Create a Table from Another Table

Some database systems allow creating a new table directly from the result of a SELECT query.

CREATE TABLE AS SELECT

CREATE TABLE TopStudents AS
SELECT StudentID,
       Name,
       Department
FROM Students
WHERE Marks >= 90;

Tip

Support for CREATE TABLE AS SELECT varies between database systems. The exact syntax may differ depending on the DBMS.

⚖️ CREATE TABLE vs ALTER TABLE

Feature	CREATE TABLE	ALTER TABLE
Purpose	Create a new table.	Modify an existing table.
Add Columns	During creation.	After creation.
Define Constraints	Yes.	Yes.
Requires Existing Table	❌ No	✅ Yes

💼 Real-World Example

A university is developing a Student Management System. The first step is to create a table that stores student information.

Student Management Table

CREATE TABLE Students
(
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Department VARCHAR(100),
    Email VARCHAR(150) UNIQUE,
    AdmissionDate DATE
);

Once the table is created, student records can be inserted, updated, queried, and deleted as needed.

⚠️ Common Mistakes

❌ Choosing inappropriate data types.
❌ Forgetting to define a primary key.
❌ Using duplicate column names.
❌ Selecting column sizes that are too small or unnecessarily large.
❌ Omitting important constraints such as NOT NULL or UNIQUE.

Warning

Careful table design is essential. Changing a poorly designed table later can be more difficult than designing it correctly from the beginning.

⚠️ Best Practices

Best Practice

Use meaningful table and column names, choose appropriate data types, define a primary key for every table, apply constraints to maintain data integrity, avoid storing redundant information, and design tables with future scalability in mind.

🚀 Key Points to Remember

📌 CREATE TABLE creates a new database table.
📌 Every column must have a data type.
📌 Constraints help ensure data accuracy and consistency.
📌 Foreign keys establish relationships between tables.
📌 Table design has a significant impact on database performance and maintainability.
📌 Different SQL databases may support additional table creation options.

>>"A well-designed table is the foundation of every reliable and scalable database."

Summary

✅ The CREATE TABLE statement is one of the most fundamental SQL commands. It defines how data is stored, organized, and validated within a database. By choosing suitable data types, constraints, and relationships, you can build efficient, secure, and maintainable database applications.