English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 125 lectures (8h 21m) | 3.55 GB
Learn advanced data analysis with SQL, and master topics like subqueries, CTEs, window functions, and 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_NUMBER, RANK, FIRST_VALUE, LEAD, and LAG. 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 LIMIT, DISTINCT, and more
Multi-Table Analysis
Review JOIN basics (INNER, LEFT, RIGHT, OUTER) and introduce variations like self joins, CROSS JOINs, and more
Subqueries & CTEs
Learn how to write subqueries and Common Table Expressions and understand the best situations for using certain techniques
Window Functions
Introduce window functions to perform calculations across a set of rows and discuss various function options and applications
Functions by Data Type
Discover the many SQL functions that can be applied to fields of numeric, datetime, string, and NULL data types
Data Analysis Applications
Apply advanced querying techniques to common data analysis scenarios, including pivoting data, rolling calculations, and more
Final Project
Leverage everything you’ve learned to track how Major League Baseball (MLB) player statistics have changed over time and across different teams in the league
What you’ll learn
- 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
Table of Contents
Getting Started
1 Course Introduction
2 Course Structure Outline
3 READ ME Important Notes for New Students
4 DOWNLOAD Course Resources
5 PREVIEW Final Project
6 Setting Expectations
Installation Setup
7 Installation Setup
8 Where to Write SQL Code
9 Installing MySQL Mac
10 Installing MySQL Workbench Mac
11 Installing MySQL PC
12 Installing MySQL Workbench PC
13 Getting Started with MySQL Workbench
14 Loading Data for This Course
15 DEMO Loading Data in MySQL
16 DEMO Loading Data in Other RDBMSs
SQL Basics Review
17 Section Introduction
18 The Big 6
19 Common SQL Keywords
20 DEMO SQL Basics Review
MultiTable Analysis
21 Section Introduction
22 Working with Multiple Tables
23 Basic Joins
24 Basic Join Types
25 ASSIGNMENT Basic Joins
26 SOLUTION Basic Joins
27 Joining on Multiple Columns
28 Joining Multiple Tables
29 ASSIGNMENT Self Joins
30 SOLUTION Self Joins
31 Cross Joins
32 UNION vs UNION ALL
33 Key Takeaways
Subqueries CTEs
34 Section Introduction
35 Subquery Basics
36 Subqueries in the SELECT Clause
37 ASSIGNMENT Subqueries in the SELECT Clause
38 SOLUTION Subqueries in the SELECT Clause
39 Subqueries in the FROM Clause
40 Multiple Subqueries
41 ASSIGNMENT Subqueries in the FROM Clause
42 SOLUTION Subqueries in the FROM Clause
43 Subqueries in the WHERE HAVING Clauses
44 ANY vs ALL
45 EXISTS and Correlated Subqueries
46 ASSIGNMENT Subqueries in the WHERE Clause
47 SOLUTION Subqueries in the WHERE Clause
48 Common Table Expressions
49 Subqueries vs CTEs
50 Referencing a CTE Multiple Times
51 ASSIGNMENT CTEs
52 SOLUTION CTEs
53 Multiple CTEs
54 ASSIGNMENT Multiple CTEs
55 SOLUTION Multiple CTEs
56 Recursive CTEs
57 Subqueries vs CTEs vs Temp Tables vs Views
58 Key Takeaways
Window Functions
59 Section Introduction
60 Window Function Basics
61 Breaking Down a Window Function
62 ASSIGNMENT Window Functions
63 SOLUTION Window Functions
64 Functions for Window Functions
65 ROWNUMBER RANK DENSERANK
66 ASSIGNMENT Row Numbering
67 SOLUTION Row Numbering
68 FIRSTVALUE LASTVALUE NTHVALUE
69 ASSIGNMENT Value Within a Window
70 SOLUTION Value Within a Window
71 LEAD LAG
72 ASSIGNMENT Value Relative to a Row
73 SOLUTION Value Relative to a Row
74 NTILE
75 ASSIGNMENT Statistical Functions
76 SOLUTION Statistical Functions
77 PREVIEW Moving Average Calculations
78 Key Takeaways
Functions By Data Type
79 Section Introduction
80 Function Basics
81 Numeric Functions
82 CAST CONVERT
83 ASSIGNMENT Numeric Functions
84 SOLUTION Numeric Functions
85 DateTime Functions
86 ASSIGNMENT DateTime Functions
87 SOLUTION DateTime Functions
88 String Functions
89 ASSIGNMENT String Functions
90 SOLUTION String Functions
91 Pattern Matching
92 DEMO Pattern Matching
93 ASSIGNMENT Pattern Matching
94 SOLUTION Pattern Matching
95 NULL Functions
96 ASSIGNMENT NULL Functions
97 SOLUTION NULL Functions
98 Key Takeaways
Data Analysis Applications
99 Section Introduction
100 Duplicate Values
101 ASSIGNMENT Duplicate Values
102 SOLUTION Duplicate Values
103 Min Max Value Filtering
104 ASSIGNMENT Min Max Value Filtering
105 SOLUTION Min Max Value Filtering
106 Pivoting
107 ASSIGNMENT Pivoting
108 SOLUTION Pivoting
109 Rolling Calculations
110 DEMO Rolling Calculations
111 ASSIGNMENT Rolling Calculations
112 SOLUTION Rolling Calculations
113 DEMO Imputing NULL Values
114 Key Takeaways
Final Project
115 Final Project Overview
116 ASSIGNMENT School Analysis
117 SOLUTION School Analysis
118 ASSIGNMENT Salary Analysis
119 SOLUTION Salary Analysis
120 ASSIGNMENT Player Career Analysis
121 SOLUTION Player Career Analysis
122 ASSIGNMENT Player Comparison Analysis
123 SOLUTION Player Comparison Analysis
Next Steps
124 BONUS LESSON
Resolve the captcha to access the links!