Logicmojo - Updated Jan 13, 2024
Introduction
A database is a collection of tables that contain useful information. All of the tables are linked by some kind of relation. However, the basic point here is why we need to generate so many tables rather than simply one. Rather than repeating the same data in a single table, many tables allow for the orderly storage of vast volumes of data. However, we cannot deny that if we wish to query some data from this database, we must combine all relevant tables into a single table.
Structured Query Language (SQL) makes use of a variety of data structures, with tables being one of the most common. Tables, on the other hand, have some limits. You can't, for example, restrict visitors to merely seeing a portion of a table. Access to a complete table, not just a few columns inside it, must be allowed to a user.
A view in SQL is a virtual table whose contents are the result of a specified query to one or more underlying tables. This guide gives an overview of SQL views and why they are valuable. It also shows how to use normal SQL syntax to create, query, change, and destroy views.
What Is SQL View?
A view is nothing additional than a SQL statement with a name that is saved in the database. A view is essentially a table in the form of a preset SQL query. A view can contain all of the rows in a table or only a subset of the rows in a table. A view can be built from one or more tables, depending on the SQL query used to build the view.
Views, a form of virtual table, enable users to achieve the following:
Data should be structured in a way that consumers or groups of users find simple or straightforward.
Restriction access to the data so that a user can only see and (sometimes) edit what they require.
Data from several tables can be summarized and utilized to make reports.
What is the Work of a SQL View?
In the database, a table (concrete table) holds its data in columns and rows. A view (virtual table) is constructed on top of the concrete table(s) from which it retrieves data and does not store any data of its own in the database. A view just contains the SQL query used to retrieve the data.
To summarize, the result set of a view is not materialized on disk, and the query saved by the view is executed each time the view is invoked. To understand the distinction between concrete and virtual tables, consider the diagram below.
CREATING VIEWS In SQL
The construct VIEW statement is used to construct database views. A single table, numerous tables, or another view can be used to construct a view. A user must have the required system privileges for the specific implementation in order to create a view.
Syntax
CREATE VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];
The CREATE VIEW command generates the view view_name. Following the AS clause is a SQL query that will be saved in a view.
You can include numerous tables in your SELECT statement in the same manner that you would in a standard SQL SELECT query.
Sample Table for Queries
Creating SQL View From Single Table
Consider the Employee Experience table, which contains the following data:
Now, We will create a view named “Employee_View” from the table “Employee Experience” (Table 1) for querying about the Experience of the Employee in the Company, to show in the Output.
Query
CREATE VIEW Employee_View AS SELECT Name, Experience FROM EmployeeExperience WHERE EID < 6;
You can now query Employee_View in the same way that you would a table. The following is an example of this.
SELECT * FROM Employee_View;
Outout
Now, We will create a view named “Employee_ID” from the table “Employee Experience” (Table 1) for querying about the Experience of the Employee in the Company, to show in the Output.
Query
CREATE VIEW Employee_ID AS SELECT EID, Name FROM EmployeeExperience WHERE Experience < 3;
You can now query Employee_ID in the same way that you would a table. The following is an example of this.
SELECT * FROM Employee_ID;
Output
Creating A View From Multiple Table
Consider the Employee Experience table and Employee Details table, which contains the following data:
Creating a view from several tables is as simple as including numerous tables in the SELECT statement. Now, We will create a view named “Employee_Salary” from the tables “Employee Experience” (Table 1)and "Employee Details" (Table 2)
Query
CREATE VIEW Employee_Salary AS SELECT EmployeeExperience.Name, EmployeeExperience.Experience, EmployeeDetails.Salary FROM EmployeeExperience, EmployeeDetails WHERE EmployeeExperience.EID = EmployeeDetails.EID;
You can now query Employee_View in the same way that you would a table. The following is an example of this.
SELECT * FROM Employee_Salary;
Output
UPDATING VIEWS In SQL
The CREATE OR REPLACE VIEW statement can be used to update a view.
Certain conditions must be met in order to update a view. If any of these conditions is not met, we will be unable to refresh the view.
The keyword DISTINCT may not appear in the SELECT clause.
Summary functions are not permitted in the SELECT clause.
Set functions are not permitted in the SELECT clause.
Set operators are not permitted in the SELECT clause.
An ORDER BY clause is not permitted in the SELECT clause.
Multiple tables are not permitted in the FROM clause.
Subqueries are not permitted in the WHERE clause.
GROUP BY and HAVING cannot be used in the query.
Calculated columns are not guaranteed to be updated.
In order for the INSERT query to work, all NOT NULL fields from the base table must be included in the view.
If the statement is specified in the create view, then every row changed or inserted must adhere to the definition of the view. (the condition specified).
If the WITH CHECK OPTION is included in the CREATE Statement and the update does not match the condition in the Create statement, an error message will be displayed.
Views were created to simplify data delivery and reduce the complexity of numerous tables. Views hide the database's complexity by joining and simplifying many tables into a single virtual table that is easier for a user to grasp.
Views, like virtual tables, need very little storage because the database only contains a view's statements (definition) and not a copy of all the tables the view creates. For example, even if we create numerous views, they will still take up little space in the database when compared to a single real table.
Views allow users to pick information from various tables without having to know how to do a join.
Views protect the data by acting as a security mechanism.Views enable us to hide/show some table data based on requirements and security. We can hide some data for a specific query using criteria.
As the DBMS translates our request through the views, we may quickly edit the data in the virtual tables (views).
Views also preserve data integrity by presenting a consistent and correct view of the data from the database even when the underlying source is restructured, renamed, or divided.
Syntax
CREATE OR REPLACE VIEW view_name AS SELECT column1,column2,.. FROM table_name WHERE [condition];
If a view with the name view_name already exists with this syntax, the database system will change it to represent the data returned by the new SELECT statement. If a view with that name does not already exist, the DBMS will build one.
Example
If we want to replace the view, Employee_Salary and include the field Age from the Employee Details Table, we may do so as follows:
Query
CREATE VIEW Employee_Salary AS SELECT EmployeeExperience.Name, EmployeeExperience.Experience, EmployeeDetails.Salary, EmployeeDetails.Age FROM EmployeeExperience, EmployeeDetails WHERE EmployeeExperience.EID = EmployeeDetails.EID;
If we want to look at the data in Employee_Salary, we may use the following query.
SELECT * FROM Employee_Salary;
Output
Inserting a row in a SQL View
A view can be populated with rows of data. The same rules apply to the INSERT command as they do to the UPDATE command. To insert a row into a View, we can use SQL's INSERT INTO statement.
Syntax
INSERT INTO view_name(column1, column2 , column3,..) VALUES(value1, value2, value3..);
Example
In the following example, we will insert a new row into the View Employee_View that we built before in the "Creating SQL View From Single Table" example.
INSERT INTO Employee_View(Name, Experience)
VALUES("Rajat", 34);
If we want to look at the data in Employee_View, we may use the following query.
SELECT * FROM Employee_View;
Output
Deleting a row in a SQL View
A view's rows of data can be erased. The DELETE command follows the same principles as the UPDATE and INSERT commands.
Syntax
DELETE FROM view_name WHERE condition;
Example
In the following instance, we will delete the last row from the view Employee_View that we just added in the previous inserting rows example.
!-- HTML generated using hilite.me -->DELETE FROM Employee_View WHERE Name="Rajat";
This would eventually delete a record from the base table Employee Experience, which would be reflected in the view. Try querying the base table again, and the SELECT operation will yield the following result.
SELECT * FROM Employee_View;
Output
Deleting SQL Views(Dropping a View)
We've learnt about establishing a View, updating a row in a view, and putting a row in a view, but what if a previously constructed View is no longer required? Obviously, we'll want to get rid of it. We can delete an existing View using SQL. Using the DROP statement, we can delete or remove a View.
Syntax
DROP VIEW view_name;
Example
In the following example we will delete or drop the Employee_View from the Employee Experience Table.
DROP VIEW Employee_View;
WITH CHECK OPTION
The clause "WITH CHECK OPTION" is a very handy statement for views. It aids in the accurate and continuous updating of perspectives. (This is an optional clause in the CREATE View statement.) An example will help us understand how this works. Before proceeding, consider the following points.
Example
In the following example, we create a View EmployeeView from the Employee details Table using the WITH CHECK OPTION clause.
CREATE VIEW EmployeeView AS SELECT NAME, Age FROM Employee_details WHERE NAME IS NOT NULL WITH CHECK OPTION;
If we try to insert a new row in this View with a null value in the NAME column, we will get an error because the view was built with the condition for the NAME column set to NOT NULL. For example, even though the View is updatable, the following query for this View is invalid:
INSERT INTO EmployeeView(Age) VALUES(36);
Because the NAME column's default value is null, the error will be displayed in the output.
Uses of a SQL View
A appropriate database should include views for the following reasons:
Conclusions
This article discusses Views in SQL in detail, including appropriate codes for a better understanding. We've gone over everything from view operations to view types. We also learnt how to make Views from Single and Multiple Tables, as well as the advantages of using views. Try using such views to increase your SQL coding experience and practice comparable tasks to thoroughly comprehend the syntax.
Good luck and happy learning!