Self-Paced Course
Advanced SQL Querying
Learn advanced data analysis with SQL, and master topics like subqueries, CTEs, window functions, and more.
Course Description
SQL is a powerful tool for managing and analyzing relational databases, and a skill that every analyst, data scientist, and BI professional should seek to master.
In this course, we’ll move beyond the basic "Big 6" clauses (SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY) and cover advanced querying techniques like JOIN types, subqueries, CTEs and window functions.
From there, we’ll introduce advanced functions used for manipulating various data types, including numeric, datetime, string and NULL functions.
Finally, we'll practice solving real-world business problems and common data analytics use cases by applying advanced querying techniques like pivoting with CASE statements, calculating running totals with window functions, and more.
If you're looking for a hands-on, practical guide to advanced querying with SQL, this is the course for you.
COURSE CURRICULUM:
- Welcome to the Course!
- Benchmark Assessment
- Course Introduction
- Course Structure & Outline
- DOWNLOAD: Course Resources
- Introducing the Course Project
- Setting Expectations
- Installation & Setup
- Where to Write SQL Code
- Installing MySQL (Mac)
- Installing MySQL Workbench (Mac)
- Installing MySQL (PC)
- Installing MySQL Workbench (PC)
- Getting Started with MySQL Workbench
- Loading Data for This Course
- DEMO: Loading Data in MySQL
- DEMO: Loading Data in Other RDBMSs
- Section Introduction
- The Big 6
- Common SQL Keywords
- DEMO: SQL Basics Review
- Section Introduction
- Working with Multiple Tables
- Basic Joins
- Basic Join Types
- ASSIGNMENT: Basic Joins
- SOLUTION: Basic Joins
- Joining on Multiple Columns
- Joining Multiple Tables
- Self Joins
- ASSIGNMENT: Self Joins
- SOLUTION: Self Joins
- Cross Joins
- UNION vs UNION ALL
- Key Takeaways
- Section Introduction
- Subquery Basics
- Subqueries in the SELECT Clause
- ASSIGNMENT: Subqueries in the SELECT Clause
- SOLUTION: Subqueries in the SELECT Clause
- Subqueries in the FROM Clause
- Multiple Subqueries
- ASSIGNMENT: Subqueries in the FROM Clause
- SOLUTION: Subqueries in the FROM Clause
- Subqueries in the WHERE & HAVING Clauses
- ANY vs ALL
- EXISTS and Correlated Subqueries
- ASSIGNMENT: Subqueries in the WHERE Clause
- SOLUTION: Subqueries in the WHERE Clause
- Common Table Expressions
- Subqueries vs CTEs
- Referencing a CTE Multiple Times
- ASSIGNMENT: CTEs
- SOLUTION: CTEs
- Multiple CTEs
- ASSIGNMENT: Multiple CTEs
- SOLUTION: Multiple CTEs
- Recursive CTEs
- Subqueries vs CTEs vs Temp Tables vs Views
- Key Takeaways
- Section Introduction
- Window Function Basics
- Breaking Down a Window Function
- ASSIGNMENT: Window Functions
- SOLUTION: Window Functions
- Functions for Window Functions
- ROW_NUMBER, RANK & DENSE_RANK
- ASSIGNMENT: Row Numbering
- SOLUTION: Row Numbering
- FIRST_VALUE, LAST_VALUE & NTH_VALUE
- ASSIGNMENT: Value Within a Window
- SOLUTION: Value Within a Window
- LEAD & LAG
- ASSIGNMENT: Value Relative to a Row
- SOLUTION: Value Relative to a Row
- NTILE
- ASSIGNMENT: Statistical Functions
- SOLUTION: Statistical Functions
- PREVIEW: Moving Average Calculations
- Key Takeaways
- Section Introduction
- Function Basics
- Numeric Functions
- CAST & CONVERT
- ASSIGNMENT: Numeric Functions
- SOLUTION: Numeric Functions
- DateTime Functions
- ASSIGNMENT: DateTime Functions
- SOLUTION: DateTime Functions
- String Functions
- ASSIGNMENT: String Functions
- SOLUTION: String Functions
- Pattern Matching
- DEMO: Pattern Matching
- ASSIGNMENT: Pattern Matching
- SOLUTION: Pattern Matching
- NULL Functions
- ASSIGNMENT: NULL Functions
- SOLUTION: NULL Functions
- Key Takeaways
- Section Introduction
- Duplicate Values
- ASSIGNMENT: Duplicate Values
- SOLUTION: Duplicate Values
- Min / Max Value Filtering
- ASSIGNMENT: Min / Max Value Filtering
- SOLUTION: Min / Max Value Filtering
- Pivoting
- ASSIGNMENT: Pivoting
- SOLUTION: Pivoting
- Rolling Calculations
- DEMO: Rolling Calculations
- ASSIGNMENT: Rolling Calculations
- SOLUTION: Rolling Calculations
- DEMO: Imputing NULL Values
- Key Takeaways
- Final Project Overview
- SOLUTION: School Analysis
- SOLUTION: Salary Analysis
- SOLUTION: Player Career Analysis
- SOLUTION: Player Comparison Analysis
- Final Assessment
- Course Feedback Survey
- Share the Love!
- Next Steps
WHO SHOULD TAKE THIS COURSE?
- Analysts or BI professionals looking to analyze data stored in relational database systems
- SQL users who want to develop advanced querying skills
- Anyone looking for a hands-on, practical, and highly engaging way to master SQL for advanced data analysis
WHAT ARE THE COURSE REQUIREMENTS?
- MySQL Workbench and Community Server (we’ll walk you through the install process) or any other RDBMS
- If you are new to SQL, we strongly recommend taking the basic MySQL Data Analysis course first
Start learning for FREE, no credit card required!
Every subscription includes access to the following course materials
- Interactive Project files
- Downloadable e-books
- Graded quizzes and assessments
- 1-on-1 Expert support
- 100% satisfaction guarantee
- Verified credentials & accredited badges
Ready to become a
data rockstar?
Start learning for free, no credit card required!