Data Training from Zomalex

Home | Power BI | Excel | Python | SQL | Visualising Data | Generative AI

SQL Advanced Course

Who should attend

People who have completed the foundation and intermediate courses and want to do more powerful analysis with SQL.

Course Length

1 day (short version) or 2 days (preferred, full version)

Learning Objectives

Attendees will learn to write SQL statements to retrieve data across several related tables, calculate new columns, aggregate data and calculate over groups of data e.g. rolling averages or cumulative totals.

Pre-requisites

A basic understanding of the SQL language - for example by attending our SQL Foundation course.

Course Content

Tally tables. When to use and why they are useful.

SQL Window functions are powerful, flexible and applicable to many problems that would otherwise be hard to specify in SQL for example, rolling totals, ranking, Top N per category, daily change The details of Window functions:

• the OVER clause to define the window
• the PARTITION BY clause – apply the windows (and group/aggregate) over the distinct values in one or more columns
• the ORDER BY clause – example of use: e.g. ranking by category
• the special Window functions: ROW_NUMBER, LEAD and LAG, RANK and DENSE_RANK

Problems that can be solved with Window functions: running totals, ranking, Top N per category, and daily changes in balances.