Views

What Is a View?

A view is a virtual table that does not physically store data; instead, it stores a SQL query. Every time the view is accessed, the query is run again.

Creating a View (CREATE VIEW)

CREATE VIEW v_active_employees AS
SELECT employee_id, name, department, salary
FROM EMPLOYEE
WHERE status = 'Active';

SELECT * FROM v_active_employees;

Views with JOIN

CREATE VIEW v_detail_employee AS
SELECT
    k.employee_id,
    k.name,
    d.department_name,
    k.salary
FROM EMPLOYEE k
JOIN DEPARTMENT d ON k.department_id = d.department_id;

Views with Aggregation

CREATE VIEW v_summary_department AS
SELECT
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM EMPLOYEE
GROUP BY department;

Modifying a View (CREATE OR REPLACE)

CREATE OR REPLACE VIEW v_active_employees AS
SELECT employee_id, name, department, salary, email
FROM EMPLOYEE
WHERE status = 'Active';

Dropping a View

DROP VIEW v_active_employees;

Uses of Views

Pros and Cons

Pros:
- Simplifies complex queries
- Improves data security
- Consistent data retrieval

Cons:
- Performance can be slow for complex queries
- Not all DBMSs support updating through views

Views help manage access and simplify queries. Next, we will learn about Index for performance optimization.