We deleted Ben’s duplicate payment, and the real one went too

See the problem

The payment provider tells the shop’s app about each payment. When a message times out, the provider sends it again, and the app saves both. On 14 October, Ben’s UPI payment for order 109 was saved twice, as payments 8 and 14. Finance’s report said Ben paid 26.38 for a 13.19 order.

A developer found the copies the way the duplicate emails problem in the GROUP BY lesson does, with HAVING count(*) > 1, and deleted them:

The cleanup

DELETE FROM payments
WHERE order_id IN (
  SELECT order_id
  FROM payments
  GROUP BY order_id
  HAVING count(*) > 1
);

Both of Ben’s payments went. Order 109 now looked unpaid, and the app asked Ben to pay again.

Define the task

Delete the extra copies of each payment, and keep the first one saved: the smallest payment_id.

• A copy matches another payment in every column but payment_id: the same order_id, paid_on, method and amount.
• Two payments for one order that differ, like a card part and a wallet part, are both real. Keep both.

Pause and think

⏸ Pause and think

Ben’s order 109 has payments 8 and 14, the same in every column but payment_id. The cleanup deletes every payment of an order with more than one. Which of the two does it keep? And what should a cleanup keep from each group of copies?

Give it two minutes with pen and paper before you open a hint.

Hints open one at a time:

Hint 1 of 3

Finding the copies is half the job. The other half is keeping one of each.

Hint 2 of 3

SELECT min(payment_id) … GROUP BY gives one id for each group: the first one saved. Delete every payment that isn’t on that list.

Hint 3 of 3

Group by every column but payment_id. A group of order_id alone puts a card part and a wallet part together.

Understand the idea

Three statements change data. INSERT adds rows, UPDATE changes them, and DELETE removes them. UPDATE and DELETE change every row their WHERE matches, and every row in the table when there’s no WHERE.

INSERT INTO products (product_id, name, category, price)
VALUES (9, 'Phone stand', 'electronics', 7.50);

UPDATE products
SET price = price * 1.10
WHERE category = 'electronics';

DELETE FROM products
WHERE product_id = 9;

Name the columns in an INSERT, so it still works when someone adds a column. A column you leave out gets its DEFAULT, or NULL. In an UPDATE, SET reads each row as it was before the change, so price * 1.10 uses the old price: the mouse goes from 18.00 to 19.80.

Look before you change. Run the WHERE as a SELECT first, to see the rows it matches. Then make the change inside a transaction, and check it before you keep it:

BEGIN;
DELETE FROM payments
WHERE payment_id = 13
RETURNING *;
-- Postgres says DELETE 1, and RETURNING shows the row that went.
ROLLBACK;  -- puts it back; COMMIT keeps it

Until you COMMIT, no one else sees the change. The editors on this page roll back every check for you.

To delete duplicates, keep one of each group. First say what a copy is: the same values in the columns that matter. Then make a keep list with GROUP BY those columns and min(payment_id), the first one saved, and delete every row that isn’t on it with NOT IN. payment_id is a key, so min() never gives NULL, and NOT IN is safe.

Watch the cleanup run on today’s payments. Then type your own, like three copies of one payment, or an order paid in two different parts:

The duplicate cleanup, step by step

⤢ Full view

1. Subquery: FROM payments
2. GROUP BY order_id, paid_on, method, amount
3. SELECT min(payment_id)
4. DELETE … WHERE payment_id NOT IN (…)
5. The table after

payments

payment_id	order_id	method	amount
14	115	card	30.00
15	115	wallet	15.00
16	115	wallet	15.00
17	116	upi	12.00
18	116	upi	12.00
19	117	card	59.00

Step 1 of 5. The subquery runs first. FROM payments reads today’s 6 payments, all paid on 21 October. Question: Payments 15 and 16 match in every column but payment_id. How many groups does GROUP BY order_id, paid_on, method, amount make of them?

What happens next? Payments 15 and 16 match in every column but payment_id. How many groups does GROUP BY order_id, paid_on, method, amount make of them?

