I has been an enjoyable course that brings me the confidence to better manage my Sustainability Projects requiring knowledge of SQL. It is timely!
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.
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:
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
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
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
with Martin Ganchev and Vladimir Saev