O'Reilly logo
live online training icon Live Online training

Advanced SQL Series: Relational Division

Ami Levin

Structured Query Language—more commonly known as SQL—is an amazing declarative programming language. While easy enough to get started, learning just the syntax and simple use cases will only get you so far. The trickery that you can apply to writing highly efficient and refined logic is mind-blowingly complex. Producing elegant, efficient, and scalable set-based solutions in SQL can be very challenging.

SQL expert Ami Levin leads a thrilling, action-packed deep dive into relational division. You'll learn how to solve relational division challenges using set operators, COUNTs, JOINs, and nested NOT EXISTS and explore more advanced topics like Todd’s division, Romley’s division, and ordered division.

What you'll learn-and how you can apply it

By the end of this live online course, you’ll understand:

  • Relational division concepts and challenges
  • The pros and cons of relational division solutions, including set operators, COUNTs, JOINs, and nested NOT EXISTS
  • Advanced relational division topics

And you’ll be able to:

  • Solve relational division and similar challenges using SQL
  • Approach other challenging SQL tasks with a proven, easy methodology
  • Improve performance, readability, and scalability of your SQL queries

This training course is for you because...

  • You're an experienced SQL developer who wants to take your skills to a new level.
  • You're a data analyst or scientist who is often stumped by challenges that seem too hard to solve in SQL.

Prerequisites

  • At least two years of SQL programming experience
  • A basic understanding of the relational model

Recommended preparation:

About your instructor

  • Ami Levin is a senior instructor, data tier architect, data modeler, database designer and SQL developer with over 20 years of experience.

Schedule

The timeframes are only estimates and may vary according to how the class is progressing

Introduction to relational division (20 minutes)

  • Lecture: Codd’s table operations—set operators and relationship operators; the relational division operator—division with and without remainders
  • Hands-on exercise: Download and install the course exercise database

Solving relational division using set operators (20 minutes)

  • Lecture and demonstration: Using set operators
  • Hands-on exercise: Use set operators

Break (10 minutes)

Solving relational division with JOINs (30 minutes)

  • Lecture and demonstration: Using JOINs
  • Hands-on exercise: Use JOINs

Solving relational division using COUNTs (20 minutes)

  • Lecture and demonstration: Using COUNT
  • Hands-on exercise: Use COUNT

Break (10 minutes)

Solving relational division with nested NOT EXISTS (40 minutes)

  • Lecture and demonstration: Using NOT EXISTS
  • Hands-on exercise: Use NOT EXISTS

Advanced relational divisions (20 minutes)

  • Lecture: Todd’s division; Romley’s division; ordered division

Wrap-up and Q&A (10 minutes)