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.
Simplified's videos table holds each video's chapter, and its length in seconds.
Can you get each number from Postgres, in one query?
Each step asks its question in plain words. You pick how to answer it.
The first number is how many videos chapter 1 has.
Send back that one number.
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.
The team wants to know how long chapter 3 takes to watch.
Send back how many seconds its videos run for, all together.
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.
Now the team wants to know how many videos each chapter has.
Send back each chapter, with how many rows of videos it has.
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.
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.
Each step passes once its query sends back the right rows. Then mark the lab complete to unlock the next lesson.