Simplified
Simplified

Chapter totals

YOU
Lab 4

Chapter totals

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

Answer each of the team's questions with one query, and let Postgres do the counting.

Scenario

Simplified's team wants to know how many videos each chapter has, and how long it takes to watch. Each number is one query, and you write it.

Time
About 10 min
Steps
6
Where
Your browser

What this lab tests

  • 3.7 Counting: One number from millions of rows, Adding up a column, Finding the average, One count for each group
  • 3.3 Comparing Values: Not equal to

Simplified's videos table holds each video's chapter, and its length in seconds.

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

Can you get each number from Postgres, in one query?

Each step asks its question in plain words. You pick how to answer it.

PostgreSQLStarting in your browser…
1

Step 1: How many videos in chapter 1?

The first number is how many videos chapter 1 has.

Send back that one number.

TestsOne number from millions of rows3.7 Counting
SQLYour query
Connecting…
1
2
3
⌘ / Ctrl Enter to run
2

Step 2: Fix the assistant's query

The team asks how many videos run for more than 100 seconds.

An assistant wrote this query. It runs, and it sends back a number.

Run it, work out why that number is wrong, then fix it.

TestsOne number from millions of rows3.7 CountingAdding up a column3.7 Counting
Pass step 1 to open this one.
3

Step 3: How long is chapter 3?

The team wants to know how long chapter 3 takes to watch.

Send back how many seconds its videos run for, all together.

TestsAdding up a column3.7 Counting
Pass step 2 to open this one.
4

Step 4: A typical video

Chapter 1 is the oldest, so the team leaves it out of this one.

Send back the average length, in seconds, of every video that isn't in chapter 1.

TestsFinding the average3.7 CountingNot equal to3.3 Comparing Values
Pass step 3 to open this one.
5

Step 5: Videos in each chapter

Now the team wants to know how many videos each chapter has.

Send back each chapter, with how many rows of videos it has.

TestsOne count for each group3.7 Counting
Pass step 4 to open this one.
6

Step 6: Seconds in each chapter

Last, the team wants to know how long each chapter takes to watch.

Predict how many rows come back, then send back each chapter, with the total seconds of its videos.

TestsAdding up a column3.7 CountingOne count for each group3.7 Counting
Pass step 5 to open this one.

column "videos.chapter" must appear in the GROUP BY clause or be used in an aggregate function

The query names chapter beside a count or a total, but never makes a group for each chapter.

End it with GROUP BY chapter.

syntax error at or near "FROM", after writing COUNT *

The star goes in brackets: COUNT(*).

Not yet: one number came back, but not the right one

Check the WHERE first: which rows pass it?

Then check what the step asks for: how many rows, their total, or their average.

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.