From Zero to Advanced Queries and Performance Tuning

Read before you buy: the PDF here is a free sample — the first 20 pages of the 460-page book (payments, sales and reviews on this demo site are sample data). Get the full e-book at thenexbooks on Gumroad.

Learning SELECT takes an afternoon. Getting SQL right — and fast — takes much longer.

The hard parts of SQL hide where beginners never look: NULL quietly changing a filter, a join that silently multiplies rows, an average that is wrong because of how it was grouped, a report that shifts by a day because of time zones, and a query that was instant in development and takes minutes in production.

This book takes you from your first query to advanced analysis and real performance tuning, with one realistic running example throughout: Spokeway, a bike-sharing network with members, stations, trips, payments, maintenance, and app events. It is written for both audiences who live in SQL every day — software developers and data analysts.

• 460 pages · 28 chapters + 4 appendices · practice exercises in every chapter
• SELECT, filtering, data types, NULL and three-valued logic, and functions
• Aggregation, every kind of join, subqueries, CTEs, and recursive queries
• Window functions: ranking, top-N per group, running totals, moving averages, LAG/LEAD
• Time series, time zones, cohorts, retention, funnels, sessions, and gaps-and-islands
• JSON, arrays, and full-text search
• Data modeling, constraints, upserts and MERGE, transactions, and concurrency
• Views, materialized views, functions, and procedures
• EXPLAIN and EXPLAIN ANALYZE, indexing (B-tree, composite, partial, GIN, BRIN), and query tuning
• SQL from application code, SQL injection, ORMs, N+1, and pagination
• PostgreSQL throughout, with dialect notes for MySQL, SQL Server, SQLite, and cloud warehouses

Table Of Contents

Part I — SQL Foundations

• Ch 1 · Why SQL Still Matters: Databases, Tables, and the Relational Model
• Ch 2 · Setting Up: PostgreSQL, the Spokeway Sample Database, and Your SQL Toolkit
• Ch 3 · Your First Queries: SELECT, WHERE, ORDER BY, and LIMIT
• Ch 4 · Data Types and NULL: Numbers, Text, Dates and Times, Booleans, and Three-Valued Logic
• Ch 5 · Expressions and Functions: CASE, String, Numeric, and Date/Time Functions

Part II — Combining and Summarizing Data

• Ch 6 · Aggregation: GROUP BY, HAVING, and Summary Statistics
• Ch 7 · Joins in Depth: INNER, OUTER, CROSS, Self Joins, and Join Pitfalls
• Ch 8 · Subqueries: Scalar, Correlated, IN, and EXISTS
• Ch 9 · Common Table Expressions and Recursive Queries
• Ch 10 · Set Operations, Conditional Aggregation, and Pivoting

Part III — Advanced Querying for Analysis

• Ch 11 · Window Functions I: Ranking, Numbering, and Top-N per Group
• Ch 12 · Window Functions II: Running Totals, Moving Averages, LAG/LEAD, and Frames
• Ch 13 · Dates and Time Series: Bucketing, Time Zones, Gaps, and Calendars
• Ch 14 · Analytical Patterns: Cohorts, Retention, Funnels, Sessions, and Gaps-and-Islands
• Ch 15 · Semi-Structured Data: JSON, Arrays, and Full-Text Search

Part IV — Designing and Changing Data

• Ch 16 · Data Modeling and Normalization for Developers and Analysts
• Ch 17 · Creating Tables: DDL, Keys, Constraints, Defaults, and Generated Columns
• Ch 18 · Changing Data: INSERT, UPDATE, DELETE, Upserts, and MERGE
• Ch 19 · Transactions, Isolation Levels, Locking, and Concurrency
• Ch 20 · Views, Materialized Views, Functions, and Stored Procedures

Part V — Performance Tuning

• Ch 21 · How Queries Run: The Planner, EXPLAIN, and EXPLAIN ANALYZE
• Ch 22 · Indexing Fundamentals: B-Tree Indexes and How the Planner Uses Them
• Ch 23 · Advanced Indexing: Composite, Covering, Partial, Expression, GIN, GiST, and BRIN
• Ch 24 · Query Tuning Patterns: Diagnosing and Rewriting Slow Queries
• Ch 25 · Large Tables: Statistics, Vacuum, Partitioning, and Batch Operations

Part VI — SQL in Practice

• Ch 26 · SQL from Application Code: Parameters, Injection, ORMs, N+1, and Pagination
• Ch 27 · SQL Across Databases: MySQL, SQL Server, SQLite, and Cloud Data Warehouses
• Ch 28 · Case Study: Building the Spokeway Operations and Analytics Database

Appendices

• App A · SQL Cheat Sheet
• App B · Templates and Function Reference
• App C · Production Checklist
• App D · Glossary

WHO IT IS FOR
Software developers who write SQL from application code, data analysts who query databases and warehouses, complete beginners (Part I starts from zero), and experienced users who want to master window functions, execution plans, and indexing.

FORMAT
PDF (A4), 460 pages, English, clickable table of contents and PDF bookmarks. 1st Edition, 2026. Published by thenexbooks.

This book is independent and is not affiliated with or endorsed by the PostgreSQL Global Development Group, Oracle, Microsoft, or any database vendor.

Book details