SQL Examples

Real, executable SQL examples for intermediate and advanced techniques — every query runs live against a sample database.

Aggregates

Aggregate Functions: AVG, MAX, MIN, and COUNT Together

Summarize student grades per course using AVG, MAX, MIN, and COUNT alongside a join for readable course names.

CASE Expressions

CASE Expressions: Conditional Logic in SQL

Use CASE WHEN to bucket employees into salary bands, and inside COUNT() to conditionally tally rows.

CTEs

Common Table Expressions (CTEs) With WITH

Use a WITH clause to compute total spend per customer as a named, reusable result set, then filter and join against it.

Subqueries

Correlated Subqueries: Comparing Each Row to Its Own Group

Find movies whose budget exceeds the average budget for their own genre, using a subquery that references the outer query's current row.

EXISTS

EXISTS and NOT EXISTS: Testing for Related Rows

Use EXISTS with a correlated subquery to find authors who have published at least one book, and NOT EXISTS for the opposite.

Group By

GROUP BY Basics: Aggregating Rows Into Summaries

Collapse individual orders into one summary row per customer using GROUP BY, COUNT, and SUM.

Joins

INNER JOIN: Combining Rows From Two Related Tables

Use INNER JOIN to attach department names to employee records, keeping only rows that match on both sides.

LEFT JOIN: Keeping Rows That Don't Match

Use LEFT JOIN to list every movie alongside its audience rating, including movies that have never been rated.

Window Functions

Window Functions: RANK() and PARTITION BY

Rank employees by salary within their own department using RANK() OVER (PARTITION BY ...), without collapsing rows the way GROUP BY does.