Practical SQL: A Beginner's Guide to Storytelling with Data

By Anthony DeBarros
Practical SQL: A Beginner's Guide to Storytelling with Data — book cover Get Your Copy

★★★★★ Rated 4.7 out of 5 stars on Amazon

"One of the most interesting books about SQL I've ever read. You won't find any boring lectures – only the essence of SQL and real examples."
— Jakub Romanowski, LearnSQL.com

“Practical SQL will take you from an absolute databases beginner towards becoming a database and SQL master. This book is one of those books that you feel like [you're] reading a novel, not a technical book. It uses an easy to follow and [understandable] approach that makes it easy to read for people from all backgrounds.”
— Sara A. Metwalli, Towards Data Science

"If you are a beginner, I’d highly recommend this book. It’s easy to understand, practical, and takes you from the basics to the more advanced stuff."
— Tom Corbin, "The 2024 Guide to Becoming a Data Engineer"

"Ever so slightly more fun and more engaging [than Learning SQL from O'Reilly]."
— Giles McMullen-Klein, "Python Programmer" on YouTube

A recommended SQL resource in "R for Data Science (2e)" by Hadley Wickham, Mine Çetinkaya-Rundel & Garrett Grolemund!

About the Book

Whether you're new to SQL or looking to sharpen your skills, Practical SQL gives you the tools to explore, analyze, and tell stories with data. Author Anthony DeBarros guides you from the fundamentals — counting, sorting, and filtering — through advanced techniques in statistics, full-text search, and GIS, all using the free and widely-used PostgreSQL database.

The SQL syntax and concepts covered apply broadly to other database systems including MySQL, Oracle, and SQLite (note that Microsoft SQL Server uses a variant called T-SQL, which this book does not cover). Read more from No Starch Press or read an interview with the author (LearnSQL.com).

Chapter Overview (2nd Edition)

  • Installing PostgreSQL and the pgAdmin user interface (Windows, macOS, Linux)
  • Choose a text editor
  • Download code and data from GitHub

  • Understanding relational databases
  • The CREATE DATABASE statement
  • The CREATE TABLE statement
  • Adding rows with INSERT

  • SELECT syntax
  • Sorting data with ORDER BY
  • Using DISTINCT for unique values
  • Filtering with WHERE
  • Matching with LIKE and ILIKE
Download free chapter (PDF)

  • Characters and numbers
  • IDENTITY syntax and auto-incrementing integers
  • Decimal vs. floating point math
  • Dates and times
  • JSON data
  • Converting data types with CAST

  • Delimited text files
  • COPY for importing and exporting
  • Importing U.S. Census county population estimates

  • Math operators and functions
  • Adding, subtracting, division
  • Roots, factorials, exponents
  • Calculating percents and percent change
  • Aggregate functions for average and sum
  • Median and percentiles

  • Linking tables using JOIN
  • Primary keys and foreign keys
  • JOIN types
  • Types of table relationships
  • Set operators UNION, INTERSECT, EXCEPT

  • Naming conventions for identifiers
  • Table constraints: CHECK, UNIQUE, NOT NULL
  • Natural vs. surrogate primary keys
  • Foreign keys
  • Using indexes (using NYC address data)

  • Creating tables for library survey data
  • Counting with count()
  • Using max() and min()
  • Aggregating with GROUP BY
  • Using sum()
  • The HAVING clause

  • Creating a table for food producer data
  • Checking for missing values
  • Checking for inconsistencies
  • Checking for malformed data
  • Modifying tables, columns, and data
  • ALTER TABLE and UPDATE
  • Deleting data
  • Transaction blocks

  • Creating a table of U.S. Census American Community Survey data
  • Measuring correlation
  • Prediction with regression
  • Variance and standard deviation
  • Creating rankings
  • Calculating rates
  • Rolling averages

  • Data types for dates and times
  • Working with timestamps
  • Time zones
  • Calculations with dates and times
  • Finding patterns in NYC taxi data
  • Finding duration of Amtrak train trips

  • Using subqueries
  • Derived tables
  • Subquery expressions
  • LATERAL subqueries
  • Common Table Expressions
  • Cross tabulations
  • CASE statement

  • String functions
  • Find and replace
  • Matching with regular expressions
  • Extracting data from crime reports
  • Full-text search (using presidents’ speeches)

  • PostGIS
  • Spatial data types
  • Constructing geometries
  • Well-known text
  • Projections and coordinate systems
  • Analyzing farmers’ markets data
  • Working with shapefiles

  • Introducing JSON
  • JSON data types
  • JSON extraction operators
  • Containment and existence
  • Analyzing earthquake data
  • Generating and manipulating JSON

  • Using views to simplify queries
  • Standard views and materialized views
  • Inserting, updating and deleting with a view
  • Creating functions and procedures
  • Automating actions with triggers

  • Setting up your command line (Windows, macOS, Linux)
  • Working with the psql utility
  • Meta-commands
  • Command-line utilities createdb and shp2pgsql

  • VACUUM
  • Server settings
  • Backing up and restoring data

  • A framework for generating ideas for analysis, vetting data, drawing sound conclusions, and presenting your findings clearly.

  • Software and documentation to help you grow your skills.

Each chapter ends with a Try It Yourself section with exercises to reinforce what you learned. Answers are provided with the book’s resources on GitHub.