A DBA creates a view called monthly_sales_summary that joins five large tables and computes SUM and COUNT aggregations. A dashboard queries this view every 5 minutes. What performance problem should the DBA anticipate?
ANone — views automatically cache their results, so repeated queries are fast
BThe view's JOIN and aggregation logic executes fresh on every query, potentially making each dashboard refresh expensive
CThe view will accumulate stale data over time, gradually degrading read performance
DViews containing GROUP BY are invalid SQL and this definition will fail
A regular SQL view stores only the query definition — no data. Every time a client queries the view, the database substitutes in the underlying SELECT and executes it from scratch against the current base tables. For expensive joins and aggregations across large tables, this means each dashboard refresh triggers the full computation. The solution is a materialized view, which stores the pre-computed result and serves reads instantly — at the cost of possible staleness.
Question 2 Multiple Choice
A company grants employees SELECT access to an employee_directory view (showing name, email, department) but not to the underlying employees table (which also contains salary and SSN). An employee runs SELECT * FROM employee_directory. What does the database actually do?
AReturns a pre-stored snapshot of the permitted columns from the last time the view was refreshed
BExecutes the view's underlying SELECT query against the current employees table, returning only the columns the view exposes
CRaises an error because employees lack direct table access, making the query impossible
DPrompts the DBA to approve the access before returning results
The database substitutes the view's query definition and executes it. Because the view's SELECT only references name, email, and department, the employee sees only those columns — even though the underlying table contains salary and SSN. The database enforces permissions at the view boundary: the employee never directly accesses the base table. This is the security use case for views: exposing a safe, limited interface over sensitive data.
Question 3 True / False
A regular SQL view improves query performance because the database pre-computes and stores the view's result on disk, making subsequent reads faster.
TTrue
FFalse
Answer: False
Regular (non-materialized) views store the query definition only — zero rows of data are persisted. Each access re-executes the full underlying SELECT. There is no performance advantage from the view itself. Only materialized views physically store results on disk and serve pre-computed data. A common misconception is that creating a view is like caching a result; it is not — it is just giving a name to a query.
Question 4 True / False
A SQL view can enforce security by limiting which columns or rows a user can see, even when the user has no direct access to the underlying table.
TTrue
FFalse
Answer: True
Views are a standard database security mechanism. By granting SELECT on a view (not the underlying table), you control exactly what data is visible. A view over employees that excludes salary and SSN, or filters WHERE department = 'HR', lets you expose exactly the right data to the right users. When the user queries the view, the database executes the underlying query transparently — the user sees only what the view's SELECT specifies.
Question 5 Short Answer
Explain the key difference between a regular view and a materialized view in terms of what is stored, and describe the tradeoff this creates.
Think about your answer, then reveal below.
Model answer: A regular view stores only the SQL query text — no data. Every query re-executes the SELECT from scratch, so results are always current but potentially slow for complex queries. A materialized view stores the query result as actual data on disk. Reads are fast because the computation is pre-done, but the data can become stale between refreshes. The tradeoff is: regular views = always fresh, potentially slow; materialized views = very fast reads, potentially out-of-date.
The right choice depends on your access pattern and freshness requirements. If the underlying data changes frequently and users need current results, a regular view is safer. If the query is expensive (multi-table joins, aggregations) and data can be slightly stale (e.g., nightly reports, dashboards refreshed every hour), a materialized view dramatically improves performance. Some databases support incremental refresh — updating only changed rows — to reduce the cost of keeping materialized views current.