SQL DDL Tutorial: CREATE, ALTER & DROP Tables

SQL DDL Commands Tutorial: CREATE, ALTER & DROP Tables

📖 Introduction: Building the Foundation

Every database starts as an empty shell. DDL (Data Definition Language) is how you build the structure — tables, columns, data types, and constraints. Think of DDL as the architecture of your database, while DML (INSERT, UPDATE, DELETE) is the furniture you put inside.

Command	Purpose	Reversible?
`CREATE`	Build new tables/databases	Yes (with `DROP`)
`ALTER`	Modify existing structure	Yes
`DROP`	Permanently delete tables/databases	No
`TRUNCATE`	Delete all data, keep structure	No

⚠️ Warning: DROP and TRUNCATE are irreversible. Always backup before running them!

1️⃣ Data Types: Choosing the Right Container

Before creating tables, you must understand data types. They define what kind of data each column can hold.

Data Type	Stores	Example Value	Use Case
`INT`	Whole numbers	`42`, `-7`	IDs, counts, ages
`BIGINT`	Very large whole numbers	`9223372036854775807`	Big IDs, timestamps
`DECIMAL(p,s)`	Exact decimal numbers	`DECIMAL(10,2)` → `99999999.99`	Money, prices
`FLOAT` / `DOUBLE`	Approximate decimals	`3.14159`	Scientific calculations
`VARCHAR(n)`	Variable-length text	`VARCHAR(100)` → `'John Doe'`	Names, emails, titles
`CHAR(n)`	Fixed-length text	`CHAR(5)` → `'ABCDE'`	Codes, country codes
`TEXT`	Long text

Choosing DECIMAL for Money

sql
1DECIMAL(10,2)
2-- 10 = total digits
3-- 2 = digits after decimal
4-- Max value: 99,999,999.99

Never use FLOAT for money! Floats are approximate and can cause rounding errors. Always use DECIMAL for financial data.

2️⃣ CREATE TABLE — Building Your First Table

Purpose: Define a new table with columns, data types, and constraints.

Syntax:

sql
1CREATE TABLE table_name (
2    column1 datatype constraints,
3    column2 datatype constraints,
4    column3 datatype constraints,
5    ...
6);

Example 1: Create a Simple Employees Table

sql
1CREATE TABLE employees (
2    id INT PRIMARY KEY AUTO_INCREMENT,
3    name VARCHAR(100) NOT NULL,
4    email VARCHAR(100) UNIQUE,
5    salary DECIMAL(10,2),
6    hire_date DATE,
7    department_id INT
8);

Breaking it down:

Component	Meaning
`id INT`	Column `id` stores integers
`PRIMARY KEY`	Uniquely identifies each row; no duplicates allowed
`AUTO_INCREMENT`	Automatically generates next number (1, 2, 3...)
`VARCHAR(100)`	Text up to 100 characters
`NOT NULL`	This column must have a value; cannot be empty
`UNIQUE`	No two rows can have the same email
`DECIMAL(10,2)`	Number with up to 10 digits, 2 after decimal

Example 2: Create a Table with More Constraints

sql
1CREATE TABLE products (
2    product_id INT PRIMARY KEY AUTO_INCREMENT,
3    product_name VARCHAR(200) NOT NULL,
4    description TEXT,
5    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
6    stock_quantity INT DEFAULT 0,
7    category_id INT,
8    is_active BOOLEAN DEFAULT TRUE,
9    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
10);

New constraints explained:

Constraint	Purpose
`DEFAULT 0`	If no value given, uses `0`
`DEFAULT TRUE`	If no value given, uses `TRUE`
`CHECK (price > 0)`	Ensures price is always positive
`DEFAULT CURRENT_TIMESTAMP`	Auto-fills with current date/time

3️⃣ ALTER TABLE — Modifying Existing Structure

Purpose: Change a table after creation without losing data.

A) ADD COLUMN — Add a New Column

sql
1ALTER TABLE employees ADD COLUMN phone VARCHAR(15);

What happens: A new phone column is added to the employees table. Existing rows will have NULL in this column unless you specify a default.

With default value:

sql
1ALTER TABLE employees ADD COLUMN country VARCHAR(50) DEFAULT 'USA';

B) MODIFY COLUMN — Change Data Type

sql
1ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2);

