SQL for Data Analytics : Analyze Data Effectively, Uncover Insights and Master Advanced SQL for Real-World Applications.

Format:

Book

Author/Creator:

Shan, Jun.

Contributor:

Li, Haibin.

Goldwasser, Matt.

Malik, Upom.

Johnston, Benjamin.

Language:

English

Subjects (All):

SQL (Computer program language).

Data mining.

Physical Description:

1 online resource (336 pages)

Edition:

1st ed.

Place of Publication:

Birmingham : Packt Publishing, Limited, 2025.

Summary:

Turn SQL into your competitive edge for uncovering patterns and accelerating data-driven business decisions Key Features Solve real business problems with advanced SQL techniques Work with time-series, geospatial, and text data using PostgreSQL Build job-ready analytics skills with hands-on SQL projects Purchase of the print or Kindle book.

Contents:

Cover

FM

Contributors

Preface

Free Benefits with Your Book

Part 1: Data Management Systems

Chapter 1: Introduction to Data Management Systems

Technical requirements

Describing the world with data

Data modeling

Understanding relational databases and SQL

Primary keys and foreign keys

Normalization

Advantages and disadvantages of SQL databases

Setting up a PostgreSQL relational database

Exercise 1.1: Installing PostgreSQL on your local machine

Exercise 1.2: Accessing and understanding PostgreSQL

Exercise 1.3: Utilizing PostgreSQL query tools

Exercise 1.4: Import a sample sqlda database

Introducing the sqlda database

Activity 1

Summary

Chapter 2: Creating Tables with Solid Structures

Running CRUD with SQL

CREATE

READ

UPDATE

DELETE

Creating a table from an existing dataset

Describing columns

Learning about basic data types of SQL

Numeric and monetary

Character

Boolean

Datetime

More data types

Creating a table with an explicit definition

Column constraints and table constraints

Exercise 2.1: Creating and populating tables

Inserting data into a table

Exercise 2.2: Populating a table

Deleting/dropping tables

Exercise 2.3: Deleting an unnecessary table

Activity 2

Chapter 3: Exchanging Data Using COPY

Exporting data from a PostgreSQL database

\COPY in psql

Configuring COPY and \COPY

Importing data into a PostgreSQL database

Exercise 3.1: Exporting data to a file for further processing in Excel

Activity 3

Get This Book's PDF Version and Exclusive Extras

Chapter 4: Manipulating Data with Python

Getting started with Python

Exercise 4.1: Setting up Python on your machine

Managing data with Python.

What is SQLAlchemy?

Using Python with SQLAlchemy and pandas

Reading and writing to a database with pandas

Writing data to the database from Python

Exercise 4.2: Reading, visualizing, and saving data in Python

Activity 4

Part 2: Data Presentation and Manipulation

Chapter 5: Presenting Data with SELECT

Using SELECT expressions

Expression alias

The LIMIT clause

The ORDER BY clause

The DISTINCT and DISTINCT ON functions

Filtering query results

The AND/OR and NOT clauses

The IN/NOT IN clause

The IS NULL/IS NOT NULL clauses

Exercise 5.1: Reading data from the database

Activity 5

Chapter 6: Transforming and Updating Data

Updating table data

Cleaning data

Exercise 6.1: Updating and deleting data

Running data transformation functions

The CASE WHEN function

Functions for different data types

The datetime function

The string function

The casting function

The NULL handling functions

The COALESCE function

The NULLIF function

Exercise 6.2: Data manipulation using functions

Creating user-defined functions

The \df and \sf commands

Exercise 6.3: Creating functions with arguments

Triggers

Changing the table definition

Activity 6

Chapter 7: Defining Datasets from Existing Datasets

Creating derived datasets

Common table expressions

Views

Exercise 7.1: Utilizing subqueries

Joining tables

Inner joins

Outer joins

Left outer joins

Right outer joins

Full outer joins

Cross joins

Exercise 7.2: Using joins to analyze a sales dealership

Running set operations

Exercise 7.3: Generating an elite customer party guest list using UNION

Activity 7

Get This Book's PDF Version and Exclusive Extras.

Chapter 8: Aggregating Data with GROUP BY

Aggregating data

Exercise 8.1: Using aggregate functions to analyze data

Aggregating with GROUP BY clause

The GROUP BY clause

Exercise 8.2: Calculating the cost by product type using GROUP BY

Grouping sets

Ordered set aggregates

Applying the HAVING clause

Exercise 8.3: Calculating and displaying data using the HAVING clause

Activity 8

Chapter 9: Inter-Row Operation with Window Functions

Defining window functions

The basics of window functions

Exercise 9.1: Analyzing Customer Data Fill Rates over Time

Using advanced window definitions

Common window functions

The WINDOW keyword

Window frame

Exercise 9.2: Team Lunch Motivation

Activity 9

Part 3: Advanced Topics on Analytics

Chapter 10: Performant SQL

Scanning the database

Query planning

Exercise 10.1: Interpreting the query planner

Scanning the index

The B-tree index

Exercise 10.2: Creating an index scan

The hash index

Exercise 10.3: Generating hash indexes to investigate performance

Effective index use

Activity 10

Chapter 11: Processing JSON and Arrays

Understanding types of data

Using JSON

JSONB: Pre-parsed JSON

Accessing data from a JSON or JSONB field

Leveraging JSON path

Creating and modifying data in a JSONB field

Exercise 11.1: Searching through JSONB

Using arrays to process element collections

Exercise 11.2: Analyzing sequences using arrays

Activity 11

Chapter 12: Advanced Data Types: Date, Text, and Geospatial

Using date and time in data analytics.

The DATE type

Transforming DATE data types

Intervals

Exercise 12.1: Analytics with time-series data

Understanding text processing

String characteristics and manipulation

Identifying string patterns

Exercise 12.2: Text processing

Applying geospatial data

Latitude and longitude

Exercise 12.3: Geospatial analysis

Activity 12

Chapter 13: Inferential Statistics Using SQL

Moving from analytics to statistics

Understanding fundamental concepts: population versus samples, parameters versus statistics

Population versus samples

Parameters versus statistics

Estimating: point estimates and confidence intervals

Testing hypotheses

Analyzing correlation and performing regression

Interpreting regression results

Understanding a simple linear regression example

Activity 13

Chapter 14: A Case Study for Analytics Using SQL

Understanding the data analytics system

Understanding dimensional models

Understanding data warehouse architecture

Applying data analysis using SQL

Exercise 14.1: Copying from a file into the staging table

Exercise 14.2: Quality check for raw data

Exercise 14.3: Loading data into the star schema

Exercise 14.4: Delivery data for analysis

Chapter 15: Unlock Your Exclusive Benefits

Unlock this Book's Free Benefits in 3 Easy Steps

Need help?

Other Books You May Enjoy

Index.

Notes:

Description based upon print version of record.

Exercise 6.2: Data manipulation using functions

Description based on publisher supplied metadata and other sources.

ISBN:

1-83664-624-0

9781836646242

OCLC:

1547907503