Top Five Things to Know in Advanced SQL
1h 36mAdvanced2023-08-24
Authors
Kendall Ruber
Course details
In this course, Kendall Ruber shows you how to build on your existing knowledge of basic SQL commands by introducing advanced SQL concepts and providing hands-on exercises for you to apply these skills to common business problems. By the end of this course, students should understand the different use cases and limitations for various advanced functions in SQL, as well as be able to appropriately apply these functions to solve business problems. Learn and practice how to construct subqueries, CTEs (Common Table Expressions), and windows functions.
Skills covered
SQLDatabase AdministrationAdvancedDatabase DevelopmentDatabase ManagementData AnalysisProgramming LanguagesData ScienceBusiness Analysis and StrategyBusiness Software and ToolsOpen SourceSoftware Development
Concepts
0. Introduction
- 01 - The importance of SQL in a data-driven world
- 02 - Prerequisite knowledge
- 03 - Navigating the exercise files on GitHub
- 04 - Setting up Microsoft SQL Server
- 05 - Adding primary key constraints
1. Subqueries
- 06 - What is a subquery
- 07 - Syntax of a scalar subquery
- 08 - Syntax of a multiple-row subquery
- 09 - Uncorrelated subquery
- 10 - Correlated subquery
- 11 - Challenge - Create a subquery
- 12 - Solution - Create a subquery
2. Common Table Expressions (CTE)
- 13 - Syntax of CTEs
- 14 - Recursive CTEs
- 15 - Challenge - Create a CTE
- 16 - Solution - Create a CTE
- 17 - Difference between a CTE and a subquery
3. Windows Functions - ROW NUMBER()
- 18 - Introduction to window functions
- 19 - Diving into ROW NUMBER()
- 20 - Challenge - Calculate a ROW NUMBER
- 21 - Solution - Calculate a ROW NUMBER
4. Windows Functions - LAG() and LEAD()
- 22 - Use cases and syntax of LAG() and LEAD()
- 23 - Using LAG() and LEAD() in tandem
- 24 - Challenge - Using LAG() and LEAD() to determine most recent order quantities
- 25 - Solution - Using LAG() and LEAD() to determine most recent order quantities
5. Ranking Windows Functions
- 26 - Introduction to ranking window functions
- 27 - Demonstrating the distinction between RANK() and DENSE RANK()
- 28 - Challenge - RANK() and DENSE RANK()
- 29 - Solution - RANK() and DENSE RANK()
Conclusion
- 30 - Wrap-up