What happens: The salary column can now hold larger numbers (up to 999,999,999,999.99). Existing data is preserved if compatible.

Another example — increase text length:

sql
1ALTER TABLE employees MODIFY COLUMN name VARCHAR(150);

Caution: Modifying to a smaller type (e.g., VARCHAR(100) → VARCHAR(5)) may truncate existing data!

C) DROP COLUMN — Remove a Column

sql
1ALTER TABLE employees DROP COLUMN phone;

What happens: The phone column and all its data are permanently deleted.

⚠️ Warning: This cannot be undone! Always backup first.

D) RENAME TABLE — Change Table Name

sql
1ALTER TABLE employees RENAME TO staff;

What happens: The table is now called staff. All data, columns, and constraints remain intact.

Alternative syntax (MySQL):

sql
1RENAME TABLE employees TO staff;

4️⃣ DROP — Permanent Deletion

A) DROP TABLE — Delete a Table Completely

sql
1DROP TABLE employees;

What happens: The entire table — structure, data, indexes, constraints — is permanently deleted.

Safe practice — check if exists:

sql
1DROP TABLE IF EXISTS employees;

B) DROP DATABASE — Delete an Entire Database

sql
1DROP DATABASE company_db;

What happens: Every table, view, procedure, and piece of data in company_db is gone forever.

Safe practice:

sql
1DROP DATABASE IF EXISTS company_db;

🔴 CRITICAL: There is no "Recycle Bin" in SQL. DROP is instant and irreversible!

5️⃣ TRUNCATE TABLE — Delete All Data, Keep Structure

sql
1TRUNCATE TABLE employees;

What happens:

All rows are deleted
Table structure remains
AUTO_INCREMENT counter resets to 1
Much faster than DELETE FROM (no row-by-row logging)

	`DELETE`	`TRUNCATE`
Speed	Slower	Very fast
Rollback	Possible	Not possible
Resets auto-increment	No	Yes
Deletes structure	No	No

📝 Complete DDL Command Reference

Command	Action	Example
`CREATE TABLE`	Create new table	`CREATE TABLE users (id INT, name VARCHAR(50));`
`ALTER TABLE ... ADD`	Add column	`ALTER TABLE users ADD age INT;`
`ALTER TABLE ... MODIFY`	Change column type	`ALTER TABLE users MODIFY name VARCHAR(100);`
`ALTER TABLE ... DROP`	Remove column	`ALTER TABLE users DROP COLUMN age;`
`ALTER TABLE ... RENAME`	Rename table	`ALTER TABLE users RENAME TO members;`
`DROP TABLE`	Delete table	`DROP TABLE users;`
`TRUNCATE TABLE`	Delete all rows	`TRUNCATE TABLE users;`
`DROP DATABASE`	Delete database	`DROP DATABASE mydb;`

🚀 Hands-On Project: E-Commerce Database Schema

Project Goal

Design a complete, production-ready database for an online store.

Step 1: Create the Database

sql
1CREATE DATABASE ecommerce_db;
2USE ecommerce_db;

Step 2: Create the `customers` Table

sql
1CREATE TABLE customers (
2    customer_id INT PRIMARY KEY AUTO_INCREMENT,
3    first_name VARCHAR(50) NOT NULL,
4    last_name VARCHAR(50) NOT NULL,
5    email VARCHAR(100) UNIQUE NOT NULL,
6    password_hash VARCHAR(255) NOT NULL,
7    phone VARCHAR(20),
8    address TEXT,
9    city VARCHAR(50),
10    country VARCHAR(50) DEFAULT 'USA',
11    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
12    is_active BOOLEAN DEFAULT TRUE
13);

Step 3: Create the `categories` Table

sql
1CREATE TABLE categories (
2    category_id INT PRIMARY KEY AUTO_INCREMENT,
3    category_name VARCHAR(100) NOT NULL,
4    description TEXT,
5    parent_category_id INT DEFAULT NULL
6);

Step 4: Create the `products` Table

sql
1CREATE TABLE products (
2    product_id INT PRIMARY KEY AUTO_INCREMENT,
3    product_name VARCHAR(200) NOT NULL,
4    description TEXT,
5    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
6    stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0),
7    category_id INT,
8    sku VARCHAR(50) UNIQUE,
9    weight_kg DECIMAL(5,2),
10    is_active BOOLEAN DEFAULT TRUE,
11    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
12    FOREIGN KEY (category_id) REFERENCES categories(category_id)
13);

