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.
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.