Importance of View

In production environments, database views are crucial for several reasons. A view is essentially a virtual table created by querying data from one or more tables. It doesn't store data itself but provides a way to access and interact with the data more efficiently and securely.

Refer to the details here and get the setup ready before testing the samples

Creating View

This section shows examples of creating different view that clearly shows how views help DBA and Application programmers and why they are important

(Pre-requiste) Drop Existing views

DROP VIEW EMP_MENTOR_DETAILS;
DROP VIEW EMP_PUBLIC_DIRECTORY;
DROP VIEW EMPLOYEE_OVERVIEW;
DROP VIEW DEPT_SALARY_SUMMARY;
DROP VIEW DEPT_SALARY_AGG;
DROP VIEW MENTOR_SKILL_DISTRIBUTION;
COMMIT;

Simplifies Complex Queries

Views allow you to encapsulate complex queries into a single object, making it easier for developers or end-users to retrieve data without needing to know the underlying complexities. Example: If you frequently join multiple tables for a report, a view can encapsulate the join logic, so users can query a simple view instead of repeatedly writing complex joins.

CREATE VIEW EMP_MENTOR_DETAILS AS
SELECT
    E.EMP_ID,
    E.NAME,
    E.DEPARTMENT,
    E.PHONE,
    E.SALARY,
    M.MENTOR_ID,
    M.SKILLS
FROM EMP E
LEFT JOIN MENTOR M ON E.EMP_ID = M.EMP_ID;

Data Security and Access Control

Views can limit access to sensitive data by exposing only a subset of the data. You can grant permissions to specific users on views rather than on the underlying tables, thus hiding sensitive information. Example: In a human resources system, you can create a view that shows employee names and departments, but hides salary or other private details.

CREATE VIEW EMP_PUBLIC_DIRECTORY AS
SELECT
    EMP_ID,
    NAME,
    DEPARTMENT
FROM EMP;

Improved Maintainence

By using views, you can centralize the logic of querying and presenting data. If the underlying database schema changes (e.g., a table structure), you can modify the view rather than rewriting all queries throughout your application. Example: If a column is renamed in the underlying table, you only need to update the view rather than updating all SQL queries in the application code.

CREATE VIEW EMPLOYEE_OVERVIEW AS
SELECT
    EMP_ID,
    NAME,
    DEPARTMENT,
    PHONE,
    SALARY
FROM EMP;

Consitency

Views ensure consistent data presentation across different parts of an application. By centralizing the data logic, you ensure that reports and queries always pull the same data, reducing inconsistencies. Example: Pre-aggregates salary data per department to maintain reporting consistency. So that If reports rely on this view, they always get consistent salary summaries.

CREATE VIEW DEPT_SALARY_SUMMARY AS
SELECT
    DEPARTMENT,
    COUNT(EMP_ID) AS TOTAL_EMPLOYEES,
    SUM(SALARY) AS TOTAL_SALARY,
    AVG(SALARY) AS AVERAGE_SALARY
FROM EMP
GROUP BY DEPARTMENT;

Data Aggregation and Transformation

Aggregation means summarizing data, like counting, summing, or averaging values. Transformation means modifying the data format, such as grouping, categorizing, or applying calculations. Views precompute aggregations or transformations so that users don’t have to write complex queries repeatedly. The data remains unchanged in the original table, but the view presents a summarized version. Let’s say we frequently need a report that shows the total, average, and highest salary for each department. Instead of computing it manually every time, we create a view:

CREATE VIEW DEPT_SALARY_AGG AS
SELECT
    DEPARTMENT,
    COUNT(EMP_ID) AS TOTAL_EMPLOYEES,
    SUM(SALARY) AS TOTAL_SALARY,
    AVG(SALARY) AS AVERAGE_SALARY,
    MAX(SALARY) AS HIGHEST_SALARY
FROM EMP
GROUP BY DEPARTMENT;

Abstracting Complex Business Logic

Views can abstract complex business logic, such as filtering, transformations, or business rules that might otherwise be repeated across different queries. This makes the database easier to work with and reduces the risk of errors. Example: View allows easy querying of mentor skills without manual aggregations so applications don’t have to handle skill aggregation manually.

CREATE VIEW MENTOR_SKILL_DISTRIBUTION AS
SELECT
    SKILLS,
    COUNT(MENTOR_ID) AS MENTOR_COUNT
FROM MENTOR
GROUP BY SKILLS;

Accessing data using Views

After we create the view, this sections show how to access data using views. TBA