Simplified
Simplified

Search and rank

YOU
Lab 3

Search and rank

Write each query here, and run it on a real Postgres database in your browser.

Find videos by a pattern, and send them back in the order the team asks for.

Scenario

Simplified's team looks its videos up by their titles, and lists them in order. You write both kinds of query.

Time
About 10 min
Steps
6
Where
Your browser

What this lab tests

  • 3.5 Matching Text: How a search box finds a name, Exactly one more character, When capitals shouldn't matter
  • 3.6 Sorting: Putting rows in order, Who comes first in a tie?, Just the first few rows

Simplified's videos table holds eight videos.

videosidtitlechapterseconds1Servers11402Memory and disk1953Queries and results11204The primary key21105Generated ids2956The schema2807Text in quotes31508The empty result3100

Can you find the right videos, and send them back in the right order?

Postgres promises no order until your query asks for one.

PostgreSQLStarting in your browser…
1

Step 1: Titles that start with The

The team remembers only that some titles start with the word The.

Send back every title that starts with The.

TestsHow a search box finds a name3.5 Matching Text
SQLYour query
Connecting…
1
2
3
⌘ / Ctrl Enter to run
2

Step 2: An e as the second letter

Someone on the team remembers a title whose second letter is e.

Send back every title that fits.

TestsExactly one more character3.5 Matching Text
Pass step 1 to open this one.
3

Step 3: Fix the assistant's search

The team wants every title with qu in it, capital or small.

An assistant wrote this query. It runs, but one title is missing.

Run it, work out which one, then fix it.

TestsHow a search box finds a name3.5 Matching TextWhen capitals shouldn't matter3.5 Matching Text
Pass step 2 to open this one.
4

Step 4: Newest first

The team lists its videos with the newest at the top.

A newer video has a higher id.

Send back every video's id and title, highest id first.

TestsPutting rows in order3.6 Sorting
Pass step 3 to open this one.
5

Step 5: Break the tie

Now the team lists every video, longest first.

Two videos tie on 95 seconds, so break the tie by title, in alphabetical order.

Send back title and seconds.

TestsPutting rows in order3.6 SortingWho comes first in a tie?3.6 Sorting
Pass step 4 to open this one.
6

Step 6: Only the two shortest

The team wants only two videos: the two shortest.

Two videos tie for second place, so the one in the earlier chapter goes first.

Send back their title and seconds, shortest first.

TestsPutting rows in order3.6 SortingWho comes first in a tie?3.6 SortingJust the first few rows3.6 Sorting
Pass step 5 to open this one.

A search sends back fewer rows than it should

LIKE treats a capital and a small letter as different.

Check the capitals in your pattern, or use ILIKE to ignore case.

syntax error at or near "ORDER"

LIMIT goes at the very end, after ORDER BY.

Not yet: these are the right rows, but not in the right order

Check which column ORDER BY sorts by, and whether it needs DESC.

If two rows tie, add a second column after a comma.

Finish the lab

Each step passes once its query sends back the right rows. Then mark the lab complete to unlock the next lesson.

0 of 6 steps done
Assistant

Highlight lesson text and click Ask AI, or type a question below.