lesson 63 Database engineering · advanced

Views: reusable query definitions

Define a reusable query as a view.

the idea

A view is a stored query with a name. Selecting from it runs the query against the current data, so it always reflects the latest rows. Views give a stable, reusable definition (for example, “net sales per customer”) without copying data.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Create a view and count from it. sale_lines is a saved join of order_items and orders filtered to sales. COUNT(*) runs that definition against whatever rows exist now.

   step-1.sql Run

   sql›

   CREATE VIEW sale_lines AS
   SELECT i.*, o.ordered_at, o.customer_id
   FROM order_items i
   JOIN orders o ON o.order_id = i.order_id
   WHERE o.order_type = 'sale';
   SELECT COUNT(*) AS sale_lines
   FROM sale_lines;

2. 2

   See a view refresh with new data. The view does not store results—it re-queries. When order 1 becomes a cancellation, the cancellation count changes immediately.

   step-2.sql Run

   sql›

   CREATE VIEW cancellations AS
   SELECT order_id
   FROM orders
   WHERE order_type = 'cancellation';
   UPDATE orders
   SET order_type = 'cancellation'
   WHERE order_id = 1;
   SELECT COUNT(*) AS cancellation_orders
   FROM cancellations;

3. 3

   Put it together. The lesson's pattern in one query.the pattern

   example.sql Run

   sql›

   CREATE VIEW sale_lines AS
   SELECT i.*, o.ordered_at, o.customer_id
   FROM order_items i
   JOIN orders o ON o.order_id = i.order_id
   WHERE o.order_type = 'sale';
   SELECT COUNT(*)
   FROM sale_lines;

common mistake

A view does not store results; it is not a snapshot and does not make the underlying query faster.