SQL for Developers

Welcome to the SQL for Developers documentation! This comprehensive guide covers everything from SQL fundamentals to advanced optimization techniques across major database platforms.

What You'll Learn

This documentation covers:

• SQL Fundamentals: SELECT, INSERT, UPDATE, DELETE, JOINs, and subqueries
• Query Optimization: EXPLAIN plans, performance tuning, and best practices
• Indexing Strategies: B-tree, hash, GIN, GiST, and composite indexes
• Database-Specific Guides: PostgreSQL, MySQL, and MS SQL Server
• Database Design: Normalization, relationships, and schema design
• Transactions & ACID: Isolation levels, locking, and concurrency control

Prerequisites

Before diving in, you should have:

1. A database server installed (PostgreSQL, MySQL, or SQL Server)
2. Basic command line familiarity
3. Understanding of data types and programming concepts

Quick Navigation

Topic Overview

SQL Fundamentals

Master the core building blocks of SQL:

• Data Retrieval: SELECT statements with filtering and sorting
• Data Manipulation: INSERT, UPDATE, DELETE operations
• Joins: INNER, LEFT, RIGHT, FULL OUTER, and CROSS joins
• Aggregations: GROUP BY, HAVING, and aggregate functions
• Subqueries: Correlated and non-correlated subqueries
• CTEs: Common Table Expressions for readable queries

Query Optimization

Learn to write performant queries:

• Execution Plans: Reading and understanding EXPLAIN output
• Index Usage: Ensuring queries use indexes effectively
• Query Rewriting: Transforming slow queries into fast ones
• Statistics: How the query planner makes decisions
• Common Anti-patterns: Avoiding N+1 queries, SELECT *, etc.

Indexing Strategies

Understand when and how to create indexes:

• B-tree Indexes: Default index type for most queries
• Hash Indexes: Equality comparisons
• GIN Indexes: Full-text search and arrays (PostgreSQL)
• GiST Indexes: Geometric and spatial data
• Composite Indexes: Multi-column indexes and column order
• Partial Indexes: Indexing subsets of data

Database Platforms

Quick Start

PostgreSQL

# Connect to PostgreSQL
psql -U postgres -d mydatabase

# List all databases
\l

# Connect to a specific database
\c mydatabase

# List all tables
\dt

MySQL

# Connect to MySQL
mysql -u root -p

# List all databases
SHOW DATABASES;

# Use a specific database
USE mydatabase;

# List all tables
SHOW TABLES;

MS SQL Server

# Connect using sqlcmd
sqlcmd -S localhost -U sa -P 'YourPassword'

# List all databases
SELECT name FROM sys.databases;

# Use a specific database
USE mydatabase;

# List all tables
SELECT * FROM INFORMATION_SCHEMA.TABLES;

Performance Comparison

Best Practices

1. Use EXPLAIN: Always analyze query execution plans
2. Index Wisely: Don't over-index; each index has write overhead
3. **Avoid SELECT ***: Only select columns you need
4. Use Parameterized Queries: Prevent SQL injection
5. Monitor Slow Queries: Enable slow query logging
6. Regular Maintenance: VACUUM (PostgreSQL), OPTIMIZE (MySQL)

Next Steps