12Just show me

◀ BackPlayNext ▶Reset Ask me first Show as text

1DELETE FROM payments
2WHERE payment_id NOT IN (
3  SELECT min(payment_id)
4  FROM payments
5  GROUP BY order_id, paid_on, method, amount
6);

Try your own input

Payments: order_id, method, amount115, card, 30.00 115, wallet, 15.00 115, wallet, 15.00 116, upi, 12.00 116, upi, 12.00 117, card, 59.00Show it

Constraints are rules the table keeps on every write. You met PRIMARY KEY, FOREIGN KEY, UNIQUE and NOT NULL in the keys lesson. Two more:

• CHECK: a condition every row must meet. The store’s orders have CHECK (status IN ('placed', 'shipped', 'delivered', 'cancelled')), and its prices CHECK (price >= 0).
• DEFAULT: the value a column gets when an INSERT leaves it out, like created_at timestamp NOT NULL DEFAULT now().

A statement that breaks a rule is rejected whole: if one row of an INSERT is bad, none of its rows go in. That’s why a rule in the table beats a check in the app: every write meets it, whichever app sends it.

A type is a rule too. Pick the type for what the value is:

• Whole numbers: integer, or bigint past about 2 billion.
• Money: numeric(10, 2), exact to the cent. Not float or real: they keep a close binary value, so 0.10 + 0.20 gives 0.30000000000000004, as in the Python numbers lesson.
• Days: date, and timestamp for a moment in a day. A date column refuses '2026-02-30'; a text column takes anything.
• Yes or no: boolean. Words: text.

Stop the copies at the door. Cleaning up is half the fix. The other half is a rule that refuses the second copy. Say payments had a provider_ref column with the provider’s own id for each payment, and UNIQUE on it. Then a repeated message would be rejected, or skipped with INSERT … ON CONFLICT (provider_ref) DO NOTHING, as in practice problem 1. Add a rule like that after the cleanup: it can’t be added while copies are there.

Climb the ladder

All three delete copies. What changes is which payments each one counts as a copy.

Okay

Delete every payment of an order with two

The idea: Find the orders with more than one payment, and delete their payments.

It looks right because the store has no copies, so it deletes nothing.

DELETE FROM payments
WHERE order_id IN (
  SELECT order_id
  FROM payments
  GROUP BY order_id
  HAVING count(*) > 1
);

Gets right

No payment saved twice, as in the store

Gets wrong

A payment saved twice

Why

It deletes every row of a group, the first one too, so nothing of Ben’s payment is left.

Where it breaks: Ben’s payment is saved twice, and Okay deletes both. Okay passes 1 of the 3 checks below.

Better

Keep the smallest payment_id of each order

The idea: Delete every payment that isn’t the first one of its order.

The gap in Okay: it kept nothing of each group.

Highlighted lines are new in this rung.

DELETE FROM payments
WHERE payment_id NOT IN (
  SELECT min(payment_id)
  FROM payments
  GROUP BY order_id
);

Gets right

A payment saved twicewas No payment saved twice, as in the store

Gets wrong

An order paid in two parts

Why

It counts every payment of an order as a copy, so a real second payment goes too.

What changed from the previous rung

