CONTENTS IN DETAIL
ACKNOWLEDGMENTS
INTRODUCTION
What Is SQL?
Why Use SQL?
About This Book
Using the Book’s Code Examples
Using PostgreSQL
Installing PostgreSQL
Working with pgAdmin
Alternatives to pgAdmin
Wrapping Up
Creating a Database
Executing SQL in pgAdmin
Connecting to the Analysis Database
Creating a Table
The CREATE TABLE Statement
Making the teachers Table
Inserting Rows into a Table
The INSERT Statement
Viewing the Data
When Code Goes Bad
Formatting SQL for Readability
Wrapping Up
Try It Yourself
Basic SELECT Syntax
Querying a Subset of Columns
Using DISTINCT to Find Unique Values
Sorting Data with ORDER BY
Filtering Rows with WHERE
Using LIKE and ILIKE with WHERE
Combining Operators with AND and OR
Putting It All Together
Wrapping Up
Try It Yourself
Characters
Numbers
Integers
Auto-Incrementing Integers
Decimal Numbers
Choosing Your Number Data Type
Dates and Times
Using the interval Data Type in Calculations
Miscellaneous Types
Transforming Values from One Type to Another with CAST
CAST Shortcut Notation
Wrapping Up
Try It Yourself
4
Working with Delimited Text Files
Quoting Columns that Contain Delimiters
Handling Header Rows
Using COPY to Import Data
Importing Census Data Describing Counties
Creating the us_counties_2010 Table
Census Columns and Data Types
Performing the Census Import with COPY
Importing a Subset of Columns with COPY
Adding a Default Value to a Column During Import
Using COPY to Export Data
Exporting All Data
Exporting Particular Columns
Exporting Query Results
Importing and Exporting Through pgAdmin
Wrapping Up
Try It Yourself
Math Operators
Math and Data Types
Adding, Subtracting, and Multiplying
Division and Modulo
Exponents, Roots, and Factorials
Minding the Order of Operations
Doing Math Across Census Table Columns
Adding and Subtracting Columns
Finding Percentages of the Whole
Tracking Percent Change
Aggregate Functions for Averages and Sums
Finding the Median
Finding the Median with Percentile Functions
Median and Percentiles with Census Data
Finding Other Quantiles with Percentile Functions
Creating a median() Function
Finding the Mode
Wrapping Up
Try It Yourself
6
Linking Tables Using JOIN
Relating Tables with Key Columns
Querying Multiple Tables Using JOIN
JOIN Types
JOIN
LEFT JOIN and RIGHT JOIN
FULL OUTER JOIN
CROSS JOIN
Using NULL to Find Rows with Missing Values
Three Types of Table Relationships
One-to-One Relationship
One-to-Many Relationship
Many-to-Many Relationship
Selecting Specific Columns in a Join
Simplifying JOIN Syntax with Table Aliases
Joining Multiple Tables
Performing Math on Joined Table Columns
Wrapping Up
Try It Yourself
Naming Tables, Columns, and Other Identifiers
Using Quotes Around Identifiers to Enable Mixed Case
Pitfalls with Quoting Identifiers
Guidelines for Naming Identifiers
Controlling Column Values with Constraints
Primary Keys: Natural vs. Surrogate
Foreign Keys
Automatically Deleting Related Records with CASCADE
The CHECK Constraint
The UNIQUE Constraint
The NOT NULL Constraint
Removing Constraints or Adding Them Later
Speeding Up Queries with Indexes
B-Tree: PostgreSQL’s Default Index
Considerations When Using Indexes
Wrapping Up
Try It Yourself
Creating the Library Survey Tables
Creating the 2014 Library Data Table
Creating the 2009 Library Data Table
Exploring the Library Data Using Aggregate Functions
Counting Rows and Values Using count()
Finding Maximum and Minimum Values Using max() and min()
Aggregating Data Using GROUP BY
Wrapping Up
Try It Yourself
9
Importing Data on Meat, Poultry, and Egg Producers
Interviewing the Data Set
Checking for Missing Values
Checking for Inconsistent Data Values
Checking for Malformed Values Using length()
Modifying Tables, Columns, and Data
Modifying Tables with ALTER TABLE
Modifying Values with UPDATE
Creating Backup Tables
Restoring Missing Column Values
Updating Values for Consistency
Repairing ZIP Codes Using Concatenation
Updating Values Across Tables
Deleting Unnecessary Data
Deleting Rows from a Table
Deleting a Column from a Table
Deleting a Table from a Database
Using Transaction Blocks to Save or Revert Changes
Improving Performance When Updating Large Tables
Wrapping Up
Try It Yourself
Creating a Census Stats Table
Measuring Correlation with corr(Y, X)
Checking Additional Correlations
Predicting Values with Regression Analysis
Finding the Effect of an Independent Variable with r-squared
Creating Rankings with SQL
Ranking with rank() and dense_rank()
Ranking Within Subgroups with PARTITION BY
Calculating Rates for Meaningful Comparisons
Wrapping Up
Try It Yourself
Data Types and Functions for Dates and Times
Manipulating Dates and Times
Extracting the Components of a timestamp Value
Creating Datetime Values from timestamp Components
Retrieving the Current Date and Time
Working with Time Zones
Finding Your Time Zone Setting
Setting the Time Zone
Calculations with Dates and Times
Finding Patterns in New York City Taxi Data
Finding Patterns in Amtrak Data
Wrapping Up
Try It Yourself
Using Subqueries
Filtering with Subqueries in a WHERE Clause
Creating Derived Tables with Subqueries
Joining Derived Tables
Generating Columns with Subqueries
Subquery Expressions
Common Table Expressions
Cross Tabulations
Installing the crosstab() Function
Tabulating Survey Results
Tabulating City Temperature Readings
Reclassifying Values with CASE
Using CASE in a Common Table Expression
Wrapping Up
Try It Yourself
Formatting Text Using String Functions
Case Formatting
Character Information
Removing Characters
Extracting and Replacing Characters
Matching Text Patterns with Regular Expressions
Regular Expression Notation
Turning Text to Data with Regular Expression Functions
Using Regular Expressions with WHERE
Additional Regular Expression Functions
Full Text Search in PostgreSQL
Text Search Data Types
Creating a Table for Full Text Search
Searching Speech Text
Ranking Query Matches by Relevance
Wrapping Up
Try It Yourself
Installing PostGIS and Creating a Spatial Database
The Building Blocks of Spatial Data
Two-Dimensional Geometries
Well-Known Text Formats
A Note on Coordinate Systems
Spatial Reference System Identifier
PostGIS Data Types
Creating Spatial Objects with PostGIS Functions
Creating a Geometry Type from Well-Known Text
Creating a Geography Type from Well-Known Text
Point Functions
LineString Functions
Polygon Functions
Analyzing Farmers’ Markets Data
Creating and Filling a Geography Column
Adding a GiST Index
Finding Geographies Within a Given Distance
Finding the Distance Between Geographies
Working with Census Shapefiles
Contents of a Shapefile
Loading Shapefiles via the GUI Tool
Exploring the Census 2010 Counties Shapefile
Performing Spatial Joins
Exploring Roads and Waterways Data
Joining the Census Roads and Water Tables
Finding the Location Where Objects Intersect
Wrapping Up
Try It Yourself
Using Views to Simplify Queries
Creating and Querying Views
Inserting, Updating, and Deleting Data Using a View
Programming Your Own Functions
Creating the percent_change() Function
Using the percent_change() Function
Updating Data with a Function
Using the Python Language in a Function
Automating Database Actions with Triggers
Logging Grade Updates to a Table
Automatically Classifying Temperatures
Wrapping Up
Try It Yourself
Setting Up the Command Line for psql
Windows psql Setup
macOS psql Setup
Linux psql Setup
Working with psql
Launching psql and Connecting to a Database
Getting Help
Changing the User and Database Connection
Running SQL Queries on psql
Navigating and Formatting Results
Meta-Commands for Database Information
Importing, Exporting, and Using Files
Additional Command Line Utilities to Expedite Tasks
Adding a Database with createdb
Loading Shapefiles with shp2pgsql
Wrapping Up
Try It Yourself
Recovering Unused Space with VACUUM
Tracking Table Size
Monitoring the autovacuum Process
Running VACUUM Manually
Reducing Table Size with VACUUM FULL
Changing Server Settings
Locating and Editing postgresql.conf
Reloading Settings with pg_ctl
Backing Up and Restoring Your Database
Using pg_dump to Back Up a Database or Table
Restoring a Database Backup with pg_restore
Additional Backup and Restore Options
Wrapping Up
Try It Yourself
Start with a Question
Document Your Process
Gather Your Data
No Data? Build Your Own Database
Assess the Data’s Origins
Interview the Data with Queries
Consult the Data’s Owner
Identify Key Indicators and Trends over Time
Ask Why
Communicate Your Findings
Wrapping Up
Try It Yourself
APPENDIX
ADDITIONAL POSTGRESQL RESOURCES
PostgreSQL Development Environments
PostgreSQL Utilities, Tools, and Extensions
PostgreSQL News
Documentation
INDEX
FOREWORD
When people ask which programming language I learned first, I often absent-mindedly reply, “Python,” forgetting that it was actually with SQL that I first learned to write code. This is probably because learning SQL felt so intuitive after spending years running formulas in Excel spreadsheets. I didn’t have a technical background, but I found SQL’s syntax, unlike that of many other programming languages, straightforward and easy to implement. For example, you run SELECT * on a SQL table to make every row and column appear. You simply use the JOIN keyword to return rows of data from different related tables, which you can then further group, sort, and analyze .
I’m a graphics editor, and I’ve worked as a developer and journalist at a number of publications, including POLITICO, Vox, and USA TODAY. My daily responsibilities involve analyzing data and creating visualizations from what I find. I first used SQL when I worked at The Chronicle of Higher Education and its sister publication, The Chronicle of Philanthropy. Our team analyzed data ranging from nonprofit financials to faculty salaries at colleges and universities. Many of our projects included as much as 20 years’ worth of data, and one of my main tasks was to import all that data into a SQL database and analyze it. I had to calculate the percent change in fund raising dollars at a nonprofit or find the median endowment size at a university to measure an institution’s performance. I discovered SQL to be a powerful language, one that fundamentally shaped my understanding of what you can and can’t do with data.
SQL excels at bringing order to messy, large data sets and helps you discover how different data sets are related. Plus, its queries and functions are easy to reuse within the same project or even in a different database. This leads me to Practical SQL. Looking back, I wish I’d read Chapter 4 on “Importing and Exporting Data” so I could have understood the power of bulk imports instead of writing long, cumbersome INSERT statements when filling a table. The statistical capabilities of PostgreSQL, covered in Chapters 5 and 10 in this book, are also something I wish I had grasped earlier, as my data analysis often involves calculating the percent change or finding the average or median values. I’m embarrassed to say that I didn’t know how percentile_cont(), covered in Chapter 5, could be used to easily calculate a median in PostgresSQ with the added bonus that it also finds your data’s natural breaks or quantiles. But at that stage in my career, I was only scratching the surface of SQL’s capabilities. It wasn’t until 2014, when I became a data developer at Gannett Digital on a team led by Anthony DeBarros, that I learned to use PostgreSQL. I began to understand just how enormously powerful SQL was for creating a reproducible and sustainable workflow.
When I met Anthony, he had been working at USA TODAY and other Gannett properties for more than 20 years, where he had led teams that built databases and published award-winning investigations. Anthony was able to show me the ins and outs of our team’s databases in addition to teaching me how to properly build and maintain my own. It was through working with Anthony that I truly learned how to code.
One of the first projects Anthony and I collaborated on was the 2014 U.S. midterm elections. We helped build an election forecast data visualization to show USA TODAY readers the latest polling averages, campaign finance data, and biographical information for more than 1,300 candidates in more than 500 congressional and gubernatorial races.
Building our data infrastructure was a complex, multistep process powered by a PostgreSQL database at its heart. Anthony taught me how to write code that funneled all the data from our sources into a half-dozen tables in PostgreSQL. From there, we could query the data into a format that would power the maps, charts, and front-end presentation of our election forecast. Around this time, I also learned one of my favorite things about PostgreSQL—its powerful suite of geographic functions (Chapter 14 in this book). By adding the PostGIS extension to the database, you can create spatial data that you can then export as GeoJSON or as a shapefile, a format that is easy to map. You can also perform complex spatial analysis, like calculating the distance between two points or finding the density of schools or, as Anthony shows in the chapter, all the farmers’ markets in a given radius.
It’s a skill I’ve used repeatedly in my career. For example, I used it to build a data set of lead exposure risk at the census-tract level while at Vox, which I consider one of my crowning PostGIS achievements. Using this database, I was able to create a data set of every U.S. Census tract and its corresponding lead exposure risk in a spatial format that could be easily mapped at the national level.
With so many different programming languages available more than 200, if you can believe it , it’s truly overwhelming to know where to begin. One of the best pieces of advice I received when first starting to code was to find an inefficiency in my workflow that could be improved by coding. In my case, it was building a database to easily query a project’s data. Maybe you’re in a similar boat or maybe you just want to know how to analyze large data sets.
Regardless, you’re probably looking for a no-nonsense guide that skips the programming jargon and delves into SQL in an easy-to-understand manner that is both practical and, more importantly, applicable. And that’s exactly what Practical SQL does. It gets away from programming theory and focuses on teaching SQL by example, using real data sets you’ll likely encounter. It also doesn’t shy away from showing you how to deal with annoying messy data pitfalls: misspelled names, missing values, and columns with unsuitable data types. This is important because, as you’ll quickly learn, there’s no such thing as clean data. Over the years, my role as a data journalist has evolved. I build fewer databases now and build more maps. I also report more. But the core requirement of my job, and what I learned when first learning SQL, remains the same: know thy data and to thine own data be true. In other words, the most important aspect of working with data is being able to understand what’s in it. You can’t expect to ask the right questions of your data or tell a compelling story if you don’t understand how to best analyze it. Fortunately, that’s where Practical SQL comes in. It’ll teach you the fundamentals of working with data so that you can discover your own stories and insights.
Sarah Frostenson
Graphics Editor at POLITICO
INTRODUCTION
Shortly after joining the staff of USA TODAY I received a data set I would analyze almost every week for the next decade. It was the weekly Best-Selling Books list, which ranked the nation’s top-selling books based on confidential sales data. The list not only produced an endless stream of story ideas to pitch, but it also captured the zeitgeist of America in a singular way.
For example, did you know that cookbooks sell a bit more during the week of Mother’s Day, or that Oprah Winfrey turned many obscure writers into number one best-selling authors just by having them on her show? Week after week, the book list editor and I pored over the sales figures and book genres, ranking the data in search of the next headline. Rarely did we come up empty: we chronicled everything from the rocketrise of the blockbuster Harry Potter series to the fact that Oh, the Places You’ll Go! by Dr. Seuss has become a perennial gift for new graduates. My technical companion during this time was the database programming language SQL (for Structured Query Language). Early on, I convinced USA TODAY’s IT department to grant me access to the SQLbased database system that powered our book list application. Using SQL, I was able to unlock the stories hidden in the database, which contained titles, authors, genres, and various codes that defined the publishing world. Analyzing data with SQL to discover interesting stories is exactly what you’ll learn to do using this book.
What Is SQL?
SQL is a widely used programming language that allows you to define and query databases. Whether you’re a marketing analyst, a journalist, or a researcher mapping neurons in the brain of a fruit fly, you’ll benefit from using SQL to manage database objects as well as create, modify, explore, and summarize data.
Because SQL is a mature language that has been around for decades, it’s deeply ingrained in many modern systems. A pair of IBM researchers first outlined the syntax for SQL (then called SEQUEL) in a 1974 paper, building on the theoretical work of the British computer scientist Edgar F. Codd. In 1979, a precursor to the database company Oracle (then called Relational Software) became the first to use the language in a commercial product. Today, it continues to rank as one of the most-used computer languages in the world, and that’s unlikely to change soon.
SQL comes in several variants, which are generally tied to specific database systems. The American National Standards Institute (ANSI) and International Organization for Standardization (ISO), which set standards for products and technologies, provide standards for the language and shepherd revisions to it. The good news is that the variants don’t stray far from the standard, so once you learn the SQL conventions for one database, you can transfer that knowledge to other systems.
Why Use SQL?
So why should you use SQL? After all, SQL is not usually the first tool people choose when they’re learning to analyze data. In fact, many people start with Microsoft Excel spreadsheets and their assortment of analytic functions. After working with Excel, they might graduate to Access, the database system built into Microsoft Office, which has a graphical query interface that makes it easy to get work done, making SQL skills optional. But as you might know, Excel and Access have their limits. Excel currently allows 1,048,576 rows maximum per worksheet, and Access limits database size to two gigabytes and limits columns to 255 per table. It’s not uncommon for data sets to surpass those limits, particularly when you’re working with data dumped from government systems. The last obstacle you want to discover while facing a deadline is that your database system doesn’t have the capacity to get the job done.
Using a robust SQL database system allows you to work with terabytes of data, multiple related tables, and thousands of columns. It gives you improved programmatic control over the structure of your data, leading to efficiency, speed, and most important accuracy. SQL is also an excellent adjunct to programming languages used in the data sciences, such as R and Python. If you use either language, you can connect to SQL databases and, in some cases, even incorporate SQL syntax directly into the language. For people with no background in programming languages, SQL often serves as an easy-to-understand introduction into concepts related to data structures and programming logic.
Additionally, knowing SQL can help you beyond data analysis. If you delve into building online applications, you’ll find that databases provide the backend power for many common web frameworks, interactive maps, and content management systems. When you need to dig beneath the surface of these applications, SQL’s capability to manipulate data and databases will come in very handy.
About This Book
Practical SQL is for people who encounter data in their everyday lives and want to learn how to analyze and transform it. To this end, I discuss realworld data and scenarios, such as U.S. Census demographics, crime statistics, and data about taxi rides in New York City. Along with information about databases and code, you’ll also learn tips on how to analyze and acquire data as well as other valuable insights I’ve accumulated throughout my career. I won’t focus on setting up servers or other tasks typically handled by a database administrator, but the SQL and PostgreSQL fundamentals you learn in this book will serve you well if you intend to go that route.
I’ve designed the exercises for beginner SQL coders but will assume that you know your way around your computer, including how to install programs, navigate your hard drive, and download files from the internet.
Although many chapters in this book can stand alone, you should work through the book sequentially to build on the fundamentals. Some data sets used in early chapters reappear later in the book, so following the book in order will help you stay on track. Practical SQL starts with the basics of databases, queries, tables, and data that are common to SQL across many database systems. Chapters 13 to 17 cover topics more specific to PostgreSQL, such as full text search and GIS. The following table of contents provides more detail about the topics discussed in each chapter:
Chapter 1: Creating Your First Database and Table introduces PostgreSQL, the pgAdmin user interface, and the code for loading a simple data set about teachers into a new database.
Chapter 2: Beginning Data Exploration with SELECT explores basic SQL query syntax, including how to sort and filter data.
Chapter 3: Understanding Data Types explains the definitions for setting columns in a table to hold specific types of data, from text to dates to various forms of numbers.
Chapter 4: Importing and Exporting Data explains how to use SQL commands to load data from external files and then export it. You’ll load a table of U.S. Census population data that you’ll use throughout the book.
Chapter 5: Basic Math and Stats with SQL covers arithmetic operations and introduces aggregate functions for finding sums, averages, and medians.
Chapter 6: Joining Tables in a Relational Database explains how to query multiple, related tables by joining them on key columns. You’ll learn how and when to use different types of joins.
Chapter 7: Table Design that Works for You covers how to set up tables to improve the organization and integrity of your data as well as how to speed up queries using indexes.
Chapter 8: Extracting Information by Grouping and Summarizing explains how to use aggregate functions to find trends in U.S. library use based on annual surveys.
Chapter 9: Inspecting and Modifying Data explores how to find and fix incomplete or inaccurate data using a collection of records about meat, egg, and poultry producers as an example.
Chapter 10: Statistical Functions in SQL introduces correlation, regression, and ranking functions in SQL to help you derive more meaning from data sets.
Chapter 11: Working with Dates and Times explains how to create, manipulate, and query dates and times in your database, including working with time zones, using data on New York City taxi trips and Amtrak train schedules.
Chapter 12: Advanced Query Techniques explains how to use more complex SQL operations, such as subqueries and cross tabulations, and the CASE statement to reclassify values in a data set on temperature readings.
Chapter 13: Mining Text to Find Meaningful Data covers how to use PostgreSQL’s full text search engine and regular expressions to extract data from unstructured text, using a collection of speeches by U.S. presidents as an example.
Chapter 14: Analyzing Spatial Data with PostGIS introduces data types and queries related to spatial objects, which will let you analyze geographical features like states, roads, and rivers.
Chapter 15: Saving Time with Views, Functions, and Triggers explains how to automate database tasks so you can avoid repeating routine work.
Chapter 16: Using PostgreSQL from the Command Line covers how to use text commands at your computer’s command prompt to connect to your database and run queries.
Chapter 17: Maintaining Your Database provides tips and procedures for tracking the size of your database, customizing settings, and backing up data.
Chapter 18: Identifying and Telling the Story Behind Your Data provides guidelines for generating ideas for analysis, vetting data, drawing sound conclusions, and presenting your findings clearly.
Appendix: Additional PostgreSQL Resources lists 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.
Using the Book’s Code Examples
Each chapter includes code examples, and most use data sets I’ve already compiled. All the code and sample data in the book is available to download at nostarch.com/practicalSQL/. Click the Download the code from GitHub link to go to the GitHub repository that holds this material. At GitHub, you should see a “Clone or Download” button that gives you the option to download a ZIP file with all the materials. Save the file to your computer in a location where you can easily find it, such as your desktop.
Inside the ZIP file is a folder for each chapter. Each folder contains a file named Chapter_XX (XX is the chapter number) that ends with a .sql extension. You can open those files with a text editor or with the PostgreSQL administrative tool you’ll install. You can copy and paste code when the book instructs you to run it. Note that in the book, several code examples are truncated to save space, but you’ll need the full listing from the .sql file to complete the exercise. You’ll know an example is truncated when you see snip inside the listing.
Also in the .sql files, you’ll see lines that begin with two hyphens (--) and a space. These are comments that provide the code’s listing number and additional context, but they’re not part of the code. These comments also note when the file has additional examples that aren’t in the book.
Of course, you can also use another database system, such as Microsoft SQL Server or MySQL; many code examples in this book translate easily to either SQL implementation. However, some examples, especially later in the book, do not, and you’ll need to search online for equivalent solutions. Where appropriate, I’ll note whether an example code follows the ANSI SQL standard and may be portable to other systems or whether it’s specific to PostgreSQL.
Using PostgreSQL
In this book, I’ll teach you SQL using the open source PostgreSQL database system. PostgreSQL, or simply Postgres, is a robust database system that can handle very large amounts of data. Here are some reasons PostgreSQL is a great choice to use with this book:
It’s free.
It’s available for Windows, macOS, and Linux operating systems.
Its SQL implementation closely follows ANSI standards.
It’s widely used for analytics and data mining, so finding help online from peers is easy.
Its geospatial extension, PostGIS, lets you analyze geometric data and perform mapping functions.
It’s available in several variants, such as Amazon Redshift and Green - plum, which focus on processing huge data sets.
It’s a common choice for web applications, including those powered by the popular web frameworks Django and Ruby on Rails.
