Top Five Things to Know in Advanced SQL

Learn advanced SQL concepts and practice them with hands-on exercises.

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

1. Subqueries

• 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

2. Common Table Expressions (CTE)

• Syntax of CTEs
• Recursive CTEs
• Challenge: Create a CTE
• Solution: Create a CTE
• Difference between a CTE and a subquery

3. Windows Functions: ROW_NUMBER()

• Introduction to window functions
• Diving into ROW_NUMBER()
• Challenge: Calculate a ROW_NUMBER
• Solution: Calculate a ROW_NUMBER

4. Windows Functions: LAG() and LEAD()

• 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

5. Ranking Windows Functions

• Introduction to ranking window functions
• Demonstrating the distinction between RANK() and DENSE_RANK()
• Challenge: RANK() and DENSE_RANK()
• Solution: RANK() and DENSE_RANK()

Conclusion

• Wrap-up