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.

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

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

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

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