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