The big idea: A view is a stored query given a name. Selecting from it runs the query underneath and returns the result.
No data of its own: It stores no data of its own — which is why it is always current, and why it costs nothing to keep.
CREATE VIEW ActiveStudents AS
SELECT studentID, name, yearGroup
FROM Student
WHERE enrolled = TRUE;
-- then used exactly like a table
SELECT * FROM ActiveStudents WHERE yearGroup = 12;Always up to date, by construction: Because a view holds no data, it cannot go stale. Insert a student and the next query against the view includes them — there is nothing to refresh.
Free preview
This is the free notes preview
You're reading the free notes. Aimnova Pro unlocks the full study experience — and you can try it with your first topic free to keep:
- FlashcardsLock in vocabulary and key terms with spaced repetition.
- Practice questionsAnswer exam-style questions and get instant AI marking.
- Mock exams & past-paper vaultSit full mocks and see exactly how examiners award marks.
- Personalised study planA daily plan built around your exam date and weak areas.
Start Studying Free Full access to Aimnova Pro · cancel anytime
Security
- Expose some columns, not the whole table
- A payroll view without the salary column
- Grant access to the view, not the table underneath
- The user cannot reach what the view does not select
Simplicity
- Hide a five-table join behind one name
- Users who cannot write SQL can still query it
- The complexity is written once and reviewed once
Consistency
- Everyone uses the same definition of "active student"
- Change the rule in one place and every report follows
- Without it, each report re-implements the rule slightly differently
More reasons the guide names
| Reason | What it means | Example |
|---|---|---|
| Data independence | The tables underneath can change while the view keeps the same columns, so programs using it still work | A Student table is split in two; the view joins them and reports never notice |
| Query simplification | A long query is written once; users just SELECT from the view | A three-table sales report becomes SELECT * FROM MonthlySales |
| Read-only or updatable | A view over one table can usually be updated; one with joins or aggregates is read-only | Updating StudentContacts changes Student; MonthlySales cannot be updated |
The security use is the strongest: Row and column restriction through a view is real access control: the user is granted rights on the view only and has no permission on the underlying table.
They cannot select a column the view does not expose, because as far as their permissions go it does not exist.
See how examiners mark answers
Answer exam-style questions with model answers. Learn exactly what earns marks and what doesn't.
Try Exam Vault FreeYour first topic is free to keep • No credit card required
| Virtual (standard) view | Materialised (snapshot) view | |
|---|---|---|
| Stores data | No — runs the query each time | Yes — the result is saved |
| Currency | Always current | Current to the last refresh |
| Read cost | Pays the query cost every time | Fast — it is already computed |
| Suits | Anything needing live data | Expensive aggregates read often |
A view does not make a slow query fast: It stores the query, not the answer. A view over a five-table join runs that join on every read.
Naming something does not optimise it — that is what a materialised view or an index is for.
Updating through a view is restricted: A simple view over one table can often be updated. One containing a join, GROUP BY or aggregate usually cannot — because there is no single underlying row for the change to apply to.
Say that if asked whether a view is writable.
How this is tested — you must name what a view stores — nothing — and justify one from security or consistency. It comes up two ways:
Paper 1 Section A
- Describe what a view is, 3-4 marks
- State one reason to use a view
- Write a CREATE VIEW statement
Paper 1 Section B — case study
- Justify a view for a scenario
- Explain why a view does not improve performance
The classic trap: Saying a view "stores a copy of the data". A standard view stores only the query — which is exactly why it is always current and why it does not run any faster.
IB-style questionExplain[5 marks]
A school lets teachers see student contact details but not medical notes or safeguarding records, all held in one Student table. Explain how a view achieves this and one limitation.
Model answer plan
See the answer broken into clear points, with guidance on how each point earns marks.