HTML
JAVASCRIPT
CSS
PYTHON
JAVA
SQL

SQL Views

What is an SQL View?

A view in SQL is a virtual table that represents the result of a stored query. Unlike physical tables, views do not store data themselves; they dynamically retrieve data from one or more tables whenever they are accessed. This abstraction layer simplifies queries, enhances security, and promotes reusability.

Advantages of Using Views

  • Data Abstraction – Users interact with simplified structures without dealing with underlying complexities.
  • Security – Restricts direct access to specific columns or rows by defining selective data visibility.
  • Simplicity – Reduces redundant queries and simplifies code by encapsulating complex joins and aggregations.
  • Consistency – Ensures uniform data representation across different applications.
  • Performance Optimization – Predefined query execution can sometimes enhance retrieval speeds when indexed properly.

Syntax of Creating a View

To define a view, use the following SQL statement:

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

Explanation:

  • view_name: The name assigned to the virtual table.
  • SELECT: Retrieves specific columns from the original table.
  • FROM table_name:Specifies the source table(s).
  • WHERE condition: Filters the result set (optional).

Example: Creating and Using a View

Consider an "Employees" table storing information about employees in a company:

Employees List

Below is the list of Employees with their details.

Emp_ID Name Department Salary City
101 Alice IT 70000 New York
102 Bob HR 50000 Chicago
103 Carol IT 75000 Seattle
104 Dave Finance 60000 Boston

Creating a View for IT Employees

To create a view that only shows IT department employees:

Syntax:

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

Querying the View

After creating the view, retrieve data using:

SELECT * FROM IT_Employees;

Output:

Emp_ID Name Salary
101 Alice 70000
103 Carol 75000

Modifying a View

To update an existing view, use the CREATE OR REPLACE statement:

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

Dropping a View

To remove a view from the database, use:

DROP VIEW IT_Employees;

Conclusion

SQL Views streamline database interactions by offering simplified, secure, and reusable representations of data. They act as virtual tables without storing information directly, reducing redundancy and improving security by restricting access to sensitive data.

Previous Next