About the Book

Practical SQL is an approachable and fast-paced guide to the database programming language SQL. Using the free, open-source database PostgreSQL, journalist and data analyst Anthony DeBarros takes you from beginner to advanced SQL techniques, including GIS, search, and statistics. Practical SQL is more than just a book about SQL. It's a book about data analysis and finding the story inside everyday data.

Though the book employs PostgreSQL, the general SQL syntax and concepts apply to most database systems, including MySQL, Oracle, and SQLite (note that Microsoft SQL Server employs a variant of the language called T-SQL, which this book does not cover). Read more from No Starch Press.

First edition available now; revised and expanded second edition available for pre-order.

Download a sample chapter

Interview with the author (LearnSQL.com)

Chapters Overview (2nd Edition)

1: Setting Up Your Coding Environment
- Installing PostgreSQL and the pgAdmin user interface (Windows, macOS, Linux)
- Choose a text editor
- Download code and data from GitHub

2: Creating Your First Database and Table
- Understanding relational databases
- The CREATE DATABASE statement
- The CREATE TABLE statement
- Adding rows with INSERT

3: Beginning Data Exploration with SELECT (Download free chapter)
- SELECT syntax
- Sorting data with ORDER BY
- Using DISTINCT for unique values
- Filtering with WHERE
- Matching with LIKE and ILIKE

4: Understanding Data Types
- Characters and numbers
- IDENTITY syntax and auto-incrementing integers
- Decimal vs. floating point math
- Dates and times
- JSON data
- Converting data types with CAST

5: Importing and Exporting Data
- Delimited text files
- COPY for importing and exporting
- Importing U.S. Census county population estimates

6: Basic Math and Stats with SQL
- Math operators and functions
- Adding, subtracting, division
- Roots, factorials, exponents
- Calculating percents and percent change
- Aggregate functions for average and sum
- Median and percentiles

7: Joining Tables in a Relational Database
- Linking tables using JOIN
- Primary keys and foreign keys
- JOIN types
- Types of table relationships
- Set operators UNION, INTERSECT, EXCEPT

8: Table Design that Works for You
- Naming conventions for identifiers
- Table constraints: CHECK, UNIQUE, NOT NULL
- Natural vs. surrogate primary keys
- Foreign keys
- Using indexes (using NYC address data)

9: Extracting Information by Grouping and Summarizing
- Creating tables for library survey data
- Counting with count()
- Using max() and min()
- Aggregating with GROUP BY
- Using sum() - The HAVING clause

10: Inspecting and Modifying Data
- 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

11: Statistical Functions in SQL
- 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

12: Working with Dates and Times
- 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

13: Advanced Query Techniques
- Using subqueries
- Derived tables
- Subquery expressions
- LATERAL subqueries
- Common Table Expressions
- Cross tabulations
- CASE statement

14: Mining Text to Find Meaningful Data
- String functions
- Find and replace
- Matching with regular expressions
- Extracting data from crime reports
- Full-text search (using presidents' speeches)

15: Analyzing Spatial Data with PostGIS
- PostGIS
- Spatial data types
- Constructing geometries
- Well-known text
- Projections and coordinate systems
- Analyzing farmers' markets data
- Working with shapefiles

16: Working with JSON Data
- Introducing JSON
- JSON data types
- JSON extraction operators
- Containment and existence
- Analyzing earthquake data
- Generating and manipulating JSON

17: Saving Time with Views, Functions, and Triggers
- 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

18: Using PostgreSQL from the Command Line
- Setting up your command line (Windows, macOS, Linux)
- Working with the psql utility
- Meta-commands
- Command-line utilities createdb and shp2pgsql

19: Maintaining Your Database
- VACUUM
- Server settings
- Backing up and restoring data

20: Telling Your Data’s Story
- A framework for generating ideas for analysis, vetting data, drawing sound conclusions, and presenting your findings clearly.

Appendix: Additional PostgreSQL Resources
- Software and documentation to help you grow your skills.

Each chapter ends with a Try It Yourself section that contains exercises to help you reinforce the topics you learned. Answers are provided with the book's resources on GitHub.