- LPU MCA Semester 1
- CAP570
- Unit 4
CAP570 · Unit 4: Database joins
Views
9 practice questions · in the ETE syllabus
Key points
- A view is a stored query used like a virtual table
- Views hold no data and reflect the base tables
- Views support security and simpler queries
- A materialised view stores the result and needs refreshing
A view is a stored SELECT query that is used like a virtual table. It is created with CREATE VIEW name AS SELECT ... and queried with SELECT * FROM name. A view stores no data of its own: each time it is queried, its SELECT runs against the base tables, so it always reflects their current rows. Adding a row that matches the view's condition makes the row appear in the view, and emptying the base table empties the view.
| Purpose | Example |
|---|---|
| Security | Expose only some columns or rows to a user |
| Simplicity | Hide a join or an aggregate behind a name |
| Consistency | Reuse one definition of a calculation |
Views can be built on joins and on aggregate queries such as SUM(qty) ... GROUP BY item, and further filtered by an outer query. Many databases allow inserts and updates through a simple view that maps to one table, but views built on joins or aggregates are usually read-only. A materialised view (for example in Oracle) stores the result physically and must be refreshed, trading freshness for speed. DROP VIEW removes the definition and never the base data.
Exam trap: a view is not a copy of the data. Changes in the base tables show up in the view automatically.
Try two questions
How does a materialised view differ from an ordinary view?
- It stores the query result physically and must be refreshed to stay current
- It stores no result and always recomputes the query when it is used
- It can only be created on a table that has no rows at all
- It is deleted automatically at the end of every transaction
Show the answer
A. A materialised view trades freshness for speed by saving the result.
Why not B: That describes an ordinary view.
Why not C: A materialised view can be created on tables that hold data.
Why not D: A materialised view persists until it is dropped.
Which statement creates a view?
- CREATE TABLE v AS VIEW ...
- MAKE VIEW v FROM SELECT ...
- CREATE VIEW v AS SELECT ...
- SELECT v AS VIEW ...
Show the answer
C. The syntax is CREATE VIEW name AS followed by a SELECT.
Why not A: CREATE TABLE ... AS creates a table, not a view.
Why not B: MAKE VIEW is not a valid SQL statement.
Why not D: A SELECT statement cannot define a view by itself.