Intermediate SQL: Joins, Subqueries and CASE WHEN
Intermediate SQL (joins, subqueries) is an intermediate-level course of 5 lessons (about 2 h 32 min), with 19 exercises and 15 quiz questions. Prerequisites: SQL Fundamentals (SELECT, Filters, Aggregates). Exercises run in the browser, with nothing to install.
- SQL
- Intermediate
- 5 lessons
- 2 h 32 min
- 19 exercises
- Exercises run in the browser
The course in brief
This course teaches you how to query a database made of several tables linked by keys. You will write inner and outer joins, anti-joins, and scalar, correlated or FROM-clause subqueries, and you will combine results with set operations. All exercises run in the browser on an online store database.
What you will be able to do
- Link tables with INNER JOIN and LEFT JOIN, choosing the right join
- Find rows with no match using an anti-join and NOT EXISTS
- Chain several joins and write self-joins
- Write scalar subqueries, with IN or EXISTS, correlated or as a derived table
- Create categories and cross-tabs with CASE WHEN
- Combine results with UNION, UNION ALL, INTERSECT and EXCEPT
Syllabus: 5 lessons
- Understand why data is spread across several tables linked by keys
- Write an inner join with JOIN ... ON
- Use table aliases and qualify ambiguous columns
- Aggregate the result of a join
- 02
Keeping All Rows: LEFT JOIN and the Anti-Join
- Use LEFT JOIN to keep rows with no match
- Find "orphan" rows with an anti-join (LEFT JOIN ... IS NULL)
- Understand the difference between a condition in ON and in WHERE
LEFT JOINAnti-joinCondition in ON or in WHERERIGHT and FULL JOIN
- 03
Multiple Joins and Self-Joins
- Chain several joins to traverse a schema (clients → commandes → lignes → produits)
- Compute amounts from an order lines table
- Join a table to itself (self-join) to handle a hierarchy
- 04
Subqueries: Scalar, IN, EXISTS and Correlated
- Use a scalar subquery in a filter
- Filter with IN, NOT IN and EXISTS
- Build a derived table in the FROM clause
- Write a correlated subquery
Scalar subqueryIN and EXISTSDerived tableCorrelated subquery
- 05
Conditional Logic and Set Operations: CASE, UNION, EXCEPT
- Create categories with CASE WHEN
- Use conditional aggregation (counting by criterion within a GROUP BY)
- Combine results with UNION, UNION ALL, INTERSECT and EXCEPT
CASE WHENConditional aggregationUNION and UNION ALLINTERSECT and EXCEPT
Final project
Multi-table sales analysis for an online store
Using the tables clients, commandes, lignes_commande, produits and fournisseurs (an extended version of the ones in the course), answer the sales department's questions: revenue by customer and by category, inactive customers, products never sold, each customer's latest order, customer segmentation and list comparison.
Deliverables
- A .sql file with at least 10 commented queries, each answering a business question stated in plain language
- At least one four-table join and one self-join
- At least one anti-join (LEFT JOIN ... IS NULL or NOT EXISTS)
- At least one derived table and one correlated subquery
- A customer segmentation with CASE WHEN and a cross-tab built with conditional aggregation
- A note explaining the choice between INNER and LEFT JOIN for three of the queries
Assessment criteria
- Results are correct (no rows lost or multiplied by a join)
- The join type is justified and conditions are placed in ON or WHERE appropriately
- The NOT IN / NULL trap is avoided
- Queries are readable: explicit aliases, indentation, comments
- Result columns are clearly named
Frequently asked questions
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN keeps only the rows that have a match in both tables. LEFT JOIN keeps every row of the left table and fills the right-hand columns with NULL when there is no match.
Should I use a join or a subquery?
They are often equivalent, and modern engines optimize them in a similar way. Pick the most readable form: EXISTS to test for existence, a join to display columns from both tables.
What level do I need to take this course?
You need to know SELECT, WHERE, ORDER BY, aggregate functions and GROUP BY, which are taught in the SQL fundamentals course.
Do the exercises work with PostgreSQL or MySQL?
The exercises run on SQLite, but the queries taught are standard SQL that also works on PostgreSQL and, with a few differences, on MySQL and SQL Server.
This course, told for your job
Say what you want to be able to do: “Intermediate SQL (joins, subqueries)” takes its place in your path, with examples from your field. The concepts stay those of the course.