How to Use CREATE TABLE in SQL

First, the Quick Syntax:

CREATE TABLE table_name (
    column1_name data_type,
    column2_name data_type,
    ...
);

This is how you tell SQL:
“Hey, I want to create a new table and here’s how I want the structure to look.”

Let’s break it down

Imagine you’re keeping a list of your friends — just like writing down names, ages, and phone numbers in a notebook.
Well, in SQL, we’re doing the same thing, just digitally.
We’re telling the database:
“I need a place to store this kind of info, and here’s how it should be organized.”

Let’s create a table called friends

We want it to store:

• Their name
• Their age
• Their phone number

CREATE TABLE friends (
    name VARCHAR(50),
    age INT,
    phone VARCHAR(15)
);

Output (Table structure after creation):

Column Name	Data Type	Nullable
name	VARCHAR(50)	YES
age	INT	YES
phone	VARCHAR(15)	YES

Note: You won’t see this table like this unless you run something like DESCRIBE friends; or use a GUI tool (like phpMyAdmin or DBeaver). But this is how SQL sees your table now.

What each part means:

• CREATE TABLE friends – makes a new table called friends
• name VARCHAR(50) – stores names, up to 50 characters
• age INT – stores their age as a number
• phone VARCHAR(15) – stores phone numbers (as text, not numbers)

Why store phone as text?
Because numbers like +91, or those starting with 0 would get messed up if treated like normal numbers.

🛠️ Let’s add more rules to make it smarter

Maybe we want each friend to have:

• A unique ID (like a serial number)
• That ID to automatically increase
• Name should never be left empty

CREATE TABLE friends (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    phone VARCHAR(15)
);

Output (Table structure after creation):

Column Name	Data Type	Nullable	Extra
id	INT	NO	AUTO_INCREMENT PK
name	VARCHAR(50)	NO
age	INT	YES
phone	VARCHAR(15)	YES

Explanation:
AUTO_INCREMENT means the id will automatically go 1, 2, 3…
PRIMARY KEY means each row will have a unique identifier.

Now let’s add some data

INSERT INTO friends (name, age, phone)
VALUES ('Ravi', 25, '9876543210');

Output:

Query OK, 1 row affected

That means: the data got saved — Ravi is now in the table! 🎉

Wanna see what’s inside?

SELECT * FROM friends;

Output:

id	name	age	phone
1	Ravi	25	9876543210

Recap Time!

• CREATE TABLE = You’re setting up a new place to store your data
• You define what columns you want and what kind of info each one holds
• You can make it smarter with things like PRIMARY KEY, NOT NULL, and AUTO_INCREMENT
• After that, you can insert data and see it anytime

Now that you’re comfortable with creating tables, the next step is learning how to change them as your needs grow. Maybe you want to add a new column, rename something, or even remove a part you no longer need. That’s where the ALTER TABLE command comes in. It lets you modify your table without starting over. You can read more about it in this guide on altering tables.