Create View - SQL Programming

A view stores a named SELECT statement. Later queries can read the view like a table while the data still comes from the base table.

Program

Play the script to create an order summary view and filter it with a selectable minimum total.

create_view.sql

CREATE TABLE orders (region TEXT, amount INTEGER);
INSERT INTO orders VALUES ('east', 12), ('east', 9), ('west', 18);
CREATE VIEW region_totals AS SELECT region, SUM(amount) AS total FROM orders GROUP BY region;
WITH params(min_total) AS (VALUES ()) SELECT region, total FROM region_totals WHERE total >= (SELECT min_total FROM params) ORDER BY region;

CREATE TABLE orders (region TEXT, amount INTEGER);
INSERT INTO orders VALUES ('east', 12), ('east', 9), ('west', 18);
CREATE VIEW region_totals AS SELECT region, SUM(amount) AS total FROM orders GROUP BY region;
WITH params(min_total) AS (VALUES ()) SELECT region, total FROM region_totals WHERE total >= (SELECT min_total FROM params) ORDER BY region;

CREATE TABLE orders (region TEXT, amount INTEGER);
INSERT INTO orders VALUES ('east', 12), ('east', 9), ('west', 18);
CREATE VIEW region_totals AS SELECT region, SUM(amount) AS total FROM orders GROUP BY region;
WITH params(min_total) AS (VALUES ()) SELECT region, total FROM region_totals WHERE total >= (SELECT min_total FROM params) ORDER BY region;

CREATE VIEW `CREATE VIEW name AS SELECT ...` gives a query a reusable name.

view query Reading from `region_totals` evaluates the saved SELECT over current table data.

parameter CTE `params` holds the selectable minimum total used by the filter.