What are SQL Views?
- Views are virtual tables in SQL. A view, like every other table in the database, has rows and columns.
- We can create a view by selecting fields from one or more tables in the database.
- A View can contain all of the rows of a table or particular rows based on a criterion.
CREATE VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE condition;
- view_name: Name for the View
- table_name: Name of the table
- condition: Condition to select rows
Types of View:
There are two types of view:
- Simple View: Simple views are the most fundamental sort of SQL view. They are built around a single table and get data with a simple SELECT statement. Simple views can be read-write or read-only, depending on whether the underlying database can be updated.
- Complex View: Complex views are views that are based on numerous tables or employ more complex SELECT queries to retrieve data. It can be used to link tables, aggregate data, and conduct other complex tasks. Complex views, like simple views, can be either read-write or read-only.
Creating a VIEW:
Consider the STUDENT table having the following records −
Query to Create a View from the above table:
CREATE VIEW Details AS SELECT NAME, ADDRESS FROM STUDENT WHERE ID < 3;
Note: The results of the SELECT statement will be saved in another object called Detail.
Displaying a VIEW:
To see the data in the View, we can query the view in the same manner as we query a table.
SELECT * FROM Details;
Here the “Details” is a view created in the previous example.
Force VIEW Creation:
In SQL, we can use the create VIEW statement with the FORCE option to build a VIEW even if the underlying table or tables do not exist. Syntax:
CREATE FORCE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition;
FORCE keyword is used to create the VIEW.
Update a VIEW:
In SQL, you can update a view using the
UPDATE statement in a similar way to updating a table. However, there are a few important considerations to keep in mind:
- Not all views can be updated. In general, a view can only be updated if it meets certain criteria, such as having a simple SELECT statement that references only one table, and not using certain constructs like aggregation functions or subqueries.
- If a view can be updated, the underlying table or tables will also be updated as a result. This means that we should use caution when updating a view, especially if it involves complex logic that could have unintended consequences.
UPDATE view-name SET column1=value1, column2=value2, ... WHERE condition;
A read-only view cannot be changed, added to, or removed. It is used to give a simplified or limited data interface without allowing changes to the underlying tables.
CREATE FORCE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition WITH read-only;
Note: Creating a read-only view is similar to creating a regular view, but with the addition of the
WITH READ ONLY clause.
Note: also read about SQL: Sequence
Please follow me to read my latest post on programming and technology if you like my post.
Staying up to the mark is what defines me. Hi all! I’m Rabecca Fatima a keen learner, great enthusiast, ready to take new challenges as stepping stones towards flying colors.