DELETE FROM payments
WHERE order_id IN (
  SELECT order_id
WHERE payment_id NOT IN (
  SELECT min(payment_id)
  FROM payments
  GROUP BY order_id
  HAVING count(*) > 1
);

Where it breaks: Dana pays for order 115 in two parts, 30.00 by card and 15.00 by wallet, and the wallet part is saved twice. Better keeps only the card part. Better passes 2 of 3.

Best

Group by every column but payment_id

The idea: A copy matches in every column but the id. Keep the smallest id of each group of copies.

The gap in Better: it grouped by the order alone.

Highlighted lines are new in this rung.

DELETE FROM payments
WHERE payment_id NOT IN (
  SELECT min(payment_id)
  FROM payments
  GROUP BY order_id, paid_on, method, amount
);

Gets right

Every checkwas A payment saved twice

Gets wrong

Nothing the checks try

Why

Only payments with the same order_id, paid_on, method and amount count as copies.

What changed from the previous rung

DELETE FROM payments
WHERE payment_id NOT IN (
  SELECT min(payment_id)
  FROM payments
  GROUP BY order_id
  GROUP BY order_id, paid_on, method, amount
);

When Best isn’t best: two real payments can match in every column. Dana could pay 15.00 by wallet twice, on purpose. Nothing in the rows tells those apart: only an id from the provider can, so store it, with UNIQUE. In Postgres, a self join does the same cleanup: DELETE FROM payments a USING payments b WHERE a.payment_id > b.payment_id, with a.x = b.x for each column that makes a copy. Window functions, later on the roadmap, can number the copies instead.

Write it yourself

Now write it in the page. Press Run query to check it. Postgres runs in your browser, so nothing is sent anywhere. Each check adds a few payments first, runs your SQL, then reads the payments back. Then it’s all put back.

Try Okay from the ladder first, and open a failed check to see what it changed.

Delete the copies

Ctrl + Enter runs

The tables

• orders (order_id, customer_id, order_date, status, total)
• payments (payment_id, order_id, paid_on, method, amount)

-- Look first: which payments match in every column but payment_id? -- Then make this a DELETE that keeps the first one saved of each. SELECT payment_id, order_id, paid_on, method, amount FROM payments ORDER BY order_id, payment_id;

Run queryReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data
   What this check runs after yours

   SELECT payment_id, order_id, paid_on, method, amount FROM payments;

2. ○Not run yet: Ben’s UPI payment for order 109 is saved twice
   What this check changes

   INSERT INTO payments (payment_id, order_id, paid_on, method, amount)
   VALUES (14, 109, '2026-10-14', 'upi', 13.19);

   What this check runs after yours

   SELECT payment_id, order_id, paid_on, method, amount FROM payments;

3. ○Not run yet: Dana pays for order 115 in two parts, and the wallet part is saved twice
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 4, '2026-10-21', 'placed', 45.00);
   INSERT INTO order_items (order_id, product_id, quantity, unit_price)
   VALUES (115, 8, 1, 45.00);
   INSERT INTO payments (payment_id, order_id, paid_on, method, amount)
   VALUES (14, 115, '2026-10-21', 'card', 30.00),
          (15, 115, '2026-10-21', 'wallet', 15.00),
          (16, 115, '2026-10-21', 'wallet', 15.00);

   What this check runs after yours

   SELECT payment_id, order_id, paid_on, method, amount FROM payments;

Each check runs your SQL on its own copy of the data, then reads the tables back with its own SQL. Nothing is kept.

Compare

Way	Gets right	Gets wrong	Use it when
Okay: every payment of an order with two	No copies	A payment saved twice	Never: it deletes the first one too
Better: the smallest payment_id of each order	A payment saved twice	An order paid in two parts	Each order can have only one payment
Best: the smallest payment_id of each group of copies	Every check	Nothing the checks try	A copy matches in every column but the id

Common mistakes and edge cases

!An UPDATE or DELETE with no WHERE changes every row

DELETE FROM payments; empties the table, and UPDATE products SET price = 0; sets every price. Run the WHERE as a SELECT first, and work inside BEGIN, so ROLLBACK can undo a mistake.

