Intermediate SQL for Data Scientists

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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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