Intermediate SQL: joins, subqueries and set operations
You answer real business questions in a single query using every kind of join, correlated subqueries, EXISTS, set operations and conditional expressions.
Level 201 · 19 lessons · 15 practice labs
Lessons
Joins
- Join types: inner, left, right, full
- Joining a table to itself
- Cross joins and date series
- Join pitfalls: multiplying rows
- Semi-joins and anti-joins
- Short task: employees with their managers
Subqueries
- Scalar subqueries
- IN and EXISTS
- Correlated subqueries
- Derived tables
- Readable queries with WITH
- Short task: customers who spend above the average
Expressions and sets
- UNION, INTERSECT and EXCEPT
- Conditional columns with CASE
- Three-valued logic and COALESCE
- Conditional aggregates and FILTER
- Text and date calculations
- Insert or update: ON CONFLICT and ON DUPLICATE KEY
- Capstone: management reports for an online shop
Practice labs
- List staff with their managers: Join Turna Lojistik's staff table to itself to show everyone with their manager's name.
- Show days with no sales too: Generate a date series, join it to sales and show days without sales as zero.
- Find differences between two sources with FULL JOIN: Compare warehouse count with system stock and list products present in only one or with differing quantities.
- Find above-average spenders: Use a scalar subquery to list customers who spend above the average order value.
- The priciest product in each category: Use a correlated subquery to return the most expensive product per category, handling ties correctly.
- Make a nested query readable with WITH: Split a three-level nested report query into named CTEs that produce the same result.
- Find this month's buyers with EXISTS: List customers with at least one order this month using EXISTS.
- Find books never borrowed: List Defne Kitabevi books never loaned using NOT EXISTS and compare with a LEFT JOIN solution.
- Find customers who bought from every category: Use a double NOT EXISTS to find customers who bought at least one product from every category.
- Combine two branches' customers: Merge two branches' customer lists without duplicates using UNION and show the difference from UNION ALL.
- Turn status codes into readable text: Use CASE to turn order status codes into readable labels and count orders per status.
- A one-row report with conditional sums: Show counts and amounts of returned, cancelled and completed orders side by side in one query with conditional sums.
- Update the product if it exists, else insert: Apply a supplier price list to the products table with ON DUPLICATE KEY UPDATE.
- Merge a stock count with an upsert: Merge a daily count file into the stock table with ON CONFLICT, adding to existing quantities.
- Repair an import that creates duplicates: Make an import script that duplicates rows on every run safely re-runnable with a unique key and an upsert.
About this course
You answer real business questions in a single query using every kind of join, correlated subqueries, EXISTS, set operations and conditional expressions.
What you will learn
- Joins
- Subqueries
- Expressions and sets
Prerequisites
Finishing the level 101 courses of the same path, or knowing those topics, is enough.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 201; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.