Mumbai University > Information Technology > Sem 3 > Database Management System

Marks: 10M

Year: May 2016

Definition of Views:

• Basically, a view is a single table derived from multiple tables or a logically representing subset of data.
• These other tables can be base tables or previously defined views.
• A view is considered to be a virtual table, in contrast to base tables, whose tuples are physically stored in database.

Creation of View:

• In SQL, the command to specify a view is CREATE VIEW. The view is given a (virtual) table name (or view name), a list of attribute names, and a query to specify the contents of the view.

Following is the syntax of view.

CREATE VIEW viewname AS 
SELECT column1, column2 
FROM tablename
WHERE columnname=expressionList;

• Consider a table Tbl_Employee with the fields Emp_ID, Emp_Name, Emp_DOB, Emp_Address, Emp_DateOfJoining, Emp_Gender, Emp_EmailID. Following is the view vw_EmployeeContactDetails which contains the name and Email ID of the employee.

  CREATE VIEW vw_EmployeeContactDetails SELECT Emp_Name, Emp_EmailID FROM Tbl_Employee

It results in the creation of view. To fetch the items of view, a select statement can be written as follows:

SELECT * FROM vw_EmployeeContactDetails

• If we do not need a view any more, we can use the DROP VIEW command to dispose of it. Syntax is

DROP VIEW viewname

Example: To drop the view vw_EmployeeContactDetails, following SQL statement must be executed:

DROP VIEW vw_EmployeeContactDetails