Creating and using views and materialized views for query reuse and performance, each explained with a short example.
30 cards · basic cards · AI-written, checked twice. Edit anything.
- What is a view in SQL?
- A stored query that acts like a table; contains only the SQL definition, not physical data.
- What is a materialized view?
- A view whose results are physically stored and refreshed periodically, unlike regular views.
- Basic syntax to create a view?
- CREATE VIEW view_name AS SELECT col1, col2 FROM table_name WHERE condition;
- Where are regular view results stored?
- Only the SQL definition is stored; results are calculated each time the view is queried.
- Where are materialized view results stored?
- Results are physically stored on disk like a real table.
- When are regular views recalculated?
- Every time the view is queried, using the stored SELECT definition.
- When are materialized views recalculated?
- Only during a manual or scheduled REFRESH operation, not on each query.
- What does WITH CHECK OPTION do on a view?
- Prevents INSERT or UPDATE operations through the view that violate the view's WHERE clause.
- Syntax to drop a view?
- DROP VIEW view_name;
- Can a view query another view?
- Yes, views can be nested; one view can be based on another view.
- How do you rename columns in a view?
- Use AS in the SELECT clause: SELECT emp_id AS employee_number FROM employees;
- Why use views to abstract table structure?
- Views hide complexity, enforce consistent business logic, and simplify queries for clients.
- How do views provide security?
- Views expose only specific columns and rows, preventing access to sensitive data.
- How do you query a view like a table?
- SELECT column FROM view_name WHERE condition;
- When use a view instead of a subquery?
- For frequently reused queries; views are named, stored once, and faster in many databases.