CREATE TABLE Statement
Syntax, Examples

Introduction

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 ‘Delhi’:

CREATE TABLE student_profile (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  city VARCHAR(30) DEFAULT 'Delhi'
);

3. Inserting Data into the Table

INSERT INTO students (roll_no, name, class, age, city) VALUES
(1, 'Ananya Sharma', '10A', 15, 'Jaipur'),
(2, 'Ravi Kumar', '9B', 14, 'Patna');

4. Viewing the Data

SELECT * FROM students;

roll_no | name           | class | age | city
--------+----------------+-------+-----+---------
1       | Ananya Sharma  | 10A   | 15  | Jaipur
2       | Ravi Kumar     | 9B    | 14  | Patna

5. Creating a Table with Constraints

Let’s now include constraints to ensure valid data entry:

CREATE TABLE exam_results (
  roll_no INT PRIMARY KEY,
  subject VARCHAR(30) NOT NULL,
  marks INT CHECK (marks BETWEEN 0 AND 100),
  grade CHAR(2)
);

6. What Happens if You Try to Break the Rules?

INSERT INTO exam_results (roll_no, subject, marks, grade)
VALUES (1, 'Maths', 110, 'A+');

ERROR: Check constraint "marks BETWEEN 0 AND 100" failed

The database protects your data — ensuring marks never go above 100.

7. Dropping a Table

If you need to delete a table entirely:

DROP TABLE exam_results;

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.

Summary

The CREATE TABLE statement gives your data a home. You define what can be stored, how it's stored, and under what rules. Whether you're building a student database or managing exam scores, it all starts here.

What’s Next?

Now that you've learned how to create tables, it's time to learn how to enforce rules using SQL Constraints. This ensures your data stays clean, consistent, and reliable.