Postgres in depth · Chapter 5 of 65

The catalogue page

By the end of this chapter you can read rows from a table, choose and rename columns, sort them (ties and missing values included), take one page at a time, remove duplicates, and say in which order Postgres runs the parts of a query. 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 courses are in the database, and Kabir wants the catalogue page live: a price list, pages of five, the list of teachers for a filter menu, and a "new this month" strip. Arjun wants to look at the students the same way.

So far you have put rows in and asked the server about itself. Reading a table back is a SELECT ... FROM, and the first thing it teaches you is chapter 3's warning: without being asked, Postgres returns rows in no particular order, so "the newest three" or "page two" mean nothing yet.

This chapter queries the tables built in chapters 3 and 4, with all of LCO's first courses and students in them. Run lab load day-one in the terminal to get them in your own database (it replaces what is there).

What you build: 4 tasks

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

  1. Task 1

    price-list.sql: the price list

    Kabir wants every course on the price list, cheapest first.

    Return two columns: course (the course's title) and price. One row per course, all 14 of them.

    Order: by price from lowest to highest; courses with the same price by title, A to Z. The course with no price yet comes last.

  2. Task 2

    page-two.sql: page two of the catalogue

    The catalogue lists courses by title, A to Z, five per page. Return page two: the 6th to the 10th course in that order.

    Return two columns: title and level.

    Order: by title, A to Z (titles are all different).

  3. Task 3

    instructors.sql: the teachers menu

    The catalogue's filter menu lists each instructor once.

    Return one column: instructor. One row per instructor, no repeats.

    Order: by instructor, A to Z.

  4. Task 4 · interview question

    Interview: newest.sql, the newest three courses

    The home page shows the three most recently launched courses.

    Return two columns: title and launched_on. Exactly three rows.

    Order: by launched_on, newest first (the dates are all different).

    Careful: courses that have not launched have no launched_on at all, and they must not take any of the three places.

What you will learn

  • SELECT ... FROM: columns from a table, *, and AS for names.
  • ORDER BY: ascending and descending, tie-breakers, and where NULLs go.
  • LIMIT and OFFSET: the first rows, and the next page.
  • DISTINCT: each value once.
  • The logical order of a query: why some names work in one clause and not another.

More in Day one: an empty server

Build The catalogue page 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.