Database Management · Intermediate level

Intermediate level · Module 2 of 6 · 16 guided hours

Advanced SQL Queries

Answer complex questions with subqueries, common table expressions and window functions.

Learning objectives

  • Write subqueries, including correlated subqueries and EXISTS
  • Structure complex queries with common table expressions
  • Use window functions for rankings, running totals and comparisons

Topics

  • subqueries in WHERE, FROM and SELECT
  • EXISTS and NOT EXISTS
  • common table expressions (WITH)
  • recursive CTEs (overview)
  • window functions: ROW_NUMBER, RANK, SUM OVER
  • PARTITION BY and frames

Lessons

  1. Lesson 1 Subqueries and EXISTS

    Use queries inside queries to filter and calculate, and test for the existence of related rows.

    subqueryIN (subquery)correlated subqueryEXISTS

  2. Lesson 2 Common Table Expressions

    Break complex queries into named steps with WITH, and understand recursive CTEs for hierarchies.

    WITH … ASstep-by-step queriesreadabilityrecursive CTE

  3. Lesson 3 Window Functions

    Rank rows, calculate running totals and compare rows with their group without collapsing them.

    window functionOVER (PARTITION BY … ORDER BY …)ROW_NUMBER and RANKrunning total

Practical activity

Answer a set of programme manager questions: households not reached in the last two distributions, the top three villages per county and a running total of items distributed.

Expected outcomes

  • Readable solutions to complex questions
  • Confident use of window functions

Assessment

Query test with six advanced questions, marked on correct results and readability.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.