دانلود کتاب Practical SQL: A Beginner's Guide to Storytelling with Data

فهرست مطالب :

Brief Contents
Contents in Detail
Foreword
Acknowledgments
Introduction
What Is SQL?
Why Use SQL?
About This Book
Using the Book’s Code Examples
Using PostgreSQL
Installing PostgreSQL
Working with pgAdmin
Alternatives to pgAdmin
Wrapping Up
Chapter 1: Creating Your First
Create a Database
Executing SQL in pgAdmin
Connecting to the Analysis Database
Create a Table
The CREATE TABLE Statement
Making the teachers Table
Insert Rows into a Table
The INSERT Statement
Viewing the Data
When Code Goes Bad
Formatting SQL for Readability
Wrapping Up
Chapter 2: Beginning Data Exploration with SELECT
Basic SELECT Syntax
Querying a Subset of Columns
Using DISTINCT to Find Unique Values
Sorting Data with ORDER BY
Filter Rows with WHERE
Using LIKE and ILIKE with WHERE
Combining Operators with AND and OR
Putting It All Together
Wrapping Up
try it yourself
Chapter 3: Understanding Data Types
Characters
Numbers
Integers
Auto-Incrementing Integers
Decimal Numbers
Choosing Your Number Data Type
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
Chapter 4: Importing and Exporting Data
Working with Delimited Text Files
Quoting Columns that Contain Delimiters
Handling Header Rows
Using COPY to Import Data
Importing Census Data Describing Counties
Creating the us_counties_2010 Table
Census Columns and Data Types
Performing the Census Import with COPY
Importing a Subset of Columns with COPY
Add a Default Value to a Column During Import
Using COPY to Export Data
Exporting All Data
Exporting Particular Columns
Exporting Query Results
Import and Export Through pgAdmin
Wrapping Up
try it yourself
Chapter 5: Basic Math and Stats with SQL
Math Operators
Math and Data Types
Adding, Subtracting, and Multiplying
Division and Modulo
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
Aggregate Functions for Average and Sums
Finding the Median
Percentile Functions for Median
Median and Percentiles with Census Data
Percentile Functions for Other Quantiles
Creating a median() Function
Finding the Mode
Wrapping Up
try it yourself
Chapter 6: Joining Tables in a
Linking Tables Using JOIN
Relating Tables with Key Columns
Querying Multiple Tables Using JOIN
JOIN Types
JOIN
LEFT JOIN and RIGHT JOIN
FULL OUTER JOIN
CROSS JOIN
Using NULL to Find Rows with Missing Values
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
Performing Math on Joined Table Columns
Wrapping Up
try it yourself
Chapter 7: Table Design That
Naming Tables, Columns, and Other Identifiers
Using Quotes Around Identifiers to Enable Mixed Case
Pitfalls with Quoting Identifiers
Guidelines for Naming Identifiers
Controlling Column Values with Constraints
Primary Keys: Natural vs. Surrogate
Foreign Keys
Automatically Deleting Related Records with CASCADE
The CHECK Constraint
The UNIQUE Constraint
The NOT NULL Constraint
Removing Constraints or Adding Them Later
Speeding Queries with Indexes
B-Tree, PostgreSQL’s Default Index
Considerations When Using Indexes
Wrapping Up
try it yourself
Chapter 8: Extracting Information by Grouping and Summarizing
Creating the Library Survey Tables
Creating the 2014 Library Data Table
Creating the 2009 Library Data Table
Exploring the Libraries 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 9: Inspecting and Modifying Data
Importing Data on Meat, Poultry, and Egg Producers
Interviewing the Data Set
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
Updating Values Across Tables
Updating Values for Consistency
Restoring Missing Column Values
Creating Backup Tables
Modifying Values with UPDATE
Deleting Unnecessary Data
Deleting Rows from a Table
Deleting a Column from a Table
Deleting a Table from a Database
Using Transaction Blocks to Save or Revert Changes
Improving Performance When Updating Large Tables
Wrapping Up
Repairing ZIP Codes Using Concatenation
Chapter 10: Statistical Functions in SQL
Creating a Census Stats Table
Measuring Correlation with corr(Y, X)
Finding the Effect of an Independent Variable with r-squared
Predicting Values with Regression Analysis
Checking Additional Correlations
Creating Rankings with SQL
Ranking with rank() and dense_rank()
Ranking Within Subgroups with PARTITION BY
Calculating Rates for Meaningful Comparisons
Wrapping Up
try it yourself
Chapter 11: Working with Dates and Times
Data Types and Functions for Dates and Times
Manipulating Dates and Times
Extracting the Components of a timestamp
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
Calculations with Dates and Times
Finding Patterns in New York City Taxi Data
Finding Patterns in Amtrak Data
Wrapping Up
try it yourself
Chapter 12: Advanced Query Techniques
Using Subqueries
Filtering with Subqueries in a WHERE Clause
Creating Derived Tables with Subqueries
Joining Derived Tables
Generating Columns with Subqueries
Subquery Expressions
Common Table Expressions
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 13: 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
Turning Text to Data with Regular Expression Functions
Using Regular Expressions with WHERE
Additional Regular Expression Functions
Full Text Search in PostgreSQL
Text Search Data Types
Create a Table for Full Text Search
Searching Speech Text
Ranking Query Matches by Relevance
Wrapping Up
try it yourself
Chapter 14: Analyzing Spatial Data with PostGIS
Installing PostGIS and Creating a Spatial Database
The Building Blocks of Spatial Data
Two-Dimensional Geometries
Well-Known Text Formats
A Note on Coordinate Systems
Spatial Reference System Identifier
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
Point Functions
LineString Functions
Polygon Functions
Analyzing Farmers’ Markets Data
Creating and Filling a Geography Column
Adding a GiST Index
Finding Geographies Within a Distance
Finding Distance Between Geographies
Working with Census Shapefiles
Contents of a Shapefile
Loading Shapefiles via the GUI Tool
Exploring the Census 2010 Counties Shapefile
Performing Spatial Joins
Exploring Roads and Waterways Data
Joining the Census Roads and Water Tables
Finding the Location Where Objects Intersect
Wrapping Up
try it yourself
Chapter 15: Saving Time with Views, Functions, and Triggers
Using Views to Simplify Queries
Creating and Querying Views
Inserting, Updating, and Deleting Data Using a View
Programming Your Own Functions
Creating the percent_change() Function
Using the percent_change() Function
Updating Data with a Function
Using the Python Language in a Function
Automating Database Actions with Triggers
Logging Grade Updates to a Table
Automatically Classifying Temperatures
Wrapping Up
try it yourself
Chapter 16: Using PostgreSQL
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
Getting Help
Changing the User and Database Connection
Running SQL Queries on psql
Navigating and Formatting Results
Meta-Commands for Database Information
Import, Export, and Using Files
Additional Command Line Utilities to Expedite Tasks
Adding a Database with createdb
Loading Shapefiles with shp2pgsql
Wrapping Up
try it yourself
Chapter 17: 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 Back Up a Database or Table
Restoring a Database Backup with pg_restore
Additional Backup and Restore Options
Wrapping Up
try it yourself
Chapter 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
Appendix: Additional PostgreSQL Resources
PostgreSQL Development Environments
PostgreSQL Utilities, Tools, and Extensions
PostgreSQL News
Documentation
Index