Views, Materialized Views, Synonym — SQL Interview preparation
What is a View?
In SQL, a view is essentially a “virtual” table created from a query that references one or more existing tables in the database. It doesn’t hold data itself; rather, it represents a subset of data from those tables. Views are great for encapsulating complex SQL queries and working with aggregated, filtered or computed data without directly changing the underlying tables.
Here are some questions alongside for better understanding:
1. What are the advantages of using views in SQL?
A. Views help simplify complex queries by encapsulating them, enhance security by restricting data access, and provide a level of abstraction which makes the database system more modular and easier to manage.
2. Can Views be updated?
A. Yes, under certain conditions. Simple views (those based on a single table without any aggregations or computed columns) can be updated. However, complex views usually can’t be updated directly because the database system might not be able to accurately determine how to update the underlying tables.
3. How does the performance of a View compare to a direct query?
A. Since a View is essentially a saved query, its performance should be similar to that of the underlying query. It’s crucial to remember that every time you access a View, it runs the original query again and fetches the latest data from the underlying table(s).
4. What are Materialized Views?
A. A materialized view is a view whose results are computed and stored physically like a regular table. Unlike regular views, they contain data on their own and can offer performance benefits by storing precomputed results of complex queries.
5. What is the ‘WITH CHECK OPTION’ in views?
A. ‘WITH CHECK OPTION’ is a clause used with Views to ensure any INSERT or UPDATE operation adheres to the View’s defining query. It’s like a guard, ensuring that only data fitting the View’s conditions can be added or modified.
6. How would you create a View that displays all customers from New York?
A. Here’s an example SQL command:
CREATE VIEW NewYorkCustomers AS
SELECT * FROM Customers
WHERE City = ‘New York’;
