The Applied SQL Data Analytics Workshop : Develop Your Practical Skills and Prepare to Become a Professional Data Analyst, 2nd Edition.

Format:

Book

Author/Creator:

Goldwasser, Matt.

Contributor:

Malik, Upom.

Johnston, Benjamin.

Language:

English

Physical Description:

1 online resource (483 pages)

Edition:

2nd ed.

Place of Publication:

Birmingham : Packt Publishing, Limited, 2020.

Summary:

The Applied SQL Data Analytics Workshop is the ideal companion on your journey to extracting information from raw business data. Whether it's importing data, analyzing complex data types, or optimizing your queries, this book equips you with the skills you need to build your knowledge in data analysis with SQL.

Contents:

Cover

FM

Copyright

Table of Contents

Preface

Chapter 1: Introduction to SQL for Analytics

Introduction

The World of Data

Types of Data

Data Analytics and Statistics

Types of Statistics

Activity 1.01: Classifying a New Dataset

Methods of Descriptive Statistics

Univariate Analysis

Data Frequency Distribution

Exercise 1.01: Creating a Histogram

Quantiles

Exercise 1.02: Calculating the Quartiles for Add-on Sales

Central Tendency

Exercise 1.03: Calculating the Central Tendency of Add-on Sales

Dispersion

Exercise 1.04: Dispersion of Add-on Sales

Bivariate Analysis

Scatterplots

Pearson Correlation Coefficient

Exercise 1.05: Calculating the Pearson Correlation Coefficient for Two Variables

Interpreting and Analyzing the Correlation Coefficient

Activity 1.02: Exploring Dealership Sales Data

Working with Missing Data

Statistical Significance Testing

Common Statistical Significance Tests

Relational Databases and SQL

Advantages and Disadvantages of SQL Databases

Basic Data Types of SQL

Numeric

Character

Boolean

Datetime

Data Structures: JSON and Arrays

Reading Tables: The SELECT Query

Basic Anatomy and Working of a SELECT Query

Basic Keywords in a SELECT Query

The SELECT and FROM Statements

The WHERE Clause

The AND/OR Clause

The IN/NOT IN Clause

The ORDER BY Clause

The LIMIT Clause

The IS NULL/IS NOT NULL Clause

Exercise 1.06: Querying the salespeople Table Using Basic Keywords in a SELECT Query

Activity 1.03: Querying the customers Table Using Basic Keywords in a SELECT Query

Creating Tables

Creating Blank Tables

Exercise 1.07: Creating a Table in SQL

Creating Tables with SELECT

Updating Tables

Adding and Removing Columns

Adding New Data

Updating Existing Rows.

Exercise 1.08: Updating the Table to Increase the Price of a Vehicle

Deleting Data and Tables

Deleting Values from a Row

Deleting Rows from a Table

Deleting Tables

Exercise 1.09: Deleting an Unnecessary Reference Table

Activity 1.04: Creating and Modifying Tables for Marketing Operations

SQL and Analytics

Summary

Chapter 2: SQL for Data Preparation

Assembling Data

Connecting Tables Using JOIN

Types of Joins

Inner Joins

Outer Joins

Cross Joins

Exercise 2.01: Using Joins to Analyze a Sales Dealership

Subqueries

Unions

Exercise 2.02: Generating an Elite Customer Party Guest List Using UNION

Common Table Expressions

Transforming Data

The CASE WHEN Function

Exercise 2.03: Using the CASE WHEN Function to Get Regional Lists

The COALESCE Function

The NULLIF Function

The LEAST/GREATEST Function

The Casting Function

The DISTINCT and DISTINCT ON Functions

Activity 2.01: Building a Sales Model Using SQL Techniques

Chapter 3: Aggregate and Window Functions

Aggregate Functions

Exercise 3.01: Using Aggregate Functions to Analyze Data

Aggregate Functions with GROUP BY

The GROUP BY Clause

Multiple Column GROUP BY

Exercise 3.02: Calculating the Cost by Product Type Using GROUP BY

Grouping Sets

Ordered Set Aggregates

The HAVING Clause

Exercise 3.03: Calculating and Displaying Data Using the HAVING Clause

Using Aggregates to Clean Data and Examine Data Quality

Finding Missing Values with GROUP BY

