Views — Concept Check
Questions on Views. Views are named stored SELECT queries; updatable views allow DML on simple single-table views; use views to simplify complex joins and enforce column-level security.
Questions in this quiz
8 questions on Views — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.A developer creates a view with the MERGE algorithm to improve performance. What is the expected behavior?
- AThe view automatically handles aggregates and DISTINCT operations.
- BThe view's definition is inlined into the outer query, allowing index use.
- CThe view's performance is unaffected by the algorithm used.
- DThe view is materialized into a temporary table before applying the outer query.
2.A view is created to expose only non-sensitive columns of a table. How should access be granted to enforce security?
- AGrant SELECT on both the view and the base table.
- BGrant DELETE on the view for security purposes.
- CGrant INSERT on the view to control data access.
- DGrant SELECT on the view, not the base table.
3.A view is defined with the WITH CHECK OPTION clause. What is the purpose of this clause?
- AIt allows the view to be materialized for performance.
- BIt prevents updates that would violate the view's WHERE condition.
- CIt allows the view to be used in GROUP BY queries.
- DIt enables the use of DISTINCT in the view's SELECT statement.
4.A developer notices that a query on a view is slow and suspects the TEMPTABLE algorithm is used. What should they check in the EXPLAIN output?
- ACheck for SIMPLE in the select_type, indicating no materialization.
- BVerify if the view uses the MERGE algorithm for better performance.
- CLook for DERIVED in the select_type, indicating materialization.
- DEnsure the view definition includes aggregates for optimization.
5.Why might a view with aggregates be non-updatable in MySQL?
- AAggregates allow for direct index usage on the base table.
- BAggregates ensure the view is always updatable.
- CAggregates prevent direct mapping to a single base table.
- DAggregates automatically enable the MERGE algorithm.
6.A view is created with the ALGORITHM=TEMPTABLE clause. What is a potential downside of this choice?
- AThe outer WHERE predicates cannot use base table indexes.
- BThe view is automatically updatable with this algorithm.
- CThe view cannot include any WHERE conditions.
- DThe view will always execute faster than with MERGE.
7.What happens if you try to update a row through a view with a WHERE filter that the update would violate?
- AThe update is blocked if WITH CHECK OPTION is used.
- BThe update succeeds, ignoring the WHERE filter.
- CThe view automatically adjusts to show the updated row.
- DThe update is always allowed if the view is updatable.
8.When creating a view to simplify complex joins, what is a key benefit?
- AIt eliminates the need for any future query optimization.
- BIt automatically improves execution speed of all queries.
- CIt ensures all columns in the base tables are exposed.
- DIt provides a stable API layer independent of base tables.