Python MySQL - DDL Statements (E-Commerce Platform)

What You'll Learn Here

CREATE — creating tables for E-Commerce
ALTER — modifying existing tables
DROP — removing tables safely
TRUNCATE — clearing table data
Python implementation — executing DDL from Python

What are DDL Statements?

DDL (Data Definition Language) statements are used to create, modify, and delete database objects like tables, indexes, and databases. They define the structure of your database.

CREATE

Creates new tables, databases, or indexes

ALTER

Modifies existing tables (add/change columns)

DROP

Deletes tables, databases, or indexes

TRUNCATE

Removes all data from a table (keeps structure)

DDL in E-Commerce

# ============================================================
# DDL STATEMENTS IN E-COMMERCE
# ============================================================

# 1. CREATE - Build the product table
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0
);

# 2. ALTER - Add a new column
ALTER TABLE products ADD COLUMN category VARCHAR(50);

# 3. ALTER - Modify a column
ALTER TABLE products MODIFY price DECIMAL(12,2);

# 4. DROP - Remove a table
DROP TABLE IF EXISTS old_orders;

# 5. TRUNCATE - Clear all data
TRUNCATE TABLE temporary_cart;

Key point: DDL statements change the structure of your database, not the data inside it.

Quick Check: What does DDL stand for? (Answer: Data Definition Language)

CREATE - Building Tables

Creating E-Commerce Tables from Python

# ============================================================
# CREATE TABLES FROM PYTHON
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="ecommerce_db"
)

cursor = connection.cursor()

# ============================================================
# 1. CREATE PRODUCTS TABLE
# ============================================================

create_products = """
CREATE TABLE IF NOT EXISTS products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    description TEXT,
    category VARCHAR(50),
    price DECIMAL(10,2) NOT NULL,
    cost DECIMAL(10,2),
    stock_quantity INT DEFAULT 0,
    reorder_level INT DEFAULT 10,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
"""
cursor.execute(create_products)
print("Products table created")

# ============================================================
# 2. CREATE CUSTOMERS TABLE
# ============================================================

create_customers = """
CREATE TABLE IF NOT EXISTS customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(15),
    address TEXT,
    city VARCHAR(50),
    state VARCHAR(50),
    zip_code VARCHAR(10),
    country VARCHAR(50) DEFAULT 'India',
    registration_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
"""
cursor.execute(create_customers)
print("Customers table created")

# ============================================================
# 3. CREATE ORDERS TABLE
# ============================================================

create_orders = """
CREATE TABLE IF NOT EXISTS orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2) NOT NULL,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    tax_amount DECIMAL(10,2) DEFAULT 0,
    shipping_amount DECIMAL(10,2) DEFAULT 0,
    status VARCHAR(20) DEFAULT 'pending',
    payment_status VARCHAR(20) DEFAULT 'pending',
    shipping_address TEXT,
    tracking_number VARCHAR(50),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
)
"""
cursor.execute(create_orders)
print("Orders table created")

# ============================================================
# 4. CREATE ORDER ITEMS TABLE
# ============================================================

create_order_items = """
CREATE TABLE IF NOT EXISTS order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    discount DECIMAL(10,2) DEFAULT 0,
    total_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
)
"""
cursor.execute(create_order_items)
print("Order items table created")

# ============================================================
# 5. CREATE REVIEWS TABLE
# ============================================================

create_reviews = """
CREATE TABLE IF NOT EXISTS reviews (
    review_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    rating INT CHECK (rating >= 1 AND rating <= 5),
    comment TEXT,
    review_date DATE,
    is_verified_purchase BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
)
"""
cursor.execute(create_reviews)
print("Reviews table created")

connection.commit()
cursor.close()
connection.close()

CREATE key points:

CREATE TABLE — defines a new table
IF NOT EXISTS — prevents errors if table exists
PRIMARY KEY — unique identifier for each row
AUTO_INCREMENT — automatically generates unique IDs
FOREIGN KEY — links tables together

Quick Check: What does AUTO_INCREMENT do? (Answer: It automatically generates a unique number for each new row)

ALTER - Modifying Tables

Changing Table Structure from Python

