DB Interview Topic : What are VIEWS in DATABASE

What are Views and how do they work? This is the most asked DB Question in Interviews.

Photo by Cristina Gottardi on Unsplash

A VIEW in a database is essentially a virtual table that is created by running a query on one or more tables. Unlike an actual table, a view doesn’t store data physically; it is a saved query that displays the result set of the query each time it is accessed. Here’s a detailed explanation of views in a database:

1. Definition of a View

• Virtual Table: A view behaves like a table but does not hold actual data. The data displayed by the view is derived from the underlying base tables using a SELECT query.
• Result of a Query: A view is based on a predefined SQL query. The data seen through the view is the result of that query being executed on the base tables.

2. Creating a View

To create a view, the SQL CREATE VIEW statement is used. Here’s the basic syntax:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

CREATE VIEW employee_view AS
SELECT id, name, department
FROM employees
WHERE department = 'IT';

This view will only display employees from the IT department.

3. Types of Views

• Simple View: Created from a single table and does not include functions, aggregates, or multiple tables.
• Complex View: Created from multiple tables, can include joins, aggregates, and other advanced SQL features.
• Updatable View: Allows INSERT, UPDATE, DELETE operations if certain conditions are met (i.e., no joins, groupings, or aggregates).
• Non-updatable View: Views that cannot be modified directly, typically due to the complexity of the underlying query (joins, aggregate functions).

4. Advantages of Views

• Simplification: Simplifies complex queries for users. Instead of writing a complex join or subquery, users can query a view.
• Security: Restricts access to certain data. You can expose only specific columns or rows of a table without granting full access to the table itself.
• Data Abstraction: Hides the complexity of the underlying database schema from the user. It can present a consistent, logical structure even if the underlying database changes.
• Reusability: Once created, views can be reused in multiple queries, saving time and improving consistency.

5. Disadvantages of Views

• Performance Overhead: Since views do not store data, every time you query a view, it runs the underlying query again, which can impact performance, especially for large and complex views.
• Non-Modifiable: Not all views are updatable, so if a view involves joins or aggregate functions, you can’t insert, update, or delete records through it.
• Dependency Management: Changes to the underlying tables (like deleting columns) might break views if the view references that column.

6. Updatable Views

In some cases, a view can be updatable, meaning you can run INSERT, UPDATE, or DELETE statements on the view, and these operations will affect the base tables. However, for a view to be updatable, it must:

• Reference only one base table.
• Not contain any aggregate functions, DISTINCT, GROUP BY, or HAVING.
• Include all columns from the base table that do not allow null values or have a default value.

Get Full Stack Developer’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

7. Indexed Views

An Indexed View (also called Materialized View in some databases like Oracle) is a view that stores its result set physically on disk. This improves performance because the data is precomputed and stored, but it comes with a maintenance cost as changes to the base tables require the view to be updated.

• Syntax for Indexed View (SQL Server):

CREATE VIEW view_name WITH SCHEMABINDING AS
SELECT column1, column2
FROM table_name;
CREATE UNIQUE CLUSTERED INDEX index_name ON view_name (column1);

• Use Case: Used in scenarios where the query is computationally expensive, and performance needs to be optimized.

8. Uses of Views

• Data Security: To hide sensitive columns from users (e.g., exposing only non-sensitive data).
• Report Generation: Simplifying the process of generating reports from complex data.
• Modularity: Breaking down large and complex queries into manageable, reusable parts.
• Custom Data Presentation: Formatting data differently without altering the actual data (e.g., concatenating columns, aliasing columns).

9. Modifying Views

You can modify an existing view using the ALTER VIEW statement. Here’s the basic syntax:

ALTER VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

10. Dropping a View

If a view is no longer needed, it can be deleted using the DROP VIEW statement:

DROP VIEW view_name;

11. Example Scenarios

• Data Security: If a table contains sensitive information (like salaries), a view can be created that only exposes non-sensitive information to certain users.

CREATE VIEW public_employee_data AS
SELECT name, department FROM employees;

• Simplifying Queries: If a user frequently runs a query with several joins, a view can be created to encapsulate the join logic.

CREATE VIEW employee_department_view AS
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

12. View vs Table

FeatureViewTableData StorageNo, derived from base tablesYes, stores data physicallyPerformanceSlower for large datasetsFaster as data is storedUpdatableSometimes, depending on conditionsAlways, unless restrictedPhysical StorageNoYes

13. Limitations

• Views cannot include certain constructs like ORDER BY (unless used with TOP or LIMIT in some databases).
• Changes to the base tables (like adding or removing columns) might break the view, requiring updates.

14. Database-Specific Features

Different databases implement views slightly differently:

• MySQL: Views are straightforward but have limited support for updating complex views.
• SQL Server: Supports indexed views for performance.
• Oracle: Offers Materialized Views that store data physically and are useful for performance in distributed environments.

Conclusion

Views are powerful tools in SQL for abstracting complex queries, improving security, and simplifying access to data. They offer flexibility in how data is presented but come with trade-offs in terms of performance and updatability. Understanding when to use views and how to manage them is crucial for efficient database design.