We may earn an affiliate commission when you visit our partners.
Maven Analytics and Alice Zhao

This is a hands-on, project-based course designed to help you move beyond the "Big 6" clauses into advanced querying techniques.

We’ll start by reviewing the basics and conducting multi-table analyses, including basic joins, self-joins, cross-joins, and unions.

Next, we’ll cover different ways of working with nested queries by writing subqueries and common table expressions, or CTEs. We’ll walk through examples of subqueries within the various clauses, rewrite subqueries as CTEs, introduce recursive CTEs, and compare these techniques to other options like temporary tables and views.

Read more

This is a hands-on, project-based course designed to help you move beyond the "Big 6" clauses into advanced querying techniques.

We’ll start by reviewing the basics and conducting multi-table analyses, including basic joins, self-joins, cross-joins, and unions.

Next, we’ll cover different ways of working with nested queries by writing subqueries and common table expressions, or CTEs. We’ll walk through examples of subqueries within the various clauses, rewrite subqueries as CTEs, introduce recursive CTEs, and compare these techniques to other options like temporary tables and views.

From there, we’ll break down each component of a window function and review common window functions like ROW_ We’ll also cover general functions for working with different data types in SQL, including numeric, datetime, string, and NULL functions.

Last but not least, we’ll take the concepts we’ve learned and use them across a series of common data analysis applications. We’ll deal with duplicate values, apply special value filters, perform rolling calculations, and more.

To wrap up the course, you’ll work on a project as a Data Analyst Intern for Major League Baseball, and use advanced SQL querying techniques to track how player stats like salary, height, and weight have changed over time and across different teams.

COURSE OUTLINE:

  • SQL Basics Review

    • Review the big 6 clauses of a SQL query along with other commonly used keywords like Join today and get immediate

      Happy learning.

      -Alice Zhao (Author, SQL Pocket Guide and Data Science Instructor, Maven Analytics)

      Looking for our full business intelligence stack? Search for "Maven Analytics" to browse our full course library, including Excel, Power BI, MySQL, Tableau and Machine Learning courses.

      See why our courses are among the TOP-RATED on Udemy:

      "Some of the BEST courses I've ever taken. I've studied several programming languages, Excel, VBA and web dev, and Maven is among the very best I've seen. " Russ C.

      "This is my fourth course from Maven Analytics and my fourth 5-star review, so I'm running out of things to say. I wish Maven was in my life earlier. " Tatsiana M.

      "Maven Analytics should become the new standard for all courses taught on Udemy. " Jonah M.

Enroll now

What's inside

Learning objectives

  • Conduct multi-table analysis using joins and learn variations like self joins, cross joins, and more
  • Learn to work with nested queries by writing subqueries and common table expressions (ctes), and understand the best use cases for each
  • Use window functions to perform calculations across a set of rows and learn various function options and applications
  • Discover the many sql functions that can be applied to fields of numeric, datetime, string, and null data types
  • Apply advanced querying techniques to common data analysis scenarios, including pivoting data, rolling calculations, and more

Syllabus

Getting Started
Course Introduction
Course Structure & Outline
READ ME: Important Notes for New Students
Read more
DOWNLOAD: Course Resources
PREVIEW: Final 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
SQL Basics Review
Section Introduction
Joining Multiple Tables
The Big 6
Common SQL Keywords
DEMO: SQL Basics Review
Prerequisite Skills
Multi-Table Analysis
Referencing a CTE Multiple Times
Working with Multiple Tables
Basic Joins
Basic Join Types
ASSIGNMENT: Basic Joins
SOLUTION: Basic Joins
Joining on Multiple Columns
Self Joins
ASSIGNMENT: Self Joins
SOLUTION: Self Joins
Cross Joins
UNION vs UNION ALL
Key Takeaways
Subqueries & CTEs
Breaking Down a Window Function
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
ASSIGNMENT: CTEs
SOLUTION: CTEs
Multiple CTEs
ASSIGNMENT: Multiple CTEs
SOLUTION: Multiple CTEs
Recursive CTEs
Subqueries vs CTEs vs Temp Tables vs Views
Window Functions
Window Function Basics
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
Functions By Data Type
Function Basics
Numeric Functions
CAST & CONVERT
ASSIGNMENT: Numeric Functions
SOLUTION: Numeric Functions
DateTime Functions
ASSIGNMENT: DateTime Functions
SOLUTION: DateTime Functions
String Functions

