SQL Views

In SQL, a View is like a virtual table that is created from the result of an SQL query.
It doesn’t store actual data itself; instead, it stores the SQL query that fetches data from one or more tables whenever the view is accessed.

Think of it as a saved query that can be reused just like a table.

Key Points About Views

• Virtual Table: Views look like a table, but they don’t store data permanently.
• Based on Queries: Views are created using SELECT statements.
• Security: They can hide sensitive columns and show only necessary data.
• Simplification: They make complex queries easier to reuse.

Syntax to Create a View

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

Example: Creating a View

Suppose we have a table Employees:

If we only want to see IT department employees, we can create a view:

CREATE VIEW IT_Employees AS
SELECT Name, Salary
FROM Employees
WHERE Department = 'IT';

Now, instead of writing the filter query every time, we can just use:

SELECT * FROM IT_Employees;

Types of Views

1. Simple View
   • Based on one table.
   • Does not use functions, GROUP BY, or multiple tables.
   • Example: sqlCopyEditCREATE VIEW EmployeeNames AS SELECT Name FROM Employees;
2. Complex View
   • Based on multiple tables or includes functions, GROUP BY, etc.
   • Example: sqlCopyEditCREATE VIEW DepartmentSalary AS SELECT Department, AVG(Salary) AS AvgSalary FROM Employees GROUP BY Department;

Advantages of Views

1. Data Security – You can restrict access to specific columns.
2. Simplifies Queries – Reuse complex SQL logic easily.
3. Consistency – Changes in the base table reflect in the view automatically.
4. Logical Data Independence – Users don’t need to know table structures.

Disadvantages of Views

1. No Storage of Data – Every time you query a view, it runs the underlying query, which can affect performance.
2. Cannot Always Modify Data – Some views are not updatable (especially complex ones).
3. Dependency on Base Tables – If the base table changes, the view may stop working.

Modifying a View

CREATE OR REPLACE VIEW IT_Employees AS
SELECT Name, Department
FROM Employees
WHERE Department = 'IT';

Deleting a View

DROP VIEW IT_Employees;

Summary Table of SQL Views

In short: SQL Views are a powerful tool for simplifying data retrieval, increasing security, and organizing database queries without physically duplicating data. They are essential for clean, maintainable, and secure database design.