Skip to content
Tutorials Center
My learning

Topics

Lesson 88 of 90 ยท Practice and Review

PostgreSQL Exercises with Solutions

Fourteen graded PostgreSQL exercises with solutions on types, queries, upserts, indexes, transactions and schema design. Portable parts run live in your browser.

You now have a long list of PostgreSQL features in your head: types, DISTINCT ON, upserts, indexes, MVCC, isolation levels, constraints. Reading about them is the easy half. The hard half is reaching for the right one when a problem is put in front of you. This page gives you fourteen exercises, ordered from easy to demanding, each with a task, a place to try it, and a hidden solution with an explanation.

A word on what runs where. The editors on this site run SQLite in your browser, not a PostgreSQL server. So every exercise that can be solved with portable SQL (joins, FILTER, window functions, upserts, RETURNING, transactions, partial indexes, constraints) runs live, and the solution notes show the PostgreSQL spelling when it differs. Exercises about PostgreSQL-only features (DISTINCT ON, timestamptz, jsonb indexes, isolation levels) are marked read-only: you reason about them on paper, then open the solution.

How to use this page

Try first Spend a few minutes before opening a solution.
Run it Live editors start with a query you can extend.
Compare Match the result, not the exact wording.
Read the notes The explanation matters more than the code.
1 / 2

Most live exercises use the bookstore database: authors, books, customers and orders. Run the starter query first and look at the column names. If you get stuck on a topic, the lessons are one click away, and the cheat sheet is a good tab to keep open.

Types and queries

1. Prices in cents (Beginner)

Money should never travel as a float. List the first three books with their price converted to a whole number of cents, in a column named cents. Practices numeric types.

SQL Exercise 1: Prices in cents ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
SELECT title, CAST(ROUND(price * 100) AS INTEGER) AS cents
FROM books
WHERE id <= 3;

ROUND matters: 14.99 * 100 is not exactly 1499 in binary floating point, and a plain cast would truncate any tiny error downward. In PostgreSQL you would normally store numeric(10,2) or integer cents in the first place, and the cast is written (price * 100)::int or round(price * 100)::int.

2. Most expensive book per genre (Intermediate)

For each genre, show the single most expensive book (title and price). Break ties by title. This is the classic "top-N per group" problem from DISTINCT ON and top-N.

SQL Exercise 2: Top book per genre ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Show solution

The portable version numbers the rows inside each genre and keeps number 1:

SQL
SELECT genre, title, price
FROM (
  SELECT genre, title, price,
         ROW_NUMBER() OVER (PARTITION BY genre ORDER BY price DESC, title) AS rn
  FROM books
) AS ranked
WHERE rn = 1
ORDER BY genre;

PostgreSQL has a shorter, PostgreSQL-only form (read-only here):

SQL
SELECT DISTINCT ON (genre) genre, title, price
FROM books
ORDER BY genre, price DESC, title;

DISTINCT ON keeps the first row of each genre according to the ORDER BY, so the leading ORDER BY column must match the DISTINCT ON expression.

3. In stock versus sold out (Intermediate)

For each genre, count how many books have stock and how many are sold out (stock of zero), in two columns named in_stock and sold_out. Use one pass over the table. Practices FILTER and grouping.

SQL Exercise 3: Stock per genre ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
SELECT genre,
       COUNT(*) FILTER (WHERE stock > 0)  AS in_stock,
       COUNT(*) FILTER (WHERE stock = 0)  AS sold_out
FROM books
GROUP BY genre
ORDER BY genre;

FILTER (WHERE ...) limits one aggregate to some rows without affecting the others. The older workaround, SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END), works everywhere but is noisier.

4. Who spent the most? (Intermediate)

Show each customer's name and the total they spent (quantity times the book's price), rounded to two decimals, biggest spender first. Break ties by name.

SQL Exercise 4: Spending per customer ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
SELECT c.name, ROUND(SUM(o.quantity * b.price), 2) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN books b ON b.id = o.book_id
GROUP BY c.id, c.name
ORDER BY spent DESC, c.name;

Join first, then group. Grouping by c.id (the primary key) is enough in PostgreSQL, because the other customers columns are functionally dependent on it, but listing c.name too is harmless and works in every database. In PostgreSQL, ROUND(x, 2) needs a numeric argument, so with a real or double precision column you would cast first: ROUND((...)::numeric, 2).

5. Authors with no sold-out book (Advanced)

List the authors none of whose books are sold out, in alphabetical order. Think about which join or subquery handles "none".

SQL Exercise 5: Authors without sold-out books ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
SELECT a.name
FROM authors a
WHERE NOT EXISTS (
  SELECT 1
  FROM books b
  WHERE b.author_id = a.id AND b.stock = 0
)
ORDER BY a.name;

