SQL Guide | Complete SQL Tutorial & Reference

Introduction

Welcome to the most comprehensive SQL Guide. SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. Whether you're a data analyst, backend developer, or database administrator, mastering SQL is essential for working with data in the modern world.

1974

Year SQL Created

Major JOIN Types

Normalization Forms

∞

Applications

This guide covers everything from basic SELECT statements to advanced window functions, database normalization, and performance optimization. By the end, you'll be able to write efficient, secure SQL queries for any relational database system.

What You'll Learn

This guide covers SQL fundamentals, DDL (CREATE, ALTER, DROP), DML (INSERT, UPDATE, DELETE), DQL (SELECT with filtering, sorting, grouping), JOINs, subqueries, CTEs, window functions, database normalization, indexes, SQL security, common mistakes, and real-world worked examples.

What is SQL?

SQL (Structured Query Language) is a domain-specific programming language designed for managing data held in a relational database management system (RDBMS). It was originally developed at IBM in the 1970s and has since become an ANSI and ISO standard.

Major SQL Dialects

MySQL

Open-source, widely used for web applications. Popular with PHP, WordPress.

Best for: Web apps, small-medium databases

PostgreSQL

Advanced open-source RDBMS with strong SQL compliance and extensibility.

Best for: Complex queries, GIS, analytics

SQL Server

Microsoft's enterprise RDBMS with deep integration with .NET ecosystem.

Best for: Enterprise, Windows environments

Oracle

Enterprise-grade RDBMS known for scalability and reliability.

Best for: Large enterprises, banking

SQLite

Serverless, file-based database. Perfect for mobile and embedded apps.

Best for: Mobile apps, testing, small projects

BigQuery / Redshift

Cloud data warehouses designed for analytics at massive scale.

Best for: Big data, analytics, BI

SQL is Universal

While each database has its own dialect, the core SQL syntax is standardized. If you learn SQL for MySQL, you can easily adapt to PostgreSQL, SQL Server, or Oracle. The differences are mostly in advanced features and specific functions.

SQL Basics (SELECT, FROM, WHERE)

The foundation of SQL is the SELECT statement, which retrieves data from one or more tables. Every SQL query you write will build on these core concepts.

Basic SELECT Syntax

SELECT column1, column2 FROM table_name WHERE condition;

Core SELECT Clauses

Clause	Purpose	Example
SELECT	Specifies which columns to retrieve	SELECT name, age
FROM	Specifies the table(s) to query	FROM users
WHERE	Filters rows based on conditions	WHERE age > 18
ORDER BY	Sorts the result set	ORDER BY age DESC
LIMIT	Limits number of rows returned	LIMIT 10

Basic Query Examples

-- Select all columns from users table
SELECT * FROM users;

-- Select specific columns
SELECT name, email, created_at FROM users;

-- Filter with WHERE
SELECT name, age FROM users WHERE age >= 18;

-- Multiple conditions with AND/OR
SELECT * FROM products 
WHERE price < 100 AND category = 'Electronics';

-- Sort and limit
SELECT name, price FROM products 
ORDER BY price DESC LIMIT 10;
        

WHERE Clause Operators

Operator	Description	Example
=	Equal to	WHERE status = 'active'
<>, !=	Not equal to	WHERE age <> 0
<, >, <=, >=	Comparison operators	WHERE price > 50
BETWEEN	Range (inclusive)	WHERE age BETWEEN 18 AND 65
IN	Matches any value in list	WHERE country IN ('US', 'UK')
LIKE	Pattern matching (% and _)	WHERE name LIKE 'John%'
IS NULL	Check for NULL values	WHERE email IS NULL

Data Definition Language (DDL)

DDL is used to define and modify the structure of database objects like tables, indexes, and schemas. DDL commands are auto-committed (cannot be rolled back in most databases).

CREATE TABLE

-- Create a new table with constraints
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    age INT CHECK (age >= 13),
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email)
);
        

Common Data Types

Category	Data Type	Use Case
Integer	INT, BIGINT, SMALLINT	IDs, counts, quantities
Decimal	DECIMAL(p,s), FLOAT	Money, precise measurements
String	VARCHAR(n), TEXT	Names, emails, descriptions
Date/Time	DATE, DATETIME, TIMESTAMP	Birthdays, created_at
Boolean	BOOLEAN, BOOL	Flags, true/false values
JSON	JSON, JSONB	Flexible structured data

ALTER and DROP

-- Add a new column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Modify an existing column
ALTER TABLE users MODIFY COLUMN email VARCHAR(150);

-- Drop a column
ALTER TABLE users DROP COLUMN phone;

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

-- Drop a table (destructive!)
DROP TABLE IF EXISTS temp_data;
        

DDL is Destructive

Be extremely careful with DROP commands! DROP TABLE and DROP DATABASE permanently delete data and structure. Always backup your database before running DDL commands in production.

Data Manipulation Language (DML)

DML is used to modify the data within tables. Unlike DDL, DML operations can be rolled back using transactions.

INSERT

-- Insert a single row
INSERT INTO users (username, email, password_hash, age)
VALUES ('johndoe', 'john@example.com', 'hashed_pw', 25);

-- Insert multiple rows
INSERT INTO users (username, email, password_hash, age) VALUES
    ('alice', 'alice@example.com', 'hash1', 28),
    ('bob', 'bob@example.com', 'hash2', 32),
    ('carol', 'carol@example.com', 'hash3', 24);

-- Insert from another table
INSERT INTO archived_users SELECT * FROM users WHERE status = 'inactive';
        

UPDATE

-- Update specific rows (always use WHERE!)
UPDATE users 
SET status = 'banned', updated_at = NOW()
WHERE username = 'spammer123';

-- Update multiple columns
UPDATE products 
SET price = price * 1.10,
    stock = stock - 1
WHERE id = 42;
        

DELETE

-- Delete specific rows
DELETE FROM users WHERE status = 'banned';

-- Delete with JOIN (MySQL syntax)
DELETE o FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.deleted_at IS NOT NULL;

-- TRUNCATE (faster, resets auto-increment)
TRUNCATE TABLE temp_logs;
        

UPDATE/DELETE Without WHERE

Never run UPDATE or DELETE without a WHERE clause in production! It will affect ALL rows in the table. Always test with SELECT first to verify which rows will be affected.

Data Query Language (DQL)

DQL is the heart of SQL—used to retrieve and analyze data. The SELECT statement with its various clauses gives you powerful tools for data analysis.

Aggregate Functions

Function	Description	Example
COUNT()	Count rows	COUNT(*) or COUNT(column)
SUM()	Sum of values	SUM(price)
AVG()	Average value	AVG(rating)
MIN() / MAX()	Minimum / Maximum	MAX(salary)

GROUP BY and HAVING

-- Group by category and count products
SELECT category, COUNT(*) AS product_count, 
       AVG(price) AS avg_price
FROM products
WHERE status = 'active'
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY product_count DESC;
        

Execution Order of SQL Clauses

FROM / JOIN

Tables are identified and joined first

WHERE

Rows are filtered before aggregation

GROUP BY

Rows are grouped into buckets

HAVING

Groups are filtered after aggregation

SELECT

Columns are selected and expressions evaluated

ORDER BY / LIMIT

Results are sorted and limited last

SQL JOINs

JOINs combine rows from two or more tables based on a related column. They are essential for working with normalized databases.

Types of JOINs

JOIN Type	Behavior	Use Case
INNER JOIN	Returns only matching rows from both tables	Find users with orders
LEFT JOIN	All rows from left, matches from right (NULL if no match)	All users, even without orders
RIGHT JOIN	All rows from right, matches from left	Rarely used (use LEFT instead)
FULL OUTER JOIN	All rows from both tables	Complete reconciliation
CROSS JOIN	Cartesian product (all combinations)	Generate all combinations
SELF JOIN	Table joined with itself	Employee-manager hierarchy

JOIN Examples

-- INNER JOIN: Get users with their orders
SELECT u.name, o.order_date, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: All users, even without orders
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- SELF JOIN: Find employees and their managers
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
        

JOIN Performance

Always index the columns used in JOIN conditions! Foreign key columns should be indexed for optimal JOIN performance. Without indexes, the database must perform full table scans.

Advanced Queries

Once you master the basics, these advanced SQL features will unlock powerful data analysis capabilities.

Subqueries

-- Scalar subquery (returns single value)
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated subquery (references outer query)
SELECT e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary) 
    FROM employees e2 
    WHERE e2.department = e1.department
);

-- Subquery in FROM (derived table)
SELECT dept, avg_sal FROM (
    SELECT department AS dept, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department
) AS dept_stats
WHERE avg_sal > 50000;
        

Common Table Expressions (CTEs)

-- Basic CTE (WITH clause)
WITH high_earners AS (
    SELECT id, name, salary, department
    FROM employees
    WHERE salary > 80000
)
SELECT department, COUNT(*) AS count
FROM high_earners
GROUP BY department;

-- Recursive CTE (for hierarchical data)
WITH RECURSIVE org_tree AS (
    -- Base case: top-level managers
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: employees under managers
    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e
    INNER JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree;
        

Window Functions

-- ROW_NUMBER: Assign sequential numbers
SELECT name, salary, department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

-- RANK and DENSE_RANK
SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

-- Running total with SUM() OVER
SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- Moving average
SELECT date, price,
       AVG(price) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS 7_day_avg
FROM stock_prices;
        

Window Functions vs GROUP BY

Window functions let you perform aggregations without collapsing rows! Unlike GROUP BY, window functions preserve the original rows while adding aggregate information. This is perfect for rankings, running totals, and moving averages.

Database Normalization

Normalization is the process of organizing data to reduce redundancy and improve data integrity. It involves dividing large tables into smaller, related tables.

The Normal Forms

1NF

First Normal Form

Atomic values (no repeating groups), each column contains single values, unique rows

2NF

Second Normal Form

Must be in 1NF + no partial dependencies (all non-key columns depend on full primary key)

3NF

Third Normal Form

Must be in 2NF + no transitive dependencies (non-key columns don't depend on other non-key columns)

Example: Denormalized vs Normalized

-- ❌ Denormalized (violates 2NF and 3NF)
CREATE TABLE orders_bad (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),  -- Repeated for each order
    customer_email VARCHAR(100), -- Repeated for each order
    product_name VARCHAR(100),   -- Repeated for each order
    product_price DECIMAL(10,2), -- Transitive dependency
    quantity INT
);

-- ✅ Normalized (3NF)
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    order_date TIMESTAMP
);

CREATE TABLE order_items (
    order_id INT REFERENCES orders(id),
    product_id INT REFERENCES products(id),
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);
        

When to Denormalize

Normalization is for data integrity; denormalization is for performance. In data warehouses and analytics, intentional denormalization (star schema, snowflake schema) is common to speed up read-heavy queries. The key is knowing when each approach is appropriate.

Indexes & Performance

Indexes are data structures that speed up data retrieval operations on a table. Think of them like a book's index—they let the database find rows without scanning the entire table.

Types of Indexes

Index Type	Structure	Best For
B-Tree	Balanced tree	Equality and range queries (default)
Hash	Hash table	Exact match only (very fast)
Full-Text	Inverted index	Text search, LIKE queries
GiST / GIN	Generalized search tree	Geometric data, arrays (PostgreSQL)

Creating and Managing Indexes

-- Create an index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (multiple columns)
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Drop an index
DROP INDEX idx_users_email;

-- Analyze query performance (PostgreSQL)
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
        

Indexing Best Practices

Index columns used in WHERE clauses frequently
Index foreign key columns for JOIN performance
Don't over-index—each index slows down INSERT/UPDATE/DELETE
Use composite indexes for queries filtering on multiple columns
Order matters in composite indexes—most selective column first
Monitor slow queries and add indexes where needed

Index Trade-offs

Indexes speed up reads but slow down writes. Every INSERT, UPDATE, or DELETE must also update all indexes on that table. For write-heavy tables (like logs), be selective about which columns to index.

SQL Security

SQL injection is one of the most common and dangerous web application vulnerabilities. Understanding SQL security is critical for any developer.

SQL Injection Attack

-- ❌ Vulnerable code (string concatenation)
-- User input: ' OR '1'='1
SELECT * FROM users 
WHERE username = '' OR '1'='1' AND password = 'anything';
-- Returns ALL users! Authentication bypass!
        

Prevention: Parameterized Queries

-- ✅ Safe: Use parameterized queries
-- Python example with psycopg2
cursor.execute(
    "SELECT * FROM users WHERE username = %s AND password_hash = %s",
    (username, password_hash)
);

-- ✅ Safe: Use prepared statements
-- Node.js with pg
const result = await pool.query(
    'SELECT * FROM users WHERE id = $1',
    [userId]
);

-- ✅ Safe: Use ORM query builders
-- Prisma, Sequelize, SQLAlchemy automatically parameterize
        

Additional Security Best Practices

Use parameterized queries for ALL user input
Principle of least privilege—give database accounts minimal permissions
Hash passwords with bcrypt, Argon2, or PBKDF2 (never store plain text)
Encrypt sensitive data at rest and in transit (TLS/SSL)
Regular security audits and penetration testing
Keep database software updated with security patches

Never Trust User Input

Always assume user input is malicious. Even if you validate on the frontend, attackers can bypass that. Always use parameterized queries or prepared statements on the backend—no exceptions.

Common Mistakes

Even experienced developers make these SQL mistakes. Being aware of them will save you hours of debugging.

SELECT * in Production

