We all know that SQL is a Structured Query Language used for storing, updating, manipulating and retrieving data from databases or tables. We can simply say that it is used to communicate with a database. Views in Structured Query Language (SQL) are simply a virtual table which is formed based on results obtained from the SQL Query can be created using both single tables and also with multiple tables.
As said before, views in SQL are something like a virtual table or we can say it is a logical table which means views don't exist in reality, so not require any space in the database as we said views are tables, Views also have rows and columns same as the original table.
Views can be created using the tables in the database, we can use one table or more than one table to create the views depending on the condition we want to make the views.
We can specify which rows and which columns should be present in the views, which also depends on the condition we specify.
We can store the data in many/multiple tables, but the problem here is retrieving data, which needs the specification of multiple tables. We use these views to avoid using multiple tables and save space in the database.
These views manage data very easily from relevant tables quietly.
Now let us see the creation of Views.
Views in SQL can be created using the keyword CREATE VIEW
as the syntax.
Syntax to create a view in SQL:
CREATE VIEW viewname AS
SELECT column_1, column_2, .....column_n
FROM table_1, table_2, .....table_n
WHERE some_condition;
To retrieve data or to view data present in the created view use the SELECT statement
:
SELECT * FROM viewname;
Let us discuss the syntax to create a view in brief.
→ Firstly, we start with CREATE VIEW which is the keyword to start the creation of view then we have to keep a name to the view we are creating (viewname).
→ Next, Select the particular columns from particular tables and then use some conditions. To define them use the WHERE clause.
Let us discuss the example to create a view from the employee table.
Table name: employees
Id |
Employee_name |
Age |
Address |
Salary |
---|---|---|---|---|
1 |
Ramu |
25 |
Delhi |
2000 |
2 |
Sita |
23 |
Mumbai |
3000 |
3 |
John |
36 |
Hyderabad |
15000 |
4 |
Raju |
27 |
Bangalore |
6500 |
5 |
Muffy |
22 |
Bhopal |
8500 |
6 |
Roshan |
32 |
Maharashtra |
8000 |
7 |
Komal |
28 |
Kota |
2000 |
8 |
Mary |
25 |
MP |
4500 |
Creation of view from employees’ table,
CREATE VIEW view1 AS
SELECT *
FROM employees;
We created a view that has the same data as the employee’s table, let us check the view using the
SELECT statement:
SELECT * FROM view1;
The result would be:
Id |
Employee_name |
Age |
Address |
Salary |
---|---|---|---|---|
1 |
Ramu |
25 |
Delhi |
2000 |
2 |
Sita |
23 |
Mumbai |
3000 |
3 |
John |
36 |
Hyderabad |
15000 |
4 |
Raju |
27 |
Bangalore |
6500 |
5 |
Muffy |
22 |
Bhopal |
8500 |
6 |
Roshan |
32 |
Maharashtra |
8000 |
7 |
Komal |
28 |
Kota |
2000 |
8 |
Mary |
25 |
MP |
4500 |
We can create a view with selected columns as well for example,
Creation of view from employees’ table,
CREATE VIEW view2 AS
SELECT Employee_name, Age
FROM employees;
Let us check the view using the SELECT statement
:
SELECT * FROM view2;
The result would be:
Employee_name |
Age |
---|---|
Ramu |
25 |
Sita |
23 |
John |
36 |
Raju |
27 |
Muffy |
22 |
Roshan |
32 |
Komal |
28 |
Mary |
25 |
Creation of view can also be done using WITH CHECK OPTION
,
Use of WITH CHECK OPTION
is to ensure update, and insert statements satisfy the conditions in the view creation statement.
Example of creating same view view2 with WITH CHECK OPTION
,
CREATE VIEW view3 AS
SELECT Employee_name, Age
FROM employees
WHERE Age IS NOT NULL
WITH CHECK OPTION;
Here as per the query written, it checks whether Age is null or not and returns Age which is not null i.e., ignores the rows which have Age as NULL.
Till now we saw the creation view with a single table, Let us now see the creation of a view with multiple tables.
Views can be created using multiple tables, simply using multiple tables in the select statement.
Example:
Table1 students
Id |
Student_Name |
Address |
Branch |
---|---|---|---|
1 |
Ramu |
Delhi |
CSE |
2 |
Sita |
Mumbai |
ECE |
3 |
John |
Hyderabad |
EEE |
4 |
Raju |
Bangalore |
CSE |
5 |
Muffy |
Bhopal |
IT |
Table2 marks
Id |
Student_Name |
Branch |
Marks |
---|---|---|---|
1 |
Ramu |
CSE |
97 |
2 |
Sita |
ECE |
95 |
3 |
John |
EEE |
85 |
4 |
Raju |
CSE |
77 |
5 |
Muffy |
IT |
79 |
Creation of view from students’ table and marks table,
CREATE VIEW marks_view AS
SELECT students.Student_Name, students.Address, marks.Marks
FROM students, marks
WHERE students.Student_Name = marks.Student_Name;
Let us check the view using the SELECT statement
:
SELECT * FROM marks_view;
The result would be,
Student_Name |
Address |
Marks |
---|---|---|
Ramu |
Delhi |
97 |
Sita |
Mumbai |
95 |
John |
Hyderabad |
85 |
Raju |
Bangalore |
77 |
Muffy |
Bhopal |
79 |
For managing views, we have different aspects,
With these operations, we can easily manage the views.
Simple View
Complex View
Let us know about the uses of view,
Views in SQL are used to reduce the usage of tables.
A Proper database should contain views for -
Restricting data access
Storing Complex queries
Hiding the data complexity occurred due to usage of multiple tables.
Renaming Columns
Views in SQL allow the user to select required information from multiple tables without using the join operation.
Views in SQL provide additional Security by restricting access.
Views hide the complexity which comes from usage of multiple tables.
Views have the capability to store the large queries.
Renaming columns mean without affecting the original table used to create a view, we can rename columns in view which can be done by specifying in the SELECT statement.
We can create different views by using the same table for different reasons or for different users.
Views in SQL use ZERO Space in the database, so that our database is free of space, it doesn't copy the data into the database.
It can easily update, delete the rows from the view which is called a virtual table.
Views help in improving performance and to partition data.