MCQs on Views and Materialized Views | PostgreSQL Database
Master PostgreSQL Views and Materialized Views by exploring their creation, management, and use cases. Learn the intricacies of materialized views, including how to refresh and utilize them efficiently.
Creating and Managing Views
- What is a PostgreSQL view?
- A) A temporary table
- B) A virtual table based on a query
- C) A copy of a database
- D) A database snapshot
- Which command is used to create a view in PostgreSQL?
- A) CREATE TABLE
- B) CREATE VIEW
- C) CREATE QUERY
- D) CREATE TABLE AS
- Views in PostgreSQL are updated when:
- A) Data in the base table changes
- B) The view is refreshed manually
- C) They cannot reflect updated data
- D) The database is restarted
- What happens if you drop a base table associated with a view?
- A) The view remains functional
- B) The view becomes invalid
- C) The view updates automatically
- D) The view converts to a table
- Which of the following is NOT true about views?
- A) Views are stored as query definitions.
- B) Views can be indexed for better performance.
- C) Views do not store data physically.
- D) Views can be used to simplify complex queries.
- How can you update data in a view in PostgreSQL?
- A) Views are read-only by default.
- B) Directly modify the view definition.
- C) Insert data into the view.
- D) Update data in the base table.
- Which statement is used to modify an existing view?
- A) ALTER VIEW
- B) UPDATE VIEW
- C) MODIFY VIEW
- D) CREATE OR REPLACE VIEW
- How can you delete a view in PostgreSQL?
- A) DROP VIEW
- B) DELETE VIEW
- C) REMOVE VIEW
- D) TRUNCATE VIEW
Use Cases for Views
- Why are views used in PostgreSQL?
- A) To create indexes on large tables
- B) To simplify complex queries
- C) To store data physically
- D) To replace triggers
- Which of the following is an example of using a view?
- A) Hiding sensitive columns from a table
- B) Aggregating data for reporting
- C) Creating read-only subsets of data
- D) All of the above
- How can views improve database security?
- A) By allowing selective data access
- B) By encrypting the underlying table
- C) By blocking all unauthorized queries
- D) By locking the base tables
- Views are best suited for:
- A) Modifying data in base tables
- B) Optimizing query performance
- C) Presenting aggregated data
- D) Storing frequently accessed data
- In a reporting system, views are primarily used to:
- A) Increase data redundancy
- B) Format data for end-user consumption
- C) Replace indexes
- D) Enhance database transactions
- What is the advantage of combining multiple tables into a single view?
- A) Reduces storage space
- B) Simplifies query execution for users
- C) Makes tables read-only
- D) Creates temporary data copies
- Views are NOT ideal for:
- A) Frequently changing data
- B) Simplifying data access
- C) Securing sensitive data
- D) Aggregating data for reports
Understanding Materialized Views
- What is a materialized view in PostgreSQL?
- A) A temporary table stored in memory
- B) A view that stores data physically
- C) A snapshot of the database schema
- D) A virtual table with live updates
- How does a materialized view differ from a standard view?
- A) It requires storage space.
- B) It is updated in real-time.
- C) It stores query definitions only.
- D) It cannot be indexed.
- Which command is used to create a materialized view in PostgreSQL?
- A) CREATE MATERIALIZED VIEW
- B) CREATE VIEW MATERIALIZED
- C) CREATE TABLE VIEW
- D) CREATE SNAPSHOT VIEW
- Why are materialized views used in PostgreSQL?
- A) To enhance query performance by storing results
- B) To replace base tables
- C) To create dynamic data
- D) To enforce data integrity
- What is the disadvantage of using materialized views?
- A) They cannot store aggregated data.
- B) They must be refreshed to stay updated.
- C) They are slower than regular views.
- D) They require a foreign key.
- Can a materialized view be indexed in PostgreSQL?
- A) Yes, for faster querying
- B) No, indexing is not allowed
- C) Only for read-only operations
- D) Only with a primary key
- When should you choose a materialized view over a standard view?
- A) For dynamic queries
- B) When query performance is critical
- C) For real-time data updates
- D) For storing raw data
Refreshing Materialized Views
- Which command is used to refresh a materialized view?
- A) REFRESH MATERIALIZED VIEW
- B) UPDATE MATERIALIZED VIEW
- C) MODIFY MATERIALIZED VIEW
- D) REFRESH VIEW
- What happens when a materialized view is refreshed?
- A) It clears its data and reloads from the base query.
- B) It updates only changed rows.
- C) It deletes the view and recreates it.
- D) It modifies the base table.
- How can you refresh materialized views incrementally in PostgreSQL?
- A) Use the CONCURRENTLY option.
- B) Perform a full refresh manually.
- C) Add indexes to the base table.
- D) Update the view manually.
- What is the advantage of the CONCURRENTLY option in refreshing materialized views?
- A) Allows users to query the view during the refresh process.
- B) Reduces storage requirements.
- C) Updates the base table automatically.
- D) Eliminates the need for indexing.
- Incremental refresh of materialized views works best when:
- A) The data changes frequently.
- B) The base query is complex.
- C) Indexes exist on the base tables.
- D) The materialized view has no indexes.
- A materialized view that is not refreshed regularly may:
- A) Improve performance.
- B) Show outdated data.
- C) Cause database crashes.
- D) Lock base tables.
- How do you automate the refreshing of materialized views?
- A) By creating a database trigger
- B) Using cron jobs or scheduled tasks
- C) Adding an ON UPDATE rule
- D) Writing a stored procedure
- Which of the following best practices applies to materialized views?
- A) Avoid refreshing during peak usage.
- B) Use for real-time analytics.
- C) Keep base queries simple.
- D) Both A and C.
Answer Key
| Qno | Answer |
|---|---|
| 1 | B) A virtual table based on a query |
| 2 | B) CREATE VIEW |
| 3 | A) Data in the base table changes |
| 4 | B) The view becomes invalid |
| 5 | B) Views can be indexed for better performance |
| 6 | D) Update data in the base table |
| 7 | D) CREATE OR REPLACE VIEW |
| 8 | A) DROP VIEW |
| 9 | B) To simplify complex queries |
| 10 | D) All of the above |
| 11 | A) By allowing selective data access |
| 12 | C) Presenting aggregated data |
| 13 | B) Format data for end-user consumption |
| 14 | B) Simplifies query execution for users |
| 15 | A) Frequently changing data |
| 16 | B) A view that stores data physically |
| 17 | A) It requires storage space |
| 18 | A) CREATE MATERIALIZED VIEW |
| 19 | A) To enhance query performance by storing results |
| 20 | B) They must be refreshed to stay updated |
| 21 | A) Yes, for faster querying |
| 22 | B) When query performance is critical |
| 23 | A) REFRESH MATERIALIZED VIEW |
| 24 | A) It clears its data and reloads from the base query |
| 25 | A) Use the CONCURRENTLY option |
| 26 | A) Allows users to query the view during the refresh process |
| 27 | C) Indexes exist on the base tables |
| 28 | B) Show outdated data |
| 29 | B) Using cron jobs or scheduled tasks |
| 30 | D) Both A and C |