Practice

Find the data bugs the UI hides

Six data rules, one shop database with seeded bugs. Write a SQL check for each rule, and it is run against a clean copy, the data you can see, and a second copy you can't. Runs in your browser.

How it works

The database behind a small shop has five tables and six rules. Some rows break the rules. Nothing on a screen would show it: an order item that belongs to no order never appears on any page, and a total that doesn't match its items only shows up when someone adds them up.

For each rule, write one query that returns the rows breaking it. Look around the data first with the query box below, and read the rules carefully: each copy of the data has rows that look wrong and aren't.

What counts as caught

Your check runs on three copies of the database, each built fresh, so nothing you run can change them:

a clean copy, where every rule holds. Any row here is a false alarm;
the copy you can see, where it must return exactly the bad rows;
a second copy with the same kinds of problems in different rows, and every id changed. A check that names ids or emails from the first copy fails here.

Only the first column counts, so you can add more columns to read the result. Everything runs in your browser with SQLite 3.49.1 via sql.js 1.14.2; no query is sent anywhere.

The rules

Every customer has an email address: not missing, and not blank.
No two customers share an email address, ignoring upper and lower case.
Every order item belongs to an order that exists.
An order's total is the sum of quantity × unit price over its items. A cancelled order's total is 0, whatever items it still lists.
Every order with status paid or shipped has a payment. Pending and cancelled orders don't need one.
No order is placed before its customer registered. Times are UTC, stored as YYYY-MM-DD HH:MM:SS.

The tables

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  email TEXT,
  name TEXT NOT NULL,
  created_at TEXT NOT NULL
);
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price_cents INTEGER NOT NULL
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  created_at TEXT NOT NULL,
  total_cents INTEGER NOT NULL
);
CREATE TABLE order_items (
  id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  unit_price_cents INTEGER NOT NULL
);
CREATE TABLE payments (
  id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  amount_cents INTEGER NOT NULL,
  paid_at TEXT NOT NULL
);

Foreign keys aren't declared, which is how an order item can point at an order that doesn't exist. SQLite doesn't enforce them unless they are declared and switched on.

Prefer your own tools? Download the data you can see as a SQL script and load it into any SQLite database. The checker's clean and second copies stay on this page.

Look at the data

Query the shop data

The six checks

Task 1

Customers without an email address

Return the id of every customer whose email is missing or blank. Return the customer id as the first column.

Hint

Missing and blank are two different things in SQL, and blank can be more than an empty string.

Show an answer

SELECT id
FROM customers
WHERE email IS NULL OR trim(email) = '';

A common first attempt

SELECT id
FROM customers
WHERE email = NULL OR email = '';

Comparing anything with NULL gives NULL, never true, so `email = NULL` matches no row. And `email = ''` misses an address made of spaces.

Task 2

Two customers, one email address

Return the id of every customer whose email address another customer also has, ignoring case. Return the customer id as the first column.

Hint

Group by the address in one case, then go back for the customers in those groups.

Show an answer

SELECT id
FROM customers
WHERE lower(email) IN (
  SELECT lower(email)
  FROM customers
  GROUP BY lower(email)
  HAVING count(*) > 1
);

A common first attempt

SELECT min(id)
FROM customers
GROUP BY email
HAVING count(*) > 1;

Grouping by the raw address treats [email protected] and [email protected] as different people, and returning one id per group hides the other customer.

Task 3

Order items with no order

Return the id of every order item whose order doesn't exist. Return the order item id as the first column.

Hint

An inner join only returns rows that match on both sides, so it can never show you the rows that don't.

Show an answer

SELECT i.id
FROM order_items i
LEFT JOIN orders o ON o.id = i.order_id
WHERE o.id IS NULL;

A common first attempt

SELECT i.id
FROM order_items i
JOIN orders o ON o.id = i.order_id
WHERE o.id IS NULL;

The inner join drops every item without an order before the WHERE clause runs, so the query can't find the rows it is looking for.

Task 4

Totals that don't add up

Return the id of every order whose total isn't the sum of its items. Cancelled orders must have a total of 0. Return the order id as the first column.

Hint

An order with no items still has a total to check, and a cancelled order follows a different rule.

Show an answer

SELECT o.id
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
GROUP BY o.id
HAVING o.total_cents <> CASE
  WHEN o.status = 'cancelled' THEN 0
  ELSE coalesce(sum(i.quantity * i.unit_price_cents), 0)
END;

A common first attempt

SELECT o.id
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id
HAVING o.total_cents <> sum(i.quantity * i.unit_price_cents);

The inner join loses the order that has a total but no items, and ignoring the cancelled rule flags a correct cancelled order as a false alarm.

Task 5

Paid for, but no payment

Return the id of every paid or shipped order that has no payment. Return the order id as the first column.

Hint

Read the statuses in the rule carefully: an order that has shipped was paid for too.

Show an answer

SELECT o.id
FROM orders o
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status IN ('paid', 'shipped')
  AND p.id IS NULL;

A common first attempt

SELECT o.id
FROM orders o
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'paid'
  AND p.id IS NULL;

It checks one of the two statuses the rule names, so a shipped order with no payment gets through.

Task 6

Orders from before the customer existed

Return the id of every order placed before its customer registered. Return the order id as the first column.

Hint

Both columns hold a date and a time. Compare all of it.

Show an answer

SELECT o.id
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at < c.created_at;

A common first attempt

SELECT o.id
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE date(o.created_at) < date(c.created_at);

`date()` throws the time away, so an order placed half an hour before registration, on the same day, looks fine.

Limits

All three copies ship in this page's JavaScript, so reading the source reveals them. The checker is for practising, not for proving anything to anyone else.

The answers are written for SQLite. Other databases differ in their functions and in how they compare text, so check a query against your own database's documentation before you rely on it there.

Passing here means your check finds these rows in these copies. Real data has more ways to go wrong than six, and a check that returns nothing only tells you that none of the rows it looks for are there. For why a green result can mislead, see Why did this test pass?