coderz.py

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.

Syntax:

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

where,

• 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 −

ID	NAME	ADDRESS
1	Stephan	Delhi
2	Kathrin	Noida
3	David	Ghaziabad
4	Alina	Gurugram

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;

here the 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:

1. 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.
2. 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.

Syntax:

UPDATE view-name
SET column1=value1, column2=value2, ...
WHERE condition;

Read-Only VIEW:

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.

Syntax:

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

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py