paint-brush
How to Create a View in SQL and Its Usesby@sandeepmishratech
729 reads
729 reads

How to Create a View in SQL and Its Uses

by Sandeep MishraOctober 11th, 2022
Read on Terminal Reader
Read this story w/o Javascript
tldt arrow

Too Long; Didn't Read

Views in Structured Query Language (SQL) are simply virtual table which is formed based on results obtained from the query. Views can be created using single tables and multiple tables. Views can also be updated, deleted, renamed, and inserted in rows. 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. We can specify which rows and which columns should be present in the views, which also depends on the condition we specify.
featured image - How to Create a View in SQL and Its Uses
Sandeep Mishra HackerNoon profile picture


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.

Scope of the Article:

  • In this article we will discuss the views, Views can be created and used as virtual tables.
  • Views can also be updated, deleted, renamed, and inserted in rows.
  • We will discuss the Creation of views with a single table and multiple tables.
  • We will know the different types of views and discuss the uses of Views in SQL.

Introduction:

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.


Creation of views in SQL:

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,

  • Creating views
  • Updating views
  • Deleting views
  • Renaming the views
  • Inserting rows and more operations.


With these operations, we can easily manage the views.


Types of views:

System Defined View

  1. Information Schema View
  2. Catalog View
  3. Dynamic Management View

User Defined View

  1. Simple View

  2. Complex View


Let us know about the uses of view,

Uses of Views in SQL:

  • Views in SQL are used to reduce the usage of tables.

  • A Proper database should contain views for -

    1. Restricting data access

    2. Storing Complex queries

    3. Hiding the data complexity occurred due to usage of multiple tables.

    4. 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.