In this article we will learn what are views in SQL. It briefly explains about how to use the view, how to create a view , how to read it and finally implementing dropping function on view.To learn different types of joins in SQL you can visit Learn about joins in SQL.
To understand what are views in SQL study the points given below:
Introduction:
- View is a result set obtain by sql query.
- View contains information of real tables in the form of row and column like real table.
- View is a virtual table that forms information dynamically depending on the sql statement.
- In this topic we will discuss the following points:
- Uses of view.
- Creation of view.
- Reading a view.
- Dropping a view.
- For Example: Let us assume that we have two real tables, salary and emp-profile. Now we will make a view that displays the result of combination of both real tables.
- Uses of view:
- View store data dynamically.
- It prevents to access real table.
- Only selected column can be accessed in view.
- It takes reference for real table that prevent the access of underlying tables.
- It is a virtual table.
- Creation/Modification of a View :
- The basic syntax for creation of view is :
- In case if you need to modify the view, you can use alter keyword for replacing it.
- Let’s assume that we have two main tables, one is employee salary table and other is employee profile table.
- Now, we need to create a view that fetchs information of both the tables and displays the result in a single virtual table:
- Now let’s create view as virtual table. It will fetch information from both table and result a single virtual table
- Reading a View:
- To read information from view,syntax is given below:
- Now we have learnt to create and read view.
- Let’s read the information from View_empdetail view
- Drop a view:
- TO delete a view, the basic syntax is:
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name where [condition]
Alter VIEW view_name AS SELECT column_name(s) FROM table_name where [condition]
create VIEW [dbo].[View_empdetail] AS SELECT dbo.salary.empid, dbo.empprofile.Passwrod, dbo.empprofile.username, dbo.salary.monthlysalary, dbo.salary.department, dbo.empprofile.name FROM dbo.salary INNER JOIN dbo.empprofile ON dbo.salary.empid = dbo.empprofile.empid
Select * from view_name [where condition]
select * from [dbo].[View_empdetail]
DROP VIEW view_name
Thus, we are now familiar and learnt what are views in SQL along with examples implementing its uses, creation, reading and droping functions on view.
For reference you can watch this video: