CREATE VIEW & reusable logic

Save a query as a view

Lesson 18 of 20 · 10 min · Module 5: Change data safely

The order total calculation has appeared in many lessons. A view stores such a query under a name, so every report uses the same, tested definition instead of copying it.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Create a view
Query a view like a table
Know what views do and do not store

The idea

CREATE VIEW name AS SELECT … saves the query. Afterwards you can select from the view like a table, and it always reflects the current data, because the query runs each time. Views do not store a copy of the rows.

CREATE VIEW kitchen_products AS
  SELECT name, price_cents FROM products WHERE category = 'Kitchen';
SELECT * FROM kitchen_products;

01One definition, many reports

Every report that needs order totals can now say FROM order_totals. If the calculation ever changes, for example to include shipping, you change it in one place.

02Always current

A view is a saved query, not a saved result. Insert a new order and the view includes it immediately.

CREATE VIEW n AS SELECT COUNT(*) AS c FROM products;
INSERT INTO products (name, category, price_cents) VALUES ('Pen', 'Office', 150);
SELECT c FROM n;

Output

03Views can hide columns

A view that leaves out sensitive columns, such as email addresses, lets reporting tools read the data they need and nothing more.

Your turn

Create a view order_totals with one row per order: order_id, customer_id, ordered_at, status, and total_cents. Then use the view to return each status with its number of orders as orders and the sum of their totals as total_cents, sorted by status.

What the checks look for

Summarises orders by status through the view
The view order_totals exists
The view has one row per order

Try it in the editor →

Take this with you

Views give a name to logic you use again and again.

Go a little further

Create a view customer_summary on top of order_totals and use it in the customer value report.