Introduction
The need for SQL in data science
()
What you should know
()
1. Foundations of SQL for Data Science
Overview of data science operations
()
Data manipulation commands
()
Data definition commands
()
SQL standards
()
Installing PostgreSQL
()
2. Basic Statistics with SQL
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
()
3. Data Munging with SQL
Reformat character data
()
Extract strings from character data
()
Filter with regular expressions
()
Reformat numeric data
()
Use SOUNDEX with misspelled text
()
Challenge: Prepare a data set for analysis
()
Solution: Prepare a data set for analysis
()
4. Filtering and Aggregation
Use the HAVING clause to find subgroups
()
Subqueries for column values
()
Subqueries in FROM clauses
()
Subqueries in WHERE clauses
()
Use ROLLUP to create subtotals
()
Use CUBE to total across dimensions
()
Use Top-N queries to find top results
()
Challenge: Filter and aggregate a data set
()
Solution: Filter and aggregate a data set
()
5. Window Functions and Ordered Data
Introduction to window functions
()
NTH_VALUE and NTILE
()
RANK, LEAD, and LAG
()
WIDTH_BUCKET and CUME_DIST
()
Challenge: Segment a data set using Window functions
()
Solution: Segment a data set using Window functions
()
6. Common Table Expressions
Introduction to common table expressions (CTEs)
()
Multiple table common table expressions
()
Hierarchical tables
()
Recursive common table expressions
()
Challenge: Rewrite a complex query to use CTEs
()
Solution: Rewrite a complex query to use CTEs
()
Ex_Files_Intermediate_SQL_for_Data_Scientists.zip
(45 KB)