SQL Fundamentals

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. It allows you to create, read, update, and delete data.

Database Hierarchy

graph TB
    A[Database Server] --> B[Database 1]
    A --> C[Database 2]
    B --> D[Table 1]
    B --> E[Table 2]
    D --> F[Rows/Records]
    D --> G[Columns/Fields]
    F --> H[Data Values]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style D fill:#FF9800
    style F fill:#9C27B0

Key Points:

• Server: Hosts multiple databases
• Database: Collection of related tables
• Table: Structured data in rows and columns
• Row: Single record with multiple field values
• Column: Specific attribute/field across all records

SQL Command Categories

graph LR
    A[SQL Commands] --> B[DDL]
    A --> C[DML]
    A --> D[DQL]
    A --> E[DCL]
    A --> F[TCL]
    
    B --> B1[CREATE, ALTER, DROP]
    C --> C1[INSERT, UPDATE, DELETE]
    D --> D1[SELECT]
    E --> E1[GRANT, REVOKE]
    F --> F1[COMMIT, ROLLBACK]
    
    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: Define database structure (tables, schemas)
• DML: Manipulate data (insert, update, delete)
• DQL: Query and retrieve data (select)
• DCL: Control access permissions
• TCL: Manage transactions (commit, rollback)

Data Types Overview

graph TB
    A[SQL Data Types] --> B[Numeric]
    A --> C[String]
    A --> D[Date/Time]
    A --> E[Boolean]
    
    B --> B1[INT, BIGINT, DECIMAL]
    C --> C1[VARCHAR, CHAR, TEXT]
    D --> D1[DATE, TIME, DATETIME]
    E --> E1[BOOLEAN, BIT]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style D fill:#9C27B0
    style E fill:#F44336

Key Points:

• Numeric: INT for whole numbers, DECIMAL for precision
• String: VARCHAR for variable length, CHAR for fixed
• Date/Time: DATE for dates, DATETIME for timestamps
• Boolean: TRUE/FALSE values
• Choice: Select based on data requirements and storage

JOIN Types Visualization

graph TB
    A[SQL JOINs] --> B[INNER JOIN]
    A --> C[LEFT JOIN]
    A --> D[RIGHT JOIN]
    A --> E[FULL OUTER JOIN]
    A --> F[CROSS JOIN]
    
    B --> B1[Matching rows only]
    C --> C1[All left + matching right]
    D --> D1[All right + matching left]
    E --> E1[All rows from both]
    F --> F1[Cartesian product]
    
    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:

• INNER JOIN: Returns only matching rows from both tables
• LEFT JOIN: All rows from left table, matching from right
• RIGHT JOIN: All rows from right table, matching from left
• FULL OUTER JOIN: All rows from both tables
• CROSS JOIN: Every row from first with every row from second

Query Execution Order

flowchart TB
    A[FROM] --> B[JOIN]
    B --> C[WHERE]
    C --> D[GROUP BY]
    D --> E[HAVING]
    E --> F[SELECT]
    F --> G[DISTINCT]
    G --> H[ORDER BY]
    H --> I[LIMIT/OFFSET]
    
    style A fill:#4CAF50
    style C fill:#FF9800
    style D fill:#9C27B0
    style F fill:#2196F3
    style H fill:#F44336

Key Points:

• FROM/JOIN: Identify and combine tables first
• WHERE: Filter rows before grouping
• GROUP BY: Group rows for aggregation
• HAVING: Filter groups after aggregation
• SELECT: Choose columns to return
• ORDER BY: Sort final result set

Constraint Types

graph LR
    A[Constraints] --> B[PRIMARY KEY]
    A --> C[FOREIGN KEY]
    A --> D[UNIQUE]
    A --> E[NOT NULL]
    A --> F[CHECK]
    A --> G[DEFAULT]
    
    B --> B1[Unique identifier]
    C --> C1[Referential integrity]
    D --> D1[No duplicates]
    E --> E1[Required value]
    F --> F1[Value validation]
    G --> G1[Default value]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800

Key Points:

• PRIMARY KEY: Uniquely identifies each row, cannot be NULL
• FOREIGN KEY: Links tables, ensures referential integrity
• UNIQUE: Ensures no duplicate values in column
• NOT NULL: Column must have a value
• CHECK: Validates data against condition
• DEFAULT: Provides default value if none specified

Transaction Flow

sequenceDiagram
    participant App as Application
    participant DB as Database
    participant Trans as Transaction
    
    App->>Trans: BEGIN TRANSACTION
    App->>DB: SQL Statement 1
    App->>DB: SQL Statement 2
    App->>DB: SQL Statement 3
    
    alt All Success
        App->>Trans: COMMIT
        Trans->>DB: Save Changes
        DB->>App: Success
    else Any Failure
        App->>Trans: ROLLBACK
        Trans->>DB: Undo Changes
        DB->>App: Reverted
    end

Key Points:

• Atomicity: All operations succeed or all fail
• Consistency: Database remains in valid state
• Isolation: Transactions don't interfere with each other
• Durability: Committed changes are permanent
• SAVEPOINT: Create checkpoint for partial rollback

Index Performance

graph TB
    A[Query Without Index] --> B[Full Table Scan]
    B --> C[Check Every Row]
    C --> D[Slow O n]
    
    E[Query With Index] --> F[Index Lookup]
    F --> G[Direct Row Access]
    G --> H[Fast O log n]
    
    style A fill:#F44336
    style D fill:#F44336
    style E fill:#4CAF50
    style H fill:#4CAF50

Key Points:

• Without Index: Scans entire table sequentially
• With Index: Uses B-tree for fast lookup
• Trade-off: Faster reads, slower writes
• Use Cases: Frequently queried columns, JOIN keys
• Avoid: Low cardinality columns, frequently updated columns

Code Examples

DDL - Table Creation

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,
    salary DECIMAL(10,2) CHECK (salary > 0),
    hire_date DATE DEFAULT CURRENT_DATE,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

DML - Data Manipulation

-- INSERT
INSERT INTO employees (first_name, last_name, email, salary)
VALUES ('John', 'Doe', '[email protected]', 50000);

-- UPDATE
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 5;

-- DELETE
DELETE FROM employees
WHERE hire_date < '2020-01-01';

DQL - Queries with Aggregation

-- Basic SELECT with WHERE
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000
ORDER BY salary DESC;

-- GROUP BY with HAVING
SELECT department_id, AVG(salary) as avg_salary, COUNT(*) as emp_count
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 60000;

JOINs

-- INNER JOIN
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- LEFT JOIN
SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

Subqueries

-- Subquery in WHERE
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated subquery
SELECT e1.first_name, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

Transactions

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- If all successful
COMMIT;

-- If error occurs
ROLLBACK;

Window Functions

SELECT 
    employee_id,
    first_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as rank,
    AVG(salary) OVER (PARTITION BY department_id) as dept_avg
FROM employees;

Best Practices

1. Use Indexes: Index frequently queried columns and foreign keys
2. **Avoid SELECT ***: Specify only needed columns
3. Use Transactions: Ensure data consistency for related operations
4. Parameterize Queries: Prevent SQL injection attacks
5. Normalize Data: Reduce redundancy with proper table design
6. Use Constraints: Enforce data integrity at database level
7. Optimize JOINs: Use appropriate JOIN types and indexes
8. Monitor Performance: Use EXPLAIN to analyze query execution

---