# ============================================================
# ALTER STATEMENTS FROM PYTHON
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="ecommerce_db"
)

cursor = connection.cursor()

# ============================================================
# 1. ADD A NEW COLUMN
# ============================================================

# Add discount column to products
cursor.execute("""
    ALTER TABLE products 
    ADD COLUMN discount_percent DECIMAL(5,2) DEFAULT 0
""")
print("Added discount_percent column to products")

# Add delivery_estimate to orders
cursor.execute("""
    ALTER TABLE orders 
    ADD COLUMN delivery_estimate DATE
""")
print("Added delivery_estimate column to orders")

# ============================================================
# 2. MODIFY AN EXISTING COLUMN
# ============================================================

# Change price precision
cursor.execute("""
    ALTER TABLE products 
    MODIFY price DECIMAL(12,2) NOT NULL
""")
print("Modified price column")

# Change VARCHAR length
cursor.execute("""
    ALTER TABLE customers 
    MODIFY phone VARCHAR(20)
""")
print("Modified phone column")

# ============================================================
# 3. RENAME A COLUMN
# ============================================================

cursor.execute("""
    ALTER TABLE products 
    CHANGE COLUMN product_name name VARCHAR(100) NOT NULL
""")
print("Renamed product_name to name")

# ============================================================
# 4. DROP A COLUMN
# ============================================================

cursor.execute("""
    ALTER TABLE products 
    DROP COLUMN discount_percent
""")
print("Removed discount_percent column")

# ============================================================
# 5. ADD AN INDEX
# ============================================================

cursor.execute("""
    ALTER TABLE products 
    ADD INDEX idx_category (category)
""")
print("Added index on category")

cursor.execute("""
    ALTER TABLE orders 
    ADD INDEX idx_customer_status (customer_id, status)
""")
print("Added composite index on customer_id and status")

# ============================================================
# 6. DROP AN INDEX
# ============================================================

cursor.execute("""
    ALTER TABLE products 
    DROP INDEX idx_category
""")
print("Removed index on category")

# ============================================================
# 7. RENAME A TABLE
# ============================================================

cursor.execute("""
    ALTER TABLE reviews 
    RENAME TO product_reviews
""")
print("Renamed reviews to product_reviews")

# Rename it back
cursor.execute("""
    ALTER TABLE product_reviews 
    RENAME TO reviews
""")
print("Renamed product_reviews back to reviews")

connection.commit()
cursor.close()
connection.close()

ALTER key points:

ADD COLUMN — adds a new column
MODIFY — changes column definition
CHANGE — renames and modifies a column
DROP COLUMN — removes a column
ADD INDEX — adds an index for performance

Quick Check: What ALTER command adds a new column? (Answer: ALTER TABLE table_name ADD COLUMN column_name data_type)

DROP - Removing Tables

Deleting Tables from Python

⚠️ WARNING: DROP is permanent!

DROP TABLE removes both the table structure and all data. There is no undo. Always use DROP IF EXISTS to avoid errors.

# ============================================================
# DROP STATEMENTS FROM PYTHON
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="ecommerce_db"
)

cursor = connection.cursor()

# ============================================================
# 1. DROP A TABLE (WITH IF EXISTS)
# ============================================================

cursor.execute("DROP TABLE IF EXISTS temporary_cart")
print("Dropped temporary_cart table (if it existed)")

cursor.execute("DROP TABLE IF EXISTS old_orders")
print("Dropped old_orders table (if it existed)")

# ============================================================
# 2. DROP A TABLE (WITHOUT IF EXISTS - RISKY)
# ============================================================

# This would cause an error if the table doesn't exist
try:
    cursor.execute("DROP TABLE backup_orders")
    print("Dropped backup_orders table")
except mysql.connector.Error as e:
    print(f"Error: {e}")

# ============================================================
# 3. DROP A DATABASE
# ============================================================

# DANGEROUS - This deletes everything in the database
# cursor.execute("DROP DATABASE IF EXISTS test_ecommerce")

# ============================================================
# 4. SAFE DROP WITH CONFIRMATION
# ============================================================

