View in SQL

Introduction of View

• View is a powerful feature of SQL that can significantly streamline our data management and querying processes.

Definition

• View is a virtual table created using the CREATE VIEW statement from an existing table.

Characteristics

• In SQL, a view is a virtual table based on the result set of a query. It contains rows and columns just like a real table, and the fields in a view are fields from one or more real tables in the database.
• Once a view is created, we can query it just like a regular table.

Advantages

• View allows us to encapsulate complex queries into a simple table-like structure that can be queried just like a real table.
• A view can simplify data access, simplify complex queries, enhance security by restricting specific data access, present data in/from a different perspective, and improve maintainability by centralizing complex logic.
• They simplify complex queries, enhance security, and improve maintainability.

Views Operations

• To Create a View
  • The view is a virtual table created using the CREATE VIEW statement. 
  • Syntax:

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

• • Example:

CREATE VIEW sales_employee AS
SELECT id, first_name, last_name, salary
FROM employee
WHERE department = ‘Sales’;

(The above statement is used to create a view named sales_employee that shows only id, first_name, last_name, salary from the employee table of the “Sales” department)

• To Display the View Contents (Querying a View)
  • A view can be queried like a table, and some can be updated directly.
  • Example –

SELECT * FROM sales_employee;

(This will return all columns and rows from the sales_employee view.)

• To Update/Edit/Modify a View
  • We can also update the data in the underlying table through a view, provided the view is updatable.
  • A View can be updated when it must satisfy certain conditions :-
    • It must reference only one table.
    • It must not contain any aggregate functions, GROUP BY, DISTINCT, etc.
  • Example –

UPDATE sales_employee
SET salary = salary * 1.1
WHERE last_name = ‘Kumar’;

• To Drop/Remove a View
  • If we no longer need a view, we can drop it.
  • The keyword  DROP VIEW statement is used to remove a view.
  • Syntax

DROP VIEW view_name;

• • Example

DROP VIEW sales_employee;