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.
- 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.
- 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).
- 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.
- 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.