Good to know

Know what's good
, what to watch for
, and possible dealbreakers
Uses a project-based approach, allowing learners to immediately apply advanced SQL querying techniques to real-world data analysis scenarios, which reinforces learning and builds a portfolio
Covers common table expressions (CTEs), which are essential for writing complex, readable, and maintainable SQL queries, and are widely used in professional data analysis and database management
Explores window functions, which are powerful tools for performing calculations across sets of rows, such as rolling calculations and ranking, which are frequently used in data analysis
Includes a review of SQL basics, ensuring that learners with varying levels of experience can benefit from the course and build a solid foundation for advanced topics
Requires installing MySQL and MySQL Workbench, which may pose a barrier for learners who prefer other database management systems or have limited system resources
Focuses on advanced querying techniques, which may not be suitable for learners who are completely new to SQL or have limited experience with basic SQL concepts

Save this course

Save SQL for Data Analysis: Advanced SQL Querying Techniques to your list so you can find it easily later:
Save

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in SQL for Data Analysis: Advanced SQL Querying Techniques with these activities:
Review SQL Basics
Review fundamental SQL concepts to ensure a solid foundation for the advanced topics covered in this course.
Show steps
  • Review basic SQL syntax and commands (SELECT, INSERT, UPDATE, DELETE).
  • Practice writing simple queries against a sample database.
  • Familiarize yourself with different data types in SQL.
Review 'SQL Pocket Guide'
Use this book to quickly reference SQL syntax and commands, especially when working on complex queries.
Show steps
  • Read the chapters related to JOINs, subqueries, and window functions.
  • Take notes on key concepts and syntax.
  • Try the examples provided in the book.
Practice writing JOIN queries
Practice writing different types of JOIN queries to solidify your understanding of multi-table analysis.
Show steps
  • Set up a sample database with multiple related tables.
  • Write queries using INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.
  • Experiment with joining on multiple columns.
Four other activities
Expand to see all activities and additional details
Show all seven activities
Create a SQL cheat sheet
Create a cheat sheet summarizing advanced SQL querying techniques for quick reference.
Show steps
  • Compile a list of commonly used window functions and their syntax.
  • Summarize different types of subqueries and CTEs with examples.
  • Include examples of numeric, datetime, and string functions.
  • Organize the cheat sheet for easy navigation.
Analyze MLB Player Data
Apply advanced SQL querying techniques to analyze MLB player data, similar to the final project in the course.
Show steps
  • Download a dataset of MLB player statistics (e.g., from Kaggle).
  • Use window functions to calculate rolling averages of player stats.
  • Use CTEs to identify players with the most significant salary changes over time.
  • Present your findings in a report or presentation.
Review 'SQL Cookbook'
Use this book to find solutions to specific SQL problems you encounter while working on data analysis projects.
Show steps
  • Browse the table of contents to identify relevant recipes.
  • Study the code examples and explanations.
  • Adapt the recipes to your own data and problems.
Help others in SQL forums
Reinforce your understanding by helping others with their SQL questions in online forums.
Show steps
  • Find online SQL forums or communities.
  • Browse the questions and identify those you can answer.
  • Provide clear and concise explanations with code examples.

Career center

Learners who complete SQL for Data Analysis: Advanced SQL Querying Techniques will develop knowledge and skills that may be useful to these careers:
Data Analyst
A Data Analyst uses SQL extensively to extract, manipulate, and analyze data, transforming it into actionable insights. This course helps a Data Analyst build a strong foundation in SQL, moving beyond basic queries into advanced techniques like multi-table analysis, nested queries, and window functions. These skills are essential for a Data Analyst to perform complex data analysis tasks, such as tracking changes over time and identifying trends. The project-based approach of this course, working with real-world data, is especially valuable for anyone pursuing a career as a Data Analyst.
Business Intelligence Analyst
Business Intelligence Analysts rely on SQL to access and analyze data, which is used to generate reports and dashboards that inform business decisions. This course provides a Business Intelligence Analyst with the advanced SQL querying skills needed to perform complex data analysis. The topics covered, such as subqueries, common table expressions, and window functions, are critical for a Business Intelligence Analyst to derive meaningful insights from large datasets. Learning data analysis scenarios, including pivoting data and performing rolling calculations, makes this course particularly valuable for a Business Intelligence Analyst.
Database Developer
Database Developers design and create databases, writing complex SQL queries for data manipulation and retrieval. This course's focus on advanced SQL querying equips a Database Developer with the expertise to use features such as nested queries, common table expressions, and window functions. Database Developers also need a solid understanding of SQL functions for working with various data types, all of which this course covers. The material presented on data analysis applications within SQL would be highly beneficial to a Database Developer.
Data Engineer
Data Engineers build and maintain the infrastructure that allows for data analysis, often using SQL for data transformation and pipeline development. This course can help a Data Engineer understand advanced SQL querying techniques, such as subqueries and common table expressions, which are needed for efficient data processing. Working with multi-table analysis and window functions, as covered in the course, will enable a Data Engineer to design and maintain robust data pipelines. This course's project-based approach is particularly useful for a Data Engineer who can use these skills in a practical setting.
Data Scientist
Data Scientists use SQL to extract data from databases and prepare it for analysis and modeling. While Data Scientists often use other tools, knowing advanced SQL querying techniques, like those covered in this course, is crucial. This course helps a Data Scientist move beyond basic queries by including topics like window functions, nested queries, and common table expressions. A Data Scientist who has taken this course can perform more complex data wrangling tasks, making the data ready for advanced statistical analysis, and derive insights more efficiently.
Reporting Analyst
Reporting Analysts use SQL to generate regular and ad hoc reports, typically accessing data from various sources. This course may be useful for a Reporting Analyst who needs to improve their SQL skills and perform more sophisticated data analysis. The course's focus on advanced querying techniques, including multi-table joins, subqueries, and window functions, directly relates to preparing detailed reports. A Reporting Analyst can also benefit from working with diverse SQL data types and functions, which the course covers in detail.
Marketing Analyst
Marketing Analysts use data to assess marketing campaigns and to better understand customer behavior. Often, they rely on SQL to extract this information. This course helps a Marketing Analyst perform more sophisticated analyses using advanced SQL techniques. Topics like subqueries, common table expressions, and window functions, all covered in this course, help a Marketing Analyst analyze marketing data more effectively. The focus on rolling calculations and filtering unique values will allow a Marketing Analyst to gain deeper insights from the data.
Financial Analyst
Financial Analysts use SQL to access and analyze financial data, often working with large datasets to identify trends and inform financial decisions. This course may be of use to a Financial Analyst who is looking to expand their skills in SQL querying and perform more complex analysis. The course content on multi-table analysis, nested queries, and window functions will allow a Financial Analyst to derive more value from financial datasets. The skills gained in this class can help a Financial Analyst to perform sophisticated financial modeling and reporting.
Database Administrator
A Database Administrator maintains and manages database systems, and SQL is a key language they use for these tasks. While a Database Administrator may not be writing queries for data analysis, understanding advanced SQL querying is beneficial for performance tuning and data management. This course may be useful for a Database Administrator because it covers topics such as subqueries, common table expressions, and window functions. Familiarity with these SQL features will allow a Database Administrator to better maintain and optimize databases.
Operations Analyst
Operations Analysts use SQL to analyze operational data, looking for areas to improve processes and efficiency. This course may be useful for an Operations Analyst looking to advance their SQL skills. By gaining a deeper understanding of the advanced querying techniques, such as multi-table joins and window functions, an Operations Analyst would be able to perform more complex data analysis. The course's content on data types and functions will help an Operations Analyst to work with operational data more efficiently.
Risk Analyst
A Risk Analyst uses SQL to analyze data to identify and mitigate potential risks. While many Risk Analysts may use other tools, SQL is vital to extracting information from databases. This course may help a Risk Analyst by extending their skills into more advanced techniques, such as subqueries, CTEs, and window functions. The course will also provide a Risk Analyst with techniques for analyzing data over time, which is important for tracking risk trends and patterns. The project based approach of this course also will benefit a Risk Analyst who practices their SQL skills.
Research Analyst
Research Analysts often use SQL to extract and analyze data from databases in order to conduct research and inform decision making. This course may help a Research Analyst by teaching them advanced SQL querying skills. Techniques such as multi-table joins, nested queries, and window functions, will expand the possibilities for a Research Analyst's work. The course also focuses on working with different data types, which is useful for a Research Analyst who works across a variety of different datasets.
Product Analyst
Product Analysts use SQL to analyze product usage data, identify trends, and inform product development and strategy. This course may be valuable for a Product Analyst who wants to expand their SQL skills and perform more sophisticated data analysis. The advanced querying techniques, including subqueries and common table expressions, all found in this course, can help a Product Analyst perform more complex analysis. The skills a Product Analyst develops from this course will help them derive more actionable insights.
Logistics Analyst
Logistics Analysts use SQL to analyze supply chain and transportation data, optimizing operations and improving efficiency. This course may be valuable for Logistics Analysts who want to expand their technical skills in data analysis using SQL. By learning advanced techniques such as joins, subqueries, and window functions, a Logistics Analyst will be better equipped to analyze complex logistics data. The course also covers a range of data types, which is beneficial to a Logistics Analyst as they work with different kinds of logistics data.
Healthcare Analyst
Healthcare Analysts use SQL to extract and analyze healthcare data, informing decisions on patient care, operations, and policy. This course may be useful for a Healthcare Analyst who is seeking to learn more advanced SQL querying techniques. The course content on multi-table analysis, subqueries, and window functions will help a Healthcare Analyst perform more complex data analysis. A Healthcare Analyst can use these skills to gain deeper insights from healthcare data, and track trends in patient outcomes.

