VIEW in SQL
What is a view in SQL?
I. A view in SQL is a virtual table that is based on the result of a query. ( it is a duplication table of an
original table).
II. A view is like a saved query.
III. It doesn’t store data itself but provides a way to look at data from one or more tables in a
simplified, customized format.
IV. Views can simplify complex queries, enhanced security and allow for data abstraction.
How to create a view?
CREATE VIEW view_name AS SELECT column1,column2 from table_name WHERE <condition>;
Example of creating a view?
Suppose you have the following two tables
Table-1: employee
Empid Name dept
1 Alice HR
2 Bob IT
3 Charlie HR
Table-2: department
Deptid Dept_name
1 HR
2 IT
Create a view to show employee along with their department names
CREATE VIEW employee_details AS SELECT employee.Name,department.dept_name FROM employees
JOIN department ON employee.dept=department.Dept_name;
This view will show employee names along with their department names.
Using the view
After creating the view , you can query it like a regular table.
SELECT *FROM employee_details;
Output
Name Dept_name
Alice HR
Bob IT
Charlie HR
Operations on View
1. Select data from a view
We can select data from a view just like a regular table.
SELECT *FROM employee_details;
2. Updating data through a view
If the view is simple and involves only one table, you may be able to update data through the view.
However, updates through views are limited if the view contains complex operations like joins.
UPDATE employe_details SET dept_name=”Finance” WHERE name=”Alice”;
3. Dropping a view
To remove a view , we can use the DROP VIEW command.
DROP VIEW employee_details;
Advantage of Views:
Data abstraction: Hides complexity from the end-user.
Reusability: Saves time by reusing complex queries
Security: Restricts access to sensitive data by only showing selected columns or rows.
Example:
View level Teacher Student Office Staff
Regno Name Mark
In above table is consider as student table and there is three different columns
( Regno,Name,Mark)
For that one table there are total 3-views
1st-view for the Teacher, Teacher can view complete table like (Regno ,Name , Marks).
2nd view for the Student , Student can view only (Regno , Name) and Makrs columns hides from the
Students
3rd view for the Office staff , office staff can view only(Name) and Regno,Marks hide from the office
staff
So there is only one table and there are total 3-views of that particular users. So this views are
called as “virtual Table”.
You can view the particular table as per the restriction ,as per the add authorized person or as per
the requirement so this is called as view.
Limitation
Views do not store data themselves; they simply store the query.
Some views, especially those with joins or aggregations, might not be directly updateable.
VIEW TABLE
A database object that allows generating a logical A database object or an entity that stores the data
subset of data from one or more tables of a database
A virtual table An Actual table
View depends on the table Table is an independent data object
You can create a table in your DB, You can
Every users having different views or different
perform INSERT, DELETE,UPDATE, this kind of
display of that particular table this is called as
operations can do in Table. You can also create a
virtual table
structure of a table
In view , you table structure is already created you
need to just perform operations on displaying
purpose, not on manipulation or not on data
purpose
You can’t change row name , column name in view
,you can display the actual table as it as per the
requirement
