CREATE TABLE in SQL

A table is a structure used to store related information in a database. It contains columns and rows.

Students

ID    Name       Age
1     Rahul      20
2     Priya      21
3     Amit       19

Here, ID, Name, and Age are columns and each student record is a row.

The basic syntax for creating a table is:

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

You must provide a table name, column names, and appropriate data types.

CREATE TABLE Students (
    id INT,
    name VARCHAR(100),
    age INT
);

This creates a table named Students with three columns: id, name, and age.

Use the USE statement to select a database before creating a table.

USE school;

After this statement, tables will be created inside the school database.

You can first create a database and then create a table inside it.

CREATE DATABASE school;

USE school;

CREATE TABLE Students (
    id INT,
    name VARCHAR(100),
    age INT
);

Column names describe the type of information stored in each column.

CREATE TABLE Students (
    student_id INT,
    student_name VARCHAR(100),
    mobile VARCHAR(15),
    age INT
);

Here, student_id, student_name, mobile, and age are column names.

The INT data type is commonly used for whole numbers.

CREATE TABLE Students (
    id INT,
    age INT
);

Examples of integer values are:

10
25
100
500

VARCHAR is used to store variable-length text.

CREATE TABLE Students (
    name VARCHAR(100)
);

The number inside the brackets specifies the maximum length.

The DATE data type is used to store dates.

CREATE TABLE Students (
    admission_date DATE
);

A date can be stored in the format:

2026-09-20

The DECIMAL data type is useful for values that require decimal precision, such as fees and prices.

CREATE TABLE Courses (
    course_name VARCHAR(100),
    fee DECIMAL(10,2)
);

For example:

5000.00
12500.50

A table can contain many columns.

CREATE TABLE Employees (
    id INT,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    joining_date DATE
);

A primary key uniquely identifies each record in a table.

CREATE TABLE Students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

The value of id should be unique for every student.

In MySQL, AUTO_INCREMENT can automatically generate a new numeric ID.

CREATE TABLE Students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    age INT
);

When a new record is inserted, the ID can be generated automatically.

NOT NULL means that a column cannot contain a NULL value.

CREATE TABLE Students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT
);

The student name must be provided when inserting a record.

The DEFAULT keyword provides a value automatically when no value is supplied.

CREATE TABLE Students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(50) DEFAULT 'Aurangabad'
);

The UNIQUE constraint ensures that values in a column are not duplicated.

CREATE TABLE Students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(150) UNIQUE
);

Here is a practical example of a student table:

CREATE TABLE Students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    mobile VARCHAR(15),
    age INT,
    course VARCHAR(100),
    fee DECIMAL(10,2),
    admission_date DATE
);

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    joining_date DATE
);

CREATE TABLE Courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    duration VARCHAR(50),
    fee DECIMAL(10,2)
);

After creating a table, you can use SHOW TABLES to see the tables inside the selected database.

SHOW TABLES;

The DESCRIBE statement can be used to view the structure of a table.

DESCRIBE Students;

You can also use:

DESC Students;

The IF NOT EXISTS option prevents an error if the table already exists.

CREATE TABLE IF NOT EXISTS Students (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

• Use meaningful table names.
• Avoid unnecessary spaces in names.
• Use consistent naming conventions.
• Do not use SQL reserved words as names.
• Keep names easy to understand.

Example:

Students
Student_Courses
Employee_Details

Every column definition should normally contain a column name and a data type.

Incorrect:

CREATE TABLE Students (
    id,
    name
);

Correct:

CREATE TABLE Students (
    id INT,
    name VARCHAR(100)
);

CREATE TABLE Students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    age INT,
    city VARCHAR(50) DEFAULT 'Aurangabad'
);

This example combines several concepts such as PRIMARY KEY, AUTO_INCREMENT, NOT NULL, UNIQUE, and DEFAULT.

In real-world applications, tables are created for different types of data.

• Students
• Employees
• Customers
• Products
• Orders
• Courses
• Payments
• Attendance

Good table design makes database applications easier to manage.