DB Exercises
0
Track Guide & Problems

SQL Joins & Relational Modeling Practice

Connect relational data across enterprise tables. Detect dormant accounts, attribute marketing ROI across campaigns, build organizational hierarchies with self joins, and reconcile ledgers with FULL OUTER JOIN.

Track Size
12 Practice Problems
Engine
PostgreSQL 16 WASM (<5ms)
Grading
Automated Row-by-Row Checks
Start Problem 1: Detecting Dormant Accounts with LEFT JOIN

Core Syntax & Mechanics

Key patterns and rules practiced in this track.

LEFT JOIN Anti-Patterns (Detecting Missing Rows)

Keeps all rows from table_a. If no matching row exists in table_b, its columns are filled with NULL. Filtering WHERE b.id IS NULL extracts records with zero child activity.

Syntax Pattern
SELECT a.* FROM table_a a LEFT JOIN table_b b ON a.id = b.a_id WHERE b.id IS NULL
Example
SELECT c.customer_id, c.name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_id IS NULL;

Self Joins for Hierarchical Relationships

Joining a table to itself allows modeling recursive parent-child relationships like organizational charts, flight layovers, and referral chains.

Syntax Pattern
SELECT e.name, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.id
Example
SELECT emp.full_name, mgr.full_name AS manager_name FROM staff emp LEFT JOIN staff mgr ON emp.manager_id = mgr.staff_id;

FULL OUTER JOIN for Financial Reconciliation

Returns all records from both tables. When a key matches, rows are joined; unmatched records from either table appear with NULLs on the opposing side.

Syntax Pattern
SELECT * FROM bank_statement s FULL OUTER JOIN internal_ledger l ON s.ref = l.ref
Example
SELECT COALESCE(s.ref, l.ref) AS ref_code, s.amount, l.amount FROM bank_statements s FULL OUTER JOIN ledger_entries l ON s.ref = l.ref;

Joins Exercises

Click any problem to open the interactive editor.

12 Problems
#1
Detecting Dormant Accounts with LEFT JOINFREE

List of customer_id, company_name, account_manager, and plan_name for customers with zero activity records.

Practice
#2
Ad Campaign Attribution & ROIFREE

Campaign performance metrics with campaign_name, channel, budget_spent, revenue_generated, and roas.

Practice
#3
Inpatient Primary Attending Physician RosterFREE

Roster of admitted inpatients with attending physician details, sorted by ward room.

Practice
#4
Missing Inbound Supplier Shipments with LEFT JOINPRO

List of unreceived purchase orders sorted chronologically by expected delivery date.

Practice
#5
Organizational Chart Hierarchy with SELF JOINPRO

Org chart hierarchy showing employee name, title, and manager name.

Practice
#6
Cross-Border Wire Transfers with Beneficiary KYC LevelPRO

Compliance violation list of high-value wires sent to unverified accounts.

Practice
#7
Fleet Vehicle Compliance Audit with Multi-Table JOINPRO

Trips performed with uninspected vehicles sorted by fare amount.

Practice
#8
Feature Matrix Generation with CROSS JOINPRO

Complete plan-to-feature matrix with enabled status flag.

Practice
#9
Drug-Allergy Interaction Warning JoinPRO

Flagged prescriptions where prescribed drug substance matches documented patient allergies.

Practice
#10
General Ledger Bank Reconciliation with FULL OUTER JOINPRO

Reconciliation table classifying each transaction as Matched, Missing in Bank, or Missing in Ledger.

Practice
#11
Abandoned Cart Sessions & Lost GMVPRO

High-value abandoned cart sessions with cart value >= $100.

Practice
#12
Multi-Leg Freight Hub Transit ConnectionsPRO

Two-leg transit routes from Chicago with layover hub and total flight hours.

Practice

Common Interview Mistakes

  • •Accidentally turning a `LEFT JOIN` into an `INNER JOIN` by putting a filter on the right-hand table in the `WHERE` clause instead of the `ON` clause.
  • •Unintended Cartesian explosion when joining on non-unique foreign keys without proper grouping.
  • •Forgetting that `FULL OUTER JOIN` requires `COALESCE()` to display a non-null identifier from either side.

Frequently asked questions about Joins

When should a filter condition be placed in ON vs WHERE in a LEFT JOIN?▸
Placing a condition in the `ON` clause filters the right-side table before joining, ensuring all left-side rows are still returned. Placing a condition on the right-side table in the `WHERE` clause removes rows where the right side is NULL, effectively converting the query into an INNER JOIN.
What is a CROSS JOIN and when is it used?▸
A CROSS JOIN produces the Cartesian product of two tables (every row of table A paired with every row of table B). It is commonly used to generate matrix grids, date scaffolds, and feature entitlement baselines.
How do you find orphaned records that have no match in a foreign key relationship?▸
Use a LEFT JOIN from the primary table to the related table and add `WHERE related_table.primary_key IS NULL`.

Practice Joins now

Run queries directly in your browser against realistic tables.

Open Problem 1