What is SQL View?
A view is a virtual table, which gets saved in the database after you create a view. It is a result of SQL statements which is used while creating a view.
A view contains rows and columns, just like a real table. These rows and columns are nothing but rows and columns from one or more real table.
The data belongs to the view are not physically stored in the view, whenever you execute a view it fetched the data from the table in the database.
Following are syntax and example of SQL view creation.
/*Syntax of View creation*/
CREATE VIEW view_name AS
SELECT column1, column2 FROM table_name;
Once you have created a view in a database, you can use below SQL query to see the data of the view.
/*Syntax to see data of view*/
SELECT * FROM view_name;
Following are some benefits of View which defines the importance of view in SQL.
- Using view, we can make a summary of data which come from one or more tables which can be used to generate reports.
- To format and structure the data such a way that user or business want.
- A view always shows up-to-date data. Whenever user queries a view, the database engine recreates the data, using the view's SQL statement.
- Views show only those columns that are present in the query which is used to make a view.
- Restrict access to the data in such a way that a user can only access particular view, not all the tables present in database.
How to create a view from multiple tables?
Yes, it is possible to create a view from multiple tables, for that you have to do a joins between tables. Following is the example of view creation using join which creates view from more than one table.
CREATE VIEW student_data
SELECT student_name, roll_no, english_marks, math_marks
FROM student INNER JOIN marks
ON student.roll_no=marks.roll_no;
You can use different types of join as per the requirement.
Is it possible to insert data in view?
No, you can not insert data directly in the view but using view you can insert data in table. Following are some conditions, if that satisfied then only data get inserted in view (nothing but table).
- The SELECT clause may not contain the keyword DISTINCT
- The SELECT clause may not contain summary functions
- The SELECT clause may not contain set functions
- The SELECT clause may not contain set operators
- The SELECT clause may not contain an ORDER BY clause
- The FROM clause may not contain multiple tables
- The WHERE clause may not contain sub queries
- The query may not contain GROUP BY or HAVING
- Calculated columns may not be updated
- All NOT NULL columns from the base table must be included in the view in order for the INSERT query to function
How to drop a view from database?
To drop a view from database, there is a standard SQL command, using this the view gets dropped permanently from the database. Following is the Syntax and example for dropping view.
Syntax
DROP VIEW view_name;
Example
DROP VIEW student_data;
What is Materialized view in Oracle & PostgreSQL?
The Materialized view is same as simple view, the only difference is, it is storing data physically in the database whereas simple view doesn't. It is a physical copy of the base table. You cannot insert data in the materialized view. To update the content of the materialized view, you can execute a refresh query. This will execute the query which is used to create materialized view. The benefits of the materialized view are fast retrieval of aggregated data and improve the view performance.
