Scenario design
- SQL course
- Expert
- Lesson 19/45
- 5 min
Set up the slow-report scenario and write skeleton queries.
Scenario design
The performance project starts here. In the first step you design the orders table with id, customer_id, amount, created_at columns. This table feeds every following lesson.
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT,
amount NUMERIC,
created_at TIMESTAMP
);The base table is the performance scenario.
The table orders is created.
Scenario design — details
This table is the base for performance experiments. Each lesson takes the previous solution as its starting point: add an index, read the plan, rewrite the query, compare.
Practice Scenario design
Create the orders table with id SERIAL PRIMARY KEY, customer_id INT, amount NUMERIC, created_at TIMESTAMP.
Starter code
-- scenario table
CREATE TABLE ordersSample solution
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT,
amount NUMERIC,
created_at TIMESTAMP
);Expected output
CREATE TABLE
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Base table of the performance project?
- orders Correct answer
- users
- logs
- audit
Explanation: The orders table is the base of the performance project.
2 Primary key
id ____ PRIMARY KEYid SERIAL PRIMARY KEYExplanation: SERIAL generates an auto-incrementing number for the primary key.
3 Scenario design: match the terms
| Term | Meaning |
|---|---|
orders |
orders |
id |
id |
customer_id |
customer |
amount |
amount |