Views and ALTER TABLE
Saved queries and new columns.
Remember the four table join that works out each order's total? Typing it every time is a pain. A view saves a query under a name, so you can use it just like a table.

- That long join, again?
- Save it with a name
- Use it like a table
CREATE VIEW
Create view, a name, as, and then any select. Now order totals behaves like a table. Below, we use it to get the average order value for each status. Run it. Delivered orders average about thirty two dollars. Returned orders are a little smaller, at about twenty seven fifty.
CREATE VIEW order_totals AS
SELECT o.order_id, o.status,
ROUND(SUM(oi.quantity * b.price), 2) AS total
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN books AS b ON b.book_id = oi.book_id
GROUP BY o.order_id;
SELECT status, COUNT(*) AS orders, ROUND(AVG(total), 2) AS avg_total
FROM order_totals
GROUP BY status
ORDER BY orders DESC;โโโโโโโโโโโโโฌโโโโโโโโโฌโโโโโโโโโโโโ โ status โ orders โ avg_total โ โโโโโโโโโโโโโผโโโโโโโโโผโโโโโโโโโโโโค โ delivered โ 1265 โ 31.99 โ โ cancelled โ 140 โ 32.33 โ โ shipped โ 130 โ 29.44 โ โ returned โ 63 โ 27.57 โ โโโโโโโโโโโโโดโโโโโโโโโดโโโโโโโโโโโโ
ALTER TABLE
Tables can change after they're made. Helen is starting a newsletter. Alter table, add column, adds a new column to every row, with a default of zero. Then we sign up Irish customers who have an email. Run it. Forty seven customers are now on the newsletter.
ALTER TABLE customers
ADD COLUMN newsletter INTEGER DEFAULT 0;
UPDATE customers
SET newsletter = 1
WHERE country = 'Ireland' AND email IS NOT NULL;
SELECT newsletter, COUNT(*) AS customers
FROM customers
GROUP BY newsletter;โโโโโโโโโโโโโโฌโโโโโโโโโโโโ โ newsletter โ customers โ โโโโโโโโโโโโโโผโโโโโโโโโโโโค โ 0 โ 453 โ โ 1 โ 47 โ โโโโโโโโโโโโโโดโโโโโโโโโโโโ
Summary
A view is a saved query you can use like a table. Alter table adds columns to an existing table. Next, making queries fast.
Key takeaways- CREATE VIEW name AS SELECT
- Use it like a table
- ALTER TABLE ... ADD COLUMN