BackendInterview.com

Data Definition Language

Data Definition Language (DDL) is a subset of SQL that deals with the structure and organization of database objects. Think of DDL as the architectural blueprint commands for your database. While Data Manipulation Language (DML) works with the data inside tables, DDL works with the tables, indexes, and other database objects themselves.

Key Characteristics of DDL:

  • Schema Management: Creates and modifies the database structure
  • Auto-Commit: DDL statements are typically auto-committed (changes are permanent immediately)
  • Metadata Operations: Works with database metadata (information about the database structure)
  • Structure Definition: Defines how data will be organized and stored

Why DDL Matters:

  • Database Design: Establishes the foundation for data storage
  • Data Integrity: Sets up constraints and relationships
  • Performance: Proper structure design affects query performance
  • Maintenance: Allows for schema evolution as requirements change

CREATE Statement

The CREATE statement is used to build new database objects from scratch. It’s like laying the foundation and framing of a house before you can move furniture in.

Creating Tables

Purpose: Establishes a new table structure with defined columns, data types, and constraints.

Basic Table Creation Process: When you create a table, you’re defining:

  • Column Names: What each piece of data will be called
  • Data Types: What kind of data each column can hold
  • Constraints: Rules that ensure data quality and relationships
  • Storage Properties: How the database should physically store the data

Simple Table Creation Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    salary DECIMAL(10,2)
);

Understanding This Example:

  • employee_id: Integer that uniquely identifies each employee (PRIMARY KEY ensures uniqueness)
  • first_name/last_name: Variable-length strings up to 50 characters (NOT NULL means these fields are required)
  • email: Unique email address (UNIQUE constraint prevents duplicates)
  • hire_date: When the employee was hired
  • salary: Decimal number with up to 10 total digits, 2 after decimal point

