SQL CREATE TABLE Statement - Syntax, Examples

CREATE TABLE

Every database begins with a table. A table is where your data lives. In SQL, the CREATE TABLE statement helps you define this storage space — specifying what kind of data you’ll collect, and how it should behave. It's like designing a form before collecting responses.

Syntax of CREATE TABLE

CREATE TABLE table_name (
  column1 datatype constraint,
  column2 datatype constraint,
  ...
);

Let’s break it down:

• table_name: Name of your table (e.g., students)
• column: Name of each field (e.g., roll_no, name)
• datatype: Defines the kind of value stored (e.g., INT, VARCHAR)
• constraint: Optional rules (e.g., PRIMARY KEY, NOT NULL)

1. Creating a Basic Students Table

Let’s design a table to store student details at a school in India.

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  class VARCHAR(10),
  age INT,
  city VARCHAR(30)
);

Explanation

• roll_no uniquely identifies each student. Marked as PRIMARY KEY.
• name cannot be empty. Hence, NOT NULL.
• class, age, and city store remaining student information.

2. Creating a Table with Default Values

We can predefine values using DEFAULT. Suppose most students belong to ‘Hyderabad:

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  class VARCHAR(10),
  age INT,
  city VARCHAR(30) DEFAULT 'Hyderabad'
);

Best Practices for CREATE TABLE

• Always define a PRIMARY KEY to uniquely identify rows.
• Use NOT NULL to prevent blank values in critical columns.
• Use CHECK and DEFAULT constraints to control behavior.