The view is saved SQL query or we can call it a virtual table.
We have these 2 tables and by joining these 2 tables we want to output.
By joining these 2 tables we get the above result. Now we want to create a view, like other create statement like create a table, create procedure we have to create view statement
Below is a query for creating a view
create view empwithdepartment as select e.EmpID,e.full_name,e.Salary,e.Gender,d.Department_Name from Employee as e join Department as d on d.DepartmentID=e.DepartmentID;
To find a view in the database refer below image
If you check the above query it just selects query and if you want to get data from the view you will able to treat it as a table and simple select * from view_name; by using this you can get data.
View doesn’t store any data and it is just saved select query.
Advantages of views
- It is used to reduce the complexity of database schema
- It provides a mechanism to implement column level and row-level security means if you want to give access to certain users with a limited number of rows and columns then put your query in view and given that view name to end-user so that he performs the operation on that considering this is a table. By doing this end-user doesn’t know the underlying base table.
- It Is used to present aggregated data or detailed data.
To alter view you can use alter view syntax
Alter view view_name As - your query syntax here
To drop the view you can use
Drop view view_name;
GitHub : https://github.com/Sagar-Jaybhay