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