Home
/
Blog
/
Everything You Need to Know About Views in SQL

Everything You Need to Know About Views in SQL

A view in Oracle SQL is a virtual table. It is just a mirror image of the original table and users can use it as a table in their SQL queries. Users can always fire the INSERT, UPDATE, DELETE and MERGE SQL statements. The only difference between a view and an original table is that a view does not store any data. It has columns with various data types.

Summary

Rules for Creating Views
How to create a view?
How to join Views in SQL?
How to replace Views?
How to drop Views?
Conclusion

In other words, a view is a legitimate copy of another table or union of tables. A view obtains its data from the tables. These tables are described as base tables and these base tables sometimes could be real tables or they might be views themselves. All actions executed on a view really modify the base table of the view. Users can utilize views in the same procedure as tables.

Rules for Creating Views

There are certain rules and requirements for creating views. These are as follows:

To create a view, the user must have the CREATE VIEW privilege and if a particular user wants to create a view in some other user’s schema then the CREATE ANY VIEW system privilege is necessary.
If the owner of the view plans to allow access to other users then the object privileges such as GRANT OPTION or ADMIN OPTION are necessary.

How to create a view?

The views can be created by using a create view statement. Now, each view is described by a query that specifies tables. As with all subqueries, the query that specifies a view cannot include the FOR UPDATE condition.

The following statement creates a view on the emp table:

CREATE VIEW my_empview AS
      SELECT empno, ename, deptno
      FROM emp
      WHERE deptno = 20
    WITH CHECK OPTION CONSTRAINT dept_loc_cnst;

Now, the statement that describes the my_empview references only rows in department 20. Moreover, the CHECK OPTION generates the view with the limitation (called dept_loc_cnst) that INSERT and UPDATE statements declared upon the view cannot appear in rows that the view cannot select. For instance, the following INSERT statement favorably inserts a row into the table that includes all rows with department 20:

INSERT INTO my_empview VALUES (7567, 'RICKY', 20);

Still, the following INSERT statement gives an error because it tries to insert a row for department number 10:

INSERT INTO my_empview VALUES (7539, 'IAN', 10);

The above view could have been created defining the WITH READ ONLY condition, which limits any updates, inserts, or deletes from being performed to the main table within the view. If no WITH clause is defined, the view, with some limitations, is naturally updatable.

How to join Views in SQL?

The great thing about views is that they can be joined in your schema. For example, users can create views that define two or more tables or views in the FROM clause. The views that include two or more tables are called join views. The following example is displaying the join views of EMP and DEPT tables.

CREATE VIEW myjoin_view AS
      SELECT ename, eno, job, dname
      FROM emp, dept
      WHERE emp.deptno IN (20, 30)
         AND emp.deptno = dept.deptno;

How to replace Views?

In SQL, Views can be replaced too. If you want to replace a view then privileges to drop and create a view are necessary. Users can’t use the ALTER statement to change the definition and description of the view. The following example is displaying how to replace a view.

CREATE OR REPLACE VIEW my_view AS
     SELECT empno, ename, deptno, job, salary
     FROM emp
     WHERE deptno = 20
     WITH CHECK OPTION CONSTRAINT my_emp_cnt;

You can restructure the view with a CREATE VIEW statement that includes the OR REPLACE condition. The OR REPLACE condition substitutes the prevailing description of a view and maintains the current security permissions. For instance, imagine that you designed the my_view as explained earlier, and, also, you gave various object privileges to other users. Still, now you want to restructure the my_view to alter the department number defined in the WHERE condition. You can substitute the prevailing version of the my_view with the above SQL statement.

How to drop Views?

In Oracle SQL, the view can be dropped from any schema. The DROP ANY View system privilege is necessary to drop any from your schema or any other schema. To drop a view, the user can simply fire the DROP VIEW statement. For instance, the following statement drops the my_view from the schema:

DROP VIEW my_view;

Conclusion

A view is utilized for security design in the database and works as a middle man between real tables schema & functionality. It also limits the user from seeing specific columns and the row of the table. Views give data autonomy as the application relies on a view but not on a base table. Consequently, any modification in the table will not change the application, and an application will ever be autonomous of the table schema configuration.