Reading list

We've selected two books that we think will supplement your learning. Use these to develop background knowledge, enrich your coursework, and gain a deeper understanding of the topics covered in SQL for Data Analysis: Advanced SQL Querying Techniques.
Provides a concise overview of SQL syntax and commands. It's a useful reference for quickly looking up syntax and understanding basic concepts. It can be used as a quick reference during the course. It is especially helpful for those who need a refresher on SQL fundamentals.
Provides practical solutions to common SQL problems. It's a valuable resource for learning different approaches to solving data analysis challenges. It is best used as additional reading to deepen understanding. It offers a wide range of recipes for various SQL tasks.

Share

Help others find this course page by sharing it with your friends and followers:

Similar courses

Similar courses are unavailable at this time. Please try again later.
Our mission

OpenCourser helps millions of learners each year. People visit us to learn workspace skills, ace their exams, and nurture their curiosity.

Our extensive catalog contains over 50,000 courses and twice as many books. Browse by search, by topic, or even by career interests. We'll match you to the right resources quickly.

Find this site helpful? Tell a friend about us.

Affiliate disclosure

We're supported by our community of learners. When you purchase or subscribe to courses and programs or purchase books, we may earn a commission from our partners.

Your purchases help us maintain our catalog and keep our servers humming without ads.

Thank you for supporting OpenCourser.

© 2016 - 2025 OpenCourser