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

Title Page....16

Copyright....17

About the Author....19

Preface to the Second Edition....20

Acknowledgments....23

Introduction....24

What Is SQL?....25

Why SQL?....26

Who Is This Book For?....28

What You’ll Learn....28

Chapter 1: Setting Up Your Coding Environment....31

Installing a Text Editor....31

Downloading Code and Data from GitHub....33

Installing PostgreSQL and pgAdmin....34

Windows Installation....35

macOS Installation....40

Linux Installation....42

Working with pgAdmin....45

Launching pgAdmin and Setting a Master Password....45

Connecting to the Default postgres Database....47

Exploring the Query Tool....49

Customizing pgAdmin....51

Alternatives to pgAdmin....52

Wrapping Up....52

Chapter 2: Creating Your First Database and Table....53

Understanding Tables....53

Creating a Database....55

Executing SQL in pgAdmin....56

Connecting to the analysis Database....59

Creating a Table....59

Using the CREATE TABLE Statement....60

Making the teachers Table....61

Inserting Rows into a Table....63

Using the INSERT Statement....63

Viewing the Data....65

Getting Help When Code Goes Bad....66

Formatting SQL for Readability....66

Wrapping Up....67

Chapter 3: Beginning Data Exploration with SELECT....68

Basic SELECT Syntax....69

Querying a Subset of Columns....70

Sorting Data with ORDER BY....72

Using DISTINCT to Find Unique Values....74

Filtering Rows with WHERE....76

Using LIKE and ILIKE with WHERE....78

Combining Operators with AND and OR....80

Putting It All Together....81

Wrapping Up....82

Chapter 4: Understanding Data Types....83

Understanding Characters....84

Understanding Numbers....88

Using Integers....88

Auto-Incrementing Integers....89

Using Decimal Numbers....92

Choosing Your Number Data Type....95

Understanding Dates and Times....96

Using the interval Data Type in Calculations....99

Understanding JSON and JSONB....100

Using Miscellaneous Types....101

Transforming Values from One Type to Another with CAST....102

Using CAST Shortcut Notation....103

Wrapping Up....104

Chapter 5: Importing and Exporting Data....105

Working with Delimited Text Files....106

Handling Header Rows....107

Quoting Columns That Contain Delimiters....107

Using COPY to Import Data....108

Importing Census Data Describing Counties....111

Creating the us_counties_pop_est_2019 Table....112

Understanding Census Columns and Data Types....113

Performing the Census Import with COPY....115

Inspecting the Import....116

Importing a Subset of Columns with COPY....118

Importing a Subset of Rows with COPY....120

Adding a Value to a Column During Import....121

Using COPY to Export Data....123

Exporting All Data....123

Exporting Particular Columns....124

Exporting Query Results....124

Importing and Exporting Through pgAdmin....125

Wrapping Up....126

Chapter 6: Basic Math and Stats with SQL....128

Understanding Math Operators and Functions....128

Understanding Math and Data Types....129

Adding, Subtracting, and Multiplying....130

Performing Division and Modulo....131

Using Exponents, Roots, and Factorials....132

Minding the Order of Operations....133

Doing Math Across Census Table Columns....134

Adding and Subtracting Columns....135

Finding Percentages of the Whole....137

Tracking Percent Change....138

Using Aggregate Functions for Averages and Sums....140

Finding the Median....141

Finding the Median with Percentile Functions....142

Finding Median and Percentiles with Census Data....144

Finding Other Quantiles with Percentile Functions....145

Finding the Mode....147

Wrapping Up....147

Chapter 7: Joining Tables in a Relational Database....149

Linking Tables Using JOIN....149

Relating Tables with Key Columns....150

Querying Multiple Tables Using JOIN....154

Understanding JOIN Types....155

JOIN....158

LEFT JOIN and RIGHT JOIN....159

FULL OUTER JOIN....161

CROSS JOIN....162

Using NULL to Find Rows with Missing Values....163

Understanding the Three Types of Table Relationships....164

One-to-One Relationship....165

One-to-Many Relationship....165

Many-to-Many Relationship....165

Selecting Specific Columns in a Join....166

Simplifying JOIN Syntax with Table Aliases....167

Joining Multiple Tables....168

Combining Query Results with Set Operators....170

UNION and UNION ALL....171

INTERSECT and EXCEPT....173

Performing Math on Joined Table Columns....174

Wrapping Up....177

Chapter 8: Table Design That Works for You....179

Following Naming Conventions....179

Quoting Identifiers Enables Mixed Case....181

Pitfalls with Quoting Identifiers....182

Guidelines for Naming Identifiers....182

Controlling Column Values with Constraints....183

Primary Keys: Natural vs. Surrogate....184

Foreign Keys....193

How to Automatically Delete Related Records with CASCADE....196

The CHECK Constraint....196

The UNIQUE Constraint....198

The NOT NULL Constraint....200

How to Remove Constraints or Add Them Later....200

