Views
In short: A view is a query saved under a name that you use like a table, but it stores no data of its own.
Problem
The same calculation everywhere
How much is an order worth? There is no sum in orders. It is calculated from the order's rows:
This calculation is needed for the daily report, for the customer statement, for the list of unpaid orders. Each time it is written again. If one day a delivery fee or a discount is added, it has to be fixed in ten places. And one of them will be forgotten.
The idea
CREATE VIEW
CREATE VIEW name AS SELECT … saves a query under a name. This is a view. After that the view is read like a table:
The calculation is written once, in the view. The reports only say FROM order_totals.
The idea
Like a table
In SELECT a view is used like any table: with WHERE, ORDER BY, JOIN. In this field order_totals has already been made. Here are the new orders, with the customer and the value:
The query is shorter and clearer: “the order, the customer, the value”. How the value is calculated is hidden in the view.
Try it
The big orders
The view order_totals is ready. Which orders are over 100 €? Show the number and the value.
Solution
SELECT order_id, total FROM order_totals WHERE total > 100;SELECT order_id, total FROM order_totals WHERE total > 100;. Three orders: 13 (102.10 €), 23 (104 €) and 56 (118.20 €).
See it
A view holds no data
The customer of order 1 calls: they want 5 packs of white cheese, not 4. Run the query and look at both results:
Before it is 31.60 €, after it is 39.50 €. Nobody touched the view. The view does not keep the values, only the query. Every read runs it again on the tables, so a view cannot go stale.
That is also why you cannot write into a view:
The data is changed in the tables the view reads from.
More precisely, if you are curious
PostgreSQL also has CREATE MATERIALIZED VIEW: a view that keeps its result like a table. It is fast to read, but it goes stale and is refreshed with REFRESH MATERIALIZED VIEW. It is used for heavy reports that need not be accurate to the second. SQLite has no such view.
Try it
Sales by product
Make a view product_sales: for each product (product_id), how many pieces were ordered (sold), leaving out the rows of cancelled orders.
Solution
CREATE VIEW product_sales AS
SELECT oi.product_id, SUM(oi.quantity) AS sold
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE o.status <> 'cancelled'
GROUP BY oi.product_id;CREATE VIEW product_sales AS SELECT oi.product_id, SUM(oi.quantity) AS sold FROM order_items oi JOIN orders o ON o.id = oi.order_id WHERE o.status <> 'cancelled' GROUP BY oi.product_id;. Without the JOIN to orders, the order's status is not visible.
The idea
DROP VIEW
A view is removed with DROP VIEW. DROP TABLE does not work:
The tables the view reads from are not touched. You remove only the saved query.
More precisely, if you are curious
To change a view in SQLite, you remove it with DROP VIEW and make it again with CREATE VIEW. PostgreSQL and MySQL have CREATE OR REPLACE VIEW, and SQLite does not:
It works the other way too: a column that a view uses cannot be removed with DROP COLUMN (the previous lesson mentioned it).
Exercise
Two reports
1. The owner wants to send thank-you cards to the three customers who have ordered for the most money.
Using the view order_totals, show the customer's name and how much they ordered in total (spent), leaving out cancelled orders. Only the top three, from most to least.
Solution
SELECT c.name, ROUND(SUM(t.total), 2) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_totals t ON t.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY c.id, c.name
ORDER BY spent DESC
LIMIT 3;JOIN of customers, orders and order_totals, WHERE o.status <> 'cancelled', GROUP BY the customer, ORDER BY spent DESC LIMIT 3. Maria Ivanova (375.70 €), Martin Kostov (216.80 €) and Nikolay Stoyanov (143.50 €).
2. In the morning the warehouse looks at which orders are waiting.
Make a view open_orders with the order number, the customer's name and the order's value, only for new and paid orders (new, paid). Use order_totals.
Solution
CREATE VIEW open_orders AS
SELECT o.id, c.name, t.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_totals t ON t.order_id = o.id
WHERE o.status IN ('new', 'paid');CREATE VIEW open_orders AS SELECT o.id, c.name, t.total FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_totals t ON t.order_id = o.id WHERE o.status IN ('new', 'paid');. 23 orders. A view can read from another view.
Test