SQL CREATE TABLE Statement — Syntax, Examples
Create Tables
The office needs a consistent place to store roll numbers, names and marks before it can save the first student.
CREATE TABLE defines a table’s columns and constraints. It establishes the structure for future rows; it does not insert student records by itself.
Explain and apply
Define a student table and explain how its columns and primary key describe valid records.
Work it out
Create a small mark table, insert one valid row and read it back.
Avoid mistakes
A default is not the same as a universal validation rule.
Create Tables: the request and its result
School need
The office needs a consistent place to store roll numbers, names and marks before it can save the first student.
Database operation
CREATE TABLE defines a table’s columns and constraints. It establishes the structure for future rows; it does not insert student records by itself.
Check the outcome
The definition creates an empty table with explicit rules. The separate INSERT provides the data that SELECT can then return.
Understand what the database does
Each column definition combines a name with a type and optional rules. A primary key identifies rows uniquely and disallows missing key values. NOT NULL requires a value; CHECK expresses a permitted condition. A default supplies a value when a column is omitted, subject to the engine’s rules.
Related concepts: Insert Rows and Data Constraints.
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
SQL · portable concepts; preserved examples identify their engine
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
SQL · portable concepts; preserved examples identify their engine
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
Query discussed in this section:
CREATE TABLE students (
roll_no INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
class VARCHAR(10),
age INT,
city VARCHAR(30)
);roll_nouniquely identifies each student. Marked as PRIMARY KEY.namecannot be empty. Hence, NOT NULL.class,age, andcitystore remaining student information.
2. Creating a Table with Default Values
SQL · portable concepts; preserved examples identify their engine
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
Query discussed in this section:
CREATE TABLE students (
roll_no INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
class VARCHAR(10),
age INT,
city VARCHAR(30) DEFAULT 'Hyderabad'
);- 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.
Important differences and mistakes
A default is not the same as a universal validation rule. IF NOT EXISTS does not verify that an existing table has the intended schema. Avoid experimenting on a production table, and verify engine-specific type and constraint support.
Try it with the school data
Create a small mark table, insert one valid row and read it back.
This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.
| roll_no | name | class | marks | city |
|---|---|---|---|---|
| 1 | Aarav | 10A | 85 | Pune |
| 2 | Diya | 9B | 70 | Chennai |
| 3 | Sneha | 10A | 85 | Pune |
SQL · create only these practice tables in a disposable database:
CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');
Worked answer and reasoning
This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.
| roll_no | name | class | marks | city |
|---|---|---|---|---|
| 1 | Aarav | 10A | 85 | Pune |
| 2 | Diya | 9B | 70 | Chennai |
| 3 | Sneha | 10A | 85 | Pune |
SQL · create only these practice tables in a disposable database:
CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');
SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect
CREATE TABLE practice_marks (roll_no INTEGER PRIMARY KEY, marks INTEGER NOT NULL CHECK (marks BETWEEN 0 AND 100)); INSERT INTO practice_marks VALUES (1,85);
SELECT roll_no, marks FROM practice_marks;| roll_no | marks |
|---|---|
| 1 | 85 |
The definition creates an empty table with explicit rules. The separate INSERT provides the data that SELECT can then return.
Check your understanding
Use the idea independently
CREATE TABLE defines a table’s columns and constraints. It establishes the structure for future rows; it does not insert student records by itself.
The definition creates an empty table with explicit rules. The separate INSERT provides the data that SELECT can then return.
Continue your understanding with Insert Rows and Data Constraints.
Source for the engine-specific behavior: MySQL 8.4 reference documentation.
Reaching the recap and completing the quizzes are separate milestones.