Speeding Up Queries with Indexes....202

B-Tree: PostgreSQL’s Default Index....202

Considerations When Using Indexes....206

Wrapping Up....207

Chapter 9: Extracting Information by Grouping and Summarizing....209

Creating the Library Survey Tables....210

Creating the 2018 Library Data Table....210

Creating the 2017 and 2016 Library Data Tables....212

Exploring the Library Data Using Aggregate Functions....214

Counting Rows and Values Using count()....214

Finding Maximum and Minimum Values Using max() and min()....217

Aggregating Data Using GROUP BY....219

Wrapping Up....230

Chapter 10: Inspecting and Modifying Data....232

Importing Data on Meat, Poultry, and Egg Producers....233

Interviewing the Dataset....235

Checking for Missing Values....236

Checking for Inconsistent Data Values....239

Checking for Malformed Values Using length()....240

Modifying Tables, Columns, and Data....242

Modifying Tables with ALTER TABLE....243

Modifying Values with UPDATE....244

Viewing Modified Data with RETURNING....246

Creating Backup Tables....246

Restoring Missing Column Values....247

Updating Values for Consistency....251

Repairing ZIP Codes Using Concatenation....252

Updating Values Across Tables....254

Deleting Unneeded Data....256

Deleting Rows from a Table....257

Deleting a Column from a Table....258

Deleting a Table from a Database....259

Using Transactions to Save or Revert Changes....260

Improving Performance When Updating Large Tables....262

Wrapping Up....264

Chapter 11: Statistical Functions in SQL....266

Creating a Census Stats Table....267

Measuring Correlation with corr(Y, X)....269

Checking Additional Correlations....272

Predicting Values with Regression Analysis....274

Finding the Effect of an Independent Variable with r-Squared....277

Finding Variance and Standard Deviation....278

Creating Rankings with SQL....280

Ranking with rank() and dense_rank()....280

Ranking Within Subgroups with PARTITION BY....282

Calculating Rates for Meaningful Comparisons....284

Finding Rates of Tourism-Related Businesses....285

Smoothing Uneven Data....287

Wrapping Up....292

Chapter 12: Working with Dates and Times....293

Understanding Data Types and Functions for Dates and Times....293

Manipulating Dates and Times....295

Extracting the Components of a timestamp Value....295

Creating Datetime Values from timestamp Components....298

Retrieving the Current Date and Time....299

Working with Time Zones....301

Finding Your Time Zone Setting....301

Setting the Time Zone....303

Performing Calculations with Dates and Times....306

Finding Patterns in New York City Taxi Data....306

Finding Patterns in Amtrak Data....313

Wrapping Up....318

Chapter 13: Advanced Query Techniques....320

Using Subqueries....320

Filtering with Subqueries in a WHERE Clause....321

Creating Derived Tables with Subqueries....323

Joining Derived Tables....325

Generating Columns with Subqueries....327

Understanding Subquery Expressions....329

Using Subqueries with LATERAL....332

Using Common Table Expressions....335

Performing Cross Tabulations....339

Installing the crosstab() Function....339

Tabulating Survey Results....340

Tabulating City Temperature Readings....342

Reclassifying Values with CASE....345

Using CASE in a Common Table Expression....347

Wrapping Up....349

Chapter 14: Mining Text to Find Meaningful Data....350

Formatting Text Using String Functions....350

Case Formatting....350

Character Information....351

Removing Characters....351

Extracting and Replacing Characters....352

Matching Text Patterns with Regular Expressions....352

Regular Expression Notation....353

Using Regular Expressions with WHERE....355

Regular Expression Functions to Replace or Split Text....356

Turning Text to Data with Regular Expression Functions....357

Full-Text Search in PostgreSQL....370

Text Search Data Types....370

Creating a Table for Full-Text Search....372

Searching Speech Text....373

Ranking Query Matches by Relevance....377

Wrapping Up....379

Chapter 15: Analyzing Spatial Data with PostGIS....380

Enabling PostGIS and Creating a Spatial Database....381

Understanding the Building Blocks of Spatial Data....381

Understanding Two-Dimensional Geometries....382

Well-Known Text Formats....384

Projections and Coordinate Systems....386

Spatial Reference System Identifier....386

Understanding PostGIS Data Types....387

Creating Spatial Objects with PostGIS Functions....388

Creating a Geometry Type from Well-Known Text....389

Creating a Geography Type from Well-Known Text....391

Using Point Functions....392

Using LineString Functions....392

Using Polygon Functions....393

Analyzing Farmers’ Markets Data....394

Creating and Filling a Geography Column....395

Adding a Spatial Index....396

Finding Geographies Within a Given Distance....398

Finding the Distance Between Geographies....400

Finding the Nearest Geographies....403

Working with Census Shapefiles....404

Understanding the Contents of a Shapefile....405

Loading Shapefiles....406

Exploring the Census 2019 Counties Shapefile....410

