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.
Syntax:
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.
Example 1:
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.
Output:
100 Pune
50 Satara
65 Pune
97 Mumbai
12 USA
Example 2:
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’.
Output:
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 statement
should not be used alongsideDISTINCT
keyword andORDER BY
clause.- SQL SELECT statement should not contain set operators, set functions, and summary functions.
- The View query should not be used alongside
HAVING
andGROUP BY
clause. - The query should not use
FROM clause
to fetch data from multiple columns. - SQL View should contain all the
NON-NULL values
of the original database.
Syntax:
UPDATE View-name
SET column="value"
WHERE condition;
Example:
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.
Output:
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";
Example:
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;
Output:
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.
Syntax:
INSERT into View-name(columns) values(val1,..,valN)
Example:
INSERT into Info_review(id,city) values(12,'France');
SELECT * from Info_review;
Output:
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.
The Drop statement
deletes the entire View from the memory along with its structure(schema).
Syntax:
DROP VIEW View-name;
Example:
DROP VIEW Info_review;
Having deleted the View, we can use the below SELECT statement to verify its deletion.
Select * from Info_review;
Output:
ERROR 1146 (42S02) at line 15: Table 'test.Info_review' doesn't exist
Conclusion
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.
Great posts!
Excellent.. Great Job.