sql · level 3 · unit 4 · lesson 4
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
A is a saved query you can read like a table.
Some questions get asked every day. Save the query with and the database remembers it. From then on, SELECT * FROM name runs it for you. Unlike a CTE, a view lasts beyond one statement.
A view stores the query, not the rows. Every time you read it, it runs again on the latest data, so it is never out of date.
Views give a whole team one agreed answer, such as one way of adding up spending. DROP VIEW name removes one.
CREATE VIEW customer_spend AS
SELECT c.name, COUNT(o.id) AS orders, COALESCE(SUM(o.total), 0) AS spent
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id;
SELECT name, spent FROM customer_spend ORDER BY spent DESC, name;Put ORDER BY in the query that reads the view, not inside the view itself.
guess first
A new order arrives after the view is made. What now?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
CREATE VIEW big_orders AS
SELECT id, total FROM orders WHERE total >= 40;
INSERT INTO orders (customer_id, day, total) VALUES (4, '2024-05-07', 50);
SELECT COUNT(*) AS big FROM big_orders;your turn
Your turn. Save the daily takings.
Fill the first blank so the statement saves a view called daily. Then make the last query read from that view to find the best day.
result
Your answer appears here as a table.
fix it
Kit cannot join through the view. Fix it!
result
Your answer appears here as a table.
- Read the error. What kind of thing is
leeds_customers? - A view holds no rows of its own, so new rows go into the real table underneath it.
- Insert into customers with the columns name and city, giving Kit the city 'Leeds', then press run.
nailed it
you saved a query for everyone. :D
CREATE VIEW name AS SELECT ...saves a query with a name.- A view stores the query, so it is always up to date.
- Change the real tables, not the view.
new words in your collection
- view
- CREATE VIEW
This lesson isn’t saved. A free account remembers every lesson you finish.