Practical SQL : a beginner's guide to storytelling with data

Practical SQL A beginner's guide to storytelling with data

Anthony DeBarros

Book - 2022

"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 Geolog...ical 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"--

Saved in:
Place a Hold Save to List Email this

2nd Floor Show me where

005.7565/SQL/DeBarros
1 / 1 copies available
Location Call Number   Status
2nd Floor 005.7565/SQL/DeBarros Checked In
Subjects
SQL (Computer program language)
Database design.
Published
San Francisco : No Starch Press [2022]
Language
English
Main Author
Anthony DeBarros (author)
Edition
2nd edition
Item Description
Includes index.
Physical Description
xxviii, 427 pages : illustrations ; 24 cm
ISBN
9781718501065
  • Preface to the Second Edition
  • Acknowledgments
  • Introduction
  • What Is SQL?
  • Why SQL?
  • Who Is This Book For?
  • What You'll Learn
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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 PosfgreSQL
  • Text Search Data Types
  • Creating a Table for Full-Text Search
  • Searching Speech Text
  • Ranking Query Matches by Relevance
  • Wrapping Up
  • 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
  • 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
  • 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
  • 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
  • 19. Maintaining Your Database
  • Recovering Unused Space with VACUUM
  • Tracking Table Size
  • Monitoring the Autovacuum Process
  • Running VACUUM Manually
  • Reducing Tabie 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
  • 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

Similar Items