🛠️ Database Administration: Views

1. What is a View?

A View is a virtual table based on the result set of a SQL query. It presents data from one or more tables without storing the data physically.

• Acts as a stored SELECT query.
• Provides abstraction and simplified data access.
• Can restrict access to sensitive data.
• Can combine data from multiple tables.

2. Why Use Views?

• Simplify complex queries by encapsulating them.
• Provide data security by showing only specific columns or rows.
• Support data abstraction hiding the underlying table structure.
• Facilitate data consistency by reusing common queries.
• Can be used as read-only or updatable views (depending on DBMS and query).

3. Creating a View

CREATE VIEW employee_details AS
SELECT employee_id, first_name, last_name, department, salary
FROM employees
WHERE status = 'ACTIVE';

• The view employee_details now acts like a table for querying active employees.

4. Using Views

• Query a view just like a table:

SELECT * FROM employee_details WHERE department = 'Sales';

• Views can be joined with other tables or views.

5. Types of Views

6. Updating Data Through Views

• Updatable Views allow INSERT, UPDATE, DELETE through the view if:

  • The view is based on a single table.
  • Does not contain aggregates, DISTINCT, GROUP BY, or joins.
  • Includes all NOT NULL columns required.

• Otherwise, views are read-only.

Example of updating through view:

UPDATE employee_details SET salary = salary * 1.1 WHERE employee_id = 101;

7. Advantages of Views

• Security: Restrict user access to only required data.
• Simplicity: Hide complexity of underlying joins or calculations.
• Consistency: Centralize business logic or calculations.
• Reusability: Share complex queries without rewriting them.

8. Managing Views

• Modify view:

CREATE OR REPLACE VIEW employee_details AS
SELECT employee_id, first_name, last_name, department, salary
FROM employees
WHERE status = 'ACTIVE' AND department IS NOT NULL;

• Drop view:

DROP VIEW employee_details;

9. Limitations of Views

• Performance can be slower than direct table queries (especially with complex views).
• Updatability is restricted based on the view definition.
• Materialized views require maintenance to keep data fresh.

10. Summary Table