Create Table in SQL

Tables are the most important objects in a relational database. A database stores data in the form of rows and columns, and these rows and columns are organized inside tables. Before inserting any data into a database, we must first create a table structure that defines what type of information will be stored.

The SQL CREATE TABLE statement is used to create new tables inside a database. During table creation, we define column names, data types, constraints, keys, and other rules that control how data will be stored and managed.

Whether you are developing a school management system, hospital database, banking application, e-commerce platform, or inventory management software, understanding table creation is essential because every database begins with properly designed tables.

What is a Table in SQL?

A table is a collection of related data organized into rows and columns.

Rows represent records.
Columns represent attributes or fields.

For example, a Student table may contain the following columns:

StudentID	Name	Course	Age
101	Rahul	B.Tech	20
102	Priya	BCA	19

In this example, StudentID, Name, Course, and Age are columns, while each student entry represents a row.

Why Create Tables?

Tables provide a structured way to store and organize data.

Store information systematically.
Improve data management.
Reduce redundancy.
Support data retrieval and updates.
Enable relationships between entities.
Maintain consistency and integrity.

CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table inside an existing database.

Basic Syntax

CREATE TABLE TableName (
Column1 DataType,
Column2 DataType,
Column3 DataType
);

Example

CREATE TABLE Student (
StudentID INT,
StudentName VARCHAR(100),
Course VARCHAR(50)
);

This statement creates a Student table containing three columns.

Understanding Table Components

Component	Description
Table Name	Name assigned to the table
Column Name	Name of a field in the table
Data Type	Type of data stored in a column
Constraints	Rules applied to data

Choosing Appropriate Data Types

Each column must be assigned a suitable data type.

Data Type	Purpose
INT	Whole numbers
VARCHAR	Variable length text
CHAR	Fixed length text
DATE	Date values
DECIMAL	Financial values
BOOLEAN	True or False values

Creating a Student Table

CREATE TABLE Student (
StudentID INT,
StudentName VARCHAR(100),
Age INT,
Course VARCHAR(50)
);

This table stores basic student information.

Adding Constraints During Table Creation

Constraints are rules that ensure data accuracy and integrity.

NOT NULL Constraint

CREATE TABLE Student (
StudentID INT,
StudentName VARCHAR(100) NOT NULL
);

StudentName cannot be left empty.

UNIQUE Constraint

CREATE TABLE Student (
Email VARCHAR(100) UNIQUE
);

Duplicate email addresses are not allowed.

Using PRIMARY KEY

A Primary Key uniquely identifies each record in a table.

CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100)
);

Benefits

Unique identification.
No duplicate values.
No NULL values.
Faster indexing.

Using FOREIGN KEY

Foreign Keys establish relationships between tables.

CREATE TABLE Department (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);

CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100),
DepartmentID INT,
FOREIGN KEY (DepartmentID)
REFERENCES Department(DepartmentID)
);

The DepartmentID in Employee references the Department table.

Using CHECK Constraint

CHECK validates data before insertion.

CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Age INT CHECK (Age >= 18)
);

Only students aged 18 or older can be inserted.

Using DEFAULT Constraint

CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
Status VARCHAR(20) DEFAULT 'Active'
);

If no value is supplied, Active becomes the default status.

Creating Tables with Multiple Constraints

CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
Age INT CHECK (Age >= 18),
Course VARCHAR(50) DEFAULT 'B.Tech'
);

This example combines several constraints in a single table.

Composite Primary Key

A composite key consists of multiple columns.

CREATE TABLE Enrollment (
StudentID INT,
CourseID INT,
PRIMARY KEY (StudentID, CourseID)
);

The combination uniquely identifies each enrollment record.

Creating Tables for Real-World Applications

Library Management System

CREATE TABLE Books (
BookID INT PRIMARY KEY,
BookTitle VARCHAR(150),
Author VARCHAR(100),
Price DECIMAL(8,2)
);

Hospital Management System

CREATE TABLE Patient (
PatientID INT PRIMARY KEY,
PatientName VARCHAR(100),
Gender CHAR(1),
AdmissionDate DATE
);

E-Commerce System

CREATE TABLE Product (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Price DECIMAL(10,2),
Stock INT
);

Best Practices for Creating Tables

Use meaningful table names.
Choose appropriate data types.
Define primary keys.
Avoid unnecessary columns.
Use constraints wherever possible.
Keep naming conventions consistent.
Normalize data properly.
Document table structures.

Common Naming Conventions

Good Name	Poor Name
Student	Tbl1
Employee	Data123
Department	TestTable

Common Mistakes to Avoid

Using incorrect data types.
Ignoring primary keys.
Allowing unnecessary NULL values.
Using long and confusing names.
Not defining relationships.
Creating duplicate columns.
Skipping validation constraints.

Advantages of Proper Table Design

Improves performance.
Maintains data integrity.
Supports scalability.
Reduces redundancy.
Simplifies maintenance.
Improves query efficiency.
Enhances security.

Interview Questions on CREATE TABLE

1. What is the purpose of CREATE TABLE?

CREATE TABLE is used to create a new table inside a database where data can be stored in rows and columns.

2. What is the syntax of CREATE TABLE?

CREATE TABLE TableName (
Column1 DataType,
Column2 DataType
);

3. Why is a Primary Key important?

It uniquely identifies each record and prevents duplicate entries.

4. Can a table have multiple Primary Keys?

No. A table can have only one Primary Key, although it may consist of multiple columns.

5. What is a Composite Key?

A Composite Key is a Primary Key made up of two or more columns.

6. What is the difference between VARCHAR and CHAR?

VARCHAR stores variable-length strings, while CHAR stores fixed-length strings.

7. What is a Foreign Key?

A Foreign Key creates a relationship between two tables and references a Primary Key.

8. What is the role of constraints?

Constraints enforce rules on data and maintain integrity.

9. What happens if a NOT NULL column receives no value?

The database generates an error because the column requires a value.

10. Why should meaningful table names be used?

Meaningful names improve readability, maintenance, and collaboration among developers.

Complete Practical Example

CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
Age INT CHECK (Age >= 18),
Course VARCHAR(50),
AdmissionDate DATE,
Status VARCHAR(20) DEFAULT 'Active'
);

This example demonstrates a professionally designed table using data types, keys, and constraints together.

Summary

The CREATE TABLE statement is one of the most frequently used SQL commands because every database application relies on properly designed tables. Tables define how information is stored, validated, and organized inside a database.

By understanding table creation, data types, constraints, keys, naming conventions, and best practices, developers can build reliable, efficient, and scalable database systems. A strong understanding of CREATE TABLE forms the foundation for advanced SQL concepts such as relationships, indexing, normalization, stored procedures, and database optimization.

← Previous: Create Database Next: ALTER Table →