What is a view, and why would you create one?
A view is a query that you give a name to, so that you can use it like a table.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL,
city VARCHAR(80) NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
placed_on TEXT NOT NULL
);
-- A view that joins two tables.
CREATE VIEW customer_totals AS
SELECT
c.id AS customer_id,
c.name,
c.city,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.city;
So why would you create one? Four reasons.
The first one is that the logic lives in one place. If the rule for "a big spender" changes, you change it in one place and every report that uses the view is right again.
The second one is that the query gets shorter. A report that joins four tables and groups by two of them is a mouthful every time, and the view turns it into a name.
The third one is about permissions. You can give a reporting tool the right to read the view and no right to read the tables underneath, so it cannot see anything you did not mean it to see.
The fourth one is that the view is a contract. When the tables change and the view is updated to match, every program that uses the view keeps working without a change.
The next two questions in this topic look at a bigger view and at what a view really is.