Practical SQL A Beginner Guide to Storytelling with Data 2nd Edition by Anthony DeBarros ISBN 1718501072 9781718501072

Practical SQL A Beginner’s Guide to Storytelling with Data 2nd Edition by Anthony DeBarros – Ebook PDF Instant Download/Delivery: 1718501072, 9781718501072
Full download Practical SQL A Beginner’s Guide to Storytelling with Data 2nd Edition after payment

Product details:

ISBN 10: 1718501072 
ISBN 13: 9781718501072
Author: Anthony DeBarros

Analyze data like a pro, even if you’re a beginner. 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 Geological 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. You’ll learn how to: Create databases and related tables using your own data Aggregate, sort, and filter data to find patterns Use functions for basic math and advanced statistical operations Identify errors in data and clean them up Analyze spatial data with a geographic information system (PostGIS) Create advanced queries and automate tasks This updated second edition has been thoroughly revised to reflect the latest in SQL features, including additional advanced query techniques for wrangling data. This edition also has two new chapters: an expanded set of instructions on for setting up your system plus a chapter on using PostgreSQL with the popular JSON data interchange format.  Learning SQL doesn’t have to be dry and complicated. Practical SQL delivers clear examples with an easy-to-follow approach to teach you the tools you need to build and manage your own databases. * Microsoft SQL Server employs a variant of the language called T-SQL, which is not covered by Practical SQL.

Practical SQL A Beginner’s Guide to Storytelling with Data 2nd Table of contents:

Chapter 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

Chapter 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

Chapter 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

Chapter 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

Chapter 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

Chapter 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

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

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

Chapter 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

Chapter 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

Chapter 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

Chapter 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

Chapter 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

Chapter 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

Chapter 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 PostgreSQL

Text Search Data Types

Creating a Table for Full-Text Search

Searching Speech Text

Ranking Query Matches by Relevance

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

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

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

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

Chapter 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

Chapter 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

Chapter 19: 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 Export a Database or Table

Restoring a Database Export with pg_restore

Exploring Additional Backup and Restore Options

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

People also search for Practical SQL A Beginner’s Guide to Storytelling with Data 2nd:

Tags:

Anthony DeBarros,Practical SQL,Beginner,Storytelling,Data