def safe_drop_table(table_name):
    """Drop a table with confirmation"""
    confirm = input(f"Are you sure you want to drop table '{table_name}'? (yes/no): ")
    if confirm.lower() == 'yes':
        cursor.execute(f"DROP TABLE IF EXISTS {table_name}")
        connection.commit()
        print(f"Table {table_name} dropped")
        return True
    else:
        print("Drop cancelled")
        return False

# safe_drop_table("old_orders")

connection.commit()
cursor.close()
connection.close()

DROP key points:

DROP TABLE — removes table and data
IF EXISTS — prevents errors if table doesn't exist
Permanent — cannot be undone
Use with caution — always backup before dropping

Quick Check: What does DROP TABLE do? (Answer: Removes the entire table and all its data permanently)

TRUNCATE - Clearing Tables

Removing All Data While Keeping Structure

# ============================================================
# TRUNCATE STATEMENTS FROM PYTHON
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="ecommerce_db"
)

cursor = connection.cursor()

# ============================================================
# 1. TRUNCATE A TABLE
# ============================================================

# TRUNCATE removes all data but keeps the table structure
cursor.execute("TRUNCATE TABLE temporary_cart")
print("Cleared all data from temporary_cart")

# ============================================================
# 2. TRUNCATE VS DELETE
# ============================================================

print("""
TRUNCATE:
- Removes ALL rows
- Faster than DELETE
- Cannot use WHERE
- Resets AUTO_INCREMENT counter
- Cannot be rolled back (in some cases)

DELETE:
- Can use WHERE to delete specific rows
- Slower for large tables
- Can be rolled back
- Does not reset AUTO_INCREMENT
""")

# ============================================================
# 3. TRUNCATE WITH FOREIGN KEYS
# ============================================================

# TRUNCATE may fail on tables with foreign keys
# You may need to:
# 1. Disable foreign key checks temporarily
# 2. TRUNCATE the table
# 3. Re-enable foreign key checks

try:
    # Disable foreign key checks
    cursor.execute("SET FOREIGN_KEY_CHECKS = 0")
    
    # TRUNCATE the table
    cursor.execute("TRUNCATE TABLE order_items")
    print("Truncated order_items (with foreign keys disabled)")
    
    # Re-enable foreign key checks
    cursor.execute("SET FOREIGN_KEY_CHECKS = 1")
    
except mysql.connector.Error as e:
    print(f"Error: {e}")
    cursor.execute("SET FOREIGN_KEY_CHECKS = 1")

connection.commit()
cursor.close()
connection.close()

TRUNCATE key points:

TRUNCATE — removes all rows from a table
Keeps structure — table remains but empty
Faster than DELETE — for clearing all rows
Resets AUTO_INCREMENT — counter starts from 1
Cannot use WHERE — always removes all rows

Quick Check: What is the difference between TRUNCATE and DELETE without WHERE? (Answer: TRUNCATE is faster and resets AUTO_INCREMENT; DELETE can be rolled back)

E-Commerce Python Examples

Building E-Commerce Database from Python

# ============================================================
# COMPLETE E-COMMERCE DATABASE SETUP
# ============================================================

import mysql.connector

