Leanpub Header

Skip to main content
Go to Leanpub.com

The Complete SQL Guide: From Beginner to Advanced

Master Structured Query Language for Database Development, Data Analysis, and Performance Engineering

The Complete SQL Guide: From Beginner to Advanced
This book is 100% completeLast updated on 2026-08-19
Steve Publications

Go from your first SQL query to solving real database problems with confidence. This hands-on guide covers everything from SQL fundamentals and data analysis to advanced queries, optimization and production best practices, with practical examples that work across today’s major database systems.

Minimum price

$19.00

$29.00

You pay

Author earns

$

Also available for 1 book credit with a Reader Membership

PDF
EPUB
WEB
APP
267
Pages
About

About

About the Book

This book takes you from zero database experience to professional-level SQL mastery through a carefully structured learning path. Using consistent real-world examples from two sample databases that run throughout the entire book, you will learn relational database fundamentals, core querying techniques, advanced analytical patterns, database programming, performance optimization, and production best practices. Every concept is explained with working code, practical scenarios, and attention to how SQL behaves across major database systems including PostgreSQL, MySQL, SQL Server, Oracle, and SQLite. This is both a structured learning resource and a long-term professional reference.

Share this book

Categories

SQLDatabasesComputers and Programming

Feedback

Email the Author

Author

About the Author

Steve Publications

Steve is a technology professional with more than 20 years of experience in software development, server infrastructure, cybersecurity, vulnerability research and reverse engineering. Throughout his career, he has designed, secured, analyzed and tested complex software and infrastructure, with a particular focus on understanding how systems fail and how they can be made more secure.

Outside of work, Steve enjoys sharing knowledge with the technology community. He collaborates with researchers, industry experts and technology professionals to write practical books covering software development, cybersecurity, cloud computing, networking, DevOps, artificial intelligence and enterprise technologies. His books focus on practical learning through clear explanations, real-world examples and hands-on exercises. With more than two decades of industry experience, his goal is to help IT professionals, students and technology enthusiasts build useful skills and stay current in a rapidly changing industry.

We believe readers deserve to know how our books are created. Most of our authors are not native English speakers, so we use AI to help translate, proofread manuscripts, fix grammar, improve sentence structure and make technical explanations easier to read. AI is used as an editing tool only. It does not replace the research, technical knowledge or hands-on experience behind our books. Some of our authors also prefer to remain anonymous for privacy or professional reasons. In those cases, we publish their work under a different name. The author's name may be different, but the quality of the content and our review process remain the same.

Every book is written, reviewed and maintained by experienced technology professionals, with contributions from our private technical community of more than 420 engineers and researchers. We spend far more time validating technical accuracy and keeping our content up to date than generating text. We are always interested in working with experienced professionals who have deep expertise in a particular technology or domain. If you would like to publish a book with us or help review an existing manuscript, we'd love to hear from you. Send us a message describing your area of expertise. We are especially interested in niche technologies, specialized skills and emerging topics that are underrepresented in existing technical literature.

If you look through the contents of our books, you'll see practical examples, detailed explanations and material that is regularly updated. Our goal is to publish books that professionals can actually rely on, not low-effort AI-generated content. If you ever feel that one of our books does not meet that standard, Leanpub offers a 60-day money-back guarantee. Feel free to request a refund if you are not satisfied with your purchase.

Contents

Table of Contents

Master Structured Query Language for Database Development, Data Analysis, and Performance Engineering

Introduction: Why SQL Matters

  1. What This Book Assumes About You
  2. How This Book Is Organized
  3. The Sample Databases
  4. Standard SQL Versus Vendor Differences
  5. How to Use This Book Effectively
  6. A First Glimpse: Your First Query

Chapter 1: Understanding the Relational Model

  1. From File Systems to Relational Databases: A Brief History
  2. What Is a Relation? Tables, Rows, and Columns Explained
  3. Entities, Attributes, and Relationships in the Real World
  4. The Core Principles of the Relational Model
  5. Schemas: Organizing Your Data Universe
  6. Chapter Summary

Chapter 2: Data Types, Keys, and Constraints

  1. Choosing the Right Data Type: Numbers, Strings, Dates, and More
  2. Primary Keys: Uniquely Identifying Every Row
  3. Foreign Keys: Linking Tables Together
  4. Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT
  5. Chapter Summary

Chapter 3: Database Design and Normalization

  1. Why Database Design Matters More Than You Think
  2. First Normal Form: Eliminating Repeating Groups
  3. Second and Third Normal Forms: Removing Dependencies
  4. Beyond 3NF: BCNF and Practical Limits
  5. When to Denormalize: Performance vs Integrity Trade-offs
  6. Common Design Patterns and Anti-Patterns
  7. Chapter Summary

Chapter 4: Creating and Managing Database Objects

  1. CREATE DATABASE and Schema Organization
  2. CREATE TABLE: Defining Your First Table
  3. ALTER TABLE: Evolving Your Schema Over Time
  4. DROP and TRUNCATE: Removing Data Objects
  5. Building the Northwind Analytics Database: Complete Schema
  6. Building the TechCorp HR Database: Complete Schema
  7. Chapter Summary

