Introduction And Types Of View In SQL

In this article I am explaining about View and its types.

View in SQL Server is a virtual table which is used to create web API. Suppose there is a company which has more than 2 departments. Client of this company would like to know the data of employee. But company management would not like to share salary details of Employee to the client, In this case back end developer we will create one view in SQL Server that will not contain salary column. For better understanding we will create two tables as EmployeeDetails and Department in database like the following,

  1. create table Empdetails  
  2.     (  
  3.         eid int Primary key, ename varchar(20), Designation varchar(20), Salary int  
  4.     )  
  5. create table Department  
  6.     (  
  7.         Did int, DName varchar(20), eid int foreign key references Empdetails(eid)  
  8.     )  
  9. Inserting values in Database  
  10. Insert into Empdetails values  
  11.     (  
  12.         1, ’Sandeep’, ’S / w Dev.’, 25000  
  13.     )  
  14. Insert into Department values  
  15.     (  
  16.         101, ’Development’, 1  
  17.     )  
EmployeeDetails
                       Figure: EmployeeDetails table

Department
                      Figure: Department table

Now back end developer will create one web API to use view-in-sql-server. Here's the syntax or the same,

Syntax
  1. Create view-in-sql-server view-in-sql-server_name   
  2. As   
  3. Select column name   
  4. From tables name   
  5. Where clause   
Sample Example
  1. create view - in -sql - server ViewEmpdep  
  2. as  
  3. select e.ename, e.Designation, d.did, d.dname  
  4. from Empdetails e, Department d  
  5. where e.eid = d.eid  
ViewEmpdep
      Figure: ViewEmpdep

Now front end developer can write the code and send the Employee details to client. Suppose front end developer is writing code in C#.NET as,
  1. Using System.Data;  
  2. Using System.Data.SqlClient;  
  3. SqlConnection con = new SqlConnection(“Integrated security = true; Initial catalog = DatabaseDetails; data source = .”);  
  4. SqlDataAdapter Da;  
  5. Private void Page_Load()  
  6. {  
  7.     Con.Open();  
  8.     String s = ”select * from ViewEmpdep”  
  9.     Da = new SqlDataAdaper(s, con);  
  10.     Dataset ds = new Dataset();  
  11.     Da.Fill(ds, ”Empdet”);  
  12.     DataGridView1.DataSource = ds.Tables[0];  
  13.     DataGridView1.DataBound  
  14.     Con.Close  
  15. }  
  16. MessageBox.Show(“Details has displayed”);  
Now company can send the Employee details without Salary details to client.

Point to Remember: View is virtual table which is used to create web API. It is used to display the data from one and more than one tables. There are two types of View and these are: 
  1. Simple View
  2. Complex View

Syntax for View is

  1. Create viewView_Name  
  2. As  
  3. Select column name  
  4. From tables  
  5. Where clause   
  1. Simple View: Simple view is a type of view which is used to create web API for restrictions of data in a single table.

    Simple View Explanations

    EmployeeDetails
          Figure: Table EmployeeDetails

    There is a table EmployeeDetails which contains columns Eid, EName, Edistig and Salary. In this table we have to display EName, Edistig where Salary is greater than 20000.

    Query for Simple View is:
    1. Create View viewSimple As  
    2. Select Eid, EName, Edistig  
    3. FromEmployeeDetails  
    4. Where Salary > 20000  
  2. Complex View: Complex view is a type of view which is used to create web API for restrictions of data by using more than 1 tables.

    Complex View Explanations

    EmployeeDetails
             Figure: Table EmployeeDetails

    Department
                   Figure: Table Department

    EmployeeAdress
             Figure: Table EmployeeAdress

    There are two tables, EmployeeDetails which contains columns Eid, EName, Edestig and Salary and table Department that contains column Did, DName, EId and Table EmployeeAddress contain Column EId, EAdress and ParentContact . In these table we have to display Ename, Edestig, Salary, DId, DName, EAdress, and ParentContact .

    Query for creating Complex View:
    1. Create View ViewComplex  
    2. as  
    3. selecte.ename, e.Designation, d.DId, d.DName, a.EAdress, a.ParentContact  
    4. from EmpDetails e, Department d, EmployeeAdress a  
    5. where e.EId = d.EId And  
    6. e.EId = a.EId  

Recap: View is a virtual table which is used to create Web API. There are 2 types of View and those are,

  1. Simple View and
  2. Complex view

Syntax

  1. CreateView View_Name  
  2. As  
  3. SelectColumns Name  
  4. FromTables Name  
  5. Where Clause