Lesson 19 of 20

In progress

Course progress

SQL

Views: naming a query

Save a query under a name so reports stay consistent, understand what a view does not do, and know when a materialised view is worth it.

A view is a stored query with a name. Selecting from it runs the query, which means the definition lives in one place instead of being copied into five reports.

What you will learn

  • Create and use a view.
  • Recognise what a view cannot do.
  • Decide between a view, a CTE and a materialised view.

A view in practice

SQL
Editable. Runs in your browser.





Press Run to see the output.

The rule "a paid order is one with status paid" now exists once. When the definition changes — say refunds move to their own table — every report that uses the view changes with it.

Views also document a domain term. paid_orders tells a reader what the business means by paid, which a WHERE buried in a dashboard query does not.

Views do not store rows

Selecting from a view runs the underlying query every time. There is no copy to go stale, and there is no copy to make it fast.

That has two consequences. First, a view over a slow query is still slow. Second, an index on the base table helps the view, but there is no such thing as an index on a view in SQLite.

Because the query is expanded at planning time, the engine can often push filters from your query into the view’s definition. SELECT * FROM paid_orders WHERE customer = 'Ana' can use an index on customer, even though the filter is outside the view.

Updating through a view

Simple views — one table, no aggregation, no DISTINCT — are updatable in many databases. SQLite does not support UPDATE on a view directly; use an INSTEAD OF trigger if you need that behaviour.

For anything with a join or an aggregate, treat the view as read-only. The database cannot know which base row an update to an averaged column should change.

A view for a join

SQL
Editable. Runs in your browser.





Press Run to see the output.

order_details turns a three-line join into a name. Reports read like questions about orders rather than about o and c.

Views versus CTEs

A CTE lives for one query. A view lives in the schema and can be used by any query, any report and any colleague.

Use a CTE when the step is internal to one query. Use a view when the step is a concept the organisation talks about: active subscriptions, overdue loans, paid orders.

Nesting views is allowed and worth doing sparingly. A view on a view on a view is hard to debug, and the query plan becomes hard to read. Two levels is usually enough; beyond that, a table or a scheduled summary is clearer.

Materialised views

A materialised view stores the result. Reads are fast because nothing is computed, and the data is as fresh as the last refresh.

SQLite has no materialised views. PostgreSQL and Oracle do; MySQL emulates them with a table and a scheduled refresh. The trade-off is always the same: speed at read time against staleness and refresh cost.

A practical pattern in SQLite is a summary table rebuilt on a schedule inside a transaction:

SQL
Editable. Runs in your browser.





Press Run to see the output.

The table is explicit, the refresh is visible in code, and nothing pretends to be automatic.

Naming and review

Prefix or suffix views so they are recognisable: v_paid_orders or paid_orders, applied consistently. A schema where views and tables share a naming style is confusing when someone tries to insert into a view.

Review a view like code: it is a query with a name, and the name is a promise about its contents. A view called active_users that includes deactivated accounts is worse than no view, because people trust the name.

Creating a view that selects * from a table. When the table gains a column, the view’s shape changes silently and downstream reports may break. List the columns.

Create a view of overdue orders — pending for more than seven days against a fixed reference date of 2025-07-10 — and use it to list each customer’s overdue amount.

Show solution
SQL
Editable. Runs in your browser.





Press Run to see the output.

Meera’s 2025-07-06 order is not yet seven days old on the reference date, so the view returns no rows. The fixed date keeps the example reproducible; in production the comparison uses date('now'), and the view definition stays the single source of the rule.

Recap

  • A view stores a query, not data, and runs it on every select.
  • Use views for shared definitions, CTEs for steps inside one query.
  • List columns explicitly, and use a summary table when you need stored results.

The capstone brings constraints, indexes, transactions and views together in a small library database.

Do views have permissions?
Most databases let you grant access to a view without granting the base tables, which is a common way to expose limited data.
Can a view reference another view?
Yes. Keep the chain short so the plan stays readable.

Progress is saved in this browser only. No account, no email.

Admin

About the author

Admin

Instructor and technical writer at tutorials.in

Writes beginner lessons for tutorials.in. Each lesson is checked against the language or tool documentation, and examples are run before publishing.

Reviewed by the editorial team before publishing. Last updated November 16, 2026.