Chapter 5: Retrieving Data with SELECT

  1. Anatomy of a SELECT Statement
  2. Filtering Rows with WHERE: Comparison and Logical Operators
  3. Sorting Results with ORDER BY
  4. Limiting Output: TOP, LIMIT, and OFFSET Across Vendors
  5. Column Aliases and Readable Queries
  6. Handling NULL Values: IS NULL, COALESCE, and NULLIF
  7. DISTINCT and Duplicate Elimination
  8. Chapter Summary

Chapter 6: Expressions, Functions, and Conditional Logic

  1. Arithmetic and Comparison Expressions
  2. String Functions: Concatenation, Substrings, Trimming, Searching
  3. Date and Time Functions Across Major Databases
  4. Numeric and Mathematical Functions
  5. The CASE Expression: Conditional Logic in SQL
  6. Operator Precedence and Parentheses
  7. Practical Examples: Transforming and Enriching Data
  8. Chapter Summary

Chapter 7: Aggregation and Grouping

  1. Aggregate Functions: COUNT, SUM, AVG, MIN, MAX
  2. GROUP BY: Organizing Data into Meaningful Groups
  3. HAVING vs WHERE: Filtering Before and After Grouping
  4. Multiple Column Grouping and Hierarchical Aggregation
  5. ROLLUP, CUBE, and GROUPING SETS
  6. Common Aggregation Mistakes and How to Avoid Them
  7. Chapter Summary

Chapter 8: Joining Tables

  1. Why Joins Exist: Combining Related Data
  2. INNER JOIN: Matching Rows Across Tables
  3. LEFT, RIGHT, and FULL OUTER JOINs: Including Non-Matching Rows
  4. SELF JOIN: Comparing Rows Within the Same Table
  5. CROSS JOIN: Cartesian Products and When They’re Useful
  6. Joining Multiple Tables: Building Complex Queries
  7. Join Conditions vs Filters: WHERE vs ON Semantics
  8. Common Join Mistakes and Performance Tips
  9. Chapter Summary

Chapter 9: Subqueries and Derived Tables

  1. What Is a Subquery and Where Can It Go?
  2. Scalar Subqueries: Single Value Results
  3. Table Subqueries in the FROM Clause
  4. IN, ANY, ALL, and SOME: Set-Based Comparisons
  5. EXISTS and NOT EXISTS: Efficient Existence Checks
  6. Correlated Subqueries: When Inner Queries Reference Outer Rows
  7. Performance Considerations for Subqueries
  8. Chapter Summary

Chapter 10: Common Table Expressions and Recursive Queries

  1. What Is a Common Table Expression?
  2. Using CTEs to Structure Complex Queries
  3. Multiple CTEs: Building Step-by-Step Logic
  4. Recursive CTEs Explained: Anchor and Recursive Members
  5. Traversing Hierarchical Data: Org Charts and Category Trees
  6. Practical Patterns: Running Calculations and Gap Detection
  7. Chapter Summary

Chapter 11: Set Operations

  1. UNION and UNION ALL: Combining Result Sets Vertically
  2. INTERSECT: Finding Common Rows
  3. EXCEPT and MINUS: Set Difference Operations
  4. Set Operations vs Joins: Choosing the Right Tool
  5. Practical Scenarios: Data Comparison and Reconciliation
  6. Chapter Summary

Chapter 12: Window Functions and Analytical Queries

  1. What Are Window Functions? Beyond Aggregation
  2. The OVER Clause: PARTITION BY and ORDER BY
  3. Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE
  4. Value Functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE
  5. Aggregate Window Functions: Running Totals and Moving Averages
  6. Window Frames: ROWS, RANGE, and GROUPS
  7. Advanced Analytical Patterns: Gaps, Islands, and Trending
  8. Chapter Summary

Chapter 13: Views, Temporary Tables, and Materialized Views

  1. Views: Virtual Tables for Reusable Queries
  2. Creating and Using Temporary Tables
  3. Materialized Views: Precomputed Results
  4. Choosing Between Views, CTEs, and Temp Tables
  5. Performance Implications of Each Approach
  6. Chapter Summary

Chapter 14: Stored Procedures and User-Defined Functions

  1. Stored Procedures: Encapsulating Business Logic
  2. Parameters: Input, Output, and Default Values
  3. Control Flow: IF, CASE, Loops, and Cursors
  4. User-Defined Functions: Scalar and Table-Valued
  5. Error Handling: TRY-CATCH and Transaction Safety
  6. When to Use Procedural Code vs Pure SQL
  7. Chapter Summary

Chapter 15: Triggers and Automated Actions

  1. What Are Triggers and When Do They Fire?
  2. BEFORE, AFTER, and INSTEAD OF Triggers
  3. Row-Level vs Statement-Level Triggers
  4. Practical Use Cases: Auditing, Validation, and Cascading Updates
  5. The Hidden Costs of Triggers: Debugging and Performance
  6. Modern Alternatives to Trigger-Based Logic
  7. Chapter Summary

