Simplified
Simplified

Videos and their lessons

YOU
Lab 7

Videos and their lessons

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

Save a video under the right lesson, and read across two tables in one query.

Scenario

Each of Simplified's videos belongs to a lesson. You add new videos to their lessons, then write the queries that read the two tables together.

Time
About 15 min
Steps
7
Where
Your browser

What this lab tests

  • 4.3 Two Tables: Pointing at a row that exists, Whose id goes in the new row?
  • 4.4 Reading Across Tables: Which table's id do you mean?, Reading two tables side by side, Filtering the joined rows
  • 3.7 Counting: One number from millions of rows

Simplified keeps its lessons in lessons, and each lesson's videos in videos.

videosidlesson_idtitleseconds11Servers14021Memory and disk9531Queries and results12042The primary key11052Generated ids9562The schema8073Text in quotes15083The empty result100lessonsidtitle1What a database is2Tables3Reading data4Changing data

Can you put each video beside the lesson it is in?

Each row of videos points at its lesson with lesson_id, a foreign key to lessons.

PostgreSQLStarting in your browser…
1

Step 1: Look up the lesson's id

The team adds a video called Ties to the lesson called Reading data.

The new row needs that lesson's id, and the team has only its title.

Send back the id of the lesson with that title.

TestsWhose id goes in the new row?4.3 Two Tables
SQLYour query
Connecting…
1
2
3
⌘ / Ctrl Enter to run
2

Step 2: Save the new video

The lookup sent back 3.

Add a row to videos: 3 as its lesson_id, the title Ties, and 130 seconds.

TestsWhose id goes in the new row?4.3 Two Tables
Pass step 1 to open this one.
3

Step 3: Fix the assistant's INSERT

Next comes Several rows at once, 90 seconds long, in the lesson called Changing data.

An assistant wrote this INSERT for it.

Run it, read the error, then fix it.

TestsPointing at a row that exists4.3 Two TablesWhose id goes in the new row?4.3 Two Tables
Pass step 2 to open this one.
4

Step 4: Each video beside its lesson

The Courses page lists every video under its lesson's title.

Send back videos.title and lessons.title, for every video.

TestsWhich table's id do you mean?4.4 Reading Across TablesReading two tables side by side4.4 Reading Across Tables
Pass step 3 to open this one.
5

Step 5: One lesson's videos

A learner opens the lesson called Tables.

Send back lessons.title and videos.title, for every video in that lesson.

Find the lesson by its title.

TestsWhich table's id do you mean?4.4 Reading Across TablesReading two tables side by side4.4 Reading Across TablesFiltering the joined rows4.4 Reading Across Tables
Pass step 4 to open this one.
6

Step 6: Count a lesson's videos

The team asks how many videos the lesson called Reading data has.

Send back that one number, finding the lesson by its title.

TestsFiltering the joined rows4.4 Reading Across TablesOne number from millions of rows3.7 Counting
Pass step 5 to open this one.
7

Step 7: Fix the assistant's join

The team wants every video longer than 120 seconds, beside its lesson's title.

An assistant wrote this query. It runs, but two of the three videos are missing.

Run it, work out why, then fix it.

TestsReading two tables side by side4.4 Reading Across TablesFiltering the joined rows4.4 Reading Across Tables
Pass step 6 to open this one.

column reference "title" is ambiguous

Both tables have a title column, so Postgres can't tell which one you mean.

Write the table's name first: videos.title or lessons.title.

insert or update on table "videos" violates foreign key constraint "videos_lesson_id_fkey"

The lesson_id matches no id in lessons, so Postgres refuses the row.

Use the id of a lesson that is in lessons.

missing FROM-clause entry for table "lessons"

The query names lessons, but never reads it.

Add it after JOIN: FROM videos JOIN lessons.

Finish the lab

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

0 of 7 steps done
Assistant

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