فهرست مطالب

• 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