Home > Categories > Programming > Practical SQL, 2nd Edition
Practical SQL, 2nd Edition
📥 Download PDF

Practical SQL, 2nd Edition

by Anthony DeBarros

Programming

Book Details

Book Title

Practical SQL, 2nd Edition

Author

Anthony DeBarros

Publisher

No Starch Press

Publication Date

2022

ISBN

9781718501065

Number of Pages

612

Language

English

Format

PDF

File Size

4.9MB

Subject

sql-for-storytelling

Table of Contents

  • Title Page
  • Copyright
  • About the Author
  • Preface to the Second Edition
  • Acknowledgments
  • Introduction
  • Chapter 1: Setting Up Your Coding Environment
  • Installing a Text Editor
  • Downloading Code and Data from GitHub
  • Installing PostgreSQL and pgAdmin
  • Working with pgAdmin
  • Alternatives to pgAdmin
  • Wrapping Up
  • Chapter 2: Creating Your First Database and Table
  • Understanding Tables
  • Creating a Database
  • Creating a Table
  • Inserting Rows into a Table
  • Getting Help When Code Goes Bad
  • Formatting SQL for Readability
  • Wrapping Up
  • Chapter 3: Beginning Data Exploration with SELECT
  • Basic SELECT Syntax
  • Sorting Data with ORDER BY
  • Using DISTINCT to Find Unique Values
  • Filtering Rows with WHERE
  • Putting It All Together
  • Wrapping Up
  • Chapter 4: Understanding Data Types
  • Understanding Characters
  • Understanding Numbers
  • 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
  • Using COPY to Import Data
  • Importing Census Data Describing Counties
  • 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
  • Importing and Exporting Through pgAdmin
  • Wrapping Up
  • Chapter 6: Basic Math and Stats with SQL
  • Understanding Math Operators and Functions
  • Doing Math Across Census Table Columns
  • Using Aggregate Functions for Averages and Sums
  • Finding the Median
  • 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
  • Using NULL to Find Rows with Missing Values
  • Understanding the Three Types of Table Relationships
  • Selecting Specific Columns in a Join
  • Simplifying JOIN Syntax with Table Aliases
  • Joining Multiple Tables
  • Combining Query Results with Set Operators
  • Performing Math on Joined Table Columns
  • Wrapping Up
  • Chapter 8: Table Design That Works for You
  • Following Naming Conventions
  • Controlling Column Values with Constraints
  • Speeding Up Queries with Indexes
  • Wrapping Up
  • Chapter 9: Extracting Information by Grouping and Summarizing
  • Creating the Library Survey Tables
  • Exploring the Library Data Using Aggregate Functions
  • Wrapping Up
  • Chapter 10: Inspecting and Modifying Data
  • Importing Data on Meat, Poultry, and Egg Producers
  • Interviewing the Dataset
  • Modifying Tables, Columns, and Data
  • Deleting Unneeded Data
  • 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
  • Creating Rankings with SQL
  • Calculating Rates for Meaningful Comparisons
  • 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
  • Working with Time Zones
  • Performing Calculations with Dates and Times
  • Wrapping Up
  • Chapter 13: Advanced Query Techniques
  • Using Subqueries
  • Using Common Table Expressions
  • Performing Cross Tabulations
  • 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
  • Matching Text Patterns with Regular Expressions
  • Full-Text Search in PostgreSQL
  • 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
  • Understanding PostGIS Data Types
  • Creating Spatial Objects with PostGIS Functions
  • Analyzing Farmers’ Markets Data
  • Working with Census Shapefiles
  • Performing Spatial Joins
  • 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
  • Analyzing Earthquake Data
  • Generating and Manipulating JSON
  • Using JSON Processing Functions
  • Wrapping Up
  • Chapter 17: Saving Time with Views, Functions, and Triggers
  • Using Views to Simplify Queries
  • Creating Your Own Functions and Procedures
  • Automating Database Actions with Triggers
  • Wrapping Up
  • Chapter 18: Using PostgreSQL from the Command Line
  • Setting Up the Command Line for psql
  • Working with psql
  • Additional Command Line Utilities to Expedite Tasks
  • Wrapping Up
  • Chapter 19: Maintaining Your Database
  • Recovering Unused Space with VACUUM
  • Changing Server Settings
  • Backing Up and Restoring Your Database
  • 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