The A - Z Guide Of SQL Views

Introduction

In this article, we will 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 with the underlying 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 the virtual table; to create a view, we can select the fields from one or more tables in the database. Like SQL tables, views can store 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 table records or a particular record from the table using conditional statements in SQL.

Creating a SQL view 

Below is the statement for creating a view in the 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 by inserting some data.

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. We can also modify the existing data and insert a new record in the view anytime. SQL will not allow updating the view already created with more than one table.

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, 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 ...!