SQL Basics & DDL

SQL (Structured Query Language) is the standard language for managing relational databases. DDL (Data Definition Language) defines and manages database structure.

SQL Command Categories

graph TB
    A[SQL Commands] --> B[DDL]
    A --> C[DML]
    A --> D[DQL]
    A --> E[DCL]
    A --> F[TCL]
    
    B --> B1[CREATE, ALTER, DROP]
    B --> B2[Define structure]
    
    C --> C1[INSERT, UPDATE, DELETE]
    C --> C2[Manipulate data]
    
    D --> D1[SELECT]
    D --> D2[Query data]
    
    E --> E1[GRANT, REVOKE]
    E --> E2[Control access]
    
    F --> F1[COMMIT, ROLLBACK]
    F --> F2[Manage transactions]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style D fill:#9C27B0
    style E fill:#F44336
    style F fill:#00BCD4

Key Points:

• DDL: Defines database objects (tables, indexes, schemas)
• DML: Manipulates data within tables
• DQL: Queries and retrieves data
• DCL: Controls user permissions and access
• TCL: Manages database transactions

Database Hierarchy

graph TB
    A[Database Server] --> B[Database 1]
    A --> C[Database 2]
    A --> D[Database 3]
    
    B --> E[Table 1]
    B --> F[Table 2]
    
    E --> G[Columns]
    E --> H[Rows]
    E --> I[Constraints]
    E --> J[Indexes]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style E fill:#FF9800
    style G fill:#9C27B0

Key Points:

• Server: Hosts multiple databases
• Database: Collection of related tables
• Table: Structured data in rows and columns
• Columns: Define data attributes and types
• Rows: Individual records with data values

Data Types Overview

graph LR
    A[SQL Data Types] --> B[Numeric]
    A --> C[String]
    A --> D[Date/Time]
    A --> E[Binary]
    A --> F[Other]
    
    B --> B1[INT, BIGINT, DECIMAL, FLOAT]
    C --> C1[VARCHAR, CHAR, TEXT]
    D --> D1[DATE, TIME, DATETIME, TIMESTAMP]
    E --> E1[BLOB, BINARY]
    F --> F1[BOOLEAN, ENUM, JSON]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style D fill:#9C27B0
    style E fill:#F44336
    style F fill:#00BCD4

Key Points:

• Numeric: INT for whole numbers, DECIMAL for precision, FLOAT for scientific
• String: VARCHAR for variable length, CHAR for fixed, TEXT for large content
• Date/Time: DATE for dates, DATETIME for timestamps, TIMESTAMP for auto-update
• Binary: BLOB for files, BINARY for fixed binary data
• Other: BOOLEAN for true/false, ENUM for predefined values, JSON for documents

CREATE TABLE Flow

sequenceDiagram
    participant User
    participant DB as Database
    participant Schema as Schema
    participant Storage as Storage
    
    User->>DB: CREATE TABLE statement
    DB->>DB: Parse SQL syntax
    DB->>Schema: Validate data types
    Schema->>Schema: Check constraints
    Schema->>Storage: Allocate space
    Storage->>DB: Create table structure
    DB->>User: Table created successfully

Key Points:

• Syntax Validation: SQL statement parsed and validated
• Data Types: Column types checked for validity
• Constraints: PRIMARY KEY, FOREIGN KEY, etc. validated
• Storage: Physical space allocated for table
• Metadata: Table definition stored in system catalog

ALTER TABLE Operations

flowchart TB
    A[ALTER TABLE] --> B[ADD]
    A --> C[MODIFY]
    A --> D[DROP]
    A --> E[RENAME]
    
    B --> B1[Add Column]
    B --> B2[Add Constraint]
    B --> B3[Add Index]
    
    C --> C1[Change Data Type]
    C --> C2[Change Size]
    C --> C3[Change NULL]
    
    D --> D1[Drop Column]
    D --> D2[Drop Constraint]
    D --> D3[Drop Index]
    
    E --> E1[Rename Table]
    E --> E2[Rename Column]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style D fill:#F44336
    style E fill:#9C27B0

Key Points:

• ADD: Add new columns, constraints, or indexes
• MODIFY: Change column properties (type, size, NULL)
• DROP: Remove columns, constraints, or indexes
• RENAME: Change table or column names
• Caution: Some operations may require table rebuild

DROP vs TRUNCATE vs DELETE

graph TB
    A[Remove Data Options] --> B[DROP]
    A --> C[TRUNCATE]
    A --> D[DELETE]
    
    B --> B1[Removes table structure]
    B --> B2[Cannot rollback]
    B --> B3[Fastest]
    
    C --> C1[Keeps structure]
    C --> C2[Removes all rows]
    C --> C3[Fast, resets identity]
    
    D --> D1[Keeps structure]
    D --> D2[Can use WHERE]
    D --> D3[Slower, can rollback]
    
    style B fill:#F44336
    style C fill:#FF9800
    style D fill:#4CAF50

