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.
Simplified keeps its lessons in lessons, and each lesson's videos in videos.
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.
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.
The lookup sent back 3.
Add a row to videos: 3 as its lesson_id, the title Ties, and 130 seconds.
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.
The Courses page lists every video under its lesson's title.
Send back videos.title and lessons.title, for every video.
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.
The team asks how many videos the lesson called Reading data has.
Send back that one number, finding the lesson by its title.
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.
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.
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.