2 / 5
SQL (SQLite engine)

Validate a left join with missing rows

Medium70 pts~25 min
  • LEFT JOIN
  • Missing rows
  • COUNT semantics
Practice app · Acme Commerce DB

A real SQL database (SQLite engine) seeded with an e-commerce and HR dataset, with schema browser and query runner.

Test URL
/lab/database-testing-validate-a-left-join-with-missing-rows

Your starter code already declares TEST_URL — never hardcode a host.

Objective

Validate the “orders per customer” report: every customer must appear, including the five who never ordered, with a count of 0.

Your task

  1. 1LEFT JOIN customers to orders on customer_id.
  2. 2Group by customer and return c.id, c.name and COUNT(o.id) AS order_count.
  3. 3Confirm there are 30 rows and exactly 5 have order_count = 0.

Acceptance criteria

  • The last result set lists all 30 customers with correct order counts
  • The query uses LEFT JOIN

Constraints

  • Only the last result set of your script is compared, so finish with the verification query.

SQL (SQLite engine) · Database Testing · Joins, views & aggregation