Key Points:

• DROP: Removes entire table including structure, permanent
• TRUNCATE: Removes all rows, keeps structure, resets auto-increment
• DELETE: Removes specific rows, can use WHERE, supports rollback
• Performance: DROP fastest, TRUNCATE fast, DELETE slowest
• Recovery: Only DELETE can be rolled back in transaction

Table Constraints

graph LR
    A[Table Creation] --> B[Define Columns]
    B --> C[Add Constraints]
    
    C --> D[PRIMARY KEY]
    C --> E[FOREIGN KEY]
    C --> F[UNIQUE]
    C --> G[NOT NULL]
    C --> H[CHECK]
    C --> I[DEFAULT]
    
    D --> J[Unique identifier]
    E --> J
    F --> J
    G --> J
    H --> J
    I --> J
    
    J --> K[Data Integrity]
    
    style A fill:#2196F3
    style C fill:#4CAF50
    style K fill:#FF9800

Key Points:

• PRIMARY KEY: Unique, non-NULL identifier for each row
• FOREIGN KEY: Links tables, ensures referential integrity
• UNIQUE: No duplicate values allowed
• NOT NULL: Column must have a value
• CHECK: Validates data against condition
• DEFAULT: Provides default value when none specified

DDL Best Practices

flowchart TB
    A[DDL Best Practices] --> B[Naming]
    A --> C[Data Types]
    A --> D[Constraints]
    A --> E[Documentation]
    
    B --> B1[Lowercase with underscores]
    B --> B2[Descriptive names]
    
    C --> C1[Appropriate size]
    C --> C2[Avoid over-sizing]
    
    D --> D1[Always use PRIMARY KEY]
    D --> D2[Add FOREIGN KEYs]
    D --> D3[Use NOT NULL]
    
    E --> E1[Comment tables]
    E --> E2[Document relationships]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style D fill:#9C27B0
    style E fill:#00BCD4

Key Points:

• Naming: Use clear, consistent naming conventions
• Data Types: Choose appropriate types and sizes
• Constraints: Enforce data integrity at database level
• Documentation: Comment complex structures
• Testing: Test DDL changes in development first

Code Examples

CREATE DATABASE

-- Create database
CREATE DATABASE company_db;

-- Use database
USE company_db;

-- Create with character set
CREATE DATABASE company_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

CREATE TABLE

-- Basic table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    hire_date DATE NOT NULL,
    salary DECIMAL(10,2) CHECK (salary > 0),
    department_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table with foreign key
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'shipped', 'delivered') DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Composite primary key
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

ALTER TABLE

-- Add column
ALTER TABLE employees
ADD COLUMN phone VARCHAR(20);

-- Modify column
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(12,2);

-- Add constraint
ALTER TABLE employees
ADD CONSTRAINT fk_department 
    FOREIGN KEY (department_id) REFERENCES departments(department_id);

-- Drop column
ALTER TABLE employees
DROP COLUMN phone;

-- Rename table
ALTER TABLE employees RENAME TO staff;

-- Add index
ALTER TABLE employees
ADD INDEX idx_email (email);

DROP Operations

-- Drop table
DROP TABLE IF EXISTS temp_table;

-- Drop database
DROP DATABASE IF EXISTS old_database;

-- Truncate table (remove all rows, keep structure)
TRUNCATE TABLE logs;

-- Delete with condition (can rollback)
DELETE FROM orders WHERE order_date < '2020-01-01';

Data Types Examples

CREATE TABLE data_types_demo (
    -- Numeric
    id INT PRIMARY KEY AUTO_INCREMENT,
    age TINYINT,
    salary DECIMAL(10,2),
    rating FLOAT,
    
    -- String
    name VARCHAR(100),
    code CHAR(10),
    description TEXT,
    
    -- Date/Time
    birth_date DATE,
    login_time TIME,
    created_at DATETIME,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Other
    is_active BOOLEAN DEFAULT TRUE,
    status ENUM('active', 'inactive', 'pending'),
    metadata JSON
);

Best Practices

1. Naming Conventions: Use lowercase with underscores (snake_case)
2. Primary Keys: Every table should have a primary key
3. Data Types: Choose appropriate types and sizes
4. NOT NULL: Use for required fields
5. Foreign Keys: Maintain referential integrity
6. Indexes: Index foreign keys and frequently queried columns
7. Documentation: Comment tables and complex structures
8. Testing: Test DDL changes in development environment first

---