Complex Table with Relationships:

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    shipping_address TEXT,
    order_status ENUM('Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled'),
    total_amount DECIMAL(12,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Breaking Down This Complex Example:

  • AUTO_INCREMENT: Database automatically generates sequential numbers
  • DEFAULT CURRENT_TIMESTAMP: Automatically sets current date/time when record is created
  • ENUM: Restricts values to specific predefined options
  • TEXT: For longer text content like addresses
  • FOREIGN KEY: Creates relationship to customers table, ensuring referential integrity
  • ON UPDATE CURRENT_TIMESTAMP: Automatically updates timestamp when record is modified

Creating Indexes

Purpose: Indexes improve query performance by creating faster paths to data, similar to an index in a book.

When to Create Indexes:

  • Columns frequently used in WHERE clauses
  • Columns used for JOIN operations
  • Columns used for ORDER BY operations
  • Foreign key columns

Index Creation Examples:

-- Single column index for faster customer lookups
CREATE INDEX idx_customer_email ON customers(email);

-- Composite index for queries that filter by both city and state
CREATE INDEX idx_location ON customers(city, state);

-- Unique index to ensure no duplicate combinations
CREATE UNIQUE INDEX idx_product_sku ON products(sku);

Index Types and Their Uses:

  • B-Tree Index: Default type, good for equality and range queries
  • Hash Index: Fast for exact matches, not good for range queries
  • Composite Index: Covers multiple columns, useful for complex queries
  • Unique Index: Enforces uniqueness while providing performance benefits

Creating Views

Purpose: Views are virtual tables that present data from one or more tables in a specific way without storing the data separately.

View Creation Example:

CREATE VIEW customer_summary AS
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    c.email,
    COUNT(o.order_id) as total_orders,
    SUM(o.total_amount) as total_spent,
    MAX(o.order_date) as last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name, c.email;

Benefits of Views:

  • Security: Hide sensitive columns from certain users
  • Simplification: Make complex queries reusable
  • Abstraction: Present data in user-friendly formats
  • Consistency: Ensure consistent data presentation across applications

ALTER Statement

The ALTER statement modifies existing database objects. It’s like renovating a house while people are still living in it – you need to be careful not to break anything.

Adding Columns

Purpose: Expand table structure to accommodate new data requirements.

Basic Column Addition:

ALTER TABLE employees 
ADD COLUMN phone_number VARCHAR(20);

Adding Multiple Columns with Constraints:

ALTER TABLE employees 
ADD COLUMN department_id INT,
ADD COLUMN manager_id INT,
ADD COLUMN is_active BOOLEAN DEFAULT TRUE,
ADD COLUMN emergency_contact VARCHAR(100);

Strategic Considerations When Adding Columns:

  • Default Values: New columns in existing tables need default values for existing rows
  • NULL Handling: Decide whether new columns should allow NULL values
  • Performance Impact: Adding columns to large tables can be time-consuming
  • Application Impact: Ensure applications can handle the new column structure

Modifying Existing Columns

Changing Data Types:

-- Increase the size of a varchar column
ALTER TABLE products 
MODIFY COLUMN product_description VARCHAR(500);

-- Change numeric precision
ALTER TABLE financial_records 
MODIFY COLUMN amount DECIMAL(15,2);

Adding and Removing Constraints:

-- Add a NOT NULL constraint
ALTER TABLE customers 
MODIFY COLUMN phone_number VARCHAR(20) NOT NULL;

-- Add a check constraint
ALTER TABLE products 
ADD CONSTRAINT chk_positive_price CHECK (price > 0);

-- Add a foreign key constraint
ALTER TABLE orders 
ADD CONSTRAINT fk_customer 
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

Renaming Columns and Tables:

-- Rename a column
ALTER TABLE employees 
RENAME COLUMN emp_id TO employee_id;

-- Rename a table
ALTER TABLE customer_info 
RENAME TO customers;

Complex Alterations

Adding Composite Constraints:

ALTER TABLE order_items
ADD CONSTRAINT unique_order_product 
UNIQUE (order_id, product_id);

Modifying Table Engine or Properties:

-- Change storage engine (MySQL example)
ALTER TABLE large_table ENGINE = InnoDB;

-- Add partitioning
ALTER TABLE sales_data 
PARTITION BY RANGE(YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

Best Practices for ALTER Operations

Planning Considerations:

  • Backup First: Always backup before major structural changes
  • Test in Development: Try alterations on a copy of production data
  • Consider Downtime: Large table alterations may lock the table
  • Monitor Performance: Check how changes affect query performance

Impact Assessment:

  • Application Compatibility: Ensure existing applications work with changes
  • Data Integrity: Verify that data remains consistent after changes
  • Index Impact: Consider whether existing indexes are still optimal
  • Storage Impact: Monitor disk space usage after alterations

DROP Statement

The DROP statement completely removes database objects. It’s like demolishing a building – once it’s done, the structure is gone forever.

Dropping Tables

Purpose: Completely removes a table and all its data from the database.

Basic Table Removal:

DROP TABLE temporary_data;

Conditional Dropping:

-- Only drop if the table exists (prevents errors)
DROP TABLE IF EXISTS old_customer_backup;

Understanding the Impact of DROP TABLE:

  • Data Loss: All data in the table is permanently deleted
  • Structure Loss: The table definition is completely removed
  • Dependency Issues: Cannot drop tables referenced by foreign keys
  • Index Removal: All indexes on the table are automatically removed
  • Permission Requirements: Usually requires higher privileges than other operations

Dropping Columns

Purpose: Remove specific columns from existing tables.

Column Removal Examples:

-- Remove a single column
ALTER TABLE employees 
DROP COLUMN temporary_notes;

-- Remove multiple columns
ALTER TABLE products 
DROP COLUMN old_category,
DROP COLUMN deprecated_field;

Considerations for Column Removal:

  • Data Loss: All data in dropped columns is permanently lost
  • Application Impact: Applications expecting these columns will fail
  • Index Dependencies: Indexes using dropped columns are automatically removed
  • Constraint Dependencies: Constraints involving dropped columns are removed

Dropping Indexes

Purpose: Remove indexes that are no longer needed or are hindering performance.

Index Removal Examples:

-- Drop a specific index
DROP INDEX idx_customer_phone ON customers;

-- Drop index if it exists
DROP INDEX IF EXISTS idx_temporary_lookup ON temp_table;

When to Drop Indexes:

  • Unused Indexes: Indexes that don’t improve any queries
  • Redundant Indexes: When a composite index covers single-column index functionality
  • Performance Issues: Indexes that slow down INSERT/UPDATE operations more than they help SELECT
  • Storage Concerns: When disk space is limited

Dropping Views

Purpose: Remove views that are no longer needed.

DROP VIEW customer_summary;

-- Conditional dropping
DROP VIEW IF EXISTS old_reporting_view;

Dropping Constraints

Purpose: Remove constraints that are no longer needed or need to be redefined.

-- Drop a foreign key constraint
ALTER TABLE orders 
DROP FOREIGN KEY fk_customer;

-- Drop a check constraint
ALTER TABLE products 
DROP CONSTRAINT chk_positive_price;

-- Drop a unique constraint
ALTER TABLE customers 
DROP INDEX unique_email;

Safety Considerations for DROP Operations

Pre-Drop Checklist:

  • Backup Strategy: Ensure recent backups exist
  • Dependency Check: Identify all objects that depend on what you’re dropping
  • Impact Analysis: Understand which applications or reports will be affected
  • Communication: Inform relevant stakeholders about the change

Recovery Planning:

  • Documentation: Keep records of dropped object definitions
  • Rollback Plan: Have a plan to recreate dropped objects if needed
  • Testing: Test application functionality after dropping objects

TRUNCATE Statement

The TRUNCATE statement removes all data from a table but keeps the table structure intact. It’s like emptying all the contents of a filing cabinet while keeping the cabinet and its folder structure.

Understanding TRUNCATE vs DELETE

TRUNCATE Characteristics:

  • Speed: Much faster than DELETE for large tables
  • Reset Identity: Resets auto-increment counters to starting values
  • No WHERE Clause: Cannot selectively remove rows
  • Minimal Logging: Less transaction log overhead
  • Cannot be Rolled Back: In many databases, TRUNCATE cannot be undone

When to Use TRUNCATE:

  • Complete Data Refresh: When reloading entire datasets
  • Testing Scenarios: Clearing test data between test runs
  • Staging Tables: Emptying temporary data processing tables
  • Performance Critical: When speed of data removal is important

TRUNCATE Examples:

-- Clear all data from a staging table
TRUNCATE TABLE sales_staging;

-- Clear test data
TRUNCATE TABLE test_customer_data;

TRUNCATE vs DELETE Comparison

Performance Differences:

-- DELETE: Row-by-row removal, can be slow for large tables
DELETE FROM large_transaction_table;

-- TRUNCATE: Deallocates data pages, much faster
TRUNCATE TABLE large_transaction_table;

Functional Differences:

  • DELETE can have WHERE clauses, TRUNCATE cannot
  • DELETE can be rolled back in transactions, TRUNCATE often cannot
  • DELETE triggers are fired, TRUNCATE triggers may not be
  • DELETE maintains identity seed values, TRUNCATE resets them

Practical Applications of TRUNCATE

Data Warehouse Scenarios:

-- Daily ETL process
-- 1. Truncate staging table
TRUNCATE TABLE daily_sales_staging;

-- 2. Load new data (using INSERT or LOAD DATA)
-- 3. Process and move to final tables

Testing and Development:

-- Reset test environment
TRUNCATE TABLE test_orders;
TRUNCATE TABLE test_customers;
TRUNCATE TABLE test_products;

Batch Processing:

-- Clear temporary processing table
TRUNCATE TABLE temp_calculation_results;

-- Perform batch calculations
-- Load results into permanent tables

Limitations and Considerations

When TRUNCATE Cannot Be Used:

  • Foreign Key References: Tables referenced by foreign keys cannot be truncated
  • Replicated Tables: Some replication setups don’t support TRUNCATE
  • Partitioned Tables: Behavior varies by database system
  • System Tables: Cannot truncate system or metadata tables

Safety Considerations:

  • No Undo: Once executed, data recovery requires backup restoration
  • Dependency Check: Ensure no foreign key constraints prevent operation
  • Timing: Execute during maintenance windows for production systems
  • Documentation: Record TRUNCATE operations for audit purposes

DDL Best Practices and Strategic Considerations

Schema Evolution Strategy

Versioning Database Changes:

  • Migration Scripts: Create scripts that can be applied incrementally
  • Rollback Plans: Always have a way to undo structural changes
  • Testing Pipeline: Test DDL changes in development environments first
  • Documentation: Keep detailed records of all schema changes

Change Management Process:

-- Example migration script structure
-- Version: 2024_03_15_add_customer_preferences
-- Description: Add customer preference tracking

-- Step 1: Add new columns
ALTER TABLE customers 
ADD COLUMN newsletter_opt_in BOOLEAN DEFAULT FALSE,
ADD COLUMN marketing_preferences JSON;

-- Step 2: Create supporting indexes
CREATE INDEX idx_newsletter_optin ON customers(newsletter_opt_in);

-- Step 3: Update existing data if needed
UPDATE customers 
SET newsletter_opt_in = TRUE 
WHERE email_notifications = 'enabled';

Performance Considerations

Index Strategy:

  • Monitor Usage: Regularly review which indexes are actually used
  • Composite Indexes: Design indexes that serve multiple query patterns
  • Maintenance Overhead: Balance query performance with update performance
  • Storage Impact: Monitor disk space usage of indexes

Table Design Principles:

  • Normalization Balance: Don’t over-normalize at the expense of performance
  • Data Types: Choose appropriate sizes to minimize storage overhead
  • Partitioning: Consider table partitioning for very large tables
  • Archiving Strategy: Plan for historical data management

Security and Access Control

DDL Permissions:

  • Principle of Least Privilege: Only grant DDL permissions to those who need them
  • Role-Based Access: Use database roles to manage permissions
  • Audit Trail: Log all DDL operations for security and compliance
  • Environment Separation: Strict controls on production DDL operations

Understanding DDL commands is crucial for effective database design and maintenance. These commands form the foundation for creating robust, scalable database systems that can evolve with changing business requirements while maintaining data integrity and performance.

← SQL Syntax and Datatypes Data Manipulation Language →

Track your progress

Mark this subtopic as completed when you finish reading.