Intermediate SQL for Data Scientists

4h 5mIntermediate2025-03-14

Authors

Dan Sullivan

Enterprise Architect, Big Data Expert

Course details

Organizations are increasingly adopting digital transformation strategies that lead to greater data generation and, as a result, a greater need for data analysis. This course is designed for analysts and data scientists who work with SQL databases. Instructor Dan Sullivan outlines how to perform common data science tasks, including finding, exploring, and extracting data within relational databases. Explore the basics of using aggregates, statistical functions, window operations, regular expressions, and subqueries. Along the way, learn how to use JSON, a semistructured data type, and perform join operations to enable more complex queries.

Learning objectives
Perform statistical functions and string, numeric, and regular expression functions in SQL.
Apply advanced filtering and aggregation techniques.
Perform various types of join operations.
Work with ordered datasets by windowing functions.
Use JSON data structures and common table expressions.

Skills covered

SQLDatabase AdministrationDatabase DevelopmentDatabase ManagementData AnalysisProgramming LanguagesData ScienceBusiness Analysis and StrategyBusiness Software and ToolsOpen SourceSoftware DevelopmentOne-Off

Concepts

Introduction

The need for SQL in data science
What you should know

Foundations of SQL for Data Science

Overview of data science operations
Data manipulation commands
Data definition commands
SQL standards

Basic Statistics with SQL

Getting started with GitHub Codespaces
Creating tables and loading data
Basic aggregate functions
Statistical aggregate functions
Grouping and filtering data
Joining and filtering data
Challenge - Test an attribute for normal distribution
Solution - Test an attribute for normal distribution

Data Munging with SQL

Reformatting character data
Extracting strings from character data
Filtering with regular expressions
Reformatting numeric data
Fuzzy string matching
Challenge - Prepare a data set for analysis
Solution - Prepare a data set for analysis

Filtering and Aggregation

Using the HAVING clause to find subgroups
Subqueries for column values
Subqueries in FROM clauses
Subqueries in WHERE clauses
Using ROLLUP to create subtotals
Using CUBE to total across dimensions
Using Top-N queries to find top results
Challenge - Filter and aggregate a data set
Solution - Filter and aggregate a data set

Window Functions and Ordered Data

Introducing window functions
NTH VALUE and NTH TILE
RANK, LEAD, and LAG
Width buckets and CUME DIST
Challenge - Segment a data set using window functions
Solution - Segment a data set using window functions

Common Table Expressions

Introducing common table expressions (CTEs)
Multiple table common table expressions
Hierarchical tables
Recursive common table expressions
Challenge - Rewriting a complex query to use CTEs
Solution - Rewriting a complex query to use CTEs

Types of Joins

Overview of types of joins
Inner joins
Right outer joins
Left outer joins
Full outer joins
Challenge - Choose the correct type of join
Solution - Choose the correct type of join

Working with JSON

JSON in relational databases
JSON data types
Inserting JSON data
Querying JSON data
Indexing JSON data
Challenge - Query a JSON column
Solution - Query a JSON column

Conclusion

Next steps