• = NULL matches nothing. UPDATE employees SET manager_id = 1 WHERE manager_id = NULL changes 0 rows. Use IS NULL, as in the NULL lesson.
• A NULL on the keep list empties NOT IN. payment_id NOT IN (14, NULL) is never true, so the DELETE removes nothing. A key like payment_id can’t be NULL, so min(payment_id) is safe.
• SET reads the old row. UPDATE customers SET name = city, city = name WHERE customer_id = 1 swaps the two: Asha’s name becomes Pune, and her city Asha Rao.
• A rule can’t be added while rows break it. The shop sets a minimum order of 10.00, but ALTER TABLE orders ADD CHECK (total >= 10.00) fails: Grace’s order 112 is 9.99. Postgres says the check constraint “is violated by some row”. Deal with those rows first, then add the rule.
• CHECK lets NULL through. NULL >= 0 is unknown, not false, and a CHECK only refuses false. Add NOT NULL too.
• numeric(10, 2) rounds a third decimal, with no error. 7.505 is stored as 7.51.
• Money in a float drifts. 0.10 + 0.20 is 0.30000000000000004. Practice problem 2.
• ON CONFLICT … DO UPDATE needs the key. Write ON CONFLICT (product_id), a column with a PRIMARY KEY or UNIQUE rule. With no column, Postgres stops: “ON CONFLICT DO UPDATE requires inference specification or constraint name”. Practice problem 1.
• A row others point at can’t go first. Deleting order 101 fails, because its items and its payment point at it: “violates foreign key constraint”. Delete the rows that point at it first. Practice problem 3.

Say it in the interview

Questions to ask first

• What makes two rows copies: every column, or some of them?
• Which copy stays: the first one saved, or the last?
• Can I try it inside a transaction first? Is there a backup?

A 60-second walkthrough

“First I’d agree what a copy is: the same order, day, method and amount. payment_id is only a counter. I make a keep list with GROUP BY on those four columns and min(payment_id), the first one saved. Then I delete every payment whose id isn’t on it. payment_id is a key, so NOT IN is safe. I’d run it inside BEGIN, check the DELETE count against a SELECT of the same rows, and then COMMIT. To stop it happening again, I’d store the provider’s payment id with a UNIQUE rule.”

Likely follow-ups

• Keep the newest copy instead? Use max(payment_id).
• DELETE, TRUNCATE or DROP? DELETE removes the rows its WHERE matches. TRUNCATE empties the whole table at once, fast, with no WHERE. DROP TABLE removes the table itself, rules and all.
• Why not a float for money? A float keeps a close binary value, not the exact decimal, so cents drift: 0.10 + 0.20 is 0.30000000000000004. numeric is exact.
• How do you add a row, or update it if it’s already there? INSERT … ON CONFLICT (key) DO UPDATE SET …, often called an upsert. Practice problem 1.

Practice

Three problems, from easy to medium. Each has hints, checks you can run here, and its own Okay, Better and Best answers. Try before you open them.

1. The supplier’s price import (easy)

Each night, the supplier’s price list comes in, and an import adds each product to products. Tonight’s list has the phone stand: product 9, electronics, 7.50. Write the import’s statement for it. Add it if it’s new. If product 9 is already there, give it the new price.

Hint 1 of 3

A plain INSERT stops with “duplicate key value violates unique constraint” when product 9 is already there.

Hint 2 of 3

ON CONFLICT (product_id) DO NOTHING skips the row instead. But then the old price stays.

Hint 3 of 3

ON CONFLICT (product_id) DO UPDATE SET price = EXCLUDED.price. EXCLUDED is the row you tried to add.

The supplier’s price import

Ctrl + Enter runs

The tables

• products (product_id, name, category, price)

-- Add the phone stand, or give it the new price if it's already there. INSERT INTO products (product_id, name, category, price) VALUES (9, 'Phone stand', 'electronics', 7.50);

Run queryReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data: the phone stand is new
   What this check runs after yours

   SELECT product_id, name, category, price FROM products;

2. ○Not run yet: The import runs twice: the phone stand is already there
   What this check changes

   INSERT INTO products (product_id, name, category, price)
   VALUES (9, 'Phone stand', 'electronics', 7.50);

   What this check runs after yours

   SELECT product_id, name, category, price FROM products;

3. ○Not run yet: Last night’s import added the phone stand at 6.90
   What this check changes

   INSERT INTO products (product_id, name, category, price)
   VALUES (9, 'Phone stand', 'electronics', 6.90);

   What this check runs after yours

   SELECT product_id, name, category, price FROM products;

Each check runs your SQL on its own copy of the data, then reads the tables back with its own SQL. Nothing is kept.

