Practical SQL, 2nd Edition: A Beginner’s Guide to Storytelling with Data
Analyze data like a pro, even if you’re a beginner.
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.
Title Page Copyright About the Author Preface to the Second Edition Acknowledgments Introduction What Is SQL? Why SQL? Who Is This Book For? What You’ll Learn Chapter 1: Setting Up Your Coding Environment Installing a Text Editor Downloading Code and Data from GitHub Installing PostgreSQL and pgAdmin Windows Installation macOS Installation Linux Installation Working with pgAdmin Launching pgAdmin and Setting a Master Password Connecting to the Default postgres Database Exploring the Query Tool Customizing pgAdmin Alternatives to pgAdmin Wrapping Up Chapter 2: Creating Your First Database and Table Understanding Tables Creating a Database Executing SQL in pgAdmin Connecting to the analysis Database Creating a Table Using the CREATE TABLE Statement Making the teachers Table Inserting Rows into a Table Using the INSERT Statement Viewing the Data Getting Help When Code Goes Bad Formatting SQL for Readability Wrapping Up Chapter 3: Beginning Data Exploration with SELECT Basic SELECT Syntax Querying a Subset of Columns Sorting Data with ORDER BY Using DISTINCT to Find Unique Values Filtering Rows with WHERE Using LIKE and ILIKE with WHERE Combining Operators with AND and OR Putting It All Together Wrapping Up Chapter 4: Understanding Data Types Understanding Characters Understanding Numbers Using Integers Auto-Incrementing Integers Using Decimal Numbers Choosing Your Number Data Type Understanding Dates and Times Using the interval Data Type in Calculations Understanding JSON and JSONB Using Miscellaneous Types Transforming Values from One Type to Another with CAST Using CAST Shortcut Notation Wrapping Up Chapter 5: Importing and Exporting Data Working with Delimited Text Files Handling Header Rows Quoting Columns That Contain Delimiters Using COPY to Import Data Importing Census Data Describing Counties Creating the us_counties_pop_est_2019 Table Understanding Census Columns and Data Types Performing the Census Import with COPY Inspecting the Import Importing a Subset of Columns with COPY Importing a Subset of Rows with COPY Adding a 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 Chapter 6: Basic Math and Stats with SQL Understanding Math Operators and Functions Understanding Math and Data Types Adding, Subtracting, and Multiplying Performing Division and Modulo Using 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 Using Aggregate Functions for Averages and Sums Finding the Median Finding the Median with Percentile Functions Finding Median and Percentiles with Census Data Finding Other Quantiles with Percentile Functions Finding the Mode Wrapping Up Chapter 7: Joining Tables in a Relational Database Linking Tables Using JOIN Relating Tables with Key Columns Querying Multiple Tables Using JOIN Understanding JOIN Types JOIN LEFT JOIN and RIGHT JOIN FULL OUTER JOIN CROSS JOIN Using NULL to Find Rows with Missing Values Understanding the 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 Combining Query Results with Set Operators UNION and UNION ALL INTERSECT and EXCEPT Performing Math on Joined Table Columns Wrapping Up Chapter 8: Table Design That Works for You Following Naming Conventions Quoting Identifiers Enables Mixed Case Pitfalls with Quoting Identifiers Guidelines for Naming Identifiers Controlling Column Values with Constraints Primary Keys: Natural vs. Surrogate Foreign Keys How to Automatically Delete Related Records with CASCADE The CHECK Constraint The UNIQUE Constraint The NOT NULL Constraint How to Remove Constraints or Add Them Later Speeding Up Queries with Indexes B-Tree: PostgreSQL’s Default Index Considerations When Using Indexes Wrapping Up Chapter 9: Extracting Information by Grouping and Summarizing Creating the Library Survey Tables Creating the 2018 Library Data Table Creating the 2017 and 2016 Library Data Tables 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 Chapter 10: Inspecting and Modifying Data Importing Data on Meat, Poultry, and Egg Producers Interviewing the Dataset 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 Viewing Modified Data with RETURNING Creating Backup Tables Restoring Missing Column Values Updating Values for Consistency Repairing ZIP Codes Using Concatenation Updating Values Across Tables Deleting Unneeded Data Deleting Rows from a Table Deleting a Column from a Table Deleting a Table from a Database Using Transactions to Save or Revert Changes Improving Performance When Updating Large Tables Wrapping Up Chapter 11: Statistical Functions in SQL 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 Finding Variance and Standard Deviation Creating Rankings with SQL Ranking with rank() and dense_rank() Ranking Within Subgroups with PARTITION BY Calculating Rates for Meaningful Comparisons Finding Rates of Tourism-Related Businesses Smoothing Uneven Data Wrapping Up Chapter 12: Working with Dates and Times Understanding 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 Performing Calculations with Dates and Times Finding Patterns in New York City Taxi Data Finding Patterns in Amtrak Data Wrapping Up Chapter 13: Advanced Query Techniques Using Subqueries Filtering with Subqueries in a WHERE Clause Creating Derived Tables with Subqueries Joining Derived Tables Generating Columns with Subqueries Understanding Subquery Expressions Using Subqueries with LATERAL Using Common Table Expressions Performing 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 Chapter 14: Mining Text to Find Meaningful Data Formatting Text Using String Functions Case Formatting Character Information Removing Characters Extracting and Replacing Characters Matching Text Patterns with Regular Expressions Regular Expression Notation Using Regular Expressions with WHERE Regular Expression Functions to Replace or Split Text Turning Text to Data with 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 Chapter 15: Analyzing Spatial Data with PostGIS Enabling PostGIS and Creating a Spatial Database Understanding the Building Blocks of Spatial Data Understanding Two-Dimensional Geometries Well-Known Text Formats Projections and Coordinate Systems Spatial Reference System Identifier Understanding 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 Using Point Functions Using LineString Functions Using Polygon Functions Analyzing Farmers’ Markets Data Creating and Filling a Geography Column Adding a Spatial Index Finding Geographies Within a Given Distance Finding the Distance Between Geographies Finding the Nearest Geographies Working with Census Shapefiles Understanding the Contents of a Shapefile Loading Shapefiles Exploring the Census 2019 Counties Shapefile Examining Demographics Within a Distance Performing Spatial Joins Exploring Roads and Waterways Data Joining the Census Roads and Water Tables Finding the Location Where Objects Intersect Wrapping Up Chapter 16: Working with JSON Data Understanding JSON Structure Considering When to Use JSON with SQL Using json and jsonb Data Types Importing and Indexing JSON Data Using json and jsonb Extraction Operators Key Value Extraction Array Element Extraction Path Extraction Containment and Existence Analyzing Earthquake Data Exploring and Loading the Earthquake Data Working with Earthquake Times Finding the Largest and Most-Reported Earthquakes Converting Earthquake JSON to Spatial Data Generating and Manipulating JSON Turning Query Results into JSON Adding, Updating, and Deleting Keys and Values Using JSON Processing Functions Finding the Length of an Array Returning Array Elements as Rows Wrapping Up Chapter 17: Saving Time with Views, Functions, and Triggers Using Views to Simplify Queries Creating and Querying Views Creating and Refreshing a Materialized View Inserting, Updating, and Deleting Data Using a View Creating Your Own Functions and Procedures Creating the percent_change() Function Using the percent_change() Function Updating Data with a Procedure Using the Python Language in a Function Automating Database Actions with Triggers Logging Grade Updates to a Table Automatically Classifying Temperatures Wrapping Up Chapter 18: Using PostgreSQL from the Command Line 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 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 Chapter 19: Maintaining Your Database 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 Export a Database or Table Restoring a Database Export with pg_restore Exploring Additional Backup and Restore Options Wrapping Up Chapter 20: Telling Your Data’s Story 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 Appendix: Additional PostgreSQL Resources PostgreSQL Development Environments PostgreSQL Utilities, Tools, and Extensions PostgreSQL News and Community Documentation Index
How to download source code?
1. Go to:
2. Search the book title:
Practical SQL, 2nd Edition: A Beginner’s Guide to Storytelling with Data, sometime you may not get the results, please search the main title
3. Click the book title in the search results
3. Download the Source Code.
1. Disable the AdBlock plugin. Otherwise, you may not get any links.
2. Solve the CAPTCHA.
3. Click download link.
4. Lead to download server to download.