Intermediate SQL for Data Scientists (2021)

2h 39mIntermediate2021-07-20

Authors

Dan Sullivan

Enterprise Architect, Big Data Expert

Course details

There is an increasing need for data scientists and analysts to understand relational data stores. Organizations have long used SQL databases to store transactional data as well as business intelligence related data. This course was designed for data scientists who need to work with SQL databases. Specifically, it was designed to help these professionals learn how to perform common data science tasks, including exploration and extraction of data within relational databases.

Instructor Dan Sullivan kicks off the course with a brief overview of SQL data manipulation and data definition commands. He then focuses on how to use SQL queries to prepare data for analysis; leverage statistical functions to better understand that data; and work with aggregates, window operations, and more.

Skills covered

PostgreSQLDesktop DatabasesSQLDatabase AdministrationDatabase DevelopmentDatabase ManagementPersonaData AnalysisProgramming LanguagesData ScienceBusiness Analysis and StrategyBusiness Software and ToolsOpen SourceSoftware Development

Concepts

0. Introduction

01 - The need for SQL in data science
02 - What you should know

1. Foundations of SQL for Data Science

03 - Overview of data science operations
04 - Data manipulation commands
05 - Data definition commands
06 - SQL standards
07 - Installing PostgreSQL

2. Basic Statistics with SQL

08 - Loading data
09 - Basic aggregate functions
10 - Statistical aggregate functions
11 - Grouping and filtering data
12 - Joining and filtering data
13 - Challenge - Write a Query with a Filter
14 - Solution - Write a Query with a Filter

3. Data Munging with SQL

15 - Reformat character data
16 - Extract strings from character data
17 - Filter with regular expressions
18 - Reformat numeric data
19 - Use SOUNDEX with misspelled text
20 - Challenge - Prepare a data set for analysis
21 - Solution - Prepare a data set for analysis

4. Filtering and Aggregation

22 - Use the HAVING clause to find subgroups
23 - Subqueries for column values
24 - Subqueries in FROM clauses
25 - Subqueries in WHERE clauses
26 - Use ROLLUP to create subtotals
27 - Use CUBE to total across dimensions
28 - Use Top-N queries to find top results
29 - Challenge - Filter and aggregate a data set
30 - Solution - Filter and aggregate a data set

5. Window Functions and Ordered Data

31 - Introduction to window functions
32 - NTH VALUE and NTILE
33 - RANK, LEAD, and LAG
34 - WIDTH BUCKET and CUME DIST
35 - Challenge - Segment a data set using Window functions
36 - Solution - Segment a data set using Window functions

6. Common Table Expressions

37 - Introduction to common table expressions (CTEs)
38 - Multiple table common table expressions
39 - Hierarchical tables
40 - Recursive common table expressions
41 - Challenge - Rewrite a complex query to use CTEs
42 - Solution - Rewrite a complex query to use CTEs

Conclusion

43 - Next steps