Database View

View is a database object also called a logical table. it has no psychical existence. It is not like a simple table, but is a virtual or logical table which contains columns and data from different tables (may be one or more tables). A View does not contain any data, it is a set of queries that are applied to one or more tables that is stored within the database as an object. After creating a view from some table(s), it used as a reference of those tables and when executed, it shows only those data which are already mentioned in the query during the creation of the View.

Creating view Syntax:

-------------------------------------------------

CREATE VIEW [View_Name]
 AS
 [SELECT Statement]

------------------------------------ -------------

CREATE VIEW SampleView
As
SELECT EmpID, EmpName  FROM EmpInfo 

--------------------------------------------------

Data retrieve from view: 

SELECT * FROM SampleView WHERE EmpID ='FN0009C1' 

View does not modify. means that when we modiy as Select statement of view . we did not Alter view.

 So any changes for view , at first DROP the view and again create the view 

---------------------------------------------------

DROP VIEW SampleView  

--------------------------------------

Different types of Views

There are two different types of Views:

• System Views
• Information Schema View
• Catalog View
• Dynamic Management View (DMV)
• User Defined Views
• Simple View
• Complex View

Now we discuss about Simple view and Complex View 

Simple view

A Simple View is a user defined view. A Simple View takes the data from a single table and has no function. The user defined view is created by the user as needed. We are showing a Simple View using a student table.

----------------------------------------

CREATE VIEW stud 

AS SELECT studname,studid,studaddress 
FROM student WHERE depno='d001'

  ----------------------------------------------------

Complex View

A Complex View is created for multiple tables and contains functions and group data.


CREATE VIEW [asd] 
AS 

SELECT DISTINCT depno FROM [student]


GROUP BY depno