Chapter 16: Transactions, ACID, and Concurrency Control

  1. What Is a Transaction? The Concept of Atomic Work Units
  2. ACID Properties: Atomicity, Consistency, Isolation, Durability
  3. COMMIT, ROLLBACK, and SAVEPOINT
  4. Isolation Levels: From Read Uncommitted to Serializable
  5. Locking Mechanisms: Shared, Exclusive, and Intent Locks
  6. Deadlocks: Causes, Detection, and Prevention
  7. Practical Transaction Patterns for Real Applications
  8. Chapter Summary

Chapter 17: Indexes and Query Optimization

  1. How Indexes Work: The B-Tree Explained Simply
  2. Creating and Choosing Index Types
  3. Composite and Covering Indexes
  4. Reading Query Execution Plans
  5. Writing SARGable Queries: Patterns That Use Indexes
  6. Common Performance Anti-Patterns and Fixes
  7. Vendor-Specific Index Features
  8. Chapter Summary

Chapter 18: Advanced Performance and Large-Scale Data

  1. Table Partitioning: Horizontal and Vertical Strategies
  2. Query Statistics and the Optimizer’s Knowledge Base
  3. Connection Management and Pooling
  4. Caching Strategies at Database and Application Levels
  5. Handling Very Large Tables: Practical Techniques
  6. When Traditional SQL Databases Hit Their Limits
  7. Chapter Summary

Chapter 19: Security, Access Control, and Data Integrity

  1. Users, Roles, and Privileges: The Principle of Least Privilege
  2. Authentication Methods Across Major Databases
  3. SQL Injection: Understanding the Attack
  4. Preventing SQL Injection: Parameterized Queries and Best Practices
  5. Row-Level Security and Data Masking
  6. Encryption: At Rest and In Transit
  7. Chapter Summary

Chapter 20: Backup, Recovery, and Operational Excellence

  1. Backup Strategies: Full, Incremental, and Differential
  2. Point-in-Time Recovery Concepts
  3. Replication Basics: Master-Slave and Multi-Master
  4. Database Monitoring and Health Checks
  5. Routine Maintenance Tasks
  6. Schema Migration Best Practices
  7. Professional SQL Habits for Production Environments
  8. Chapter Summary

Conclusion: Your Path Forward with SQL

Appendix A: Quick Reference Guide

  1. Data Definition Language (DDL)
  2. Data Manipulation Language (DML)
  3. Common Functions
  4. Transaction Control Language (TCL)
  5. Data Control Language (DCL)

Appendix B: Vendor Comparison Cheat Sheet

  1. Auto-Incrementing Primary Keys
  2. Current Timestamp
  3. String Concatenation
  4. Pagination
  5. Date Truncation
  6. Boolean Type
  7. Window Functions Support
  8. Materialized Views

Appendix C: Troubleshooting Guide

  1. Common Errors and Fixes
  2. Debugging Techniques

References

Get the free sample chapters

Click the buttons to get the free sample in PDF or EPUB, or read the sample online here

Download Sample PDFDownload Sample EPUB

The Leanpub 60 Day 100% Happiness Guarantee

Within 60 days of purchase you can get a 100% refund on any Leanpub purchase, in two clicks.

See full terms...

Earn $8 on a $10 Purchase, and $16 on a $20 Purchase

We pay 80% royalties on purchases of $7.99 or more, and 80% royalties minus a 50 cent flat fee on purchases between $0.99 and $7.98. You earn $8 on a $10 sale, and $16 on a $20 sale. So, if we sell 5000 non-refunded copies of your book for $20, you'll earn $80,000.

(Yes, some authors have already earned much more than that on Leanpub.)

In fact, authors have earned over $15 million writing, publishing and selling on Leanpub.

Learn more about writing on Leanpub

Free Updates. DRM Free.

If you buy a Leanpub book, you get free updates for as long as the author updates the book! Many authors use Leanpub to publish their books in-progress, while they are writing them. All readers get free updates, regardless of when they bought the book or how much they paid (including free).

Most Leanpub books are available in PDF (for computers) and EPUB (for phones, tablets and Kindle). The formats that a book includes are shown at the top right corner of this page.

Finally, Leanpub books don't have any DRM copy-protection nonsense, so you can easily read them on any supported device.

Learn more about Leanpub's ebook formats and where to read them

Write and Publish on Leanpub

You can use Leanpub to easily write, publish and sell in-progress and completed ebooks and online courses!

Leanpub is a powerful platform for serious authors, combining a simple, elegant writing and publishing workflow with a store focused on selling in-progress ebooks.

Leanpub is a magical typewriter for authors: just write in plain text, and to publish your ebook, just click a button. (Or, if you are producing your ebook your own way, you can even upload your own PDF and/or EPUB files and then publish with one click!) It really is that easy.

Learn more about writing on Leanpub