Postgres in depth · Chapter 6 of 65
Finding the right course
By the end of this chapter you can keep only the rows you want: compare values, combine conditions with AND, OR and NOT (with the right brackets), and match lists, ranges and text patterns. 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
The catalogue now shows everything, and that is the problem. Kabir wants an "intermediate courses" shelf and a "launched in January" strip. The search box has to find "script" in titles however people type it. Sana needs the courses still in draft for two teachers who keep asking when theirs go live.
Chapter 5's tools sort and cut a result, but LIMIT keeps the first rows, not the right ones. You need a condition that every row is tested against: WHERE.
As in chapter 5, run lab load day-one to have LCO's courses and students in your own database. The examples use students; the tasks use courses.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
intermediate.sql: the intermediate shelf
Kabir's new shelf shows the published courses whose level is intermediate, written exactly in lowercase.
Return two columns: title and price.
Order: by price, lowest first; the same price by title, A to Z.
Careful: a draft (not published) intermediate course must not appear.
- Task 2
january.sql: launched in January
The "launched in January" strip shows every course launched between 2026-01-01 and 2026-01-31, both days included.
Return two columns: title and launched_on.
Order: by launched_on, earliest first (the dates are all different).
- Task 3
search.sql: the search box
A visitor types script into the search box. Return every course whose title contains script anywhere, in any mix of capitals and small letters.
Return one column: title.
Order: by title, A to Z.
Careful: the titles spell it with a capital S.
- Task 4 · interview question
Interview: waiting-drafts.sql, AND meets OR
Sana needs the courses that are still drafts (not published) and whose instructor is Rahul Iyer or Siddharth Jain.
Return two columns: title and instructor. Any order.
Careful: a course by Rahul Iyer that is already published must not appear.
What you will learn
- intermediate.sql: the intermediate shelf
- january.sql: launched in January
- search.sql: the search box
- Interview: waiting-drafts.sql, AND meets OR
More in Day one: an empty server
Build "Finding the right course" 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.