Simplified
Simplified

Publish a video

YOU
Lab 5

Publish a video

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

Add new videos to the videos table, and get back what Postgres filled in for them.

Scenario

Simplified's team is publishing the videos of its next chapters. You write the INSERT its server sends for each of them.

Time
About 10 min
Steps
5
Where
Your browser

What this lab tests

  • 4.1 Adding Rows: Adding a new row, Three rows in one go, Which id did Postgres pick?

Simplified's videos table holds eight videos so far, in chapters 1 to 3.

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

Can you add the next ones, the way Simplified's server does?

These queries change the table. After each run, you'll see videos as your query left it.

PostgreSQLStarting in your browser…
1

Step 1: A new video

The team adds a video called Ties, to chapter 3. It hasn't been recorded yet.

Add its row to videos.

Give only its title and chapter. Postgres fills in the rest.

TestsAdding a new row4.1 Adding Rows
SQLYour query
Connecting…
1
2
3
⌘ / Ctrl Enter to run
2

Step 2: What Postgres filled in

Your INSERT never named id or seconds, so Postgres filled them in.

Send back the id, title and seconds of the row whose title is Ties.

TestsAdding a new row4.1 Adding Rows
Pass step 1 to open this one.
3

Step 3: Three videos at once

Chapter 4 opens with three recorded videos: Several rows at once, The missing WHERE and What a write reports.

They run for 90, 105 and 85 seconds.

Add all three rows with one INSERT, in that order.

TestsThree rows in one go4.1 Adding Rows
Pass step 2 to open this one.
4

Step 4: Get the new id back

One more video joins chapter 4: The foreign key, not recorded yet.

Simplified's server needs the new row's id at once, to link to it.

Predict that id. Then add its row, and have the same query send its id back.

TestsThree rows in one go4.1 Adding RowsWhich id did Postgres pick?4.1 Adding Rows
Pass step 3 to open this one.
5

Step 5: Fix the assistant's INSERT

The next one is called One-to-many. It's in chapter 4, and runs for 130 seconds.

An assistant wrote this INSERT for it. Postgres runs it without an error.

Run it, look at videos after it, then fix it.

TestsAdding a new row4.1 Adding Rows
Pass step 4 to open this one.

null value in column "title" of relation "videos" violates not-null constraint

The INSERT gave no title, and title is NOT NULL.

Name title in the brackets after videos, and give its value.

cannot insert a non-DEFAULT value into column "id"

Leave id out of the INSERT. Postgres generates it.

INSERT has more expressions than target columns

VALUES gives more values than the columns you named.

Give one value for each column, in the same order.

invalid input syntax for type integer: "Several rows at once"

A title landed in a number column, so the values are out of order.

Give them in the order the columns are named.

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 5 steps done
Assistant

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