Explain SQL Views

SQL Views are virtual tables that allow users to simplify complex queries, encapsulate business logic, and enhance security by restricting access to specific data. A view is a stored query that can be treated as a table, meaning you can select, insert, update, and delete data from a view, depending on the SQL database system and the complexity of the view.

Creating a View

The CREATE VIEW statement is used to create a view. Here's a basic example:

CREATE VIEW EmployeeView AS
SELECT EmployeeID, FirstName, LastName, Department
FROM Employees
WHERE Department = 'Sales';

This view, named EmployeeView, selects specific columns from the Employees table where the department is 'Sales'. You can now query EmployeeView as if it were a table:

SELECT * FROM EmployeeView;

Benefits of Using Views

1. Simplification: Views can simplify complex queries by encapsulating them into a single query.
2. Security: Views can restrict access to specific columns or rows, protecting sensitive data.
3. Data Abstraction: Views can provide a level of abstraction, allowing users to interact with data without knowing the underlying table structure.
4. Consistency: Views can ensure that users see a consistent, unchanging view of the data, even if the underlying tables change.

Example Scenario

Let's say you have the following tables:

Employees Table

Departments Table

Creating a Complex View

Suppose you want a view that shows the total salary for each department. You can create a view like this:

CREATE VIEW DepartmentSalary AS
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;

Now, querying this view will give you the total salary per department:

SELECT * FROM DepartmentSalary;

Output:

Updatable Views

Some views are updatable, meaning you can use INSERT, UPDATE, and DELETE statements on them. However, there are limitations, especially with complex views involving joins, aggregations, or certain functions.

Here's an example of an updatable view:

CREATE VIEW SalesEmployees AS
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Department = 'Sales';

You can update the Salary of a sales employee through the view:

UPDATE SalesEmployees
SET Salary = 80000
WHERE EmployeeID = 1;

This will update John Doe's salary in the Employees table.

Conclusion

SQL Views are powerful tools for simplifying data retrieval, enhancing security, and abstracting data complexity. They provide a way to present data in a customized format while keeping the underlying table structure hidden and secure.