SQL For Data Analysis: Advanced SQL Querying Techniques

[BaDshaH]

Published 11/2024
MP4 | Video: h264, 1920x1080 | Audio: AAC, 44.1 KHz
Language: English | Size: 3.55 GB | Duration: 8h 21m

Learn advanced data analysis with SQL, and master topics like subqueries, CTEs, window functions, and more

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

Requirements
Any SQL Editor (we'll walk you through the install process specifically for MySQL Workbench)
If you are new to SQL, we strongly recommend taking Maven Analytics' Beginner MySQL Business Intelligence course first

Description
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 ReviewReview the big 6 clauses of a SQL query along with other commonly used keywords like LIMIT, DISTINCT, and moreMulti-Table AnalysisReview JOIN basics (INNER, LEFT, RIGHT, OUTER) and introduce variations like self joins, CROSS JOINs, and moreSubqueries & CTEsLearn how to write subqueries and Common Table Expressions and understand the best situations for using certain techniquesWindow FunctionsIntroduce window functions to perform calculations across a set of rows and discuss various function options and applicationsFunctions by Data TypeDiscover the many SQL functions that can be applied to fields of numeric, datetime, string, and NULL data typesData Analysis ApplicationsApply advanced querying techniques to common data analysis scenarios, including pivoting data, rolling calculations, and moreFinal ProjectLeverage everything you've learned to track how Major League Baseball (MLB) player statistics have changed over time and across different teams in the league__________Ready to dive in? Join today and get immediate, LIFETIME access to the following:8 hours of high-quality video21 homework assignments6 quizzes4-part final projectAdvanced SQL Querying ebook (150+ pages)Downloadable project files & solutionsExpert support and Q&A forum30-day Udemy satisfaction guaranteeIf you're an analyst, data scientist, or BI professional looking to master advanced querying with SQL, this is the course for you.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.

Overview
Section 1: Getting Started
Lecture 1 Course Introduction
Lecture 2 Course Structure & Outline
Lecture 3 READ ME: Important Notes for New Students
Lecture 4 DOWNLOAD: Course Resources
Lecture 5 PREVIEW: Final Project
Lecture 6 Setting Expectations
Section 2: Installation & Setup
Lecture 7 Installation & Setup
Lecture 8 Where to Write SQL Code
Lecture 9 Installing MySQL (Mac)
Lecture 10 Installing MySQL Workbench (Mac)
Lecture 11 Installing MySQL (PC)
Lecture 12 Installing MySQL Workbench (PC)
Lecture 13 Getting Started with MySQL Workbench
Lecture 14 Loading Data for This Course
Lecture 15 DEMO: Loading Data in MySQL
Lecture 16 DEMO: Loading Data in Other RDBMSs
Section 3: SQL Basics Review
Lecture 17 Section Introduction
Lecture 18 The Big 6
Lecture 19 Common SQL Keywords
Lecture 20 DEMO: SQL Basics Review
Section 4: Multi-Table Analysis
Lecture 21 Section Introduction
Lecture 22 Working with Multiple Tables
Lecture 23 Basic Joins
Lecture 24 Basic Join Types
Lecture 25 ASSIGNMENT: Basic Joins
Lecture 26 SOLUTION: Basic Joins
Lecture 27 Joining on Multiple Columns
Lecture 28 Joining Multiple Tables
Lecture 29 Self Joins
Lecture 30 ASSIGNMENT: Self Joins
Lecture 31 SOLUTION: Self Joins
Lecture 32 Cross Joins
Lecture 33 UNION vs UNION ALL
Lecture 34 Key Takeaways
Section 5: Subqueries & CTEs
Lecture 35 Section Introduction
Lecture 36 Subquery Basics
Lecture 37 Subqueries in the SELECT Clause
Lecture 38 ASSIGNMENT: Subqueries in the SELECT Clause
Lecture 39 SOLUTION: Subqueries in the SELECT Clause
Lecture 40 Subqueries in the FROM Clause
Lecture 41 Multiple Subqueries
Lecture 42 ASSIGNMENT: Subqueries in the FROM Clause
Lecture 43 SOLUTION: Subqueries in the FROM Clause
Lecture 44 Subqueries in the WHERE & HAVING Clauses
Lecture 45 ANY vs ALL
Lecture 46 EXISTS and Correlated Subqueries
Lecture 47 ASSIGNMENT: Subqueries in the WHERE Clause
Lecture 48 SOLUTION: Subqueries in the WHERE Clause
Lecture 49 Common Table Expressions
Lecture 50 Subqueries vs CTEs
Lecture 51 Referencing a CTE Multiple Times
Lecture 52 ASSIGNMENT: CTEs
Lecture 53 SOLUTION: CTEs
Lecture 54 Multiple CTEs
Lecture 55 ASSIGNMENT: Multiple CTEs
Lecture 56 SOLUTION: Multiple CTEs
Lecture 57 Recursive CTEs
Lecture 58 Subqueries vs CTEs vs Temp Tables vs Views
Lecture 59 Key Takeaways
Section 6: Window Functions
Lecture 60 Section Introduction
Lecture 61 Window Function Basics
Lecture 62 Breaking Down a Window Function
Lecture 63 ASSIGNMENT: Window Functions
Lecture 64 SOLUTION: Window Functions
Lecture 65 Functions for Window Functions
Lecture 66 ROW_NUMBER, RANK & DENSE_RANK
Lecture 67 ASSIGNMENT: Row Numbering
Lecture 68 SOLUTION: Row Numbering
Lecture 69 FIRST_VALUE, LAST_VALUE & NTH_VALUE
Lecture 70 ASSIGNMENT: Value Within a Window
Lecture 71 SOLUTION: Value Within a Window
Lecture 72 LEAD & LAG
Lecture 73 ASSIGNMENT: Value Relative to a Row
Lecture 74 SOLUTION: Value Relative to a Row
Lecture 75 NTILE
Lecture 76 ASSIGNMENT: Statistical Functions
Lecture 77 SOLUTION: Statistical Functions
Lecture 78 PREVIEW: Moving Average Calculations
Lecture 79 Key Takeaways
Section 7: Functions By Data Type
Lecture 80 Section Introduction
Lecture 81 Function Basics
Lecture 82 Numeric Functions
Lecture 83 CAST & CONVERT
Lecture 84 ASSIGNMENT: Numeric Functions
Lecture 85 SOLUTION: Numeric Functions
Lecture 86 DateTime Functions
Lecture 87 ASSIGNMENT: DateTime Functions
Lecture 88 SOLUTION: DateTime Functions
Lecture 89 String Functions
Lecture 90 ASSIGNMENT: String Functions
Lecture 91 SOLUTION: String Functions
Lecture 92 Pattern Matching
Lecture 93 DEMO: Pattern Matching
Lecture 94 ASSIGNMENT: Pattern Matching
Lecture 95 SOLUTION: Pattern Matching
Lecture 96 NULL Functions
Lecture 97 ASSIGNMENT: NULL Functions
Lecture 98 SOLUTION: NULL Functions
Lecture 99 Key Takeaways
Section 8: Data Analysis Applications
Lecture 100 Section Introduction
Lecture 101 Duplicate Values
Lecture 102 ASSIGNMENT: Duplicate Values
Lecture 103 SOLUTION: Duplicate Values
Lecture 104 Min / Max Value Filtering
Lecture 105 ASSIGNMENT: Min / Max Value Filtering
Lecture 106 SOLUTION: Min / Max Value Filtering
Lecture 107 Pivoting
Lecture 108 ASSIGNMENT: Pivoting
Lecture 109 SOLUTION: Pivoting
Lecture 110 Rolling Calculations
Lecture 111 DEMO: Rolling Calculations
Lecture 112 ASSIGNMENT: Rolling Calculations
Lecture 113 SOLUTION: Rolling Calculations
Lecture 114 DEMO: Imputing NULL Values
Lecture 115 Key Takeaways
Section 9: Final Project
Lecture 116 Final Project Overview
Lecture 117 ASSIGNMENT: School Analysis
Lecture 118 SOLUTION: School Analysis
Lecture 119 ASSIGNMENT: Salary Analysis
Lecture 120 SOLUTION: Salary Analysis
Lecture 121 ASSIGNMENT: Player Career Analysis
Lecture 122 SOLUTION: Player Career Analysis
Lecture 123 ASSIGNMENT: Player Comparison Analysis
Lecture 124 SOLUTION: Player Comparison Analysis
Section 10: Next Steps
Lecture 125 BONUS LESSON
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

Homepage

[To see links please register or login]

[To see links please register or login]