SQL for Data Analysis: Advanced SQL Querying Techniques from Udemy

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.

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

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

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

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

Show steps

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'

Show steps

Use this book to quickly reference SQL syntax and commands, especially when working on complex queries.

View SQL Pocket Guide: A Guide to SQL Usage on Amazon

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

Show steps

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

Show steps

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

Show steps

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'

Show steps

Use this book to find solutions to specific SQL problems you encounter while working on data analysis projects.

View SQL Cookbook: Query Solutions and Techniques... on Amazon

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

Show steps

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.

See salaries and explore the career path for 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.

See salaries and explore the career path for 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.

See salaries and explore the career path for 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.

See salaries and explore the career path for Data Engineer

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.

See salaries and explore the career path for Data Scientist

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.

See salaries and explore the career path for Reporting Analyst

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.

See salaries and explore the career path for Marketing Analyst

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.

See salaries and explore the career path for Financial Analyst

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.

See salaries and explore the career path for Database Administrator

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.

See salaries and explore the career path for Operations Analyst

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.

See salaries and explore the career path for Risk Analyst

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.

See salaries and explore the career path for Research Analyst

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.

See salaries and explore the career path for Product Analyst

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.

See salaries and explore the career path for Logistics Analyst

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.

See salaries and explore the career path for Healthcare Analyst

SQL for Data Analysis

Advanced SQL Querying Techniques

What's inside

Learning objectives

Syllabus

Good to know

Save this course

Activities

Career center

Reading list

Share

Similar courses