SQL Views – A Comprehensive Guide

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 alongside DISTINCT keyword and ORDER BY clause.
• SQL SELECT statement should not contain set operators, set functions, and summary functions.
• The View query should not be used alongside HAVING and GROUP 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.

References

• SQL VIEWS – Documentation