AI Hints
Homeโ€บSQL Tutorialโ€บRules, Safety, and Speed
SQL ยท Lesson 76 of 126

Views and ALTER TABLE

Saved queries and new columns.

โฑ 2 min readโ–ถ Video at 1:46:57๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Views and ALTER TABLE
From the video: Views and ALTER 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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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
Next up: Indexes โ€” making queries fast