SQL Views

Introduction to Views

  • View is a virtual table that contains rows and columns, just like a real table.
  • It is used to hide complexity of query from user.
  • A virtual table does not exist physically, it is created by a SQL statement that joins one or more tables.
Syntax:
CREATE VIEW view_name
AS
SELECT
FROM
WHERE ;

Example

CREATE VIEW emp_dept_view
AS
SELECT Ename, DeptName
FROM Employee, Department
WHERE Employee.Eid=Department.Eid;


You can now see the view using following query,

SELECT * FROM emp_dept_view;

EnameDeptName
ABCFinance
PQRProduction
LMNSales
XYZMarketing
STUHuman Resource

Advantages of View

  • View provides data security for the same base tables.
  • It allows different users to view the same data in different ways at the same time.
  • It is used to represent additional information like derived columns.
  • It is used to hide complex queries.
  • It presents a consistent, unchanged image of the database structure, even if the tables are split or renamed.
  • It does not allow direct access to the tables of the data dictionary.

Disadvantages of View

  • We cannot use DML operations on View, if there is more than one table.
  • When table is dropped view becomes inactive.
  • View is a database object, so it occupies the space.
  • Without table, view will not work.
  • Updation is possible for simple view but not for complex views, they are read only type of views.
▲