Measuring Data Quality with Aggregates

Activity 3.01: Analyzing Sales Data Using Aggregate Functions

Window Functions

The Basics of Window Functions

Exercise 3.04: Analyzing Customer Data Fill Rates over Time

The WINDOW Keyword

Statistics with Window Functions.

Exercise 3.05: Rank Order of Hiring

Window Frame

Exercise 3.06: Team Lunch Motivation

Activity 3.02: Analyzing Sales Using Window Frames and Window Functions

Chapter 4: Importing and Exporting Data

The COPY Command

Copying Data with psql

Configuring COPY and \copy

Using COPY and \copy to Bulk Upload Data to Your Database

Exercise 4.01: Exporting Data to a File for Further Processing in Excel

Using R with Our Database

Why Use R?

Getting Started with R

Using Python with Our Database

Why Use Python?

Getting Started with Python

Improving Postgres Access in Python with SQLAlchemy and pandas

What is SQLAlchemy?

Using Python with Jupyter Notebook

Reading and Writing to our Database with pandas

Exercise 4.02: Reading Data and Visualizing Data in Python

Writing Data to the Database Using Python

Improving Python Write Speed with COPY

Reading and Writing CSV File with Python

Best Practices for Importing and Exporting Data

Going Passwordless

Activity 4.01: Using an External Dataset to Discover Sales Trends

Chapter 5: Analytics Using Complex Data Types

Date and Time Data Types for Analysis

Starting with the date Type

Transforming Date Types

Intervals

Exercise 5.01: Analytics with Time Series Data

Performing Geospatial Analysis in PostgreSQL

Latitude and Longitude

Representing Latitude and Longitude in PostgreSQL

Exercise 5.02: Geospatial Analysis

Using Array Data Types in PostgreSQL

Starting with Arrays

Exercise 5.03: Analyzing Sequences Using Arrays

Using JSON Data Types in PostgreSQL

JSONB: Pre-Parsed JSON

Accessing Data from a JSON or JSONB Field

Leveraging the JSON Path Language for JSONB Fields

Creating and Modifying Data in a JSONB Field.

Exercise 5.04: Searching through JSONB

Text Analytics Using PostgreSQL

Tokenizing Text

Exercise 5.05: Performing Text Analytics

Performing Text Search

Optimizing Text Search on PostgreSQL

Activity 5.01: Sales Search and Analysis

Chapter 6: Performant SQL

Database Scanning Methods

Query Planning

Scanning and Sequential Scans

Exercise 6.01: Interpreting the Query Planner

Activity 6.01: Query Planning

Index Scanning

B The B-tree Index

Exercise 6.02: Creating an Index Scan

Activity 6.02: Implementing Index Scans

The Hash Index

Exercise 6.03: Generating Several Hash Indexes to Investigate Performance

Activity 6.03: Implementing Hash Indexes

Effective Index Use

Performant Joins

Exercise 6.04: Determining the Use of Inner Joins

Activity 6.04: Implementing Performant Joins

Functions and Triggers

Function Definitions

Exercise 6.05: Creating Functions without Arguments

Activity 6.05: Defining a Maximum Sale Function

Exercise 6.06: Creating Functions with Arguments

The \df and \sf commands

Activity 6.06: Creating Functions with Arguments

Triggers

Exercise 6.07: Creating Triggers to Update Fields

Activity 6.07: Creating a Trigger to Track Average Purchases

Killing Queries

Exercise 6.08: Canceling a Long-Running Query

Activity 6.08: Terminating a Long-Running Query

Chapter 7: The Scientific Method and Applied Problem Solving

Case Study

The Scientific Method

Exercise 7.01: Preliminary Data Collection Using SQL Techniques

Exercise 7.02: Extracting the Sales Information

Activity 7.01: Quantifying the Sales Drop

Exercise 7.03: Launch Timing Analysis

Activity 7.02: Analyzing the Difference in the Sales Price Hypothesis

Exercise 7.04: Analyzing Sales Growth by Email Opening Rate.

Exercise 7.05: Analyzing the Performance of the Email Marketing Campaign

Conclusions

In-Field Testing

Appendix

Index.

Notes:

Description based on publisher supplied metadata and other sources.

ISBN:

1-80020-537-6

OCLC:

1245667586