Table Builders Unite! Creating Your First Database Tables with CREATE TABLE

Imagine you’re building a Lego house. Before you start putting Lego pieces together, you need a base plate—a clean, flat surface where your house will stand.
In MySQL, that base plate is called a table.

A table is where your data lives. It’s like a grid with rows and columns—just like your school timetable!

Today, we’ll learn how to create tables using the SQL command:

CREATE TABLE

Let’s break it down in a super simple way.

1. What Is a Table? (Explained Like You’re 10)

Think of a table like a notebook.

Each page = a row
Each column = a type of information you write about

For example, if you make a notebook page about superheroes:

Column 1 → Name
Column 2 → Power
Column 3 → Strength Points

Every superhero you write becomes a row.

MySQL tables work the same way!

2. CREATE TABLE Basic Structure

Here is the simple pattern:

CREATE TABLE table_name (
    column_name1 data_type,
    column_name2 data_type,
    column_name3 data_type
);

You tell MySQL:

What the table is called
What columns does it have
What types of data go into each column

3. Example 1 — Creating a Simple Students Table

CREATE TABLE students (
    id INT,
    name VARCHAR(50),
    age INT
);

Explanation:

id — like roll number
name — student’s name (letters allowed)
age — number

That’s it! You’ve built your first MySQL table.

4. Adding Constraints — The “Rules” for Your Table

MySQL lets you add rules so your data behaves properly.

Common constraints:

Constraint	Meaning (Kid Version)
PRIMARY KEY	A special column that must be unique → “No duplicates!”
NOT NULL	Cannot be empty
UNIQUE	No two rows can have the same value
AUTO_INCREMENT	MySQL automatically assigns numbers (1,2,3…)

Example 2 — Students Table (Improved Version)

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

This creates a smart table that avoids bad data.

5. Example 3 — Creating an Orders Table

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE NOT NULL,
    customer_name VARCHAR(100),
    total_amount DECIMAL(10,2)
);

Meaning:

DECIMAL(10,2) → Up to 10 digits with 2 for paisa/decimal.

6. Example 4 — CREATE TABLE with FOREIGN KEY

A FOREIGN KEY connects one table to another, like a friendship.

CREATE TABLE order_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_name VARCHAR(100),
    price DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

Now every order_items row must belong to an order in the orders table.

7. CREATE TABLE IF NOT EXISTS

To avoid errors:

CREATE TABLE IF NOT EXISTS students (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

8. Deleting a Table (Be Careful!)

DROP TABLE students;

This wipes it permanently—like erasing your notebook!

Practice Questions (with Solutions)

1. Create a table named `books` with these columns:

book_id (INT, Primary key)
title (VARCHAR 100)
author (VARCHAR 50)
price (DECIMAL 6,2)

Answer:

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(50),
    price DECIMAL(6,2)
);

2. Create a table `employees` with an auto-incrementing ID, name (not null), salary, and joining date.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2),
    joining_date DATE
);

3. Create two tables:

departments (dept_id PK, name)
employees (emp_id PK, name, dept_id FK → departments.dept_id)

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Interview Questions (With Answers)

1. What is CREATE TABLE used for?

It is used to create a new table in a database with columns and data types.

2. What is a PRIMARY KEY?

A column that uniquely identifies each row.
No duplicates + cannot be null.

3. What is AUTO_INCREMENT?

MySQL automatically gives increasing numbers (1,2,3…) to each new row.

4. Difference between CHAR and VARCHAR?

CHAR	VARCHAR
Fixed length	Variable length
Faster	Space efficient

5. Why do we use FOREIGN KEY?

To link two tables together and maintain relational integrity.

6. What is the purpose of NOT NULL?

It stops MySQL from allowing empty values in a column.

7. What is the meaning of DECIMAL(10,2)?

Total digits = 10
Digits after decimal = 2

8. What happens if you run CREATE TABLE twice?

You get an error unless you use:

CREATE TABLE IF NOT EXISTS table_name;

9. What does DROP TABLE do?

Deletes the whole table permanently.

10. Can a table have more than one PRIMARY KEY?

No. However, it can have a composite primary key (i.e., multiple columns combined).