Show the Okay, Better and Best answers

Okay

A plain INSERT

The idea: Add the row.

Okay passes 1 of 3. When the import runs twice, it stops: duplicate key value violates unique constraint “products_pkey”.

INSERT INTO products (product_id, name, category, price)
VALUES (9, 'Phone stand', 'electronics', 7.50);

Gets right

A new product, as in the store

Gets wrong

Running twice

Why

product_id is the key, so a second product 9 is rejected, and the import stops.

Better

ON CONFLICT (product_id) DO NOTHING

The idea: Skip the row when product 9 is already there.

The gap in Okay: a second run stopped with an error. Now it skips the row.

Highlighted lines are new in this rung.

INSERT INTO products (product_id, name, category, price)
VALUES (9, 'Phone stand', 'electronics', 7.50)
ON CONFLICT (product_id) DO NOTHING;

Gets right

Running twicewas A new product, as in the store

Gets wrong

A new price

Why

DO NOTHING keeps the row that’s there, with its old price.

What changed from the previous rung

INSERT INTO products (product_id, name, category, price)
VALUES (9, 'Phone stand', 'electronics', 7.50);
VALUES (9, 'Phone stand', 'electronics', 7.50)
ON CONFLICT (product_id) DO NOTHING;

Where it breaks: last night’s import added the phone stand at 6.90, and tonight’s price, 7.50, never goes in. Better passes 2 of 3.

Best

ON CONFLICT (product_id) DO UPDATE

The idea: When product 9 is there, set its price from the new row.

The gap in Better: it kept the old price.

Highlighted lines are new in this rung.

INSERT INTO products (product_id, name, category, price)
VALUES (9, 'Phone stand', 'electronics', 7.50)
ON CONFLICT (product_id) DO UPDATE SET price = EXCLUDED.price;

Gets right

Every checkwas Running twice

Gets wrong

Nothing the checks try

Why

EXCLUDED is the row the INSERT tried to add, so EXCLUDED.price is tonight’s price.

What changed from the previous rung

INSERT INTO products (product_id, name, category, price)
VALUES (9, 'Phone stand', 'electronics', 7.50)
ON CONFLICT (product_id) DO NOTHING;
ON CONFLICT (product_id) DO UPDATE SET price = EXCLUDED.price;

When Best isn’t best: if the supplier can rename a product too, set each column that can change: DO UPDATE SET name = EXCLUDED.name, category = EXCLUDED.category, price = EXCLUDED.price. Many other databases have no ON CONFLICT. They use MERGE, which Postgres 15 and later have too.

2. Gift card balances (medium)

The shop sells gift cards, and the app made this table with the balance as a float. Grace’s card got 0.10 of cashback, then 0.20 more, and the app showed 0.30000000000000004. Write gift_cards so a balance stays exact to the cent, and can never go below 0.

Hint 1 of 3

A float keeps a close binary value. 0.10 and 0.20 have no exact one.

Hint 2 of 3

numeric(10, 2) keeps exact decimals: up to 10 digits, 2 of them after the point.

Hint 3 of 3

A type can’t say “not below 0”. Add CHECK (balance >= 0).

Gift card balances

Ctrl + Enter runs

The tables

• customers (customer_id, name, email, city, signed_up_on)

-- The app's table. Change it so balances stay exact, and never go below 0. CREATE TABLE gift_cards ( card_id integer PRIMARY KEY, customer_id integer NOT NULL REFERENCES customers, balance float NOT NULL );

Run queryReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: Grace’s card with 25.00 goes in, and reads back
   What this check runs after yours

   INSERT INTO gift_cards (card_id, customer_id, balance) VALUES (1, 7, 25.00);
   SELECT card_id, customer_id, balance FROM gift_cards;

2. ○Not run yet: A card with 0.10 of cashback gets 0.20 more
   What this check runs after yours

   INSERT INTO gift_cards (card_id, customer_id, balance) VALUES (1, 7, 0.10);
   UPDATE gift_cards SET balance = balance + 0.20 WHERE card_id = 1;
   SELECT card_id, customer_id, balance FROM gift_cards;

