Zomalex | Data and AI Training
SQL Advanced Course Outline
Who should attend
People who want to take their SQL skills to an advanced level, especially to use the powerful SQL window functions in their analyses.
Learning Objectives
Attendees will learn to write SQL statements that contain window functions in order to to calculate running totals, ranking, Top N per category, and daily changes in balances. They will also practise joining data from several tables and using tally tables.
Course Content
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.
Tally tables solve problems that would otherwise be difficult to specify in SQL. How to use tally tables and why they are useful.
Practise joining data to return a dataset that combines data from more than one table. (This is a common requirement in real-world SQL problems and attendees often find it the most difficult part of writing SQL statements.)
Course Length
1 day (short version) or 2 days (preferred, full version)
Pre-requisites
A good understanding of, and familiarity with, the basics of the SQL language - for example by attending our foundation and intermediate courses and consolidating with some practice.
Snapshots from the course exercises
A snapshot of a SQL Advanced course exercise