Using SELECT * retrieves unnecessary columns and breaks when schema changes.

Fix: Always specify columns explicitly.

WHERE on Aggregated Columns

Using WHERE with aggregate functions (WHERE COUNT(*) > 5).

Fix: Use HAVING for aggregate conditions.

Implicit Type Conversion

Comparing VARCHAR to INT causes slow implicit conversions.

Fix: Use matching data types in comparisons.

Missing Indexes on JOINs

JOINs without indexes on foreign keys cause full table scans.

Fix: Index all foreign key columns.

N+1 Query Problem

Looping to query related data one row at a time.

Fix: Use JOINs or batch queries.

NULL Comparison Mistakes

Using = NULL instead of IS NULL (always returns unknown).

Fix: Use IS NULL or IS NOT NULL.

Worked Examples

Let's apply these concepts to real-world scenarios.

Example 1: E-commerce Sales Report

Problem: Get monthly sales totals by category for 2026, showing only categories with > $10,000 in sales.

SELECT 
    DATE_FORMAT(o.order_date, '%Y-%m') AS month,
    c.name AS category,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(oi.quantity * oi.unit_price) AS total_sales
FROM orders o
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
INNER JOIN categories c ON p.category_id = c.id
WHERE o.order_date >= '2026-01-01' 
  AND o.order_date < '2027-01-01'
  AND o.status = 'completed'
GROUP BY month, c.name
HAVING total_sales > 10000
ORDER BY month, total_sales DESC;
        

Example 2: Top 3 Employees per Department

Problem: Find the top 3 highest-paid employees in each department using window functions.

WITH ranked AS (
    SELECT 
        name, 
        department, 
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department 
            ORDER BY salary DESC
        ) AS rank
    FROM employees
)
SELECT department, name, salary, rank
FROM ranked
WHERE rank <= 3
ORDER BY department, rank;
        

Example 3: Customer Retention Analysis

Problem: Find customers who made their first purchase in 2025 and their second purchase within 90 days.

WITH first_purchase AS (
    SELECT customer_id, MIN(order_date) AS first_date
    FROM orders
    GROUP BY customer_id
    HAVING first_date >= '2025-01-01'
),
second_purchase AS (
    SELECT o.customer_id, o.order_date
    FROM orders o
    INNER JOIN first_purchase fp ON o.customer_id = fp.customer_id
    WHERE o.order_date > fp.first_date
      AND o.order_date <= fp.first_date + INTERVAL 90 DAY
)
SELECT 
    COUNT(DISTINCT fp.customer_id) AS new_customers,
    COUNT(DISTINCT sp.customer_id) AS retained_customers,
    ROUND(100.0 * COUNT(DISTINCT sp.customer_id) / 
           COUNT(DISTINCT fp.customer_id), 2) AS retention_rate
FROM first_purchase fp
LEFT JOIN second_purchase sp ON fp.customer_id = sp.customer_id;
        

Example 4: Running Total with Window Function

Problem: Calculate daily revenue and 7-day moving average for a dashboard.

SELECT 
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY order_date) AS cumulative_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS 7_day_moving_avg
FROM (
    SELECT 
        DATE(order_date) AS order_date,
        SUM(total_amount) AS daily_revenue
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL 30 DAY
    GROUP BY DATE(order_date)
) daily_stats
ORDER BY order_date;
        

Query Optimization Tips

Always use EXPLAIN to understand query execution plans. Look for full table scans, missing indexes, and inefficient joins. Optimize the slowest parts first—premature optimization is the root of all evil.

Conclusion

SQL is one of the most valuable skills in the tech industry. From simple SELECT queries to complex window functions and database design, the concepts in this guide form the foundation of data management. Whether you're building a web application, analyzing business data, or designing a data warehouse, SQL is your essential tool.

Key Takeaways

Master the basics first: SELECT, FROM, WHERE, ORDER BY, LIMIT
Understand JOINs: They're essential for working with normalized databases
Use CTEs for readability: WITH clauses make complex queries easier to understand
Window functions are powerful: Rankings, running totals, moving averages
Normalize your data: Reduce redundancy, improve integrity
Index strategically: Speed up reads without killing writes
Never trust user input: Always use parameterized queries
Use EXPLAIN: Understand query performance before optimizing

In the world of data, SQL is the universal language. Master it, and you unlock the ability to ask any question of any dataset, no matter how large or complex.

— Database Wisdom

Start Querying

Ready to write better SQL? Explore our suite of free SQL tools below to format, validate, and optimize your queries. From beginners to experts, we've got the tools to help you master SQL. Happy querying! 🗄️🚀✨