Contents in Detail
| FOREWORD by Sarah Frostenson | xviii |
|---|---|
| ACKNOWLEDGMENTS | xxi |
| INTRODUCTION | xxiii |
| What Is SQL? | xxiv |
| Why Use SQL? | xxiv |
| About This Book. | xxv |
| Using the Book’s Code Examples. | xxvii |
| Using PostgreSQL | xxvii |
| Installing PostgreSQL | xxviii |
| Working with pgAdmin | xxxi |
| Alternatives to pgAdmin. | xxxii |
| Wrapping Up | xxxiii |
| 1 | |
| CREATING YOUR FIRST DATABASE AND TABLE | 1 |
| Creating a Database | 3 |
| Executing SQL in pgAdmin. | 3 |
| Connecting to the Analysis Database. | 5 |
| Creating a Table | 5 |
| The CREATE TABLE Statement | 6 |
| Making the teachers Table | 7 |
| Inserting Rows into a Table | 8 |
| The INSERT Statement | 8 |
| Viewing the Data | 9 |
| When Code Goes Bad | 9 |
| Formatting SQL for Readability | 10 |
| Wrapping Up | 10 |
| Try It Yourself. | 10 |
| 2 | |
| BEGINNING DATA EXPLORATION WITH SELECT | 11 |
| Basic SELECT Syntax | 12 |
| Querying a Subset of Columns | 13 |
| Using DISTINCT to Find Unique Values | 14 |
| Sorting Data with ORDER BY | 15 |
| Filtering Rows with WHERE | 17 |
| Using LIKE and ILIKE with WHERE | 19 |
| Combining Operators with AND and OR | 20 |
| Putting It All Together | 21 |
| Wrapping Up | 21 |
| Try It Yourself | 22 |
| 3 UNDERSTANDING DATA TYPES | 23 |
| Characters | 24 |
| Numbers | 26 |
| Integers | 27 |
| Auto-Incrementing Integers | 27 |
| Decimal Numbers | 28 |
| Choosing Your Number Data Type | 31 |
| Dates and Times | 32 |
| Using the interval Data Type in Calculations | 34 |
| Miscellaneous Types | 35 |
| Transforming Values from One Type to Another with CAST | 35 |
| CAST Shortcut Notation | 36 |
| Wrapping Up | 36 |
| Try It Yourself. | 37 |
| 4 IMPORTING AND EXPORTING DATA | 39 |
| Working with Delimited Text Files | 40 |
| Quoting Columns that Contain Delimiters | 41 |
| Handling Header Rows | 41 |
| Using COPY to Import Data. | 42 |
| Importing Census Data Describing Counties | 43 |
| Creating the us_counties_2010 Table | 44 |
| Census Columns and Data Types | 45 |
| Performing the Census Import with COPY | 47 |
| Importing a Subset of Columns with COPY | 49 |
| Adding a Default Value to a Column During Import | 50 |
| Using COPY to Export Data. | 51 |
| Exporting All Data | 51 |
| Exporting Particular Columns | 52 |
| Exporting Query Results | 52 |
| Importing and Exporting Through pgAdmin | 52 |
| Wrapping Up | 53 |
| Try It Yourself. | 54 |
| 5 BASIC MATH AND STATS WITH SQL | 55 |
| Math Operators | 56 |
| Math and Data Types | 56 |
| Adding, Subtracting, and Multiplying | 57 |
| Division and Modulo | 57 |
| Exponents, Roots, and Factorials | 58 |
| Minding the Order of Operations | 59 |
| Doing Math Across Census Table Columns | 60 |
| Adding and Subtracting Columns | 60 |
| Finding Percentages of the Whole. | 62 |
| Tracking Percent Change | 63 |
| Aggregate Functions for Averages and Sums | 64 |
| Finding the Median | 65 |
| Finding the Median with Percentile Functions | 66 |
| Median and Percentiles with Census Data | 67 |
| Finding Other Quantiles with Percentile Functions | 67 |
| Creating a median() Function | 69 |
| Finding the Mode | 70 |
| Wrapping Up | 71 |
| Try It Yourself. | 71 |
| 6 JOINING TABLES IN A RELATIONAL DATABASE | 73 |
| Linking Tables Using JOIN. | 74 |
| Relating Tables with Key Columns | 74 |
| Querying Multiple Tables Using JOIN. | 77 |
| JOIN Types . | 78 |
| JOIN . | 80 |
| LEFT JOIN and RIGHT JOIN . | 80 |
| FULL OUTER JOIN . | 82 |
| CROSS JOIN . | 82 |
| Using NULL to Find Rows with Missing Values . | 83 |
| Three Types of Table Relationships . | 84 |
| One-to-One Relationship . | 84 |
| One-to-Many Relationship . | 84 |
| Many-to-Many Relationship . | 85 |
| Selecting Specific Columns in a Join. | 85 |
| Simplifying JOIN Syntax with Table Aliases. | 86 |
| Joining Multiple Tables . | 87 |
| Performing Math on Joined Table Columns | 88 |
| Wrapping Up . | 90 |
| Try It Yourself. | 91 |
| 7 TABLE DESIGN THAT WORKS FOR YOU | 93 |
| Naming Tables, Columns, and Other Identifiers. | 94 |
| Using Quotes Around Identifiers to Enable Mixed Case | 94 |
| Pitfalls with Quoting Identifiers | 95 |
| Guidelines for Naming Identifiers | 96 |
| Controlling Column Values with Constraints. | 96 |
| Primary Keys: Natural vs. Surrogate | 97 |
| Foreign Keys | 102 |
| Automatically Deleting Related Records with CASCADE | 104 |
| The CHECK Constraint. | 104 |
| The UNIQUE Constraint. | 105 |
| The NOT NULL Constraint. | 106 |
| Removing Constraints or Adding Them Later. | 107 |
| Speeding Up Queries with Indexes. | 108 |
| B-Tree: PostgreSQL’s Default Index | 108 |
| Considerations When Using Indexes | 111 |
| Wrapping Up . | 111 |
| Try It Yourself. | 112 |
| 8 | |
| EXTRACTING INFORMATION BY GROUPING AND SUMMARIZING | |
| Creating the Library Survey Tables | 113 |
| Creating the 2014 Library Data Table | 114 |
| Creating the 2009 Library Data Table | 116 |
| Exploring the Library Data Using Aggregate Functions | 117 |
| Counting Rows and Values Using count() | 117 |
| Finding Maximum and Minimum Values Using max() and min() | 119 |
| Aggregating Data Using GROUP BY | 120 |
| Wrapping Up | 128 |
| Try It Yourself. | 128 |
| 9 | |
| INSPECTING AND MODIFYING DATA | |
| Importing Data on Meat, Poultry, and Egg Producers | 130 |
| Interviewing the Data Set | 131 |
| Checking for Missing Values | 132 |
| Checking for Inconsistent Data Values | 134 |
| Checking for Malformed Values Using length() | 135 |
| Modifying Tables, Columns, and Data | 136 |
| Modifying Tables with ALTER TABLE | 137 |
| Modifying Values with UPDATE | 138 |
| Creating Backup Tables | 139 |
| Restoring Missing Column Values | 140 |
| Updating Values for Consistency | 142 |
| Repairing ZIP Codes Using Concatenation | 143 |
| Updating Values Across Tables | 145 |
| Deleting Unnecessary Data | 147 |
| Deleting Rows from a Table | 147 |
| Deleting a Column from a Table | 148 |
| Deleting a Table from a Database | 148 |
| Using Transaction Blocks to Save or Revert Changes | 149 |
| Improving Performance When Updating Large Tables | 151 |
| Wrapping Up | 152 |
| Try It Yourself. | 152 |
| 10 | |
| STATISTICAL FUNCTIONS IN SQL | |
| Creating a Census Stats Table | 156 |
| Measuring Correlation with corr(Y, X) | 157 |
| Checking Additional Correlations | 159 |
| Predicting Values with Regression Analysis | 160 |
| Finding the Effect of an Independent Variable with r-squared | 163 |
| Creating Rankings with SQL | 164 |
| Ranking with rank() and dense_rank() | 164 |
| Ranking Within Subgroups with PARTITION BY | 165 |
| Calculating Rates for Meaningful Comparisons | 167 |
| Wrapping Up | 169 |
| Try It Yourself. | 169 |
| 11 | |
| Working with Dates and Times | 171 |
| 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. |
172 172 173 174 175 |
| Working with Time Zones Finding Your Time Zone Setting Setting the Time Zone Calculations with Dates and Times |
177 177 178 180 |
| Finding Patterns in New York City Taxi Data | 180 |
| Finding Patterns in Amtrak Data | 186 |
| Wrapping Up | 189 |
| Try It Yourself. | 190 |
| 12 Advanced Query Techniques |
191 |
| Using Subqueries | 192 |
| Filtering with Subqueries in a WHERE Clause | 192 |
| Creating Derived Tables with Subqueries | 194 |
| Joining Derived Tables | 195 |
| Generating Columns with Subqueries | 197 |
| Subquery Expressions | 198 |
| Common Table Expressions. | 200 |
| Cross Tabulations. | 203 |
| Installing the crosstab() Function | 203 |
| Tabulating Survey Results | 203 |
| Tabulating City Temperature Readings. | 205 |
| Reclassifying Values with CASE | 207 |
| Using CASE in a Common Table Expression | 209 |
| Wrapping Up | 210 |
| Try It Yourself. | 210 |
| 13 Mining Text to Find Meaningful Data |
211 |
| Formatting Text Using String Functions | 212 |
| Case Formatting | 212 |
| Character Information | 212 |
| Removing Characters | 213 |
| Extracting and Replacing Characters | 213 |
| Matching Text Patterns with Regular Expressions | 214 |
| Regular Expression Notation. | 214 |
| Turning Text to Data with Regular Expression Functions | 216 |
| Using Regular Expressions with WHERE | 228 |
| Additional Regular Expression Functions | 230 |
| Full Text Search in PostgreSQL | 231 |
| Text Search Data Types | 231 |
| Creating a Table for Full Text Search | 233 |
| Searching Speech Text | 234 |
| Ranking Query Matches by Relevance | 237 |
| Wrapping Up | 239 |
| Try It Yourself. | 239 |
| 14 ANALYZING SPATIAL DATA WITH POSTGIS |
241 |
| Installing PostGIS and Creating a Spatial Database | 242 |
| The Building Blocks of Spatial Data | 243 |
| Two-Dimensional Geometries. | 243 |
| Well-Known Text Formats. | 244 |
| A Note on Coordinate Systems. | 245 |
| Spatial Reference System Identifier | 246 |
| PostGIS Data Types | 247 |
| Creating Spatial Objects with PostGIS Functions | 247 |
| Creating a Geometry Type from Well-Known Text. | 247 |
| Creating a Geography Type from Well-Known Text | 248 |
| Point Functions. | 249 |
| LineString Functions. | 249 |
| Polygon Functions. | 250 |
| Analyzing Farmers’ Markets Data | 250 |
| Creating and Filling a Geography Column. | 251 |
| Adding a GiST Index. | 252 |
| Finding Geographies Within a Given Distance. | 253 |
| Finding the Distance Between Geographies. | 254 |
| Working with Census Shapefiles | 256 |
| Contents of a Shapefile. | 256 |
| Loading Shapefiles via the GUI Tool | 257 |
| Exploring the Census 2010 Counties Shapefile. | 259 |
| Performing Spatial Joins | 262 |
| Exploring Roads and Waterways Data | 262 |
| Joining the Census Roads and Water Tables | 263 |
| Finding the Location Where Objects Intersect | 264 |
| Wrapping Up | 265 |
| Try It Yourself. | 265 |
| 15 SAVING TIME WITH VIEWS, FUNCTIONS, AND TRIGGERS |
267 |
| Using Views to Simplify Queries | 268 |
| Creating and Querying Views | 269 |
| Inserting, Updating, and Deleting Data Using a View | 271 |
| Programming Your Own Functions | 275 |
| Creating the percent_change() Function | 276 |
| Using the percent_change() Function | 277 |
| Updating Data with a Function | 278 |
| Using the Python Language in a Function | 281 |
| Automating Database Actions with Triggers. | 282 |
| Logging Grade Updates to a Table | 282 |
| Automatically Classifying Temperatures | 286 |
| Wrapping Up | 289 |
| Try It Yourself. | 289 |
| 16 | |
| USING POSTGRESQL FROM THE COMMAND LINE | 291 |
| Setting Up the Command Line for psql | 292 |
| Windows psql Setup | 292 |
| macOS psql Setup | 296 |
| Linux psql Setup | 299 |
| Working with psql | 299 |
| Launching psql and Connecting to a Database | 299 |
| Getting Help | 300 |
| Changing the User and Database Connection | 300 |
| Running SQL Queries on psql | 301 |
| Navigating and Formatting Results | 303 |
| Meta-Commands for Database Information. | 306 |
| Importing, Exporting, and Using Files | 307 |
| Additional Command Line Utilities to Expedite Tasks. | 310 |
| Adding a Database with createdb | 310 |
| Loading Shapefiles with shp2pgsql | 311 |
| Wrapping Up | 311 |
| Try It Yourself. | 312 |
| 17 | |
| MAINTAINING YOUR DATABASE | 313 |
| Recovering Unused Space with VACUUM. | 314 |
| Tracking Table Size. | 314 |
| Monitoring the autovacuum Process | 316 |
| Running VACUUM Manually | 318 |
| Reducing Table Size with VACUUM FULL. | 318 |
| Changing Server Settings | 319 |
| Locating and Editing postgresql.conf | 319 |
| Reloading Settings with pg_ctl | 321 |
| Backing Up and Restoring Your Database. | 321 |
| Using pg_dump to Back Up a Database or Table | 321 |
| Restoring a Database Backup with pg_restore | 322 |
| Additional Backup and Restore Options. | 323 |
| Wrapping Up | 323 |
| Try It Yourself. | 323 |
| 18 | |
| IDENTIFYING AND TELLING THE STORY BEHIND YOUR DATA | 325 |
| Start with a Question | 326 |
| Document Your Process | 326 |
| Gather Your Data | 326 |
| Assess the Data’s Origins | 328 |
| Interview the Data with Queries | 328 |
| Consult the Data’s Owner | 328 |
| Identify Key Indicators and Trends over Time | 329 |
| Ask Why | 331 |
| Communicate Your Findings | 331 |
| Wrapping Up | 332 |
| Try It Yourself | 332 |
| APPENDIX | |
| ADDITIONAL POSTGRESQL RESOURCES | 333 |
| PostgreSQL Development Environments | 333 |
| PostgreSQL Utilities, Tools, and Extensions | 334 |
| PostgreSQL News | 335 |
| Documentation | 335 |
{11}————————————————
{12}————————————————
{13}————————————————
{14}————————————————
{15}————————————————
{16}————————————————
{17}————————————————
{18}————————————————
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
{19}————————————————
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 fundraising 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 PostgresSQL—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 frontend 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
{20}————————————————
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
{21}————————————————
{22}————————————————
Acknowledgments
Practical SQL is the work of many hands. My thanks, first, go to the team at No Starch Press. Thanks to Bill Pollock and Tyler Ortman for capturing the vision and sharpening the initial concept; to developmental editors Annie Choi and Liz Chadwick for refining each chapter; to copyeditor Anne Marie Walker for polishing the final drafts with an eagle eye; and to production editor Janelle Ludowise for laying out the book and keeping the process well organized.
Josh Berkus, Kubernetes community manager for Red Hat, Inc., served as our technical reviewer. To work with Josh was to receive a master class in SQL and PostgreSQL. Thank you, Josh, for your patience and high standards.
Thank you to Investigative Reporters and Editors (IRE) and its members and staff past and present for training journalists to find great stories in data. IRE is where I got my start with SQL and data journalism.
During my years at USA TODAY, many colleagues either taught me SQL or imparted memorable lessons on data analysis. Special thanks to
{23}————————————————
Paul Overberg for sharing his vast knowledge of demographics and the U.S. Census, to Lou Schilling for many technical lessons, to Christopher Schnaars for his SQL expertise, and to Sarah Frostenson for graciously agreeing to write the book’s foreword.
My deepest appreciation goes to my dear wife, Elizabeth, and our sons. Thank you for making every day brighter and warmer, for your love, and for bearing with me as I completed this book.
{24}————————————————