Hey folks! In this article we will be focusing on SQL Views.
What is a View in SQL?
In SQL, a View can be considered as virtual access to the database in a restricted manner. SQL Views allow limiting the view access to a specific set of data for a set of users. You can create a view with a limited amount of data from one or more tables and columns.
Need of SQL Views
Views help the database administrator to provide restricted access to the tables of the database to other users/employees.
For example, consider a website’s login database that contains the data columns as ‘User_id’, ‘Password’, ‘Phone_num’ and ‘Name’. In case, if the database administrator happens to provide access to the System’s Tester(employee), he can do so by providing restricted/limited access to the user through SQL Views. He can limit the database access by just providing access to data columns such as ‘Name’ and ‘User_id’.
Thus, SQL Views helps in aligning the confidential data access through various restricted parameters.
Creating an SQL View
By creating a View, we decide the limit to provide access to the original database.
CREATE VIEW View_name AS SELECT columns FROM Table;
We use View alongside
SELECT statement to select the data columns to be made visible to the user.
We have created a Table (using data from our previous tutorial on SQL sum, average, and count functions) – ‘Info’ as seen below with data columns — ‘id’, ‘Cost’, ‘city’, and ‘Password’.
id Cost city Password 1 100 Pune 11**&! 2 50 Satara 1234$ 3 65 Pune qwerty123@ 4 97 Mumbai JAM1998# 5 12 USA 0000%!
Having created a Table, let us start with the creation of View on the database.
CREATE VIEW Info_review AS SELECT cost, city FROM Info; SELECT * from Info_review;
We have created a View as ‘Info_review’ over the Table – ‘Info’ by providing access to the data of the columns ‘Cost’ and ‘city’ using the SELECT statement.
100 Pune 50 Satara 65 Pune 97 Mumbai 12 USA
CREATE VIEW Info_review AS SELECT cost, city FROM Info WHERE city = "Pune"; SELECT * from Info_review;
In this example, we have created a SQL View using
SELECT statement and
WHERE clause by restricting the access such that only those data values are made available that belong to the city ‘Pune’.
100 Pune 65 Pune
Updating an SQL View
Updation in a View means to update or manipulate the pre-defined records in the database.
We need to follow the below conditions in order to Update the data of a View:
SQL SELECT statementshould not be used alongside
- SQL SELECT statement should not contain set operators, set functions, and summary functions.
- The View query should not be used alongside
- The query should not use
FROM clauseto fetch data from multiple columns.
- SQL View should contain all the
NON-NULL valuesof the original database.
UPDATE View-name SET column="value" WHERE condition;
CREATE VIEW Info_review AS SELECT id, city FROM Info; UPDATE Info_review SET city="Satara" WHERE id = 1; SELECT * from Info_review;
In the above example, we have updated the city value whose id = 1.
1 Satara 2 Satara 3 Pune 4 Mumbai 5 USA
Once an updation is made in the View, it immediately gets reflected in the table of the original database.
SELECT * from Info;
If we execute the above SELECT statement, we can see that the updation made in the View of id = 1 is reflected in the original Table – ‘Info’.
1 100 Satara 11**&! 2 50 Satara 1234$ 3 65 Pune qwerty123@ 4 97 Mumbai JAM1998# 5 12 USA 0000%!
Deletion of rows from SQL Views
Data rows can be deleted from SQL View by using the below command:
DELETE FROM View-name WHERE column= "value";
create table Info(id integer, Cost integer, city varchar(200),Password varchar(200)); insert into Info(id, Cost,city,Password) values(1, 100,"Pune","11**&!"); insert into Info(id, Cost,city,Password) values(2, 50, "Satara","1234$"); insert into Info(id, Cost,city,Password) values(3, 65,"Pune","qwerty123@"); insert into Info(id, Cost,city,Password) values(4, 97,"Mumbai","JAM1998#"); insert into Info(id, Cost,city,Password) values(5, 12,"USA","0000%!"); CREATE VIEW Info_review AS SELECT id, city FROM Info; DELETE FROM Info_review WHERE city = "USA"; SELECT * from Info_review;
1 Pune 2 Satara 3 Pune 4 Mumbai
Changes made in the View are reflected in the original table as seen below.
SELECT * from Info;
1 100 Pune 11**&! 2 50 Satara 1234$ 3 65 Pune qwerty123@ 4 97 Mumbai JAM1998#
As you may notice, the row containing USA is no longer available in both, the view and the original table.
Insertion of a row in SQL Views
Insertion of a data row in SQL View should follow all the conditions mentioned in the Updation of View section.
INSERT into View-name(columns) values(val1,..,valN)
INSERT into Info_review(id,city) values(12,'France'); SELECT * from Info_review;
1 Pune 2 Satara 3 Pune 4 Mumbai 5 USA 12 France
All the changes made in the SQL View are reflected in the original table as shown below.
SELECT * from Info;
1 100 Pune 11**&! 2 50 Satara 1234$ 3 65 Pune qwerty123@ 4 97 Mumbai JAM1998# 5 12 USA 0000%! 12 NULL France NULL
Dropping an SQL View
If we wish to delete the entire View along with the data and its schema, we need to use Drop statement.
Drop statement deletes the entire View from the memory along with its structure(schema).
DROP VIEW View-name;
DROP VIEW Info_review;
Having deleted the View, we can use the below SELECT statement to verify its deletion.
Select * from Info_review;
ERROR 1146 (42S02) at line 15: Table 'test.Info_review' doesn't exist
By this, we have come to the end of this topic. I believe that the concept is clear to all the readers.
Please feel free to comment in case you come across a doubt.