Practical sql: a beginner’s Guide to Storytelling with Data pdfdrive com

Document Outline

• Title Page
• Copyright Page
• About the Author
• About the Technical Reviewer
• Brief Contents
• Contents in Detail
• Foreword by Sarah Frostenson
• Acknowledgments
• Introduction
  • What Is SQL?
  • Why Use SQL?
  • About This Book
  • Using the Book’s Code Examples
  • Using PostgreSQL
  • Wrapping Up
• 1. Creating Your First Database and Table
  • Creating a Database
  • Creating a Table
  • Inserting Rows into a Table
  • When Code Goes Bad
  • Formatting SQL for Readability
  • Wrapping Up
  • Try It Yourself
• 2. Beginning Data Exploration with SELECT
  • Basic SELECT Syntax
  • Sorting Data with ORDER BY
  • Filtering Rows with WHERE
  • Putting It All Together
  • Wrapping Up
  • Try It Yourself
• 3. Understanding Data Types
  • Characters
  • Numbers
  • Dates and Times
  • Using the interval Data Type in Calculations
  • Miscellaneous Types
  • Transforming Values from One Type to Another with CAST
  • CAST Shortcut Notation
  • Wrapping Up
  • Try It Yourself
• 4. 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
  • Adding a Default Value to a Column During Import
  • Using COPY to Export Data
  • Importing and Exporting Through pgAdmin
  • Wrapping Up
  • Try It Yourself
• 5. Basic Math and Stats With SQL
  • Math Operators
  • Doing Math Across Census Table Columns
  • Aggregate Functions for Averages and Sums
  • Finding the Median
  • Finding the Mode
  • Wrapping Up
  • Try It Yourself
• 6. Joining Tables in a Relational Database
  • Linking Tables Using JOIN
  • Relating Tables with Key Columns
  • Querying Multiple Tables Using JOIN
  • JOIN Types
  • Using NULL to Find Rows with Missing Values
  • Three Types of Table Relationships
  • Selecting Specific Columns in a Join
  • Simplifying JOIN Syntax with Table Aliases
  • Joining Multiple Tables
  • Performing Math on Joined Table Columns
  • Wrapping Up
  • Try It Yourself
• 7. Table Design That Works for You
  • Naming Tables, Columns, and Other Identifiers
  • Controlling Column Values with Constraints
  • Speeding Up Queries with Indexes
  • Wrapping Up
  • Try It Yourself
• 8. Extracting Information by Grouping and Summarizing
  • Creating the Library Survey Tables
  • Exploring the Library Data Using Aggregate Functions
  • Wrapping Up
  • Try It Yourself
• 9. Inspecting and Modifying Data
  • Importing Data on Meat, Poultry, and Egg Producers
  • Interviewing the Data Set
  • Modifying Tables, Columns, and Data
  • Deleting Unnecessary Data
  • Using Transaction Blocks to Save or Revert Changes
  • Improving Performance When Updating Large Tables
  • Wrapping Up
  • Try It Yourself
• 10. Statistical Functions in SQL
  • Creating a Census Stats Table
  • Creating Rankings with SQL
  • Calculating Rates for Meaningful Comparisons
  • Wrapping Up
  • Try It Yourself
• 11. Working with Dates and Times
  • Data Types and Functions for Dates and Times
  • Manipulating Dates and Times
  • Working with Time Zones
  • Calculations with Dates and Times
  • Wrapping Up
  • Try It Yourself
• 12. Advanced Query Techniques
  • Using Subqueries
  • Common Table Expressions
  • Cross Tabulations
  • Reclassifying Values with CASE
  • Using CASE in a Common Table Expression
  • Wrapping Up
  • Try It Yourself
• 13. Mining Text to Find Meaningful Data
  • Formatting Text Using String Functions
  • Matching Text Patterns with Regular Expressions
  • Full Text Search in PostgreSQL
  • Wrapping Up
  • Try It Yourself
• 14. Analyzing Spatial Data with PostGIS
  • Installing PostGIS and Creating a Spatial Database
  • The Building Blocks of Spatial Data
  • Two-Dimensional Geometries
  • PostGIS Data Types
  • Creating Spatial Objects with PostGIS Functions
  • Analyzing Farmers’ Markets Data
  • Working with Census Shapefiles
  • Performing Spatial Joins
  • Wrapping Up
  • Try It Yourself
• 15. Saving Time with Views, Functions, and Triggers
  • Using Views to Simplify Queries
  • Programming Your Own Functions
  • Automating Database Actions with Triggers
  • Wrapping Up
  • Try It Yourself
• 16. 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
  • Try It Yourself
• 17. Maintaining Your Database
  • Recovering Unused Space with VACUUM
  • Changing Server Settings
  • Backing Up and Restoring Your Database
  • Wrapping Up
  • Try It Yourself
• 18. Identifying and Telling the Story Behind Your Data
  • 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
  • Try It Yourself
• Appendix: Additional PostgreSQL Resources
  • PostgreSQL Development Environments
  • PostgreSQL Utilities, Tools, and Extensions
  • PostgreSQL News
  • Documentation
• Index