I. Getting started, installing SQLiteStudio (10 minutes)
II. Subqueries, Derived Tables, and Unions (30 minutes)
- Scalar subqueries
- Derived tables
- Joining derived tables
- Unions
- Using group_concat()
- Exercises
(10 minute break)
III. Regular Expressions (20 minutes)
- Introduction to regular expressions
- Using a regular expression to qualify records
- Exercises
IV. Advanced joins (40 minutes)
- Inner Join/Left Join review
- Creating a volatile table
- Doing a regex join
- Self joins
- Cross Joins
- Comparative Joins
- Exercises
(10 minute break)
V. Windowing functions (40 minutes)
- Windowing with partitions
- Windowing with ordering
- Windowing with partitioning and ordering
- Exercises
VI. Programming with SQL (20 minutes)
- Using SQL with Python
- Using SQL with R
- Using SQL in Java/Scala/Kotlin