Postgres in depth · Chapter 8 of 65

Price cuts and takedowns

By the end of this chapter you can change rows that are already in a table, remove rows or empty a table, see exactly which rows a change touched, and try a risky change in a way you can take back. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Day one: an empty server
  • 4 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.

Why you need this

LCO's first sale starts tomorrow: Kabir wants ₹500 off the published intermediate courses. Machine Learning Intro is finally ready to launch, but its level was typed as Intermediate with a capital I. jQuery Essentials is being taken down for good. And Kabir also wants every draft deleted, which makes Priya nervous: "Show me what it would delete before it deletes anything."

INSERT only adds rows. Fixing a row by deleting it and inserting it again is slow and easy to get wrong. You need UPDATE and DELETE, and a way to undo a change you regret.

Run lab load day-one to get LCO's courses and students in your own database; run it again whenever you want them back as they were. The examples change students; the tasks change courses.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    sale.sql: 500 off

    For the sale, every published course whose level is intermediate (in lowercase) costs 500 less than it does now.

    Afterwards those courses' prices are 500 lower, and every other course, drafts included, is unchanged.

    Careful: 100% Practical TypeScript is intermediate too, but it is a draft, so its price stays.

  2. Task 2

    relaunch.sql: Machine Learning Intro goes live

    Machine Learning Intro is ready. In one statement: set its level to intermediate (lowercase), publish it, and set its launch day to 2026-03-02.

    Afterwards Machine Learning Intro has these three new values, and nothing else in the table has changed.

  3. Task 3

    takedown.sql: jQuery Essentials goes

    jQuery Essentials is being taken down for good. Remove it from courses, and return what was removed.

    The statement returns two columns, title and price, for the removed row.

    Afterwards courses has the other 13 courses, unchanged.

  4. Task 4 · interview question

    Interview: dry-run.sql, look before you delete

    Kabir wants every draft (not published) course deleted. Priya wants a dry run first: run the delete inside a transaction, return the title of every course it removes, and then undo it.

    The file returns one column, title, one row per course the delete would remove. Any order.

    Afterwards nothing has changed: all 14 courses are still there.

What you will learn

  • UPDATE ... SET ... WHERE: change some columns of some rows.
  • DELETE ... WHERE: remove rows.
  • TRUNCATE: empty a whole table, fast.
  • RETURNING: the rows a change touched.
  • BEGIN and ROLLBACK: a safety net for a risky change.

More in Day one: an empty server

Build "Price cuts and takedowns" yourself

LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.