SQL for Data Analysis: Advanced SQL Querying Techniques

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

Prerequisite Skills

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

Multi-Table Analysis

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

Subqueries & CTEs

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

Window Functions

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

Functions By Data Type

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

Data Analysis Applications

Final Project Overview

ASSIGNMENT: School Analysis

SOLUTION: School Analysis

ASSIGNMENT: Salary Analysis

SOLUTION: Salary Analysis

ASSIGNMENT: Player Career Analysis

SOLUTION: Player Career Analysis

ASSIGNMENT: Player Comparison Analysis

SOLUTION: Player Comparison Analysis

BONUS LESSON