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.
20 lessons 2h
Start course
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.
20 High Quality Lessons
14 Practical Tasks
2 Hours of Video
Certificate of Achievement
Skills you will gain
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 Free SQL Window Functions - Introduction Free The ROW_NUMBER() Ranking Window Function Free Using Several Window Functions in a Query Free SQL Window Functions Syntax Free PARTITION BY vs GROUP BY Free The RANK() and DENSE_RANK() Window Functions Free Using MySQL Ranking Window Functions and Joins Together The LAG() and LEAD() Value Window Functions SQL Aggregate Functions and Window Functions - Part I SQL 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 Introduction SQL Common Table Expressions (CTEs) - Introduction The Same CTE Task - an Alternative Solution A WITH Clause with Multiple Subclauses - Part I A WITH Clause with Multiple Subclauses - Part II Referring 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 - Introduction SQL Temporary Tables - Application SQL Temporary Tables - Extra Features
Student feedback
Filter by rating
- 5 stars
- 4 stars
- 3 stars
- 2 stars
- 1 stars
Sort
- Newest
- Oldest
“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.”
Advanced SQL
with Martin Ganchev and Vladimir Saev