NOT EXISTS asks "is there no matching row?" and is safe with NULLs. NOT IN (SELECT ...) looks similar but silently returns nothing if the subquery produces a single NULL. PostgreSQL turns NOT EXISTS into an anti-join, which is usually the best plan.

6. A week of dates (Intermediate)

Produce one row per day from 2025-02-26 to 2025-03-02, with the day number of the week in a second column. Practices set-returning functions.

SQL Exercise 6: Calendar rows

Press Run (or Ctrl + Enter) to see the result here.

Show solution

This runs live with a recursive CTE, because SQLite has no generate_series in our build:

SQL
WITH RECURSIVE d(day) AS (
  SELECT '2025-02-26'
  UNION ALL
  SELECT date(day, '+1 day') FROM d WHERE day < '2025-03-02'
)
SELECT day, strftime('%w', day) AS dow
FROM d;

In PostgreSQL (read-only):

SQL
SELECT d::date AS day, extract(dow FROM d) AS dow
FROM generate_series('2025-02-26'::date, '2025-03-02'::date, interval '1 day') AS d;

Both number Sunday as 0. PostgreSQL also offers isodow, where Monday is 1 and Sunday is 7.

7. Time zones (Read-only)

A timestamptz column holds 2025-03-30 00:30:00+00. A colleague in Paris (UTC+1 in winter, UTC+2 once summer time starts at 01:00 UTC on that date) says the row happened "on the 30th". Is the colleague right, and how would you display it in Paris time?

Show solution

Yes. A timestamptz stores an absolute instant (in UTC), and the time zone only affects display. At 00:30 UTC the clocks in Paris still show winter time, so the local time is 01:30 on March 30. You can show it explicitly with:

SQL
SELECT created_at AT TIME ZONE 'Europe/Paris' AS paris_time
FROM events;

AT TIME ZONE on a timestamptz returns a plain timestamp in that zone. Applying it to a plain timestamp does the opposite: it interprets the value as local time and returns a timestamptz. See dates, times and time zones.

Changing data

8. Stock counter upsert (Intermediate)

A table inventory(sku PRIMARY KEY, qty) holds pen with 10 and ink with 4. Add 5 pens and 7 caps in one statement: existing SKUs must increase, new SKUs must be inserted, and the statement must return the final rows it touched. Practices upserts.

SQL Exercise 8: Upsert with RETURNING

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
INSERT INTO inventory (sku, qty) VALUES ('pen', 5), ('cap', 7)
ON CONFLICT (sku) DO UPDATE SET qty = inventory.qty + EXCLUDED.qty
RETURNING sku, qty;

A plain INSERT of pen would fail with a duplicate key; ON CONFLICT (sku) DO UPDATE turns that conflict into an update. EXCLUDED holds the row you tried to insert, and the table name holds the stored row. The statement is atomic, so two sessions racing to add stock cannot lose an increment. ink is untouched and is not returned.

Indexes

9. Index only what you query (Intermediate)

A tickets table has millions of rows, but only about 2% are open, and the application constantly asks for one owner's open tickets. Write an index that makes this query fast and stays small. Then run EXPLAIN QUERY PLAN to confirm the index is used. Practices partial indexes.

SQL Exercise 9: A partial index

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
CREATE INDEX idx_open ON tickets (owner) WHERE status = 'open';

Add that line before the EXPLAIN and the plan changes from a full scan to a search on the index. Because the index only contains open tickets, it is about fifty times smaller than a full index on owner, cheaper to maintain, and still perfect for this query. The query must repeat the predicate (status = 'open') so the planner can prove the index applies. PostgreSQL syntax is identical.

10. Column order (Read-only)

The query WHERE tenant_id = 7 AND created_at >= '2025-01-01' ORDER BY created_at runs often. Which index is better, (created_at, tenant_id) or (tenant_id, created_at)?

Show solution

(tenant_id, created_at). A B-tree is sorted by its first column, then by the second inside each first-column value. With an equality on tenant_id first, PostgreSQL jumps to that tenant's entries, which are already sorted by created_at, so the range condition and the ORDER BY are satisfied by one short scan with no sort. The opposite order would walk every date and filter tenants. The rule of thumb: equality columns first, then the range or sort column. See B-tree indexes.

11. Choose the index type (Read-only)

For each need, name the index type: (a) WHERE attrs @> '{"color":"red"}' on a jsonb column; (b) an append-only log of one billion rows searched by time range; (c) prevent overlapping tstzrange bookings.

Show solution

