SQL Views

SQL Views, What is a SQL View, Uses of Views, Creating Views, Modifying Views, Dropping views, and Advantages and Disadvantages of SQL Views.

SQL Views

Introduction:

A VIEW is a virtual table, through which a selective portion of the data from one or more tables can be seen. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity. A view is stored as a SELECT statement in the database. DML operations on a view like INSERT, UPDATE, DELETE affects the data in the original table upon which the view is based.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. A view is a specific representation of data from one or more tables. The tables referred in the views are known as Base tables. Creating a view does not take any storage space as only the query is stored in the data dictionary and the actual data is not stored anywhere.

Uses of Views:

The reasons for using views in applications can be many like,

• Reducing complexity.

• Security is increased – sensitive information can be excluded from a view

• Renaming the table columns- by giving the different names to columns while creating views.

• Views can represent a subset of the data contained in a table.

• Views can join and simplify multiple tables into a single virtual table.

• Views take very little space to store; the database contains only the definition of a view, not a copy of all the data it presents.

• Different views can be created on the same base table for different users.

Creating Views:

Database views are created using the CREATE VIEW statement. Views can be created from a single table, multiple tables, or another view.

To create a view, a user must have the appropriate system privilege according to the specific implementation.

Syntax: The basic CREATE VIEW syntax is

CREATE VIEW view_name AS
SELECT column1, column2…..
FROM table_name
WHERE [condition];

You can include multiple tables in your SELECT statement in very similar way as you use them in normal SQL SELECT query.

Example: SQL > CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM CUSTOMERS;

The WITH CHECK OPTION: The WITH CHECK OPTION is a CREATE VIEW statement option. The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERTs satisfy the condition(s) in the view definition. If they do not satisfy the condition(s), the UPDATE or INSERT returns an error.

The following is an example of creating same view CUSTOMERS_VIEW with the WITH CHECK OPTION:

CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM CUSTOMERS
WHERE age IS NOT NULL
WITH CHECK OPTION;

The WITH CHECK OPTION in this case should deny the entry of any NULL values in the view’s AGE column, because the view is defined by data that does not have a NULL value in the AGE column.

When a view is created, the name of the view is stored in the sysobjects table. Information about the columns defined in a view is added to the syscolumns table, and information about the view dependencies is added to the sysdepends table. In addition, the text of the CREATE VIEW statement is added to the syscomments table. This is similar to a stored procedure; when a view is executed for the first time, only its query tree is stored in the procedure cache. Each time a view is accessed, its execution plan is recompiled.

Modifying Views:

You can use the OR REPLACE option to modify the view. If the view exists it will be replaced with the new definition or a new view will be created. We can use Create or Replace option to create views instead of dropping the view and recreating it as with this option the privileges granted on the view are preserved, but the dependent stored programs and view become invalid.
The view will become invalid whenever the base table is altered. We can recompile a view using the Alter view statement, but oracle automatically recompiles the view once it is accessed. On recompiling the dependent objects become invalid.

Syntax: SQL> ALTER VIEW View Name;

Example 1:

ALTER VIEW Stu_View (Stu_id, Stu_Name, Stu_Class)
AS SELECT stu_id, stu_name, stu_class
FROM Stu_Table where Stu_Id <=5
Example 2:

SQL > UPDATE CUSTOMERS_VIEW
SET AGE = 35
WHERE name=’Ramesh’;

Inserting Rows into a View: Rows of data can be inserted into a view. The same rules that apply to the UPDATE command also apply to the INSERT command.

Deleting Rows into a View:

Rows of data can be deleted from a view. The same rules that apply to the UPDATE and INSERT commands apply to the DELETE command.

Following is an example to delete a record having AGE= 22.

Example:
SQL > DELETE FROM CUSTOMERS_VIEW
WHERE age = 22;

This would ultimately delete a row from the base table CUSTOMERS and same would reflect in the view itself.

Dropping view:

The DROP VIEW statement is used to remove a view or an object view from the database. You can change the definition of a view by dropping and re-creating it. The view must be in your own schema or you must have the DROP ANY VIEW system privilege.

You may drop a view permanently when it is no longer useful or temporarily. If the view is not useful it could be dropped. Also, if a view needs to be changed it would be dropped and then created again with changes in place.

Syntax: DROP VIEW view_name;

When a view becomes obsolete, it can be removed from the system with the DROP command.

Example:

SQL> DROP VIEW EXPENSES;

view dropped.

Advantages of views:

Security: Each user can be given permission to access the database only through a small set of views that contain the specific data the user is authorized to see, thus restricting the user’s access to stored data.

Query Simplicity: A view can draw data from several different tables and present it as a single table, turning multi-table queries into single-table queries against the view.

Structural simplicity: Views can give a user a “personalized” view of the database structure, presenting the database as a set of virtual tables that make sense for that user.

Consistency: A view can present a consistent, unchanged image of the structure of the database, even if the underlying source tables are split, restructured, or renamed.

Data Integrity: If data is accessed and entered through a view, the DBMS can automatically check the data to ensure that it meets the specified integrity constraints.

Logical data independence: View can make the application and database tables to a certain extent independent. If there is no view, the application must be based on a table. With the view, the program can be established in view of above, to view the program with a database table to be separated.

Disadvantages of views:

Performance: Views create the appearance of a table, but the DBMS must still translate queries against the view into queries against the underlying source tables. If the view is defined by a complex, multi-table query then simple queries on the views may take considerable time.

Update restrictions: When a user tries to update rows of a view, the DBMS must translate the request into an update on rows of the underlying source tables. This is possible for simple views, but more complex views are often restricted to read-only.


SQL Video Tutorial for Beginners

SQL Syllabus

SQL Queries for Software Testers

Follow me on social media: