A3.3.5 · Database programming
Views
By the end you can
Describe what a view is, the kinds of view, and why a database offers them.
Most people who use a database are not developers, and should not have to write a four-table join to see this month's sales. A is a virtual table: a saved SELECT query that you can use as if it were a table.
CREATE VIEW Catalogue AS SELECT Book.Title, Author.Name, Book.Price FROM Book JOIN Author ON Book.AuthorID = Author.AuthorID;
After that, SELECT * FROM Catalogue gives the joined result. The view stores no data of its own, only the query, so whenever the underlying tables change, the view changes with them.
Kinds of view#
- Simple view: one table, no joins or aggregates, often just a choice of columns.
- Complex view: joins several tables and may use aggregate functions and GROUP BY.
- Materialised (snapshot) view: the result is actually stored, and refreshed now and then. It reads faster because the query is not re-run each time, but it can be out of date between refreshes. (This sandbox has the ordinary kind only.)
Why use them#
- Hiding complexity. The joins and calculations are written once. Users just query the view.
- Security. A view can leave out columns (emails, salaries) or rows, and users can be given access to the view but not the tables underneath.
- Consistency. Everyone sees the same figures calculated the same way, always from current data.
- Independence. If the tables are reorganised, only the view definition changes. The programs that use it do not.
Author
| AuthorIDPK | Name | Country |
|---|---|---|
| 1 | Maya Okafor | Nigeria |
| 2 | Liam Chen | China |
| 3 | Sofia Rossi | Italy |
| 4 | Arun Patel | India |
| 5 | Noor Haddad | Egypt |
5 rows. AuthorID INT, Name TEXT(40), Country TEXT(20).
It stores its own rows on disk and is the real home of the data.
It stores only a query and always shows up-to-date results.
It stores a pre-calculated result for fast reading, but can be out of date until refreshed.
0 of 3 matched
Check yourself
Views
Quick questions, made fresh every time and marked here. Do a few until they feel easy.
Exam practice
Paper 1-style questions
4 questions · 13 marks
Write your answer as you would in the exam, then show the mark points and tick each one your answer made. These are our own questions in the style of the exam, not past-paper questions.
1.Describe what a database view is.
[3]Describe: A detailed account: say what happens, step by step if it is a process.
2.Discuss whether a view is physically stored in a database.
[3]Discuss: Look at the question from more than one side, weigh the points against each other, and come to a conclusion.
3.Outline two benefits of using views.
[4]Outline: A brief account: the main points, without much detail.
4.Construct a view named EmpNames that shows the EmployeeID, FirstName and LastName from the table Employees.
[3]Construct: Draw or write it out (a table, a diagram, a program).
In a nutshell
- A view is a virtual table defined by a SELECT query. It stores the query, not the data, so it always reflects the current tables.
- Simple views read one table; complex views use joins and aggregates; a materialised (snapshot) view stores its result and must be refreshed.
- Benefits: it hides complexity, can restrict which columns and rows users see (security), gives everyone the same consistent results, and keeps programs independent of table changes.
- Create one with CREATE VIEW name AS SELECT …; query it like a table; remove it with DROP VIEW.