Zomalex | Data and AI Training

Home | Power BI | Excel | Python | SQL | Gen AI | Public Courses | Prices | Contact Us | LBAG | info@zomalex.co.uk

SQL Advanced Course Outline

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 good understanding of, and familiarity with, the basics of the SQL language - for example by attending our SQL Foundation and Intermediate courses and consolidating with some practice.

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.

A few comments from attendees of previous courses

“Another outstanding delivery by Mark! The SQL material was highly informative and detailed, covering all the essentials of writing SQL effectively. The course delivery was exceptional as always.”

“Thank you, Mark was a really excellent teacher.”

“Really good course and delivered well with lots of interactive learning.”