SQL View Management 3 — Questions and Answers
Question 1: An aggregate view using GROUP BY and SUM is typically what kind of view?
- Non-updatable (Correct answer)
- Always updatable
- A materialized view by default
- A system catalog view
Correct answer: Non-updatable
Views containing aggregation cannot be directly updated because rows do not map one-to-one.
Question 2: Which command refreshes the stored data of a materialized view in many databases?
- REFRESH MATERIALIZED VIEW (Correct answer)
- UPDATE VIEW
- RELOAD VIEW
- REBUILD VIEW
Correct answer: REFRESH MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW re-executes the underlying query and updates the cache.
Question 3: A view that references another view is best described as a what?
- Nested view (Correct answer)
- Recursive trigger
- Pivot table
- Foreign key chain
Correct answer: Nested view
Views can be layered on top of other views, forming nested views.
Question 4: What is a key benefit of using views to simplify complex joins?
- Users query one named object instead of rewriting the join (Correct answer)
- Joins run with no CPU cost
- Indexes become unnecessary
- Data is automatically deduplicated
Correct answer: Users query one named object instead of rewriting the join
Views encapsulate complex logic so users can query a simple name.
Question 5: Which clause renames the columns exposed by a view?
- Specifying a column list after the view name (Correct answer)
- USING an ALIAS keyword on the view
- ORDER BY in the definition
- GROUP BY in the definition
Correct answer: Specifying a column list after the view name
Listing column names in CREATE VIEW v (col1, col2) renames output columns.
Question 6: If a base table column referenced by a view is dropped, what typically happens?
- The view becomes invalid or errors when queried (Correct answer)
- The view auto-recreates the column
- Nothing, views ignore base changes
- The base table is restored
Correct answer: The view becomes invalid or errors when queried
Removing a column the view depends on breaks the view until redefined.
Question 7: What does the WITH READ ONLY option on a view enforce?
- No DML can be performed through the view (Correct answer)
- Only one user can read it
- The view refreshes hourly
- Columns are encrypted
Correct answer: No DML can be performed through the view
WITH READ ONLY blocks INSERT, UPDATE, and DELETE through the view.
SQL View Management 3
An aggregate view using GROUP BY and SUM is typically what kind of view?