An Ultimate Guide to View in SQL With Examples

How to Create SQL Views: Simple Examples

We can easily create a view in SQL from a single table or even multiple tables using the CREATE VIEW statement. But before anything else, we will create two client-related tables that we will use throughout this article. We will also insert some values in these tables.

The first table we create will be the “Clients” table. This table will contain all the basic information about the client such as ID, Name, and Email_Id.

CREATE TABLE Clients(

ID INT PRIMARY KEY,

   Name VARCHAR(20),

   Email_Id NVARCHAR(20)

);

INSERT INTO Clients VALUES (1, 'George', 'ge.com');

INSERT INTO Clients VALUES (2, 'David', 'da.com');

INSERT INTO Clients VALUES (3, 'Chirs', 'ch.com');

INSERT INTO Clients VALUES (4, 'Morrison', 'mo.com');

INSERT INTO Clients VALUES (5, 'Brian', 'br.com');

SELECT * FROM Clients;

Output:

The second table we create will be the “Clients_Location” table. As the name suggests , this table will have information about the clients’ location, including ID, Country, and Country_Code.

CREATE TABLE Clients_Location(

ID INT PRIMARY KEY,

Country VARCHAR(20),

Country_Code VARCHAR(5)

);

INSERT INTO Clients_Location VALUES (1, 'INDIA', 'IND');

INSERT INTO Clients_Location VALUES (2, 'SPAIN', 'ESP');

INSERT INTO Clients_Location VALUES (3, 'FRANCE', 'FRA');

INSERT INTO Clients_Location VALUES (4, 'ENGLAND', 'ENG');

INSERT INTO Clients_Location VALUES (5, 'POLAND', 'POL');

SELECT * FROM Clients_Location;

Output:

Now that we have our tables ready, we can get back to creating a view in SQL.

The Syntax for Creating a View in SQL

The syntax for creating a view is:

CREATE VIEW view_name AS

SELECT col1, col2, ...

FROM table_name

WHERE condition; --(optional)

We can use the above  syntax to create a view from single as well as multiple tables. Once the view is created, we can use the same SELECT statement, we use with tables to get the results.

Your Data Analytics Career is Around The Corner!

Data Analyst Master’s ProgramExplore Program

Creating a View from a Single Table

To begin, we will create a simple view from our Clients table.

CREATE VIEW V_Clients

AS

SELECT * FROM Clients;

This view will store all the columns from the Clients table. Let’s execute it with the select statement and see the results.

SELECT * FROM V_Clients;

Output:

Creating a View from Multiple Tables

We can also create views from multiple tables. In this example, we will create a view that will store ID, Name, Country, and Country_Code columns’ data for all five clients.

CREATE VIEW V_Clients_Loc_All

AS

SELECT Clients.ID, Clients.Name, Clients_Location.Country, Clients_Location.Country_Code

FROM Clients, Clients_Location

WHERE Clients.ID = Clients_Location.ID;

Now, let’s execute the view to see the table that it stores.

SELECT * FROM V_Clients_Loc_All;

Output:

How to Insert, Update, and Delete Records in a View in SQL

Like actual tables, we can easily insert, update, and delete rows in a view that is not allowed in stored procedures.

Inserting a New Row in a View

For the inserting example, we will use the V_Clients view that we had created earlier. The view already had three rows, and we will insert the fourth one using the INSERT INTO statement.

INSERT INTO V_Clients VALUES (6, 'Aakash', 'update_later');

SELECT * FROM V_Clients;

Output:

As we can see in the output, another row is inserted in the view, which confirms that the insert statement was executed successfully.

Updating a Row in a View in SQL

Like inserting, we can also update a row in a view. If you notice the previous example where we inserted a new row, we have inserted the Email_Id column value as ‘update_later.’ That’s what we will be updating here.

UPDATE V_Clients SET

Email_Id = 'aa.com'

WHERE ID = 6;

SELECT * FROM V_Clients;

Output:

As we can see, the Email_Id value was updated to ‘aa.com.’ Thus, it is confirmed that the update statement was executed successfully.

Deleting a Row in a View

Just like how we inserted and updated a row, we can also delete one using the DELETE statement. For this example, we will delete the row with ID=6 using the WHERE condition.

DELETE

FROM V_Clients

WHERE ID = 6;

SELECT * FROM V_Clients;

Output:

As we can see in the output, the row with ID=6 is deleted, confirming the successful execution of the query.

How to Drop a View in SQL

Just like a table, we can also drop a view using the ‘DROP statement’. Let’s drop our V_Clients view and then try to execute it to see the results.

DROP VIEW V_Clients;

SELECT * FROM V_Clients;

Output:

As we can see, the output says that the table V_Clients does not exist. This confirms that the view was dropped.

Become an AI-powered Business Analyst

With Purdue University's Business Analysis ProgramExplore Now

What are the Advantages and Disadvantages of a View in SQL?

Like most other objects, functions, and statements, even views have a few advantages and disadvantages. Here are the primary ones:

Advantages of a View

• Security: Views can enhance security by restricting data access to users. For instance, we can limit a user from accessing the actual table that contains sensitive data but provide access to the view that has only insensitive data.
• Easy and simple: While you are an expert in database management, the users for whom you have created the database or an app might not. You can use views to create and hide complex queries while allowing the end-users to call and get the results quickly.
• Memory efficiency: While storing and executing queries from actual tables can consume disk space, views are just virtual tables. Hence, they don’t consume as much space as the actual tables do.
• Make changes quickly: In a vast RDBMS, many tables will be interconnected. Hence, assessing what changes in a particular table will impact other related tables can be daunting and challenging. You can create views and use them instead of actual tables for making the  changes.
• Business consistency and continuity: You can easily make changes to views according to the user requirements, and the effect of the same will be seen quickly. For instance, if you are using it for reports, changing the view will automatically change the content of the reports as required by the users.
• Multiple views for a single table: You can create any number of views from a single table and give access to the users you want to. Thus, you get the facility to provide different users different views of a single table.

Disadvantages of a View

Although there are many advantages and reasons to use a view in SQL, there are a few disadvantages as well. Some of them are discussed below:

• Database relation: If you remove an attribute from an actual table, it can impact the functionality of views, just like in related tables. For instance, if a view is using the email column of a table and you drop that column or even a single data of it that is in use by the view, the output of the view will be impacted.
• Performance: Although views don’t require much space while execution if you have hidden long queries and the end-users keep on executing them multiple times, the performance will be hindered.

When to Use Views in SQL?

Since views have both advantages and disadvantages, the question arises when to use them. Well, the short answer is when you want to write complex select queries that require gathering data from multiple tables. A relatable application of using views will be for fetching reports from a database. You can also use it according to your preference if you feel the need for any of the advantages mentioned above.

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Certification Program. Enroll now!

Conclusion

In this article, you have learned everything about a view in SQL. You have also gone through how to create views and insert/update/delete rows along with the examples. Views provide numerous benefits; hence, it is essential to learn how to use them when working with vast databases. You can now start tweaking the examples above and improve your understanding of views. However, learning about and using views is just one of the fundamental concepts. If you want to pursue a career in database management, understanding the other vital concepts is equally essential. For that, you can opt for Simplilearn’s SQL Certification Training Course. The course provides all the necessary learning materials and covers all the basic and advanced SQL concepts, thereby helping you excel in SQL database management.