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.
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;
| Ename | DeptName |
|---|---|
| ABC | Finance |
| PQR | Production |
| LMN | Sales |
| XYZ | Marketing |
| STU | Human 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.