class ECommerceDatabase:
    """Build and manage E-Commerce database structure"""
    
    def __init__(self, db_config):
        self.db_config = db_config
        self.connection = None
        self.cursor = None
    
    def connect(self):
        try:
            self.connection = mysql.connector.connect(**self.db_config)
            self.cursor = self.connection.cursor()
            return True
        except Exception as e:
            print(f"Connection failed: {e}")
            return False
    
    # ============================================================
    # CREATE TABLES
    # ============================================================
    
    def create_database(self, db_name):
        """Create the database if it doesn't exist"""
        # Connect without database first
        temp_config = self.db_config.copy()
        temp_config.pop('database', None)
        temp_conn = mysql.connector.connect(**temp_config)
        temp_cursor = temp_conn.cursor()
        
        temp_cursor.execute(f"CREATE DATABASE IF NOT EXISTS {db_name}")
        temp_cursor.close()
        temp_conn.close()
        print(f"Database '{db_name}' created or exists")
    
    def create_tables(self):
        """Create all tables for E-Commerce platform"""
        
        # Products table
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS products (
                product_id INT PRIMARY KEY AUTO_INCREMENT,
                product_name VARCHAR(100) NOT NULL,
                description TEXT,
                category VARCHAR(50),
                price DECIMAL(10,2) NOT NULL,
                stock_quantity INT DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        print("Products table ready")
        
        # Customers table
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS customers (
                customer_id INT PRIMARY KEY AUTO_INCREMENT,
                first_name VARCHAR(50) NOT NULL,
                last_name VARCHAR(50) NOT NULL,
                email VARCHAR(100) UNIQUE NOT NULL,
                phone VARCHAR(15),
                city VARCHAR(50),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        print("Customers table ready")
        
        # Orders table
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS orders (
                order_id INT PRIMARY KEY AUTO_INCREMENT,
                customer_id INT NOT NULL,
                order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
                total_amount DECIMAL(10,2) NOT NULL,
                status VARCHAR(20) DEFAULT 'pending',
                FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
            )
        """)
        print("Orders table ready")
        
        # Order items table
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS order_items (
                order_item_id INT PRIMARY KEY AUTO_INCREMENT,
                order_id INT NOT NULL,
                product_id INT NOT NULL,
                quantity INT NOT NULL,
                unit_price DECIMAL(10,2) NOT NULL,
                FOREIGN KEY (order_id) REFERENCES orders(order_id),
                FOREIGN KEY (product_id) REFERENCES products(product_id)
            )
        """)
        print("Order items table ready")
        
        # Reviews table
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS reviews (
                review_id INT PRIMARY KEY AUTO_INCREMENT,
                customer_id INT NOT NULL,
                product_id INT NOT NULL,
                rating INT,
                comment TEXT,
                FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
                FOREIGN KEY (product_id) REFERENCES products(product_id)
            )
        """)
        print("Reviews table ready")
        
        self.connection.commit()
    
    # ============================================================
    # ALTER TABLES
    # ============================================================
    
    def add_category_column(self):
        """Add category column to products"""
        try:
            self.cursor.execute("""
                ALTER TABLE products 
                ADD COLUMN category VARCHAR(50) DEFAULT 'General'
            """)
            self.connection.commit()
            print("Added category column")
        except mysql.connector.Error as e:
            if "Duplicate column" in str(e):
                print("Category column already exists")
            else:
                print(f"Error: {e}")
    
    def add_discount_column(self):
        """Add discount column to products"""
        try:
            self.cursor.execute("""
                ALTER TABLE products 
                ADD COLUMN discount_percent DECIMAL(5,2) DEFAULT 0
            """)
            self.connection.commit()
            print("Added discount column")
        except mysql.connector.Error as e:
            if "Duplicate column" in str(e):
                print("Discount column already exists")
            else:
                print(f"Error: {e}")
    
    def add_tracking_column(self):
        """Add tracking number to orders"""
        try:
            self.cursor.execute("""
                ALTER TABLE orders 
                ADD COLUMN tracking_number VARCHAR(50)
            """)
            self.connection.commit()
            print("Added tracking_number column")
        except mysql.connector.Error as e:
            if "Duplicate column" in str(e):
                print("Tracking number column already exists")
            else:
                print(f"Error: {e}")
    
    # ============================================================
    # INDEXES
    # ============================================================
    
    def add_indexes(self):
        """Add indexes for performance"""
        try:
            self.cursor.execute("""
                ALTER TABLE products 
                ADD INDEX idx_category (category)
            """)
            print("Added index on category")
        except mysql.connector.Error:
            print("Index on category already exists")
        
        try:
            self.cursor.execute("""
                ALTER TABLE orders 
                ADD INDEX idx_customer_id (customer_id)
            """)
            print("Added index on customer_id")
        except mysql.connector.Error:
            print("Index on customer_id already exists")
        
        self.connection.commit()
    
    # ============================================================
    # SHOW TABLES
    # ============================================================
    
    def list_tables(self):
        """List all tables in the database"""
        self.cursor.execute("SHOW TABLES")
        tables = self.cursor.fetchall()
        print("\nTables in database:")
        for table in tables:
            print(f"  - {table[0]}")
    
    def close(self):
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()

# ============================================================
# DEMONSTRATION
# ============================================================

db_config = {
    "host": "localhost",
    "user": "root",
    "password": "secret",
    "database": "ecommerce_db"
}

db = ECommerceDatabase(db_config)

if db.connect():
    
    print("=" * 60)
    print("E-COMMERCE DATABASE SETUP")
    print("=" * 60)
    
    # 1. Create tables
    print("\n1. CREATING TABLES")
    db.create_tables()
    
    # 2. Add columns
    print("\n2. ADDING COLUMNS")
    db.add_category_column()
    db.add_discount_column()
    db.add_tracking_column()
    
    # 3. Add indexes
    print("\n3. ADDING INDEXES")
    db.add_indexes()
    
    # 4. List tables
    print("\n4. TABLES LIST")
    db.list_tables()
    
    print("\nDatabase setup complete!")
    db.close()

This example shows:

Creating a complete E-Commerce database from Python
Using CREATE, ALTER, and INDEX statements
Error handling for existing columns
Organized class structure for database management
Listing tables for verification

Quick Check: Why is it important to check for duplicate columns before ALTER? (Answer: To avoid errors and make scripts idempotent)

Best Practices

DDL Best Practices

# ============================================================
# DDL BEST PRACTICES
# ============================================================

print("1. USE IF EXISTS / IF NOT EXISTS")
print("   - CREATE TABLE IF NOT EXISTS")
print("   - DROP TABLE IF EXISTS")
print("   - Prevents errors in repeated scripts")

print("\n2. USE TRANSACTIONS FOR DDL")
print("   - Some DDL operations can be rolled back")
print("   - Group related changes")

print("\n3. BACKUP BEFORE DDL OPERATIONS")
print("   - Always backup before DROP or ALTER")
print("   - Use CREATE TABLE backup AS SELECT * FROM original")

print("\n4. CHECK EXISTING STRUCTURE")
print("   - Check if columns exist before ALTER")
print("   - Use INFORMATION_SCHEMA to check")

print("\n5. USE MEANINGFUL NAMES")
print("   - Clear table and column names")
print("   - Consistent naming conventions")

print("\n6. ADD INDEXES AFTER DATA LOAD")
print("   - Add indexes after inserting data")
print("   - Speeds up data loading")

print("\n7. USE FOREIGN KEYS FOR DATA INTEGRITY")
print("   - Ensures data consistency")
print("   - Prevents orphaned records")

print("\n8. TEST DDL IN DEVELOPMENT FIRST")
print("   - Never test DDL in production")
print("   - Use staging environment")

Summary of best practices:

Use IF EXISTS/IF NOT EXISTS — prevent errors
Backup before DDL — protect your data
Check existing structure — avoid duplicate columns
Add indexes after data load — better performance
Test in development — never in production

Quick Check: Why should you test DDL in development first? (Answer: To avoid accidental data loss or structure changes in production)

Try It Yourself

Experiment with DDL statements in the editor below.

Loading Pyodide... 0%

Python Code Editor

# Python MySQL DDL - Practice

print("=" * 40)
print("DDL STATEMENTS - PRACTICE")
print("=" * 40)

# Simulated Database Structure
class SimulatedDB:
    tables = {}
    
    @classmethod
    def create_table(cls, table_name, columns):
        """Simulate CREATE TABLE"""
        if table_name in cls.tables:
            print(f"Table '{table_name}' already exists")
            return
        cls.tables[table_name] = columns
        print(f"Created table: {table_name}")
        print(f"  Columns: {', '.join(columns)}")
    
    @classmethod
    def alter_table(cls, table_name, operation, details):
        """Simulate ALTER TABLE"""
        if table_name not in cls.tables:
            print(f"Table '{table_name}' not found")
            return
        
        if operation == "ADD":
            cls.tables[table_name].append(details)
            print(f"Added column '{details}' to {table_name}")
        elif operation == "DROP":
            if details in cls.tables[table_name]:
                cls.tables[table_name].remove(details)
                print(f"Removed column '{details}' from {table_name}")
            else:
                print(f"Column '{details}' not found")
    
    @classmethod
    def drop_table(cls, table_name):
        """Simulate DROP TABLE"""
        if table_name not in cls.tables:
            print(f"Table '{table_name}' not found")
            return
        del cls.tables[table_name]
        print(f"Dropped table: {table_name}")
    
    @classmethod
    def truncate_table(cls, table_name):
        """Simulate TRUNCATE TABLE"""
        if table_name not in cls.tables:
            print(f"Table '{table_name}' not found")
            return
        print(f"Truncated table: {table_name} (all data removed)")
    
    @classmethod
    def list_tables(cls):
        """List all tables"""
        if not cls.tables:
            print("No tables found")
            return
        print("\nTables:")
        for name, columns in cls.tables.items():
            print(f"  {name}: {', '.join(columns)}")

print("\n1. CREATE TABLES")
print("-" * 40)

SimulatedDB.create_table("products", 
    ["product_id INT PRIMARY KEY", "name VARCHAR(100)", "price DECIMAL(10,2)"]
)
SimulatedDB.create_table("customers",
    ["customer_id INT PRIMARY KEY", "name VARCHAR(100)", "email VARCHAR(100)"]
)
SimulatedDB.create_table("orders",
    ["order_id INT PRIMARY KEY", "customer_id INT", "total DECIMAL(10,2)"]
)

print("\n2. ALTER TABLES")
print("-" * 40)

SimulatedDB.alter_table("products", "ADD", "category VARCHAR(50)")
SimulatedDB.alter_table("products", "ADD", "stock INT DEFAULT 0")

print("\n3. LIST TABLES")
print("-" * 40)

SimulatedDB.list_tables()

print("\n4. DROP TABLE")
print("-" * 40)

SimulatedDB.drop_table("orders")

print("\n5. TRUNCATE TABLE")
print("-" * 40)

SimulatedDB.truncate_table("products")

print("\n6. FINAL TABLES")
print("-" * 40)

SimulatedDB.list_tables()

print("\n7. DDL SUMMARY")
print("-" * 40)
print("""
┌─────────────────┬──────────────────────────────────────────────┐
│ DDL Statement   │ Purpose                                     │
├─────────────────┼──────────────────────────────────────────────┤
│ CREATE          │ Create new tables, databases               │
│ ALTER           │ Modify existing tables                     │
│ DROP            │ Remove tables permanently                  │
│ TRUNCATE        │ Remove all data, keep structure            │
└─────────────────┴──────────────────────────────────────────────┘
""")

print("DDL statements define your database structure!")

========================================
DDL STATEMENTS - PRACTICE
========================================

1. CREATE TABLES
----------------------------------------
Created table: products
Columns: product_id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2)
Created table: customers
Columns: customer_id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100)
Created table: orders
Columns: order_id INT PRIMARY KEY, customer_id INT, total DECIMAL(10,2)

2. ALTER TABLES
----------------------------------------
Added column 'category VARCHAR(50)' to products
Added column 'stock INT DEFAULT 0' to products

3. LIST TABLES
----------------------------------------

Tables:
products: product_id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2), category VARCHAR(50), stock INT DEFAULT 0
customers: customer_id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100)
orders: order_id INT PRIMARY KEY, customer_id INT, total DECIMAL(10,2)

4. DROP TABLE
----------------------------------------
Dropped table: orders

5. TRUNCATE TABLE
----------------------------------------
Truncated table: products (all data removed)

6. FINAL TABLES
----------------------------------------

Tables:
products: product_id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2), category VARCHAR(50), stock INT DEFAULT 0
customers: customer_id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100)

7. DDL SUMMARY
----------------------------------------
┌─────────────────┬──────────────────────────────────────────────┐
│ DDL Statement │ Purpose │
├─────────────────┼──────────────────────────────────────────────┤
│ CREATE │ Create new tables, databases │
│ ALTER │ Modify existing tables │
│ DROP │ Remove tables permanently │
│ TRUNCATE │ Remove all data, keep structure │
└─────────────────┴──────────────────────────────────────────────┘

DDL statements define your database structure!

🏆

You've Got It!

You now understand DDL statements in MySQL from Python. You can CREATE, ALTER, DROP, and TRUNCATE tables for your E-Commerce platform.

Quick Quiz

Test what you've learned:

1. What does CREATE TABLE do?

A) Adds data to a table B) Creates a new table C) Deletes a table D) Modifies a table

2. What ALTER command adds a new column?

A) ALTER TABLE table_name MODIFY column_name B) ALTER TABLE table_name ADD COLUMN column_name C) ALTER TABLE table_name DROP COLUMN column_name D) ALTER TABLE table_name RENAME column_name

3. What does DROP TABLE do?

A) Removes all data but keeps structure B) Removes the entire table permanently C) Adds a new column D) Creates a new table

4. What is the difference between TRUNCATE and DELETE?

A) They are the same B) TRUNCATE is faster and resets AUTO_INCREMENT C) DELETE is faster than TRUNCATE D) TRUNCATE can use WHERE

5. Why should you use IF EXISTS with DROP?

A) To make it run faster B) To prevent errors if the table doesn't exist C) To backup the table first D) To drop multiple tables

Frequently Asked Questions

What is the difference between DDL and DML?

DDL (Data Definition Language) defines the structure of the database (CREATE, ALTER, DROP). DML (Data Manipulation Language) manipulates the data (INSERT, UPDATE, DELETE). DDL changes the structure; DML changes the data.

Can I rollback DDL statements?

Some DDL statements can be rolled back in transactions (like CREATE TABLE in InnoDB). However, DROP is often permanent. Always test DDL in development first.

What is a common interview question about DDL?

Common questions include: "Explain the difference between DDL and DML," "What are the main DDL statements?" and "How do you add a column to an existing table?"

Why add indexes after data load?

Adding indexes before data load slows down insert operations. It's faster to load data first, then add indexes. This is especially important for large datasets.

Will this conflict with future pure MySQL tutorials?

No. This tutorial focuses on executing DDL from Python with mysql-connector. Future MySQL tutorials will focus on SQL syntax and optimization with different titles and URLs.

Where to Go From Here

Now that you understand DDL statements, check out these related topics:

Previous: MySQL Operators Next: DML Statements →

Python

Python: DDL Statements in MySQL

What are DDL Statements?

CREATE

ALTER

DROP

TRUNCATE

DDL in E-Commerce

CREATE - Building Tables

Creating E-Commerce Tables from Python

ALTER - Modifying Tables

Changing Table Structure from Python

DROP - Removing Tables

Deleting Tables from Python

TRUNCATE - Clearing Tables

Removing All Data While Keeping Structure

E-Commerce Python Examples

Building E-Commerce Database from Python

Best Practices

DDL Best Practices

Try It Yourself

You've Got It!

Quick Quiz

Frequently Asked Questions

What is the difference between DDL and DML?

Can I rollback DDL statements?

What is a common interview question about DDL?

Why add indexes after data load?

Will this conflict with future pure MySQL tutorials?

Where to Go From Here

DML Statements

JOIN Operations

Aggregation

Python

Python: DDL Statements in MySQL

What are DDL Statements?

CREATE

ALTER

DROP

TRUNCATE

DDL in E-Commerce

CREATE - Building Tables

Creating E-Commerce Tables from Python

ALTER - Modifying Tables

Changing Table Structure from Python

DROP - Removing Tables

Deleting Tables from Python

TRUNCATE - Clearing Tables

Removing All Data While Keeping Structure

E-Commerce Python Examples

Building E-Commerce Database from Python

Best Practices

DDL Best Practices

Try It Yourself

You've Got It!

Quick Quiz

Frequently Asked Questions

What is the difference between DDL and DML? ▼

Can I rollback DDL statements? ▼

What is a common interview question about DDL? ▼

Why add indexes after data load? ▼

Will this conflict with future pure MySQL tutorials? ▼

Where to Go From Here

DML Statements

JOIN Operations

Aggregation

What is the difference between DDL and DML?

Can I rollback DDL statements?

What is a common interview question about DDL?

Why add indexes after data load?

Will this conflict with future pure MySQL tutorials?