SQL – Advanced

Who is this course for? This people who understand the basics of retrieving data with the SQL SELECT statement and would like to do more powerful analysis with SQL.

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.

Course Length and Price: 2 days, £2,900 ex VAT

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

Course Content:

Get data from several tables using JOINs. Create a SQL statement that retrieves data more than one table. Use the JOIN clause to specify the common columns.

  • The JOIN keyword
  • How to use table aliases to improve clarity
  • Types of JOIN (INNER, LEFT, others)

Subqueries and CTEs. Subqueries and common table expressions (CTEs) increase the analytical power of our SQL statements. Typical uses include aggregation of aggregations and comparing row values against an aggregate.

  • Simple subqueries
  • Correlated subqueries
  • CTEs and the WITH clause and why this is often a better alternative to a subquery

CASE statements. The CASE statement gives great flexibility to create new calculated columns.

  • The basic CASE WHEN THEN ELSE END structure
  • Typical uses: counting rows that meet a certain criterion
  • Using CASE with aggregate functions e.g. calculating percentages

Window functions are powerful, flexible and applicable to many problems that would otherwise be hard to specify in SQL.

  • Creating a Window function – The OVER() clause
  • The PARTITION 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 ROWS PRECEDING / FOLLOWING clause – sliding windows and moving averages
  • Typical examples of using Window functions

Below are some images from the lab exercises on the course.

Chinook ER diagram
This shows the data map (ER diagram) of the sample database used in some of the lab exercises.
SQL statement showing Window Functions
This SQL statement uses Window Functions to calculate a cumulative total.
SQL statement with GROUP BY and HAVING clauses
This SQL statement uses GROUP BY and HAVING clauses to aggregate data and then filter the data once it has been grouped.