Learn advanced SQL concepts and practice them with hands-on exercises.
Overview
Syllabus
Introduction
- The importance of SQL in a data-driven world
- Prerequisite knowledge
- Navigating the exercise files on GitHub
- Setting up Microsoft SQL Server
- Adding primary key constraints
- What is a subquery?
- Syntax of a scalar subquery
- Syntax of a multiple-row subquery
- Uncorrelated subquery
- Correlated subquery
- Challenge: Create a subquery
- Solution: Create a subquery
- Syntax of CTEs
- Recursive CTEs
- Challenge: Create a CTE
- Solution: Create a CTE
- Difference between a CTE and a subquery
- Introduction to window functions
- Diving into ROW_NUMBER()
- Challenge: Calculate a ROW_NUMBER
- Solution: Calculate a ROW_NUMBER
- Use cases and syntax of LAG() and LEAD()
- Using LAG() and LEAD() in tandem
- Challenge: Using LAG() and LEAD() to determine most recent order quantities
- Solution: Using LAG() and LEAD() to determine most recent order quantities
- Introduction to ranking window functions
- Demonstrating the distinction between RANK() and DENSE_RANK()
- Challenge: RANK() and DENSE_RANK()
- Solution: RANK() and DENSE_RANK()
- Wrap-up
Taught by
Kendall Ruber