3. ○Not run yet: Grace tries to spend 30.00 with 25.00 on the card
   What this check runs after yours

   INSERT INTO gift_cards (card_id, customer_id, balance) VALUES (1, 7, 25.00);
   UPDATE gift_cards SET balance = balance - 30.00 WHERE card_id = 1;
   SELECT card_id, customer_id, balance FROM gift_cards;

Each check runs your SQL on its own copy of the data, then its own SQL after yours, to see what your tables let in. Nothing is kept.

Show the Okay, Better and Best answers

Okay

balance float

The idea: A number with a decimal point.

Okay passes 1 of 3. 25.00 reads back as 25, but 0.10 and 0.20 don’t add up to 0.30.

CREATE TABLE gift_cards (
  card_id     integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers,
  balance     float NOT NULL
);

Gets right

25.00 going in and reading back

Gets wrong

Adding cents

Why

A float keeps the closest binary value it can, so 0.10 + 0.20 is 0.30000000000000004.

Better

balance numeric(10, 2)

The idea: An exact decimal, to the cent.

The gap in Okay: the float drifted.

Highlighted lines are new in this rung.

CREATE TABLE gift_cards (
  card_id     integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers,
  balance     numeric(10, 2) NOT NULL
);

Gets right

Adding centswas 25.00 going in and reading back

Gets wrong

Spending too much

Why

The type keeps the cents exact, but nothing stops a balance below 0.

What changed from the previous rung

CREATE TABLE gift_cards (
  card_id     integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers,
  balance     float NOT NULL
  balance     numeric(10, 2) NOT NULL
);

Where it breaks: Grace spends 30.00 with 25.00 on the card, and the balance goes to -5.00. Better passes 2 of 3.

Best

And CHECK (balance >= 0)

The idea: Let the table refuse a balance below 0.

The gap in Better: a balance could go below 0.

Highlighted lines are new in this rung.

CREATE TABLE gift_cards (
  card_id     integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers,
  balance     numeric(10, 2) NOT NULL CHECK (balance >= 0)
);

Gets right

Every checkwas Adding cents

Gets wrong

Nothing the checks try

Why

Every INSERT or UPDATE that would leave a balance below 0 is rejected, whichever app sends it.

What changed from the previous rung

CREATE TABLE gift_cards (
  card_id     integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers,
  balance     numeric(10, 2) NOT NULL
  balance     numeric(10, 2) NOT NULL CHECK (balance >= 0)
);

When Best isn’t best: the app should still check before it charges a card, to tell the customer why. The CHECK is the safety net, not the message. A shop with many currencies often stores whole cents in a bigint, with the currency in a column of its own.

3. Dana closes her account (medium)

Dana (customer 4) asks the shop to delete her account and everything about her. Write the SQL that deletes Dana and every row that points at her: her events, her orders, and their items and payments.

Hint 1 of 3

A row others point at can’t go first. orders and events point at customers; order_items and payments point at orders.

Hint 2 of 3

Delete from the far end: payments and order_items, then orders and events, then the customer.

Hint 3 of 3

order_items and payments have no customer_id. Find Dana’s orders with WHERE order_id IN (SELECT order_id FROM orders WHERE customer_id = 4).

Dana closes her account

Ctrl + Enter runs

The tables

• customers (customer_id, name, email, city, signed_up_on)
• orders (order_id, customer_id, order_date, status, total)
• order_items (order_id, product_id, quantity, unit_price)
• payments (payment_id, order_id, paid_on, method, amount)
• events (event_id, customer_id, event_type, occurred_at)

-- Delete Dana (customer 4) and every row that points at her. DELETE FROM customers WHERE customer_id = 4;

Run queryReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data: Dana has page views, and no orders
   What this check runs after yours

   SELECT
     (SELECT count(*) FROM customers) AS customers,
     (SELECT count(*) FROM orders) AS orders,
     (SELECT count(*) FROM order_items) AS order_items,
     (SELECT count(*) FROM payments) AS payments,
     (SELECT count(*) FROM events) AS events;