Examining Demographics Within a Distance....413

Performing Spatial Joins....416

Exploring Roads and Waterways Data....416

Joining the Census Roads and Water Tables....417

Finding the Location Where Objects Intersect....418

Wrapping Up....420

Chapter 16: Working with JSON Data....421

Understanding JSON Structure....421

Considering When to Use JSON with SQL....424

Using json and jsonb Data Types....425

Importing and Indexing JSON Data....426

Using json and jsonb Extraction Operators....428

Key Value Extraction....429

Array Element Extraction....430

Path Extraction....432

Containment and Existence....434

Analyzing Earthquake Data....437

Exploring and Loading the Earthquake Data....438

Working with Earthquake Times....440

Finding the Largest and Most-Reported Earthquakes....442

Converting Earthquake JSON to Spatial Data....445

Generating and Manipulating JSON....450

Turning Query Results into JSON....450

Adding, Updating, and Deleting Keys and Values....453

Using JSON Processing Functions....456

Finding the Length of an Array....456

Returning Array Elements as Rows....457

Wrapping Up....459

Chapter 17: Saving Time with Views, Functions, and Triggers....461

Using Views to Simplify Queries....462

Creating and Querying Views....463

Creating and Refreshing a Materialized View....467

Inserting, Updating, and Deleting Data Using a View....469

Creating Your Own Functions and Procedures....474

Creating the percent_change() Function....475

Using the percent_change() Function....476

Updating Data with a Procedure....478

Using the Python Language in a Function....482

Automating Database Actions with Triggers....484

Logging Grade Updates to a Table....485

Automatically Classifying Temperatures....490

Wrapping Up....493

Chapter 18: Using PostgreSQL from the Command Line....495

Setting Up the Command Line for psql....496

Windows psql Setup....496

macOS psql Setup....501

Linux psql Setup....504

Working with psql....505

Launching psql and Connecting to a Database....505

Running SQL Queries on psql....509

Navigating and Formatting Results....512

Meta-Commands for Database Information....516

Importing, Exporting, and Using Files....518

Additional Command Line Utilities to Expedite Tasks....522

Adding a Database with createdb....522

Loading Shapefiles with shp2pgsql....523

Wrapping Up....524

Chapter 19: Maintaining Your Database....525

Recovering Unused Space with VACUUM....525

Tracking Table Size....527

Monitoring the Autovacuum Process....529

Running VACUUM Manually....531

Reducing Table Size with VACUUM FULL....532

Changing Server Settings....532

Locating and Editing postgresql.conf....533

Reloading Settings with pg_ctl....535

Backing Up and Restoring Your Database....537

Using pg_dump to Export a Database or Table....537

Restoring a Database Export with pg_restore....538

Exploring Additional Backup and Restore Options....538

Wrapping Up....539

Chapter 20: Telling Your Data’s Story....541

Start with a Question....542

Document Your Process....542

Gather Your Data....543

No Data? Build Your Own Database....544

Assess the Data’s Origins....544

Interview the Data with Queries....545

Consult the Data’s Owner....545

Identify Key Indicators and Trends over Time....546

Ask Why....549

Communicate Your Findings....549

Wrapping Up....551

Appendix: Additional PostgreSQL Resources....552

PostgreSQL Development Environments....552

PostgreSQL Utilities, Tools, and Extensions....553

PostgreSQL News and Community....554

Documentation....555

Index....557

Practical SQL is an approachable and fast-paced guide to SQL (Structured Query Language), the standard programming language for defining, organizing, and exploring data in relational databases. Anthony DeBarros, a journalist and data analyst, focuses on using SQL to find the story within your data. The examples and code use the open-source database PostgreSQL and its companion pgAdmin interface, and the concepts you learn will apply to most database management systems, including MySQL, Oracle, SQLite, and others.*

You’ll first cover the fundamentals of databases and the SQL language, then build skills by analyzing data from real-world datasets such as US Census demographics, New York City taxi rides, and earthquakes from US Geological Survey. Each chapter includes exercises and examples that teach even those who have never programmed before all the tools necessary to build powerful databases and access information quickly and efficiently.

You’ll learn how to:

• Create databases and related tables using your own data
• Aggregate, sort, and filter data to find patterns
• Use functions for basic math and advanced statistical operations
• Identify errors in data and clean them up
• Analyze spatial data with a geographic information system (PostGIS)
• Create advanced queries and automate tasks

This updated second edition has been thoroughly revised to reflect the latest in SQL features, including additional advanced query techniques for wrangling data. This edition also has two new chapters: an expanded set of instructions on for setting up your system plus a chapter on using PostgreSQL with the popular JSON data interchange format.

Learning SQL doesn’t have to be dry and complicated. Practical SQL delivers clear examples with an easy-to-follow approach to teach you the tools you need to build and manage your own databases.

* Microsoft SQL Server employs a variant of the language called T-SQL, which is not covered by Practical SQL.