25.01.2023
I has been an enjoyable course that brings me the confidence to better manage my Sustainability Projects requiring knowledge of SQL. It is timely!
Advanced SQL
with
Martin Ganchev
and
Vladimir Saev
Learn and apply MySQL window functions, common table expressions, and temporary tables to gain a competitive advantage over analysts using SQL.
2 hours 20 lessons
Start course
20 High Quality Lessons
14 Practical Tasks
2 Hours of Video
Certificate of Achievement
Course Overview
Unlike some other programming languages, SQL’s syntax is quite intuitive to understand and use, facilitating many to take their first steps toward working with relational database management systems (RDBMS). But knowing how to apply SQL’s basic instruments—such as aggregate functions, joins, and stored procedures—won’t allow you to reach its full potential. In this course, we’ll extensively cover window functions, common table expressions, and temporary tables to enrich your skill set and toolbox for handling relational databases and reveal plenty of options for data retrieval.
Topics covered
data analysisProgrammingRelational DatabasesSQL
What You'll Learn
MySQL window functions, common table expressions, and temporary tables are highly sought-after tools in the arsenal of every database administrator, data scientist, or data analyst. By completing this course—packed with real-world scenarios and practical problems—you’ll understand and master the mechanics behind each one of these tools, and more precisely:
Get acquainted with and explore such window functions as ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), and LEAD()
Learn how to use MySQL aggregate functions in the context of window functions
Improve your data retrieval precision by using common table expressions
Simplify and save time in your work with temporary tables
Learn how to combine newly-learned tools with joins and aggregate functions
Curriculum
- SQL Window Functions11 Lessons 62 Min
When we say “window,” we mean a particular part of your database on which we want to apply some function. The functions to use can be ranking window functions (ROW_NUMBER(), RANK(), or DENSE_RANK()), value window functions (LAG() and LEAD()), or MySQL aggregate window functions applied in the context of window functions, such as MAX() or MIN().
SQL Window Functions - Section Introduction FreeSQL Window Functions - Introduction FreeThe ROW_NUMBER() Ranking Window Function FreeUsing Several Window Functions in a Query FreeSQL Window Functions Syntax FreePARTITION BY vs GROUP BY FreeThe RANK() and DENSE_RANK() Window Functions FreeUsing MySQL Ranking Window Functions and Joins TogetherThe LAG() and LEAD() Value Window FunctionsSQL Aggregate Functions and Window Functions - Part ISQL Aggregate Functions and Window Functions - Part II - SQL Common Table Expressions (CTEs)6 Lessons 24 Min
This section covers MySQL Common Table Expressions (CTEs)—a tool allowing you to obtain a temporary result set that you can use and reuse within the scope of a query. More precisely, we’ll explore single and multiple subclauses in a WITH clause and refer to already specified common table expressions in a WITH clause.
SQL Common Table Expressions (CTEs) - Section IntroductionSQL Common Table Expressions (CTEs) - IntroductionThe Same CTE Task - an Alternative SolutionA WITH Clause with Multiple Subclauses - Part IA WITH Clause with Multiple Subclauses - Part IIReferring to CTEs in a WITH Clause - SQL Temporary Tables3 Lessons 15 Min
As you progress as an SQL analyst, you’ll increasingly work with more extensive databases containing tables with millions of records. In other words, the queries you write can easily take a few minutes before returning the desired result. In such cases, you’ll look for tools like MySQL temporary tables to increase the efficiency of the data retrieval process. In this section, you’ll create your first MySQL Temporary Table and explore some of its features.
SQL Temporary Tables - IntroductionSQL Temporary Tables - ApplicationSQL Temporary Tables - Extra Features
Student feedback
Filter by rating
- 5 stars
- 4 stars
- 3 stars
- 2 stars
- 1 stars
Sort
- Newest
- Oldest
- 1
- 2
- 3
“Unlike programming languages like Python or R, SQL doesn’t have a vast library of ready-made tools that allow you to retrieve the desired data by simply executing a short line of code. Mastering SQL requires a comprehensive understanding of the functionality of each of its tools and how to combine them to retrieve specific information from your database. And that’s precisely the challenge we present through the highly practical configuration of this course.”
Worked at PaySafe Group
Advanced SQL
with Martin Ganchev and Vladimir Saev