book

SQL for Data Analytics - Fourth Edition

Name: SQL for Data Analytics - Fourth Edition
ISBN: 9781836646259

by Jun Shan, Haibin Li, Matt Goldwasser, Upom Malik, Benjamin Johnston

November 2025

Beginner to intermediate

336 pages

7h 48m

English

Packt Publishing

Read now

Unlock full access

Preface
Free Benefits with Your Book
Part 1: Data Management Systems
Introduction to Data Management Systems
Technical requirementsFree Benefits with Your BookDescribing the world with dataData modelingUnderstanding relational databases and SQLPrimary keys and foreign keysNormalizationAdvantages and disadvantages of SQL databasesSetting up a PostgreSQL relational databaseExercise 1.1: Installing PostgreSQL on your local machineExercise 1.2: Accessing and understanding PostgreSQLExercise 1.3: Utilizing PostgreSQL query toolsExercise 1.4: Import a sample sqlda databaseIntroducing the sqlda databaseActivity 1SummaryGet This Book’s PDF Version and Exclusive Extras
Creating Tables with Solid Structures
Technical requirementsRunning CRUD with SQLCREATEREADUPDATEDELETECreating a table from an existing datasetDescribing columnsLearning about basic data types of SQLNumeric and monetaryCharacterBooleanDatetimeMore data typesCreating a table with an explicit definitionColumn constraints and table constraintsExercise 2.1: Creating and populating tablesInserting data into a tableExercise 2.2: Populating a tableDeleting/dropping tablesExercise 2.3: Deleting an unnecessary tableActivity 2Summary
Exchanging Data Using COPY
Technical requirementsExporting data from a PostgreSQL database\COPY in psqlConfiguring COPY and \COPYImporting data into a PostgreSQL databaseExercise 3.1: Exporting data to a file for further processing in ExcelActivity 3SummaryGet This Book’s PDF Version and Exclusive Extras
Manipulating Data with Python
Technical requirementsGetting started with PythonExercise 4.1: Setting up Python on your machineManaging data with PythonWhat is SQLAlchemy?Using Python with SQLAlchemy and pandasReading and writing to a database with pandasWriting data to the database from PythonExercise 4.2: Reading, visualizing, and saving data in PythonActivity 4Summary
Part 2: Data Presentation and Manipulation
Presenting Data with SELECT
Technical requirementsUsing SELECT expressionsExpression aliasThe LIMIT clauseThe ORDER BY clauseThe DISTINCT and DISTINCT ON functionsFiltering query resultsThe AND/OR and NOT clausesThe IN/NOT IN clauseThe IS NULL/IS NOT NULL clausesExercise 5.1: Reading data from the databaseActivity 5SummaryGet This Book’s PDF Version and Exclusive Extras
Transforming and Updating Data
Technical requirementsUpdating table dataCleaning dataExercise 6.1: Updating and deleting dataRunning data transformation functionsThe CASE WHEN functionFunctions for different data typesThe datetime functionThe string functionThe casting functionThe NULL handling functionsThe COALESCE functionThe NULLIF functionExercise 6.2: Data manipulation using functionsCreating user-defined functionsThe \df and \sf commandsExercise 6.3: Creating functions with argumentsTriggersChanging the table definitionActivity 6Summary
Defining Datasets from Existing Datasets
Technical requirementsCreating derived datasetsCommon table expressionsViewsExercise 7.1: Utilizing subqueriesJoining tablesInner joinsOuter joinsLeft outer joinsRight outer joinsFull outer joinsCross joinsExercise 7.2: Using joins to analyze a sales dealershipRunning set operationsExercise 7.3: Generating an elite customer party guest list using UNIONActivity 7SummaryGet This Book’s PDF Version and Exclusive Extras

Aggregating Data with GROUP BY
Technical requirementsAggregating dataExercise 8.1: Using aggregate functions to analyze dataAggregating with GROUP BY clauseThe GROUP BY clauseExercise 8.2: Calculating the cost by product type using GROUP BYGrouping setsOrdered set aggregatesApplying the HAVING clauseExercise 8.3: Calculating and displaying data using the HAVING clauseActivity 8Summary
Inter-Row Operation with Window Functions
Technical requirementsDefining window functionsThe basics of window functionsExercise 9.1: Analyzing Customer Data Fill Rates over TimeUsing advanced window definitionsCommon window functionsThe WINDOW keywordWindow frameExercise 9.2: Team Lunch MotivationActivity 9SummaryGet This Book’s PDF Version and Exclusive Extras
Part 3: Advanced Topics on Analytics
Performant SQL
Technical requirementsScanning the databaseQuery planningExercise 10.1: Interpreting the query plannerScanning the indexThe B-tree indexExercise 10.2: Creating an index scanThe hash indexExercise 10.3: Generating hash indexes to investigate performanceEffective index useActivity 10Summary
Processing JSON and Arrays
Technical requirementsUnderstanding types of dataUsing JSONJSONB: Pre-parsed JSONAccessing data from a JSON or JSONB fieldLeveraging JSON pathCreating and modifying data in a JSONB fieldExercise 11.1: Searching through JSONBUsing arrays to process element collectionsExercise 11.2: Analyzing sequences using arraysActivity 11SummaryGet This Book’s PDF Version and Exclusive Extras
Advanced Data Types: Date, Text, and Geospatial
Technical requirementsUsing date and time in data analyticsThe DATE typeTransforming DATE data typesIntervalsExercise 12.1: Analytics with time-series dataUnderstanding text processingString characteristics and manipulationIdentifying string patternsExercise 12.2: Text processingApplying geospatial dataLatitude and longitudeExercise 12.3: Geospatial analysisActivity 12Summary
Inferential Statistics Using SQL
Technical requirementsMoving from analytics to statisticsUnderstanding fundamental concepts: population versus samples, parameters versus statisticsPopulation versus samplesParameters versus statisticsEstimating: point estimates and confidence intervalsTesting hypothesesAnalyzing correlation and performing regressionInterpreting regression resultsUnderstanding a simple linear regression exampleActivity 13SummaryGet This Book’s PDF Version and Exclusive Extras
A Case Study for Analytics Using SQL
Technical requirementsUnderstanding the data analytics systemUnderstanding dimensional modelsUnderstanding data warehouse architectureApplying data analysis using SQLExercise 14.1: Copying from a file into the staging tableExercise 14.2: Quality check for raw dataExercise 14.3: Loading data into the star schemaExercise 14.4: Delivery data for analysisSummary
Unlock Your Exclusive Benefits
Unlock this Book’s Free Benefits in 3 Easy StepsNeed help?
Other Books You May Enjoy
Index

Content preview from SQL for Data Analytics - Fourth Edition

5 Presenting Data with SELECT

In previous chapters, you learned about the basic lifecycle of data operations. You are now able to create and drop tables and insert data into tables. You also learned how to import data into a relational database as well as export it. All these operations complete the starting and ending operations of the following CRUD lifecycle:

A diagram of a flowchart

AI-generated content may be incorrect.

Figure 5.1: CRUD operations

The ultimate goal of data management is to utilize the data to generate analytical information. Reading the stored data is the fundamental step in this process. In this chapter, you will start to learn how to read data from existing tables. In the next chapter, ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781836646259

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

SQL for Data Analytics - Fourth Edition

by Jun Shan, Haibin Li, Matt Goldwasser, Upom Malik, Benjamin Johnston

5

Presenting Data with SELECT

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.