(a) GIN, which indexes the keys and values inside each document. (b) BRIN, a tiny index of min/max values per block range, which works because the data is physically ordered by time. (c) GiST, used by an EXCLUDE USING gist constraint, because range overlap is not a B-tree operation. See GIN and GiST and BRIN, hash and SP-GiST.

Transactions and design

12. Move money safely (Intermediate)

Two accounts, ana with 100 and ben with 50. Transfer 30 from ana to ben so that either both updates happen or neither does. Show the balances inside the transaction, undo it, then do it again and commit. Practices transactions.

SQL Exercise 12: Rollback and commit

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
UPDATE accounts SET balance = balance + 30 WHERE id = 2;
SELECT id, balance FROM accounts ORDER BY id;   -- 70 and 80
ROLLBACK;
SELECT id, balance FROM accounts ORDER BY id;   -- 100 and 50 again

BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
UPDATE accounts SET balance = balance + 30 WHERE id = 2;
COMMIT;
SELECT id, balance FROM accounts ORDER BY id;   -- 70 and 80, permanently

The CHECK (balance >= 0) constraint is a second line of defence: if the sender lacked the money, the first UPDATE would fail, and in PostgreSQL the whole transaction would then be in an aborted state until you ROLLBACK. Never calculate "can they afford it?" in the application alone; let the database enforce it.

13. Two sessions, one seat (Read-only)

Two sessions run SELECT count(*) FROM bookings WHERE show = 5, each sees 99 of 100 seats taken, and each inserts a booking for the last seat. Both commit. Which isolation levels allow this, and how do you prevent it?

Show solution

This is write skew: both transactions read the same data, then write different rows based on what they read. It is allowed under Read Committed and Repeatable Read; only Serializable detects it and aborts one transaction with serialization_failure (SQLSTATE 40001), which the application must retry. Other fixes are to lock a parent row with SELECT ... FOR UPDATE before counting, to keep a counter row that both sessions must update (so they conflict), or to model each seat as a row with a unique constraint. See isolation levels and locking.

14. Overlapping bookings (Advanced)

A booking table holds room_id, starts and ends. Write a query that finds every pair of bookings of the same room that overlap, using only ids in the result. Two bookings overlap when each starts before the other ends. Practices exclusion constraints.

SQL Exercise 14: Find overlaps

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
SELECT a.id AS first, b.id AS second
FROM booking a
JOIN booking b
  ON a.room_id = b.room_id
 AND a.id < b.id
 AND a.starts < b.ends
 AND b.starts < a.ends;

a.id < b.id stops every pair from appearing twice and a booking from matching itself. Using strict < means a booking that starts exactly when another ends does not count as an overlap. In PostgreSQL you would prevent overlaps instead of finding them: EXCLUDE USING gist (room_id WITH =, tstzrange(starts, ends) WITH &&) rejects the second booking at insert time, even under concurrency.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In exercise 2, why must a leading ORDER BY column match the DISTINCT ON expression in PostgreSQL?

    DISTINCT ON picks the first row per distinct value according to the ORDER BY. If the sort did not begin with the group expression, rows of one group would not be adjacent and PostgreSQL rejects the query.
  2. 2. Which index suits an equality on tenant_id plus a range and sort on created_at?

    Equality columns go first, then the range or sort column. The leading equality narrows the scan to one sorted run, so no separate sort is needed.
  3. 3. What does NOT EXISTS handle better than NOT IN (subquery)?

    x NOT IN (..., NULL) evaluates to unknown for every x that is not in the list, so no rows are returned. NOT EXISTS has no such trap.
  4. 4. Which isolation level is needed to stop write skew in PostgreSQL?

    Only Serializable (SSI) detects the read-write dependency cycle behind write skew and aborts one transaction. The application must retry it.
  5. 5. Why does an upsert with ON CONFLICT beat SELECT-then-INSERT-or-UPDATE?

    The conflict check and the write happen together. The SELECT-first pattern leaves a gap in which another session can insert the same key.

Summary

  • Join first, then group; use FILTER for several conditional counts in one pass and NOT EXISTS for "none of".
  • Top-N per group is ROW_NUMBER() portably and DISTINCT ON in PostgreSQL.
  • Store money as numeric or integer cents, never as a float, and remember that timestamptz is an instant whose display depends on the session zone.
  • INSERT ... ON CONFLICT ... RETURNING is the atomic way to add-or-update and read the result back.
  • Index design: equality columns first, partial indexes for rare states, GIN for jsonb, BRIN for ordered append-only data, GiST for ranges.
  • Wrap multi-step changes in a transaction and let constraints and isolation levels guard the rules; retry on serialization_failure.
  • Prevent bad data with constraints (such as exclusion constraints) instead of only searching for it afterwards.

Next: PostgreSQL Interview Questions, concise answers to the questions that come up again and again.