Working with views

Views in SQL databases are the virtual tables; any view in a database always sits on top of one or more tables. By definition, VIEW contain rows and columns from a SELECT statement from the real table; it could be a combination of one or more tables:

As you can see in the figure, VIEW contains 1 column from TABLE A and two from TABLE B. Tables are joined using a join between two tables.

There are a few benefits when it comes to view creation, and they are as follows:

• Provides a security mechanism
• Can hide the complexity of the table in a view
• Data in the view gets auto-updated whenever the underlying table gets changed
• Statistics defined on the table are available to views also

As a best practice, it is recommended to create a separate database containers for views. As shown in the screenshot, we have two separate containers for tables and views:

The following is the syntax for creating a view:

/*View creating syntax*/
CREATE/REPLACE VIEW <viewname> 
AS  
<select query...