2. ○Not run yet: Dana starts order 115, and the app saves it before its items
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 4, '2026-10-21', 'placed', 45.00);

   What this check runs after yours

   SELECT
     (SELECT count(*) FROM customers) AS customers,
     (SELECT count(*) FROM orders) AS orders,
     (SELECT count(*) FROM order_items) AS order_items,
     (SELECT count(*) FROM payments) AS payments,
     (SELECT count(*) FROM events) AS events;

3. ○Not run yet: Dana’s order 115 has a backpack in it, and she paid for it
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 4, '2026-10-21', 'placed', 45.00);
   INSERT INTO order_items (order_id, product_id, quantity, unit_price)
   VALUES (115, 8, 1, 45.00);
   INSERT INTO payments (payment_id, order_id, paid_on, method, amount)
   VALUES (14, 115, '2026-10-21', 'card', 45.00);

   What this check runs after yours

   SELECT
     (SELECT count(*) FROM customers) AS customers,
     (SELECT count(*) FROM orders) AS orders,
     (SELECT count(*) FROM order_items) AS order_items,
     (SELECT count(*) FROM payments) AS payments,
     (SELECT count(*) FROM events) AS events;

Each check runs your SQL on its own copy of the data, then reads the tables back with its own SQL. Nothing is kept.

Show the Okay, Better and Best answers

Okay

Her events, then her

The idea: Delete what you can see points at Dana today: her events.

Okay passes 1 of 3. Once Dana has an order, it stops: update or delete on table “customers” violates foreign key constraint “orders_customer_id_fkey”.

DELETE FROM events WHERE customer_id = 4;
DELETE FROM customers WHERE customer_id = 4;

Gets right

Dana with no orders, as in the store

Gets wrong

An order of hers

Why

orders points at customers too, so Dana can’t go while she has an order.

Better

Her events, her orders, then her

The idea: Delete her orders before her too.

The gap in Okay: Dana’s orders still pointed at her.

Highlighted lines are new in this rung.

DELETE FROM events WHERE customer_id = 4;
DELETE FROM orders WHERE customer_id = 4;
DELETE FROM customers WHERE customer_id = 4;

Gets right

An order with nothing in itwas Dana with no orders, as in the store

Gets wrong

An order with items or a payment

Why

order_items and payments point at orders, so an order with either can’t go first.

What changed from the previous rung

DELETE FROM events WHERE customer_id = 4;
DELETE FROM orders WHERE customer_id = 4;
DELETE FROM customers WHERE customer_id = 4;

Where it breaks: Dana’s order 115 has a backpack in it, and she paid for it. Better stops: update or delete on table “orders” violates foreign key constraint “order_items_order_id_fkey”. Better passes 2 of 3.

Best

From the far end: payments and items, orders and events, then her

The idea: Delete each row only once nothing points at it.

The gap in Better: Dana’s order still had rows pointing at it.

Highlighted lines are new in this rung.

DELETE FROM payments
WHERE order_id IN (SELECT order_id FROM orders WHERE customer_id = 4);
DELETE FROM order_items
WHERE order_id IN (SELECT order_id FROM orders WHERE customer_id = 4);
DELETE FROM orders WHERE customer_id = 4;
DELETE FROM events WHERE customer_id = 4;
DELETE FROM customers WHERE customer_id = 4;

Gets right

Every checkwas An order with nothing in it

Gets wrong

Nothing the checks try

Why

Each DELETE runs after the rows that point at its rows are gone.

What changed from the previous rung

DELETE FROM events WHERE customer_id = 4;
DELETE FROM payments
WHERE order_id IN (SELECT order_id FROM orders WHERE customer_id = 4);
DELETE FROM order_items
WHERE order_id IN (SELECT order_id FROM orders WHERE customer_id = 4);
DELETE FROM orders WHERE customer_id = 4;
DELETE FROM events WHERE customer_id = 4;
DELETE FROM customers WHERE customer_id = 4;

When Best isn’t best: a shop usually has to keep orders and payments for its tax records. Then delete what you may, and blank out the rest with UPDATE: UPDATE customers SET name = 'Deleted customer', email = 'deleted-4@example.invalid' WHERE customer_id = 4. With ON DELETE CASCADE on each foreign key, one DELETE would remove everything that points at Dana, and so would a DELETE by mistake.

Check yourself

A few quick questions. Ones you miss are remembered, so you can find your weak spots later.

1. Predict the outputThe store has 13 payments, each for a different order. How many rows does this delete?

   DELETE FROM payments
   WHERE payment_id NOT IN (
     SELECT min(payment_id)
     FROM payments
     GROUP BY order_id
   );

   131201

   Check answer
2. Predict the outputAsha (customer 1) lives in Pune. What are her name and city after this?

   UPDATE customers
   SET name = city, city = name
   WHERE customer_id = 1;

   Pune and Asha RaoPune and PuneAsha Rao and Asha RaoIt stops with an error

   Check answer
3. Predict the outputOrder 101 has three items and a payment. What happens?

   DELETE FROM orders
   WHERE order_id = 101;

   Order 101, its items and its payment are deletedOrder 101 is deleted; its items and payment stayIts payment gets order_id NULLIt stops with an error, and nothing is deleted

   Check answer
4. Predict the outputprice has CHECK (price >= 0). What happens?

   INSERT INTO products (product_id, name, category, price)
   VALUES (9, 'Phone stand', 'electronics', 7.50),
          (10, 'Sticker', 'stationery', -1.00);

   Both rows go inNeither goes in: it stops with an errorThe phone stand goes in, and the sticker doesn’tThe sticker goes in at 0.00

   Check answer
5. Predict the outputMeera (employee 1) is the only one with no manager. How many rows does this change?

   UPDATE employees
   SET manager_id = 1
   WHERE manager_id = NULL;

   1100It stops with an error

   Check answer

Flashcards

Add these to your review deck. Each card comes back a little later every time you get it right.

• Card 1What do UPDATE and DELETE change when there’s no WHERE?Show answer
• Card 2How do you delete duplicate rows and keep one of each?Show answer
• Card 3What does INSERT … ON CONFLICT (product_id) DO UPDATE SET price = EXCLUDED.price do?Show answer
• Card 4Why not store money in a float?Show answer
• Card 5Does CHECK (balance >= 0) refuse a NULL balance?Show answer
• Card 6DELETE, TRUNCATE or DROP TABLE?Show answer

Add 6 cards to my review deck

Cheat sheet

• INSERT adds rows: name the columns. UPDATE and DELETE change every row their WHERE matches, and all of them with no WHERE.
• Look first: run the WHERE as a SELECT, then change inside BEGIN, and COMMIT or ROLLBACK.
• To delete copies: GROUP BY what makes a copy, keep min(id), and delete the rest with NOT IN.
• ON CONFLICT (key) DO NOTHING skips a row that’s there; DO UPDATE SET col = EXCLUDED.col updates it.
• Money in numeric, never float. CHECK and DEFAULT add rules, and a statement that breaks one is rejected whole.
• Delete the rows that point at a row before the row itself.

Go deeper

• In the PostgreSQL docs: INSERT, with ON CONFLICT, UPDATE, DELETE, constraints and numeric.
• Try your own payments: the duplicate cleanup on the SQL visualizer.
• Check the whole level: the SQL foundations check, 15 questions, one or more on each topic.
• Next on the SQL roadmap: the whole map, with transactions and window functions in the lessons after foundations.

Can you do these now?

If you’re not sure of any of these, scroll back to that part before moving on.

• Add, change and remove rows with INSERT, UPDATE and DELETE, and look before you change
• Delete duplicate rows and keep one of each
• Add a row, or update it if it’s already there, with ON CONFLICT
• Pick column types and constraints so the table refuses bad data

✓Mark as completeI already know this

Status: Not started