The A - Z Guide Of SQL Views

Introduction

In this article, we are going to learn about the SQL database concept called views and how to manage views from the database level such as creating a new view, removing a view, and updating the view that with underlying the data.

Table of contents

• What are views in SQL?
• Creating a View
• Update an SQL View
• Inserting records into SQL View
• Conclusion

What are views in SQL?

Views in SQL are considered as the virtual table and to create a view, we can select the fields from one or more tables that are present in the database. Like the SQL tables, views have the capability of storing the data in the format of rows and columns, but the rows do not have any physical existence in the database. A view can store either all the records of the table or a particular record from the table using the conditional statements in SQL.

Creating a SQL view 

Below is the statement for creating a view in SQL database.

Create View View_Name As
  Select Column1, Column2 ..... ColumnN From Table_Name
      Where Condition;

Let's deep dive into this with a detailed example, Create a sample table with inserting some data into it

SQL Table with Sample Data

CREATE TABLE  "AGENTS"
(
    "AGENT_CODE" NVARCHAR(10) NOT NULL PRIMARY KEY,
	"AGENT_NAME" NVARCHAR(30),
	"WORKING_AREA" NVARCHAR(30),
	"COMMISSION" bigint,
	"PHONE_NO" NVARCHAR(20),
	"COUNTRY" NVARCHAR(20)
);

Add some Data to the newly created table through query 

INSERT INTO AGENTS VALUES ('A007', 'Ramasundar', 'Bangalore', 1, '077-25814763', null);
INSERT INTO AGENTS VALUES ('A003', 'Alex ', 'London', 2, '075-12458969', '');
INSERT INTO AGENTS VALUES ('A008', 'Alford', 'New York', 3, '044-25874365', '');
INSERT INTO AGENTS VALUES ('A011', 'Ravi Kumar', 'Bangalore', 4, '077-45625874', '');
INSERT INTO AGENTS VALUES ('A010', 'Santakumar', 'Chennai', 5, '007-22388644', null);
INSERT INTO AGENTS VALUES ('A012', 'Lucida', 'San Jose', 6, '044-52981425', '');
INSERT INTO AGENTS VALUES ('A005', 'Anderson', 'Brisban', 7, '045-21447739', null);
INSERT INTO AGENTS VALUES ('A001', 'Subbarao', 'Bangalore', 8, '077-12346674', '');
INSERT INTO AGENTS VALUES ('A002', 'Mukesh', 'Mumbai', 9, '029-12358964', null);
INSERT INTO AGENTS VALUES ('A006', 'McDen', 'London', 10, '078-22255588', '');
INSERT INTO AGENTS VALUES ('A004', 'Ivan', 'Torento', 11, '008-22544166', null);
INSERT INTO AGENTS VALUES ('A009', 'Benjamin', 'Hampshair', 12, '008-22536178', '');

Agent_View Syntax

create view Agent_View As 
select agent_code,agent_name,commission from AGENTS
   where COMMISSION > 5;

Displaying a View

select * from agent_view

Output

Update an SQL View

We can update the view which depends on one table. SQL will not allow updating the view which is already created with more than one table. We can also modify the existing data and insert new record in the view at any time.

Update Syntax

-- Update SQL View Syntax
Alter View View_Name As 
  Select Column1,Column2,.... ColumnN from Table_Name
   Where condition;

Example

-- Update SQL View Syntax
ALTER view Agent_View As 
  Select agent_code,Agent_name,commission,WORKING_AREA from AGENTS
   Where WORKING_AREA = 'Bangalore';

Output

Inserting records into SQL View

Query Syntax

-- Insert Statement for Sql View
Insert into View_Name (Column1,Column2, .......ColumnN)
 Values(data1,data2,.....dataN);

Example

-- Insert Statement for Sql View
INSERT into Agent_View(agent_code,agent_name,commission,WORKING_AREA)
values('A013','Test',13,'Sweden');

Output

Before executing the result make sure to remove the where condition from the existing view i.e Alter the view again otherwise you won't be able to see the latest inserted records.

Conclusion

Thank you for reading, I hope this article gives you a brief idea about SQL Views with examples and code samples.

Please let me know your questions, thoughts, or feedback in the comments section. I appreciate your feedback and encouragement.

Keep learning ...!