Step 5: Create the `orders` Table

sql
1CREATE TABLE orders (
2    order_id INT PRIMARY KEY AUTO_INCREMENT,
3    customer_id INT NOT NULL,
4    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
5    status ENUM('Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled') DEFAULT 'Pending',
6    total_amount DECIMAL(12,2) DEFAULT 0,
7    shipping_address TEXT,
8    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
9);

Step 6: Create the `order_items` Table

sql
1CREATE TABLE order_items (
2    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
3    order_id INT NOT NULL,
4    product_id INT NOT NULL,
5    quantity INT NOT NULL CHECK (quantity > 0),
6    unit_price DECIMAL(10,2) NOT NULL,
7    subtotal DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
8    FOREIGN KEY (order_id) REFERENCES orders(order_id),
9    FOREIGN KEY (product_id) REFERENCES products(product_id)
10);

Step 7: Verify Your Schema

sql
1SHOW TABLES;

Expected output:

+------------------------+
| Tables_in_ecommerce_db |
+------------------------+
| categories             |
| customers              |
| order_items            |
| orders                 |
| products               |
+------------------------+

Step 8: Describe Each Table

sql
1DESCRIBE products;
2DESCRIBE orders;

Step 9: Modify the Schema (ALTER Practice)

sql
1-- Add a discount column to products
2ALTER TABLE products ADD COLUMN discount_percent DECIMAL(5,2) DEFAULT 0;
3
4-- Increase email length for international customers
5ALTER TABLE customers MODIFY COLUMN email VARCHAR(150);
6
7-- Add a tracking number to orders
8ALTER TABLE orders ADD COLUMN tracking_number VARCHAR(100);
9
10-- Remove the weight column if no longer needed
11ALTER TABLE products DROP COLUMN weight_kg;

Step 10: Schema Diagram (Visual)

┌─────────────┐       ┌─────────────┐       ┌─────────────┐
│  customers  │       │   orders    │       │ order_items │
├─────────────┤       ├─────────────┤       ├─────────────┤
│ customer_id │◄──────┤ customer_id │       │ order_id    │◄──┐
│ first_name  │       │ order_id(PK)│◄─────┤ product_id  │───┼──┐
│ last_name   │       │ order_date  │       │ quantity    │   │  │
│ email       │       │ status      │       │ unit_price  │   │  │
│ ...         │       │ total_amount│       │ subtotal    │   │  │
└─────────────┘       └─────────────┘       └─────────────┘   │  │
                                                              │  │
┌─────────────┐                                               │  │
│ categories  │                                               │  │
├─────────────┤                                               │  │
│ category_id │◄────────────────────────────────────────────┘  │
│ category_name│                                              │
│ ...         │◄─────────────────────────────────────────────────┘
└─────────────┘       ┌─────────────┐
                      │   products  │
                      ├─────────────┤
                      │ product_id  │
                      │ product_name│
                      │ price       │
                      │ category_id │
                      │ ...         │
                      └─────────────┘

✅ Module 6 Summary

Concept	Command	Remember
Create table	`CREATE TABLE`	Define columns, types, and constraints
Add column	`ALTER TABLE ... ADD`	Existing rows get NULL (or default)
Change type	`ALTER TABLE ... MODIFY`	May truncate data if shrinking
Remove column	`ALTER TABLE ... DROP`	Permanent data loss!
Rename table	`ALTER TABLE ... RENAME`	Structure and data preserved
Delete all rows	`TRUNCATE TABLE`	Fast, resets auto-increment
Delete table	`DROP TABLE`	Everything gone forever
Delete database	`DROP DATABASE`	Everything gone forever

🎯 Practice Exercises

Create a students table with: student_id, name, email, enrollment_date, gpa (DECIMAL).
Add a phone column to your students table.
Modify the name column to allow 200 characters.
Create a courses table with: course_id, course_name, credits, department.
Add a prerequisite_course_id column to courses.
Drop the prerequisite_course_id column.
Explain the difference between DELETE, TRUNCATE, and DROP.

🎓 What's Next?

In Module 7, you'll master JOINs — the most powerful tool for combining data from multiple tables. You'll learn INNER JOIN, LEFT JOIN, RIGHT JOIN, and how to query across your entire e-commerce schema! 🚀