CREATE VIEW creates a named database view from a query, letting application code reuse a report as a simple table-like query. The view can be placed in a schema; without an explicit schema, it uses the current schema. Its query runs whenever the view is called, because the result is not physically materialized. section 2.7
Create and filter a sales summary
CREATE VIEW sales.product_sales_summary AS SELECT c.name AS product_name, c.category, SUM(oi.quantity) AS total_quantity_sold, SUM(oi.quantity * oi.price) AS total_revenue FROM products.catalog c LEFT JOIN sales.order_items oi ON c.id = oi.product_id GROUP BY c.id ORDER BY total_quantity_sold DESC, total_revenue DESC; SELECT * FROM sales.product_sales_summary; SELECT * FROM sales.product_sales_summary WHERE category = 'coffee'; The first query returns the complete tabular report; the second narrows it to coffee products. section 2.7
Listing 2.28 Creating a view for the sales report summary
CREATE VIEW sales.product_sales_summary AS
SELECT
c.name AS product_name,
c.category,
SUM(oi.quantity) AS total_quantity_sold,
SUM(oi.quantity * oi.price) AS total_revenue
FROM products.catalog c
LEFT JOIN sales.order_items oi ON c.id = oi.product_id
GROUP BY c.id
ORDER BY total_quantity_sold DESC, total_revenue DESC;
Inspect the complete SQL definition for the product sales summary view. section 2.7
